How to allow remote connection in MySql

How to allow remote connection in MySql

To achieve this goal, two things need to be done

1. Activate user permissions
2. Unbind local

Activate user permissions

First log in to the MySQL server

//You can log in to mysql directly without using spaces -u username -p password mysql> use mysql
mysql> select user, host from user;

Rendering

After executing the code, you will see all users and their corresponding hosts

The host refers to the IP address that is allowed to be accessed, and % means any IP. Of course, if it is your local server, you can also configure it to a local IP.

Users can use SQL statements to set host to all

//Update the host. Here you need to select an existing user and host.
//In the user table, user and host together are the primary key, so they cannot be repeated. As shown in the figure above, if you only query the root update, an error will be reported mysql> update user set host = '%' where user = 'root' and host = 'localhost'

This modifies the permissions of an existing user.

Or

You can choose to recreate new user permissions.

// Of course, you can also specify some permissions, such as read-only mysql>GRANT ALL PRIVILEGES ON *.* TO 'newuser'@'%' IDENTIFIED BY 'newpassword' WITH GRANT OPTION;

The following is a list of global permissions:

Global management permissions:

FILE: Read and write files on the MySQL server.
PROCESS: Display or kill service threads belonging to other users.
RELOAD: Reload access control lists, refresh logs, etc.
SHUTDOWN: Shut down the MySQL service.

Database/table/column permissions:

ALTER: Modify existing data tables (such as adding/deleting columns) and indexes.
CREATE: Create a new database or table.
DELETE: Deletes records from a table.
DROP: Delete a table or database.
INDEX: Create or delete an index.
INSERT: Add records to the table.
SELECT: Display/search records of a table.
UPDATE: Modify existing records in the table.

Special permissions:

ALL: Allows you to do anything (same as root).
USAGE: Only login is allowed – nothing else is allowed.

Unbind address

I have enabled the permission before, but the local binding has not been released, so I still cannot connect to the server database through the command line locally. Later, I read some information and learned about address binding.

My server is Ubuntu Server and I installed mysql via apt-get. The location of the configuration file is as follows:

/etc/mysql/my.conf
One of the lines is bind-address = 127.0.0.1
Just comment it out

If it is a Windows server, it should be the my.ini file, and you need to find it in the location where MySQL is installed.

From now on, you can access the remote MySQL database locally through mysql -h host -r -p

After the address binding is released, you need to restart the MySQL service to take effect

Thank you for reading, I hope it can help you, thank you for your support of this site!