主要流程

1. 先建库建表 插入测试数据
2. 以物理方式备份数据
--数据备份完毕

3. 创建新的mysql-server 然后建库建表
移除表空间 
4.停止mysql服务 将备份的数据还原 
重新载入表空间
--数据还原完毕

ubuntu 中通过docker 运行mysql

测试过程

数据准备

两个sql脚本一个是建表一个是测试数据

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22


cat > /root/mytable.sql << \EOF
CREATE DATABASE `myshop` CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_general_ci';
use myshop;

-- table

DROP TABLE IF EXISTS `person`;
CREATE TABLE `person`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `age` int(4) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Compact;

DROP TABLE IF EXISTS `product`;
CREATE TABLE `product`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `price` bigint(10) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Compact;
EOF

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14


cat > /root/oldData.sql << \EOF
use myshop;
-- data

INSERT INTO `person` VALUES (1, 'tom110', 11);
INSERT INTO `person` VALUES (2, 'tom220', 22);
INSERT INTO `person` VALUES (3, 'tom330', 33);


INSERT INTO `product` VALUES (1, 'book-a', 61);
INSERT INTO `product` VALUES (2, 'book-b', 62);
INSERT INTO `product` VALUES (3, 'book-c', 63);
EOF

创建待备份数据并备份

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29


root@nu18-vm:/# docker run --name online -d -p 6606:3306 \

> -e MYSQL_ROOT_PASSWORD=123 \

> mysql:5.6.34
e1290b4a2256c801752e421954cd033c200ca7215f589343b88db3397b6edde2
root@nu18-vm:/# ## ------ 稍等一会，等待mysql服务初始化
root@nu18-vm:/# ## ------ 依次执行脚本 建库建表 导入测试数据
root@nu18-vm:/# docker exec -i online sh -c 'exec mysql -uroot -p123' < /root/mytable.sql
Warning: Using a password on the command line interface can be insecure.
root@nu18-vm:/# docker exec -i online sh -c 'exec mysql -uroot -p123' < /root/oldData.sql
Warning: Using a password on the command line interface can be insecure.

root@nu18-vm:/# ## ------ 查看状态
root@nu18-vm:/# docker exec -it online sh -c 'exec mysql -uroot -p123 myshop -e "show tables;"'
Warning: Using a password on the command line interface can be insecure.
+------------------+
| Tables_in_myshop |
+------------------+
| person           |
| product          |
+------------------+
root@nu18-vm:/# docker exec -i online ls -al /var/lib/mysql/myshop
total 228
drwx------ 2 mysql mysql  4096 Oct 15 15:55 .
drwxr-xr-x 5 mysql mysql  4096 Oct 15 15:55 ..
-rw-rw---- 1 mysql mysql    67 Oct 15 15:55 db.opt
-rw-rw---- 1 mysql mysql  8614 Oct 15 15:55 person.frm
-rw-rw---- 1 mysql mysql 98304 Oct 15 15:55 person.ibd
-rw-rw---- 1 mysql mysql  8618 Oct 15 15:55 product.frm
-rw-rw---- 1 mysql mysql 98304 Oct 15 15:55 product.ibd

备份数据

1
2


root@nu18-vm:/# mkdir /root/test-backup-mysql
root@nu18-vm:/# docker cp online:/var/lib/mysql/myshop /root/test-backup-mysql/

创建新数据库并还原数据

创建数据库

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14


root@nu18-vm:/# docker run --name offline -d -p 8806:3306 \

> -e MYSQL_ROOT_PASSWORD=456 \

> mysql:5.6.34
dd6b83d9504fe9e247ca67cf495ae38859437cf58277c6640e0edda9dc2050d8
root@nu18-vm:/# docker exec -i offline sh -c 'exec mysql -uroot -p456' < /root/mytable.sql
Warning: Using a password on the command line interface can be insecure.
root@nu18-vm:/# docker exec -it offline sh -c 'exec mysql -uroot -p456 myshop -e "show tables;"'
Warning: Using a password on the command line interface can be insecure.
+------------------+
| Tables_in_myshop |
+------------------+
| person           |
| product          |
+------------------+

先移除表空间，再将备份的ibd文件复制进来

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13


root@nu18-vm:/# docker exec -it offline sh -c \

