[TOP TIP] Daily database backup

There are times when we need to keep a backup of our SQL database (mariadb in this case). This is useful when you have linode backups enabled, which work fine for files, but a live database may not be backed-up properly, in some cases the tables become corrupted.

For high-end systems, we use clusters, but for small individual servers, it is wise to keep an SQL dump, just in case. So here is a simple and quick cron job, that will keep a daily (or more frequent) backup of your entire mariadb/mysql database, in a highly compressed bzip2 archive:

#minute (0-59)
#|   hour (0-23)
#|   |    day of the month (1-31)
#|   |    |   month of the year (1-12 or Jan-Dec)
#|   |    |   |   day of the week (0-6 with 0=Sun or Sun-Sat)
#|   |    |   |   |   commands
#|   |    |   |   |   |

8    4    *   *   *   /usr/bin/mysqldump --opt --all-databases | bzip2 > /root/all_databases.sql.bz2

The only requirement for the above command to work, is to have your access credentials in /root/.my.cnf so that mysqldump won't need to ask for a password when running as a cron job. You may also enhance the above command, to keep multiple/separate backups by adding the day of the week in the file name. Now it is easy to make daily copies of the bzip2 archive to a remote system.

Enjoy!