Executed_Gtid_Set

Hello,
I have a 3 node galera cluster(multi master) running on:
Server version: 5.6.24-72.2-56-log Percona XtraDB Cluster (GPL), Release rel72.2, Revision 1, WSREP version 25.11, wsrep_25.11

My slave hangs off node1 for replication.

Working on getting my slave back online and in replication, I ran the slave status and noticed a lot of GTID’s…

Why do I have so many GTID’s? any way to fix this?

Retrieved_Gtid_Set: 06fb5658-2486-ee1a-5161-953c56ca7bce:165832513-175386408
Executed_Gtid_Set: 04d7e815-4ae6-11e5-9823-f0921c053a58:1-1757,
06fb5658-2486-ee1a-5161-953c56ca7bce:1-165832512,
1e07439c-b527-ee1a-4b83-848aa25c4186:1-26751103,
2a2d73a5-b708-ee1a-5136-1959e7687657:1-5148177,
33ab8564-b8da-ee1a-4fe7-f0972d9df64e:1-4970416,
4269889c-dc59-11e5-a71b-f0921c051a08:1-2,
46d537bb-5e47-ee1a-4c83-2832623a6cf2:1-560922031,
4f5db2a5-ad7e-ee1a-60bf-20c5ae2584af:1-2017813,
4f619bf4-9fd9-ee1a-77fd-0884a0db9e85:1-26577,
5d59cde8-48fe-11e5-b325-f0921c051a08:1-5,
5df96a91-ad7f-ee1a-5cf1-418e928bedc9:1-5524,
63142e8b-57ba-11e5-9e0f-f0921c053a58:1-7693,
8a787515-6130-ee1a-47c9-1867dc7e88a3:1-23723494,
96512664-4672-11e5-8111-f0921c053a58:1-74,
a3d75afd-bcb6-ee1a-4c59-a4730998eef5:1-1600696,
afa4bd1a-259c-ee1a-6529-79cd9cb9ffca:1-12016218,
b7a2d638-c2b3-11e5-9b86-f0921c053a58:1-4336,
c65bd610-df6d-11e5-be75-f0921c053a50:1-213816,
cfad0778-9fd5-ee1a-6d27-7da8c3d55ff2:1-23490,
d780d0ae-9fd2-ee1a-5060-107a160688f7:1-187069468,
db5b0676-2bc1-ee1a-4b42-b473a4f16db0:1-71047185,
ef1ced5c-6d91-ee1a-5c8d-2183aa126dbe:1-89956800,
f0ec6f66-acf2-ee1a-4ba7-c0b12402f82e:1-36542094
Auto_Position: 1
1 row in set (0.00 sec)

Hello,

Executed_Gtid_Set: is a representation of the set of all transactions that are logged in the binary log. Documentation for this variable here.

https://dev.mysql.com/doc/refman/5.6/en/replication-options-gtids.html#sysvar_gtid_executed

Should you proceed to face difficulties with getting your slave back online, please include full output from the following.

SHOW SLAVE STATUS;
SHOW FULL PROCESSLIST;

Could you provide along with this please provide the output for pt-summary and pt-mysql-summary from the percona toolkit.

Thank You,
Blair Page

Here is my show slave status:

mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.0.3.20
Master_User: cmon
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: binlog.002755
Read_Master_Log_Pos: 222302173
Relay_Log_File: mysqld-relay-bin.000002
Relay_Log_Pos: 399
Relay_Master_Log_File: binlog.002697
Slave_IO_Running: Yes
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 1032
Last_Error: Could not execute Update_rows event on table person.user; Can’t find record in ‘user’, Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event’s master log binlog.002697, end_log_pos 858821513
Skip_Counter: 0
Exec_Master_Log_Pos: 858820463
Relay_Log_Space: 61695171377
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: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 1032
Last_SQL_Error: Could not execute Update_rows event on table person.user; Can’t find record in ‘user’, Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event’s master log binlog.002697, end_log_pos 858821513
Replicate_Ignore_Server_Ids:
Master_Server_Id: 11
Master_UUID: b7a2d638-c2b3-11e5-9b86-f0921c053a58
Master_Info_File: /mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State:
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp: 160315 10:20:25
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: 06fb5658-2486-ee1a-5161-953c56ca7bce:165832513-184761871
Executed_Gtid_Set: 04d7e815-4ae6-11e5-9823-f0921c053a58:1-1757,
06fb5658-2486-ee1a-5161-953c56ca7bce:1-165832512,
1e07439c-b527-ee1a-4b83-848aa25c4186:1-26751103,
2a2d73a5-b708-ee1a-5136-1959e7687657:1-5148177,
33ab8564-b8da-ee1a-4fe7-f0972d9df64e:1-4970416,
4269889c-dc59-11e5-a71b-f0921c051a08:1-2,
46d537bb-5e47-ee1a-4c83-2832623a6cf2:1-560922031,
4f5db2a5-ad7e-ee1a-60bf-20c5ae2584af:1-2017813,
4f619bf4-9fd9-ee1a-77fd-0884a0db9e85:1-26577,
5d59cde8-48fe-11e5-b325-f0921c051a08:1-5,
5df96a91-ad7f-ee1a-5cf1-418e928bedc9:1-5524,
63142e8b-57ba-11e5-9e0f-f0921c053a58:1-7693,
8a787515-6130-ee1a-47c9-1867dc7e88a3:1-23723494,
96512664-4672-11e5-8111-f0921c053a58:1-74,
a3d75afd-bcb6-ee1a-4c59-a4730998eef5:1-1600696,
afa4bd1a-259c-ee1a-6529-79cd9cb9ffca:1-12016218,
b7a2d638-c2b3-11e5-9b86-f0921c053a58:1-4338,
c65bd610-df6d-11e5-be75-f0921c053a50:1-213816,
cfad0778-9fd5-ee1a-6d27-7da8c3d55ff2:1-23490,
d780d0ae-9fd2-ee1a-5060-107a160688f7:1-187069468,
db5b0676-2bc1-ee1a-4b42-b473a4f16db0:1-71047185,
ef1ced5c-6d91-ee1a-5c8d-2183aa126dbe:1-89956800,
f0ec6f66-acf2-ee1a-4ba7-c0b12402f82e:1-36542094
Auto_Position: 1
1 row in set (0.00 sec)

ERROR:
No query specified

Full process list:

mysql> show full processlist;
±-----±------------±----------------±-------------------±--------±------±---------------------------------±----------------------±----------±--------------+
| Id | User | Host | db | Command | Time | State | Info | Rows_sent | Rows_examined |
±-----±------------±----------------±-------------------±--------±------±---------------------------------±----------------------±----------±--------------+
| 1 | cmon | 10.0.1.25:40838 | information_schema | Sleep | 0 | | NULL | 414 | 414 |
| 2 | cmon | 10.0.1.25:40839 | websportal | Sleep | 2 | | NULL | 0 | 3 |
| 4 | system user | | NULL | Connect | 82707 | Waiting for master to send event | NULL | 0 | 0 |
| 1669 | root | localhost | NULL | Query | 0 | init | show full processlist | 0 | 0 |
±-----±------------±----------------±-------------------±--------±------±---------------------------------±----------------------±----------±--------------+
4 rows in set (0.00 sec)

pt-summary

Percona Toolkit System Summary Report

Network Config

Controller | Broadcom Corporation NetXtreme BCM5719 Gigabit Ethernet PCIe (rev 01)
Controller | Broadcom Corporation NetXtreme BCM5719 Gigabit Ethernet PCIe (rev 01)
Controller | Broadcom Corporation NetXtreme BCM5719 Gigabit Ethernet PCIe (rev 01)
Controller | Broadcom Corporation NetXtreme BCM5719 Gigabit Ethernet PCIe (rev 01)
Controller | Broadcom Corporation NetXtreme II BCM57810 10 Gigabit Ethernet (rev 10)
Controller | Broadcom Corporation NetXtreme II BCM57810 10 Gigabit Ethernet (rev 10)
FIN Timeout | 60
Port Range | 65000

