mysql挂载点_mysql数据目录挂载关键问题总结

挂载数据目录时，务必保证宿主机上被挂载的目录为空，不然docker run 不起来，查看日志，会报如下错误：

[root@kribee-dong mysqldatatest]# docker logs hello-mysql

2020-02-28 09:23:20+00:00 [Note] [Entrypoint]: Entrypoint script for MySQL Server 5.7.29-1debian9 started.

2020-02-28 09:23:20+00:00 [Note] [Entrypoint]: Switching to dedicated user 'mysql'

2020-02-28 09:23:20+00:00 [Note] [Entrypoint]: Entrypoint script for MySQL Server 5.7.29-1debian9 started.

2020-02-28 09:23:20+00:00 [Note] [Entrypoint]: Initializing database files

2020-02-28T09:23:20.708327Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentationfor more details).

2020-02-28T09:23:20.710160Z 0 [ERROR] --initialize specified but the data directory has files in it. Aborting.

2020-02-28T09:23:20.710188Z 0 [ERROR] Aborting

保证宿主机上被挂载的数据目录为空，再次docker run 时挂载：

[root@kribee-dong test-data]# docker run --name hello-mysql -v /root/test-data:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=123456 -d mysql:5.7

222ac32fc51954750cb60b18caf2b63dec2fb3412e20e88ac1e6c125ac646125

[root@kribee-dong test-data]# docker ps

CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES

222ac32fc519 mysql:5.7 "docker-entrypoint.s…" 3 seconds ago Up 2 seconds 3306/tcp, 33060/tcp hello-mysql

7fecbfc0e5a7 dong-blog:v1 "java -jar blog-spri…" 19 hours ago Up 19 hours 8001/tcp boring_mccarthy

31501194b632 redis "docker-entrypoint.s…" 20 hours ago Up 20 hours 6379/tcp myredis

e0a19274dfa1 rabbitmq:management "docker-entrypoint.s…" 20 hours ago Up 20 hours 4369/tcp, 5671-5672/tcp, 15671-15672/tcp, 25672/tcp dong-rabbit

237e3e7419b3 blog-nginx-dong.com:v1.0 "nginx -g 'daemon of…" 41 hours ago Up 41 hours 0.0.0.0:80->80/tcp dong-blog-nginx

[root@kribee-dong test-data]# ls

auto.cnf ca.pem client-key.pem ibdata1 ib_logfile1 mysql private_key.pem server-cert.pem sys

ca-key.pem client-cert.pem ib_buffer_pool ib_logfile0 ibtmp1 performance_schema public_key.pem server-key.pem

将blog和idbdata复制到刚刚宿主机上被挂载的目录：

[root@kribee-dong mysqldatatest]# cp -r blog /root/test-data

[root@kribee-dong mysqldatatest]# cp ibdata1 /root/test-data

进入mysql容器中，查看：发现刚刚拷贝进去的数据库，可以查看到库，也可以use 到这个库，还可以查看到这个库中所有的数据表，但查询某个表的数据时，会报这个表不存在！

[root@kribee-dong mysqldatatest]# docker exec -it 222ac32fc519 bash

root@222ac32fc519:/# mysql -uroot -p

Enter password:

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 2

Server version: 5.7.29 MySQL Community Server (GPL)

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| blog |

| mysql |

| performance_schema |

| sys |

+--------------------+

5 rows in set (0.00 sec)

mysql> use blog;

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Database changed

mysql> show tables;

+----------------+

| Tables_in_blog |

+----------------+

| announcement |

| blog |

| blog_tag |

| code |

| discuss |

| login |

| message |

| reply |

| role |

| tag |

| user |

| user_role |

+----------------+

12 rows in set (0.00 sec)

mysql> select * from role;

ERROR 1146 (42S02): Table 'blog.role' doesn't exist

通过重启这个容器，即可解决这个问题

[root@kribee-dong mysqldatatest]# docker restart 222ac32fc519

222ac32fc519

[root@kribee-dong mysqldatatest]# docker ps

CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES

222ac32fc519 mysql:5.7 "docker-entrypoint.s…" 4 minutes ago Up 2 seconds 3306/tcp, 33060/tcp hello-mysql

7fecbfc0e5a7 dong-blog:v1 "java -jar blog-spri…" 19 hours ago Up 19 hours 8001/tcp boring_mccarthy

31501194b632 redis "docker-entrypoint.s…" 20 hours ago Up 20 hours 6379/tcp myredis

e0a19274dfa1 rabbitmq:management "docker-entrypoint.s…" 20 hours ago Up 20 hours 4369/tcp, 5671-5672/tcp, 15671-15672/tcp, 25672/tcp dong-rabbit

237e3e7419b3 blog-nginx-dong.com:v1.0 "nginx -g 'daemon of…" 41 hours ago Up 41 hours 0.0.0.0:80->80/tcp dong-blog-nginx

[root@kribee-dong mysqldatatest]# docker exec -it 222ac32fc519 bash

root@222ac32fc519:/# mysql -u root -p

Enter password:

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 3

Server version: 5.7.29 MySQL Community Server (GPL)

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| blog |

| mysql |

| performance_schema |

| sys |

+--------------------+

5 rows in set (0.00 sec)

mysql> use blog;

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Database changed

mysql> show tables;

+----------------+

| Tables_in_blog |

+----------------+

| announcement |

| blog |

| blog_tag |

| code |

| discuss |

| login |

| message |

| reply |

| role |

| tag |

| user |

| user_role |

+----------------+

12 rows in set (0.00 sec)

mysql> select * from login;

Empty set (0.01 sec)

mysql> select * from use_role;

ERROR 1146 (42S02): Table 'blog.use_role' doesn't exist

mysql> select * from user_role;

+--------------+---------+---------+---------------------+--------------+

| user_role_id | user_id | role_id | gmt_create | gmt_modified |

+--------------+---------+---------+---------------------+--------------+

| 1 | 1 | 2 | 2019-12-27 03:10:33 | NULL |

| 2 | 2 | 1 | 2019-12-27 03:10:33 | NULL |

| 3 | 3 | 1 | 2019-12-27 03:10:33 | NULL |

+--------------+---------+---------+---------------------+--------------+

3 rows in set (0.00 sec)