不停機xtrabackup建立slave節點，xtrabackupslave

不停機xtrabackup建立slave節點，xtrabackupslave
master:10.1.1.17:3306slave:10.1.1.18:3307slave節點完成基礎環境的配置：建立好mysql使用者，裝好相同版本的mysql軟體，xtrabackup軟體包，建立好目錄，配置好指令碼
1、建立完整備份：master[mysql@master1 backup]$ innobackupex --defaults-file=/data/mysqldata/3306/my.cnf --user=xtrabk --password='onlybackup' /backup/backup_full/
2、複製和準備備份組[mysql@master1 backup_full]$ scp -r /backup/backup_full/2015-05-21_01-31-19/* 10.1.1.18:/data/mysqldata/3307[mysql@master2 3307]$ innobackupex --apply-log /data/mysqldata/3307/data/
InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oyand Percona Ireland Ltd 2009-2012.  All Rights Reserved.
This software is published underthe GNU GENERAL PUBLIC LICENSE Version 2, June 1991.
IMPORTANT: Please check that the apply-log run completes successfully.           At the end of a successful apply-log run innobackupex           prints "completed OK!".


150521 01:51:29  innobackupex: Starting ibbackup with command: xtrabackup_56  --defaults-file="/data/mysqldata/3307/data/backup-my.cnf"  --defaults-group="mysqld" --prepare --target-dir=/data/mysqldata/3307/data --tmpdir=/tmp
xtrabackup_56 version 2.0.7 for MySQL server 5.6.10 Linux (x86_64) (revision id: 552)xtrabackup: cd to /data/mysqldata/3307/dataxtrabackup: This target seems to be not prepared yet.xtrabackup: xtrabackup_logfile detected: size=2097152, start_lsn=(1645755)xtrabackup: Temporary instance for recovery is set as followings.xtrabackup:   innodb_data_home_dir = ./xtrabackup:   innodb_data_file_path = ibdata1:2048M:autoextendxtrabackup:   innodb_log_group_home_dir = ./xtrabackup:   innodb_log_files_in_group = 1xtrabackup:   innodb_log_file_size = 2097152xtrabackup: Temporary instance for recovery is set as followings.xtrabackup:   innodb_data_home_dir = ./xtrabackup:   innodb_data_file_path = ibdata1:2048M:autoextendxtrabackup:   innodb_log_group_home_dir = ./xtrabackup:   innodb_log_files_in_group = 1xtrabackup:   innodb_log_file_size = 2097152xtrabackup: Starting InnoDB instance for recovery.xtrabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter)InnoDB: The InnoDB memory heap is disabledInnoDB: Mutexes and rw_locks use GCC atomic builtinsInnoDB: Compressed tables use zlib 1.2.3InnoDB: CPU supports crc32 instructionsInnoDB: Initializing buffer pool, size = 100.0MInnoDB: Completed initialization of buffer poolInnoDB: Highest supported file format is Barracuda.InnoDB: The log sequence numbers 1639650 and 1639650 in ibdata files do not match the log sequence number 1645755 in the ib_logfiles!InnoDB: Database was not shutdown normally!InnoDB: Starting crash recovery.InnoDB: Reading tablespace information from the .ibd files...InnoDB: Restoring possible half-written data pagesInnoDB: from the doublewrite buffer...InnoDB: Last MySQL binlog file position 0 812, file name mysql-bin.000011InnoDB: 128 rollback segment(s) are active.InnoDB: Waiting for purge to start2015-05-21 01:51:31 4a924940  InnoDB: Warning: table 'mysql/innodb_index_stats'InnoDB: in InnoDB data dictionary has unknown flags 50.2015-05-21 01:51:31 4a924940  InnoDB: Warning: table 'mysql/innodb_table_stats'InnoDB: in InnoDB data dictionary has unknown flags 50.InnoDB: 1.2.10 started; log sequence number 1645755
[notice (again)]  If you use binary log and don't use any hack of group commit,  the binary log position seems to be:InnoDB: Last MySQL binlog file position 0 812, file name mysql-bin.000011
xtrabackup: starting shutdown with innodb_fast_shutdown = 1InnoDB: FTS optimize thread exiting.InnoDB: Starting shutdown...InnoDB: Shutdown completed; log sequence number 1645779
150521 01:51:33  innobackupex: Restarting xtrabackup with command: xtrabackup_56  --defaults-file="/data/mysqldata/3307/data/backup-my.cnf"  --defaults-group="mysqld" --prepare --target-dir=/data/mysqldata/3307/data --tmpdir=/tmpfor creating ib_logfile*
xtrabackup_56 version 2.0.7 for MySQL server 5.6.10 Linux (x86_64) (revision id: 552)xtrabackup: cd to /data/mysqldata/3307/dataxtrabackup: This target seems to be already prepared.xtrabackup: notice: xtrabackup_logfile was already used to '--prepare'.xtrabackup: Temporary instance for recovery is set as followings.xtrabackup:   innodb_data_home_dir = ./xtrabackup:   innodb_data_file_path = ibdata1:2048M:autoextendxtrabackup:   innodb_log_group_home_dir = ./xtrabackup:   innodb_log_files_in_group = 3xtrabackup:   innodb_log_file_size = 268435456xtrabackup: Temporary instance for recovery is set as followings.xtrabackup:   innodb_data_home_dir = ./xtrabackup:   innodb_data_file_path = ibdata1:2048M:autoextendxtrabackup:   innodb_log_group_home_dir = ./xtrabackup:   innodb_log_files_in_group = 3xtrabackup:   innodb_log_file_size = 268435456xtrabackup: Starting InnoDB instance for recovery.xtrabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter)InnoDB: The InnoDB memory heap is disabledInnoDB: Mutexes and rw_locks use GCC atomic builtinsInnoDB: Compressed tables use zlib 1.2.3InnoDB: CPU supports crc32 instructionsInnoDB: Initializing buffer pool, size = 100.0MInnoDB: Completed initialization of buffer poolInnoDB: Setting log file ./ib_logfile101 size to 256 MBInnoDB: Progress in MB: 100 200InnoDB: Setting log file ./ib_logfile1 size to 256 MBInnoDB: Progress in MB: 100 200InnoDB: Setting log file ./ib_logfile2 size to 256 MBInnoDB: Progress in MB: 100 200InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0InnoDB: New log files created, LSN=1645779InnoDB: Highest supported file format is Barracuda.InnoDB: 128 rollback segment(s) are active.InnoDB: Waiting for purge to start2015-05-21 01:52:00 4a5b4940  InnoDB: Warning: table 'mysql/innodb_index_stats'InnoDB: in InnoDB data dictionary has unknown flags 50.2015-05-21 01:52:00 4a5b4940  InnoDB: Warning: table 'mysql/innodb_table_stats'InnoDB: in InnoDB data dictionary has unknown flags 50.InnoDB: 1.2.10 started; log sequence number 1646092
[notice (again)]  If you use binary log and don't use any hack of group commit,  the binary log position seems to be:InnoDB: Last MySQL binlog file position 0 812, file name mysql-bin.000011
xtrabackup: starting shutdown with innodb_fast_shutdown = 1InnoDB: FTS optimize thread exiting.InnoDB: Starting shutdown...InnoDB: Shutdown completed; log sequence number 1646102150521 01:52:01  innobackupex: completed OK!
[mysql@master2 3307]$ ls data/backup-my.cnf  ib_logfile2  performance_schema  xtrabackup_binlog_info        xtrabackup_logfileib_logfile0    ibdata1      test                xtrabackup_binlog_pos_innodbib_logfile1    mysql        xtrabackup_binary   xtrabackup_checkpoints[mysql@master2 3307]$ ls data/test/j1.frm  j1.ibd  j2.MYD  j2.MYI  j2.frm  t1.frm  t1.ibd

3、建立複製環境專用賬戶master:(system@localhost) [(none)]> grant replication slave on *.* to 'repl'@'10.1.1.%' identified by 'replsafe';Query OK, 0 rows affected (0.09 sec)
(system@localhost) [(none)]> select user,host from mysql.user;+--------+-----------+| user   | host      |+--------+-----------+| dcw    | %         || repl   | 10.1.1.%  || test   | 10.1.1.%  || system | localhost || xtrabk | localhost |+--------+-----------+
4、配置slave節點初始化參數檔案[mysql@master1 3306]$ scp my.cnf 10.1.1.18:/data/mysqldata/3307[mysql@master2 ~]$ vim /data/mysqldata/3307/my.cnfserver-id=2[mysql@master2 ~]$ sed -i 's/3306/3307/g' /data/mysqldata/3307/my.cnf[mysql@master2 ~]$ mysqld_safe --defaults-file=/data/mysqldata/3307/my.cnf &slave節點少建立了tmp和binlog兩個檔案夾[mysql@master2 3307]$ mkdir tmp[mysql@master2 3307]$ mkdir binlog[mysql@master2 3307]$ lsbinlog  data  lost+found  my.cnf  mysql-error.log  tmp啟動資料庫[mysql@master2 3307]$ mysqld_safe --defaults-file=/data/mysqldata/3307/my.cnf &


5、配置slave節點複製環境查看備份組資訊：[mysql@master2 3307]$ cat data/xtrabackup_binlog_infomysql-bin.000011        812[mysql@master2 3307]$ mysql -usystem -p'5ienet.com' -S /data/mysqldata/3307/mysql.sock(system@localhost) [(none)]> change master to master_host='10.1.1.17',master_port=3306,master_user='repl',master_password='replsafe',master_log_file='mysql-bin.000011',master_log_pos=812;Query OK, 0 rows affected, 2 warnings (0.09 sec)(system@localhost) [(none)]> start slave;Query OK, 0 rows affected (0.14 sec)
6、檢查(system@localhost) [(none)]> start slave;Query OK, 0 rows affected (0.14 sec)
(system@localhost) [(none)]> show slave status\G*************************** 1. row ***************************               Slave_IO_State: Waiting for master to send event                  Master_Host: 10.1.1.17                  Master_User: repl                  Master_Port: 3306                Connect_Retry: 60              Master_Log_File: mysql-bin.000011          Read_Master_Log_Pos: 1024               Relay_Log_File: mysql-relay-bin.000002                Relay_Log_Pos: 495        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: 1024              Relay_Log_Space: 668              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: 0Master_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                  Master_UUID: bd8ce1ca-edb7-11e4-964f-000c291b94c4             Master_Info_File: /data/mysqldata/3307/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: 01 row in set (0.03 sec)

master：(system@localhost) [(none)]> use test;Database changed(system@localhost) [test]> show tables;+----------------+| Tables_in_test |+----------------+| j1             || j2             || t1             |+----------------+3 rows in set (0.00 sec)
(system@localhost) [test]> select * from t1;+------+| name |+------+| dcw  || jjf  |+------+2 rows in set (0.11 sec)
slave：(system@localhost) [(none)]> use test;Database changed(system@localhost) [test]> show tables;+----------------+| Tables_in_test |+----------------+| j1             || j2             || t1             |+----------------+3 rows in set (0.03 sec)
(system@localhost) [test]> select * from t1;+------+| name |+------+| dcw  || jjf  |+------+2 rows in set (0.05 sec)
master插入資料：(system@localhost) [test]> insert into t1 values('jone');Query OK, 1 row affected (0.05 sec)
slave中查詢：同步過來了(system@localhost) [test]> select * from t1;+------+| name |+------+| dcw  || jjf  || jone |+------+3 rows in set (0.00 sec)