MySQL 8.0 upgrade experience

Preface

I have been busy with the Fighting project recently, so I have been a little slack in writing, which is really not right.

I just happened to encounter the need to upgrade from MySQL5.7.33 to MySQL8.0.x, so I recorded the entire upgrade process to avoid the pitfalls.

Background summary: The MySQL used in the local Docker container is 5.7.33 (the image system uses Ubuntu 16.04), while the new project uses MySQL 8.0 online. The SQL file (table structure + data) exported from the online has a character encoding that is not used by the lower version of MySQL. An error occurs when it is imported. The character set error encountered is as follows:

 - Unknown collation: 'utf8mb4_0900_ai_ci'

After searching for a while, I calmed down and came up with two ideas. One is what I call a self-defeating solution, and the other is a radical solution.

As the name suggests, covering one's ears and stealing the bell does not solve the essential problem, but solves the error by replacing the character set in batches, that is, replacing all utf8mb4_0900_ai_ci in the file with: utf8_general_ci.

The second method is to keep it consistent with the online MySQL version so that the character set can be supported.

So I used the second method to upgrade MySQL in my docker container and package a new image at the same time.

The upgrade process is a bit bumpy, but the logic is clear.

1. First completely uninstall the existing version of MySQL.

You can use the following command:

#Delete mysql data files sudo rm -R /var/lib/mysql/
#Delete the mysql configuration file sudo rm -R /etc/mysql/

#Automatically uninstall mysql (including server and client)
sudo apt-get autoremove mysql* --purge
sudo apt-get remove apparmor

Remember to delete the configuration file, as it may be incompatible.

2. Download deb and install MySQL 8.x.

You can download it from the official website at https://dev.mysql.com/repo/apt

We choose the latest deb download, the URL is:
https://dev.mysql.com/get/mysql-apt-config_0.8.20-1_all.deb. Use the wget command to download after the command, as follows:

wget https://dev.mysql.com/get/mysql-apt-config_0.8.20-1_all.deb

After the download is complete, use the dpkg command and update the apt source, then you can install it. The complete command is as follows:

dpkg -i mysql-apt-config_0.8.20-1_all.deb
apt-get update
apt-get install -y mysql-server

During the last command execution, it will ask you to enter the root password and some basic configuration, and then you can complete the installation.

Because MySQL is installed in Ubuntu, the startup method is:

service mysql start

You can use the command to view the process running status. When normal, the output is similar to the following:

/usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --log-error=/var/log/mysql/error.log --pid-file=/var/run/mysqld/mysqld.pid --socket=/var/run/mysqld/mysqld.sock

3. Create users and configure permissions.

In the actual use of MySQL 8.0.x, it is found that user authorization is slightly different from previous versions and needs to be divided into three steps:

1. Create a user.
2. Assign permissions.
3. Refresh permissions.
   

The specific SQL statements are as follows, and the order is strict:

create user 'freephp'@'%' identified by '176serTTc8Cg';

grant all privileges on dev_database.* to 'freephp'@'%' with grant option;

flush privileges;

Let me explain the above SQL. The first sentence creates a user named freephp with a password of 176serTTc8Cg. % means that any host can connect.

The second sentence means to grant all permissions of dev_database to user freephp.

The third sentence is to refresh the permissions immediately so that the newly created permissions can take effect.

The adventure is now complete. The new features of MySQL 8.0 still need to be explored. We will talk about it next time.

Summarize

This is the end of this article about MySQL 8.0 upgrade pitfalls. For more information about MySQL 8.0 upgrade pitfalls, please search 123WORDPRESS.COM’s previous articles or continue to browse the following related articles. I hope you will support 123WORDPRESS.COM in the future!