Details
-
Bug
-
Status: Closed (View Workflow)
-
Blocker
-
Resolution: Fixed
-
10.1.6, 10.1.8
-
6x Galera Nodes:
Kernel: 3.10.0-229.14.1.el7.x86_64
Disk: Local Virtio 200GB
Hypervisor: CentOS7 KVM
vCPUS: 4
Memory: 16GB
4x Slaves:
Kernel: 3.10.0-229.14.1.el7.x86_64
Disk: Local Virtio 40GB
Hypervisor: CentOS7 KVM
vCPUS: 2
Memory: 4GB
1x Garb:
Kernel: 3.10.0-229.14.1.el7.x86_64
Physical Node
MariaDB Packge: MariaDB-server-10.1.8-1.el7.centos.x86_64
Slave Config:
{code}
[unknown] bryan_seitz@v-pdnsauth-001-net:/etc/my.cnf.d$ grep -v ^# server.cnf
[server]
[mysqld]
server_id=101
datadir=/var/lib/mysql
user=mysql
default_storage_engine=InnoDB
thread_cache_size = 128
max-allowed-packet = 64M
skip-name-resolve
sync_binlog=1
log_bin=binlog
log_slave_updates=1
expire_logs_days=7
binlog_format=ROW
replicate_do_db=powerdns
slave_net_timeout=60
relay_log=v-pdnsauth-001-net-relay-bin
innodb_flush_method = O_DIRECT
innodb_log_files_in_group = 2
innodb_log-buffer-size=64M
innodb_log_file_size = 256M
innodb_flush_log_at_trx_commit = 1
innodb_file_per_table = 1
innodb_buffer_pool_size = 512M
[embedded]
[mariadb]
[mariadb-10.1]
{code}
Galera Config:
{code}
[mysqld]
server_id=1
datadir=/var/lib/mysql
user=mysql
default_storage_engine=InnoDB
skip-name-resolve
### WSRep
wsrep_on=ON
wsrep_provider=/usr/lib64/galera/libgalera_smm.so
wsrep_cluster_address=gcomm://100.65.3.71,100.65.3.72,100.65.3.73,100.65.11.71,100.65.11.72,100.65.11.73,10.51.22.253
wsrep_node_address=100.65.3.71
wsrep_node_name=v-mysql-001-net.ash2.symcpe.net
wsrep_slave_threads=8
wsrep_sst_method=rsync
wsrep_cluster_name=net_cluster_1
wsrep_provider_options="gcache.size=256M"
wsrep_provider_options="evs.suspect_timeout=PT5S"
wsrep_provider_options="evs.inactive_timeout=PT15S"
wsrep_provider_options="evs.send_window=4"
wsrep_provider_options="evs.user_send_window=2"
wsrep_provider_options="evs.inactive_check_period=PT2S"
wsrep_provider_options="gmcast.segment=0"
wsrep_certify_nonPK=1
wsrep_max_ws_rows=131072
wsrep_max_ws_size=1073741824
wsrep_debug=0
wsrep_convert_LOCK_to_trx=0
wsrep_retry_autocommit=5
wsrep_auto_increment_control=1
wsrep_replicate_myisam=0
wsrep_causal_reads=0
gtid_strict_mode=1
gtid-domain-id=2
innodb-force-primary-key=1
enforce-storage-engine=innodb
### Tuning
tmp_table_size=64M
max_allowed_packet=1073741824
key_buffer_size=24M
query_cache_type=0
query_cache_size=0
max_connections=500
thread_cache_size=512
open_files_limit=65534
table_definition_cache=1024
table_open_cache=2048
### InnoDB
innodb_flush_method=O_DIRECT
innodb_log_files_in_group=2
innodb-log-buffer-size=64M
innodb_log_file_size=256M
innodb_flush_log_at_trx_commit=0
innodb_file_per_table=1
innodb_buffer_pool_size=1G
innodb-doublewrite=1
innodb-stats-on-metadata=0
innodb_autoinc_lock_mode=2
innodb-thread-concurrency=0
### Binary logging
binlog_format=ROW
log_bin=/data/mysql-logs/mysql-bin
expire_logs_days=7
sync_binlog=1
binlog_checksum=1
log_slave_updates=1
ignore_db_dirs=.ssh
ignore_db_dirs=lost+found
### SST
[sst]
progress=1
### EOF
{code}6x Galera Nodes: Kernel: 3.10.0-229.14.1.el7.x86_64 Disk: Local Virtio 200GB Hypervisor: CentOS7 KVM vCPUS: 4 Memory: 16GB 4x Slaves: Kernel: 3.10.0-229.14.1.el7.x86_64 Disk: Local Virtio 40GB Hypervisor: CentOS7 KVM vCPUS: 2 Memory: 4GB 1x Garb: Kernel: 3.10.0-229.14.1.el7.x86_64 Physical Node MariaDB Packge: MariaDB-server-10.1.8-1.el7.centos.x86_64 Slave Config: {code} [unknown] bryan_seitz@v-pdnsauth-001-net :/etc/my.cnf.d$ grep -v ^# server.cnf [server] [mysqld] server_id=101 datadir=/var/lib/mysql user=mysql default_storage_engine=InnoDB thread_cache_size = 128 max-allowed-packet = 64M skip-name-resolve sync_binlog=1 log_bin=binlog log_slave_updates=1 expire_logs_days=7 binlog_format=ROW replicate_do_db=powerdns slave_net_timeout=60 relay_log=v-pdnsauth-001-net-relay-bin innodb_flush_method = O_DIRECT innodb_log_files_in_group = 2 innodb_log-buffer-size=64M innodb_log_file_size = 256M innodb_flush_log_at_trx_commit = 1 innodb_file_per_table = 1 innodb_buffer_pool_size = 512M [embedded] [mariadb] [mariadb-10.1] {code} Galera Config: {code} [mysqld] server_id=1 datadir=/var/lib/mysql user=mysql default_storage_engine=InnoDB skip-name-resolve ### WSRep wsrep_on=ON wsrep_provider=/usr/lib64/galera/libgalera_smm.so wsrep_cluster_address= gcomm://100.65.3.71,100.65.3.72,100.65.3.73,100.65.11.71,100.65.11.72,100.65.11.73,10.51.22.253 wsrep_node_address=100.65.3.71 wsrep_node_name=v-mysql-001-net.ash2.symcpe.net wsrep_slave_threads=8 wsrep_sst_method=rsync wsrep_cluster_name=net_cluster_1 wsrep_provider_options="gcache.size=256M" wsrep_provider_options="evs.suspect_timeout=PT5S" wsrep_provider_options="evs.inactive_timeout=PT15S" wsrep_provider_options="evs.send_window=4" wsrep_provider_options="evs.user_send_window=2" wsrep_provider_options="evs.inactive_check_period=PT2S" wsrep_provider_options="gmcast.segment=0" wsrep_certify_nonPK=1 wsrep_max_ws_rows=131072 wsrep_max_ws_size=1073741824 wsrep_debug=0 wsrep_convert_LOCK_to_trx=0 wsrep_retry_autocommit=5 wsrep_auto_increment_control=1 wsrep_replicate_myisam=0 wsrep_causal_reads=0 gtid_strict_mode=1 gtid-domain-id=2 innodb-force-primary-key=1 enforce-storage-engine=innodb ### Tuning tmp_table_size=64M max_allowed_packet=1073741824 key_buffer_size=24M query_cache_type=0 query_cache_size=0 max_connections=500 thread_cache_size=512 open_files_limit=65534 table_definition_cache=1024 table_open_cache=2048 ### InnoDB innodb_flush_method=O_DIRECT innodb_log_files_in_group=2 innodb-log-buffer-size=64M innodb_log_file_size=256M innodb_flush_log_at_trx_commit=0 innodb_file_per_table=1 innodb_buffer_pool_size=1G innodb-doublewrite=1 innodb-stats-on-metadata=0 innodb_autoinc_lock_mode=2 innodb-thread-concurrency=0 ### Binary logging binlog_format=ROW log_bin=/data/mysql-logs/mysql-bin expire_logs_days=7 sync_binlog=1 binlog_checksum=1 log_slave_updates=1 ignore_db_dirs=.ssh ignore_db_dirs=lost+found ### SST [sst] progress=1 ### EOF {code}
-
10.1.10
Description
Seeing binlog corruption and other errors on slave nodes.
Slave bootstrap procedure:
Dump on cluster node 1:
mysqldump -u root -p -B powerdns -c --add-drop-database --add-drop-table -e --routines --master-data --gtid > v-mysql-001-net.sql
|
On each slave node:
mariadb> stop slave
|
mariadb> reset slave
|
mysql -u root -p < v-mysql-001-net.sql
|
mariadb> start slave
|
Sometimes this works sometimes it does not. If it works, it dies minutes later with errors like:
Binlog issues:
[unknown] root@v-mysql-003-net:/data/mysql-logs# ls -l
|
total 60736
|
-rw-rw---- 1 mysql mysql 5366340 Oct 27 10:20 mysql-bin.000001
|
-rw-rw---- 1 mysql mysql 5455473 Oct 27 11:20 mysql-bin.000002
|
-rw-rw---- 1 mysql mysql 5342334 Oct 27 12:20 mysql-bin.000003
|
-rw-rw---- 1 mysql mysql 5757401 Oct 27 13:20 mysql-bin.000004
|
-rw-rw---- 1 mysql mysql 5632098 Oct 27 14:20 mysql-bin.000005
|
-rw-rw---- 1 mysql mysql 5740158 Oct 27 15:20 mysql-bin.000006
|
-rw-rw---- 1 mysql mysql 5720343 Oct 27 16:20 mysql-bin.000007
|
-rw-rw---- 1 mysql mysql 5474330 Oct 27 17:20 mysql-bin.000008
|
-rw-rw---- 1 mysql mysql 5891716 Oct 27 18:20 mysql-bin.000009
|
-rw-rw---- 1 mysql mysql 5670778 Oct 27 19:20 mysql-bin.000010
|
-rw-rw---- 1 mysql mysql 5481569 Oct 27 20:20 mysql-bin.000011
|
-rw-rw---- 1 mysql mysql 574991 Oct 27 20:43 mysql-bin.000012
|
-rw-rw---- 1 mysql mysql 414 Oct 27 20:43 mysql-bin.000013
|
-rw-rw---- 1 mysql mysql 442 Oct 27 20:43 mysql-bin.index
|
[unknown] root@v-mysql-003-net:/data/mysql-logs# mysqlbinlog mysql-bin.000011 > /dev/null
|
ERROR: Error in Log_event::read_log_event(): 'Event truncated', data_len: 858861106, event_type: 49
|
ERROR: Could not read entry at offset 4421267: Error in log format or read error.
|
[unknown] root@v-mysql-003-net:/data/mysql-logs#
|
Slave errors:
Relay_Log_Pos: 4
|
Relay_Master_Log_File: mysql-bin.000078
|
Slave_IO_Running: No
|
Slave_SQL_Running: Yes
|
Replicate_Do_DB: powerdns
|
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: 927253
|
Relay_Log_Space: 249
|
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: 1236
|
Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Error: connecting slave requested to start from GTID 2-1-76468, which is no
|
t in the master's binlog'
|
Last_SQL_Errno: 0
|
Last_SQL_Error:
|
Replicate_Ignore_Server_Ids:
|
Master_Server_Id: 3
|
Master_SSL_Crl:
|
Master_SSL_Crlpath:
|
Using_Gtid: Slave_Pos
|
Gtid_IO_Pos: 2-1-76468
|
Replicate_Do_Domain_Ids:
|
Replicate_Ignore_Domain_Ids:
|
Parallel_Mode: conservative
|
1 row in set (0.00 sec)
|
or
Last_IO_Errno: 1236
|
Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'could not find next log; the first event 'mysql-bin.000075' at 415, the last event read from 'mysql-bin.000074' at 41011, the last byte read from 'mysql-bin.000074' at 41042.'
|
Last_SQL_Errno: 0
|
Last_SQL_Error:
|
Replicate_Ignore_Server_Ids:
|
Master_Server_Id: 3
|
or
MariaDB [(none)]> show slave status\G;
|
*************************** 1. row ***************************
|
Slave_IO_State:
|
Master_Host: mysql-cluster1-net.ash2.symcpe.net
|
Master_User: slave_user
|
Master_Port: 3306
|
Connect_Retry: 60
|
Master_Log_File: mysql-bin.000071
|
Read_Master_Log_Pos: 458
|
Relay_Log_File: v-pdnsauth-001-net-relay-bin.000002
|
Relay_Log_Pos: 750
|
Relay_Master_Log_File: mysql-bin.000071
|
Slave_IO_Running: No
|
Slave_SQL_Running: Yes
|
Replicate_Do_DB: powerdns
|
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: 458
|
Relay_Log_Space: 1065
|
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: 1236
|
Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'binlog truncated in the middle of event; consider out of disk space on master; the first event 'mysql-bin.000071' at 458, the last event read from 'mysql-bin.000071' at 454149, the last byte read from 'mysql-bin.000071' at 454168.'
|
Last_SQL_Errno: 0
|
Last_SQL_Error:
|
Replicate_Ignore_Server_Ids:
|
Master_Server_Id: 1
|
Master_SSL_Crl:
|
Master_SSL_Crlpath:
|
Using_Gtid: Slave_Pos
|
Gtid_IO_Pos: 2-3-69910
|
Replicate_Do_Domain_Ids:
|
Replicate_Ignore_Domain_Ids:
|
Parallel_Mode: conservative
|
1 row in set (0.00 sec)
|
more
Last_IO_Errno: 1236
|
Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'binlog truncated in the middle of event; consider out of disk space on master; the first event '.' at 4, the last event read from 'mysql-bin.000001' at 60529261, the last byte read from 'mysql-bin.000001' at 60529590.'
|
Last_SQL_Errno: 0
|
Last_SQL_Error:
|
Replicate_Ignore_Server_Ids:
|
Master_Server_Id: 5
|
Master_SSL_Crl:
|
Master_SSL_Crlpath:
|
Using_Gtid: Slave_Pos
|
Gtid_IO_Pos: 2-3-9495
|
Replicate_Do_Domain_Ids:
|
Replicate_Ignore_Domain_Ids:
|
Parallel_Mode: conservative
|
1 row in set (0.00 sec)
|
|
|
Last_IO_Errno: 1236
|
Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'binlog truncated in the middle of event; consider out of disk space on master; the first event '.' at 4, the last event read from 'mysql-bin.000011' at 4420261, the last byte read from 'mysql-bin.000011' at 4420590.'
|
Last_SQL_Errno: 0
|
Last_SQL_Error:
|
Replicate_Ignore_Server_Ids:
|
Master_Server_Id: 3
|
Master_SSL_Crl:
|
Master_SSL_Crlpath:
|
Using_Gtid: Slave_Pos
|
Gtid_IO_Pos: 2-3-9490
|
Replicate_Do_Domain_Ids:
|
Replicate_Ignore_Domain_Ids:
|
Parallel_Mode: conservative
|
1 row in set (0.00 sec)
|
|
|
Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'binlog truncated in the middle of event; consider out of disk space on master; the first event '.' at 4, the last event read from 'mysql-bin.000011' at 4420261, the last byte read from 'mysql-bin.000011' at 4420590.'
|
Last_SQL_Errno: 0
|
Last_SQL_Error:
|
Replicate_Ignore_Server_Ids:
|
Master_Server_Id: 3
|
Master_SSL_Crl:
|
Master_SSL_Crlpath:
|
Using_Gtid: Slave_Pos
|
Gtid_IO_Pos: 2-3-9490
|
Replicate_Do_Domain_Ids:
|
Replicate_Ignore_Domain_Ids:
|
Parallel_Mode: conservative
|
1 row in set (0.00 sec)
|