> 'exec mysql -uroot -p456 myshop -e "alter table person discard tablespace; alter table product discard tablespace;"'
Warning: Using a password on the command line interface can be insecure.
root@nu18-vm:/# docker exec -i offline ls -al /var/lib/mysql/myshop
total 36
drwx------ 2 mysql mysql 4096 Oct 15 16:15 .
drwxr-xr-x 5 mysql mysql 4096 Oct 15 16:14 ..
-rw-rw---- 1 mysql mysql   67 Oct 15 16:14 db.opt
-rw-rw---- 1 mysql mysql 8614 Oct 15 16:14 person.frm
-rw-rw---- 1 mysql mysql 8618 Oct 15 16:14 product.frm
root@nu18-vm:/# cd /root/test-backup-mysql/ && mkdir myshop-ibd && cp myshop/*.ibd myshop-ibd/
root@nu18-vm:~/test-backup-mysql# docker cp /root/test-backup-mysql/myshop-ibd/. offline:/var/lib/mysql/myshop

重启mysql服务重新加载表空间

1
2
3
4
5


root@nu18-vm:~/test-backup-mysql# docker restart offline
offline
root@nu18-vm:~/test-backup-mysql# docker exec -it offline sh -c \

> 'exec mysql -uroot -p456 myshop -e "alter table person import tablespace; alter table product import tablespace;"'
Warning: Using a password on the command line interface can be insecure.

检查还原情况

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18


root@nu18-vm:~/test-backup-mysql# docker exec -it offline sh -c 'exec mysql -uroot -p456 myshop -e "select * from person;"'
Warning: Using a password on the command line interface can be insecure.
+----+--------+------+
| id | name   | age  |
+----+--------+------+
|  1 | tom110 |   11 |
|  2 | tom220 |   22 |
|  3 | tom330 |   33 |
+----+--------+------+
root@nu18-vm:~/test-backup-mysql# docker exec -it offline sh -c 'exec mysql -uroot -p456 myshop -e "select * from product;"'
Warning: Using a password on the command line interface can be insecure.
+----+--------+-------+
| id | name   | price |
+----+--------+-------+
|  1 | book-a |    61 |
|  2 | book-b |    62 |
|  3 | book-c |    63 |
+----+--------+-------+

清理测试环境

1
2
3
4


root@nu18-vm:~/test-backup-mysql# docker rm -f online offline
online
offline
root@nu18-vm:~/test-backup-mysql# rm -rf  /root/test-backup-mysql

测试脚本整理

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40


#老mysql
docker run --name online -d -p 6606:3306 \

-e MYSQL_ROOT_PASSWORD=123 \

mysql:5.6.34

docker exec -i online sh -c 'exec mysql -uroot -p123' < /root/mytable.sql
docker exec -i online sh -c 'exec mysql -uroot -p123' < /root/oldData.sql

docker exec -it online sh -c 'exec mysql -uroot -p123 myshop -e "show tables;"' 
docker exec -i online ls -al /var/lib/mysql/myshop

mkdir /root/test-backup-mysql
docker cp online:/var/lib/mysql/myshop /root/test-backup-mysql/

#新mysql
docker run --name offline -d -p 8806:3306 \

-e MYSQL_ROOT_PASSWORD=456 \

mysql:5.6.34

docker exec -i offline sh -c 'exec mysql -uroot -p456' < /root/mytable.sql
docker exec -i offline sh -c 'exec mysql -uroot -p456 myshop -e "show tables;"' 
docker exec -it offline sh -c \

'exec mysql -uroot -p456 myshop -e "alter table person discard tablespace; alter table product discard tablespace;"' 

docker exec -i offline ls -al /var/lib/mysql/myshop

#还原数据
cd /root/test-backup-mysql/ && mkdir myshop-ibd && cp myshop/*.ibd myshop-ibd/
docker cp /root/test-backup-mysql/myshop-ibd/. offline:/var/lib/mysql/myshop 

docker restart offline

docker exec -it offline sh -c \

'exec mysql -uroot -p456 myshop -e "alter table person import tablespace; alter table product import tablespace;"' 

#查看还原情况
docker exec -it offline sh -c 'exec mysql -uroot -p456 myshop -e "select * from person;"' 
docker exec -it offline sh -c 'exec mysql -uroot -p456 myshop -e "select * from product;"' 
docker rm -f online offline
rm -rf  /root/test-backup-mysql

mysql物理备份与还原示例

文章目录

主要流程

测试过程

数据准备

创建待备份数据并备份

创建新数据库并还原数据

清理测试环境

测试脚本整理