Shulou(Shulou.com)05/31 Report--

Editor to share with you how to deal with the problems brought about by server_id consistency in MySQL. I hope you will get something after reading this article. Let's discuss it together.

Brief introduction

We all know that when building a replication environment in MySQL, we need to set the server_id of each server to be inconsistent. If the master database is consistent with the server_id of the slave library, then the replication will fail. But recently in solving a customer's problem, encountered an interesting phenomenon, the customer environment has three database servers, one master and two slaves, the customer's two slave libraries set the same server_id, in the process of troubleshooting, look at the MySQL error log, found that there are a lot of strange information.

We simulated the customer's environment, tested and analyzed, and finally found the answer we wanted in the code. Here are the steps and contents of our testing, analysis, and summary.

Introduction to the test step environment

Main library

IP:192.168.1.130

Server_id:3656

From library A

IP:192.168.1.36

Server_id:56

From library B

IP:192.168.1.57

Server_id:56

Except for server_id, the configuration of the three hosts is as follows:

Server_id = 123

[client]

Socket = / home/mysql/data/mysqldata5.5/sock/mysql.sock

[mysqld]

# server_id = 3655

Server_id = 123

Port = 3306

Skip_name_resolve = 1

Binlog_format = ROW

# binlog_format = STATEMENT

Basedir = / home/mysql/program/mysql5.5.36

Datadir = / home/mysql/data/mysqldata5.5/mydata

Socket = / home/mysql/data/mysqldata5.5/sock/mysql.sock

Pid-file = / home/mysql/data/mysqldata5.5/sock/mysql.pid

Tmpdir = / home/mysql/data/mysqldata5.5/tmpdir

Log-error = / home/mysql/data/mysqldata5.5/log/error.log

Slow_query_log

Slow_query_log_file = / home/mysql/data/mysqldata5.5/slowlog/slow-query.log

Log-bin = / home/mysql/data/mysqldata5.5/binlog/mysql-bin

Relay-log = / home/mysql/data/mysqldata5.5/relaylog/mysql-relay-bin

Innodb_data_home_dir = / home/mysql/data/mysqldata5.5/innodb_ts

Innodb_log_group_home_dir = / home/mysql/data/mysqldata5.5/innodb_log

# innodb_undo_directory = / home/mysql/data/mysqldata5.5/undo/

Sync_binlog=1

Innodb_file_per_table=1

# skip_grant_tables

Expire_logs_days = 1

Log_slave_updates = ON

# replicate-same-server-id=1

Skip_slave_start

# innodb_undo_tablespaces=1

5.5.36 version phenomenon

After the initial environment is built, check the status of each host. The steps of building the environment are omitted.

Main Library (192.168.1.130)

The main library is viewed through the show processlist statement that there is only one dump thread, but through multiple refreshes, you can see that different servers are connected. You can see that every time the Host field of the dump thread is displayed through the show processlist statement, the value of the dump thread is constantly updated, indicating that the dump thread is constantly reconnected, so that it will occupy different ports.

From Library A (192.168.1.36)

Check the replication status through the show slave status\ G command. After multiple execution, you can see the contents displayed in the Slave_IO_Running field, with two states of YES or Connnecting. You can see that I / O threads are constantly reconnecting.

And looking at the error log through the tail-f command, you can see that the I / O thread has been trying to reconnect.

You can see that the message printed in the error log is that the Imax O thread connection

From Library B (192.168.1.57)

The phenomenon of slave B is the same as that of slave A.

5.6.36 version phenomenon

The steps for building the environment are omitted.

Main Library (192.168.1.130)

Show processlist sees that there are two dump threads and refreshes them several times, and finds that the IP:PORT in the Host field has not been changed, indicating that the dump thread remains connected all the time.

From Library A (192.168.1.36)

Tail-f / home/mysql/data/mysqldata5.6/log/error.log looks at the error log and does not keep disconnecting

From Library B (192.168.1.57)

Tail-f / home/mysql/data/mysqldata5.6/log/error.log looks at the error log and does not keep disconnecting

Cause analysis

Http://www.penglixun.com/tech/database/mysql_multi_slave_same_serverid.html this is an article written by Peng Lixun about the causes of conflicts when multiple slave use the same server_id. According to Peng Da's analysis, what I understand is that when the slave I / O thread connects to the main library, the register_slave () function will be called on the main library, and the unregister_slave () function will be called in this function, which will log out the thread that used the same server_id before. This results in continuous disconnection and reconnection from the Ibank O thread of the library.

But if you take a closer look at the code of the unregister_slave () function, you don't see that MySQL logs out the dump thread according to server_id. And further compared the 5.5.36 and 5.6.36 versions of the code, and found no difference. However, the phenomenon that the consistent server_id setting of the library leads to the continuous reconnection of IWeiO threads is only seen in version 5.5, but not in version 5.6, so the reason for the phenomenon is not in the unregister_slave () function.

After taking a closer look at Peng Da's article, I found that someone commented below that it was mainly caused by the kill_zombie_slave_threads () function. So I looked at the logic of the kill_zombie_slave_threads () function and found that MySQL should have kill the thread according to server_id at this step.

Version 5.5.36

First, take a look at the code for the 5.5.36 version of the kill_zombie_dump_threads () function. Seeing that the argument passed in to this function is a slave_server_id of type uint32, what you do in the function is to traverse all the threads in MySQL. If the traversal to a thread is a dump thread and the thread's server_id is equal to the passed parameter value, then jump out of the traversal loop and drop the thread to kill.

Void kill_zombie_dump_threads (uint32 slave_server_id)

{

Mysql_mutex_lock & LOCK_thread_count)

I_List_iterator it (threads)

THD * tmp

While ((tmp=it++))

{

If (tmp- > command = = COM_BINLOG_DUMP & &

Tmp- > server_id = = slave_server_id)

{

Mysql_mutex_lock (& tmp- > LOCK_thd_data); / / Lock from delete

Break

}

}

Mysql_mutex_unlock & LOCK_thread_count)

If (tmp)

{

/ *

Here we do not call kill_one_thread () as

It will be slow because it will iterate through the list

Again. We just to do kill the thread ourselves.

, /

Tmp- > awake (THD::KILL_QUERY)

Mysql_mutex_unlock (& tmp- > LOCK_thd_data)

}

}

Version 5.6.35

Let's take a look at the code implementation of the 5.6.36 kill_zombie_dump_threads () function, which is very different from 5.5.36. The parameter passed in first is a pointer of type THD. The logic implemented in the function is also to traverse all threads in MySQL. If you find a dump thread, first check whether the thread has a uuid field (because uuid is available after version 5.6.This is for compatibility with 5.5.5.If there is uuid, uuid is used for comparison, if there is no uuid, server_id is used for comparison.

Void kill_zombie_dump_threads (THD * thd)

{

String slave_uuid

Get_slave_uuid (thd, & slave_uuid)

If (slave_uuid.length () = = 0 & & thd- > server_id = = 0)

Return

Mysql_mutex_lock & LOCK_thread_count)

THD * tmp= NULL

Thread_iterator it= global_thread_list_begin ()

Thread_iterator end= global_thread_list_end ()

Bool is_zombie_thread= false

For (; it! = end; + + it)

{

If ((* it)! = thd & & ((* it)-> get_command () = = COM_BINLOG_DUMP | (* it)-> get_command () = = COM_BINLOG_DUMP_GTID)

{

String tmp_uuid

Get_slave_uuid ((* it), & tmp_uuid)

If (slave_uuid.length ())

{

Is_zombie_thread= (tmp_uuid.length () &! strncmp (slave_uuid.c_ptr ())

Tmp_uuid.c_ptr (), UUID_LENGTH))

Else

{

/ *

? Check if it is a 5.5 slave's dump thread i.e., server_id should be

? Same & & dump thread should not contain 'UUID

Function call

Knowing the logic implemented by the kill_zombie_dump_threads () thread, where does MySQL call this function? Take a look at the function that is called in case COM_BINLOG_DUMP.

In version 5.5.36, it is in the

Case COM_BINLOG_DUMP:

{

Ulong pos

Ushort flags

Uint32 slave_server_id

Status_var_increment (thd- > status_var.com_other)

Thd- > enable_slow_log= opt_log_slow_admin_statements

If (check_global_access (thd, REPL_SLAVE_ACL))

Break

/ * TODO: The following has to be changed to an 8 byte integer * /

Pos = uint4korr (packet)

Flags = uint2korr (packet + 4)

Thd- > server_id=0; / * avoid suicide * /

If ((slave_server_id= uint4korr (packet+6) / / mysqlbinlog.server_id==0

Kill_zombie_dump_threads (slave_server_id)

Thd- > server_id = slave_server_id

General_log_print (thd, command, "Log:'% s' Pos:% ld", packet+10, (long) pos)

Mysql_binlog_send (thd, thd- > strdup (packet + 10), (my_off_t) pos, flags)

Unregister_slave (thd,1,1)

/ * fake COM_QUIT-if we get here, the thread needs to terminate * /

Error = TRUE

Break

}

In version 5.6.36, it is also in case COM_BINLOG_DUMP, but the previous logic is encapsulated in the com_binlog_dump () function, and kill_zombie_dump_threads () is also called in the com_binlog_dump () function.

Case COM_BINLOG_DUMP:

Error= com_binlog_dump (thd, packet, packet_length)

Break

What happens in case COM_BINLOG_DUMP is to tell the dump thread to pull the new binlog.

After reading this article, I believe you have a certain understanding of "how to deal with the problems caused by server_id consistency in MySQL". If you want to know more about it, you are welcome to follow the industry information channel. Thank you for reading!

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.