How to Backup MySQL on Linux

Problem

Perform MySQL back-ups and have the results e-mailed to you

tl;dr

yum install sharutils
mysqldump -ubackup -pMyPassword --no-autocommit --add-drop-table DatabaseName | gzip | uuencode db-backup.sql.gz | mail test@example.com

Solution

The best solution for maintaining reliable MySQL back-ups is to have the server perform nightly back-ups and e-mail you the results, using a cronjob. It's typically not a good idea to have your login credentials in plaintext in a crobjob, so we're going to store the back-up users log in credentials in a file that can only be read by your back-up user.

Create a file to store the login credentials and copy your login credentials into it

vim ~/.my.cnf

[mysqldump]
user = backup
password = BackupUserPassword

Before we go any further, we'll need to make sure that uuencode is installed, as some of the newer Linux versions no longer provide it by default.

yum install sharutils

Open your user's crontab and schedule a nightly back-up. In this case, the back-up will be performed everyday at midnight and will e-mail you the results. Make sure to modify the string below so that it contains your database name and e-mail address.

crontab -e

0 0 * * * mysqldump -ubackup --no-autocommit --add-drop-table DatabaseName | gzip | uuencode db-backup.sql.gz | mail test@example.com


Was this article helpful?

mood_bad Dislike 0
mood Like 0
visibility Views: 1486