Replication hangs after mysqldump / innobackupex from master

MySQL & MariaDB Other MySQL® Questions
1

Hi,

currently we have some problems with the replication of the percona server (Server version: 5.5.27-28.1-log Percona Server (GPL), Release 28.1).
The problem is that the slave stands still some seconds after the innobackupex backup was copied and the change master & start slave was executed, but only the Exec_Master_Log_Pos hangs, Relay_Log_Space is still increasing and in the file system the relay logs are increasing. The configuration (my.cnf) contains exactly the same configuration as the master, we tried also to change the binlog_format from RAW to MIXED but nothing changed (of course we did the reset master thing after the changes).

We also tried a mysqldump import to the slave but nothing seems to solve the problem.

While the slave is started the mysqld process consumes 100% of one CPU thread, the IO-load itself is low and the server runs with a high performance SSD RAID 5.

Our configuration is as the following:
key_buffer = 4096M
max_allowed_packet = 128M
read_buffer_size = 8M
thread_stack = 512K
table_cache = 48K
myisam_sort_buffer = 70M
join_buffer_size = 1024M
tmp_table_size = 2048M
max_heap_table_size = 50M
max_connections = 300
innodb_adaptive_flushing_method = keep_average
myisam-recover = BACKUP
query_cache_limit = 64M
query_cache_size = 100M
query_cache_type = 1
server-id = 805
log_bin = /var/lib/mysql/percona/logs/master_805
expire_logs_days = 1
max_binlog_size = 100M
binlog_format = MIXED
innodb_data_home_dir = /var/lib/mysql/percona/innodb/
innodb_data_file_path = ibdata1:4096M:autoextend
innodb_autoextend_increment = 4096M
innodb_log_group_home_dir = /var/lib/mysql/percona/logs/
innodb_buffer_pool_size = 8192M
innodb_additional_mem_pool_size = 1024M
innodb_log_file_size = 1024M
innodb_log_buffer_size = 20M
innodb_flush_log_at_trx_commit = 0
innodb_lock_wait_timeout = 9600
transaction-isolation = READ-COMMITTED
innodb_thread_concurrency = 12
innodb_file_per_table
sort_buffer_size = 1024M
net_write_timeout = 3600

We’re currently a bit perplexed and don’t know how to fix this issue, so hopefully you expert guys have an idea what we can do to solve that problem.

Any help would be really welcome!

Thanks,

Sebastian

2

Hi,

Can you add skip-slave-start on your config, restart MySQL and execute change master to then start slave. See if it works?

3

Hi,

no, unfortunately this doesn’t work, the Exec_Master_Log_Pos stands still and the Relay_Log increases + the Seconds Behind Master increases all the time. I tried also a reset slave all; and change master to the last position as well as a fresh innobackupex copy but the result is still the same.

Additionally i want to add that we have round about 200 different databases at the master host, we don’t restrict the database replication with do-db / ignore-db settings, etc… Could the amount of databases produce this problem?

Best,

Sebastian