MySQLdump, useful tips and summary notes

February 1, 2018 by richardvk

Filed under Databases

Last modified February 5, 2018

Dumping databases for backups is absolutely essential, but like all things in life, you can never get everything you want – there are tradeoffs….


The most important points probably when dumping databases are:

  • We want it to be as quick as possible
  • We want it to have as little as impact as possible (preferably NO impact) on users experience
  • We want a backup that will restore consistent data

The mysqldump tool is very powerful with lots of options. You can find the 5.7 reference docs here, and at the very least I suggest spending 5 minutes just skimming over the table of options so you have an idea of whats available, and then delve into the ones that might seem relevant to your situation.

One production environment I help to look after is a triple node Percona XtraDB Cluster which is geographically redundant. From time to time as certain tables got beyond a certain size, the dumps started taking too long, the pending transaction queue built up, and eventually the database ran out of available connections and everything would stall. And then the monitoring went mad, applications stopped working, and complaints would start rolling in.

I found this article on Servers For Hackers extremely helpful, and, mostly for my own future reference purposes and very much for my specific use case, I want to summarise a few critical consideration points below:

  • By default, mysqldump will lock all tables in the database it is dumping for the duration of the dump. This is a huge consideration! It makes total sense though, as the only way to ensure consistency of your dumped (and therefore restorable) data it to make sure nothing is added, altered or removed from any table during the dump. As the Servers For Hackers article suggests, imagine performing a dump, but during the dump an incoming DELETE removes a row in another table yet to be dumped that is a foreign key to a row in a table you have already dumped – BIG PROBLEM!
  • Table locking is essential during any operation that changes data, but locks have user impact, so a decent compromise is reached with InnoDB tables which unlike MyISAM tables lock much less aggressively (the details are beyond the scope of this post). InnoDB tables are the default on 5.7 and its highly suggested that ALL tables are InnoDB unless you have a good reason to use something else. In the case where you have ALL your tables as InnoDB, you can use this as a fair compromise dump statement:
    • mysqldump –single-transaction –skip-lock-tables database_name > database_name.sql
  • In the case where there are MyISAM tables, one option is to have separate dump processes, one for the InnoDB tables and another for the MyISAM tables. You can do this by using table specific dumps, like:
    • mysqldump –single-transaction –skip-lock-tables database_name innodb_table_one innodb_table_two innodb_table_three > database_name_all_innodb_tables_tables.sql
  • If you make any use of primary and foreign keys, you may just have to suffer the consequences of locked tables, and look for an alternative strategy, like backing up from a slave or replica that exists for this sole purpose and is never read from or written to by client applications. You could also look to Xtrabackup for incremental backups, or, in the event of a cluster, consider removing a node temporarily from the cluster and doing a disk dump instead (ie make a copy of the entire /var/lib/mysql directory before onlining the node again and letting it catch up through an IST, if you are familiar with the workings of a Percona/Galera cluster)

In summary, there is no one-size-fits-all strategy for MySQL backups. You absolutely should invest reasonable time considering the requirements and researching associated options. This will NOT happen overnight, it may take a long time to benchmark your backup performance since, as in my case, all was wonderful until a certain size threshold was reached and then all hell broke loose. The lesson – make sure you also have the tools to help you monitor and analyse the performance of your database server both during and outside of backup runs, so you can deal with possible issues before they cause unhappiness.

Related Articles

                          Leave a Comment