Shulou(Shulou.com)06/01 Report--

1. Master-slave non-synchronization of MySQL

1.1 latency of the network

Because mysql master-slave replication is an asynchronous replication based on binlog

When binlog files are transmitted through the network, of course, network delay is the vast majority of reasons for master-slave non-synchronization, especially the probability of data synchronization across computer rooms is very high, so do read-write separation and pay attention to the pre-design from the business layer.

1.2 the load of the master and slave machines is not consistent.

Because mysql master-slave replication starts 1 io thread above the master database, and starts 1 SQL thread and 1 io thread from above, any one of the machines has a high load and is too busy, resulting in a shortage of resources in any of these threads, and there will be master inconsistency.

1.3 inconsistent max_allowed_packet settings

The max_allowed_packet set above the master database is larger than that of the slave database. When a large sql statement can be executed on the master database, the setting from the master database is too small to be executed, resulting in the master never being consistent.

1.4 self-increasing bond inconsistency

The key value of key starting from the increment key is not consistent with the master caused by the inconsistency of the self-increment step size setting.

1.5 synchronization parameter setting problem

In the case of abnormal mysql downtime, if sync_binlog=1 or innodb_flush_log_at_trx_commit=1 is not set, it is very likely that the binlog or relaylog file will be corrupted, resulting in inconsistency of the master.

1.6 self-bug

Master-slave asynchrony caused by mysql's own bug

1.7 version inconsistency

Especially when the high version is the master and the low version is the slave, the function supported on the master database is not supported from the database.

1.8 Master inconsistent optimal configuration

Based on the above situation, first make sure that the max_allowed_packet is consistent with the starting point and the growth point.

In addition, sync_binlog is enabled on the host at the expense of some performance. For libraries that use innodb, it is recommended to configure the following

Innodb_flush_logs_at_trx_commit = 1

Innodb-support_xa = 1 # Mysql 5.0 or above

Innodb_safe_binlog # Mysql 4.0

At the same time, the following two parameters are recommended from above.

Skip_slave_start

Read_only

Second, the method to solve the master-slave non-synchronization

2.1 Master-slave non-synchronization scenario description

Today, it was found that the master-slave database of Mysql was not synchronized.

Go to the Master library first:

Mysql > show processlist

Check to see if the process has too much Sleep. It turns out it's normal.

Show master status

It is also normal to check the status of the main library.

Mysql > show master status;FilePositionBinlog_Do_DBBinlog_Ignore_DBmysqld-bin.0000013260mysql,test,information_schema

1 row in set (0.00 sec)

Copy the code and view it on Slave

Mysql > show slave statusG

Slave_IO_Running: Yes

Slave_SQL_Running: No

Copying the code shows that the Slave is out of sync

2.2 solution 1: continue to synchronize after ignoring errors

This method is suitable for situations where there is little difference between master and slave database data, or when the data is not completely unified, and the data requirements are not strict.

Resolve:

Stop slave

Copy the code

Indicates that one step is skipped and the following number is variable.

Set global sql_slave_skip_counter = 1

Start slave

Copy the code

Then use mysql > show slave statusG to see:

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Copy the code ok, and now the master-slave synchronization status is normal.

2.3 method 2: re-master and slave, complete synchronization

This method is suitable for situations where there is a large difference between master and slave database data, or when the data is required to be completely unified.

The resolution steps are as follows:

1. First enter the main library and lock the table to prevent data from being written

Use the command:

Mysql > flush tables with read lock

Note: this is where the lock is read-only and the statement is case-insensitive.

two。 Make a data backup

Back up the data to a mysql.bak.sql file

[root@server01 mysql] # mysqldump-uroot-p-hlocalhost > mysql.bak.sql

One thing to note here: database backup must be carried out regularly. You can use shell script or python script, both of which are convenient to ensure that the data is foolproof.

3. View master status

Mysql > show master status

+-+

| | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | |

+-+

| | mysqld-bin.000001 | 3260 | | mysql,test,information_schema |

+-+

1 row in set (0.00 sec)

Copy the code

4. Transfer the mysql backup files to the slave machine for data recovery

Use the scp command

[root@server01 mysql] # scp mysql.bak.sql root@192.168.1.206:/tmp/

5. Stop the state of the slave library

Mysql > stop slave

6. Then execute the mysql command from the slave library to import the data backup

Mysql > source / tmp/mysql.bak.sql

7. Set the slave database synchronization. Pay attention to the synchronization point there, that is, the | File | Position items in the show master status information of the master database.

Change master to master_host = '192.168.1.206, master_user =' rsync', master_port=3306, master_password=', master_log_file = 'mysqld-bin.000001', master_log_pos=3260

8. Re-enable slave synchronization

Mysql > start slave

9. View synchronization status

Mysql > show slave statusG View:

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

All right, synchronization is complete.

Here to share a learning material, including: (BATJ interview materials, high availability, high concurrency, high performance and distributed, Jvm performance tuning, Spring source code, MyBatis,Netty,Redis,Kafka,Mysql,Zookeeper,Tomcat,Docker,Dubbo,Nginx and other knowledge points of the architecture materials) and Java advanced learning roadmap, free plus Q group: 478052716!

Third, how to monitor the delay between master and slave of mysql

3.1 Preface:

In daily work, there are two aspects of checking MYSQL master-slave replication.

Ensure the integrity of the overall structure of the copy

Need to check whether the data are consistent

For the former, we can monitor whether the replication thread is working properly and whether the master-slave delay is within the tolerance range, and for the latter, we can check whether the MD5 code of the data in the master-slave table is consistent or not. We can use the mk-table-checksum tool in the Maatkit toolkit to check.

This document describes how to check for master-slave delays.

There are usually two methods to judge the master-slave delay: Seconds_Behind_Master and mk-heartbeat.

3.2 method 1.

The master-slave delay is determined by monitoring the value of the Seconds_Behind_Master parameter output by the show slave statusG command.

Mysql > show slave statusG

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.000008 Read_Master_Log_Pos: 120 Relay_Log_File: edu-mysql-relay-bin.000002 Relay_Log_Pos: 287 Relay_Master_Log_File: edu-mysql-bin.000008 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_ Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table:

Replicate_Wild_Ignore_Table:

Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 120 Relay_Log_Space: 464 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0

Master_SSL_Verify_Server_Cert: No

Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error:

Replicate_Ignore_Server_Ids:

Master_Server_Id: 205Master_UUID: 7402509d-fd14-11e5-bfd0-000c2963dd15 Master_Info_File: / home/mysql/data/master.info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log Waiting for the slave I/O thread to update it Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0

1 row in set (0.00 sec)

Copying the code above is the output of show slave statusG, and these structures provide a lot of meaningful parameters for our monitoring.

Slave_IO_Running

This parameter can be used as a monitoring item for io_thread. Yes indicates that the connection between io_thread and the master database is normal and can be copied, while No indicates that the communication with the master database is abnormal. In most cases, the problem is caused by the master-slave network.

Slave_SQL_Running

This parameter indicates whether the sql_thread is normal, that is, whether the statement is executed or not. You will often encounter a duplicate primary key or a table that does not exist.

Seconds_Behind_Master

This difference is obtained by comparing the timestamp of event executed by sql_thread with the timestamp of io_thread copied event (abbreviated as ts); NULL- indicates that either io_thread or sql_thread has failed, that is, the thread's Running state is No, not Yes. 0-this value is zero, which is what we are very eager to see, indicating that the master-slave copy is good and it can be considered that lag does not exist.

Positive value-indicates that there is a delay between the master and slave, and the higher the number, the more the slave lags behind the master. Negative value-rarely seen, I just heard some senior DBA say that it has seen, in fact, this is a bug value, this parameter does not support negative values, that is, should not appear.

Note the problems that may be caused by the way Seconds_Behind_Master is calculated.

As we all know, the content of relay-log is exactly the same as that of the bin-log of the master library. While recording the sql statement, it will be recorded with the ts at that time, so the comparative reference value comes from binlog. In fact, it is not necessary for master and slave to synchronize with NTP, that is to say, there is no need to ensure the consistency of the master-slave clock. You will also find that it really happens between io_thread and sql_thread, while io_thread is really related to the main library, so the problem arises.

When the main library Imax O is heavily loaded or the network is blocked

Io_thread cannot copy binlog in time (without interruption, it is also replicating), and sql_thread can always keep up with io_thread 's script, so the value of Seconds_Behind_Master is 0.

That's what we think of as no delay, but, actually, it's not, you know.

This is why people criticize the use of this parameter to monitor whether the database has a delay error, but this value is not always incorrect.

This value is also valuable when the io_thread and master networks are good.'' Earlier, it was mentioned that there will be a negative value for the parameter Seconds_Behind_Master. We already know that this value is the ts difference between io_thread and the new ts and sql_thread.

The former is always greater than the latter, and the only possible thing is that there is an error in the ts of an event, which is smaller than the previous one, so when this happens, a negative value becomes possible.

3.2 method 2.

A tool in the mk-heartbeat:Maatkit universal toolkit that is thought to accurately determine the method of replication delay.

The implementation of mk-heartbeat is also realized with the help of timestmp comparison, which first needs to ensure that the master and slave servers must be consistent by synchronizing the clock with the same NTP server. It needs to create a heartbeat table on the master database, which has at least two fields: id and ts. Id is server_id,ts, which is the current timestamp now (). This structure will also be copied to the slave database. After the table is built, a row of update commands will be executed on the master database in the mode of background process, and the insert data in the table will be directed to the table regularly. This period defaults to 1 second. At the same time, the slave library will execute a monitoring command in the background. Compared with the period consistent with the main database, the copied ts value is compared with the same ts value on the main database. A difference of 0 means no delay, and a larger difference means more seconds of delay. We all know that replication is asynchronous and ts is not completely consistent, so the tool allows a gap of half a second, within which differences can be ignored as no latency. This tool is to check latency through real copy and ingenious borrowing of timestamp.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.