MySQL 5.6 Non-stop machine master-slave build (one master one from Gtid replication)

Environment Description:
Version 5.6.25-log
Main Library ip:10.219.24.25
From library ip:10.219.24.22
OS Version: CentOS 6.7
Hot standby software installed: Xtrabackup
Firewall is off

Add:

Principle of Master-slave replication: http://blog.csdn.net/zhang123456456/article/details/72972701
GTID Copy principle: http://blog.csdn.net/zhang123456456/article/details/73002216
MySQL 5.6 Installation: http://blog.csdn.net/zhang123456456/article/details/53608554
Xtrabackup Installation: http://blog.csdn.net/zhang123456456/article/details/72836184

One master one from the building process:

1, the main library parameter adjustment
--Stop the main library MySQL
[[Email protected] ~]# service MySQL stop
[Email protected] ~]# Netstat-nltp|grep mysql|grep 3606
--Adjust MY.CNF parameters
[Email protected] ~]# CAT/ETC/MY.CNF
[Client]
Password = Oracle
Port = 3306
Socket =/data/mysql/mysql.sock
[Mysqld]

Port = 3306
Socket =/data/mysql/mysql.sock
Character_set_server = UTF8
Character_set_client = UTF8
Collation-server=utf8_general_ci
Lower_case_table_names = 1
max_connections = 1000
DataDir =/data/mysql

#GTID:
Server-id=25
Gtid_mode=on #开启gtid模式
Enforce_gtid_consistency=on #强制gtid一致性, not supported for specific CREATE table after opening

#binlog
Log_bin =/data/mysql/binarylog/binlog
Log_bin_index =/data/mysql/binarylog/binlog
Binlog_format = Mixed
Log-slave-updates=1

#relay Log
Skip_slave_start=1
[MySQL]
Default-character-set = UTF8
Description: Parameters that the main library must configure
Server-id (master and slave Server-id must be different), Log_bin, Log-slave-updates, Gtid_mode, enforce_gtid_consistency

--Start the main library
[Email protected] ~]# Mysqld_safe--defaults-file=/etc/my.cnf &

2, from the Library parameter adjustment
--Stop MySQL from the library
[[Email protected] ~]# service MySQL stop
[Email protected] ~]# Netstat-nltp|grep mysql|grep 3606
--Adjust MY.CNF parameters
[Email protected] ~]# CAT/ETC/MY.CNF
[Client]
Password = Oracle
Port = 3306
Socket =/data/mysql/mysql.sock
[Mysqld]
Port = 3306
Socket =/data/mysql/mysql.sock
Character_set_server = UTF8
Character_set_client = UTF8
Collation-server=utf8_general_ci
Lower_case_table_names = 1
max_connections = 1000
DataDir =/data/mysql
Read-only = 1

#GTID:
Server-id=22
Gtid_mode=on
Enforce_gtid_consistency=on

#binlog
Log_bin =/data/mysql/binarylog/binlog
Log_bin_index =/data/mysql/binarylog/binlog
Log-slave-updates=1

#relay Log
Relay-log =/data/mysql/relaylog/relay
Relay-log-index =/data/mysql/relaylog/relay
Relay_log_purge = On
Skip_slave_start=1
[MySQL]
Default-character-set = UTF8
Description: The parameters that must be configured from the library
Server-id, Log_bin, Relay-log, Read-only, log-slave-updates

3, the main library backup
--Main Library backup directory
[Email protected] full]# pwd
/xtrabackup/full
--Main Library Innobackupex backup
[Email protected] ~]# Innobackupex--user=root--password=oracle--port=3606/xtrabackup/full/
170610 17:50:23 Backup created in directory '/xtrabackup/full/2017-06-10_17-50-19/'
MySQL binlog position:filename ' binlog.000010 ', position ' 120 '
....
170610 17:50:23 completed ok!

