No downtime xtrabackup create slave node, xtrabackupslave

No downtime xtrabackup create slave node, xtrabackupslave
Master: 10.1.1.17: 3306 slave: 10.1.1.18: 3307slave node complete basic environment configuration: Create mysql users, install mysql software of the same version, xtrabackup software package, create directory, configure script
1. 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 the backup set [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,200 9 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: 2048 M: autoextendxtrabackup: innodb_log_group_home_dir =. /xtrabackup: innodb_log_files_in_group = 1 xtrabackup: innodb_log_file_size = 2097152 xtrabackup: Temporary instance for recovery is set as followings. xtrabackup: innodb_data_home_dir =. /xtrabackup: innodb_data_file_path = ibdata1: 2048 M: autoextendxtrabackup: innodb_log_group_home_dir =. /xtrabackup: innodb_log_files_in_group = 1 xtrabackup: in Nodb_log_file_size = 2097152 xtrabackup: Starting InnoDB instance for recovery. usage: Using 104857600 bytes for buffer pool (set by -- use-memory parameter) InnoDB: The InnoDB memory heap is disabledInnoDB: Mutexes and cannot use GCC atomic builtinsInnoDB: Compressed tables use zlib 1.2.3InnoDB: CPU supports crc32 instructionsInnoDB: Initializing buffer pool, size = 100.0 MInnoDB: Completed ini Tialization 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. 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 = 1 InnoDB: 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: 2048 M: autoextendxtrabackup: innodb_log_group_home_dir =. /xtrabackup: innodb_log_files_in_group = 3 xtrabackup: innodb_log_file_size = 268435456 xtrabackup: Temporary instance for recovery is set as followings. xtrabackup: innodb_data_home_dir =. /xtrabackup: innodb_data_file_path = ibdata1: 2048 M: autoextendxtrabackup: innodb_log_group_home_dir =. /xtrabackup: innodb_log_files_in_group = 3 xtrabackup: innodb_log_file_size = 268435456 xtrabackup: Starting InnoDB instance for recovery. usage: Using 104857600 bytes for buffer pool (set by -- use-memory parameter) InnoDB: The InnoDB memory heap is disabledInnoDB: Mutexes and cannot use GCC atomic builtinsInnoDB: Compressed tables use zlib 1.2.3InnoDB: CPU supports crc32 instructionsInnoDB: Initializing buffer pool, size = 100.0 MInnoDB: Completed initialization of buffer poolInnoDB: Setting log file. /ib_logfile101 size to 256 MBInnoDB: Progress in MB: 100 200 InnoDB: Setting log file. /ib_logfile1 size to 256 MBInnoDB: Progress in MB: 100 200 InnoDB: Setting log file. /ib_logfile2 size to 256 MBInnoDB: Progress in MB: 100 200 InnoDB: Renaming log file. /ib_logfile101. /ib_logfile0InnoDB: New log files created, LSN = 1645779 InnoDB: 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 = 1 InnoDB: 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 validate against ibdata1 test against mysql connector [mysql @ master2 3307] $ ls data/test/j1.frm j1.ibd j2.MYD j2.MYI j2.frm t1.frm t1.ibd

3. Create a master account dedicated to the replication environment: (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 slave node replication environment 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 rows: mysql-bin.000011 rows: Yes rows: Keys: failed: counters: 0 Last_Error: Skip_Counter: 0 condition: 1024 Relay_Log_Space: 668 Until_Condition: None Until_Log_File: Failed: 0 condition: No condition: Master_SSL_Cert: Keys: Master_SSL_Key: usage: No usage: 0 Last_IO_Error: Usage: 0 Last_ SQL _Error: Usage: Master_Server_Id: 1 Master_UUID: bd8ce1ca-edb7-11e4-964f-000c291b94c4 Master_Info_File:/data/mysqldata/3307/data/master.info SQL _Delay: 0 usage: NULL layout: Slave has read all relay log; waiting for the slave I/O thread to update it Master_Retry_Count: 86400 Master_Bind: Usage: Master_SSL_Crl: Master_SSL_Crlpath: Usage: 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: synchronized (system @ localhost) [test]> select * from t1; + ------ + | name | + ------ + | dcw | jjf | jone | + ------ + 3 rows in set (0.00 sec)