The lack of a MYSQL primary key causes the standby hang to live _mysql

The recent occurrence of slave delays on the line is frequent. After the discovery for users in the deletion of data, because the primary key of the table is missing, and delete conditions without index, or delete the conditions of poor filtration, resulting in slave hang live, serious impact on the stability of the production environment, Also hope that through this blog, to deepen the importance of the primary key in the InnoDB engine, I hope users in the use of RDS, design their own tables, must be added to the table primary key, the primary key can be considered as the InnoDB storage engine life, Let's take a look at this case (the production environment for this case is binlog for row mode and the same problem for the MyISAM storage engine):
(1). Slave:

 mysql> show slave status\g; *************************** 1 Row *************************** Slave_io_state:waiting for Master to send event Master_Host:xxx.xx.xx.xx master_user:replicator master_port:3006 Conne Ct_retry:60 master_log_file:mysql-bin.000006 read_master_log_pos:47465657 relay_log_file:slave-relay.100383 Relay_
log_pos:251 relay_master_log_file:mysql-bin.000006 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:18057461 relay_log_space:29409335 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:1339 Master_ssl_verify_server_cert:no last_io_errno:0 Last _io_error:last_sql_errno:0 Last_sql_error: 

Slave Seconds_behind_master has been increasing, slave appeared hang live.
(2). Resolves the binlog where the current slave executes:

mysqlbinlog-vvv/home/mysql/data3006/mysql/mysql-bin.000006–start-position=18057461 >/tmp/2.log
### UPDATE Qianyi.dmpush_message_temp
### WHERE
### @1= ' fb5c72c9-0ac2-4800-93b2-b94dc9e1dd54′/* varstring (108) meta= 108 nullable=1 is_null=0 *
### @2=133 * longint meta=0 nullable=1 is_null=0 * * ###
@3= ' 20121012220000′/* VARSTR ING (in) meta=42 nullable=1 is_null=0
/### @4= ' 0′/* varstring (a) meta=24 nullable=1-is_null=0 * *

(3) Analysis:
Simulation Scenario:
1. The table has no primary key, the whole table is updated:
Master
Table structure:
CREATE TABLE ' Dmpush_message_temp ' (
' ClientID ' varchar DEFAULT NULL,
' Infoid ' bigint DEFAULT NULL,
' endtime ' varchar DEFAULT NULL,
' Stat ' varchar (8) DEFAULT NULL
) Engine=innodb DEFAULT Charset=utf8;

Mysql> update dmpush_message_temp set stat=1;
Query OK, 226651 rows Affected (1.69 sec)
Rows matched:226651 changed:226651 warnings:0
A.binlog The first occurrence of upd ATE transaction log:
mysqlbinlog-vvv/home/mysql/data3006/mysql/mysql-bin.000007 >/tmp/test.log

2281772 ### UPDATE qianyi.dmpush_message_temp
2281773 ### WHERE
2281774 ### @1= ' fb5c72c9-0ac2-4800-93b2-b94dc9e1dd54′/* varstring (108) meta=108 nullable=1 is_null=0 */
2281775 ### @2=133/* LONGIN T meta=0 nullable=1 is_null=0 */
2281776 ### @3= ' 20121012220000′/* varstring ($) meta=42 nullable=1 is_null=0 */
2281777 ### @4= ' 0′/* varstring () meta=24 nullable=1 is_null=0 */
2281778 ### SET
2281779 ### @1= ' fb5c72c9-0ac2- 4800-93b2-b94dc9e1dd54′/* varstring (108) meta=108 nullable=1 is_null=0 */
2281780 ### @2=133/* LONGINT meta=0 Nullab le=1 is_null=0 */
2281781 ### @3= ' 20121012220000′/* varstring (km) meta=42 nullable=1 is_null=0 */
2281782 ### @4= ' 1′/* varstring meta=24 nullable=1 is_null=0 */

The last update transaction log that appears in

