Solve the problems encountered when upgrading Docker mysql container to mysql8

mysql video tutorial This column introduces the problems encountered when upgrading the Docker mysql container to mysql8 and tells you how to effectively solve it.

Recommended (free): mysql video tutorial

Problem Restoration
The original mysql container creation command is as follows

docker run --name mysql -v /xxxx/xxx/mysqldata:/var/lib/mysql -p xxx:3306 -d mysql:laster

The data directory is mounted to /xxxx/xxx/mysqldata. The leader below asked me to upgrade mysql. I naively downloaded a mysql: 8.0.11 latest
mysql image and then execute

docker run --name mysql -v /xxxx/xxx/mysqldata:/var/lib/mysql -p xxx:3306 -d mysql:8.0.11

Create the container and mount it to the original directory: /xxxx/xxx/mysqldata

As a result, the container crashes in seconds and cannot be started at all View the log

 docker logs -f -t --tail 70 mysql

The log said that the plug-in file under /xxxx/xxx/mysqldata was created in version 5.7 and could not be started in 8.0. Then I switched back to the original mysql:laster image to create the container, and it said that the files in this directory were modified by 8.0. Unable to start, it feels like this directory is so useless, the old image cannot be started, and the new one cannot be started.

So I was so stupid that I cried. Don’t directly use the new image to start the container and mount the original mysql directory. Some colleagues said that this is okay, but for What's wrong with me? Is there a problem with people?

Solution:
Use the old mysql:laster image to connect to the backup directory of /xxxx/xxx/mysqldata,Backup is too important. Start a mysql container
Use the new mysql: 8.0.11 to start a new mysql container and mount an arbitrary directory. Then restore the old data backup to the new mysql container through other tools such as navicat. Or through the command
$ docker exec some-mysql sh -c 'exec mysqldump --all-databases -uroot -p"$MYSQL_ROOT_PASSWORD"' > /some/path/on/your/host/all- databases.sql Backup and restore.

1. If your new mysql is successfully restored and you use the navicate tool to connect, the following error is reported

Then you need to modify my.cnf of mysql File:
Add:

default_authentication_plugin=mysql_native_password

Because 8.0 uses caching_sha2_password
You can enter the container:

docker exec -it mysql /bin/bash
mysql -uroot -pxxxx
use mysql
select Host,User,plugin from mysql.user;

See the picture below

Why do I have to modify caching_sha2_password to mysql_native_password? I don’t know. It’s just that after my 8.0.11 mysql container is started, the program interface still cannot be used normally and an error message is reported: Unable to connect to an unsupported authentication method. At this time You may need to upgrade the mysql driver of the program interface, but you are too lazy to do so, so you can only modify caching_sha2_password to mysql_native_password. As a result, you can connect normally.

3. Record some docker commands

重容器中拷贝文件到宿主机 不需要容器启动
docker cp 容器:/etc/mysql/my.cnf  /home/xxx/my.cnf 

将宿主机的文件拷贝容器里面的目录下 会覆盖老的文件
docker cp /home/xxx/my.cnf   容器:/etc/mysql/
如果你特别牛逼程序中是用root来连接账号的那你可能还需要创建一个 ‘root’@'%' 的账号并修改它的权限可以远程访问，修改命令网上找

The above is the detailed content of Solve the problems encountered when upgrading Docker mysql container to mysql8. For more information, please follow other related articles on the PHP Chinese website!