For drupal we have a number of different methods to backup the database. Having a backup strategy is one of the easiest things to do but often overlooked. Perform regular backups to keep your sanity when disaster hits. Implement a backup strategy for daily weekly and monthly backups and look cool to the client. I am going to discuss about 3 different ways to backup your database.
- backup and migrate
- drush and mysqldump
- automysqlbackup
Use the Backup and Migrate drupal module
Backup and migrate module (https://drupal.org/project/backup_migrate) is perhaps the most common way to keep your db safe. It counts more than 290,000 installs and there are many reasons for this. It supports encryption, rotation and the option to create a site archive complete with files + db. Also, it supports multiple destinations:
- local filesystem backup
- backup and email
- backup to ftp
- backup to cloud (AWS S3, rackspace, dropbox)
- NodeSquirrel (A backup service by the makers of backup and migrate module)
The downside is that it relies on drupal cron to execute the backup task so it puts some load on apache. For bigger sites this may be an issue.
Backup a drupal database with drush or mysqldump
Drush is the Swiss army knife for drupal. Among other things, it can dump the database of a site with a simple command: drush sql-dump --result-file=site-stg.sql --gzip Execute this command out of the drupal directory, or the backups will be accessible by anyone: drush -r /drupal/root sql-dump --result-file=site-stg.sql --gzip If --result-file is provided with no value, then date based filename will be created under ~/drush-backups directory. If drush is not available in the server, you can still use the mysqldump command to get a db dump. mysqldump -udb_user -pdb_pass drupal_db > drupal.sql Or generate a compressed archive: mysqldump -udb_user -p drupal_db | gzip -9 > drupal.sql.gz Another option is to use drush and get the database directly on a remote server. This requires drush installed on both servers and your drush aliases working. Execute this from the remote server to get the dump: drush @client-site sql-dump > client-site.sql Or perform the same procedure with mysqldump: ssh [email protected] "mysqldump -udb_user -pdb_pass drupal_db > site.sql; gzip site.sql" scp [email protected]:site.sq.gz . Combine any of the above commands with some bash scripting to obtain a dynamic filename, add it to cron and perform daily backups automatically: drush @client-site sql-dump > `date +%Y-%m-%d`.sql
Backup up with automysqlbackup
Automysqlbackup is a sourceforge script that performs automated backups (http://sourceforge.net/projects/automysqlbackup/). Automysqlbackup is my favorite method because it is a set and forget solution. It can automatically backup new databases and will rotate the archives. Automysqlbackup provides:
- backup rotation with daily, weekly and monthly
- backup encryption
Automysqlbackup can not backup to a remote location unless you use a network drive. It can however, email you the dump. Are you using some other method to keep your sites safe? I would love to hear in the comments below.