MySQL数据库备份与恢复知识点

一、数据库备份

1.语法

mysqldump 将数据库中的数据导出，并以sql语句的形式存储，称为逻辑备份。
当数据库大于30G时，建议使用Xtrabackup物理的方式备份。

mysqldump -u用户名 -p密码 参数 数据库名 >filename.sql
mysqldump-u用户名 -p密码 参数 数据库名|gzip >filename.sql.gz

2.检查备份结果

egrep -v "#| \* | -- | ^$" filename.sql

3.参数（-B）

会在备份的数据库中增加建库以及use库的语句，可以接多个库名，同时备份多个库。

mysqldump -u用户名 -p密码 -B test test2|gzip >test.sql
diff mysql.sql mysql2.sql # 差异对比

4.备份单个表

mysqldump -u用户名 -p密码 数据库名 表名>filename.sql
mysqldump -u用户名 -p密码 数据库名 表名1 表名2>filename.sql

5.参数-d/-t/-T

参数（-d）备份数据库表结构，不含数据（sql语句形式）。
参数（-t）备份数据，不备份结构（sql语句形式）。
参数（-T）将表和结构分离成不同的文件，数据时纯文本，表结构是sql语句。

6.刷新binlog文件参数（-F）

/etc/my.conf中加入log_bin参数

7.记录binlog位置的特殊参数（–master-data）

--master-data=1 #备份结果为可执行change master的语句
--master-data=2 #备份结果为注释的--change master的语句
mysqldump -u用户名 -p密码 --master-data=1 |gzip >test.sql

8.锁定所有表备份（-x）

mysqldump备份时停止数据写入

9.innodb表特有的备份参数（–single-transaction）

10.利用SQL语句对表数据进行导出

select * from 表名 into outfile ’file_name‘ export_options

11.导入表数据

load data infile 'file_name' into table 表名 import option

二、MySQL数据库恢复

1.source恢复数据库

source db_name.sql

2.mysql命令恢复

mysql -u用户名 -p密码 <db_name.sql