Remote connection to MySQL (MariaDB) database

Network environment:

• The operating system of the MySQL (MariaDB) server is CentOS 7
• MySQL (MariaDB) Server ip:172.16.230.15
• Client operating system for Windows R2
• Client Host ip:172.16.230.200

In the actual project or project development, if the database server is placed in the cabinet of the computer room or the computer room, the development or engineering personnel can only operate through the network remote Connection database, need to use the content that I introduce below. The topology diagram is as follows:

 

1, the Client remote connection database needs to install the MySQL client tool, MySQL client tools online There are many, we can Baidu a bit.

2, look at the IP address of Windows 2008, see if and the server in the same network segment (even if not the same network segment, as long as the two sides can ping each other to OK), see the method as follows:

c:\mysql5628\bin>ipconfigwindows IP Configuration Ethernet Adapter Local Area Connection:   connection-specific DNS suffix .......:   local link IPv6 address . . . . . . . . : Fe80::ecba:5cc1:388a:edd2%11   172.16.230.200    subnet mask   255.255.255.0    172.16.230.2

3, in the client testing and database server connectivity:

C:\mysql5628\bin>ping 172.16.230.15172.16.230.15 with areply of 172.16.230.15: bytes =32 time <1ms ttl=64  172.16.230.15 reply: Byte =32 time <1ms ttl=64172.16.230.15 reply: Byte =32 time <1ms ttl=64  172.16.230.15 reply: Byte =32 time <1ms ttl=64172.16.230.15= 4, received = 4, lost = 0 (0%= 0ms, maximum = 0ms, average = 0ms

The above feedback indicates that the network connectivity between the client and the server is not a problem.

4. The client tries to connect to the database remotely through the network using the user name and password

C:\mysql5628\bin>mysql-h 172.16.230.15-u Root-**********2003 (HY000): Can'  '172.16.230.15'  (10060)

This error indicates that the database is not allowed to connect to the database except for any hosts outside of this machine. To solve this problem, we need to make changes to the access permissions on the server to allow other hosts to access the database as well.

5, the following two steps need to operate on the server

(1) For security purposes, set the password to the root user of the database

[[Email protected] ~]#mysql-u root-p//log in to the database with the root userEnter Password://The newly installed database does not have a password, just press ENTER here to line welcome to the MariaDB Monitor. Commands End With; or\g.your MariaDB Connection ID is4Server Version:5.5.52-MariaDB MariaDB servercopyright (c)+, Oracle, MariaDB Corporation Ab andothers. Type'Help ;' or '\h'  forHelp. Type'\c'To clear the current input statement. MariaDB [(none)]> Set Password for 'Root'@'localhost'= Password ('mycisco.cc'); //The password that is set for the root user is mycisco.cc, note that there is no space between the second password and the opening parenthesis. Query OK, 0 rows affected (0.00sec) MariaDB [(none)]>

This way, users must enter the correct user name and password to log in to the database. You can enter exit at the prompt to exit the database and then log back in to try to know that the new password is already in effect.

(2) For other hosts to remotely connect to the database open access permissions, re-login database:

MariaDB [(none)]> use MySQL; //Select MySQL database to operate reading table information forCompletion of Table andcolumn namesyou can turn off this feature-get a quicker startup with-adatabase changedmariadb [MySQL]> select User,password,host fromUser //View User,password,host permissions assignment for these three fields+------+-------------------------------------------+-----------------------+| user | password | Host |+------+-------------------------------------------+-----------------------+| Root | *139ebd293a149d3c25dbd676d882bcba5a00223e | localhost | |                                                                                        Root | | Localhost.localdomain | |                                                                                        Root | | 127.0.0.1 | |                                                                                        Root | |      :: 1 | |                                                                                           | |      localhost | |                                                                                           | | Localhost.localdomain |+------+-------------------------------------------+-----------------------+6 rows  inchSet (0.00sec)//The above output shows that the database only allows the user root to log on on the local server (localhost) and does not allow other hosts to connect remotely. MariaDB [MySQL]> Grant all privileges on * * to [email protected]"%"Identified by"mycisco.cc"; //The above statement will allow the user root to connect to the database on any host with a password (mycisco.cc) and give the user all permissions. Query OK, 0 rows affected (0.01sec) should not forget to refresh to make it effective after the last configuration of permissions mariadb [MySQL]>flush Privileges; Query OK, 0 rows affected (0.00sec) MariaDB [MySQL]> select User,password,host fromuser;+------+-------------------------------------------+-----------------------+| user | password | Host |+------+-------------------------------------------+-----------------------+| Root | *139ebd293a149d3c25dbd676d882bcba5a00223e | localhost | |                                                                                        Root | | Localhost.localdomain | |                                                                                        Root | | 127.0.0.1 | |                                                                                        Root | |      :: 1 | |                                                                                           | |      localhost | |                                                                                           | | Localhost.localdomain | | Root | *139ebd293a149d3c25dbd676d882bcba5a00223e |  %          | In this line, the "%"means that any host is allowed to connect to the database+------+-------------------------------------------+-----------------------+7 rowsinchSet (0.00sec) MariaDB [MySQL]>

This allows access to the database to be set up.

6, back to the client to try to connect, 1, to display the connection database successfully.

 

Note: If the connection is unsuccessful, it may be that the firewall in the server is not turned off, and you can turn off FIREWALLD and restart the system by systemctl disable Firewalld .

MariaDB differs from the normal MySQL database in that it has more than one configuration file, which puts different data into different configuration files, and the previous /etc/mysql/my.cnf content is as follows:

 

From the comments in the file, it has so many configuration files

1. /ETC/MYSQL/MARIADB.CNF default configuration file,
2. /ETC/MYSQL/CONF.D/*.CNF setting the global item's file
3. "/ETC/MYSQL/MARIADB.CONF.D/*.CNF" setting information related to MARIADB
4. "~/.MY.CNF" Sets the information for this account

This is why we do not work in the MY.CNF setting (there may be the same item in other profiles, and MySQL eventually uses the settings in another file).
According to the official statement, MARIADB to improve security, by default only to listen to the 127.0.0.1 in the 3306 port and prohibit the remote TCP link, we can use the following two steps to open MySQL remote service

1. Comment out the skip-networking option to turn on remote access.
2. Note the bind-address item, which represents the machine connection that runs which IP address, allows all remote machines to connect, but with so many configuration files, where exactly are these two options? This time use grep to recursively find all the files in the/etc/mysql/directory to see which file contains the string
   We enter:

" skip-networking " *

The results are as follows:

 

Fortunately, both of these items are in the same file (my own is not skip-networking)
We open the file /etc/mysql/mariadb.conf.d/50-server.cnf , comment out the bind-address entry, as follows:

 

Only these are still not enough, we just turned on the MySQL monitor remote connection option, next need to assign the corresponding MySQL account permissions, allow the use of this account remote connection to MySQL
Input

 from Mysql.user;

To view user account information:

 

The host entry in the root account is localhost, which means that the account can only log on locally, we need to modify the permissions and enter the command:

' Root '@'%'password' with GRANT OPTION;

Modify the remote connection password
Go to MySQL First

' Root '@'%' New password ' with GRANT OPTION;

Modify Permissions. % indicates that for all ip,password the root user will be logged in with this password, and if you want to connect only the host of an IP segment, you can change to

' Root '@'192.168.100.%'my-new-password' with GRANT OPTION;

Note: The password for the remote connection may be different from the one you logged in locally, depending on what password you gave behind identified by.
Please refer to the grant command for details.

Finally, you should not forget to make the refresh effective after you configure the permissions.

FLUSH privileges;

Save your changes. Then look at the user information:

 

This time found that compared to the previous one, its host item is%, this time the configuration is successful, we can use the account for remote access
Enter the shell service MySQL Restart Restart the remote server and test it:

 

If these are done, still can't connect, you can see if the port is blocked by the firewall

Remote connection to MySQL (MariaDB) database