No downtime xtrabackup creating slave nodes

No downtime xtrabackup creating slave nodes
Master: 10.1.1.17: 3306 slave: 10.1.1.18: 3307slave node configure the Basic Environment: Create a mysql user, install the same version of mysql software, xtrabackup software package, and create a directory, configure Script 1 and create a full backup: master

[mysql@master1 backup]$ innobackupex --defaults-file=/data/mysqldata/3306/my.cnf --user=xtrabk --password='onlybackup' /backup/backup_full/

 

2. Copy and prepare backup Sets

[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=/tmpxtrabackup_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.000011xtrabackup: starting shutdown with innodb_fast_shutdown = 1InnoDB: FTS optimize thread exiting.InnoDB: Starting shutdown...InnoDB: Shutdown completed; log sequence number 1645779150521 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.000011xtrabackup: 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. Create a dedicated account for the replication Environment

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. Configure the slave node initialization parameter file

[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/3307/G'/data/mysqldata/my. cnf [mysql @ master2 ~] $ Mysqld_safe -- defaults-file =/data/mysqldata/3307/my. the cnf & slave node has created fewer tmp and binlog folders [mysql @ master2 3307] $ mkdir tmp [mysql @ master2 3307] $ mkdir binlog [mysql @ master2 3307] $ lsbinlog data lost + found my. cnf mysql-error.log tmp start database [mysql @ master2 3307] $ mysqld_safe -- defaults-file =/data/mysqldata/3307/my. cnf &

 

5. Configure the replication environment of the slave node to view the backup set information:

[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. Check

(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)

 

Insert data to the master:

(system@localhost) [test]> insert into t1 values('jone');Query OK, 1 row affected (0.05 sec)

 

Query in slave: synced

(system@localhost) [test]> select * from t1;+------+| name |+------+| dcw  || jjf  || jone |+------+3 rows in set (0.00 sec)