How to install MySQL in Ubuntu14.04. Today, I will share with you how to configure MySQL easily, which can achieve remote connection.
The mysql configuration file is in /etc/mysql.my.cnf, as shown in the following figure. The configuration file is very rich, we can see the mysql user, listening port number, data file storage directory, etc.
2. Run the cat command to view the contents of my. CNF. As shown in the following figure, in the my.cnf configuration file, the default IP address of bind-address is 127.0.0.1, which means that connections are restricted to the local IP address. If mysql is not configured, you cannot connect to the database using Navicat or any other remote connection tool.
3. Set the IP address of bind-address to 0.0.0.0 so that other external IP addresses can be accessed normally. Use vi or vim to edit the my.cnf configuration file, as shown below.
4. After modifying the configuration file my.cnf, you need to restart mysql. Enter sudo service mysql restart, as shown in the following figure.
If the mysqld process already exists, the mysql server is successfully started.
5. Next, connect to the database remotely through Navicat. Run ifconfig to view the IP address. The IP address is 192.168.255.131.
6. Open Navicat and click New Connection, as shown below.
7. Click “Connection Test” to pop up the interface below. The connection test failed because we have not authorized the mysql remote connection yet. Mysql permissions are very strict, even though we have opened the IP address, we have not authorized root because the connection still failed. In other words, after we authorize the root user, we will be able to access the database with an IP address other than 127.0.0.1. If not set, external IP connections are invalid by default, except for local localhost connections. Even though their IP is bound at 0.0.0.0, they still have no permissions.
8. Next, let’s go to mysql in Ubuntu.
9. Enter the remote authorization command with the following syntax template: Grant all PRIVILEGES on database name. To ‘userid ‘@’IP’ identified by ‘password’ with grant option;
Grant all PRIVILEGES on *.* to ‘root’@’% ‘identified by ‘123456’ with grant option;
*.* is a regular expression that represents authorization for all tables. Root indicates the root user. % represents all external IP addresses; 123456 indicates the password.
As shown below:
10. After authorization, enter the command “Flush PRIVILEGES;” To refresh permissions, as shown in the figure above. Then you can exit the mysql database.
11. At this point, go to Navicat and try the connection test again, as shown below. You can see that the test connection succeeded.
Navicat (192.168.255.131); Navicat (192.168.255.131);
13. Double-click the left 192.168.255.131 database to view the database information. You can then operate the database remotely in Navicat, in sync with the database in Ubuntu.
Mysql remote connection setup in Ubuntu