Mysql备份工具详解

本文章主要介绍Mysql两种常用的备份工具：Mysqldump以及xtrabackup

一、MySQLdump工具详解

MySQLdump是数据库的客户端命令，我们通过MySQL协议连接至MySQL数据库服务器作备份

1.1-命令格式

mysqldump [option] database [tables] >> 支持指定数据库进行或者多表备份，但是本身是不备份我们的数据库的
mysqldump [option] -B  DB1 [DB2] [DB3] >> 支持指定数据库备份，包括数据库本身定义也会备份
mysqldump [option] -A [option] >> 备份所有的数据库 包括数据库本身的定义也会备份

1.2-mysqldump 常见通用选项

-u:指定用户登录
-p:指定用户密码
-A:备份所有的数据库，含create database
-B:备份指定的数据库，含create database
-E, --events：#备份相关的所有event scheduler
-R, --routines：#备份所有存储过程和自定义函数
--triggerss:备份表相关的触发器，默认是启用的，用--skip-triggers，不备份触发器
--default-character-set=utf8 #指定字符集
---master-data: tips: mysql8.0之后更改为--source-data
   >>此选项必须启用我们的二进制
   >>记录为被注释的#CHANGE MASTER TO语句，适合于单机使用,适用于备份还原
   >>此选项会自动关闭--lock-tables功能，自动打开-x | --lock-all-tables功能（除非开启--single-transaction）
-F, --flush-logs #备份前滚动日志，锁定表完成后，执行flush logs命令,生成新的二进制日志文件，
    配合-A 或 -B 选项时，会导致刷新多次数据库。建议在同一时刻执行转储和日志刷新，可通过和-- 
    singletransaction或-x，--master-data 一起使用实现，此时只刷新一次二进制日志
--compact #去掉注释，适合调试，节约备份占用的空间,生产不使用
-d, --no-data #只备份表结构,不备份数据,即只备份create table
-t, --no-create-info #只备份数据,不备份表结构,即不备份create table
-n,--no-create-db #不备份create database，可被-A或-B覆盖
--flush-privileges #备份mysql或相关时需要使用
-f, --force #忽略SQL错误，继续执行
--hex-blob #使用十六进制符号转储二进制列，当有包括BINARY,VARBINARY,BLOB，
BIT的数据类型的列时使用，避免乱码
-q, --quick #不缓存查询，直接输出，加快备份速度

1.3-mysqldump的MyISAM存储引擎相关的备份选项

MyISAM不支持事务，只能支持温备；不支持热备，所以必须先锁定要备份的库，而后启动备份操作

-x,--lock-all-tables #加全局读锁，锁定所有库的所有表，同时加--single-transaction或--
lock-tables选项会关闭此选项功能，注意：数据量大时，可能会导致长时间无法并发访问数据库
-l,--lock-tables #对于需要备份的每个数据库，在启动备份之前分别锁定其所有表，默认为on,--
skip-lock-tables选项可禁用,对备份MyISAM的多个库,可能会造成数据不一致
#注：以上选项对InnoDB表一样生效，实现温备，但不推荐使用

1.4-mysqldump的InnoDB存储引擎相关的备份选项

InnoDB 存储引擎支持事务,可以利用事务的相应的隔离级别,实现热备，也可以实现温备但不建议用

--single-transaction
#此选项Innodb中推荐使用，不适用MyISAM，此选项会开始备份前，先执行START TRANSACTION指令开启事务
#此选项通过在单个事务中转储所有表来创建一致的快照。 仅适用于存储在支持多版本控制的存储引擎中的表（目前只有InnoDB可以）; 转储不保证与其他存储引擎保持一致。 在进行单事务转储时，要确保有效的转储文件（正确的表内容和二进制日志位置），没有其他连接应该使用以下语句：ALTER TABLE，DROP TABLE， RENAME TABLE，TRUNCATE TABLE,此选项和--lock-tables（此选项隐含提交挂起的事务）选项是相互排斥,备份大型表时，建议将--single-transaction选项和--quick结合一起使用

1.5-生产环境实战备份策略

1.5.1-InnoDB建议备份策略

mysqldump -uroot -p -A -F -E -R --triggers --single-transaction --source-data=1
--flush-privileges --default-character-set=utf8 --hex-blob
>${BACKUP}/fullbak_${BACKUP_TIME}.sql

1.5.2-MyISAM建议备份策略

mysqldump -uroot -p -A -F -E -R -x --master-data=1 --flush-privileges --
triggers --default-character-set=utf8 --hex-blob
>${BACKUP}/fullbak_${BACKUP_TIME}.sql

1.6-实战案例：特定数据库的备份脚本

#!/bin/bash
TIME=`date +%F_%H_%M-%S%`
DIR=/backup 
DB=hellodb
PASS='kaien040729.'
[ -d $DIR ]  || mkdir  $DIR
mysqldump -uroot -p"$PASS"  -F -E -R --triggers  --single-transaction  --source-data=2 --default-character-set=utf8 -q -B $DB | gzip > ${DIR}/${DB}_${TIME}.sql.gz

1.7-实战案例：分库备份并压缩

[root@centos8 ~]#for db in `mysql -uroot -e 'show databases'|grep -Ewv
'^(Database|information_schema|performance_schema|sys)$'`;do mysqldump -B $db |
gzip > /backup/$db.sql.gz;done

[root@centos8 ~]#mysql -uroot -e 'show databases'|grep -Ewv
'^(Database|information_schema|performance_schema\sys)$'|while read db;do
mysqldump -B $db | gzip > /backup/$db.sql.gz;done

[root@centos8 ~]#mysql -uroot -e 'show databases'|grep -Ewv
'^(Database|information_schema|performance_schema|sys)$' | sed -rn 's#
(.*)#mysqldump -B \1 | gzip > /backup/\1.sql.gz#p' |bash

[root@centos8 ~]#mysql -uroot -e 'show databases'|sed -rn
'/^(Database|information_schema|performance_schema|sys)$/!s#(.*)#mysqldump -B \1
| gzip > /backup/\1.sql.gz#p' |bash

1.8-实战案例：分库备份的实战脚本

#!/bin/bash
time=`date  +%F_%H_%M-%s`
dir=/backup 
pass=kaien040729.
[ -d $dir ] ||  mkdir $dir
db_option=`mysql -uroot -p$pass -e 'show databases' | grep -Evw  'Database|information_schema|performance_schema|sys'`
for db in $db_option; do
	mysqldump   -F --single-transaction  --source-data=2 --default-character-set=utf8 -q -B $DB | gzip  > ${dir}/${db}_${time}.sql.gz 
done

1.9-实战案例：完全备份和还原

#开启二进制日志
[root@centos8 ~]#vim /etc/my.cnf.d/mariadb-server.cnf
[mysqld]
log-bin
#备份
[root@centos8 ~]#mysqldump -uroot -pmagedu -A -F --single-transaction --masterdata=
2 |gzip > /backup/all-`date +%F`.sql.gz
#还原
[root@centos8 backup]#dnf install mariadb-server
[root@centos8 backup]#gzip -d all-2019-11-27.sql.gz
[root@centos8 ~]#mysql
MariaDB [(none)]> set sql_log_bin=off;
MariaDB [(none)]> source /backup/all-2019-11-27.sql
MariaDB [(none)]> set sql_log_bin=on;