MySQL–数据备份及恢复

数据备份

数据备份语法：(不能在数据库中执行，需在命令行执行)

mysqldump -h服务地址 -P端口号 -u用户名 -p密码 数据库名 > 备份文件.sql

常见示例如下：

完整备份

同时备份表结构及表中数据，恢复时可以自动创建数据表或数据库

1. 备份整个数据库，恢复时无需创建数据库

   mysqldump -uroot -p123 --databases db1 > D:\data\db.sql

2. 备份 db1 库下所有数据**(同时备份表结构及数据)**，恢复时需要手动建库并在库中恢复

   mysqldump -uroot -p123 db1 > D:\data\db1_backup.sql

   备份的是大型数据库或表，可以使用 --single-transaction 参数来保证备份过程中的一致性：

   mysqldump -uroot -p123 --single-transaction db1 > db1_backup.sql

   使用 --quick 参数来减少内存消耗，尤其在备份大量数据时：

   mysqldump -uroot -p123 --single-transaction --quick db1 > db1_backup.sql

3. 多库备份

   mysqldump -uroot -p123 --databases db1 db2 mysql db3 > D:\data\db1_db2_mysql_db3.sql

4. 备份所有库

   mysqldump -uroot -p123 --all-databases > all.sql

部分备份

1. 备份 db1 库下指定表**(同时备份表结构及数据)**，恢复时需要手动建库在库中恢复

   mysqldump -uroot -p123 db1 table1 table2 > D:\data\test.sql

2. 只备份表结构

   -d 或 –no-data：只备份数据库中表结构，不导出数据表中的任何数据

   mysqldump -uroot -p123 --single-transaction --no-data db1 > db1_structure.sql

3. 只备份表数据

   -t 或 –no-create-info：只导出数据，而不添加 CREATE TABLE 语句

   mysqldump -uroot -p123 --single-transaction -t db1 > db1_data.sql

4. 备份数据库时忽略部分表

   –ignore-table：不导出指定表。指定忽略多个表时，需要重复多次，每次一个表。每个表必须同时指定数据库和表名

   mysqldump -uroot -p123 --ignore-table=db1.table1 --ignore-table=db1.table2 --single-transaction -t db1 > db1_data.sql

数据恢复

恢复数据的两种执行方式：

1. 命令行执行：

   mysql -uroot -p123 db1 < db1.sql

2. 非命令行执行(进数据库执行)

   mysql > source 备份的数据.sql;
   
   # 示例：
   mysql > source D:\data\db1_db2_mysql_db3.sql;

其它补充：

事务示例：

begin;  # 开启事务
select * from emp where id = 1 for update;  # 查询id值，for update添加行锁；
update emp set salary=10000 where id = 1; # 完成更新
commit; # 提交事务

禁用外键约束： 某些场景导入数据时如果有外键约束，那么在执行恢复或导入操作之前，首先禁用外键约束：

mysql > SET foreign_key_checks = 0;

恢复数据： 恢复数据后，再启用外键约束：

mysql > SET foreign_key_checks = 1;