How to configure MySQL on Ubuntu 16.04 server and enable remote connection

background

I am learning nodejs recently, and I remembered that I had a cloud server, but I haven't used it for a long time. Because I was afraid of trouble, I reinstalled an Ubuntu system on the cloud host. Then configuring MySQL became a part of configuring the service (it doesn't matter whether node uses MySQL or not, I just reconfigure one when I have nothing to do -.-). However, I encountered many problems during the configuration process, so after solving a series of problems, I will leave this blog post for future use.

step

1. Install MySQL

Since the blogger uses Ubuntu Server and the XShell tool instead of the desktop version, there is no high-end graphical interface. You can just use the mysql provided by the software source.

The command is as follows:

sudo apt-get install mysql-server

After running this command, if you are not root, you will be asked to enter the root password. After the password is entered correctly, the system will automatically download MySQL for you, as shown below:

After completing the above steps, you will enter a "graphical interface :)" to create a MySQL root password, as shown in the figure:

After entering the password, press Enter and you will be asked to confirm the password, as shown in the figure:

2. Authorize users and allow remote login

If the password is entered correctly twice, the system will help you download MySQL. However, the default MySQL only has one root account, so you might as well create an account with the same rights as root and authorize remote login permission. Then we log in to MySQL first:

mysql -u root -p

The system will ask you to enter the password. After entering the password correctly, enter MySQL:

First, we authorize an account called Ubuntu (you can choose the name) and grant it the right to connect remotely. The command is as follows:

After running, enter immediately to update the database:

FLUSH PRIVILEGES;

The effect is as follows:

Execute quit to exit mysql.

Since MySQL is used locally by default and does not open remote connections, you need to modify the configuration file. Of course, I don’t know why the configuration file of the new version of MySQL is different from the previous one. It used to be placed in: /etc/mysql/my.cnf, but now let’s take a look at what it looks like:

run:

sudo vi /etc/mysql/my.cnf

It turns out that the content in my.cnf is as follows. I personally guess that MySQL has optimized its structure. The effect is shown in the figure:

The original configuration file has become a directory structure, so look in the two directories mentioned above and you will soon find the configuration file: /etc/mysql/mysql.conf.d/mysqld.cnf

Edit it with administrator privileges:

sudo vi /etc/mysql/mysql.conf.d/mysqld.cnf

Add '#' to comment out "bind-address = 127.0.0.1", as shown below:

After commenting: wq save and restart the MySQL service:

service mysql restart

After verifying your Ubuntu password, restart the service successfully!

3. Test verification

I tried using Navicat for MySQL under Windows, the configuration information is as follows (coded to prevent hacking:D):

Take a look at the effect:

The above is the full content of this article. I hope it will be helpful for everyone’s study. I also hope that everyone will support 123WORDPRESS.COM.