首页 > 数据库 >【Docker】# MySQL从安装到备份还原

【Docker】# MySQL从安装到备份还原

时间：2023-09-13 10:05:45浏览次数：57  
标签：Mar 备份 MySQL 28 mysql xr docker root Docker

Writer：夏明亮

Date：2022/03/28

Docker安装

略

MySQL安装部署

https://hub.docker.com/_/mysql/#:~:text=Run%20docker%20stack%20deploy%20-c%20stack.yml%20mysql%20%28or,you%20to%20run%20commands%20inside%20a%20Docker%20container.

搜索合适的镜像

[root@docker-master ~]# docker search mysql --filter is-official=true
NAME         DESCRIPTION                                     STARS     OFFICIAL   AUTOMATED
mysql        MySQL is a widely used, open-source relation…   12320     [OK]       
mariadb      MariaDB Server is a high performing open sou…   4736      [OK]       
percona      Percona Server is a fork of the MySQL relati…   572       [OK]       
phpmyadmin   phpMyAdmin - A web interface for MySQL and M…   486       [OK]       
[root@docker-master ~]#

拉取镜像

[root@docker-master ~]# docker pull mysql
Using default tag: latest
latest: Pulling from library/mysql
a4b007099961: Pull complete 
e2b610d88fd9: Pull complete 
38567843b438: Pull complete 
5fc423bf9558: Pull complete 
aa8241dfe828: Pull complete 
cc662311610e: Pull complete 
9832d1192cf2: Pull complete 
f2aa1710465f: Pull complete 
4a2d5722b8f3: Pull complete 
3a246e8d7cac: Pull complete 
2f834692d7cc: Pull complete 
a37409568022: Pull complete 
Digest: sha256:b2ae0f527005d99bacdf3a220958ed171e1eb0676377174f0323e0a10912408a
Status: Downloaded newer image for mysql:latest
docker.io/library/mysql:latest
[root@docker-master ~]# 
[root@docker-master ~]# 
[root@docker-master ~]# 
[root@docker-master ~]# 
[root@docker-master ~]# 
[root@docker-master ~]# docker image ls
REPOSITORY                      TAG       IMAGE ID       CREATED       SIZE
4e45816885ee_bak_202203251500   latest    e0234dc591c2   2 days ago    279MB
mysql                           latest    562c9bc24a08   9 days ago    521MB
nginx                           latest    f2f70adc5d89   10 days ago   142MB
httpd                           2.4       b9bd7e513e0f   10 days ago   144MB
[root@docker-master ~]#

运行容器

[root@docker-master ~]# docker container ls
CONTAINER ID   IMAGE          COMMAND                  CREATED      STATUS      PORTS                                   NAMES
4e45816885ee   nginx:latest   "/docker-entrypoint.…"   4 days ago   Up 2 days   0.0.0.0:8080->80/tcp, :::8080->80/tcp   beautiful_dijkstra
b3bf61aceda1   nginx          "/docker-entrypoint.…"   4 days ago   Up 2 days   0.0.0.0:80->80/tcp, :::80->80/tcp       inspiring_proskuriakova
[root@docker-master ~]#
[root@docker-master ~]# docker run -d --name test-mysql -p 3306:3306 -e MYSQL_ROOT_PASSWORD=P@ssw0rd mysql
796a8a08c682750de56d93a8fcc179e2b3f453fc86d633e8f27e0adfa2fd2764
[root@docker-master ~]# 
[root@docker-master ~]# docker container ls
CONTAINER ID   IMAGE          COMMAND                  CREATED         STATUS         PORTS                                                  NAMES
796a8a08c682   mysql          "docker-entrypoint.s…"   7 seconds ago   Up 6 seconds   0.0.0.0:3306->3306/tcp, :::3306->3306/tcp, 33060/tcp   test-mysql
4e45816885ee   nginx:latest   "/docker-entrypoint.…"   4 days ago      Up 2 days      0.0.0.0:8080->80/tcp, :::8080->80/tcp                  beautiful_dijkstra
b3bf61aceda1   nginx          "/docker-entrypoint.…"   4 days ago      Up 2 days      0.0.0.0:80->80/tcp, :::80->80/tcp                      inspiring_proskuriakova
[root@docker-master ~]#

