17.6 使用Xtrabackup在线对MySQL做主从复制
1、XtraBackup优点
(1).无需停止数据库进行InnoDB热备
(2).增量备份MySQL
(3).流压缩到传输到其它服务器
(4).能比较容易地创建主从同步
(5).备份MySQL时不会增大服务器负载
2、主从复制类型
(1).基于语句的复制:STATEMENT,在主服务器上执行的SQL语句,在从服务器上执行同样的语句. MySQL在5.7.7以前默认采用基于语句的复制,在 5.7.7 及以后版本默认改用 row-based;
(2).基于行的复制:ROW,把改变的内容复制过去,而不是把命令在从服务器上执行一遍;
(3).混合类型的复制: MIXED,默认采用基于语句的复制,一旦发现基于语句的无法精确地复制时,就会采用基于行的复制
(4).异步复制: 主库执行完Commit后,在主库写入Binlog日志后即可成功返回客户端,无需等等Binlog日志传送给从库,一旦主库宕机,有可能会丢失日志;
(5).半同步复制: 等待其中一个从库也接收到Binlog事务并成功写入Relay Log之后,才返回Commit操作成功给客户端;如此半同步就保证了事务成功提交后至少有两份日志记录,一份在主库Binlog上,另一份在从库的Relay Log上,从而进一步保证数据完整性
3、配置主从
主从配置过程主要包括:主上创建、授权复制帐号和备份账号,开启binlog及设置主从server_i,xtrabackup在主库上备份,并恢复到从库,记录xtrabackup_binlog_info中binlog名称及偏移量,从库change master 等,验证主从同步
(1).创建复制账号
- mysql> GRANT REPLICATION SLAVE ON *.* TO 'slave_fei'@'192.168.5.%' IDENTIFIED BY 'slave_fei_pass';
- mysql> FLUSH PRIVILEGES;
(2).使用Percona-Xtrabackup恢复数据
- mysql> CREATE USER 'bkpuser'@'localhost' IDENTIFIED BY 'bkppass'; #创建备份用户
- mysql> GRANT RELOAD, LOCK TABLES, REPLICATION CLIENT,PROCESS,SUPER ON *.* TO 'bkpuser'@'localhost'; #赋予备份用户权限
- mysql> FLUSH PRIVILEGES;
使用Xtrabackup全量备份:
- $ export BKP_PASS="bkppass"
- $ innobackupex --defaults-file=/etc/my.cnf --host=localhost --port=3306 --user=bkpuser --password=${BKP_PASS} /data/backup/mysql #全量备份,备份数据放在/data/backup/mysql/
使用
Xtrabackup全量恢复:
- $ innobackupex --use-memory=16G --apply-log 2018-01-24_00-00-02 #恢复准备
- $ innobackupex --defaults-file=/opt/mysql/my.cnf --use-memory=16G --copy-back 2018-01-24_00-00-02 #全量恢复,使用新的my.cnf文件,将完整的mysql数据文件拷贝到datadir下,确认数据库是关闭的,并且datadir,目录下为空
在从机器上执行如下命令查看,结果如下,表明主从不同步
- mysql> show slave status\G
Slave_IO_Running: Yes
Slave_SQL_Running: No
解决方法一:忽略错误,继续同步
适用于主从库数据相差不大,或者要求数据可以不完全统一的情况,数据要求不严格的情况
- mysql>stop slave;
- mysql>set global sql_slave_skip_counter =1; #表示跳过一步错误,后面的数字可变
- mysql>start slave;
解决方式二:重做主从,完全同步
适用于主从库数据相差较大,或者要求数据完全统一的情况, 解决步骤如下:
1.先进入主库,进行锁表,防止数据写入 使用命令:
- mysql> flush tables with read lock;
注意:该处是锁定为只读状态,语句不区分大小写
2.进行数据备份,把数据备份到mysql.bak.sql文件
- [root@server01 mysql]#mysqldump -uroot -p -hlocalhost > mysql.bak.sql
注意:数据库备份一定要定期进行,可以用shell脚本或者python脚本,确保数据万无一失
3.查看master状态,获取File和Position信息
- mysql> show master status;
+-------------------+----------+--------------+-------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+-------------------------------+
| mysqld-bin.000001 | 3260 | | mysql,test,information_schema |
+-------------------+----------+--------------+-------------------------------+
1 row in set (0.00 sec)
4.把mysql备份文件传到从库机器,进行数据恢复
#使用scp命令
- [root@server01 mysql]# scp mysql.bak.sql root@192.168.128.101:/tmp/
5.停止从库的状态
- mysql> stop slave;
6.然后到从库执行mysql命令,导入数据备份
- mysql> source /tmp/mysql.bak.sql
7.设置从库同步,注意该处的同步点,就是主库show master status信息里的File和Position两项
- change master to master_host = '192.168.128.100', master_user = 'rsync', master_port=3306, master_password='', master_log_file = 'mysqld-bin.000001', master_log_pos=3260;
8.重新开启从同步
- mysql> start slave;
1、MySQL双主(主主)架构
- 两台mysql都可读写,互为主备,默认只使用一台(masterA)负责数据的写入,另一台(masterB)备用;
- masterA是masterB的主库,masterB又是masterA的主库,二者互为主从;
- 两台主库之间做高可用,可以采用keepalived等方案(使用VIP对外提供服务);
- 所有提供服务的从服务器与masterB进行主从同步(双主多从);
- 建议采用高可用策略的时候,masterA或masterB均不因宕机恢复后而抢占VIP(非抢占模式);这样做可以在一定程度上保证主库的高可用,在一台主库down掉之后,可以在极短的时间内切换到另一台主库上(尽可能减少主库宕机对业务造成的影响),减少了主从同步给线上主库带来的压力;
2、双主架构不足的地方
- masterB可能会一直处于空闲状态(可以用它当从库,负责部分查询);
- 主库后面提供服务的从库要等masterB先同步完了数据后才能去masterB上去同步数据,这样可能会造成一定程度的同步延时;