Automatic DB Backups - Easy and reliable

[Merrok]

Since all other Database Backup Tutorials on this forum are either bad, not working reliable or out of date, I thought I'd just quickly make a new one.

I personally use logrotate to manage my backups. This has the advantage, that I can tell my server how long I want him to save the backup file until it gets deleted, so my server isn't full after a few weeks. If you want to keep all of them, you are always able to simply download them using SCP or rsync for example before the older ones get deleted.

So the script is simple

/path/to/DBBackups/mydb.sql.gz {
    daily
    missingok
    rotate 14
    ifempty
    nocreate
    dateext
    dateformat _%Y_%m_%d
    dateyesterday
    extension .sql.gz
    sharedscripts
    postrotate
        mysqldump --single-transaction --skip-lock-tables -h 192.168.1.69 -u otbackupuser -p'securepass' mydb | gzip > "/path/to/DBBackups/mydb.sql.gz"
        chmod 0640 "/path/to/DBBackups/myot.sql.gz"
        chown otuser:otuser "/path/to/DBBackups/mydb.sql.gz"
    endscript
}

Of course you need to change the path, the login, the systemuser, the database and the server IP of your MySQL server.

So in this case the rotation will save the 15 most recent backups. The rotation is 14 days, so 14 old copies will be kept. Adding the dump from the postrotate script, that makes 15.
Here I compress the dump immediately. You don't need to do this but it saves a lot of space. And with a database like mine getting close to 50GB, it is kinda necessary.*
The options "--single-transaction" and "--skip-lock-tables" are there so the tables don't get locked while dumping and to keep consistency.
This only works with InnoDB! There are options to skip locking tables in MyISAM as well, but it will sacrifice part of the consistency. But afaik all modern OTs use InnoDB.
The script will only rotate files that are at least 1 day old (hints: daily)
Rotating a file for the first time will add a date in the format Year_Month_Day between the filename (here mydb) and the extension .sql.gz
The ifempty parameter I actually personally don't use. It means that an empty file gets rotated as well even though it is empty. If you don't want that, simply change it to notifempty. For example I don't want this because if a rotation fails, I get notified via a telegram message from my server that the rotation failed, together with error code and message. So I know and can handle it instead of having an empty backup file in my backups.

To activate it, you can either just put that script in /etc/logrotate.d/yourscript. Then it will be executed daily at 00:00 o'clock.
Or store it elsewhere like in /root/yourscript and execute it at a specific time every day using a cronjob. Like simply the time you know you have the least players. (Recommended)
To do so just run
sudo crontab -e
and add

0 8 * * * /usr/sbin/logrotate -s /path/to/DBBackups/rotate.status /root/yourscript

In this case it will be executed every day at 8am using its' own rotation status file saved in the same folder as your backups.

Important: You might want to run the mysqldump yourself once after adding the script. The postrotate script is not being run if there are no logs rotated. And if there is no file, there is nothing to rotate.

* You can check your database size in mysql using

SELECT
    table_schema AS 'DB',
    ROUND(SUM(data_length + index_length) / 1024 / 1024, 1) AS 'Size in MB'
FROM
    information_schema.tables
WHERE
    table_schema = 'mydb'
GROUP BY
    table_schema;