MySQL master data synchronization configuration

The principle of master-slave synchronization for MySQL is the same as that for master-slave synchronization.

Environment

Operating system version: 64-bit CentOS7

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-slave copy official documentation: http://dev.mysql.com/doc/refman/5.6/en/replication.html

Note:

1> the operating system version and number of digits of the master and slave servers must be consistent.

2> the Master and Slave databases must be of the same version.

3> data in Master and Slave databases must be consistent

Configuration

Before configuration, install MySQL by referring to MySQL5.7 installation and configuration (YUM). (note that MySQL 5.6 is demonstrated in this article. You need to modify the yum source in this article to 5.6)

1. Security configuration

1> firewall

Add mysql communication port (3306 by default)

Shell> vim/etc/sysconfig/iptables
-A input-m state -- state NEW-m tcp-p tcp -- dport 3306-j ACCEPT
Shell> service iptables restart
Or disable the firewall.

Shell> service iptables stop
2> disable selinux

Shell> vi/etc/selinux/config
SELINUX = disabled
Change SELINUX value to disabled

2. Configure node 1 (192.168.1.205)

2.1 Add data synchronization configuration

Shell> vim/etc/my. cnf
Add the following configuration items to [mysqld:

# The server ID, which must be unique. Generally, set your own IP address.
Server_id = 205
# Copy filtering: databases that do not need to be backed up (MySQL databases are generally not synchronized)
Binlog-ignore-db = mysql
# Enable the binary log function. The name can be retrieved as needed. It is best to have a 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 transaction process
Binlog_cache_size = 1 M
# Format of master-slave replication (mixed, statement, row, default format: statement)
Binlog_format = mixed
# Number of days when binary logs are automatically deleted/expired. The default value is 0, indicating that it is not automatically deleted.
Expire_logs_days = 7
# Skip all errors encountered in master-slave replication or errors of the specified type to avoid server load balancer replication interruption.
# For example, the error 1062 indicates that some primary keys are duplicated. The error 1032 indicates that the data in the master and slave databases is inconsistent.
Slave_skip_errorrs = 1062
# Relay logs when used as slave servers
Relay_log = edu-mysql-relay-bin
# Log_slave_updates indicates that slave writes the replication event to its own binary log.
Log_slave_updates = 1
# Primary key auto-increment rules to avoid duplicate master-slave synchronization IDs
Auto_increment_increment = 2 # Auto-incrementing factor (2 each time)
Auto_increment_offset = 1 # Auto-increment offset (starting from 1), singular
2.2 Master configuration

# Restart the service first
Shell> service mysqld restart
# Log on to mysql
Shell> mysql-uroot-p
# Create a database synchronization user and grant corresponding permissions
Mysql> grant replication slave, replication client on *. * to 'repl' @ '192. 168.1.206 'identified by 'root123456 ';
# Refresh the authorization table information
Mysql> flush privileges;
# View the position (offset) and File (log File) values of the binlog File, which must be used on the slave machine
Mysql> 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: the user and password created by running grant replication slave... on 206
# Master_log_file and master_log_pos: run show master status on 206. The command execution result corresponds to the values of the File and Position fields.
Mysql> change master to master_host = '2017. 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;
# Viewing status information of slave nodes
Mysql> show slave status \ G;
* *************************** 1. row ***************************
Slave_IO_State:
Master_Host: 192.168.1.206
Master_User: repl
Master_Port: 3306
Connect_Retry: 30
Master_Log_File: edu-mysql-bin.000001
Read_Master_Log_Pos: 439
Relay_Log_File: edu-mysql-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: edu-mysql-bin.000001
Slave_IO_Running: No
Slave_ SQL _Running: No
# Omit other configurations...
Because the Slave node is not started yet, the value of Slave_IO_State is null, and the No threads of Slave_IO_Running and Slave_ SQL _Running indicate that the Slave node is not running.

2.4 start Slave

Note: You must create a synchronization account on Node 2 before starting the account. Otherwise, the error "cannot connect to the master" will be reported.

# Start the Slave node and start to receive the event sent by the master node (all events of database data change)
Mysql> start slave;
# Check the status of the slave node again
Mysql> show slave status \ G;
* *************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.206
Master_User: repl
Master_Port: 3306
Connect_Retry: 30
Master_Log_File: edu-mysql-bin.000001
Read_Master_Log_Pos: 439
Relay_Log_File: edu-mysql-relay-bin.000002
Relay_Log_Pos: 287
Relay_Master_Log_File: edu-mysql-bin.000001
Slave_IO_Running: Yes
Slave_ SQL _Running: Yes
Replicate_Do_DB:
#... Omit other configurations
3. Configure node 2 (192.168.1.206)

3.1 add data synchronization configuration

Shell> vim/etc/my. cnf
Add the following configuration items to [mysqld:

Server_id = 206
Binlog-ignore-db = mysql
Log-bin = edu-mysql-bin
Binlog_cache_size = 1 M
Binlog_format = mixed
Expire_logs_days = 7
Slave_skip_errorrs = 1062
Relay_log = edu-mysql-relay-bin
Log_slave_updates = 1
# ID auto-increment starts from 2, double
Auto_increment_increment = 2
Auto_increment_offset = 2
3.2 Master configuration

# Restart the service first
Shell> service mysqld restart
# Log on to mysql
Shell> mysql-uroot-p
# Create a database synchronization user and grant the corresponding permissions (only allow the repl user to log on from 192.168.1.205)
Mysql> grant replication slave, replication client on *. * to 'repl' @ '192. 168.1.205 'identified by 'root123456 ';
# Refresh the authorization table information
Mysql> flush privileges;
# View the position (offset) and File (log File) values of the binlog File, which must be used on the slave machine
Mysql> 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)
In this case, you can start the slave service of node 1 (205).

3.3 Slave configuration

# Master_log_file and master_log_pos: execute show master status on node 205; values corresponding to File and position
Mysql> change master to master_host = '2017. 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.205
Master_User: repl
Master_Port: 3306
Connect_Retry: 30
Master_Log_File: edu-mysql-bin.000001
Read_Master_Log_Pos: 120
Relay_Log_File: edu-mysql-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: edu-mysql-bin.000001
Slave_IO_Running: No
Slave_ SQL _Running: No
Replicate_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 event
Master_Host: 192.168.1.205
Master_User: repl
Master_Port: 3306
Connect_Retry: 30
Master_Log_File: edu-mysql-bin.000001
Read_Master_Log_Pos: 439
Relay_Log_File: edu-mysql-relay-bin.000002
Relay_Log_Pos: 287
Relay_Master_Log_File: edu-mysql-bin.000001
Slave_IO_Running: Yes
Slave_ SQL _Running: Yes
Replicate_Do_DB:
... Omit other configurations
4. Verification

# Log on to 205 and create a database
Shell> mysql-u root-p
Mysql> create database if not exists mydb default character set utf8 collate utf8_general_ci;
Mysql> create table user (id int, username varchar (30), password varchar (30 ));
Mysql> insert into user values (1, 'angxin', '123 ');
# The following operations are performed on node 206.
#1. Log on to MySQL 206 to query all databases and check whether the database contains the mydb database.
#2. Switch to the mydb database to check whether the table contains the user and whether there is a data entry
#3. Insert a piece of data in the mydb. user table of 206 and check whether 205 has been synchronized.
Mysql> insert into user values (2, 'angxin2', '123 ')