We have a MySQL 5.5 server with binary logging enabled and a backup script that collects the binary logs and, once a day, generates a complete database dump with mysqldump
. In testing database restoration from the dump & binlogs, we found that the latter are an order of magnitude slower.
Specifically:
- Restoring from a 92.8 MB database dump with
mysql dbname < dbdump.sql
took 3m 26.526s. - Restoring all of the binary logs from after the dump with
mysqlbinlog -d dbname --start-position=107 mysql-bin.000{727..755} | mysql dbname
took 38m 10.090s. The total size of the binlog files was 56.8 MB, and the size of the output frommysqlbinlog
was 66.4 MB. - Combining the above two steps by just restoring from a (93.2 MB) dump taken after the events the binlogs cover took 2m 15.406s.
Is this time disparity normal? Can anything be done to reduce it?