Dowemo

A description Gtid for mysql5. 7. mode replication, replication delay too large: My alert 】 time: 20170725 14: 25: 02 | 【 192. xx: xx 】 vs 【 library: 192. xx: xx 】 delay is too large to exceed 457642 s!

Exception analysis: 1, base library isn't available 2, the slave backup data isn't reliable, delay too large; 3. Replication, affecting performance.
Second, troubleshooting problems

2. 1 view logs.

Fi & t, all I thought about is show processlist, looking at the status:
> show status g
* *. *..........
To for slave_io_state: waiting for master send event
Master_host: 192, xx
Master_user: myRep
Master_port: xx20
Connect_retry: 60
Master_log_file: mysql-bin. 000091.
Read_master_log_pos: 850335882
Relay_log_file: relay-bin. 000249.
Relay_log_pos: 414
Relay_master_log_file: mysql-bin. 000078.
Slave_io_running: yes
Slave_sql_running: yes
Replicate_do_db:
Replicate_ignore_db: information_schema, performance_schema,
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: 1048017045
Relay_log_space: 14058364808
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: 1484
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: 56001
Master_uuid: 2199 b808-1dc9-11e7-97a0-1418773c7c83
Master_info_file: mysql. Slave_master_info.
Sql_delay: 0
Sql_remaining_delay: null
Slave_sql_running_state: waiting for for dependent transaction to commit
Master_retry_count: 86400
Master_bind:
Last_io_error_timestamp:
Last_sql_error_timestamp:
Master_ssl_crl:
Master_ssl_crlpath:
Retrieved_gtid_set: 2199 b808-1dc9-11e7-97a0-1418773c7c83: 203746-654994
Executed_gtid_set: 2199 b808-1dc9-11e7-97a0-1418773c7c83: 1-203745,
984721db-1dd1-11e7-b548-b8ca3af0976a: 1-3
Auto_position: 1
Replicate_rewrite_db:
Channel_name:
Master_tls_version:
1 row in set ( 0, 00 sec )

The red flag indicates that the relay log pos and exec _ master _ log pos aren't changed continuously. To view binlog
# at 1048017045
# 170720 7: 17: 39 server id 56001 end_log_pos 1048017110 CRC32 0 x595de4a4 GTID last_committed=59399 sequence_number=59400
Set @ @ session. Gtid_next = '2199b808-1dc9-11e7-97a0-1418773c7c83:203746'/*!*/;.
# at 1048017110
# 170720 7: 17: 39 server id 56001 end_log_pos 1048017187 CRC32 0 x2bb724c0 Query thread_id=19064 exec_time=0 error_code=0
Set TIMESTAMP=1500506259/*!*/;
Set @@session.pseudo_thread_id=19064/*!*/;
Set @ @ session. Foreign_key_checks = 1, @ @ session. Sql_auto_is_null = 0, @ @ session. Unique_checks = 1, @@session.autocommit=1/*!*/;.
Set @@session.sql_mode=0/*!*/;
Set @ @ session. Auto_increment_increment = 1, @@session.auto_increment_offset=1/*!*/;.
/*! c utf8 *//*!*/;
Set @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
Set @@session.lc_time_names=0/*!*/;
Set @@session.collation_database=DEFAULT/*!*/;
Begin
/*!*/;
# at 1048017187
App_xx_area_day 39 server id 56001 end_log_pos 1048017288 CRC32 0 xfc36c6b9 Table_map 170720 7.: 17:: To.
# at 1048017288
Id 39 server id 56001 end_log_pos 1048025438 CRC32 0 xea7754b2 Delete_rows 170720 7 #: 17: table 1071
# at 1048025438
Id 39 server id 56001 end_log_pos 1048033606 CRC32 0 xc6ca1995 Delete_rows 170720 7 #: 17: table 1071
# at 1048033606
Id 39 server id 56001 end_log_pos 1048041769 CRC32 0 xb99f13f4 Delete_rows 170720 7 #: 17: table 1071
# at 1048041769
Id 39 server id 56001 end_log_pos 1048049923 CRC32 0 x811e0cd7 Delete_rows 170720 7 #: 17: table 1071
# at 1048049923
Id 39 server id 56001 end_log_pos 1048058135 CRC32 0 xbcbcae3a Delete_rows 170720 7 #: 17: table 1071
# at 1048058135
Id 39 server id 56001 end_log_pos 1048066299 CRC32 0 xec29b40e Delete_rows 170720 7 #: 17: table 1071
# at 1048066299
Id 39 server id 56001 end_log_pos 1048074464 CRC32 0 x9b0d3c60 Delete_rows 170720 7 #: 17: table 1071
# at 1048074464
Id 39 server id 56001 end_log_pos 1048082635 CRC32 0 xf80b65a1 Delete_rows 170720 7 #: 17: table 1071
# at 1048082635
Id 39 server id 56001 end_log_pos 1048090830 CRC32 0 x4518cd5c Delete_rows 170720 7 #: 17: table 1071
# at 1048090830
Id 39 server id 56001 end_log_pos 1048098993 CRC32 0 x3330a34b Delete_rows 170720 7 #: 17: table 1071
# at 1048098993
Id 39 server id 56001 end_log_pos 1048107195 CRC32 0 xbaa07468 Delete_rows 170720 7 #: 17: table 1071
# at 1048107195
。 。 。

Binlog see the transaction too long, table To app_xx_area_day..

2. The slaves.

Because before you don't verify it, you'll want to get a stop slave after the stop slave, and then you've got the:

From 2. 1 to view the log log, you can see that it needs to be rolled back at the slave of stop, causing the stop slave to stop the slave operation.

2, 3 synchronous table

Because we know that a table causes synchronization exceptions, it isn't clear why this table causes synchronization exceptions; We look at this table, where the log pattern of our synchronization records is the row pattern;

The table has always been a in use state to view the table structure:
Looking at the online table is a table of 1 thousand partitions, and a very obvious problem with the table is no primary key, the partition field; We sum up: A. Status shows that the position hasn't changed. B. Show open tables show that a table is always in use 1. c. Show create table view table structure can see no primary key, or no index, or index bad. Third, solution A few fields are found in table that can be used to determine how to:
Select count ( * ) from xx;
Select count ( * ) from ( select distinct xx from xxx ) t;
If the result of 2 queries count ( * ) is similar, you can add indexes to these fields
B. stop slave.
It might take longer because the transaction needs to be rolled back.
C.
Set sql_log_bin = 0;
Alter table xx add key xx ( xx );
Old version slaves apply binlog only to select the first index, need to put the new index at the front, you can first delete the old index, index. Can be placed in an sql.
D.
If it's a. You can view the rows_inserted by show, status. and select these indexes to determine the.
If the number of records modified per second is more, the replication is performing at faster speeds.

Of course, there's a problem solving this table partition too much, from root to root.
I'm direct. Xtrabackup delay is too big.



Copyright © 2011 Dowemo All rights reserved.    Creative Commons   AboutUs