Build MySQL servers that are primarily slave-to-peer

IP address of Node1:192.168.1.254
IP address of Node2:192.168.1.253
The operating system is Rhel7.3,mysql version 5.7.18
1. Modify the node1 MySQL configuration file
#注意server_id字段一定不能一样, because the previous configuration server_id is already configured and its value is unique, it is no longer configured here
Modify the MySQL configuration file for Node1
# server ID, must be unique, general settings own IP
server_id=254
# 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=lamp-mysql-bin
# Memory allocated for each session, used to store the cache of binary logs during the transaction
binlog_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 inconsistency
slave_skip_errors=1062
# as a relay log from the server
Relay_log=lamp-mysql-relay-bin
# log_slave_updates means slave writes the copy event into its own binary log
Log_slave_updates=1
# PRIMARY Key auto-increment rule to avoid duplication of master-slave synchronization ID
auto_increment_increment=2 # Self-amplification factor (modified according to MySQL server number, in this case 2 MySQL servers)
Auto_increment_offset=1 # Self-increment offset (starting from 1)


2. Modify the node2 MySQL configuration file
# 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=lamp-mysql-bin
# Memory allocated for each session, used to store the cache of binary logs during the transaction
binlog_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 inconsistency
slave_skip_errors=1062
# as a relay log from the server
Relay_log=lamp-mysql-relay-bin
# log_slave_updates means slave writes the copy event into its own binary log
Log_slave_updates=1
# PRIMARY Key auto-increment rule to avoid duplication of master-slave synchronization ID
auto_increment_increment=2 # Self-amplification factor (modified according to MySQL server number, in this case 2 MySQL servers)
auto_increment_offset=2 # Self-increment offset (starting from 2)

3. Restart MySQL service on Node1 and Node2 respectively
Restarting the MySQL service on Node1
[Email protected] ~]# systemctl restart mysqld
Restarting the MySQL service on Node2
[Email protected] ~]# systemctl restart mysqld

4. Enter the MySQL database on Node1 and configure the account to allow synchronization from Node2
[Email protected] ~]# mysql-u root-p
Enter Password:
Welcome to the MySQL Monitor. Commands End With; or \g.
Your MySQL Connection ID is 3
Server Version:5.7.18-log Source Distribution

Copyright (c), Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of the Oracle Corporation and/or its
Affiliates. Other names trademarks of their respective
Owners.

Type ' help ', ' or ' \h ' for help. Type ' \c ' to clear the current input statement.

mysql> grant replication Slave, replication Client on *. * to ' repl ' @ ' 192.168.1.253 ' identified by ' [email protected] ';
mysql> flush Privileges;
Query OK, 0 rows affected (0.06 sec)

5. View and record the value of the position and file of the Binlog file on Node1, and the configuration will be used when the slave machine
Mysql> Show master status;
+-----------------------+----------+--------------+------------------+-------------------+
| File | Position | binlog_do_db | binlog_ignore_db | Executed_gtid_set |
+-----------------------+----------+--------------+------------------+-------------------+
|      lamp-mysql-bin.000001 |              631 | |                   MySQL | |
+-----------------------+----------+--------------+------------------+-------------------+
1 row in Set (0.00 sec)

Mysql>

6, on Node2 Node1 as their primary server, and open slave state.
mysql> Change Master to master_host= ' 192.168.1.254 ', master_user= ' repl ', master_password= ' [email protected] ', master_port=3306, master_log_file= ' lamp-mysql-bin.000001 ', master_log_pos=631, master_connect_retry=30;
mysql> start slave;

7. Check your slave status on the Node2.
Mysql> show Slave status\g;

Slave_io_running:yes
Slave_sql_running:yes
All of the above two parameters are all Yes states


8. Create an account on Node2 that allows synchronization from Node1
[Email protected] ~]# mysql-u root-p
Enter Password:
Welcome to the MySQL Monitor. Commands End With; or \g.
Your MySQL Connection ID is 3
Server Version:5.7.18-log Source Distribution

Copyright (c), Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of the Oracle Corporation and/or its
Affiliates. Other names trademarks of their respective
Owners.

Type ' help ', ' or ' \h ' for help. Type ' \c ' to clear the current input statement.

mysql> grant replication Slave, replication Client on *. * to ' repl ' @ ' 192.168.1.254 ' identified by ' [email protected] ';
mysql> flush Privileges;


9. View and record the position and file values of the Binlog file on Node2.
Mysql> Show master status;
+-----------------------+----------+--------------+------------------+-------------------+
| File | Position | binlog_do_db | binlog_ignore_db | Executed_gtid_set |
+-----------------------+----------+--------------+------------------+-------------------+
|      lamp-mysql-bin.000001 |              474 | |                   MySQL | |
+-----------------------+----------+--------------+------------------+-------------------+


10, on Node1 Node2 as their primary server, and open slave state.
mysql> Change Master to master_host= ' 192.168.1.253 ', master_user= ' repl ', master_password= ' [email protected] ', master_port=3306, master_log_file= ' lamp-mysql-bin.000001 ', master_log_pos=474, master_connect_retry=30;
mysql> start slave;

11. Check your slave status on the Node1.
Mysql> show Slave status\g;

Slave_io_running:yes
Slave_sql_running:yes
All of the above two parameters are all Yes states



12, create a database in Node1 or node2 any database, will automatically sync to another database
mysql> CREATE DATABASE Netser;
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| Information_schema |
| MySQL |
| Netser |
| Performance_schema |
| SYS |
+--------------------+
5 rows in Set (0.09 sec)

In another node, view the database and see exactly the same data information
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| Information_schema |
| MySQL |
| Netser |
| Performance_schema |
| SYS |
+--------------------+
5 rows in Set (0.06 sec)

This article is from the "high-tech Library" blog, please be sure to keep this source http://gaopengju.blog.51cto.com/12830710/1947590

Build MySQL servers that are primarily slave-to-peer