作者:threedayman
来源:恒生LIGHT云社区
备份复原使用到的命令
mysqldump、mysql
关于mysqldump命令更多内容 详见 dev.mysql.com/doc/refman/…
准备工作
创立两张表user、his_user
CREATE TABLE `user` (
`id` bigint NOT NULL AUTO_INCREMENT COMMENT 'id',
`name` varchar(100) NOT NULL COMMENT '名字',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='用户表';
CREATE TABLE `his_user` (
`id` bigint NOT NULL AUTO_INCREMENT COMMENT 'id',
`name` varchar(100) NOT NULL COMMENT '名字',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='历史用户表';
刺进数据
INSERT INTO user(name) VALUES('three');
INSERT INTO his_user(name) VALUES('wang');
mysql> select * from user;
+----+-------+
| id | name |
+----+-------+
| 1 | three |
+----+-------+
1 row in set (0.01 sec)
mysql> select * from his_user;
+----+------+
| id | name |
+----+------+
| 1 | wang |
+----+------+
1 row in set (0.00 sec)
备份
全库备份
mysqldump -uroot -p123456 datax >dataxAll.sql
刺进数据
INSERT INTO user(name) VALUES('four');
INSERT INTO his_user(name) VALUES('li');
mysql> select * from user;
+----+-------+
| id | name |
+----+-------+
| 1 | three |
| 2 | four |
+----+-------+
2 rows in set (0.00 sec)
mysql> select * from his_user;
+----+------+
| id | name |
+----+------+
| 1 | wang |
| 3 | li |
+----+------+
2 rows in set (0.00 sec)
复原
康复数据
mysql -uroot -p123456 datax < dataxAll.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
查看表数据
mysql> select * from user;
+----+-------+
| id | name |
+----+-------+
| 1 | three |
+----+-------+
1 row in set (0.00 sec)
mysql> select * from his_user;
+----+------+
| id | name |
+----+------+
| 1 | wang |
+----+------+
1 row in set (0.00 sec)
数据现已康复到备份前模样。
如果需求按照表名进行过滤备份可以参考以下句子
mysqldump -uroot -p123456 datax $(mysql -N -uroot -p123456 -e "show tables from datax like 'tc%'") >t.sql