Performing database backups with rotation (backup rotation) has several advantages:
1. Saving the history of changes: Rotating backups allows you to save the history of database changes. Instead of simply replacing an existing full backup with a new one, rotation creates multiple generations of backups. Each backup is a point in time that reflects the state of the database at that particular moment. This can be useful for auditing, debugging, or restoring data at various stages of development or production.
2. Storage space management: Backup rotation allows you to use storage space more efficiently. Instead of creating a complete copy of the database every time, you can save only the changes that have occurred since the last backup. This saves disk space and reduces the load on the storage system.
3. Ease of recovery: Rotating backups makes the process of data recovery easier. In the event of a database loss or corruption, you can choose the specific point in time when the backup was created and use it for recovery. Due to the presence of several generations of backups, it is possible to restore the database to a certain state, which can be especially useful when errors or unwanted changes are detected.
4. Protection against errors and failures: Backup rotation provides an additional level of data protection. If there was an error or failure while creating one of the backups, you still have previous versions to restore. This helps to minimize the risk of data loss or corruption and ensures the reliability of backups.
5. Flexibility and customization: Backup rotation systems usually allow you to customize the storage and rotation settings according to your requirements. You can determine the number of stored generations of backups, the frequency of their creation, and other parameters. This gives you the flexibility to choose a backup strategy that suits your specific needs and constraints.
Script.
This script is an example script for creating a backup of MySQL databases with rotation.
#!/bin/bash
cd /backup/sql
rm ./sql-dump-7.gz
mv sql-dump-6.gz sql-dump-7.gz
mv sql-dump-5.gz sql-dump-6.gz
mv sql-dump-4.gz sql-dump-5.gz
mv sql-dump-3.gz sql-dump-4.gz
mv sql-dump-2.gz sql-dump-3.gz
mv sql-dump-1.gz sql-dump-2.gz
mv sql-dump-0.gz sql-dump-1.gz
mysqldump -uroot -pPASSWORD databases | gzip -c -9 > /backup/sql/sql-dump-0.gz
Let's take a look at his work:
The script goes into the /backup/sql directory where the SQL backups are located.
Next, the script deletes the oldest sql-dump-7.gz backup to make room for the new copy.
It then sequentially shifts all other backups up one position. Each copy is renamed with a higher numbered suffix, for example sql-dump-6.gz becomes sql-dump-7.gz.
The mysqldump command is then run to back up all MySQL databases. The mysqldump command is used to export the contents of databases in SQL format. The -uroot -pPASSWORD options are specified here to specify the root username and password PASSWORD of the MySQL database. The data for root can be found in /root/.my.cnf (if configured earlier). Using the --all-databases key, on the one hand, is an advantage, because all databases are saved. On the other hand, there is a disadvantage, because when restoring, you will need to select a specific database, which will need to be imported.
The result of the mysqldump command is piped to the gzip command, which compresses the backup data and saves it in archive format. The -c flag specifies to print the result to standard output, and the -9 flag specifies the highest level of compression.
Finally, the created sql-dump-0.gz backup is stored in the /backup/sql directory. The copy gets the lowest number 0, denoting the most recent version.
So this script rotates the MySQL database backups within 7 days, deleting the oldest backup and shifting all the others. A new copy is created each time the script is run, updating the older version, and saved in a compressed format to save disk space.
There are many useful articles and instructions in our FAQ.