They say there are two types of people: those who back up their databases and those who need to. Simply put, if you want to protect your most valuable data, it’s vital that you back up your MySQL databases. And those who need to will soon learn how important it is.
The problem is that over time, MySQL databases can grow so large that they strain your server, which means you’ll typically need to schedule your backups overnight. In turn, you could end up losing data if these backups are not scheduled regularly.
Fortunately, incremental MySQL backups solve this problem and help you secure your data. In this post, we’ll show you how to create them with SimpleBackups.
Before we look at how you can create incremental backups with SimpleBackups, let’s first recap what an incremental backup is. As the name implies, incremental backups only back up the data that has changed since your previous backup.
So, for example, if you have a MySQL database with 1,000 records and only 12 records change, an incremental backup will only backup those 12 records. This approach has several benefits. For one, it ensures that your backups are always up-to-date. It also keeps your backups small, reduces bandwidth, and won’t overload your server.
It’s important to remember, however, that on SimpleBackups, when your backup is executed for the first time, a full backup (level-0 backup) will be created. As such, this will be a large backup. Subsequent backups will be created on top of the level-0 backup. For example, level-1, then level-2, level-3, and so on for every following backup job.
If you have set a retention of 7 days, and you run your backup daily, once you get to level-6, a new level-0 will be created.
Keep in mind, though, that there are some preparations you’ll need to do to perform incremental MySQL backups. These relate to configuring MySQL to deal with binary logs, and we’ve dealt with these aspects in more detail here.
Now that we’ve recapped what binary logs and incremental backups are and looked at how you should configure the binary log for incremental backups, it’s time we look at the process of creating the backup with SimpleBackups.
The first step is logging into SimpleBackups.
Once logged in, on your dashboard, you'll choose to create a database backup. To do this, you have several options:
For this example, we’ll use the first option. So, we’ll click on Create in the MySQL Backup tile.
On the screen that opens, we’ll select Databases under What will your backup include? We’ll also choose Own Server as the option under Choose the server that will carry out the backup job.
This means the backup will run on our local or cloud server. Keep in mind, though, that you can also choose Serverless, which allows you to run the backup on SimpleBackups’s infrastructure.
In the Own Server box, we’ll also need to select our server in the dropdown box. At this stage, it might be necessary to configure the server first.
To do this, we’ll click on the + to add a server.
In the dialog box that options, there are several options to connect SimpleBackups to the server; Automated SSH, Manual, and Tunnel/Bastion Host, which is ideal for private databases.
In this example, we’ll use Automated SSH. So, we’ll copy and run the provided command into our server’s terminal.
Once the command has been executed, and the connection confirmed in the terminal, we can click Validate to confirm the connection.
Once we’ve connected our server, the next step is selecting the database we want to back up.
In this example, we’ll use a MySQL database residing on an AWS web server, so the first step is to select MySQL from the available options.
We’ll then need to provide the details of the database we’d like to back up:
You also have the option to configure advanced settings should you need them. However, in this example, we’ll leave the defaults as is.
Once we’ve entered all the database’s details, we can choose the type of backup we need. Here, we can choose between:
Once we’ve entered all the information necessary, we can also add a script that will be executed after the backup is uploaded to our storage option.
In this example, we won’t add a script and just proceed to click Validate Connection.
The final stage is to decide where we want to store the backup.
The first step is providing a name for our backup.
In this example, we’ll use my-first-mysql-backup. For our backup schedule, we’ll choose Daily.
Depending on your requirements, you also have other options, including Weekly, Monthly, On-Demand, and Custom. We’ll also specify that we want to keep backups for 30 days.
Finally, we’ll need to choose where we would like to store the backup.
In this example, we’ll use the same Dropbox account we used when we showed you how to encrypt your backups, so we’ll select this option from the dropdown list. We’ll also leave the path empty. Once done, we can click on Create Backup.
Once we’ve clicked Create Backup, we’ll be taken to a confirmation screen that gives an overview of the backup we’ve just created. From here, we can click Run now to run the backup if we don’t want to wait for the scheduled backup.
If something happens to go wrong with your database, we’ll be able to restore it as easily as we created it.
To do this, we’ll go to the Overview page for our backup.
Here we’ll select Logs in the top menu.
On the screen that opens, we’ll click on the i to the right of the latest backup log entry.
A dialog box will open, and if we scroll down, we’ll find the command we can run to restore our incremental backup.
We just need to copy and paste this command into our server’s terminal to initiate the restore. By running this command on our server, we’ll rebuild our database from the level-0 backup, including all the increments that were backed up since the level-0 and the selected log.
Here, it’s important to remember that the restore will overwrite our existing database on our server. If we’d like, or it’s necessary to avoid this, we can also run the command on another server, which won’t overwrite the source database.
Now you know how you can create incremental MySQL backups quickly and easily, so there’s no reason to put your valuable data at risk. And when you want to get started, there’s no better tool than SimpleBackups. To learn more about our platform and how it works, create your first backup for free today.
Free 7-day trial. No credit card required.
Have a question? Need help getting started?
Get in touch via chat or at [email protected]