Cloud server ubuntu_server_16.04.1 install MySQL and turn on remote connection

1. Install MySQL:

Use the following three commands to install the appropriate software:

$sudoapt-get install mysql-server$sudoapt-get install mysql-client$sudoapt-get install libmysqlclient-dev

In the process of executing the first command, you need to set the password for the root account of MySQL.
Use the following command to view the Scoket status of MySQL, and if you are in listen state, the installation is successful.

$sudonetstat|grep mysql

2. mysql to open remote connection

1. Modify the MySQL configuration file, bind-address = 127.0.0.1 Note, open all connections
Use the following command to modify:

$sudovim /etc/mysql/mysql.conf.d/mysqld.cnf

Just press "I" to open vim Insert, then add "#" before the statement to be commented, then press "ESC" to exit the Vim insert state, then press ": Wq" to save and exit. (mysql5.7+ configuration file as above, previous version in "/etc/mysql/my.cnf").
2. Create a user and authorize remote connections
To log in to MySQL first, the command is as follows:

$mysql-u root -p

Then create the user and authorize the command format as follows:

privilegesONTO'username'@'host'IDENTIFIEDBY'password'WITHGRANTOPTION;

such as my own configuration command:

allprivilegesonto'ubuntu'@'%'identifiedby'123456'withgrantoption;

Note: host=% indicates that the IP address of the connection is not restricted.
Refresh the permissions so that the above configuration takes effect immediately:

privileges;

Exit MySQL:

mysql>exit;

3. Test remote Connection

1. View the port number of the MySQL configuration
First go to MySQL, and then look at the port number, the command is as follows:

$mysql-u root -pmysql>'port';

MySQL's default port number is 3306, if you need to modify the port number, you can go to the configuration file to modify the ports information (see 2.1), the following port=3306 as an example.
2. View the Ubuntu Firewall
To view the firewall status:

$sudoufw status

Turn on the firewall and open port 3306

$sudoufw enable$sudoufw default deny$sudoufw allow 3306

Remember to open other necessary ports, such as SSH Port 22.

View 3306 Port Status

$netstat-an|grep 3306

3. Test MySQL remote connection
Open a command-line window on your computer, command format:

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

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

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

Cloud server ubuntu_server_16.04.1 install MySQL and turn on remote connection