MySQL Master master data synchronization

MySQL Master master synchronization and master-slave synchronization are the same principle, but both sides are the master-slave role.

Environment

Operating system version: CentOS7 64-bit
MySQL version: mysql5.6.33
Node 1ip:192.168.1.205 host Name: edu-mysql-01
Node 2ip:192.168.1.206 host Name: edu-mysql-02

MySQL Master Copy Official document: http://dev.mysql.com/doc/refman/5.6/en/replication.html

Attention:
1> master and Slave server operating system version and number of bits to be consistent
2> Master and slave database versions are consistent
3> the data in the master and slave databases is consistent

Configuration

Prior to configuration, refer to "MySQL5.7 Installation and Configuration (YUM)" To install MySQL (note that this article demonstrates the 5.6 version and needs to modify the YUM source in the article to 5.6)

1. Security Configuration

1> Firewall
Add MySQL communication port (default is 3306)

shell> vim /etc/sysconfig/iptables-A-m--NEW-m-p--3306-j ACCEPTshell> service  

Or shut down the firewall

shell> service iptables stop

2> Close SELinux

shell> vi /etc/selinux/configSELINUX=disabled

Change the SELinux value to Disabled

2. Node 1 configuration (192.168.1.205) 2.1 Add Data Synchronization Configuration

shell> vim /etc/my.cnf

Add the following configuration entry in [MYSQLD]:

# Server ID, must be unique, general settings own IPServer_id=205# Replication filtering: Databases that do not need to be backed up (MySQL libraries are not generally synchronized)Binlog-ignore-db=mysql# Turn on the binary log function, the name can be randomly taken, preferably have meaning (such as the project name)Log-bin=edu-mysql-bin# Memory allocated for each session, used to store the cache of binary logs during the transactionBinlog_cache_size=1M# master-slave copy format (mixed,statement,row, default format is statement)Binlog_format=mixed# Number of days the binary log is automatically deleted/expired. The default value is 0, which means that it is not automatically deleted. expire_logs_days=7# # Skip all errors encountered in master-slave replication or specify types of errors to avoid slave-side replication interrupts. # # Example: 1062 error refers to some primary key duplication, 1032 error is due to master and slave database data inconsistencyslave_skip_errors=1062# as a relay log from the serverRelay_log=edu-mysql-relay-bin# log_slave_updates means slave writes the copy event into its own binary loglog_slave_updates=1# PRIMARY Key auto-increment rule to avoid duplication of master-slave synchronization IDAuto_increment_increment=2# self-increment factor (per add2) auto_increment_offset=1# Self-increment offset (from1Start), singular

2.2 Master Configuration

# Restart Services shell> Service mysqld Restart # login to mysqlshell> mysql-uroot-p # Create a database synchronization user and grant the appropriate permissions mysql> grant replica tion slave, replication client on*.*To' Repl '@' 192.168.1.206 'Identified by' root123456 '; # Refresh Authorization table information mysql> Flush privileges;# View the value of position (offset) and file (log file) for Binlog files, required from the machinemysql> Show Master status;+----------------------+----------+--------------+------------------+----------- --------+| File | Position | binlog_do_db | binlog_ignore_db | Executed_gtid_set |+----------------------+----------+--------------+------------------+-------------------+ | edu-mysql-bin.000001 |              120 | |                   MySQL | |+----------------------+----------+--------------+------------------+-------------------+1 row in Set (0.00 sec)

2.3 Slave configuration

# Master_user and Master_password: Execute Grant replication slave on 206 ... The user and password created# Master_log_file and Master_log_pos: Run Show Master status on 206, command execution results corresponding to the values of the file and position fieldsMysql> Change Master to master_host= ' 192.168.1.206 ', Master_User= ' Repl ', master_password= ' root123456 ', Master_port=3306, Master_log_File= ' edu-mysql-bin.000001 ', master_log_pos=439, Master_connect_retry=30;# View status information as a slave nodeMysql> show Slave status\g;************************** * 1. Row * **************************slave_io_state:master_host:192.168.1.206Master_user:replmaster_port:3306connect_retry:30master_log_file:edu-mysql-bin.000001read_master_log_pos:439relay_log_file:edu-mysql-relay-bin.000001relay_log_pos:4relay_master_log_file:edu-mysql-bin.000001Slave_io_running:noSlave_sql_running:no# Omit other configurations ... 

Since the node is not started at this time, the value of Slave_io_state is empty, and the slave_io_running and slave_sql_running threads are no or run.

2.4 Start slave

Note: To create a synchronization account on node 2 before booting, you will not be connected to the master error

# Start from node, start working receive master node send event (all events of database data change)mysql> start slave;# then look at the status of the slave nodeMysql> show Slave status\g;************************** * 1. Row * **************************slave_io_state:waiting for Master to send eventmaster_host:192.168.1.206Master_user:replmaster_port:3306connect_retry:30master_log_file:edu-mysql-bin.000001read_master_log_pos:439relay_log_file:edu-mysql-relay-bin.000002relay_log_pos:287relay_master_log_file:edu-mysql-bin.000001Slave_io_running:yesSlave_sql_running:yesreplicate_do_db:            # ... Omit other configurations

3. Node 2 configuration (192.168.1.206) 3.1 Add data synchronization configuration

shell> vim /etc/my.cnf

Add the following configuration entry in [MYSQLD]:

server_id=206Binlog-ignore-db=MysqlLog-bin=Edu-mysql-binBinlog_cache_size=1Mbinlog_format=Mixedexpire_logs_days=7Slave_skip_errors=1062Relay_log=Edu-mysql-relay-binLog_slave_updates=1#IDSelf-increment from2Start, even Auto_increment_increment=2Auto_increment_offset=2

3.2 Master Configuration

# Restart Services shell> Service mysqld Restart # login to mysqlshell> mysql-uroot-p # to create a database synchronization user and grant the appropriate permissions (only allow REPL users from 192.168.1.20 5 login) Mysql> grant replication Slave, replication client on*.*To' Repl '@' 192.168.1.205 'Identified by' root123456 '; # Refresh Authorization table information mysql> Flush privileges;# View the value of position (offset) and file (log file) for Binlog files, required from the machinemysql> Show Master status;+----------------------+----------+--------------+------------------+----------- --------+| File | Position | binlog_do_db | binlog_ignore_db | Executed_gtid_set |+----------------------+----------+--------------+------------------+-------------------+ | edu-mysql-bin.000001 |              439 | |                   MySQL | |+----------------------+----------+--------------+------------------+-------------------+1 row in Set (0.00 sec)

You can then start the slave Service for Node 1 (205)

3.3 Slave configuration

# show Master status is executed on Master_log_file and master_log_pos:205 nodes, corresponding to the values of file and positionMysql> Change Master to master_host= ' 192.168.1.205 ', Master_User= ' Repl ', master_password= ' root123456 ', Master_port=3306, Master_log_File= ' edu-mysql-bin.000001 ', master_log_pos=120, Master_connect_retry=30; Query OK, 0 rows affected, 2 warnings (0.02 sec) mysql> Show slave status\g;************************** * 1. Row * **************************slave_io_state:master_host:192.168.1.205Master_user:replmaster_port:3306connect_retry:30master_log_file:edu-mysql-bin.000001read_master_log_pos:120relay_log_file:edu-mysql-relay-bin.000001relay_log_pos:4relay_master_log_file:edu-mysql-bin.000001Slave_io_running:noSlave_sql_running:noreplicate_do_db:              #... Omit other configurations

3.4. Start slave

shell> start slave; Query OK, 0 rows affected (0.01 sec) mysql> Show slave status\g;************************** * 1. Row * **************************slave_io_state:waiting for Master to send eventmaster_host:192.168.1.205Master_user:replmaster_port:3306connect_retry:30master_log_file:edu-mysql-bin.000001read_master_log_pos:439relay_log_file:edu-mysql-relay-bin.000002relay_log_pos:287relay_master_log_file:edu-mysql-bin.000001Slave_io_running:yesSlave_sql_running:yesreplicate_do_db:              ... Omit other configurations

4. Verification

# Login 205 Create a databaseshell> mysql-u root-pmysql> CREATE database if not EXISTS mydbdefaultCharacterSetUTF8 collate utf8_general_ci;mysql> CREATE TABLE user (IDint, username varchar ( -), Password varchar ( -));mysql> insert into user values (1,' Yangxin ',' 123456 ');# Here are the actions on the 206 node#1, login 206 to query all libraries, whether to include the MyDB database#2, switch to the MyDB library, include the user table, and have a data#3, insert a piece of data in the Mydb.user table in 206 to see if 205 is synced pastmysql> INSERT into user values (2,' yangxin2 ',' 123456 ')

The detailed procedure is as follows:

Related references:
Master-slave replication, semi-synchronous replication, and primary master replication for operational engineer-required MySQL data

MySQL Master master data synchronization