运行容器-MySQL数据持久化

这是推荐的操作，我这里仅仅测试就用了上面的方法，没有做数据持久化；数据持久化的方法我这里写一下：

[root@docker-master ~]# docker run -d --name test-mysql1 -p 3307:3306 -v /data/test-mysql1/conf.d:/etc/mysql/conf.d -v /data/test-mysql1/data:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=P@ssw0rd mysql
afb3a8bdf7ea9dca585de31d362710b4d33029783fc84caf7f95f9ec57504a7f
[root@docker-master ~]# 
[root@docker-master ~]# docker container ls
CONTAINER ID   IMAGE          COMMAND                  CREATED             STATUS             PORTS                                                  NAMES
afb3a8bdf7ea   mysql          "docker-entrypoint.s…"   8 seconds ago       Up 7 seconds       33060/tcp, 0.0.0.0:3307->3306/tcp, :::3307->3306/tcp   test-mysql1
796a8a08c682   mysql          "docker-entrypoint.s…"   About an hour ago   Up About an hour   0.0.0.0:3306->3306/tcp, :::3306->3306/tcp, 33060/tcp   test-mysql
4e45816885ee   nginx:latest   "/docker-entrypoint.…"   4 days ago          Up 2 days          0.0.0.0:8080->80/tcp, :::8080->80/tcp                  beautiful_dijkstra
b3bf61aceda1   nginx          "/docker-entrypoint.…"   4 days ago          Up 3 days          0.0.0.0:80->80/tcp, :::80->80/tcp                      inspiring_proskuriakova
[root@docker-master ~]#

这里我把端口映射为3307；同时将mysql的配置文件和数据文件挂载为宿主机的目录；这样容器本身的操作（停止/重启/删除）不会影响数据本身。

使用MySQL

使用mysql client访问：

在宿主机或者其他可以访问到容器3306端口的机器上安装mysql-client

[Ubuntu ~]# apt-get install mysql-client
#或者任意可以安装mysql client的主机。
[CentOS ~]#
[CentOS ~]# yum search mysql | grep client
Last metadata expiration check: 2:20:46 ago on Mon 28 Mar 2022 02:06:22 PM CST.
mysql.x86_64 : MySQL client programs and shared libraries
mysql-common.x86_64 : The shared files required for MySQL server and client
mysql-libs.x86_64 : The shared libraries required for MySQL clients
python2-PyMySQL.noarch : Pure-Python MySQL client library
python3-PyMySQL.noarch : Pure-Python MySQL client library
python38-PyMySQL.noarch : Pure-Python MySQL client library
python39-PyMySQL.noarch : Pure-Python MySQL client library
mariadb-java-client.noarch : Connects applications developed in Java to MariaDB and MySQL databases
[CentOS ~]# 
#Centos这个mysql其实包含了server的功能，建议找其他方法例如RPM包安装mysql-client。

下面我们使用mysql命令访问服务器，密码如刚才所示为123456,192.168.95.4为我这台机器的ip, 3306为刚才所示的占用本物理机的端口（不是在docker内部的端口）

mysql -h192.168.11.225 -P3306 -uroot -pP@ssw0rd

访问的结果如下：

mysql> show databases;

+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+

4 rows in set (0.00 sec)

直接通过docker访问

[root@docker-master ~]# docker exec -it test-mysql mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.28 MySQL Community Server - GPL

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

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 |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.01 sec)

mysql> 
mysql>

测试数据的创建

创建数据库

mysql> create DATABASE test_xml;
Query OK, 1 row affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test_xml           |
+--------------------+
5 rows in set (0.00 sec)

mysql> 
mysql>

创建数据表

mysql> use test_xml;
Database changed
mysql> show tables;
Empty set (0.00 sec)

