MySQL version: 5.6.36
Xtraback Edition: Version 2.4.6
1. Modify the master node MySQL configuration file and restart MySQL
Cat/usr/local/mysql/my.cnf
[Mysqld]
DataDir =/var/lib/mysql
Socket =/tmp/mysql.sock
Binlog-format=row
Log-slave-updates=true
Gtid-mode=on
Enforce-gtid-consistency=true
Log-bin=mysql-bin
expire_logs_days=2
Master-info-repository=table
Relay-log-info-repository=table
Sync-master-info=1
slave-parallel-workers=2
Binlog-checksum=crc32
Master-verify-checksum=1
Slave-sql-verify-checksum=1
Binlog-rows-query-log_events=1
report-port=3306
port=3306
server_id = 1
Sql_mode=no_engine_substitution,strict_trans_tables
2. Use Xtrabackup to fully prepare MySQL and copy the backup set to the slave node
Innobackupex--defaults-file=/usr/local/mysql/my.cnf--slave-info--user=root [email PROTECTED]%ACD--socket=/tmp/ Mysql.sock/root
3. Modify the MySQL configuration file from the node and restart MySQL
Cat/usr/local/mysql/my.cnf
[Mysqld]
DataDir =/var/lib/mysql
Socket =/tmp/mysql.sock
relay-log = Relay-log
relay-log-index = Relay-log.index
binlog-format=row
log-bin=mysql-bin
log-slave-updates=true
gtid-mode=on
enforce-gtid-consistency=true
master-info-repository=table
Relay-log-info-repository=table
sync-master-info=1
slave-parallel-workers=2
BINLOG-CHECKSUM=CRC32
master-verify-checksum=1
slave-sql-verify-checksum=1
Binlog-rows-query-log_events=1
report-port=3306
port=3306
server_id = ten
Sql_mode=no_engine_substitution,strict_trans_tables
4. Recovering a database from a node using Xtrabackup
Innobackupex--defautls-file=/usr/local/mysql/my.cnf--apply-log--redo-only/root/2017-07-10_16-54-29
Innobackupex--defautls-file=/usr/local/mysql/my.cnf--apply-log/root/2017-07-10_16-54-29
Innobackupex--defautls-file=/usr/local/mysql/my.cnf--socket=/tmp/mysql.sock--copy-back/root/2017-07-10_ 16-54-29
5. Create a copy dedicated account on the master node
Mysql> GRANT REPLICATION SLAVE on * * to ' repluser ' @ ' 10.10.45.155 ' identified by ' 123456 ';
mysql> flush Privileges;
6. View slave copy information
Cat/var/lib/mysql/xtrabackup_binlog_pos_innodb
mysql-bin.000003 124800463
7. Add from node to master node
mysql> Change Master to master_host= ' 10.10.45.154 ', master_port=3306,master_user= ' repluser ', master_password= ' [ Email PROTECTED]%ACD ', master_log_file= ' mysql-bin.000003 ', master_log_pos=124800463;
mysql> start slave;
Mysql> show Slave status\g;
1. Row ***************************
Slave_io_state:waiting for Master to send event
master_host:10.10.45.154
Master_user:repluser
master_port:3306
Connect_retry:60
master_log_file:mysql-bin.000005
read_master_log_pos:1073587699
relay_log_file:relay-log.000009
relay_log_pos:1073587202
relay_master_log_file:mysql-bin.000005
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:1073587032
relay_log_space:1073588154
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
Master_uuid:5a842cc5-65fb-11e7-b132-0050569b6c3a
Master_Info_File:mysql.slave_master_info
sql_delay:0
Sql_remaining_delay:null
Slave_sql_running_state:slave have 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:5a842cc5-65fb-11e7-b132-0050569b6c3a:30491-1028116
executed_gtid_set:5a842cc5-65fb-11e7-b132-0050569b6c3a:30491-1028116
auto_position:0
Add MySQL 5.6 from node Slave