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.'
