@[TOC] Today’s article came late, mainly because I woke up and turned yellow code, the key is that I didn’t know, I arrived at the company downstairs at 8.20 in the morning, the security guard asked to see the health code, when I opened the Guangdong Province with full confidence, but was stunned, torturing all morning, green code finally came back, really life is full of surprises…

A book continues and a gossip remains.

MySQL > select * from binlog; select * from binlog; select * from binlog;

1. Preparation

The following configuration is based on Docker.

I have a simple diagram to show you how MySQL master-slave works:

Here, we prepare two machines:

  • Host: 10.3.50.27:33061
  • From machine: 10.3.50.27:33062

1.1 Host Configuration

The configuration of the host is in three steps, which is relatively easy:

1. Authorize the slave server

GRANT REPLICATION SLAVE ON *.* to 'rep1'@'10.3.50.27' identified by '123';
FLUSH PRIVILEGES;
Copy the code

This indicates that the user name rep1, password 123, and address 10.3.50.27 must be configured to log in from the slave machine. After successful login, you can operate any table 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 code

2. 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:

[mysqld] # enable binlog Log-bin =javaboy_logbin # set binlog_format format binlog_format=STATEMENT # Set maximum number of bytes in a binlog file # set maximum number of bytes in a binlog file Max_binlog_size =104857600 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 code

The 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: unique identifier of the MySQL server in the master/slave environment. The value can be any number. The value cannot be the same as that of the slave server.
  • It is important to change the value of binlog_format to STATEMENT.

Restart the MySQL server after the configuration:

docker restart mysql33061
Copy the code

3. View the current binary log name and offset of the primary server

The purpose of this operation is to recover the data from this point after starting from the database:

show master status;
Copy the code

Set binlog_format to binlog_format

You can see that. No problem.

The host configuration is complete.

1.2 Slave configuration

From the configuration of the machine is also relatively simple, we look at step by step:

1. Add the configuration in /etc/my.cnf

Note On the slave server, you only need to configure the server id.

Note: If the slave machine is copied from the host, that is, the MySQL instance is obtained by copying the CentOS VM. In this case, the UUID of the two MySQL instances is the same (they will not be the same during normal installation). In this case, you need to manually change the UUID of the two MySQL instances/var/lib/mysql/auto.cnfFor example, change the length of the UUID.

After the configuration is complete, restart the slave server.

2. Run commands to configure the slave server

change master to Master_host = '10.3.50.27 master_port = 33061, master_user =' TAB ', master_password = '123', master_log_file = 'javaboy_logbin. 0000 01',master_log_pos=154;Copy the code

The host IP 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 master.

MySQL8: get_master_public_key=1: get_master_public_key=1

change master to Master_host = '10.3.50.27 master_port = 33061, master_user =' TAB ', master_password = '123', master_log_file = 'javaboy_logbin. 0000 01',master_log_pos=154,get_master_public_key=1;Copy the code

3. Start the slave process

start slave;
Copy the code

Check 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 code

At 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.

The synchronization process is as follows:

  1. Set the IP address, port number, user name, and password of host 33061 by using the change master command on slave 33062.master_log_pos), which contains the file name and log offset.
  2. Run on slave machine 33061start slaveCommand to start two threads from the opportunity, respectivelyio_thread 和 sql_thread.
  3. io_threadResponsible for establishing connection with host.
  4. After verifying the user name and password, host 33061 reads the binlog from the local host according to the location sent from host 33062, and sends it to host 33062.
  5. After getting the binlog from machine 33062, the relay log is written to a local file.
  6. sql_threadThe thread reads the relay log, parses the commands in the log, and executes them.

That’s basically the process.

2. Data inconsistency

Create a javaboy_db database and create a user table in it. The user table is defined as follows:

CREATE TABLE `user` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `uuid` varchar(128) DEFAULT NULL,
  `name` varchar(64) DEFAULT NULL.PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