mysql> 
mysql> CREATE TABLE test_table(
    -> xml_id INT NOT NULL AUTO_INCREMENT,
    -> xml_name VARCHAR(100) NOT NULL,
    -> xml_age VARCHAR(40) NOT NULL,
    -> submission_date DATE,
    -> PRIMARY KEY ( xml_id )
    -> )ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected, 1 warning (0.03 sec)

mysql> 
mysql> show tables;
+--------------------+
| Tables_in_test_xml |
+--------------------+
| test_table         |
+--------------------+
1 row in set (0.00 sec)

mysql>

插入测试数据

mysql> select * from test_table;
Empty set (0.01 sec)

mysql> 
mysql> INSERT INTO test_table 
    -> (xml_name, xml_age, submission_date)
    -> VALUES
    -> ("", "18", NOW());
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> 
mysql> INSERT INTO test_table 
    -> (xml_name, xml_age, submission_date)
    -> VALUES
    -> ("Arthur", "18", NOW());
Query OK, 1 row affected, 1 warning (0.01 sec)

mysql> 
mysql> INSERT INTO test_table 
    -> (xml_name, xml_age, submission_date)
    -> VALUES
    -> ("Bill", "18", NOW());
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> 
mysql> select * from test_table;
+--------+----------+---------+-----------------+
| xml_id | xml_name | xml_age | submission_date |
+--------+----------+---------+-----------------+
|      1 |          | 18      | 2022-03-28      |
|      2 | Arthur   | 18      | 2022-03-28      |
|      3 | Bill     | 18      | 2022-03-28      |
+--------+----------+---------+-----------------+
3 rows in set (0.00 sec)

mysql>

mysql数据文件存放位置

mysql> show variables like '%dir%';
+-----------------------------------------+--------------------------------+
| Variable_name                           | Value                          |
+-----------------------------------------+--------------------------------+
| basedir                                 | /usr/                          |
| binlog_direct_non_transactional_updates | OFF                            |
| character_sets_dir                      | /usr/share/mysql-8.0/charsets/ |
| datadir                                 | /var/lib/mysql/                |
| innodb_data_home_dir                    |                                |
| innodb_directories                      |                                |
| innodb_doublewrite_dir                  |                                |
| innodb_log_group_home_dir               | ./                             |
| innodb_max_dirty_pages_pct              | 90.000000                      |
| innodb_max_dirty_pages_pct_lwm          | 10.000000                      |
| innodb_redo_log_archive_dirs            |                                |
| innodb_temp_tablespaces_dir             | ./#innodb_temp/                |
| innodb_tmpdir                           |                                |
| innodb_undo_directory                   | ./                             |
| lc_messages_dir                         | /usr/share/mysql-8.0/          |
| plugin_dir                              | /usr/lib/mysql/plugin/         |
| replica_load_tmpdir                     | /tmp                           |
| slave_load_tmpdir                       | /tmp                           |
| tmpdir                                  | /tmp                           |
+-----------------------------------------+--------------------------------+
19 rows in set (0.00 sec)

mysql>

证明下容器重启mysql数据不会丢失（未做数据持久化）

重启容器

mysql> quit;
Bye
[root@docker-master ~]# 
[root@docker-master ~]# 
[root@docker-master ~]# 
[root@docker-master ~]# 
[root@docker-master ~]# docker container restart test-mysql
test-mysql
[root@docker-master ~]# 
[root@docker-master ~]# 
[root@docker-master ~]# docker exec -it test-mysql mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.28 MySQL Community Server - GPL

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

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> use test_xml;
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> select * from test_table;
+--------+----------+---------+-----------------+
| xml_id | xml_name | xml_age | submission_date |
+--------+----------+---------+-----------------+
|      1 |          | 18      | 2022-03-28      |
|      2 | Arthur   | 18      | 2022-03-28      |
|      3 | Bill     | 18      | 2022-03-28      |
+--------+----------+---------+-----------------+
3 rows in set (0.00 sec)

mysql>

未丢失。

重启docker

