Last updated December 16th, 2023 14:44
If you manage a Linux server that also runs MySQL databases, you might have wondered how to efficiently back up your database(s). Let’s take a look at automatic MySQL backup on Linux using commands and the CRON scheduler. This will allow us to create a schedule for automatic MySQL backups every night, saving them to SQL files, while also deleting backups older than 30 days.
The result will be that a local backup of the database(s) will be created on the server, with backups available for up to one month. It’s important to note that in this case, the backup will be local, meaning it will be stored on the same server as the database itself. Such a backup can help you recover from situations where you accidentally damage or delete a table in the database. However, it won’t protect you from a complete server crash or failure since the backup will be stored in the same location as the database.
For a complete disaster recovery solution, I recommend using an external service like Forpsi Cloud Backup, where you can regularly and automatically transfer your backup folder to a backup storage. This way, your MySQL backups are accessible both on the server and in a mirrored copy on an external backup storage.
Automatic MySQL backup on Linux
Creating a MySQL backup on Linux operating system using a command
Let’s start from the beginning. Suppose we have a database named “exampledb” on the server. Using a command, we can export this database to an SQL file and save it in a predefined folder. First, let’s create the folder “/backups/mysql” using the following command:
mkdir -p /backups/mysql
The command “mkdir” is used to create a folder. The option “-p” ensures that we create the “/backups” folder even if it doesn’t exist yet. Now let’s set the necessary permissions for the folder:
chmod 700 /backups/mysql
The “chmod” command is used to change permissions for files and folders. The option “700” sets the permissions for the “/backups/mysql” folder so that it is visible and accessible only to the owner (in this case, the user executing the command).
Now that we have prepared the folder, we can perform the initial database export using the following command:
mysqldump -u <user> -p <password> exampledb > /backups/mysql/exampledb.sql
In this command, replace “<user>” with the username that has access to the MySQL database, and “<password>” with the corresponding password. The part containing the path “/backups/mysql/” is the path to the folder where you want to store the backup. “exampledb.sql” is the name of the file where the backup will be saved.
MySQL Backup Automation
Now we can move on to the next part and utilize the cron command to automatically create a backup of the database every night at 2 AM. Additionally, to save space, we will create a backup plan for the past month. This means that backups older than 30 days will be automatically deleted. Each backup will have the date added to its filename to easily identify when it was created.
For this action, we will use cron.
Cron performs task scheduling based on a configuration file called "crontab" (Cron table). Each user can have their own crontab file, which contains scheduled tasks for that particular user.
Now you can set up cron to automatically perform backups every day at 2 AM and delete backups older than 30 days. Run the following command:
crontab -e
Open the text editor to modify CRON tasks. Add the following lines to the end of the file:
0 2 * * * mysqldump -u <user> -p <password> exampledb > /backups/mysql/exampledb_$(date +\%Y\%m\%d).sql
0 2 * * * find /backups/mysql -type f -name 'exampledb_*.sql' -mtime +30 -exec rm {} \;
In these lines, replace “<user>”, “<password>”, and “/backup/mysql/exampledb” with the respective values you used in the first command.
Save and close the file. CRON will now perform backups every day at 2 AM and delete backups older than 30 days.
Note: Make sure you have sufficient permissions to write to the backups/mysql folder and execute the mysqldump and find commands. Refer to the first part where you tested the command first.
Automatic MySQL Backup on Linux
Conclusion
As mentioned above, this MySQL backup on Linux mainly serves the purpose of recovering from issues within the database itself, such as accidental table deletions or database structure corruption. However, it is not a complete disaster recovery solution. If your server encounters problems, you may lose both the database and the backups since they are stored on the same server.
For comprehensive backup solutions, it is advisable to mirror the backups/mysql folder to a remote server or storage. There are various services available for this purpose, such as Forpsi Cloud Backup. Here, you can use automated tasks to take the backup folder every day and transfer it as a copy to a remote storage. Only then will you have a complete MySQL backup in case of system failure or server damage.
Related Topics:
The website is created with care for the included information. I strive to provide high-quality and useful content that helps or inspires others. If you are satisfied with my work and would like to support me, you can do so through simple options.
Subscribe to the Newsletter
Stay informed! Join our newsletter subscription and be the first to receive the latest information directly to your email inbox. Follow updates, exclusive events, and inspiring content, all delivered straight to your email.