Interface Statistics

interface rx_bytes rx_packets rx_errors tx_bytes tx_packets tx_errors
========= ========= ========== ========== ========== ========== ==========
lo 225000000000 400000000 0 225000000000 400000000 0
eth0 4500000000000 6000000000 150 600000000000000 400000000000 0
eth1 90000000000000 125000000000 15000 600000000000000 400000000000 0
eth2 0 0 0 0 0 0
eth3 0 0 0 0 0 0
eth4 0 0 0 0 0 0
eth5 0 0 0 0 0 0
bond0 90000000000000 125000000000 15000 1000000000000000 800000000000 0

Network Devices

Device Speed Duplex
========= ========= =========
bond0 20000Mb/s Full
eth0 10000Mb/s Full
eth1 10000Mb/s Full
eth2 Unknown! Unknown!
eth3 Unknown! Unknown!
eth4 Unknown! Unknown!
eth5 Unknown! Unknown!

Network Connections

Connections from remote IP addresses
10.0.1.20 100
10.0.1.25 10
10.0.3.20 1
10.0.4.4 2
10.0.4.5 1
10.0.254.77 1
Connections to local IP addresses
10.0.3.23 125
Connections to top 10 local ports
10050 100
22 10
57802 1
65531 1
65532 1
65534 1
States of connections
ESTABLISHED 15
LISTEN 4
TIME_WAIT 100

Top Processes

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
1646 root 20 0 15692 1796 808 R 3.8 0.0 0:00.03 top
6216 mysql 20 0 155g 11g 7720 S 3.8 4.4 40:27.73 mysqld
47847 zabbix 20 0 79180 1192 676 S 1.9 0.0 111:08.93 zabbix_agentd
1 root 20 0 19356 716 472 S 0.0 0.0 0:17.88 init
2 root 20 0 0 0 0 S 0.0 0.0 0:00.96 kthreadd
3 root RT 0 0 0 0 S 0.0 0.0 66:04.28 migration/0
4 root 20 0 0 0 0 S 0.0 0.0 5:10.33 ksoftirqd/0
5 root RT 0 0 0 0 S 0.0 0.0 0:00.00 stopper/0
6 root RT 0 0 0 0 S 0.0 0.0 0:23.53 watchdog/0

Notable Processes

PID OOM COMMAND
10325 -17 sshd
36036 -17 udevd
36037 -17 udevd

Simplified and fuzzy rounded vmstat (wait please)

procs —swap-- -----io---- —system---- --------cpu--------
r b si so bi bo ir cs us sy il wa st
1 0 0 0 600 50 0 0 1 1 98 0 0
0 0 0 0 0 0 4000 3000 0 1 99 0 0
0 0 0 0 0 150 1500 1500 0 0 100 0 0
1 0 0 0 0 25 700 700 0 0 100 0 0
1 0 0 0 0 0 500 600 0 0 100 0 0

The End

pt-mysql-summary

pt-mysql-summary

Percona Toolkit MySQL Summary Report

System time | 2016-03-16 13:34:42 UTC (local TZ: CDT -0500)

Instances

Port Data Directory Nice OOM Socket
===== ========================== ==== === ======
3306 /mysql 0 0 /var/lib/mysql/mysql.sock

MySQL Executable

Path to executable | /usr/sbin/mysqld
Has symbols | No

Report On Port 3306

