Master-slave synchronous jamming of SQL is to remove a table inside one hundred thousand tens of millions of data, soon finished in the main library, from the library is stuck,
I have changed according to the online method, the following two parameters:
The set global sync_binlog=20;
The set global innodb_flush_log_at_trx_commit=2;
Not all parameter values match, also restart,
In the end I just deleted the synchronous data from library file (master info, relay - log. The info, mysql - relay - bin. 000 XXX, etc.), to set up master-slave synchronization, still stuck!
Don't have any choice, which can help me with it? Thank you very much!
Here are all kinds of data:
Mysql> Show slave status \ G
* * * * * * * * * * * * * * * * * * * * * * * * * * * 1 row * * * * * * * * * * * * * * * * * * * * * * * * * * *
Slave_IO_State: Waiting for the master to send event
Master_Host: 172.16.3.112
Master_User: repl
Master_Port: 3306
60 Connect_Retry:Master_Log_File: mysql - bin. 001276
Read_Master_Log_Pos: 449567705
Relay_Log_File: mysql - relay - bin. 000002
Relay_Log_Pos: 279
Relay_Master_Log_File: mysql - bin. 001246
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB: mysql
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: 332146441
Relay_Log_Space: 16431867934
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: 98412
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: 31123306
Master_UUID: 29 c7258c - e7 - b50f bee8-11-00163 e0ac3db
Master_Info_File:/data/mysql/data/master/data7-209. The info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Reading the event from the relay log
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
CodePudding user response:
Mysql> Show full the processlist.+ - + -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- +
| | Id User | Host | db | Command | Time | State | Info |
+ - + -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- +
| 2 | root | localhost | test | Query | | 0 init | show full the processlist |
8 | | system user | | 6361 | | NULL | Connect Waiting for the master to send event | NULL |
| | 9 system user | | 98710 | | NULL | Connect Reading event from the relay log | NULL |
+ - + -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- +
There is no process in use,
CodePudding user response:
Check on the master binlog content:The log] [root @ mysql - 3-112 #/usr/local/mysql/bin/mysqlbinlog/data/mysql/log/mysql/data3-112 - bin. 001246-332146441 the start - position=| more
/*! The SET @ @ 50530 SESSION. PSEUDO_SLAVE_MODE=1 */.
/*! The SET @ @ 40019 session. Max_insert_delayed_threads=0 */.
/*! 50003 SET @ OLD_COMPLETION_TYPE=@ @ COMPLETION_TYPE, COMPLETION_TYPE=0 */.
DELIMITER/*! */.
# at 4
# 171113 13:54:17 server id 31123306 end_log_pos 120 Start: binlog v, 4 v 5.6.13 - log server created 171113 13:54:17
BINLOG '
ITMJWg9q59oBdAAAAHgAAAAAAAQANS42LjEzLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXAAEGggAAAAICAgCAAAACgoKGRkAAEij
LGk=
'/*! */.
# at 332146441
# 171113 14:32:02 server id 31123306 end_log_pos 332146521 Query thread_id=174103 exec_time=0 error_code=0
SET the TIMESTAMP=1510554722/*! */.
The SET @ @ session. Pseudo_thread_id=174103/*! */.
The SET @ @ session. Foreign_key_checks=1, @ @ session. Sql_auto_is_null=0, @ @ session. Unique_checks=1, @ @ session. The autocommit mode=1/*! */.
The SET @ @ session. Sql_mode=524288/*! */.
The SET @ @ session. Auto_increment_increment=1,. @ @ session auto_increment_offset=1/*! */.
/*! Utf8 \ C *//*! */.
The SET @ @ session. Character_set_client=33, @ @ session. Collation_connection=33, @ @ session. Collation_server=28/*! */.
The SET @ @ session. Time_zone='SYSTEM'/*! */.
The SET @ @ session. Lc_time_names=0/*! */.
The SET @ @ session. Collation_database=DEFAULT/*! */.
The BEGIN
/*! */.
# at 332146521
# 171113 14:32:02 server id 31123306 end_log_pos 332146596 Table_map: ` shreport `. ` stat_report_daydata_shop ` mapped to number 923
# at 332146596
# 171113 14:32:02 server id 31123306 end_log_pos 332154792 Delete_rows: table id 923
# at 332154792
# 171113 14:32:02 server id 31123306 end_log_pos 332162988 Delete_rows: table id 923
# at 332162988
nullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnull