「MySQL」数据库备份和还原 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)

数据已经恢复到备份前模样。
【「MySQL」数据库备份和还原】如果需要按照表名进行过滤备份可以参考以下语句