一、XtraBackup介绍
1、备份工具
xtrabackup：是一款基于InnoDB的在线热备工具，具有开源，免费，支持在线热备，占用磁盘空间小，能够非常快速地
备份与恢复mysql数据库，(备份时不影响数据读写)
官方下载地址为http://www.percona.com/software/percona-xtrabackup
mysqldump：备份是逻辑备份，备份出来的文件是sql语句，但是当备份MySQL数据超过10G时，用mysqldump来导出备份就比较慢了
2、备份原理
3、xtrabackup包含两个主要的工具
①xtrabackup只能备份innodb和xtradb两种引擎的表，而不能备份myisam引擎的表；
②innobackupex是一个封装了xtrabackup的Perl脚本，支持同时备份innodb和myisam，但在对myisam备份时需要加一个全局的读锁，还有就是myisam不支持增量备份
innobackupex备份和恢复的工作原理
二、XtraBackup安装部署
1、环境
①db
[root@db ~]# cat /etc/redhat-release CentOS release 6.9 (Final) [root@db ~]# uname -r 2.6.32-696.el6.x86_64 [root@db ~]# /etc/init.d/iptables status iptables: Firewall is not running. [root@db ~]# getenforce Disabled [root@db ~]# hostname -I 172.19.5.54 172.16.1.54
②db01
[root@db01 ~]# cat /etc/redhat-release CentOS release 6.9 (Final) [root@db01 ~]# uname -r 2.6.32-696.el6.x86_64 [root@db01 ~]# /etc/init.d/iptables status iptables: Firewall is not running. [root@db01 ~]# getenforce Disabled [root@db01 ~]# hostname -I 172.19.5.55 172.16.1.55
2、安装XtraBackup
cd /server/tools/ wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.9/binary/redhat/6/x86_64/percona-xtrabackup-24-2.4.9-1.el6.x86_64.rpm yum localinstall percona-xtrabackup-24-2.4.9-1.el6.x86_64.rpm -y
3、db服务器模拟插入数据
create database oldboy;#创建oldboy库 use oldboy; create table test( id int(4) not null,name char(20) not null);#在oldboy库中创建test表 insert into test values(1,"oldboy");#test表中插入数据 insert into test values(2,"oldgirl"); insert into test values(3,"inca"); insert into test values(4,"zuma"); insert into test values(5,"kaka"); insert into test values(6,"老男孩");
查看test表中内容
mysql> select * from test; +----+-----------+ | id | name | +----+-----------+ | 1 | oldboy | | 2 | oldgirl | | 3 | inca | | 4 | zuma | | 5 | kaka | | 6 | 老男孩 | +----+-----------+ 6 rows in set (0.00 sec)
4、备份数据
①db操作
mkdir /backup -p && cd /backup innobackupex --defaults-file="/etc/my.cnf" --user=root --password='123456' --socket=/application/mysql/tmp/mysql.sock /backup/ #备份完成会出现如下内容 …… xtrabackup: Transaction log of lsn (1642766) to (1642766) was copied. 180109 11:40:30 completed OK!
②查看备份的内容
[root@db backup]# ll /backup/2018-01-09_11-57-49/ total 131104 -rw-r----- 1 root root 418 2018-01-09 11:57 backup-my.cnf -rw-r----- 1 root root 12582912 2018-01-09 12:01 ibdata1 -rw-r----- 1 root root 50331648 2018-01-09 12:01 ib_logfile0 -rw-r----- 1 root root 50331648 2018-01-09 12:01 ib_logfile1 -rw-r----- 1 root root 12582912 2018-01-09 12:01 ibtmp1 drwxr-x--- 2 root root 4096 2018-01-09 11:57 mysql drwxr-x--- 2 root root 4096 2018-01-09 11:57 oldboy drwxr-x--- 2 root root 4096 2018-01-09 11:57 performance_schema -rw-r----- 1 root root 22 2018-01-09 11:57 xtrabackup_binlog_info -rw-r--r-- 1 root root 22 2018-01-09 12:01 xtrabackup_binlog_pos_innodb -rw-r----- 1 root root 113 2018-01-09 12:01 xtrabackup_checkpoints -rw-r----- 1 root root 536 2018-01-09 11:57 xtrabackup_info -rw-r----- 1 root root 8388608 2018-01-09 12:01 xtrabackup_logfile
③预处理数据
innobackupex --defaults-file="/etc/my.cnf" --user=root --password='123456' --socket=/application/mysql/tmp/mysql.sock --apply-log --use-memory=1G /backup/2018-01-09_11-57-49/
④模拟删除oldboy库，恢复全备
drop database oldboy;
⑤为了保持数据一致性，停库恢复
[root@db ~]# /etc/init.d/mysqld stop Shutting down MySQL.. SUCCESS! rm -fr /application/mysql/data/* innobackupex --defaults-file="/etc/my.cnf" --user=root --password='123456' --socket=/application/mysql/tmp/mysql.sock --move-back /backup/2018-01-09_11-57-49/
⑥恢复全备成功后，重新授权数据目录，启动MySQL
chown -R mysql.mysql /application/mysql/data/ [root@db mysql]# /etc/init.d/mysqld start Starting MySQL.. SUCCESS! #登录数据库查看恢复的数据 mysql> select * from oldboy.test; +----+-----------+ | id | name | +----+-----------+ | 1 | oldboy | | 2 | oldgirl | | 3 | inca | | 4 | zuma | | 5 | kaka | | 6 | 老男孩 | +----+-----------+ 6 rows in set (0.00 sec)
三、主从复制部署
1、授权从库连接用户
grant replication slave on *.* to 'rep'@'172.19.5.%' identified by '123456';
2、分发全备数据到从库db01
scp -rp 2018-01-09_14-58-50/ 172.19.5.55:/backup/
3、从库预处理
innobackupex --defaults-file="/etc/my.cnf" --user=root --password='123456' --socket=/application/mysql/tmp/mysql.sock --apply-log --use-memory=1G /backup/2018-01-09_10-33-44/
4、停从库，恢复数据
[root@db01 data]# /etc/init.d/mysqld stop Shutting down MySQL.. SUCCESS! rm -fr /application/mysql/data/* innobackupex --defaults-file="/etc/my.cnf" --user=root --password='123456' --socket=/application/mysql/tmp/mysql.sock --copy-back /backup/2018-01-09_14-58-50/
5、查看master位置点
[root@db01 2018-01-09_14-58-50]# cat /backup/2018-01-09_14-58-50/xtrabackup_binlog_info mysql-bin.000001 120
6、启动从库db01，设置主从复制
chown -R mysql.mysql /application/mysql/ /etc/init.d/mysqld start #登录数据库 CHANGE MASTER TO MASTER_HOST='172.19.5.54', MASTER_PORT=3306, MASTER_USER='rep', MASTER_PASSWORD='123456', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=120;
开启主从同步功能
mysql> start slave; Query OK, 0 rows affected (0.00 sec) mysql> show slave statusG *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 172.19.5.54 Master_User: rep Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000001 Read_Master_Log_Pos: 1438 Relay_Log_File: db01-relay-bin.000003 Relay_Log_Pos: 283 Relay_Master_Log_File: mysql-bin.000001 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 1438 Relay_Log_Space: 1936 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0
四、主从复制架构扩展
1、主从延迟复制
stop slave; change master to master_delay = N(600秒); start slave;
应用场景
①误删除恢复
②测试
③历史查询
2、半同步复制
①master配置
install plugin rpl_semi_sync_master soname 'semisync_master.so'; set global rpl_semi_sync_master_enabled = 1; set global rpl_semi_sync_master_timeout = 1000;
②slave配置
install plugin rpl_semi_sync_slave soname 'semisync_slave.so'; set global rpl_semi_sync_slave_enabled = 1; set global rpl_semi_sync_master_timeout = 1000;
③查看半同步是否开启
stop slave io_thread; start slave io_thread;#重启IO线程 show global status like 'rpl_sem%';#查看半同步状态