[root@docker-master ~]# systemctl restart docker.service 
[root@docker-master ~]# docker container ls
CONTAINER ID   IMAGE     COMMAND   CREATED   STATUS    PORTS     NAMES
[root@docker-master ~]# docker container start test-mysql
test-mysql
[root@docker-master ~]# docker container ls
CONTAINER ID   IMAGE     COMMAND                  CREATED       STATUS         PORTS                                                  NAMES
796a8a08c682   mysql     "docker-entrypoint.s…"   3 hours ago   Up 3 seconds   0.0.0.0:3306->3306/tcp, :::3306->3306/tcp, 33060/tcp   test-mysql
[root@docker-master ~]# 
[root@docker-master ~]# docker exec -it test-mysql mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.28 MySQL Community Server - GPL

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

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> use test_xml;
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> select * from test_table;
+--------+----------+---------+-----------------+
| xml_id | xml_name | xml_age | submission_date |
+--------+----------+---------+-----------------+
|      1 |          | 18      | 2022-03-28      |
|      2 | Arthur   | 18      | 2022-03-28      |
|      3 | Bill     | 18      | 2022-03-28      |
+--------+----------+---------+-----------------+
3 rows in set (0.00 sec)

mysql>

未丢失。

但当容器删除后，容器内的数据就会和容器一起被删除掉！！！所以，强烈建议对容器中的重要数据做数据持久化。

备份MySQL数据

方法一 进入容器，利用mysqldump

备份

[root@docker-master ~]# docker exec -it test-mysql /bin/bash
root@796a8a08c682:/# mysqldump -uroot -pP@ssw0rd --all-databases > bak_`date +%F`.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
root@796a8a08c682:/#
root@796a8a08c682:/# pwd
/
root@796a8a08c682:/# ls -l
total 3856
-rw-r--r--   1 root root 3936809 Mar 28 06:51 bak_2022-03-28.sql
drwxr-xr-x   2 root root    4096 Mar 16 00:00 bin
drwxr-xr-x   2 root root       6 Oct  3 09:00 boot
drwxr-xr-x   5 root root     340 Mar 28 06:45 dev
drwxr-xr-x   2 root root       6 Mar 18 08:05 docker-entrypoint-initdb.d
lrwxrwxrwx   1 root root      34 Mar 18 08:06 entrypoint.sh -> usr/local/bin/docker-entrypoint.sh
drwxr-xr-x   1 root root      66 Mar 28 04:14 etc
drwxr-xr-x   2 root root       6 Oct  3 09:00 home
drwxr-xr-x   1 root root      30 Mar 18 08:05 lib
drwxr-xr-x   2 root root      34 Mar 16 00:00 lib64
drwxr-xr-x   2 root root       6 Mar 16 00:00 media
drwxr-xr-x   2 root root       6 Mar 16 00:00 mnt
drwxr-xr-x   2 root root       6 Mar 16 00:00 opt
dr-xr-xr-x 283 root root       0 Mar 28 06:45 proc
drwx------   1 root root      28 Mar 28 06:49 root
drwxr-xr-x   1 root root      20 Mar 18 08:06 run
drwxr-xr-x   2 root root    4096 Mar 16 00:00 sbin
drwxr-xr-x   2 root root       6 Mar 16 00:00 srv
dr-xr-xr-x  13 root root       0 Mar 28 06:45 sys
drwxrwxrwt   1 root root       6 Mar 28 06:45 tmp
drwxr-xr-x   1 root root      19 Mar 16 00:00 usr
drwxr-xr-x   1 root root      41 Mar 16 00:00 var
root@796a8a08c682:/# 
root@796a8a08c682:/# exit
exit
[root@docker-master ~]#

数据copy到宿主机（可选操作）

复制数据的方法有和很多，我这里选择要求最少的一种方法：

[root@docker-master ~]# docker cp test-mysql:/bak_2022-03-28.sql /data/test-mysql/bak/
invalid output path: directory "/data/test-mysql" does not exist
[root@docker-master ~]# mkdir -p /data/test-mysql/bak/
[root@docker-master ~]# docker cp test-mysql:/bak_2022-03-28.sql /data/test-mysql/bak/
[root@docker-master ~]# ll /data/test-mysql/bak/
total 3848
-rw-r--r-- 1 root root 3936809 Mar 28 14:51 bak_2022-03-28.sql
[root@docker-master ~]#

