在线建从库
平时工作中，有时候需要在线添加从库，例如产线一主一从环境，因业务需要做读能力的提升，通常我们是从库上进行在线备份来搭建新从库。
环境描述：
master：192.168.1.113 #3316端口
slave：192.168.1.113 #3317端口
新slave：192.168.1.113 #3318端口
1.在slave上使用xtrabackup的 --slave-info参数：
--slave-info记录master的binlog位置到xtrabackup_slave_info文件
[root@bogon tmp]# innobackupex --defaults-file=/usr/local/mysql/my3317.cnf --no-timestamp --slave-info /tmp/backup/
MySQL slave binlog position: master host '192.168.1.113', filename 'mysql-bin.000011', position '403'
160726 10:31:22 [00] Writing backup-my.cnf
160726 10:31:22 [00] ...done
160726 10:31:22 [00] Writing xtrabackup_info
160726 10:31:22 [00] ...done
xtrabackup: Transaction log of lsn (369687072) to (369687072) was copied.
160726 10:31:22 completed OK!
[root@bogon backup]# ls -lh 总用量 1.1G -rw-r-----. 1 root root 387 7月 26 10:31 backup-my.cnf drwx------. 2 root root 4.0K 7月 26 10:31 employees -rw-r-----. 1 root root 1.0G 7月 26 10:31 ibdata1 drwx------. 2 root root 4.0K 7月 26 10:31 mysql drwx------. 2 root root 4.0K 7月 26 10:31 performance_schema -rw-r-----. 1 root root 21 7月 26 10:31 xtrabackup_binlog_info -rw-r-----. 1 root root 117 7月 26 10:31 xtrabackup_checkpoints -rw-r-----. 1 root root 515 7月 26 10:31 xtrabackup_info -rw-r-----. 1 root root 2.5K 7月 26 10:31 xtrabackup_logfile -rw-r-----. 1 root root 72 7月 26 10:31 xtrabackup_slave_info
2.将备份的数据apply-log出来：
[root@bogon backup]# innobackupex --apply-log /tmp/backup/ xtrabackup: starting shutdown with innodb_fast_shutdown = 1 InnoDB: FTS optimize thread exiting. InnoDB: Starting shutdown... InnoDB: Shutdown completed; log sequence number 369687574 160726 11:01:56 completed OK!
[root@bogon backup]# ls -lh 总用量 2.5G -rw-r-----. 1 root root 387 7月 26 10:31 backup-my.cnf drwx------. 2 root root 4.0K 7月 26 10:31 employees -rw-r-----. 1 root root 1.0G 7月 26 11:01 ibdata1 -rw-r--r--. 1 root root 500M 7月 26 11:01 ib_logfile0 -rw-r--r--. 1 root root 500M 7月 26 11:01 ib_logfile1 -rw-r--r--. 1 root root 500M 7月 26 11:01 ib_logfile2 drwx------. 2 root root 4.0K 7月 26 10:31 mysql drwx------. 2 root root 4.0K 7月 26 10:31 performance_schema -rw-r-----. 1 root root 21 7月 26 10:31 xtrabackup_binlog_info -rw-r--r--. 1 root root 26 7月 26 11:01 xtrabackup_binlog_pos_innodb -rw-r-----. 1 root root 117 7月 26 11:01 xtrabackup_checkpoints -rw-r-----. 1 root root 515 7月 26 10:31 xtrabackup_info -rw-r-----. 1 root root 2.0M 7月 26 11:01 xtrabackup_logfile -rw-r-----. 1 root root 72 7月 26 10:31 xtrabackup_slave_info
查看xtrabackup_slave_info，这个位置就是搭建新从库时需要change master to的位置信息：
[root@bogon backup]# more xtrabackup_slave_info CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000011', MASTER_LOG_POS=403
3.（1）将slave配置文件拷贝一份到新从库并修改sock、server-id、port等信息（注意权限）。
（2）将还原的数据拷贝一份到新从库：
[root@bogon backup]# innobackupex --defaults-file=/usr/local/mysql/my3318.cnf --copy-back /tmp/backup/ 160726 11:29:28 [01] Copying ./performance_schema/events_waits_history_long.frm to /data/mysql/mysql3318/data/performance_schema/events_waits_history_long.frm 160726 11:29:28 [01] ...done 160726 11:29:28 [01] Copying ./xtrabackup_info to /data/mysql/mysql3318/data/xtrabackup_info 160726 11:29:28 [01] ...done 160726 11:29:28 completed OK!
4.在master上对新slave复制授权：
root@localhost:mysql3316.sock 11:53:23 [(none)]>grant replication slave on *.* to 'repl'@'%' identified by 'repl'; Query OK, 0 rows affected (0.00 sec) root@localhost:mysql3316.sock 11:53:38 [(none)]>flush privileges; Query OK, 0 rows affected (0.21 sec)
5.查看xtrabackup_slave_info信息，编写新slave到master复制：
root@localhost:mysql3318.sock 12:03:57 [(none)]>change master to master_host='192.168.1.113',master_user='repl',master_password='repl',master_port=3316,master_log_file='mysql-bin.000011',master_log_pos=403; Query OK, 0 rows affected, 2 warnings (0.10 sec)
6.启动io_thread和sql_thread并观察复制是否正常：
root@localhost:mysql3318.sock 12:08:47 [(none)]>start slave; Query OK, 0 rows affected (0.05 sec)
root@localhost:mysql3318.sock 12:12:08 [(none)]>show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.1.113 Master_User: repl Master_Port: 3316 Connect_Retry: 60 Master_Log_File: mysql-bin.000011 Read_Master_Log_Pos: 987 Relay_Log_File: mysql-relay-bin.000002 Relay_Log_Pos: 867 Relay_Master_Log_File: mysql-bin.000011 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: 987 Relay_Log_Space: 1040 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: 1283316 Master_UUID: 8e4dd4be-2792-11e6-94e7-000c29111e54 Master_Info_File: /data/mysql/mysql3318/data/master.info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0 1 row in set (0.00 sec)
master发现两个Binglog Dump:
at line 1 root@localhost:mysql3316.sock 12:16:15 [(none)]>show processlist\G *************************** 1. row *************************** Id: 15 User: repl Host: 192.168.1.113:35275 db: NULL Command: Binlog Dump Time: 8040 State: Master has sent all binlog to slave; waiting for binlog to be updated Info: NULL *************************** 2. row *************************** Id: 22 User: repl Host: 192.168.1.113:35276 db: NULL Command: Binlog Dump Time: 283 State: Master has sent all binlog to slave; waiting for binlog to be updated Info: NULL *************************** 3. row *************************** Id: 23 User: root Host: localhost db: NULL Command: Query Time: 0 State: init Info: show processlist 3 rows in set (0.00 sec)
在线建从库OK。
注意：gtid环境下使用xtrabackup需要做purge动作（set @@global.gtid_purged='xxx:xxx';）