User | root@localhost
Time | 2016-03-16 08:34:42 (CDT)
Hostname | sql04.XXXXXXXXXXXXX.com
Version | 5.6.24-72.2-56-log Percona XtraDB Cluster (GPL), Release rel72.2, Revision 1, WSREP version 25.11, wsrep_25.11
Built On | Linux x86_64
Started | 2016-03-15 09:27 (up 0+23:07:13)
Databases | 19
Datadir | /mysql/
Processes | 3 connected, 1 running
Replication | Is a slave, has 0 slaves connected
Pidfile | /mysql/mysqld.pid (exists)

Processlist

Command COUNT(*) Working SUM(Time) MAX(Time)


Connect 1 1 80000 80000
Query 1 1 0 0
Sleep 2 0 0 0

User COUNT(*) Working SUM(Time) MAX(Time)


cmon 2 0 0 0
root 1 1 0 0
system user 1 1 80000 80000

Host COUNT(*) Working SUM(Time) MAX(Time)


10.0.1.25 2 0 0 0
1 1 80000 80000
localhost 1 1 0 0

db COUNT(*) Working SUM(Time) MAX(Time)


information_schema 1 0 0 0
NULL 2 2 80000 80000
websportal 1 0 0 0

State COUNT(*) Working SUM(Time) MAX(Time)


2 0 0 0
init 1 1 0 0
Waiting for master to send eve 1 1 80000 80000

Status Counters (Wait 10 Seconds)

Variable Per day Per second 11 secs
Aborted_clients 2
Aborted_connects 4
Binlog_snapshot_position 1500
Binlog_cache_use 2
Bytes_received 60000000000 700000 300000
Bytes_sent 600000000 6000 9000
Com_admin_commands 400000 4 3
Com_begin 5
Com_commit 2
Com_create_db 700
Com_create_table 700
Com_drop_db 1500
Com_drop_table 700
Com_insert 700
Com_reset 1
Com_select 100000 1 2
Com_set_option 25
Com_show_binlogs 300
Com_show_databases 3
Com_show_engine_status 300
Com_show_master_status 3000
Com_show_processlist 17500
Com_show_slave_status 4500
Com_show_slave_status_nolock 300
Com_show_status 90000 1 1
Com_show_storage_engines 300
Com_show_tables 1
Com_show_variables 3000
Com_slave_start 5
Com_slave_stop 1
Com_truncate 20
Com_update 5
Connections 1750 1
Created_tmp_disk_tables 17500 1
Created_tmp_files 6
Created_tmp_tables 150000 1 8
Flush_commands 1
Handler_commit 1500
Handler_external_lock 300000 3 3
Handler_read_first 700
Handler_read_key 2250
Handler_read_next 60
Handler_read_rnd 150000 1 1
Handler_read_rnd_next 40000000 450 450
Handler_rollback 10
Handler_write 35000000 400 450
Innodb_background_log_sync 90000
Innodb_buffer_pool_bytes_data 60000000 700
Innodb_buffer_pool_pages_flushed 30000
Innodb_buffer_pool_pages_old 250
Innodb_buffer_pool_read_requests 450000 5
Innodb_buffer_pool_reads 600
Innodb_buffer_pool_write_requests 150000 1
Innodb_checkpoint_max_age 1750000000 20000
Innodb_data_fsyncs 9000
Innodb_data_read 9000000 100
Innodb_data_reads 600
Innodb_data_writes 45000
Innodb_data_written 1000000000 12500
Innodb_dblwr_pages_written 30000
Innodb_dblwr_writes 1250
Innodb_history_list_length 1750
Innodb_ibuf_free_list 12500
Innodb_ibuf_segment_size 12500
Innodb_ibuf_size 1
Innodb_log_write_requests 12500
I