模拟插入新的测试数据并修改原数据

#访问
[root@docker-master ~]# docker exec -it test-mysql mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 8.0.28 MySQL Community Server - GPL

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

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> use test_xml;
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> INSERT INTO test_table 
    -> (xml_name, xml_age, submission_date)
    -> VALUES
    -> ("Tom", "18", NOW());
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> 
#修改
mysql> UPDATE test_table SET xml_name='XiaMingliang' WHERE xml_id=1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> 
mysql> select * from test_table;
+--------+--------------+---------+-----------------+
| xml_id | xml_name     | xml_age | submission_date |
+--------+--------------+---------+-----------------+
|      1 | XiaMingliang | 18      | 2022-03-28      |
|      2 | Arthur       | 18      | 2022-03-28      |
|      3 | Bill         | 18      | 2022-03-28      |
|      4 | Tom          | 18      | 2022-03-28      |
+--------+--------------+---------+-----------------+
4 rows in set (0.00 sec)

mysql>

还原

方法一 mysql
#进入容器
[root@docker-master ~]# docker exec -it test-mysql /bin/bash
#还原数据库
root@796a8a08c682:/# mysql -uroot -pP@ssw0rd < /bak_2022-03-28.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
root@796a8a08c682:/# 
root@796a8a08c682:/# 
#进入数据库验证数据
root@796a8a08c682:/# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 13
Server version: 8.0.28 MySQL Community Server - GPL

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

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> use test_xml;
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> select * from test_table;
+--------+----------+---------+-----------------+
| xml_id | xml_name | xml_age | submission_date |
+--------+----------+---------+-----------------+
|      1 |          | 18      | 2022-03-28      |
|      2 | Arthur   | 18      | 2022-03-28      |
|      3 | Bill     | 18      | 2022-03-28      |
+--------+----------+---------+-----------------+
3 rows in set (0.00 sec)

mysql> 
mysql>
方法二 source
#进入容器
[root@docker-master ~]# docker exec -it test-mysql /bin/bash
#进入数据库
root@796a8a08c682:/# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 14
Server version: 8.0.28 MySQL Community Server - GPL

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

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> source /bak_2022-03-28.sql;
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)
……
mysql>
#验证数据
mysql> use test_xml;
Database changed
mysql> select * from test_table;
+--------+----------+---------+-----------------+
| xml_id | xml_name | xml_age | submission_date |
+--------+----------+---------+-----------------+
|      1 |          | 18      | 2022-03-28      |
|      2 | Arthur   | 18      | 2022-03-28      |
|      3 | Bill     | 18      | 2022-03-28      |
+--------+----------+---------+-----------------+
3 rows in set (0.00 sec)

mysql>

方法二 Docker执行mysqldump

备份

1)推荐
#先清一下数据
[root@docker-master ~]# ll /data/test-mysql/bak/
total 3848
-rw-r--r-- 1 root root 3936809 Mar 28 14:51 bak_2022-03-28.sql
[root@docker-master ~]# rm /data/test-mysql/bak/bak_2022-03-28.sql 
rm: remove regular file '/data/test-mysql/bak/bak_2022-03-28.sql'? y
[root@docker-master ~]# ll /data/test-mysql/bak/
total 0
[root@docker-master ~]#
#备份数据
[root@docker-master ~]# docker exec test-mysql sh -c 'exec mysqldump -uroot -pP@ssw0rd --all-databases' > /data/test-mysql/bak/bak_`date +%F`.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@docker-master ~]# ll /data/test-mysql/bak/
total 3848
-rw-r--r-- 1 root root 3937360 Mar 28 15:33 bak_2022-03-28.sql
[root@docker-master ~]#
2)不推荐（有些版本测试还原未成功，但是我的Ver 8.0.2是可以正常还原的）
root@796a8a08c682:/# mysql -V
mysql  Ver 8.0.28 for Linux on x86_64 (MySQL Community Server - GPL)
root@796a8a08c682:/#
[root@docker-master ~]# docker exec -it test-mysql mysqldump -uroot -pP@ssw0rd --all-databases > /data/test-mysql/bak/bak2_`date +%F`.sql
[root@docker-master ~]# 
[root@docker-master ~]# ll /data/test-mysql/bak/
total 7696
-rw-r--r-- 1 root root 3937360 Mar 28 15:33 bak_2022-03-28.sql
-rw-r--r-- 1 root root 3938524 Mar 28 15:35 bak2_2022-03-28.sql
[root@docker-master ~]#

