mysql的备份和恢复（全量备份和增量备份）

如果对运维课程感兴趣，可以在b站上、csdn或微信视频号 上搜索我的账号： 运维实战课程，可以关注我，学习更多免费的运维实战技术视频

mysql的备份和恢复（全量备份和增量备份）

一、mysql全量备份和恢复

1.mysqldump的工作原理（全量备份）

1)mysqldump命令备份数据库的过程，实际就是把数据从mysql库里以逻辑的sql语句的形式直接输出或者生成备份文件的过程。

2)mysqldump命令恢复数据的过程，实际就是把备份文件的sql语句在数据库里重新执行一下的过程。

2.mysql的全量备份各类备份命令

1)mysql备份单个数据库及其所有的表和数据：（注意：备份导出的是乱码没事，恢复后会正常）

格式1:#mysqldump -u用户名 -p密码 数据库名 > /路径/…/备份文件名.sql              #无-B参数，恢复时需指定库名 ,要事先有该库

格式2:#mysqldump -u用户名 -p密码  --default-character-set=原数据库字符集 数据库名 > /路径/…/备份文件名.sql   

#指定字符集导出，无-B参数，恢复时需指定库名

格式3: #mysqldump -u用户名 -p密码 -B 数据库名 > /路径/…/备份文件名.sql          #有-B参数，恢复时不需指定库名，库无需事先存在

格式4:#mysqldump -u用户名 -p密码 -B 数据库名|gzip > /路径/…/备份文件名.sql.gz   #有-B参数，恢复不需指定库(压缩备份)，库无需事先存在

2)mysql备份多个数据库及其所有的表和数据

格式1:#mysqldump -u用户名 -p密码 -B 库名1 库名2 > /路径/…/备份文件名.sql      #有-B参数，恢复时不需指定库名，库无需事先存在

格式2:#mysqldump -u用户名 -p密码 -B 库名1  库名2|gzip > /路径/…/备份文件名.sql.gz #有-B参数，恢复不需指定库(压缩备份),库无需事先存在

3)mysql备份单个表及数据（备份表不能加-B参数）

格式： #mysqldump -u用户名 -p密码 库名 表名 > /路径/…/备份文件名.sql              #无-B参数，恢复时需指定库名 ,要事先有该库

4)mysql备份多个表及数据（备份表不能加-B参数）

格式： #mysqldump -u用户名 -p密码 库名 表名1 表名2 … > /路径/…/备份文件名.sql   #无-B参数，恢复时需指定库名 ,要事先有该库

5)mysql备份数据库的所有库和各库下所有数据（-A）

格式: #mysqldump -u用户名 -p密码 -A -B --events  > /路径/…/备份文件名.sql        #有-B参数，恢复时不需指定库名,库无需事先存在

6)mysql备份时切换刷新bin-log日志（以备份所有数据库及数据为例，备份之后生成新的bin-log日志文件，参数：-A和-F）

格式: #mysqldump -u用户名 -p密码 -A -B -F --events  > /路径/…/备份文件名.sql   #有-B参数，恢复时不需指定库名,库无需事先存在

7)mysql备份时指定能找到binlog位置点，且刷新binlog(以备份所有数据库及数据为例,参数：-A、--master-data=1/2和-F)

格式:# mysqldump -u用户名 -p密码 -A -B -F --master-data=1/2 --events > /路径/…/备份文件名.sql  #有-B参数，恢复时不需指定库名，…

8)mysql备份数据库表结构（只备份表结构，不包含数据，加-d参数）

a.备份单个库的所有表的结构（不包含数据）

格式： #mysqldump -u用户名 -p密码 -B -d 库名 > /路径/…/备份文件名.sql       #有-B参数，恢复时不需指定库名，库无需事先存在

b.备份单个库的某个表的结构（不包含数据）

格式： #mysqldump -u用户名 -p密码 -B -d 库名 表名 > /路径/…/备份文件名.sql  #有-B参数，恢复不需指定库名，库无需事先存在

其他常用参数介绍和注意事项：

