Upgrade Docker version of MySQL 5.7 to MySQL 8.0.13, data migration

1. Back up the old MySQL5.7 data

Remember to back up old data first to prevent data loss due to upgrade failure. There are two ways to back up. One is to directly execute the export command on the host machine, and the other is to enter the Docker environment first to perform the operation. The main export commands are as follows:

#Method 1: Back up data directly on the host machine# 0df568 is the docker id; -uroot -p123456 is the username and password; dbA dbB is the data to be backed up, --databases can be followed by multiple database names, and the exported sql is to /root/all-databases3306.sql
docker exec -it 0df568 mysqldump -uroot -p123456 --databases dbA dbB > /root/all-databases3306.sql
#========================================================================================================================================================
#Method 2: Enter docker first and execute mysqldump, then copy the exported sql to the host #Enter docker first
docker exec -it 0df568 /bin/bash
#Optional source /etc/profile
#Execute the export command mysqldump -uroot -p123456 --databases dbA dbB > /root/all-databases3306.sql
#Copy to the host machine#Exit Docker and execute the exit command exit
#At this point, you are already in the host environment. Execute the copy command to copy the sql file from docker red. docker cp 0df568:/root/all-databases3306.sql /root/all-databases3306.sql

2. Pull the image of MySQL8.0.13 and complete the installation

You can actually refer to the official website to pull and install MySQL. Reference URL: https://hub.docker.com/r/mysql/mysql-server/. Here is a brief description of the installation process.

2.1 Pull the image to local

Run the following command to pull the latest MySQL image

 docker pull mysql/mysql-server:lastest

Pull the specified MySQL image

#tag is to fill in the version number you want. For example, if you want MySQL8.0.13, then the tag is docker pull mysql/mysql-server:8.0.13
docker pull mysql/mysql-server:tag

You can use docker images to view the progress you pulled down.

2.2 Run MySQL 8.0.13 image

Run the specified MySQL8.0.13 Docker image. We have already pulled the specified image in step 2.1. Then we can view the local Docker image through docker images and get the image ID and name. Next, run the following command to run the Docker image of MySQL 8.0.13:

#--name specifies the name after running and starting -e specifies the environment variables in docker -v partition or directory mapping, docker program data is mapped to the specified location of the host -p specifies the port mapping from the host to the docker program -d specifies the version of the image.
docker run --name=mysql8.0 -e MYSQL_ROOT_PASSWORD=123456 -v /root/dockerdata/mysql8.0/data:/var/lib/mysql -p 3307:3306 -dit mysql/mysql-server:8.0.13

Check if it is running

# Execute the following command docker ps

3. Some problems after upgrading to MySQL 8.X

After upgrading MySQL 8.x, remote connections cannot be connected, which is very inconvenient and needs to be modified. It mainly includes the following two aspects of configuration:

3.1 Providing Remote Access

Change the user to @'%', % means all hosts can connect, the command is as follows:

#First log in to the database as root and execute the following commands #a. Use msyql
use mysql;
#b. Then execute authorization GRANT ALL PRIVILEGES ON . TO 'root'@'%' IDENTIFIED BY '123456'
#c. Be sure to refresh the permissions (reload), otherwise you need to restart MySQL to take effect FLUSH PRIVILEGES;

3.2 Change the password verification method for the connection

The encryption method of MySQL8.X is caching_sha2_password. The caching_sha2_password encryption method is not supported during remote access, so it needs to be changed to: mysql_native_password

# a. Modify the specified user ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '123456';
# b. Be sure to refresh the permissions (reload), otherwise you need to restart MySQL to take effect FLUSH PRIVILEGES;

4. Import data into the new MySQL

To import data, I first copy the database backup file into the new Docker, and then execute the import command. The command is as follows:

#Copy the backup file to docker cp /root/all-databases3306.sql 3sfsdf:/root/all-databases3306.sql 
#First enter the docker environment, then import it into the database docker exec -it xxx /bin/bash
mysql -u root -p < /root/all-databases3306.sql

5. Reference Documents

https://hub.docker.com/r/mysql/mysql-server/
https://bugs.mysql.com/bug.php?id=92675

Summarize

The above is the editor's introduction to the upgrade of Docker version of MySQL5.7 to MySQL8.0.13 and data migration. I hope it will be helpful to everyone. If you have any questions, please leave me a message and the editor will reply to you in time. I would also like to thank everyone for their support of the 123WORDPRESS.COM website!
If you find this article helpful, please feel free to reprint it and please indicate the source. Thank you!