数据copy到宿主机（可选操作）

<同方法一>

#清理下容器中的历史测试备份数据
[root@docker-master ~]# docker exec -it test-mysql /bin/bash
root@796a8a08c682:/# ls -l
total 3856
-rw-r--r--   1 root root 3936809 Mar 28 06:51 bak_2022-03-28.sql
drwxr-xr-x   2 root root    4096 Mar 16 00:00 bin
drwxr-xr-x   2 root root       6 Oct  3 09:00 boot
drwxr-xr-x   5 root root     340 Mar 28 06:45 dev
drwxr-xr-x   2 root root       6 Mar 18 08:05 docker-entrypoint-initdb.d
lrwxrwxrwx   1 root root      34 Mar 18 08:06 entrypoint.sh -> usr/local/bin/docker-entrypoint.sh
drwxr-xr-x   1 root root      66 Mar 28 04:14 etc
drwxr-xr-x   2 root root       6 Oct  3 09:00 home
drwxr-xr-x   1 root root      30 Mar 18 08:05 lib
drwxr-xr-x   2 root root      34 Mar 16 00:00 lib64
drwxr-xr-x   2 root root       6 Mar 16 00:00 media
drwxr-xr-x   2 root root       6 Mar 16 00:00 mnt
drwxr-xr-x   2 root root       6 Mar 16 00:00 opt
dr-xr-xr-x 283 root root       0 Mar 28 06:45 proc
drwx------   1 root root      49 Mar 28 07:24 root
drwxr-xr-x   1 root root      20 Mar 18 08:06 run
drwxr-xr-x   2 root root    4096 Mar 16 00:00 sbin
drwxr-xr-x   2 root root       6 Mar 16 00:00 srv
dr-xr-xr-x  13 root root       0 Mar 28 06:45 sys
drwxrwxrwt   1 root root       6 Mar 28 07:18 tmp
drwxr-xr-x   1 root root      19 Mar 16 00:00 usr
drwxr-xr-x   1 root root      41 Mar 16 00:00 var
root@796a8a08c682:/# rm /bak_2022-03-28.sql   
root@796a8a08c682:/# 
root@796a8a08c682:/# ls -l
total 8
drwxr-xr-x   2 root root 4096 Mar 16 00:00 bin
drwxr-xr-x   2 root root    6 Oct  3 09:00 boot
drwxr-xr-x   5 root root  340 Mar 28 06:45 dev
drwxr-xr-x   2 root root    6 Mar 18 08:05 docker-entrypoint-initdb.d
lrwxrwxrwx   1 root root   34 Mar 18 08:06 entrypoint.sh -> usr/local/bin/docker-entrypoint.sh
drwxr-xr-x   1 root root   66 Mar 28 04:14 etc
drwxr-xr-x   2 root root    6 Oct  3 09:00 home
drwxr-xr-x   1 root root   30 Mar 18 08:05 lib
drwxr-xr-x   2 root root   34 Mar 16 00:00 lib64
drwxr-xr-x   2 root root    6 Mar 16 00:00 media
drwxr-xr-x   2 root root    6 Mar 16 00:00 mnt
drwxr-xr-x   2 root root    6 Mar 16 00:00 opt
dr-xr-xr-x 283 root root    0 Mar 28 06:45 proc
drwx------   1 root root   49 Mar 28 07:24 root
drwxr-xr-x   1 root root   20 Mar 18 08:06 run
drwxr-xr-x   2 root root 4096 Mar 16 00:00 sbin
drwxr-xr-x   2 root root    6 Mar 16 00:00 srv
dr-xr-xr-x  13 root root    0 Mar 28 06:45 sys
drwxrwxrwt   1 root root    6 Mar 28 07:18 tmp
drwxr-xr-x   1 root root   19 Mar 16 00:00 usr
drwxr-xr-x   1 root root   41 Mar 16 00:00 var
root@796a8a08c682:/#
[root@docker-master ~]# docker cp /data/test-mysql/bak/ test-mysql:/
[root@docker-master ~]# 
#一种不进入容器查看容器中数据的方法
[root@docker-master ~]# docker container inspect test-mysql | grep "Source"
                "Source": "/var/lib/docker/volumes/1eea7bc97603f0d2fee4f54bf8c0d51bcb08c393e86c2e4d6595e84e59e4fc9e/_data",
