-
Type:
Bug
-
Status: Done
-
Priority:
Low
-
Resolution: Fixed
-
Affects Version/s: 5.6.x, Not 5.5.x, Not 5.7.x
-
Fix Version/s: 5.6.41-84.1
-
Component/s: None
-
Labels:
-
Launchpad URL:
**Reported in Launchpad by songjiao last update 29-03-2017 03:00:48
percona verson:
$ rpm -qa |grep -i percona
Percona-Server-client-56-5.6.27-rel75.0.el6.x86_64
Percona-Server-tokudb-56-5.6.27-rel75.0.el6.x86_64
Percona-Server-devel-56-5.6.27-rel75.0.el6.x86_64
Percona-Server-shared-56-5.6.27-rel75.0.el6.x86_64
Percona-Server-server-56-5.6.27-rel75.0.el6.x86_64
Percona-Server-test-56-5.6.27-rel75.0.el6.x86_64
Percona-Server-56-debuginfo-5.6.27-rel75.0.el6.x86_64
OS:
$ cat /etc/redhat-release
CentOS release 6.6 (Final)
$ uname -a
Linux 2.6.32-504.16.2.el6.x86_64 #1 SMP Wed Apr 22 06:48:29 UTC 2015 x86_64 x86_64 x86_64 GNU/Linux
Server:
aws c4.4xlarge with 3.4TB gp2 ssd volume
Runing 2 mysql instance in one server 3306(master)<-3307(slave)
Myql config file :
------------------------------------------------------------------------------------------------------------------------------------------
[mysqld_multi]
mysqld = /usr/bin/mysqld_safe
mysqladmin = /usr/bin/mysqladmin
[mysqld3306]
socket = /data/mysqldata/mysql3306/mysql3306.sock
port = 3306
pid-file = /data/mysqldata/mysql3306/bjzw3306.pid
datadir = /data/mysqldata/mysql3306
user = mysql
server-id = 1523306
gtid_mode = ON
gtid_deployment_step = ON
log_slave_updates = 1
enforce_gtid_consistency = 1
log_error = /data/mysqllog/mysql3306/yxmysql-error.log
log_bin = /data/mysqllog/mysql3306/yxmysql-bin.log
relay_log = /data/mysqllog/mysql3306/yxmysql-relay-bin.log
slow_query_log_file=/data/mysqllog/mysql3306/slow-queries.log
log_bin_trust_function_creators = 1
thread_handling = pool-of-threads
thread_pool_max_threads = 100000
thread_pool_size = 22
slave-skip-errors=1062
slave_compressed_protocol=ON
log-slave-updates
skip-slave-start
innodb_file_per_table = 1
innodb_file_format=BARRACUDA
innodb_thread_concurrency = 0
thread_cache_size = 32
innodb_io_capacity = 20000
innodb_read_io_threads = 8
innodb_write_io_threads = 8
innodb_log_file_size = 56M
innodb_log_files_in_group = 3
innodb_log_buffer_size = 8M
innodb_buffer_pool_size = 56M
innodb_buffer_pool_instances = 2
query_cache_type = OFF
long_query_time = 1
slow_query_log = ON
log-queries-not-using-indexes
log-slow-admin-statements
max_connect_errors = 200
max_connections = 10000
character_set_server = utf8mb4
character-set-client-handshake = FALSE
collation-server = utf8mb4_unicode_ci
init_connect='SET NAMES utf8mb4'
table_open_cache=10240
open_files_limit=20480
innodb_flush_log_at_trx_commit=0
innodb_open_files=10000
expire_logs_days = 7
-------------------------------------------------END------------------------------------------------------------------------------------
3307's conf is same with 3306 with diffrent server-id and port number
How to replay:
1set up mysql cluster as 3306(master)<--3307(slave)
2create table and user for test in 3306
create user test@127.0.0.1 identified by '123456';
grant all on test.* to test@127.0.0.1;
use test;
create table seq_test(
iid bigint(20) auto_increment,
dt1 timestamp,
dt2 timestamp default CURRENT_TIMESTAMP,
primary key(iid));
3create a script to load data into mysql
$ cat /tmp/insert.py
#!/usr/bin/python
coding:utf-8
import MySQLdb
import datetime
db = MySQLdb.connect('127.0.0.1','test','123456', '', 3306)
cursor = db.cursor()
cnt = 0
while True:
cursor.execute('''insert into test.seq_test(dt1) values('%s');''' %(datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')))
cnt = cnt + 1
if cnt % 10000 == 0:
print cnt
db.commit()
4run 3 load data script instance to load data
python /tmp/insert.py &
python /tmp/insert.py &
python /tmp/insert.py &
now the wirte qps nearly 30000
5turn off gtid_deployment_step in master (3306)
SET GLOBAL gtid_deployment_step = OFF;
6execute "show slave status\G;" in 3307 shows:
Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'log event entry exceeded max_allowed_packet; Increase max_allowed_packet on master; the first event 'yxmysql-bin.000001' at 151, the last event read from '/data/mysqllog/mysql3306/yxmysql-bin.000001' at 464889689, the last byte read from '/data/mysqllog/mysql3306/yxmysql-bin.000001' at 464889708.'
this because the binlog file in the master is damaged while turn GTID ON
I have tried 3 times ,the first time is ok,but the last 2 times are meet binlog damage problem.