02 mysql备份恢复-dump和xtrabackup 的备份恢复

mysql备份恢复

前言

前一节介绍了mysql的dump备份相关参数和选项，接下来介绍一下mysql的备份恢复使用到的一些语法（主要针对主从的架构，实现备份+gtid或者binglog pos的恢复）：

1、xtrabackup和mysqldump会造成锁等待吗?

都会，但是我们可以通过参数的调整来减少锁等待的时间。

mysqldump：

dump 备份的锁表问题：
    --opt 会lock本次需要备份的所有表，因为本次备份的是 dbname数据库，所以会锁住dbname的所有表。
    --master-data=2，--master-data=1 默认的话会--lock-all-tables，会锁住整个mysql数据库中的所有表。但是如果加上--single-transaction会加上事务，不会锁表

误区：加上--single-transaction会加上事务，不会锁表 
这里要注意，通过上篇文章对mysqldump的原理的分析中我们可以知道，加上--single-transaction 之后还是会加锁，只是加锁的时间非常的短暂，在启用了快照点之后就会unlock。

mysqldump备份时，加和不加single-transaction的区别：

  通过打开general_log可以看到过程：

     1 加single-transaction时，是通过START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */的方式来实现的。里面同时有很多savepoint，
     2 不加single-trsnsaction是通过在整个过程中锁表实现数据一致性的。

xtrabackup:

xtrabackup在备份时会产生短暂的全局读锁FTWL(flush table with read lock)，用于拷贝frm/MYD/MYI等文件，以及记录binlog信息。
如果MyISAM表的数据量非常大，则拷贝时间就越长，加锁的时间也越长mysqldump有可能会边长。（所以xtrabackup更加适合innodb这种事务型的存储引擎，
如果是myIasm这种非事务的存储引擎，并且数据量较大时，千万不要在生产环境直接执行备份，因为可能会造成严重的锁表，影响业务）

  数据量特别大的话，建议优先用 xtrabackup，提高备份/恢复速度。
  而如果数据量不是太大或者想备份单表，则建议用mysqldump了，方便逻辑恢复。各有利弊，注意其适用场景

2. xtrabackup冷知识

基于MySQL 5.6版本开发的xtrabackup，会在备份过程中生成内部通信文件 suspend file，用于 xtrabackup 和 innobackupex 的通信，备份结束后文件删除，
默认文件位置 /tmp/xtrabackup_suspended

如果在备份过程中，修改了 /tmp 的访问权限或该文件的权限，则两个程序间直接不能通信，会造成 xtrabackup hang 住，正在备份的表不能正常释放锁，
会造成锁等待，此时需要强制 kill 掉 xtrabackup 进程

Waiting for table flush状态的避免：
另外，网上有个案例，mysqldump备份时，如果没有使用参数—single-transaction 
或由于同时使用了flush-logs与—single-transaction两个参数也可能引起这样的等待场景，
这个两个参数放在一起，会在开始dump数据之前先执行一个FLUSH TABLES操作。

一、使用dump实现主从架构的搭建：

假设现在有一个单机的数据库，需要的搭建一个备库，且不能停机，并且备库已经初始化完成，那么搭建备库的方式就有

    1. dump （逻辑备份）

    2.xtrabackup （物理备份）

第一种dump的方式更加适合数据量较小，并且备库已经初始化的场景（不推荐使用该方式）：

#dump备份需要同步的的业务数据库，注意：如果业务库有多个，那么需要同时备份多个数据库，如果单独备份多个数据库，则无法确定gtid或者binglog的位置信息。
同时mysql数据库需要特殊处理，直接dump备份恢复可能会出现问题，建议直接dump mysql.user表，将权限全部手动同步到备库，然后再做恢复。

1.备份业务库：

mysqldump -uroot -p test --master-data=2 --single-transaction --triggers --routines --events > /home/mysql/dump.sql 

--master-data=2（可以选择不加）

2.处理mysql.user表

mysqldump -u root -p mysql user > /tmp/mysql.user.sql

3.备库恢复mysql.user表和test业务库

sql>source /tmp/mysql.user.sql
sql>use test;
sql>source /home/mysql/dump.sql 

4.查看业务库中的gtid或者binglog位置信息（经过测试gtid启用的模式下，使用gtid或者binglog位置信息恢复数据都可以搭建备库）
cat /home/mysql/dump.sql |more

   

 5.通过binglog方式搭建备库

