How to Create an Incremental MySQL Backup with SimpleBackups

SimpleBackups founder

Laurent Lemaire

Co-founder, SimpleBackups

September 22nd, 2022

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. 

What is an Incremental MySQL Backup?

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. 

Example:

  • Day 1: level-0 backup - 2GB
  • Day 2: level-1 backup - 25MB
  • Day 3: level-2 backup - 25MB
  • Day 4: level-3 backup - 25MB
  • Day 5: level-4 backup - 25MB
  • Day 6: level-5 backup - 25MB
  • Day 7: level-6 backup - 25MB
  • Day 8: level-0 backup - 2.2GB

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

How to Create an Incremental MySQL Backup with SimpleBackups

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. 

Logging Into 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:

  1. You can click on Create in the MySQL Backup tile.
  2. You can click the Database backup option to the right of the tiles.
  3. You can click Create in the navigation bar and select Database Backup from the dropdown list.  

For this example, we’ll use the first option. So, we’ll click on Create in the MySQL Backup tile.

How to create incremental MySQL Backup with SimpleBackups

Configuring the Backup

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. 

Configuring the Backup

Selecting the Server

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. 

Select the backup server

Selecting the Database

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.

Select the MySQL database to make a backup

We’ll then need to provide the details of the database we’d like to back up:

  • Host. Here, you must provide the host's IP address where the database resides. Because our database is on the server we chose earlier, we can enter the local host’s IP address, 127.0.0.1
  • Port. We’ll also need to provide the port of the MySQL server. The default port for MySQL is 3306, so you’ll enter this port number here. If you’ve changed it, you should enter the new port number. 
  • User. Here, you’ll have to provide the user you’ve configured in MySQL. 
  • Password. Apart from the username, you’ll also need to provide the user’s password.
  • Database name. Finally, you’ll need to provide the name of the database you would like to back up. If you want to back up more than one database, you can enter their name here. In this example, we’ve created an example database, mysql_bc_test, that we’ll 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. 

Creating incremental MySQL backups

Choosing the Type of Backup

Once we’ve entered all the database’s details, we can choose the type of backup we need. Here, we can choose between:

  • Back Up All Databases. This option allows you to update all the databases on your server at the same time. 
  • Uncompressed Database. With this option, you’ll store your MySQL backups uncompressed. When you do, it speeds up the restore process. 
  • Incremental Database Backup. This is the option we’ll use to create incremental backups, as described earlier.
  • Database Backup Streaming. With this option, you’ll stream your backup directly to your storage and, as such, requires no disk space. 

Choosing the Type of Backup

Validating the Connection

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. 

Validating the Connection between SimpleBackups and MySQL

Choosing our Storage Option

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. 

Choose your database backup storage option

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. 

Creating incremental MySQL backups

Finalizing the 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. 

run your SimpleBackups backup

Restoring Your 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.

how to restore your MySQL backup with SimpleBackups

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.  

restoring a MySQL backup in SimpleBackups

Start Creating Incremental MySQL Backups Today

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.



Back to blog

Don't want to maintain backup scripts?

Unlock no-code & optimized backup, for all your projects.

Try SimpleBackups

No credit card required. Free 7-day trial.