Ubuntu 18.04 installs mysql 5.7.23

I installed MySQL smoothly in Ubuntu 16.04 before. This time, I encountered some problems when installing MySQL 5.7.23 in Ubuntu 18.04. It took me a long time to do so. Here is a record.

1. Install the database

sudo apt-get install mysql-server

By default, mysql-client and other related clients are installed when mysql-server is installed.

2. There will be problems if you log in directly at this time

This is a pitfall. After a lot of trouble, I found that it would be successful if I logged in with root permissions.

Well, since we can only log in with root user privileges, let's first look at the key information in mysql.user.

First check the user table structure:

mysql> show columns from user; 

There are mainly these items:

Host User authentication_string (here is the encrypted password) plugin

Check out these key data in the table:

As you can see, the root password is still empty, and plugin is obviously different from others. After checking relevant information, I found that this is the problem why ordinary users cannot log in.

3. Solve the problem and change the password

mysql> update user set authentication_string=PASSWORD("123456"), plugin="mysql_native_password" where user="root";
 
mysql> FLUSH PRIVILEGES;
 
# Exit and restart the MySQL service mysql> exit;
fknight@v310:~$ service mysql restart

After update:

Now you can log in to the MySQL database using the root account name with normal user privileges. Workbench/Navciat can then connect to the database and be used normally.

Summarize:

# Install MySQL and Workbranch
fknight@v310:~$ sudo apt-get install mysql-server mysql-client mysql-workbench
 
# Connect to the database with root privileges, the initial password is empty fknight@v310:~$ sudo mysql -u root -p
 
# Modify the plugin so that ordinary users can also log in as the root user of mysql, and modify the root password mysql> update user set authentication_string=PASSWORD("123456"), plugin="mysql_native_password" where user="root";
 
# Exit and restart the MySQL service mysql> exit;
fknight@v310:~$ service mysql restart

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.