#reset 操作需要谨慎，reset slave all 将会清除掉所有的slave信息，也可以执行通道清理reset slave all for channel 't10';

reset slave all;
reset master;

#配置同步
change master to master_host='192.169.100.',
master_port=3306,
master_user='repl',
master_password='repl',
master_log_file='mybinlog.000003',
master_log_pos=579;

#启动复制
start slave;
show slave status \G

6.通过GTID方式搭建备库

#清空master和slve的复制信息（谨慎清理）
reset slave all;
reset master;
#设置GTID位点信息
SET @MYSQLDUMP_TEMP_LOG_BIN = @@SESSION.SQL_LOG_BIN;
SET @@SESSION.SQL_LOG_BIN= 0;
SET @@GLOBAL.GTID_PURGED='0d4c08a2-b965-11ea-988e-000c294468f3:1-2216,d6fc18b9-b964-11ea-9e62-000c29e95066:1-16,e142c3d5-b9c2-11ea-8ea5-000c29125545:1-5,e2958f21-0bb1-11eb-8062-000c2963c787:1-2';
SET @@SESSION.SQL_LOG_BIN = @MYSQLDUMP_TEMP_LOG_BIN;
#配置复制
CHANGE MASTER TO
MASTER_HOST='192.169.100.52',
MASTER_USER='repl',
MASTER_PASSWORD='repl',
MASTER_PORT=3306,
MASTER_AUTO_POSITION=1;
#启动复制
start slave;
show slave status \G;

二、使用xtrabackup实现主从架构的搭建：

xtrabackup为物理备份，详细的实现原理请查阅上一篇文章，在生产环境中我们我们通常为了节省主库的空间，而是用远程备份的方式，直接将备份传到备库，然后恢复。

1、配置等效性
ssh-keygen 
ssh-copy-id -i  ~/.ssh/id_rsa.pub mysql@hostip
或者：
cat ~/.ssh/id_*.pub|ssh -p 16818 mysql@ip 'cat>>~/.ssh/authorized_keys'

2.主库做全备：

备份前需检查xbstream命令
innobackupex --defaults-file=/etc/my.cnf --parallel=4 --user=username --password='passwd' --socket=/tmp/mysql.sock --host='主库ip' --stream=xbstream /mysqldata/bakData/mysql/bakfull | ssh mysql@备库ip "xbstream --parallel=4 -x -C /mysqldata/bakData/mysql/bakfull" &
后台执行：
nohup sh full.sh > full.log 2>&1 &

  3. 备库恢复数据

应用日志：
/usr/bin/innobackupex --defaults-file=/etc/my.cnf --user backup --apply-log /mysqldata/bakData/mysql/bakfull &

还原数据：
/usr/bin/innobackupex --defaults-file=/etc/my.cnf --user backup --move-back /mysqldata/bakData/mysql/bakfull &

4. 修改目录权限

chown -R mysql:mysql /mysqldata/mysql/data

5.启动数据库

mysqld_safe --defaults-file=/etc/my.cnf &
--basedir=/usr/local/mysql &

6.配置同步

#查看备份目录下的备份文件信息， 确定gitid信息，获取到上次执行的到的gtid位置：
cat xtrabackup_info
binlog_pos = filename 'mysql-bin.000840', position '11111', GTID of the last change '23d75ae2-1b3c-11e6-8474-78acc0f8bc20:1-6362590505';

#清除master的slave的复制信息
reset master;
reset slave;

#配置gtid信息
SET @MYSQLDUMP_TEMP_LOG_BIN = @@SESSION.SQL_LOG_BIN;
SET @@SESSION.SQL_LOG_BIN= 0;
SET @@GLOBAL.GTID_PURGED='23d75ae2-1b3c-11e6-8474-78acc0f8bc20:1-6362590505';
SET @@SESSION.SQL_LOG_BIN = @MYSQLDUMP_TEMP_LOG_BIN;

#配置同步
CHANGE MASTER TO
MASTER_HOST='1主库ip',
MASTER_USER='repl',
MASTER_PASSWORD='repl',
MASTER_PORT=3306,
MASTER_AUTO_POSITION=1;

#启动同步
start slave;

#主库配置双主；

主库执行：
CHANGE MASTER TO
MASTER_HOST='备库ip',
MASTER_USER='repl',
MASTER_PASSWORD='repl,
MASTER_PORT=3306,
MASTER_AUTO_POSITION=1;

start slave;