Set up MySQL servers that are master and slave to each other
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 MySQL configuration file of node1
# Note that the server_id field must not be the same. Since the previous configuration server_id has been configured and its value is unique, it will not be configured here.
Modify the mysql configuration file for node1
# the ID of the server must be unique. Generally, set your own IP
Server_id=254
# replication filtering: databases that do not need to be backed up (MySQL libraries are generally not synchronized)
Binlog-ignore-db=mysql
# enable binary log function. You can take any name you want, preferably with meaning (such as project name)
Log-bin=lamp-mysql-bin
# memory allocated for each session, cache used to store binary logs during transactions
Binlog_cache_size=1M
# Master-slave copy format (mixed,statement,row, default format is statement)
Binlog_format=mixed
# the number of days that binary logs are automatically deleted / expired. The default value is 0, which means that it is not deleted automatically.
Expire_logs_days=7
# # skip all errors or specified types of errors encountered in master-slave replication to avoid interruption of replication on slave.
# # for example, 1062 error refers to duplicate primary keys, and 1032 error is due to inconsistency between master and slave database data
Slave_skip_errors=1062
# as a relay log from the server
Relay_log=lamp-mysql-relay-bin
# log_slave_updates means that slave writes replication events to its own binary log
Log_slave_updates=1
# Primary key self-increasing rule to avoid repetition of master-slave synchronous ID
Auto_increment_increment=2 # self-increment factor (modified according to the number of MySQL servers, in this case 2 MySQL servers)
Auto_increment_offset=1 # self-increasing offset (starting at 1)
2. Modify the MySQL configuration file of node2
# replication filtering: databases that do not need to be backed up (MySQL libraries are generally not synchronized)
Binlog-ignore-db=mysql
# enable binary log function. You can take any name you want, preferably with meaning (such as project name)
Log-bin=lamp-mysql-bin
# memory allocated for each session, cache used to store binary logs during transactions
Binlog_cache_size=1M
# Master-slave copy format (mixed,statement,row, default format is statement)
Binlog_format=mixed
# the number of days that binary logs are automatically deleted / expired. The default value is 0, which means that it is not deleted automatically.
Expire_logs_days=7
# # skip all errors or specified types of errors encountered in master-slave replication to avoid interruption of replication on slave.
# # for example, 1062 error refers to duplicate primary keys, and 1032 error is due to inconsistency between master and slave database data
Slave_skip_errors=1062
# as a relay log from the server
Relay_log=lamp-mysql-relay-bin
# log_slave_updates means that slave writes replication events to its own binary log
Log_slave_updates=1
# Primary key self-increasing rule to avoid repetition of master-slave synchronous ID
Auto_increment_increment=2 # self-increment factor (modified according to the number of MySQL servers, in this case 2 MySQL servers)
Auto_increment_offset=2 # self-increasing offset (starting at 2)
3. Restart the MySQL service on node1 and node2 respectively
Restart the MySQL service on node1
[root@node1 ~] # systemctl restart mysqld
Restart the MySQL service on node2
[root@node2 ~] # systemctl restart mysqld
4. Enter the MySQL database on node1 and configure accounts that allow synchronization from node2.
[root@node1] # 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) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
Affiliates. Other names may be 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' repl@123'
Mysql > flush privileges
Query OK, 0 rows affected (0.06 sec)
5. View and record the position and File values of the binlog file on node1, which will be used when configuring the slave.
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. Use node1 as your master server on node2, and turn on slave status.
Mysql > change master to master_host='192.168.1.254',master_user='repl', master_password='repl@123', 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 node2.
Mysql > show slave status\ G
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
The above two parameters should all be Yes status.
8. Create an account on node2 that allows synchronization from node1.
[root@node2] # 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) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
Affiliates. Other names may be 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' repl@123'
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. Use node2 as your master server on node1, and turn on slave status.
Mysql > change master to master_host='192.168.1.253',master_user='repl', master_password='repl@123', master_port=3306, master_log_file='lamp-mysql-bin.000001', master_log_pos=474, master_connect_retry=30
Mysql > start slave
Check your slave status on node1.
Mysql > show slave status\ G
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
The above two parameters should all be Yes status.
12. Create a database in any database of node1 or node2 and it will be automatically synchronized 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)
On another node, if you look at the database, you will see exactly the same data information.
Mysql > show databases
+-+
| | Database |
+-+
| | information_schema |
| | mysql |
| | netser |
| | performance_schema |
| | sys |
+-+
5 rows in set (0.06 sec)