B.binlog:
5313201 ### update qianyi.dmpush_message_temp
5313202 ### WHERE
5313203 ### @1= ' fffff4fc-0b72-4ba2-9749-0189658af6d5′/* varstring (108) meta=108 nullable=1 is_null=0 */
5313204 ### @2=133/* LONGI NT meta=0 nullable=1 is_null=0 */
5313205 ### @3= ' 20121012220000′/* varstring ($) meta=42 nullable=1 is_null=0 */
5313206 ### @4= ' 0′/* varstring () meta=24 nullable=1 is_null=0 */
5313207 ### SET
5313208 ### @1= ' fffff4fc-0b72 -4ba2-9749-0189658af6d5′/* varstring (108) meta=108 nullable=1 is_null=0 */
5313209 ### @2=133/* LONGINT meta=0 Nulla ble=1 is_null=0 */
5313210 ### @3= ' 20121012220000′/* varstring (km) meta=42 nullable=1 is_null=0 */
5313211 ### @4 = ' 1′/* varstring meta=24 nullable=1 is_null=0 */
Note here because there is no primary key in the table, the update for each transaction entry is a full table scan, if a lot of data in the table, A lot of full table scan updates occur when the standby performs the updated transaction entry;

C. How many transaction entries are calculated:
Root@xxxxxxxxx # cat/tmp/test.log|grep ' UPDATE qianyi.dmpush_message_temp '-A-|wc-l
2521492

Mysql> Select 2521492/11;--11 the number of rows occupied for an update transaction entry
+ ————-+
| 2521492/11 |
+ ————-+
| 229226.5455 |
+ ————-+

Mysql> Use Qianyi
Database changed
Mysql> Select COUNT (*) from dmpush_message_temp;
+ ———-+
| COUNT (*) |
+ ———-+
| 226651 |
+ ———-+
As you can see, the number of entries in the Binlog is consistent with the amount of data in the table, that is, the number of transaction entries that are updated at all times (in row mode).
2. Add primary key for Dmpush_message_temp table:
Mysql> ALTER TABLE dmpush_message_temp add column ID int not NULL auto_increment,add PRIMARY Key (ID);
Query OK, 226651 rows affected (1.09 sec)
records:226651 duplicates:0 warnings:0

mysql> update dmpush_message_temp set stat=0;
Query OK, 226651 rows Affected (1.69 sec)
Rows matched:226651 changed:226651 warnings:0

Resolve transaction entries in Binlog:
Root@xxxxxxxxx # mysqlbinlog-vvv/home/mysql/data3006/mysql/mysql-bin.000008 >/tmp/ Test1.log

