Songko has written several articles and recorded a video on MySQL master-slave setup. However, as you know, in our project, a more common structure is the double M structure, that is, two MySQL instances, each of which is the master and standby, so that when the master node suddenly fails or becomes unavailable, the slave node can be quickly switched to the master node. The architecture diagram is as follows:
In this structure, two MySQL instances are equal to each other as master and slave. The main way to determine which one is the master and which one is the slave is the readonly. In this case, it is convenient to switch the master and slave by changing the readonly property.
Let’s set up a double M master-slave backup and see how it differs from a pure M-S structure.
1. Preparation
The following configuration is based on Docker.
Here, we first prepare two machines:
- M1：10.3.50.27:33061
- M2：10.3.50.27:33062
1.1 M1 configuration
M1 configuration has three steps, which are relatively easy:
1. Authorize the M2 server
GRANT REPLICATION SLAVE ON *.* to 'rep1'@'10.3.50.27' identified by '123';
FLUSH PRIVILEGES;
Copy the codeThe M2 user name is rep1, the password is 123, and the user must log in from the address 10.3.50.27. After successful login, you can operate any tables in any library. If you do not need to restrict the login address, you can change the IP address to a %.
Notice that in MySQL8, there are some changes in this. In MySQL8, user creation and authorization need to be separated.
CREATE USER 'rep1' @ '10.3.50.27' IDENTIFIED WITH caching_sha2_password BY 'javaboy.COM'; GRANT Replication Slave ON *.* TO 'rep1' @ '10.3.50.27';Copy the code2. Modify the configuration file of the primary database
Enable binlog and set server-id. Each modification of the configuration file takes effect only after the MySQL service is restarted
Enable binlog to modify the MySQL configuration file mysqld. CNF in the /etc/mysql.conf. d directory of the container.
For this configuration file, we make the following changes:
Log-bin =javaboy_logbin; log-bin=javaboy_logbin; STATEMENT binlog_format=ROW # Set maximum number of bytes for a binlog file # Set maximum number of bytes for a binlog file # Set maximum number of bytes for a binlog file max_binlog_size=104857600 # Set maximum number of bytes for a binlog file Days) expire_logs_days = 7 # binlog Indicates that only the update of the specified database is recorded. #binlog-ignore-db=javaboy_no_db # Write cache number of times, flush a disk, default 0 indicates that this operation is determined by the operating system based on its own load # 1 indicates that each transaction commits to write disks immediately, Sync_binlog =0 # select a unique id for the current service (required to start MySQL5.7) server-id=1Copy the codeThe meaning of each configuration has been explained by Songo in gaze. Screenshot below:
The diagram below:
- Log-bin: synchronized log path and file name. Note that MySQL has permission to write to this directory.
- Binlog-do-db: specifies the name of the database to be synchronized. After the slave server is connected to the host, only the database configured in this parameter will be synchronized.
- server-id: MySQL server id = M2; MySQL server id = M2; MySQL server id = M2; Otherwise, cyclic replication of the primary and secondary binlogs may occur.
- Note that the value of binlog_format is ROW, for reasons that Songo discussed in a previous article.
Restart the MySQL server after the configuration:
docker restart mysql33061
Copy the code3. View the current binary log name and offset of M1
The purpose of this operation is to recover data from this point after M2 is started:
show master status;
Copy the code
At this point, M1 configuration is complete.
1.2 M2 configuration
Mysqld. CNF = mysqld. CNF = mysqld. CNF = mysqld. CNF = mysqld. CNF = mysqld. CNF = mysqld. CNF = mysqld. CNF = mysqld. CNF = mysqld. CNF = mysqld. CNF = mysqld. CNF = mysqld. CNF = mysqld. CNF = mysqld. CNF = mysqld. CNF
After configuration, M2 is now a host, we can also run show master status on M2; Command, the result is as follows:
1.3 Primary/Secondary Configuration
Then configure M1 and M2 as each other’s hosts.
M1 configuration
To configure the host for M1, run the following command:
change master to Master_host = '10.3.50.77 master_port = 33062, master_user =' TAB ', master_password = '123', master_log_file = 'javaboy_logbin. 0000 01',master_log_pos=154;Copy the codeThe host ADDRESS, port number, and user name and password for logging in to the host from the host are configured. Ensure that the last two parameters are the same as those in M2.
MySQL8: get_master_public_key=1: get_master_public_key=1
change master to master_host='10.3.50.77',master_port=33062,master_user='rep1',master_password='123',master_log_file='javaboy_logbin.000001',master_log_pos=154,get_master_public_key=1;
Copy the code3. Start the slave process
start slave;
Copy the codeCheck the slave state after startup:
show slave status\G;
Copy the code
4. Check the status of the slave
If the following two values are set to YES, the configuration is correct:
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Copy the codeAt this point, the configuration is complete, the host creates a library, adds data, and the slave machine automatically synchronizes.
If either of the two values is not YES, it indicates that the primary and secondary environment fails to be set up. In this case, you can read logs to view the cause of the error and solve the problem in detail.
M2 configuration
M2 and M1 are configured in the same way as each other. In the change master command, you must write the address and port correct:
change master to master_host='10.3.50.77',master_port=33061,master_user='rep1',master_password='123',master_log_file='javaboy_logbin.000001',master_log_pos=154;
Copy the codeOnce configured, M1 and M2 are now in active/standby mode.
1.4 test
The test consists of two steps:
- Create a javaboy_db database in M1, create a table user, insert a record into the table, and then check M2 to see if the data is synchronized.
- Add a record to the user table in M2 to see if there is a value in M1.
After testing it, we found it was ok, and now we can sync each other’s data.
2. Who is in charge and who is subordinate
Although it is a double M structure, but in practical application, it still scores a master and slave, so how to divide the master and slave of double M?
In the production environment, we usually set the backup node to read_only, that is, read-only, to prevent errors. Of course, there is no need to worry about the writing of binlog after setting read_ONLY, and the super user still has the write permission.
SQL > alter database read only; alter database read only;
show variables like 'read_only';
Copy the code
As you can see, read_only is OFF by default. Let’s change it to ON and execute the following SQL:
set global read_only=1;
Copy the code1 indicates ON, and 0 indicates OFF. The command output is as follows:
This is not valid for the super user, so after setting this, we will exit the session, create a new user without super privileges, log in as the new user, execute an insert SQL, and the result is as follows:
As you can see in this error message, MySQL is read-only (query-only) and cannot execute the current SQL.
After this setting, if the master is abnormal and needs to be switched over, the slave will be replaced temporarily. For better master/slave synchronization, the recommended binlog type is row mode. For details about the three binlog modes, see 666! MySQL binlog format The article.
All right, if you have any questions, please leave a comment.