Cloud server Ubuntu_Server_16. 04.1 Method for installing MySQL and opening remote connection

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

During the execution of Command 1, you need to set the password for the root account of MySQL.

Use the following command to view the scoket status of MySQL. If it is in listen status, it means that the installation is successful.

$sudo netstat -tap | grep mysql

2. MySQL Open Remote Connection

1. Modify the mysql configuration file to set the bind-address = 127.0.0.1 Comments, open all connections

Modify with the following command:

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

That is, press "i" to start vim insertion, then add "#" before the statement to be annotated, then press "esc" to exit vim insertion status, and then press ": wq" to save and exit. (The configuration file for mysql 5.7 + is as above, and the previous version is in "/etc/mysql/my. cnf").

2. Create a user and authorize remote connections

Log in to MySQL first, and the command is as follows:

$mysql -u root -p

Then create a user and authorize it. 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 =% denotes an IP address that does not restrict connections.

Refresh permissions so that the above configuration takes effect immediately:

mysql>flush privileges;

Exit MySQL:

mysql>exit;

3. Test the remote connection

1. View the port number configured for MySQL

Enter MySQL first, and then look at 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 modify the port number, you can enter the configuration file to modify the port information (see 2.1 for operation). Take port=3306 as an example below.

2. View the firewall of Ubuntu

View firewall status:

$sudo ufw status

Open the firewall and open port 3306

$sudo netstat -tap | grep mysql

0

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

View Port 3306 Status

$sudo netstat -tap | grep mysql

1

3. Test the MySQL remote connection

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

$sudo netstat -tap | grep mysql

2

As configured above, the command to connect to the remote MySQL is:

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

Summarize