MySQL 5.6 Non-stop master building (active dual-master based on log-point 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
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
--Main Library Create relay log directory
[Email protected] full]# mkdir-p/data/mysql/relaylog/
[Email protected] full]# chown-r Mysql:mysql/data/mysql/relaylog
--Adjust MY.CNF parameters
[Email protected] ~]# CAT/ETC/MY.CNF
[Client]
Password = Oracle
Port = 3306
Socket =/data/mysql/mysql.sock
[Mysqld]
Server-id=25
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
Log_bin =/data/mysql/binarylog/binlog
Log_bin_index =/data/mysql/binarylog/binlog
Relay-log =/data/mysql/relaylog/relay
Relay-log-index =/data/mysql/relaylog/relay
Relay_log_purge = On
Binlog_format = Mixed
Innodb_data_file_path = Ibdata1:12m:autoextend
Auto_increment_increment = 10
Auto_increment_offset = 1
[MySQL]
Default-character-set = UTF8

Description
A, the main library must be configured parameters
Server-id (master and slave Server-id must be different), Log_bin, Binlog_format, Relay-log, Relay-log-index, Relay_log_purge
Auto-increment-offset, Auto-increment-increment

B, log-slave-updates means, after the relay log executes, whether these changes need to be counted into their own binarylog. When your B server needs to be a master server for another server, it needs to be opened. is the dual-master backup to each other, or multi-master loop backup. We need it here, so open it.

