Backing up Drupal database

Drupal can grow quite big in terms of database sizes. In a regular day, we often backup Drupal databases several times per day. There are many reasons we want to keep the size lower:

1. Easier to export if the size is smaller
2. Easier to transfer to another server, less expensive (less traffic), less wasted time
3. Easier to import to the new server (takes way less time to import a 14 Mb database than a 200 Mb one)
4. Takes less storage on the Development server

We have internally a standard system to manage projects on the Development server. This automatically creates Git repository, WWW folders, MySQL databses for new projects. As well we have utilities to automatically backup Site, Database or sites/default/files folder.

Our solution

Truncate as many tables as possible. There is plenty of information available in the MySQL database that we don't need to transport to another server. It's usually information relevant only for the current state of our application.

1. Truncate the following tables:

• Everything that starts with cache_ (cache, cache_block, cache_bootstrap) - here you actually have most of the data
• sessions
• watchdog
• semaphore

2. Archive your database. Making a tar.gz out of your .sql dump can drastically reduce it's size. You should never transport plain .sql files.

You should now have at least half the size of your initial database dump.

Optimising your Drupal

If you want to take this any further you can optimise your Drupal for less MySQL usage and better performance.
I will prepare a new article on performance concerns of Drupal. Mainly you should consider removing Revision system, using a Memory cache backend instead of MySQL cache backend, using plain cache module like Boost to limit the number of MySQL queries on your server.