「MySQL」数据库备份和还原

作者：threedayman

来源： 恒生LIGHT云社区

备份还原使用到的命令

mysqldump、mysql

关于mysqldump命令更多内容 详见 https://dev.mysql.com/doc/refman/5.7/en/mysqldump.html

准备工作

创建两张表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