MySQL DB Backup Script

The following is a bash shell script which I wrote to backup all databases on a MySQL server. It’s fairly simple to implement. First step is to fill in the necessary arguments in the commented region of the header. You will need:

  • The name of a MySQL user account with sufficient privileges to perform a backup. ‘root’, will likely suffice.
  • The password of the previously mentioned user.
  • The directory and file where you’d like the backup saved to. By default, backups are made to a /root/backup directory and files are named by *date*_db_backup.sql. If this directory does not exist, you’ll have to create it.
  • The specific database you’d like to backup.
#!/bin/bash
#######ARGUMENTS#############
#	MySQL user to execute dump
	db_backup_user="root";
#	Password of MySQL user
	db_backup_password="***";
#	Directory to place the updates
	db_backup_dir="/root/backup/$(date +%m_%d_%y)_db_backup.sql";
#	Specific database to backup. Removing this line and its usage in the mysqldump string will backup ALL databases with the added: --all-databases parameter.
	db_backup_db="test";
##############################
echo 'BEGINNING MySQL DUMP. PLACING DATABASES IN "/root/backup" DIR';
mysqldump -u$db_backup_user -p$db_backup_password $db_backup_db > $db_backup_dir ;
echo 'END OF "dbbackup.sh" NOW CLOSING';
exit;

Next, name the file with a .sh file extension and place it in a secure directory on your server. I recommend the root user directory (/root). This is a precaution since the file must be configured with the MySQL a user’s password in order to run properly.

Your next question may not be how to run it. You can manually run backups by executing the shell file within the shell (make sure it has execute permissions). An alternative is to automate database backups using crontab. Crontab is the better option for preventing catastrophic data loss since individuals may get lazy or genuinely forget to perform timely updates.

Please note, that there are many additional steps which may be taken to ensure the best integrity of backups. This tutorial merely shows a method for accessing the database files.

This entry was posted in Personal, Professional. Bookmark the permalink.

2 Responses to MySQL DB Backup Script

  1. Ashton says:

    You should gzip the sql file – usually reduces the size by quite a bit.

  2. Nicholas Pier says:

    Definitely a worthwhile feature. Personally, I don’t work with very large databases or storage devices where that’s an issue, but I can certainly see it’s merit. Perhaps I’ll edit the post soon to include an additional line to tar the file.

Leave a Reply

Your email address will not be published. Required fields are marked *