Mysql database backup in Linux Crontab
Here is the easiest way to run a daily backup of your database using linux crontab. Thanks to our everyday increasing mailbox size (Thanks Gmail) and their nature to be quite safe for saving document, let’s use them to store the full backup of all our Mysql database!
- Having a shell access to your linux box
- Creating a user with limited mysql rights: SELECT and LOCK_TABLES is enough, For example a user backup_user with a password ChhdeqyqUzd75687fOnmYar
- Installing Mpack: Tools for encoding/decoding MIME messages. Mpack and munpack are utilities for encoding and decoding (respectively) binary files in MIME (Multipurpose Internet Mail Extensions) format mail messages. For compatibility with older forms of transferring binary files, the munpack program can also decode messages in split-uuencoded format.
Edit your crontab
and put inside on one line the following
0 1 * * * /usr/bin/mysqldump -ubackup_user –pChhdeqyqUzd75687fOnmYar yourdb |
gzip > /database_`date +'%m-%d-%Y'`.sql.gz ;
mpack -s "Databases backup"
-c application/gzip /database_`date +'%m-%d-%Y'`.sql.gz [email protected]
You can replace the word yourdb with your tablename or –all-databases to dump all database. With the above line a backup will be run at 1AM every day and sent in your mailbox.