[root@docker-master ~]#
#当前运行的容器中的数据实际上存储宿主机对应的"MergedDir"目录下
[root@docker-master ~]# docker container inspect test-mysql | grep "MergedDir"
                "MergedDir": "/var/lib/docker/overlay2/a55cce612933d93ab494b1dba65ce04d013b6cfaa01151a82fb929deeacb6520/merged",
[root@docker-master ~]# 
[root@docker-master ~]# ll /var/lib/docker/overlay2/a55cce612933d93ab494b1dba65ce04d013b6cfaa01151a82fb929deeacb6520/merged
total 8
drwxr-xr-x 2 root root   59 Mar 28 15:35 bak
drwxr-xr-x 2 root root 4096 Mar 16 08:00 bin
drwxr-xr-x 2 root root    6 Oct  3 17:00 boot
drwxr-xr-x 1 root root   43 Mar 28 12:14 dev
drwxr-xr-x 2 root root    6 Mar 18 16:05 docker-entrypoint-initdb.d
lrwxrwxrwx 1 root root   34 Mar 18 16:06 entrypoint.sh -> usr/local/bin/docker-entrypoint.sh
drwxr-xr-x 1 root root   66 Mar 28 12:14 etc
drwxr-xr-x 2 root root    6 Oct  3 17:00 home
drwxr-xr-x 1 root root   30 Mar 18 16:05 lib
drwxr-xr-x 2 root root   34 Mar 16 08:00 lib64
drwxr-xr-x 2 root root    6 Mar 16 08:00 media
drwxr-xr-x 2 root root    6 Mar 16 08:00 mnt
drwxr-xr-x 2 root root    6 Mar 16 08:00 opt
drwxr-xr-x 2 root root    6 Oct  3 17:00 proc
drwx------ 1 root root   49 Mar 28 15:24 root
drwxr-xr-x 1 root root   20 Mar 18 16:06 run
drwxr-xr-x 2 root root 4096 Mar 16 08:00 sbin
drwxr-xr-x 2 root root    6 Mar 16 08:00 srv
drwxr-xr-x 2 root root    6 Oct  3 17:00 sys
drwxrwxrwt 1 root root    6 Mar 28 15:18 tmp
drwxr-xr-x 1 root root   19 Mar 16 08:00 usr
drwxr-xr-x 1 root root   41 Mar 16 08:00 var
[root@docker-master ~]# ll /var/lib/docker/overlay2/a55cce612933d93ab494b1dba65ce04d013b6cfaa01151a82fb929deeacb6520/merged/bak
total 7696
-rw-r--r-- 1 root root 3937360 Mar 28 15:33 bak_2022-03-28.sql
-rw-r--r-- 1 root root 3938524 Mar 28 15:35 bak2_2022-03-28.sql
[root@docker-master ~]#

模拟插入新的测试数据并修改原数据

<同方法一>

还原

<同方法一>

【Docker】# MySQL从安装到备份还原_docker

喜欢的朋友请三连哦，谢谢~


