xtrabackup 详解及相关操作流程
xtrabackup 详解
xtrabackup是Percona公司CTO Vadim参与开发的一款基于InnoDB的在线热备工具,具有开源,免费,支持在线热备,备份恢复速度快,占用磁盘空间小等特点,并且支持不同情况下的多种备份形式。
xtrabackup的官方下载地址为 http://www.percona.com/software/percona-xtrabackup
xtrabackup包含两个主要的工具,即xtrabackup和innobackupex,二者区别如下:
(1)xtrabackup只能备份innodb和xtradb两种引擎的表,而不能备份myisam引擎的表;
(2) innobackupex是一个封装了xtrabackup的Perl脚本,支持同时备份innodb和myisam,但在对myisam备份时需要加一个全局的读锁。还有就是myisam不支持增量备份。
1.备份过程
innobackupex备份过程如下图:
在图1中,备份开始时首先会开启一个后台检测进程,实时检测mysql redo的变化,一旦发现redo中有新的日志写入,立刻将日志记入后台日志文件xtrabackup_log中。之后复制innodb的数据文件和系统表空间文件ibdata1,待复制结束后,执行flush tables with read lock操作,复制.frm,MYI,MYD,等文件(执行flush tables with read lock的目的是为了防止数据表发生DDL操作,并且在这一时刻获得binlog的位置)最后会发出unlock tables,把表设置为可读可写状态,最终停止xtrabackup_log。
2.全备恢复
这一阶段会启动xtrabackup内嵌的innodb实例,回放xtrabackup日志xtrabackup_log,将提交的事务信息变更应用到innodb数据/表空间,同时回滚未提交的事务(这一过程类似innodb的实例恢复)。
恢复过程如下图:
3.增量备份
innobackupex增量备份过程中的"增量"处理,其实主要是相对innodb而言,对myisam和其他存储引擎而言,它仍然是全拷贝(全备份)
"增量"备份的过程主要是通过拷贝innodb中有变更的"页"(这些变更的数据页指的是"页"的LSN大于xtrabackup_checkpoints中给定的LSN)。增量备份是基于全备的,第一次增备的数据必须要基于上一次的全备,之后的每次增备都是基于上一次的增备,最终达到一致性的增备。增量备份的过程如下,和全备的过程很类似,区别仅在第2步。
4.增量备份恢复
和全备恢复类似,也需要两步,一是数据文件的恢复,如图4,这里的数据来源由3部分组成:全备份,增量备份和xtrabackup log。二是对未提交事务的回滚,如图5所示:
5.innobackupex使用示例
(1)安装使用xtrabackup,安装比较简单,我们使用二进制编译好的就行了,这种工具无需源码编译,因为没有什么功能需要定制。
[root@MySQL-01 ~]# wget http://www.percona.com/redir/downloads/XtraBackup/LATEST/binary/Linux/x86_64/percona-xtrabackup-2.1.8-733-Linux-x86_64.tar.gz [root@MySQL-01 ~]# tar xf percona-xtrabackup-2.1.8-733-Linux-x86_64.tar.gz -C /usr/local/ [root@MySQL-01 ~]# mv /usr/local/percona-xtrabackup-2.1.8-Linux-x86_64/ /usr/local/xtrabackup [root@MySQL-01 ~]# echo "export PATH=\$PATH:/usr/local/xtrabackup/bin" >> /etc/profile [root@MySQL-01 ~]# source /etc/profile |
(2)全量备份
创建备份用户:
mysql> create user 'backup'@'%' identified by 'peng'; Query OK, 0 rows affected (0.01 sec)
mysql> grant reload,lock tables,replication client,create tablespace,super on *.* to 'backup'@'%'; Query OK, 0 rows affected (0.00 sec) |
进行全备份:
备份数据存放在/data/backup/下面,innobackupex会自动创建一个文件夹,是当前系统的时间戳.
mysql> select * from peng.t1; +------+-------+ | id | name | +------+-------+ | 1 | peng | | 2 | atlas | +------+-------+ 2 rows in set (0.00 sec) |
测试数据就是peng库中的t1表:
[root@MySQL-01 ~]# innobackupex --user=backup --password=peng --socket=/tmp/mysqld.sock --defaults-file=/etc/my.cnf /data/backup/ xtrabackup: Creating suspend file '/data/backup/2014-04-07_23-05-04/xtrabackup_log_copied' with pid '57608' xtrabackup: Transaction log of lsn (5324782783) to (5324782783) was copied. 140407 23:06:14 innobackupex: All tables unlocked innobackupex: Backup created in directory '/data/backup/2014-04-07_23-05-04' innobackupex: MySQL binlog position: filename 'mysql-bin.000014', position 2983 140407 23:06:14 innobackupex: Connection to database server closed 140407 23:06:14 innobackupex: completed OK! |
上面的过程中处理过,主要看最后是否提示innobackupex completed ok,可以看见备份成功。我们看看/data/backup目录下产生了什么:
[root@MySQL-01 backup]# pwd /data/backup [root@MySQL-01 backup]# ll total 4 drwxr-xr-x 12 root root 4096 Apr 7 23:06 2014-04-07_23-05-04 [root@MySQL-01 backup]# cd 2014-04-07_23-05-04/ [root@MySQL-01 2014-04-07_23-05-04]# ll total 845888 -rw-r--r-- 1 root root 261 Apr 7 23:05 backup-my.cnf drwx------ 2 root root 4096 Apr 7 23:06 employees drwx------ 2 root root 4096 Apr 7 23:06 host -rw-r----- 1 root root 866123776 Apr 7 23:05 ibdata1 drwx------ 2 root root 4096 Apr 7 23:06 menagerie drwxr-xr-x 2 root root 4096 Apr 7 23:06 mysql drwxr-xr-x 2 root root 4096 Apr 7 23:06 performance_schema drwx------ 2 root root 4096 Apr 7 23:06 sakila drwx------ 2 root root 4096 Apr 7 23:06 test drwx------ 2 root root 4096 Apr 7 23:06 world_innodb drwxr-xr-x 2 root root 4096 Apr 7 23:06 world_myisam -rw-r--r-- 1 root root 13 Apr 7 23:06 xtrabackup_binary -rw-r--r-- 1 root root 24 Apr 7 23:06 xtrabackup_binlog_info -rw-r----- 1 root root 95 Apr 7 23:06 xtrabackup_checkpoints -rw-r----- 1 root root 2560 Apr 7 23:06 xtrabackup_logfile drwx------ 2 root root 4096 Apr 7 23:06 peng
可以看见有对应数据库的名字,比如peng,还有一个以时间戳命名的目录。我们看看对应文件里面的内容,这几个比较重要: [root@MySQL-01 2014-04-07_23-05-04]# cat xtrabackup_checkpoints backup_type = full-backuped from_lsn = 0 to_lsn = 5324782783 last_lsn = 5324782783 compact = 0 [root@MySQL-01 2014-04-07_23-05-04]# cat xtrabackup_binlog_info mysql-bin.000014 2983 可以看见相关文件记录了LSN,日志偏移量,还可以看见这次是全备份. |
删除数据库,然后恢复全备(线上不要这样搞)
mysql> drop database peng; Query OK, 1 row affected (0.04 sec) |
恢复全备:
恢复备份到mysql的数据文件目录,这一过程要先关闭mysql数据库,重命名或者删除原数据文件目录都可以,
再创建一个新的数据文件目录,将备份数据复制到新的数据文件目录下,赋权,修改权限,启动数据库。
[root@MySQL-01 ~]# /etc/init.d/mysqld stop Shutting down MySQL..... [ OK ] [root@MySQL-01 ~]# mv /data/mysql /data/mysql_bak [root@MySQL-01 ~]# mkdir /data/mysql
[root@MySQL-01 ~]# innobackupex --apply-log /data/backup/2014-04-07_23-05-04/ xtrabackup: starting shutdown with innodb_fast_shutdown = 1 140407 23:22:36 InnoDB: Starting shutdown... 140407 23:22:40 InnoDB: Shutdown completed; log sequence number 5324784140 140407 23:22:40 innobackupex: completed OK! 以上对应的目录就是innobackupex全备份自己创建的目录。
[root@MySQL-01 ~]# innobackupex --defaults-file=/etc/my.cnf --copy-back --rsync /data/backup/2014-04-07_23-05-04/ innobackupex: Starting to copy InnoDB log files innobackupex: in '/data/backup/2014-04-07_23-05-04' innobackupex: back to original InnoDB log directory '/data/mysql' innobackupex: Copying '/data/backup/2014-04-07_23-05-04/ib_logfile1' to '/data/mysql/ib_logfile1' innobackupex: Copying '/data/backup/2014-04-07_23-05-04/ib_logfile0' to '/data/mysql/ib_logfile0' innobackupex: Finished copying back files. 140407 23:27:38 innobackupex: completed OK!
可以看见已经成功恢复,修改数据目录权限,启动mysql,效验数据是否正常,查看peng库下面的t1表中的数据。 [root@MySQL-01 ~]# chown -R mysql.mysql /data/mysql [root@MySQL-01 ~]# /etc/init.d/mysqld start Starting MySQL................. [ OK ]
查看恢复的数据: mysql> use peng Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A
Database changed mysql> select * from t1; +------+-------+ | id | name | +------+-------+ | 1 | peng | | 2 | atlas | +------+-------+ 2 rows in set (0.00 sec) 发现数据已经成功恢复。 |
(3)增量备份
在进行增量备份时,首先要进行一次全量备份,第一次增量备份是基于全备的,之后的增量备份是基于上一次的增量备份,以此类推。
全备份放在/data/backup/full,增量备份放在/data/backup/incremental。
[root@MySQL-01 ~]# tree /data/backup/ /data/backup/ ├── full └── incremental |
先来一次全备份:
[root@MySQL-01 ~]# innobackupex --user=backup --password=peng --socket=/tmp/mysqld.sock --defaults-file=/etc/my.cnf /data/backup/full/ innobackupex: Backup created in directory '/data/backup/full/2014-04-07_23-37-20' innobackupex: MySQL binlog position: filename 'mysql-bin.000001', position 107 140407 23:38:29 innobackupex: Connection to database server closed 140407 23:38:29 innobackupex: completed OK! |
为了测试效果,我们在t1表中插入数据:
mysql> select * from t1; +------+-------+ | id | name | +------+-------+ | 1 | peng | | 2 | atlas | +------+-------+ 2 rows in set (0.00 sec)
mysql> insert into t1 select 1,'love sql'; Query OK, 1 row affected (0.01 sec) Records: 1 Duplicates: 0 Warnings: 0
mysql> select * from t1; +------+----------+ | id | name | +------+----------+ | 1 | peng | | 2 | atlas | | 1 | love sql | +------+----------+ 3 rows in set (0.00 sec) |
现在来一次增量备份1:
[root@MySQL-01 ~]# innobackupex --user=backup --password=peng --socket=/tmp/mysqld.sock --defaults-file=/etc/my.cnf --incremental /data/backup/incremental/ --incremental-basedir=/data/backup/full/2014-04-07_23-37-20/ --parallel=2 innobackupex: Backup created in directory '/data/backup/incremental/2014-04-07_23-42-46' innobackupex: MySQL binlog position: filename 'mysql-bin.000001', position 301 140407 23:43:25 innobackupex: Connection to database server closed 140407 23:43:25 innobackupex: completed OK! |
我们看看增量备份的大小以及文件内容:
[root@MySQL-01 ~]# du -sh /data/backup/full/2014-04-07_23-37-20/ 1.2G /data/backup/full/2014-04-07_23-37-20/
[root@MySQL-01 ~]# du -sh /data/backup/incremental/2014-04-07_23-42-46/ 3.6M /data/backup/incremental/2014-04-07_23-42-46/ 看见增量备份的数据很小吧,就是备份改变的数据而已。
[root@MySQL-01 2014-04-07_23-42-46]# pwd /data/backup/incremental/2014-04-07_23-42-46
[root@MySQL-01 2014-04-07_23-42-46]# cat xtrabackup_checkpoints backup_type = incremental from_lsn = 5324784718 to_lsn = 5324785066 last_lsn = 5324785066 compact = 0 上面已经明显说明是增量备份了!!
我们再次向t1表插入数据,然后创建增量备份2: mysql> select * from t1; +------+----------+ | id | name | +------+----------+ | 1 | peng | | 2 | atlas | | 1 | love sql | +------+----------+ 3 rows in set (0.00 sec)
mysql> insert into t1 select 1,'mysql dba'; Query OK, 1 row affected (0.00 sec) Records: 1 Duplicates: 0 Warnings: 0
mysql> select * from t1; +------+-----------+ | id | name | +------+-----------+ | 1 | peng | | 2 | atlas | | 1 | love sql | | 1 | mysql dba | +------+-----------+ 4 rows in set (0.00 sec) |
创建增量备份2(这次是基于上次的增量备份哦):
[root@MySQL-01 ~]# innobackupex --user=backup --password=peng --socket=/tmp/mysqld.sock --defaults-file=/etc/my.cnf --incremental /data/backup/incremental/ --incremental-basedir=/data/backup/incremental/2014-04-07_23-42-46/ --parallel=2 innobackupex: Backup created in directory '/data/backup/incremental/2014-04-07_23-51-15' innobackupex: MySQL binlog position: filename 'mysql-bin.000001', position 496 140407 23:51:55 innobackupex: Connection to database server closed 140407 23:51:55 innobackupex: completed OK!
[root@MySQL-01 ~]# ls -ltr /data/backup/full/ total 4 drwxr-xr-x 12 root root 4096 Apr 7 23:38 2014-04-07_23-37-20
[root@MySQL-01 ~]# ls -ltr /data/backup/incremental/ total 8 drwxr-xr-x 12 root root 4096 Apr 7 23:43 2014-04-07_23-42-46 drwxr-xr-x 12 root root 4096 Apr 7 23:51 2014-04-07_23-51-15 |
(4)增量备份恢复
增量备份的恢复大体为3个步骤:
*恢复完全备份
*恢复增量备份到完全备份(开始恢复的增量备份要添加--redo-only参数,到最后一次增量备份去掉--redo-only参数)
*对整体的完全备份进行恢复,回滚那些未提交的数据
恢复完全备份(注意这里一定要加--redo-only参数,该参数的意思是只应用xtrabackup日志中已提交的事务数据,不回滚还未提交的数据)
[root@MySQL-01 ~]# innobackupex --apply-log --redo-only /data/backup/full/2014-04-07_23-37-20/ xtrabackup: starting shutdown with innodb_fast_shutdown = 1 140407 23:59:43 InnoDB: Starting shutdown... 140407 23:59:43 InnoDB: Shutdown completed; log sequence number 5324784718 140407 23:59:43 innobackupex: completed OK! |
将增量备份1应用到完全备份:
[root@MySQL-01 ~]# innobackupex --apply-log --redo-only /data/backup/full/2014-04-07_23-37-20/ --incremental-dir=/data/backup/incremental/2014-04-07_23-42-46/ innobackupex: Copying '/data/backup/incremental/2014-04-07_23-42-46/mysql/func.frm' to '/data/backup/full/2014-04-07_23-37-20/mysql/func.frm' innobackupex: Copying '/data/backup/incremental/2014-04-07_23-42-46/mysql/help_relation.frm' to '/data/backup/full/2014-04-07_23-37-20/mysql/help_relation.frm' innobackupex: Copying '/data/backup/incremental/2014-04-07_23-42-46/mysql/help_category.MYD' to '/data/backup/full/2014-04-07_23-37-20/mysql/help_category.MYD' innobackupex: Copying '/data/backup/incremental/2014-04-07_23-42-46/mysql/ndb_binlog_index.frm' to '/data/backup/full/2014-04-07_23-37-20/mysql/ndb_binlog_index.frm' 140408 00:02:07 innobackupex: completed OK! |
将增量备份2应用到完全备份(注意恢复最后一个增量备份时需要去掉--redo-only参数,回滚xtrabackup日志中那些还未提交的数据):
[root@MySQL-01 ~]# innobackupex --apply-log /data/backup/full/2014-04-07_23-37-20/ --incremental-dir=/data/backup/incremental/2014-04-07_23-51-15/ innobackupex: Copying '/data/backup/incremental/2014-04-07_23-51-15/mysql/help_relation.frm' to '/data/backup/full/2014-04-07_23-37-20/mysql/help_relation.frm' innobackupex: Copying '/data/backup/incremental/2014-04-07_23-51-15/mysql/help_category.MYD' to '/data/backup/full/2014-04-07_23-37-20/mysql/help_category.MYD' innobackupex: Copying '/data/backup/incremental/2014-04-07_23-51-15/mysql/ndb_binlog_index.frm' to '/data/backup/full/2014-04-07_23-37-20/mysql/ndb_binlog_index.frm' 140408 00:04:33 innobackupex: completed OK! |
把所有合在一起的完全备份整体进行一次apply操作,回滚未提交的数据:
[root@MySQL-01 ~]# innobackupex --apply-log /data/backup/full/2014-04-07_23-37-20/ xtrabackup: starting shutdown with innodb_fast_shutdown = 1 140408 0:06:32 InnoDB: Starting shutdown... 140408 0:06:36 InnoDB: Shutdown completed; log sequence number 5324785676 140408 00:06:36 innobackupex: completed OK! |
把恢复完的备份复制到数据库目录文件中,赋权,然后启动mysql数据库,检测数据正确性:
[root@MySQL-01 ~]# /etc/init.d/mysqld stop Shutting down MySQL. [ OK ]
[root@MySQL-01 ~]# mv /data/mysql /data/mysql_bak
[root@MySQL-01 ~]# mkdir /data/mysql
[root@MySQL-01 ~]# innobackupex --defaults-file=/etc/my.cnf --copy-back --rsync /data/backup/full/2014-04-07_23-37-20/ innobackupex: Starting to copy InnoDB log files innobackupex: in '/data/backup/full/2014-04-07_23-37-20' innobackupex: back to original InnoDB log directory '/data/mysql' innobackupex: Copying '/data/backup/full/2014-04-07_23-37-20/ib_logfile1' to '/data/mysql/ib_logfile1' innobackupex: Copying '/data/backup/full/2014-04-07_23-37-20/ib_logfile0' to '/data/mysql/ib_logfile0' innobackupex: Finished copying back files. 140408 00:12:42 innobackupex: completed OK!
[root@MySQL-01 ~]# chown -R mysql.mysql /data/mysql
[root@MySQL-01 ~]# /etc/init.d/mysqld start Starting MySQL.... [ OK ] |
查看数据是否正确:
mysql> select * from t1; +------+-----------+ | id | name | +------+-----------+ | 1 | peng | | 2 | atlas | | 1 | love sql | | 1 | mysql dba | +------+-----------+ 4 rows in set (0.00 sec) |
(5)克隆slave
在日常工作中,我们有时候需要在线添加从库,比如线上有一主一从两个数据库,但是由于业务的需要,一台从库的读取无法满足现在的需求,这样就需要我们在线添加从库,由于出于安全考虑,我们通常需要在从库上进行在线克隆slave。
克隆slave时,常用参数--slave-info和--safe-slave-backup。
--slave-info会将master的binlog文件名和偏移量位置保存到xtrabackup_slave_info文件中。
--safe-slave-backup会暂停slave的SQL线程直到没有打开的临时表的时候开始备份。备份结束后SQL线程会自动启动,这样操作的目的主要是确保一致性的复制状态。
下面的例子,将介绍一主一从情况下在线搭建新的从库,环境如下:
master 192.168.0.10 #主库
slave 192.168.0.20 #从库
newslave 192.168.0.100 # 新的从库
在上述示例中,newslave即为要新搭建的从库。在老的从库上面进行备份:
[root@MySQL-02 ~]# innobackupex --user=root --password=12345 --socket=/tmp/mysqld.sock --defaults-file=/etc/my.cnf --slave-info --safe-slave-backup --no-timestamp /data/cloneslave innobackupex: Backup created in directory '/data/cloneslave' innobackupex: MySQL binlog position: filename 'mysql-bin.000022', position 107 innobackupex: MySQL slave binlog position: master host '192.168.0.10', filename 'mysql-bin.000006', position 732 140413 23:25:13 innobackupex: completed OK! |
这里的/data/cloneslave 目录要不存在,如果存在是会报错的。
查看目录下生成的文件:
[root@MySQL-02 ~]# ll /data/cloneslave/ total 26668 -rw-r--r-- 1 root root 261 Apr 13 23:24 backup-my.cnf -rw-r--r-- 1 root root 27262976 Apr 13 23:24 ibdata1 drwxr-xr-x 2 root root 4096 Apr 13 23:25 mysql drwxr-xr-x 2 root root 4096 Apr 13 23:25 performance_schema drwxr-xr-x 2 root root 4096 Apr 13 23:25 sakila drwxr-xr-x 2 root root 4096 Apr 13 23:25 world_innodb -rw-r--r-- 1 root root 13 Apr 13 23:25 xtrabackup_binary -rw-r--r-- 1 root root 23 Apr 13 23:25 xtrabackup_binlog_info -rw-r--r-- 1 root root 79 Apr 13 23:25 xtrabackup_checkpoints -rw-r--r-- 1 root root 2560 Apr 13 23:25 xtrabackup_logfile -rw-r--r-- 1 root root 72 Apr 13 23:25 xtrabackup_slave_info drwxr-xr-x 2 root root 4096 Apr 13 23:25 peng |
查看xtrabackup_slave_info文件内容,这个内容就是为搭建从库时需要change master to的参数:
[root@MySQL-02 ~]# cat /data/cloneslave/xtrabackup_slave_info CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000006', MASTER_LOG_POS=732 |
在老的slave服务器上进行还原,即192.168.0.20:
[root@MySQL-02 ~]# innobackupex --apply-log --redo-only /data/cloneslave/ xtrabackup: starting shutdown with innodb_fast_shutdown = 1 140413 23:30:37 InnoDB: Starting shutdown... 140413 23:30:37 InnoDB: Shutdown completed; log sequence number 12981048 140413 23:30:37 innobackupex: completed OK! |
将还原的文件复制到新的从库newslave,即192.168.0.100
[root@MySQL-02 data]# rsync -avprP -e ssh /data/cloneslave/ 192.168.0.100:/data/mysql/ |
在主库master上添加对新从库newslave的授权:
mysql> grant replication slave on *.* to 'repl'@'192.168.0.100' identified by '123456'; Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges; Query OK, 0 rows affected (0.02 sec) |
拷贝老的从库的配置文件到新的从库newslave,并且修改server-id参数,修改完毕后,启动新的从库;
[root@MySQL-02 data]# scp /etc/my.cnf 192.168.0.100:/etc/ root@192.168.0.100's password: my.cnf 100% 4881 4.8KB/s 00:00
[root@newslave mysql]# egrep 'log-slave|^server-id|skip_slave' /etc/my.cnf server-id = 3 skip_slave_start log-slave-updates=1
[root@newslave mysql]# chown -R mysql.mysql . [root@newslave mysql]# /etc/init.d/mysqld restart Shutting down MySQL. [ OK ] Starting MySQL.. [ OK ] |
查找老的从库备份后生成的xtrabackup_slave_info文件,提取其中的master_log_file和master_log_pos信息,然后在新的从库上进行change master to操作:
在新的从库上进行同步:
mysql> CHANGE MASTER TO MASTER_HOST='192.168.0.10',MASTER_USER='repl', MASTER_PASSWORD='123456',MASTER_LOG_FILE='mysql-bin.000006', MASTER_LOG_POS=732; Query OK, 0 rows affected (0.09 sec) |
启动io线程和sql线程,并观察复制是否正常:
mysql> start slave; Query OK, 0 rows affected (0.00 sec)
mysql> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.0.10 Master_User: repl Master_Port: 3306 Connect_Retry: 2 Master_Log_File: mysql-bin.000006 Read_Master_Log_Pos: 1309 Relay_Log_File: MySQL-02-relay-bin.000002 Relay_Log_Pos: 830 Relay_Master_Log_File: mysql-bin.000006 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: peng.% Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 1309 Relay_Log_Space: 989 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 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 1 1 row in set (0.00 sec) |
查看主库,发现已经有两个线程(Binlog Dump):
mysql> show processlist\G *************************** 1. row *************************** Id: 8 User: slave Host: 192.168.0.20:44251 db: NULL Command: Binlog Dump Time: 1088 State: Master has sent all binlog to slave; waiting for binlog to be updated Info: NULL *************************** 2. row *************************** Id: 9 User: root Host: localhost db: peng Command: Query Time: 0 State: NULL Info: show processlist *************************** 3. row *************************** Id: 10 User: repl Host: 192.168.0.100:45844 db: NULL Command: Binlog Dump Time: 124 State: Master has sent all binlog to slave; waiting for binlog to be updated Info: NULL 3 rows in set (0.00 sec) |
正常工作,到此在线克隆slave就结束啦。