Copy the code

Next we insert a record into the user table from the host as follows:

Logically, this record would be synchronized to the slave machine 33062:

As you can see, the data is synchronized, but the UUID is different.

3. Cause analysis

As we know, the main basis for master/slave synchronization in MySQL is the binlog. The master sends its binlog to the slave, and the slave gets the same data as the master after playback.

So let’s see what the master’s binlog looks like.

Let’s look at binlog as an event. The command format is as follows:

show binlog events [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count];
Copy the code

This means viewing the binlog as an event. There are several parameters involved:

  • Log_name: Specifies the name of the binlog file to be queried. If this parameter is not specified, the earliest binlog file is queried.
  • Pos: the point of pos from which to view the log. Every operation recorded in the binlog has a point of pos. This means that we can specify the operation from which to view the log.
  • Offset: This is the offset. If not specified, the default value is 0.
  • Row_count: How many rows to view, if not specified, all rows to view.

View the command as follows (I’m starting with pos 154 here) :

show binlog events IN 'javaboy_logbin.000001' FROM 154;
Copy the code

The result is as follows (part) :

As you can see from the figure, the log in the original binlog is: use javaboy_db; Insert into user(uuid,name) values(uuid(),’javaboy’).

This SQL statement will be synchronized to the slave, and the slave will execute it accordingly, which will inevitably result in inconsistent results because the uuid() function will be executed differently each time.

Now you can see the reason for the problem.

Problem solving

The problem is easy to solve, as we said in the last article that we can solve this problem by setting binlog_format to ROW.

The detailed procedure is as follows.

In the host, modify/etc/mysql/mysql. Conf., d/mysqld. CNF configuration file, change the binlog_format to ROW, as follows:

After the modification is complete, restart the host. After the host is restarted, a new binlog file will be generated. Therefore, we need to check the latest status of the host and reconfigure the slave machine.

Based on this, connect the slave machine to the host again and perform the following operations on the slave machine:

stop slave;

change master to master_host='10.3.50.27',master_port=33061,master_user='rep1',master_password='123',master_log_file='javaboy_logbin.000002',master_log_pos=794;

start slave;
Copy the code

After reconfiguring the slave machine, we continue to insert data into the user table. After the insertion, we look at the slave machine data and find that the data is consistent.

To solve this problem, the main change we made was to change binlog_format to ROW. When we changed binlog_format to ROW, let’s see what was recorded in the binlog.

show binlog events IN 'javaboy_logbin.000002' FROM 794;
Copy the code

As you can see, between BEGIN and COMMIT is our data modification operation.

  • Table_map: This line explains the next operation on the javaboy_db.user table.
  • Write_rows: This line indicates that a new row of data is to be written.

There’s nothing here, so let’s use the mysqlBinlog tool to see if we can find anything new.

MySQL provides two official tools for viewing binlog events. The other is the mysqlbinlog command.

mysqlbinlog -vv /var/lib/mysql/javaboy_logbin000002. --start-position=794;
Copy the code
  • -vv Displays detailed information. In this way, the contents of binary files in binlog are printed.

There’s a lot here, but let’s look at a few key points:

  1. Table_map: javaboy_db.userThis means that the table number 108 will be worked on next, each table having its own number.
  2. Write_rows: table ID 108 FLAGS: STMT_END_F: Add a record to table 108.

The next two lines look like Base64 transcoding. If you are interested in decoding them, you can decode them yourself. Some of them are garbled, but some strings like uUID are not garbled, and we can guess roughly what is stored here.

Let’s look at the following SQL record, as follows:

So that’s what’s in the log, and you can see that the exact value of each field is written down so that of course you don’t get data inconsistencies.

5. Summary

Ok, today I shared two different log formats of binlog with friends through a simple case. In addition, there is one MIXED format that is rarely used now. Interested friends can continue to test the MIXED mode based on the case in this article based on the content of the last article