标签：Mar,备份,MySQL,28,mysql,xr,docker,root,Docker
From： https://blog.51cto.com/mlxia/7451828

相关文章

  • 高性能MySQL实战（三）：性能优化 | 京东物流技术团队
    这篇主要介绍对慢SQL优化的一些手段，而在讲解具体的优化措施之前，我想先对EXPLAIN进行介绍，它是我们在分析查询时必要的操作，理解了它输出结果的内容更有利于我们优化SQL。为了方便大家的阅读，在下文中规定类似key1的表示二级索引，key_part1表示联合索引的第一部分，unique_key1......
  • 在 Docker 容器中部署 Ubuntu 系统，并使其编码为 UTF-8 并能使用 apt、rpm、vi 等命令
    运行一个新的Docker容器并以交互模式运行dockerrun-itd--namemy-ubuntuubuntu:20.04/bin/bash在容器中更新apt包管理器索引和安装基本工具aptupdate-yaptinstall-yapt-utilswgetcurlvimlocales在容器中设置UTF-8编码在文件中找到en_US.UTF-8UTF-8......
  • MySQL SQL语法大全
    SQL语法基础知识总结|JavaGuide(Java面试+学习指南)基本概念数据库术语数据库（database）-保存有组织的数据的容器（通常是一个文件或一组文件）。数据表（table）-某种特定类型数据的结构化清单。模式（schema）-关于数据库和表的布局及特性的信息。模式定义了数据在表中如何存储......
  • 通过SQL Server操作MySQL（下）—— 操作MySQL
    查询MySQL语句select*fromopenquery(TestMySQL,'select*fromtest.TestTable');向MySQL表中插入数据insertintoopenquery(TestMySQL,'select*fromtest.TestTable')select1,'TestName';删除MySQL表中的数据deletefromopenquery(TestMyS......
  • 【SQL Server】通过SQL Server操作MySQL（上）——连接MySQL服务器
    http://t.zoukankan.com/xd502djj-p-3104191.html 在多种数据库环境下，经常会遇见不同数据库之间转换数据或进行互相操作的情况。以下简要介绍下用SQLServer操作MySQL的步骤和方法。安装MySQL驱动想要在SQLServer中操作MySQL，首先要在SQLServer所在的服务器上安装MySQL的驱......
  • MySQL字符集详解——utf8mb4 & utf8区别？
    字符集详解|JavaGuide(Java面试+学习指南)MySQL字符编码集中有两套UTF-8编码实现：utf8和**utf8mb4**。如果使用utf8的话，存储emoji符号和一些比较复杂的汉字、繁体字就会出错。何为字符集？字符是各种文字和符号的统称，包括各个国家文字、标点符号、表情、数字等等。......
  • Linux操作系统下安装与配置MySQL
    一、安装与配置[root@mysql1~]#cat/etc/redhat-releaseCentOSLinuxrelease7.9.2009(Core)https://downloads.mysql.com/archives/community/#下载地址[root@mysql1tools]#rpm-ivhmysql-community-client-5.7.18-1.el7.x86_64.rpm警告：mysql-community-client-5.......
  • MySQL异常断电恢复脚本
    #!/bin/bashuser="root"host="127.0.0.1"passwd="password"conf_file="/etc/mysql/mysql.conf.d/mysqld.cnf"backup_file="/home/ehigh/DB.sql"data_dir="/var/lib/mysql"if[$(id-u)-ne0];then......
  • docker容器
        ......
  • MySQL-分区表和分区介绍
    一、MySQL分区简介1、数据库分区数据库分区是一种物理数据库设计技术。虽然分区技术可以实现很多效果，但其主要目的是为了在特定的SQL操作中减少数据读写的总量以缩减sql语句的响应时间，同时对于应用来说分区完全是透明的。MYSQL的分区主要有两种形式：水平分区和垂直分区①、水平......

赞助商

阅读排行

网站主页关于我们联系我们网站地图

本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。

Copyright © 2020-2023 IPS99 版权所有 IPS99