Merrok
Magic Tomato
- Joined
- Jun 18, 2009
- Messages
- 176
- Solutions
- 7
- Reaction score
- 209
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
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
Or store it elsewhere like in
To do so just run
and add
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
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
Bash:
/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
Bash:
0 8 * * * /usr/sbin/logrotate -s /path/to/DBBackups/rotate.status /root/yourscript
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
SQL:
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;
Last edited: