One mysql master-slave synchronization problem and solution process, mysql master-slave synchronization process

One mysql master-slave synchronization problem and solution process, mysql master-slave synchronization process

One mysql master-slave synchronization Solution Process

Modify the table structure the day before yesterday and extend the field structure of one of the tables from varchar (30) to varchar (50). There are more than 1.2 million table data records, it takes only 40 seconds for the master database to be executed, while synchronizing data from the slave database takes 4 hours.

Although the master database runs fast, the number of affected rows is 1.2 million. The slave database synchronizes the structure changes of the 1.2 million rows instead of simply executing SQL commands to modify the slave database.
I didn't find it at first. When the business was slow, I began to feel something wrong. I quickly went to mysql to check the currently blocked mysql process:

show proccesslist

The results here are not the results at the time (many queries were blocked at the time ):

| Id     | User  | Host            | db   | Command     | Time   | State                                                                 | Info             |+--------+-------+-----------------+------+-------------+--------+-----------------------------------------------------------------------+------------------+| 722874 | bakup | 127.0.0.1:36759 | NULL | Binlog Dump | 281055 | Master has sent all binlog to slave; waiting for binlog to be updated | NULL             || 991867 | root  | localhost       | NULL | Sleep       |    780 |                                                                       | NULL             || 992585 | root  | localhost       | NULL | Query       |      0 | NULL                                                                  | show processlist |

1. Id: process id, which is useful when you want to kill a statement.

2. User: displays the User before a ticket. If it is not root, this command only displays the SQL statements within your permission range.

3. Host: displays the port from which the statement is sent

4. db: displays the database to which the process is currently connected.

5. Command: displays the commands executed by the current connection, sleep, query, connect, and binlog)

6. Time: the duration of this state, in seconds.

7. state: displays the status of the SQL statement using the current connection. It is an important column and will be described in the future. Note that state is only a certain State in statement execution, for an SQL statement that has been queried as an example, it may need to be completed in copying to tmp table, Sorting result, Sending data, and other States,

8. info: displays the SQL statement.

Now, the process of killing and blocking the process, that is, the process of synchronously modifying the structure

 

kill 722874

 

A new problem occurs when the Master/Slave database is forcibly suspended. An error occurs. The master database cannot be synchronized to the slave database, and the latest data in the Service Query cannot be synchronized.

Run the query command on the slave database (the result here is not the result at the time (an error message is prompted at the time )):

(Mon Jun 26 20:49:40 2017) db_2 >>show slave status\G*************************** 1. row ***************************               Slave_IO_State: Waiting for master to send event                  Master_Host: 127.0.0.1                  Master_User: bakup                  Master_Port: 3306                Connect_Retry: 60              Master_Log_File: mysql-bin.000330          Read_Master_Log_Pos: 445043216               Relay_Log_File: 174-relay-bin.000043                Relay_Log_Pos: 445043362        Relay_Master_Log_File: mysql-bin.000330             Slave_IO_Running: Yes            Slave_SQL_Running: Yes              Replicate_Do_DB:           Replicate_Ignore_DB: information_schema,mysql,performance_schema,test,zabbix,information_schema,mysql,performance_schema,test,zabbix           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: 445043216              Relay_Log_Space: 445043559              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: 0Master_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: 11 row in set (0.00 sec)

So I consulted with the O & M team and adopted the following methods:

1. Restore the status of the master database to change the field
2. Stop writing the Master/Slave binary log and stop the Master/Slave synchronization.
3. Change the field structure of the master database.
4. Change the field structure of the slave database (note that the master-slave synchronization has been stopped)
5. corrected the previous synchronization error.
6. Resume writing of the Master/Slave binary log
7. Enable master-slave synchronization again

The problem was solved in about 40 minutes.

This operation is also a little short. It is better to change the structure of large data volumes when the background is almost inaccessible at night. The evaluation was also conducted on that day, which was successful within two hours.

Therefore, if you are not in a hurry, you can perform synchronous modification at night.

Attached, state column information:

The Checking table is Checking the data table (this is automatic ). Closing tables is refreshing the modified data in the table to the disk and Closing the used table. This is a very fast operation. If not, check whether the disk space is full or the disk is under a heavy load. Connect Out replication the slave server is connecting to the master server. Copying to tmp table on disk because the temporary result set is larger than tmp_table_size, the temporary table is being converted from memory storage to disk storage to save memory. Creating tmp table is Creating a temporary table to store some query results. The deleting from main table server is executing the first part of multi-table deletion. The first table has just been deleted. The deleting from reference tables server is executing the second part of multi-Table deletion and deleting records of other tables. Flushing tables is executing flush tables, waiting for other threads to close the data table. Killed sends a kill request to a thread, which will check the kill flag and discard the next kill request. MySQL checks the kill flag in each primary loop. However, in some cases, the thread may die after a short period of time. If the thread is locked by other threads, the kill request will take effect immediately when the lock is released. Locked is Locked by other queries. Sending data is processing the SELECT query record and Sending the result to the client. Sorting for group is Sorting group. Sorting for order is Sorting order. The Opening tables process should be fast unless it is disturbed by other factors. For example, a data TABLE cannot be opened by another thread before the alter table or lock table statement is executed. Opening a table. Removing duplicates is executing a select distinct query, but MySQL cannot optimize those duplicate records in the previous stage. Therefore, MySQL needs to remove duplicate records and then send the results to the client. The Reopen table obtains the lock for a table, but the lock can be obtained only after the table structure is modified. The lock has been released, the data table is closed, and the data table is being re-opened. The Repair by sorting Repair command is being sorted to create an index. The Repair with keycache Repair command is using the index cache to create a new index one by one. It is slower than Repair by sorting. Searching rows for update: the matching records are found for update. It must be completed before the related record is updated. Sleeping is waiting for the client to send a new request. System lock is waiting for an external System lock to be obtained. If multiple mysqld servers are not running to request the same table at the same time, you can add the -- skip-external-locking parameter to disable external system locks. Upgrading lock insert delayed is trying to get a lock table to INSERT a new record. Updating is searching for matched records and modifying them. User Lock is waiting for GET_LOCK (). Waiting for tables this thread is notified that the data table structure has been modified. You need to re-open the data table to obtain the new structure. Then, in order to re-open the data table, you must wait until all other threads close the table. This notification is generated in the following situations: flush tables tbl_name, alter table, rename table, repair table, analyze table, or optimize table. Waiting for handler insert insert delayed has completed all the insert operations to be processed and is waiting for new requests.