4. Create the same backup directory from the library as the main library
[[[email protected] ~]# mkdir-p/xtrabackup/full
[[email protected] ~]# chown-r mysql:mysql/xtrabackup/full/

5, the main library will back up the SCP to the from library
[[email protected] full]# pwd
/xtrabackup/full
[[email protected] full]# scp-r 2017-06-10_17-50-19 10.219.24.22:/xtrabackup/full
6, view the SCP backup from the library
[[email protected] ~]# cd/xtrabackup/ full/2017-06-10_17-50-19/
[[email protected] 2017-06-10_17-50-19]# ll
Total 12320
-rw-r-----. 1 root Root 419 June 18:01 backup-my.cnf
-rw-r-----. 1 root root 12582912 June 18:01 ibdata1
Drwxr-x---. 2 root root 40 18:01 MySQL
drwxr-x---. 2 root root 4096 June 18:01 Performance_schema
Drwxr-x---. 2 root root 4096 June 18:01 Test
-rw-r-----. 1 root root 18:0 June 18:01 xtrabackup_binlog_info
-rw-r-----. 1 root root 113 June 10 1 xtrabackup_checkpoints
-rw-r-----. 1 root root 482 June 18:01 xtrabackup_info
-rw-r-----. 1 root root 2560 June 1 0 18:01 Xtrabackup_logfile

7. Create a sync user for the main library
mysql> GRANT replication Slave on * * to ' slave25 ' @ '% ' identified by ' Oracle ';
Query OK, 0 rows affected (0.05 sec)

8. Recovering the Master library data from the library
--Rename the original DataDir folder to a new location from the library and create the original folder
[Email protected] ~]# Mv/data/mysql/data/mysqlbak
[Email protected] ~]# mkdir-p/data/mysql
--Innobackupex Apply-log
[Email protected] ~]# Innobackupex--apply-log--user=oracle \
--password=oracle--port=3606/xtrabackup/full/2017-06-10_17-50-19/
--Innobackupex copy recovered files to the original data location
[Email protected] mysql]# Innobackupex--defaults-file=/etc/my.cnf--user=root \
--copy-back/xtrabackup/full/2017-06-10_17-50-19/

170610 18:25:11 completed ok!
--Create Binlog directory and Relaylog directory and empower
[Email protected] ~]# mkdir-p/data/mysql/binarylog
[Email protected] ~]# mkdir-p/data/mysql/relaylog/
[Email protected] mysql]# chown-r mysql:mysql/data/mysql

9, from the library configuration and detection
--Start from library
[Email protected] mysql]# Mysqld_safe--defaults-file=/etc/my.cnf &
--Specify the basic information to synchronize with the main library from the library
Mysql>
Change Master to
Master_host= ' 10.219.24.25 ',
master_port=3306,
Master_user= ' Slave25 ',
master_password= ' Oracle ',
Master_auto_position=1;
Query OK, 0 rows affected, 2 warnings (0.04 sec)

Parameter explanation:
Master_host: Set the IP address of the primary server to connect to
Master_user: Set the user name of the primary server to connect to
Master_password: Set the password for the primary server to connect to
Master_auto_position:gtid mode, replication based on transaction ID
--Start slave status (start listening for msater changes)
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)
--View the status of the slave.
Mysql> show slave status \g;
1. Row ***************************
Slave_io_state:waiting for Master to send event
master_host:10.219.24.25
Master_user:slave25
master_port:3306
Connect_retry:60
master_log_file:binlog.000016
read_master_log_pos:151
relay_log_file:relay.000021
relay_log_pos:355
relay_master_log_file:binlog.000016
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:151
relay_log_space:518
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:25
master_uuid:29d68531-4cf9-11e7-8e1f-000c297c4100
Master_info_file:/data/mysql/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:
Executed_gtid_set:
Auto_position:1
1 row in Set (0.00 sec)

ERROR:
No query specified

10, master-Slave synchronization check
--Main Library
mysql> CREATE DATABASE Gtid;
Query OK, 1 row Affected (0.00 sec)
Mysql> Use Gtid
Database changed
Mysql> CREATE TABLE Gtid (id int);
Query OK, 0 rows affected (0.50 sec)
mysql> INSERT INTO Gtid values (1);
Query OK, 1 row Affected (0.00 sec)

--From the library
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| Information_schema |
| Binarylog |
| Gtid |
| MySQL |
| Performance_schema |
| Relaylog |
| Repl |
| Test |
+--------------------+
8 rows in Set (0.00 sec)
Mysql> Use Gtid
Reading table information for completion of table and column names
Can turn off this feature to get a quicker startup with-a
Database changed
Mysql> select * from Gtid;
+------+
| ID |
+------+
| 1 |
+------+
1 row in Set (0.00 sec) > Gtid synchronized successfully!

MySQL 5.6 Non-stop machine master-slave build (one master one from Gtid replication)