Solve the problem that changes to the Docker MySQL container database do not take effect

Using the official MySQL image requires some modifications, such as configuration files, DB data file directories, etc. If you re-run the container after the changes, the modified files will be invalid, and the newly generated container will not have the previously changed content.

The first is to modify the image downloaded by the official website, and then submit a new image file docker commit -m and other newly generated image information.

The second type of MYSQL DB data, if you restart the same container with docker restart after the container is closed, the data will be normal. If you re-docker run the container, the data will not be displayed because each container has a file address

This requires mounting the data file for other containers to read.

Start the mysql container in docker, make changes to the database in the mysql container (such as creating a database, changing data, etc.), and enter the container again after committing to find that all previous changes have not been saved

1. Run the mysql container in the background and set the container name to mysql:

[root@localhost ~]# docker run --name=mysql -p 3306:3306 -d owenchen1992/mysql

f80791a0daf194fdba94f16a9d89ebec8ba8fbd8af28d3ea8b599b9d705f85ba

2. Enter the container bash and mysql, create a database TEST_DB, and verify that TEST_DB is created successfully:

[root@localhost ~]# docker exec -it mysql bash root@f80791a0daf1:/# mysql -uroot -p 
show databases; 
create database TEST_DB;

3. Exit the container and commit the changes to the image: This data is actually modified on the host machine, not in the MySQL image. So it doesn't work

mysql> exit
Bye
root@f80791a0daf1:/# exit
exit
[root@localhost ~]# docker commit mysql owenchen1992/mysql

4. Restart the container and enter bash and mysql. You will find that the TEST_DB created previously is gone, indicating that the previous changes are invalid:

[root@localhost ~]# docker container stop mysql
mysql
[root@localhost ~]# docker container rm mysql 
mysql
[root@localhost ~]# docker run --name=mysql -p 3306:3306 -d owenchen1992/mysql
a1a1b4174caaadda0ec4b01b9fe5f92d6b3464d85284042274f71aebde0915dd
[root@localhost ~]# docker exec -it mysql bash
root@a1a1b4174caa:/#mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.20 MySQL Community Server (GPL)
 
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
|mysql |
| performance_schema |
|sys|
+--------------------+
4 rows in set (0.00 sec)

Cause of the problem:

In the MySQL Dockerfile there is a line like this:

VOLUME /var/lib/mysql

This means that all modifications to the directory /var/lib/mysql in the container will be mapped to a certain location on the host. You can view the specific corresponding host directory through the command: docker inspect containerID/name. When running docker commit, changes to the /var/lib/mysql directory in the container are not committed to the image, but these changes are synchronized with the corresponding directory on the host at any time.

When you restart the committed image, the container will create a new directory on the host to save its data updates. Therefore, it is not the original host directory, so the newly opened container cannot see the previous data changes.

Solution:

Now that we know the cause of the problem, it is not difficult to solve it. The steps are as follows:

You can go directly to this directory to view the data directory generated for each container

1. Find the host directory corresponding to the mysql container "/var/lib/docker/volumes/8496bbf33782bdadc027cdcf23197e5ebc36d11deb69ee833d63b557b3a7183d/_data":

[root@localhost ~]# docker inspect mysql
[
......
    "Source": "/var/lib/docker/volumes/8496bbf33782bdadc027cdcf23197e5ebc36d11deb69ee833d63b557b3a7183d/_data",
    "Destination": "/var/lib/mysql",
......
]

Repeat the previous steps of creating a container and running it, run a new container, and then create a TEST table. First close the container and then delete the container service.

4. Run the new container in the background, mount the directory corresponding to the host machine to the /var/lib/mysql directory of the container and enable read and write permissions (key steps):

[root@localhost ~]# docker run --name=mysql -p 3306:3306 -v /var/lib/docker/volumes/8496bbf33782bdadc027cdcf23197e5ebc36d11deb69ee833d63b557b3a7183d/_data:/var/lib/mysql:rw -d owenchen1992/mysql

5. Now enter the newly opened container and check the database: it is found that the database TEST_DB just created does not disappear due to re-running the container, and the problem is solved

The steps are: 1. Create a new table, 2. Shut down the container for creating the table, 3. Re-run the container and mount the data directory to /var/lib/mysql, and the problem is solved!

Supplementary knowledge: How to modify the encoding format when using MySQL in a docker environment

Method 1 (for single machine modification)

First make sure the mysql service is started

Type docker exec -it + service name/bin/bash to enter the console

Type apt-get update command

Type apt-get install vim (the above two commands are used to download the vim editor),

Enter the vim /etc/mysql/mysql.conf.d/mysqld.cnf command to enter the mysqld.cnf file

4. Use i to open the edit mode and enter the following at the end of the text:

Note: The following content must not be entered incorrectly, otherwise the configuration file will become invalid.

[client]
default-character-set=utf8
[mysql]
default-character-set=utf8
[mysqld]
character-set-server=utf8

Press ESC to exit, and press shift+colon wq to save and exit.

5. Enter the mysql console

6. Enter show variables like 'char%'; to view the modified corresponding information

After the modification is completed and the database is restarted: enter show variables like "char%"

The above results indicate that the modification has been successful.

Method 2 (applicable to cluster construction)

Copy the configuration file out of the original directory and modify it using an external file. This method is used to send files to multiple servers to modify the database default encoding.

docker exec -it + service name/bin/bash to enter the console

Enter cd /etc/mysql/conf.d/ to enter the configuration file directory

Enter pwd to get the absolute path of the file

Enter cd to return to the console, exit

Enter docker cp service name:/etc/mysql/conf.d/mysql.cnf /usr/soft (copy the mysql.cnf file in docker to the soft folder under linux)

Open an external editor (such as notepad++) and write the configuration file as follows:

[client]
default-character-set=utf8

[mysql]
default-character-set=utf8

[mysqld]
init_connect = 'SET collation_connection = utf8_unicode_ci'
init_connect='SET NAMES utf8'
character-set-server=utf8
collation-server=utf8_unicode_ci
skip-character-set-client-handshake

PS: It is important to note that there should be spacing between the contents of the configuration file.

Save well

7.docker cp /usr/soft/mysql.cnf Service name: /etc/mysql/conf.d/ Copy the modified file to the original directory and overwrite it

8. Restart the mysql service in docker, then

9. Enter the show variables like 'char%' command to view the modified character encoding format

The above article on solving the problem of Docker MySQL container database changes not taking effect is all the content that the editor shares with you. I hope it can give you a reference, and I also hope that you will support 123WORDPRESS.COM.