nnodb_log_writes 8000
Innodb_lsn_current 6000000000000 70000000
Innodb_lsn_flushed 6000000000000 70000000
Innodb_lsn_last_checkpoint 6000000000000 70000000
Innodb_master_thread_active_loops 700
Innodb_master_thread_idle_loops 90000
Innodb_max_trx_id 9000000000 100000
Innodb_mem_adaptive_hash 2500000000 30000
Innodb_mem_dictionary 600000000 7000
Innodb_mem_total 175000000000 2000000
Innodb_mutex_os_waits 500
Innodb_mutex_spin_rounds 25000
Innodb_mutex_spin_waits 8000
Innodb_os_log_fsyncs 3000
Innodb_os_log_written 10000000 125
Innodb_pages_created 3000
Innodb_pages_read 600
Innodb_pages_written 30000
Innodb_purge_trx_id 9000000000 100000
Innodb_rows_inserted 1500
Innodb_rows_read 1500
Innodb_num_open_files 15
Innodb_read_views_memory 300
Innodb_descriptors_memory 8000
Innodb_s_lock_os_waits 2000
Innodb_s_lock_spin_rounds 60000
Innodb_s_lock_spin_waits 2000
Innodb_available_undo_logs 125
Innodb_x_lock_os_waits 9
Innodb_x_lock_spin_rounds 300
Innodb_x_lock_spin_waits 5
Key_read_requests 175
Key_reads 1
Open_table_definitions 70
Opened_files 125000 1 25
Opened_table_definitions 1500
Opened_tables 800
Queries 600000 6 9
Questions 225000 2 5
Select_full_join 100000 1 1
Select_scan 175000 2 2
Slave_received_heartbeats 1
Sort_rows 150000 1 1
Sort_scan 35000
Table_locks_immediate 150000 1 1
Table_open_cache_hits 150000 1 1
Table_open_cache_misses 800
Threads_created 4
Uptime 90000 1 1

Table cache

Size | 1024
Usage | 6%

Key Percona Server features

Table & Index Stats | Disabled
Multiple I/O Threads | Enabled
Corruption Resilient | Enabled
Durable Replication | Not Supported
Import InnoDB Tables | Not Supported
Fast Server Restarts | Not Supported
Enhanced Logging | Disabled
Replica Perf Logging | Disabled
Response Time Hist. | Not Supported
Smooth Flushing | Not Supported
HandlerSocket NoSQL | Not Supported
Fast Hash UDFs | Unknown

Percona XtraDB Cluster

wsrep_on | OFF

Plugins

InnoDB compression | ACTIVE

Query cache

query_cache_type | OFF
Size | 0.0
Usage | 0%
HitToInsertRatio | 0%

Schema

Specify --databases or --all-databases to dump and summarize schemas

Noteworthy Technologies

SSL | No
Explicit LOCK TABLES | No
Delayed Insert | No
XA Transactions | No
NDB Cluster | No
Prepared Statements | No
Prepared statement count | 0# InnoDB #####################################################
Version | 5.6.24-72.2
Buffer Pool Size | 143.1G
Buffer Pool Fill | 0%
Buffer Pool Dirty | 0%
File Per Table | ON
Page Size | 16k
Log File Size | 2 * 1.0G = 2.0G
Log Buffer Size | 96M
Flush Method | O_DIRECT
Flush Log At Commit | 2
XA Support | ON
Checksums | ON
Doublewrite | ON
R/W I/O Threads | 4 4
I/O Capacity | 200
Thread Concurrency | 64
Concurrency Tickets | 5000
Commit Concurrency | 0
Txn Isolation Level | REPEATABLE-READ
Adaptive Flushing | ON
Adaptive Checkpoint |
Checkpoint Age | 0
InnoDB Queue | 0 queries inside InnoDB, 0 queries in queue
Oldest Transaction | 0 Seconds
History List Len | 1657
Read Views | 0
Undo Log Entries | 0 transactions, 0 total undo, 0 max undo
Pending I/O Reads | 0 buf pool reads, 0 normal AIO, 0 ibuf AIO, 0 preads
Pending I/O Writes | 0 buf pool (0 LRU, 0 flush list, 0 page); 0 AIO, 0 sync, 0 log IO (0 log, 0 chkp); 0 pwrites
Pending I/O Flushes | 0 buf pool, 0 log
Transaction States | 2xnot started

