How to install MySQL and enable remote connection on cloud server Ubuntu_Server_16.04.1

1. Install MySQL:

Use the following three commands to install the corresponding software:

$ sudo apt-get install mysql-server
$ sudo apt-get install mysql-client
$ sudo apt-get install libmysqlclient-dev

When executing the first command, you need to set the password for the MySQL root account.

Use the following command to check the MySQL socket status. If it is in the listening state, it means the installation is successful.

$ sudo netstat -tap | grep mysql

2. MySQL opens remote connection

1. Modify the mysql configuration file, comment out bind-address = 127.0.0.1 , and open all connections

Use the following command to modify:

$sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf

Just press "i" to start vim insertion, then add a "#" sign before the statement to be commented out, then press "esc" to exit vim insertion state, and then press ":wq" to save and exit. (The configuration file for mysql5.7+ is as above, and the previous version is in "/etc/mysql/my.cnf").

2. Create a user and authorize remote connections

First log in to MySQL, the command is as follows:

$mysql -u root -p

Then create a user and authorize him. The command format is as follows:

mysql>GRANT privileges ON databasename.tablename TO 'username'@'host' IDENTIFIED BY 'password' WITH GRANT OPTION;

Such as my own configuration command:

mysql>grant all privileges on *.* to 'ubuntu'@'%' identified by '123456' with grant option;

Note: host=% means that the IP address of the connection is not restricted.

Refresh permissions to make the above configuration take effect immediately:

mysql>flush privileges;

Exit MySQL:

mysql>exit;

3. Test remote connection

1. Check the port number configured for MySQL

First enter MySQL, and then check the port number. The command is as follows:

$mysql -u root -p
mysql>show variables like 'port';

The default port number of MySQL is 3306. If you need to change the port number, you can enter the configuration file to modify the port information (see 2.1 for operation). The following takes port=3306 as an example.

2. Check Ubuntu's firewall

Check the firewall status:

$ sudo ufw status

Open the firewall and open port 3306

$ sudo ufw enable
$ sudo ufw default deny
$ sudo ufw allow 3306

Remember to open other necessary ports, such as ssh port 22.

Check the status of port 3306

$netstat -an | grep 3306

3. Test MySQL remote connection

Open a command line window on your computer. The command format is:

$mysql -h ipaddress -P port -u ubuntu -ppassword

According to the above configuration, the command to connect to the remote MySQL is:

$mysql -h 193.112.19.56 -P 3306 -u ubuntu -p123456

Summarize

The above is the method that I introduced to you to install MySQL and enable remote connection on the cloud server Ubuntu_Server_16.04.1. I hope it will be helpful to you. If you have any questions, please leave me a message and I will reply to you in time. I would also like to thank everyone for their support of the 123WORDPRESS.COM website!