### UPDATE qianyi.dmpush_message_temp
### WHERE
### @1= ' fb5c72c9-0ac2-4800-93b2-b94dc9e1dd54′/* VARSTRING ( 108) meta=108 nullable=1 is_null=0 */
### @2=133/* longint meta=0 nullable=1 is_null=0 */
### @3= ' 20121012220000′ /* varstring () meta=42 nullable=1 is_null=0 */
### @4= ' 1′/* varstring (a) meta=24 nullable=1 is_null=0 */
### @5=1/* INT meta=0 nullable=0 is_null=0 */
### UPDATE qianyi.dmpush_message_temp
### WHERE
### @1= ' Fb5bdc4f-da 8a-4f03-aa5e-27677d7c8ac3′/* varstring (108) meta=108 nullable=1 is_null=0 */
### @2=133/* LONGINT meta=0 nullable=1 Is_null=0 */
### @3= ' 20121012220000′/* varstring (km) meta=42 nullable=1 is_null=0 */
### @4= ' 1′/* VARSTRING (24) meta=24 nullable=1 is_null=0 */
### @5=2/* INT meta=0 nullable=0 is_null=0 */
You can see that the transaction entry here has a primary key, which is @5 (the first transaction entry is more The new and second transaction entries are updated with the @5, which is the primary key, so that the transaction log is updated according to the primary key, and the repository execution is not stuck;

Solve:
the cause of the problem has been found, because there is no primary key in the table, row mode, every deletion of data will do a full table sweep, that is, a delete, if deleted 10, will do 10 times full table sweep .... So slave has been stuck;
1.slave: Stop slave;
mysql> stop Slave;
Ctrl-c-sending "KILL QUERY 59028" to server ...
Ctrl-c-query aborted.
Ctrl-c-sending "KILL 59028" to server ...
Ctrl-c-query aborted.
ctrl-c-exit!
Aborted
2. At this time, found that slave has been stuck, can not do any operation, this time only forcibly kill the MySQL process
Root@xxxxxxxx.com # Ps-ef|grep 3006
Root 4456 1 0 Oct11? 00:00:00/bin/sh/usr/bin/mysqld_safe–defaults-file=/etc/my3006.cnf
MySQL 6828 4456 0 Oct11? 00:39:27/usr/sbin/mysqld–defaults-file=/etc/my3006.cnf–basedir=/–datadir=/home/mysql/data3006/dbs3006–user= mysql–log-error=/home/mysql/data3006/mysql/master-error.log–open-files-limit=8192–pid-file=/home/mysql/ data3006/dbs3006/xxxxxxxx.com.pid–socket=/home/mysql/data3006/tmp/mysql.sock–port=3006

Kill-9 4456 6828

Since our slave copy is started automatically when the mysqld is started, we need to turn it off:
Vi/etc/my3006.cnf added: Skip-slave-start, in the use of Mysqld_safe start;

2. Because the binlog of the main library has been passed in to the repository, this time, slave to execute the transaction log without the primary key update will hang live, this time can take a clever way to avoid, that is, the table in the spare database to clear the data, slave in the execution of realy log, There will be 1032 errors, we write a script to skip these errors, when the standby to catch up with the main library, we are in the main library of the table through the mysqldump, or insert Select to restore to the repository, so that the slave normal to run up, Then the client is notified to make the primary key transformation;
A. Execute the following command on the slave:
Slave: Empty the problematic table on the standby
Set Sql_log_bin=off;
TRUNCATE TABLE qianyi.dmpush_message_temp;
Start slave;

Skipping errors on this table:
sh/tmp/skip.sh 3006 Dmpush_message_temp

B. After catching up with the main library, execute the following command:
Master

Lock tables Qianyi.dmpush_message_temp read;

CREATE table a2 like qianyi.dmpush_message_temp;

Lock tables A2 write, qianyi.dmpush_message_temp read;

INSERT INTO A2 select * from Qianyi.dmpush_message_temp;

Slave

Set Sql_log_bin=off;

drop table qianyi.dmpush_message_temp;

CREATE table qianyi.dmpush_message_temp like A2;

INSERT INTO Qianyi.dmpush_message_temp SELECT * from A2;

C. Finally let the application be modified to add the primary key:
Mysql> ALTER TABLE dmpush_message_temp add column ID int not NULL auto_increment,add PRIMARY Key (ID);

3. When slave card, you can binlog to see through the analysis, slave exactly stuck there, is that transaction, the following is a simple way to see the current Salve Open table:
Mysql> Show open tables;
+ ———-+ ——————— +--–+ ————-+
| Database | Table | In_use | name_locked |
+ ———-+ ——————— +--–+ ————-+
| Qianyi | Dmpush_message_temp | 1 | 0 |
| Qianyi | Test | 0 | 0 |
| Qianyi | Anson | 0 | 0 |
| MySQL | db | 0 | 0 |
| MySQL | Slow_log | 0 | 0 |
| MySQL | Event | 0 | 0 |
+ ———-+ ——————— +--–+ ————-+
Can see here Dmpush_message_temp has been in the open state, here can directly locate the root cause of the problem;

Summary: Primary keys for InnoDB, is very important, each table design, should be the primary key by default Plus, whether you need him, and the primary key design of the best choice of the self-added primary key, here can also slightly mention the benefits of the self-added primary key:
A. Self-increasing primary key to facilitate the improvement of insertion performance;
B. Self-enhanced primary key design (Int,bigint) reduces the space for level two indexes and increases the memory hit rate for the two-level index;
C. Self-added primary keys can reduce page fragmentation and improve space and memory usage.