1

I have a below MySQL configuration.

# The MySQL server
[mysqld]
....
key_buffer = 384M
max_allowed_packet = 5M
table_open_cache = 512
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
...
# enable GTID mode
gtid_mode = ON
enforce_gtid_consistency = 1


# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
innodb_buffer_pool_size = 384M
innodb_additional_mem_pool_size = 20M

# Set .._log_file_size to 25 % of buffer pool size
innodb_log_file_size = 100M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50

[mysqldump]
quick
max_allowed_packet = 16M

Currently trying to restore a Mysql dump from below command on a different mysql host.

mysqldump -u <> -p <> --ssl-capath=<> -all-databases --triggers --routines --events --single-transaction --master-data >> /tmp/mysqldump

Restore the same dump using below command

cat /tmp/mysqldump | mysql -u <> -p <> --ssl-capath=<>

Current stats

mysql dump - 3 GB. No network delays, on the same network

Dump generation time - 1 min Restoration time - 9 min

Host RAM - 125 GB. Swap - 8 GB

MySQL 5.6, no purge logs commands issued any where in the code or in the config.

Methods tried to fasten up restoration

Updated mysql config to below

innodb_buffer_pool_size = 2048M

# Set .._log_file_size to 25 % of buffer pool size
innodb_log_file_size = 256M
innodb_log_buffer_size = 16M
innodb_lock_wait_timeout = 50
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT

Disabling checks before dump and resetting them after dump. Setting below before dump and resetting them after restoring dump

UNIQUE_CHECKS=0;
FOREIGN_KEY_CHECKS=0;
FOREIGN_KEY_CHECKS = 0;

None of them fastened restoring database

Any recommendations on ways to improve time to restore dump ?

Tried several possible ways to improve the time to restore dump. But none of them helped. Any suggestions would be appreciated.

Original use case was to flip primary or RW host during failover. Post flip, all slaves are trying to sync with new master. If the replication is broken, as a fallback step we are taking a dump and restoring which turns out to be a cumbersome process to restore replication across 20+ hosts. To switch replication to new primary we run below command on slave

stop slave; change master to ...; start slave Some times it works but it is broken at times. Not sure when would it break

Resulting error

error 1236, Got fatal error 1236 from master when reading data from binary log: 'The slave is connecting using CHANGE MASTER TO MASTER_AUTO_POSITION = 1, but the master has purged binary logs containing GTIDs that the slave requires. Replicate the missing transactions from elsewhere, or provision a new slave from backup. Consider increasing the master's binary log expiration period. The GTID sets and the missing purged transactions are too long to print in this message. For more information, please see the master's error log or the manual for GTID_SUBTRACT.'

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.