C, Auto-increment-offset, auto-increment-increment two parameters are used to back up each other in a dual-master (multi-master loop). Because each database server can insert data in the same table, if the table has an autogrow primary key, a primary key conflict occurs on multiple servers. The solution to this problem is to make each database's self-increment primary key discontinuous. Say yes, I assume that 10 servers may be required for backup in the future, so auto-increment-increment is set to 10. The auto-increment-offset=1 represents the serial number of this server. Starting from 1, no more than auto-increment-increment. After doing this, the first ID I inserted on this server is 1, the ID of the second row is 11, not 2. (Similarly, the first ID inserted on the second server is 2, the second line is 12, which is explained later) so there is no primary key conflict. We'll show you the effect of this ID later.
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]
Server-id=22
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
Log_bin =/data/mysql/binarylog/binlog
Log_bin_index =/data/mysql/binarylog/binlog
Relay-log =/data/mysql/relaylog/relay
Relay-log-index =/data/mysql/relaylog/relay
Relay_log_purge = On
Auto_increment_increment = 10
Auto_increment_offset = 2
[MySQL]
Default-character-set = UTF8
Description: The parameters that must be configured from the library
Server-id, Log_bin, Relay-log, Relay_log_purge, Auto-increment-offset, Auto-increment-increment,

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!
--View backup Binlog number and cutoff position
[email protected] 2017-06-10_17-50-19]# cat Xtrabackup_binlog_info
binlog.000010 120

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_log_file= ' binlog.000010 ',
master_log_pos=120;
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_log_file: Set the log name of the bin log of the primary server to which you want to connect
Master_log_pos: Set the record location for the bin log of the primary server to which you want to connect
--Start slave status (start listening for msater changes)
mysql> start slave;
Query OK, 0 rows affected, 1 Warning (0.00 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 #主库 IP
Master_user:slave25 # User of Master library replication
master_port:3306 # Main Library mysqld
Connect_retry:60
MASTER_LOG_FILE:BINLOG.000010 #io_thread Read the main library master_log_file
read_master_log_pos:717 # Io_thread Read the main library Master_log_pos
relay_log_file:relay.000002
relay_log_pos:877
relay_master_log_file:binlog.000010 #sql_thread performing the master_log_file of the main library
Slave_io_running:yes # Key, io_thread whether Running
Slave_sql_running:yes # Key, sql_thread whether Running
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:717 #sql_thread performing the Master_log_pos of the main library
relay_log_space:1040
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 # delay from the library
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:0
1 row in Set (0.00 sec)

ERROR:
No query specified

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

--From the library
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| Information_schema |
| Binarylog |
| MySQL |
| Performance_schema |
| Relaylog |
| Repl |
| Test |
+--------------------+
7 Rows in Set (0.00 sec)
Mysql> Use REPL
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 REPL;
+------+
| ID |
+------+
| 1 |
+------+
1 row in Set (0.00 sec) > One Master one from sync success!

##################################################
#至此A到B的复制已经配置完成, the following configuration is copied from B to a. #
##################################################

Declaration > The new Main Library is the original from library (10.219.24.22) New from library to primary repository (10.219.24.25)

11, the new main library to create synchronization users
mysql> GRANT replication Slave on * * to ' slave22 ' @ '% ' identified by ' Oracle ';
Query OK, 0 rows Affected (0.00 sec)

12, new Main Library view Binlog file number and position point
Mysql> Show master status;
+---------------+----------+--------------+------------------+-------------------+
| File | Position | binlog_do_db | binlog_ignore_db | Executed_gtid_set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000004 | 313 | | | |
+---------------+----------+--------------+------------------+-------------------+
1 row in Set (0.00 sec)

13, new from the library to specify the basic information synchronization with the main library
Mysql>
Change Master to
Master_host= ' 10.219.24.22 ',
master_port=3306,
Master_user= ' Slave22 ',
master_password= ' Oracle ',
Master_log_file= ' binlog.000004 ',
master_log_pos=313;
Query OK, 0 rows affected, 2 warnings (0.04 sec)

14, new from the library open slave copy function
mysql> start slave;
Query OK, 0 rows Affected (0.00 sec)
15, new from the library to detect the synchronous replication status
Mysql> show slave status \g;
1. Row ***************************
Slave_io_state:waiting for Master to send event
master_host:10.219.24.22
Master_user:slave22
master_port:3306
Connect_retry:60
master_log_file:binlog.000004
read_master_log_pos:313
relay_log_file:relay.000002
relay_log_pos:280
relay_master_log_file:binlog.000004
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:313
relay_log_space:443
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:22
master_uuid:70023652-4dc7-11e7-9360-000c2944297a
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:0
1 row in Set (0.00 sec)

ERROR:
No query specified

--New test data synchronization state from library
mysql> CREATE DATABASE Mm_repl;
Query OK, 1 row Affected (0.00 sec)
mysql> use MM_REPL;
Database changed
Mysql> CREATE TABLE MM_REPL (ID int auto_increment,name varchar), primary key (ID));
Query OK, 0 rows affected (0.01 sec)
mysql> INSERT into MM_REPL (name) VALUES ("Andy"), ("Taoye");
Query OK, 1 row Affected (0.00 sec)
Mysql> select * from Mm_repl;
+----+-------+
| ID | name |
+----+-------+
| 1 | Andy |
| 11 | Taoye |
+----+-------+
2 rows in Set (0.00 sec)
--New Main library test data synchronization status
Mysql> select * from Mm_repl;
+----+-------+
| ID | name |
+----+-------+
| 1 | Andy |
| 11 | Taoye |
+----+-------+
2 rows in Set (0.00 sec)
mysql> INSERT into MM_REPL (name) VALUES ("Andy"), ("Taoye");
Query OK, 2 rows Affected (0.00 sec)
Records:2 duplicates:0 warnings:0
Mysql> select * from Mm_repl;
+----+-------+
| ID | name |
+----+-------+
| 1 | Andy |
| 11 | Taoye |
| 12 | Andy |
| 22 | Taoye |
+----+-------+
4 rows in Set (0.00 sec)
--New check synchronous replication from library
Mysql> select * from Mm_repl;
+----+-------+
| ID | name |
+----+-------+
| 1 | Andy |
| 11 | Taoye |
| 12 | Andy |
| 22 | Taoye |
+----+-------+
4 rows in Set (0.00 sec) > Master Master Sync Test succeeded

MySQL 5.6 Non-stop master building (active dual-master based on log-point replication)