MySQLdump逻辑备份 - MySQL

#MYSQLDUMP
>####mysqldump [连接选项] --database 数据库 [表名] > 备份文件
>####mysqldump [连接选项] --databases（-B） 数据库 [数据库2 数据库3 ...] > 备份文件
>####mysqldump [连接选项] --all-databases（-A） > 备份文件
>####mysqldump [连接选项] --all-databases（-A）| gzip > 备份文件
	连接选项：
	-u			#指定用户名
	-p			#指定密码
	-h			#指定服务器IP
	-P			#指定端口
	--------------------------------------------------------
	--add-drop-database  #每个数据库创建语句前加DROP DATABASE
	--add-drop-table     #在每个表创建语句前加DROP TABLE
	-n 				  #不包含数据库的创建语句
	-t				   #不包含数据表的创建语句
	-d				   #不包含数据
	--skip-triggers	  #不包含触发器的创建语句
	-R				   #导出存储过程和函数
	-E				   #导出事件
	-T				   #将指定数据表中的数据备份为单纯的数据文本和建表SQL两个文件
		--fields-terminated-by 'name'
		#字段分隔符，默认'\t'
		--fields-enclosed-by 'name'
		#字段引用符，默认不使用
		--fields-optionally-enclosed-by 'name'
		#转义字符，默认'\'
		--fields-escaped-by 'name'
		#记录结束符，默认'\n'\
	-F 			      #备份前刷新日志
	-l				   #给所有表加读锁
	--default-character-set=name	  #设置导出的客户端字符集
	
---
##示例
####使用--quick选项：这将执行一个快速的备份，减少带宽使用。
	mysqldump --quick --user=username --password=password --host=localhost database_name > backup.sql
---
####使用--compress选项：这将压缩备份文件，从而减少传输的数据量和带宽使用。
	mysqldump --compress --user=username --password=password --host=localhost database_name | gzip > backup.sql.gz
---
####使用--single-transaction选项：这将使用一个单独的事务来备份数据库，从而避免锁定表，并减少带宽使用。
	mysqldump --single-transaction --user=username --password=password --host=localhost database_name > backup.sql
---
####使用--max-allowed-packet选项：这将限制mysqldump发送的数据包大小，从而减少带宽使用。
	mysqldump --max-allowed-packet=10M --user=username --password=password --host=localhost database_name > backup.sql
---
####使用--net-buffer-length选项：这将设置网络缓冲区的大小，从而限制mysqldump使用的带宽。
	mysqldump --net-buffer-length=10K --user=username --password=password --host=localhost database_name > backup.sql

---
##备份
	mysqldump -uroot -p -A --single-transaction --master-data=2  --flush-logs > /data_bak/master.sql     
	#压缩
	mysqldump -uroot -p -A --single-transaction --master-data=2  --flush-logs | gzip > /data_bak/master.sql.gz

其他选项:
	 -A, --all-databases:所有数据库
	 --single-transaction：保证各个表具有数据一致性快照。 #常用
	 --lock-all-tables：保证整个数据库（所有schema）的数据具有一致性快照。相当于全局读锁，#一般少用
     --master-date[=value]  
         1: 记录为CHANGE MASTER TO 语句、语句不被注释
         2: 记录为注释的CHANGE MASTER TO语句
         基于二进制还原只能全库还原
	 	master-data主要是为了记录binlog的log和pos
     -E, --events: 备份事件调度器
     -R, --routines: 备份存储过程和存储函数
     --triggers: 备份表的触发器; --skip-triggers

-F,--flush-logs: 日志滚动,生成新的binlog文件
         锁定表完成后执行日志滚动

####3、备份同个库多个表
>格式：mysqldump -h主机名 -P端口 -u用户名 -p密码 --database 数据库名 表1 表2 .... > 文件名.sql

mysqldump -h192.168.1.100 -p3306 -uroot -ppassword cmdb t1 t2 > /data/backup/cmdb_t1_t2.sql

####4、同时备份多个库
>格式：mysqldump -h主机名 -P端口 -u用户名 -p密码 --databases 数据库名1 数据库名2 数据库名3 > 文件名.sql

mysqldump -h192.168.1.100 -uroot -ppassword --databases cmdb bbs blog > /data/backup/mutil_db.sql

####5、备份指定表符合要求的记录
	mysqldump -u用户名 -p密码 数据库名 表名 --where="筛选条件" > 导出文件路

####6、备份指定表
	 mysqldump -uroot test $(mysql -uroot -pxxxxxxx-A -D test -Bse "show tables where Tables_in_test like \"e_reportcustomerfee%\";")|gzip > vos.sql.gz

##恢复
###1、查看备份的binlog信息
	cat /data_bak/master.sql | grep master-bin

![](/media//201911/2019-11-24140530552686.png)

###2、恢复备份
	#方法一：
	mysql -uroot -p -Dd9cloud < 备份文件.sql
	#解压
	gunzip < 备份文件.sql.gz | mysql -uroot -p -Dd9cloud

#方法二：
	mysql> SET sql_log_bin=OFF;   #暂时先将二进制日志关闭

mysql> source /data_bak/master.sql  #恢复数据，所需时间根据数据库时间大小而定

mysql> SET sql_log_bin=ON; 开启二进制日志

###3、binlog重做
	mysqlbinlog /var/lib/mysql/master-bin.000022 | grep -A 100 "# at 862"    #查看binlog，根据需求可把不要的position跳过
	
	#grep -C 5 foo file 显示file文件里匹配foo字串那行以及上下5行
	#grep -B 5 foo file 显示foo及前5行
	#grep -A 5 foo file 显示foo及后5行

mysqlbinlog --start-position=862 --stop-position=1196 /var/lib/mysql/master-bin.000022 | mysql -uroot -p  #还原到最新