主要流程
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
|
文章作者
duansheli
上次更新
2019-12-25
(325c7b3)