Summary of the problem of master-slave crashes caused by adding modified table fields in MySQL production library

There have been some unexpected problems with the online DDL and update of some of the tables in the offline library over the weekend and the developer, and here's a summary of the process, analysis, and solution.

First, the demand background:

To add a field (MODIFIED_AT) in the following table and change the default value

table_name {

Baby_comp
Baby_comp_status
Baby_usr
Baby_ad_user
Baby_camp
Baby_ord
Baby_acc_eva

}

Each table performs the following actions
ALTER TABLE ' $table _name ' ADD COLUMN ' modified_at ' TIMESTAMP DEFAULT current_timestamp on UPDATE current_timestamp COMMENT ' Creation time/Last modified time ');
The updated statement
UPDATE ' Baby_camp '
SET ' modified_at ' = from_unixtime (updated_time + 60)
WHERE ' modified_at ' <= ' 1970-01-01 08:00:00 ';

second, the database structure

master:192.168.100.18 > main Library Write data copy source
slave1:192.168.100.17 > Search by
slave2:192.168.100.19 > For enquiries
slave3:192.168.100.10 > For enquiries
For slave4:192.168.100.15 > Backup

Third, the fault of the relevant information interception:

Question 1. Max Binlog cache shortage caused replication crashes involving from libraries (192.168.100.17-search and 192.168.100.15-Backup)

161009 21:42:49 [ERROR] Slave sql:could not execute Write_rows event on table Baby.baby_delta; Multi-statement transaction required more than ' max_binlog_cache_size ' bytes of storage; Increase this mysqld variable and try again, error_code:1197; Writing one row to the row-based binary log failed, error_code:1534; Handler error ha_err_rbr_logging_failed; The event ' s master Log mysql-bin.007759, End_log_pos 3856759100, error_code:1197

161009 21:42:49 [Warning] slave:multi-statement transaction required more than ' max_binlog_cache_size ' bytes of storage; Increase this mysqld variable and try again error_code:1197
161009 21:42:49 [Warning] slave:writing one row to the row-based binary log failed error_code:1534
161009 21:42:49 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "Slave START". We stopped at log ' mysql-bin.007759 ' position 633959791
161009 21:43:48 [ERROR] Error reading packet from Server:lost connection to MySQL server during query (server_errno=2013 )
161009 21:43:48 [Note] Slave I/O thread killed while reading event
161009 21:43:48 [Note] Slave I/O thread exiting, read up to log ' mysql-bin.007760 ', position 301659
161009 21:43:53 [Note] Slave SQL thread initialized, starting replication in log ' mysql-bin.007759 ' at position 633959791, Relay log './serverdb01-relay-bin.009618 ' position:633959937
161009 21:43:53 [Note] Slave I/O thread:connected to master ' [email protected]:3306 ', replication started in log ' Mysql-bi n.007760 ' at position 301659

++++
Explain:
The main error is: From the library on the table Baby.baby_delta operation can not write to Binlog, multi-statement things request more max_binlog_cache_szie, increase the Max_binlog_cache_szie size retry
++++

problem 2. Replication crashes due to insufficient packet of Max allowed related to from library (192.168.100.15-backup)

161009 21:42:49 [ERROR] Error Reading packet from Server:log event entry exceeded Max_allowed_packet; Increase Max_allowed_packet on Master (server_errno=1236) 131118 
161009 21:42:49 [ERROR] Slave I/o: Got fatal err Or 1236 from master when reading data from binary log: ' Log event entry exceeded Max_allowed_packet; Increase max_allowed_packet on master ', error_code:1236

++++
Explain:
The main error is: The size of the binlog that reads the main library from the library exceeds the set Max_allowed_packet size, and the value of this parameter is increased on the main library.
++++

Iv. Analysis of Causes:

The table was first operated separately: Baby_ord, a data entry with a large data size of about more than 4 million.
There are multiple triggers on this table involving the Insert\update\delete operation, which will trigger the insertion of the corresponding data row into the Baby_delta table, except that the master-slave delay does not show any other condition
So optimistic that the rest of the table does not have too much data, in addition to the master-slave delay, will not cause other problems, simply put it all in a centralized release of the changes.
After dbmigrate monitoring the execution of SQL in the main library, the main library performs normally and replication crashes from libraries 17 and 15.

See the size of the Binlog after the last batch of tables, where mysql-bin.007759 this file reaches nearly 9G, the maximum size of the Binlog file that is qualified in the configuration file is 1G
because the subsequent batch of table fields add change execution as a transaction, The binlog generated by the same transaction will not be assigned to two binlog files. Causes the above problems to occur 1 and 2

Later found that the Babysitter_campagin table physical file has a 6G size, with several columns with the data type text.
But why does the Binlog file become so big? Exceeded the limit size?
Because the Binlog format of the main library configuration is mixed, the system depends on the type of SQL to record the row format or stmt format, but the default is to record stmt format, when will be recorded
What about the row format?

1. When the SQL statement is update or delete
The disadvantage of row format is that each data change is recorded in detail, the result is that the Binlog file is large, will occupy a larger binlog cache.

Mysql> show master logs;
+------------------+------------+
| Log_name | File_size |
+------------------+------------+
| mysql-bin.007758 | 2514487585 |
| mysql-bin.007759 | 9107651572 |
+------------------+------------+

mysql> desc Baby_camp;
+-----------------------------------+-----------------------+------+-----+-------------------+----------------- ------------+
| Field | Type | Null | Key | Default | Extra |
+-----------------------------------+-----------------------+------+-----+-------------------+----------------- ------------+
| Content | Mediumtext | YES | | NULL | |
| Tweet_url | Text | YES | | NULL | |
| Note | Text | YES | | NULL | |
| Requirement | Text | YES | | NULL | |
Omitted part of the content ....
+-----------------------------------+-----------------------+------+-----+-------------------+----------------- ------------+



Mysql> Select COUNT (*) from Baby_camp;
+----------+
| COUNT (*) |
+----------+
| 1131460 |
+----------+
1 row in Set (0.50 sec)


Mysql> Select COUNT (*) from Baby_delta;
+----------+
| COUNT (*) |
+----------+
| 10136301 |
+----------+
1 row in Set (1.12 sec)

v. Again there is a solution for adding the Change column, type of requirement:

1. Can not increase, do not modify the table column or default values try not to do, the requirements seem unreasonable ah, the do have to do: (
2. Multiple tables to change the field operations, such as batch processing, reduce the production of binlog, although some trouble, safety and stability important
3. There is no way to change the parameters of the database by brute force, the disadvantage is that some parameters need to restart the DB instance

Six, the doubts of the place:

Copy of the source main library only one, where 17 and 15 replication crashes (note: Both the Binlog is turned on, and the format is row, there will be problem 1, but only 15 issues 1 and 2 appear), and query dedicated 19 and 102 from the library (note: Both libraries are not open binlog) No problem 2, Puzzled?

This article is from "Shadow Knight" blog, please make sure to keep this source http://andylhz2009.blog.51cto.com/728703/1861133

Summary of the problem of master-slave crashes caused by adding modified table fields in MySQL production library