1)以上参数可以根据情况灵活使用，不限于固定哪种格式。各参数之间可以灵活增减，根据业务需求使用参数。

2)备份时切换用-F参数刷新bin-log日志使用好处和坏处

好处是：当数据库被删除后，在恢复数据全备时候，全备之前的bin-log日志就可以不考率了，全备里面已经有了这些语句，而只需恢复全备后，将全备日期后面的新的bin-log日志做增量恢复即可，减少了恢复复杂度。

坏处是：如果多库分库备份，那么每次备份都刷新binlog，导致binlog日志文件过多，增量恢复时候比较复杂。

3）参数-B的作用：

a)如果不加-B参数时的备份，备份文件中就没有建库和切库的语句，在恢复时候需指定库名，且要恢复的数据库必须要事先存在该库名，若没有该库，需手动创建一个该库名，还需要考虑建库时候的字符集，也倒是能解决，但是比较麻烦。

b)如果加了-B参数时的备份，备份文件中就有建库和切库的语句，在恢复时候不需指定库名，且要恢复的数据库也不需要事先有该库名，即使drop掉该库也没事。-B的另一个作用还可以备份多个库，恢复时候也不需指定库，也不需事先存在该库。

c)备份多个库时候，需要使用-B参数。

4)参数--master-data=1/2

如果备份时候不刷新bin-log日志(也可刷-F）,也可使用参数--maste-data=1/2 自动能在备份文件中形成bin-log日志文件名及对应的位置点，可以自动查找binlog位置点，值1和2的区别是：2是能增加注释，1没有注释，一般情况使用2就可以，如果做主从同步时候使用--master-data=1，可以在从库上change master…时候，不用指定binlog日志和位置点。

5)参数--events的作用

备份时候，如果有警告（当然也可忽略没事），可以使用该参数，忽略警告。

6)参数-x （备份时候锁表，适用于myisam引擎和innodb引擎）

备份时候一般需要锁表，确保数据一致性，-x 参数是备份时候锁表，常适用于myisam引擎的，也可用用innodb的，但前者好。

7)参数--single-transaction （备份时候相当于锁表，仅适用于innodb引擎）

备份时候一般需要锁表，确保数字一致性，--single-transaction来保证备份的一致性，实际上他的工作原理是设定本次会话的隔离级别为：repeatable_read，以确保本次会话（dump）时，不会看到其他会话已经提交了数据，仅适用于innodb引擎

8)参数-t （备份时候只备份数据）

-t 参数是备份时候只备份数据

9)参数-d（只备份表结构，不包含数据）

只备份表结构，不包含数据，加-d参数

3.myisam引擎和innodb引擎的全量备份方式区别和最常用的备份命令 （参数根据需求灵活使用）

全量备份(备份整个数据库,一般不刷新)：

myisam引擎的全量备份：(全部数据库备份为例)

#mysqldump  -u用户名  -p密码 -A -B  --master-data=1/2 -x --events |gzip > /路径/…/all.sql.gz    

innodb引擎的全量备份：（全部数据库备份为例），一般用--single-transaction参数，仅适用于innodb引擎

#mysqldump  -u用户名  -p密码 -A -B  --master-data=1/2 --events  --single-transaction|gzip > /路径/…/all.sql.gz  

若是混合引擎（myisam和innodb都有）：  建议以myisam引擎的备份格式为主，锁表-x备份。

全量备份(备份单个数据库,一般刷新-F)：

myisam引擎的全量备份：(单个数据库备份为例)

#mysqldump  -u用户名  -p密码 -B -F 数据库名 --master-data=1/2 -x --events |gzip > /路径/…/all.sql.gz    

innodb引擎的全量备份：（单个数据库备份为例），一般用--single-transaction参数，仅适用于innodb引擎

#mysqldump  -u用户名  -p密码 -B -F 数据库名 --master-data=1/2 --events  --single-transaction|gzip > /路径/…/all.sql.gz  

全量备份(多库分库备份，写脚本,一般不刷新)：

myisam引擎的全量备份：(多库分库备份