「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