MyISAM

Key Cache | 24.0M
Pct Used | 20%
Unflushed | 0%

Security

Users | 41 users, 0 anon, 2 w/o pw, 2 old pw
Old Passwords | 0

Binary Logging

Binlogs | 174
Zero-Sized | 0
Total Size | 171.2G
binlog_format | ROW
expire_logs_days | 7
sync_binlog | 0
server_id | 101
binlog_do_db |
binlog_ignore_db |

Noteworthy Variables

Auto-Inc Incr/Offset | 1/1
default_storage_engine | InnoDB
flush_time | 0
init_connect |
init_file |
sql_mode | NO_ENGINE_SUBSTITUTION
join_buffer_size | 256k
sort_buffer_size | 256k
read_buffer_size | 128k
read_rnd_buffer_size | 256k
bulk_insert_buffer | 0.00
max_heap_table_size | 64M
tmp_table_size | 64M
max_allowed_packet | 512M
thread_stack | 256k
log |
log_error | /var/log/mysqld.log
log_warnings | 2
log_slow_queries |
log_queries_not_using_indexes | OFF
log_slave_updates | ON

Configuration File

Config File | /etc/my.cnf

[MYSQLD]
user = mysql
basedir = /usr/
datadir = /mysql
socket = /var/lib/mysql/mysql.sock
pid_file = mysqld.pid
port = 3306
log_error = /var/log/mysqld.log
log_warnings = 2
innodb_buffer_pool_size = 146562M
innodb_flush_log_at_trx_commit = 2
innodb_file_per_table = 1
innodb_data_file_path = ibdata1:100M:autoextend
innodb_read_io_threads = 4
innodb_write_io_threads = 4
innodb_io_capacity = 200
innodb_doublewrite = 1
innodb_log_file_size = 1024M
innodb_log_buffer_size = 96M
innodb_buffer_pool_instances = 8
innodb_log_files_in_group = 2
innodb_thread_concurrency = 64
innodb_file_format = barracuda
innodb_flush_method = O_DIRECT
innodb_autoinc_lock_mode = 2
innodb_stats_on_metadata = 0
default_storage_engine = innodb
gtid_mode = ON
log_bin = binlog
log_slave_updates = 1
enforce_gtid_consistency = ON
expire_logs_days = 7
server_id = 101
binlog_format = ROW
slave_net_timeout = 60
key_buffer_size = 24M
tmp_table_size = 64M
max_heap_table_size = 64M
max_allowed_packet = 512M
skip_name_resolve
memlock = 0
sysdate_is_now = 1
max_connections = 200
thread_cache_size = 512
query_cache_type = 0
query_cache_size = 0
table_open_cache = 1024
lower_case_table_names = 0
explicit_defaults_for_timestamp = 1

The End

So what I did alread ywas create a backup of one of the masters and then ran this on the slave:

mysql> reset master; mysql> source /mysql/dump.sql;
mysql> CHANGE MASTER TO MASTER_HOST=“10.0.3.20”, MASTER_USER=“cmon”, MASTER_PASSWORD=“cmon”, MASTER_PORT=3306, MASTER_AUTO_POSITION = 1;

mysql> start slave;
mysql> show slave status\G;

That starts replication again but it quickly errors out with the error you see above in the slave status. Is this becasue I reset the GTID and now its executing old binlogs that dont need to be executed?

Hello,

Could you please the error.log entries around thelocations on both master and slaves.
Please also include pt-summary and pt-mysql-summary from the master server’s as well.

Thank You,
Blair Page

I didnt have time to try and fix it. I just rebuilt the slave using this: http://lostdomain.org/2013/01/01/fixing-mysql-slaves-with-percona-xtrabackup/
It is working correctly now.