Docker version of MySQL5.7 upgraded to MySQL8.0.13 data migration

2021-06-28 14:30:21
OfStack

1. Back up 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 execute the export command directly on the host machine, the other is to first enter the Docker environment for operation.The main export commands are as follows:


# mode 1 To back up data directly on the host machine 
# 0df568  yes docker Of id ;-uroot -p123456  Is user name and password ;dbA dbB Is the data to be backed up, --databases  You can then use more than one database name to export sql reach /root/all-databases3306.sql
docker exec -it 0df568 mysqldump -uroot -p123456 --databases dbA dbB > /root/all-databases3306.sql
#========================================================================================================================================================
# mode 2 , enter first docker Execution mysqldump And then export the sql Copy to Host 
# Advanced Entry docker
docker exec -it 0df568 /bin/bash
# Optional 
source /etc/profile
# Execute export command 
mysqldump -uroot -p123456 --databases dbA dbB > /root/all-databases3306.sql
# Copy to host machine 
# Sign out Docker , execute exit command 
exit
# At this point, in the host environment, a copy command is executed that will sql Files from docker Red Copy Out 
docker cp 0df568:/root/all-databases3306.sql /root/all-databases3306.sql

2. Pull the mirror of MySQL8.0.13 to complete the installation

Pull and install MySQL can actually refer to the official website completely.Reference Web site: https://hub.docker.com/r/mysql/mysql-server/, here is a simple description of the installation process under 1.

2.1 Pull mirror 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, such as MySQL8.0.13, tag is 8.0.13, then execute: docker pull mysql/mysql-server:8.0.13 docker pull mysql/mysql-server:tag

You can use docker images to see what you've pulled down.

2.2 Run MySQL8.0.13 Mirror

Run the specified Docker image of MySQL8.0.13, we have pulled down the specified image in step 2.1, then you can see the local Docker image through docker images to get the ID and name of the image.Next, run the following command to run the Docker image of MySQL8.0.13:


#--name Specify the name after the run starts  -e  Appoint docker Environment variables in  -v  Partition or directory mapping, docker Program data mapped to the host's specified location  -p  Appoint   Host to docker Port Mapping for Programs  -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 to see if running


#  Execute the following commands 
docker ps

3. Some issues after upgrading to MySQL8.X

After upgrading MySQL8.x, the remote connection could not be connected, which is inconvenient and needs to be modified.There are two main configurations:

3.1 Providing remote access

Modify the user to @'%',%means that all hosts can connect with the following commands:


# before root Log in to the database and execute the following commands 
#a. Use msyql
use mysql;
#b. Then execute the authorization 
GRANT ALL PRIVILEGES ON . TO 'root'@'%' IDENTIFIED BY '123456'
#c.1 Do refresh permissions (reload), otherwise restart is required MySQL Can take effect 
FLUSH PRIVILEGES;

3.2 Change the password checking method for connections

MySQL8.X is encrypted using caching_sha2_password, caching_sha2_password encryption is not supported for 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.1 Do refresh permissions (reload), otherwise restart is required MySQL Can take effect 
FLUSH PRIVILEGES;

4. Import data into the new MySQL

Import data by copying the database backup file into the new Docker before executing the import command.The commands are as follows:


# Copy backed up files to docker in 
docker cp /root/all-databases3306.sql 3sfsdf:/root/all-databases3306.sql 
# Advanced Entry docker Environment, then import to 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

summary