How to Create an Incremental MySQL Backup

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. 

Table of Contents

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.

How to Create an Incremental MySQL Backup

Setting Up MySQL Server 8

To get started with MySQL database server version 8, you can begin by installing it using the following command:

sudo apt-get install mysql-server -y

Once the MySQL server is successfully installed, you can initiate the MySQL service and configure it to launch automatically upon system reboot with these commands:

sudo systemctl start mysql
sudo systemctl enable mysql

This will ensure that your MySQL server is up and running efficiently for your development needs.

Certainly, here's the content rephrased in a friendly and professional tone for developers:

Enabling Binary Logging

To enable incremental backups, the first step is to activate binary logging. This can be achieved by editing the MySQL default configuration file. Open the file for editing:

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

Within the configuration file, you'll need to add or modify the following lines based on your preferences:

Log_bin = /var/log/mysql/mysql-bin.log
expire_logs_days = 10

After making these changes, save and exit the file. To apply the configuration changes, restart the MySQL service with the following command:

sudo systemctl restart mysql

You can verify the MySQL binary log directory path by using this command:

ls -l /var/log/mysql/

In the resulting output, you will see the MySQL binary log file, typically named "mysql-bin.000001". This file is where all modifications to your MySQL databases are recorded, making it essential for incremental backups.

Creating a Database and Table

In this step, we will create a test database and a table, and then insert some sample data into the table.

To begin, let's connect to MySQL using the following command:

mysql

Once you've successfully connected, you can create a new database called "mydb" with the following command within the MySQL shell:

mysql> CREATE DATABASE mydb;

Now, switch to the "mydb" database and create a new table named "my_tbl":

mysql> USE mydb;
mysql> CREATE TABLE my_tbl (
  my_id INT NOT NULL AUTO_INCREMENT,
  my_field VARCHAR(100) NOT NULL,
  submission_date DATE,
  time_created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (my_id)
);

With the table in place, you can insert some sample rows into it using the following commands:

mysql> INSERT INTO my_tbl (my_field) VALUES ('val1');
mysql> INSERT INTO my_tbl (my_field) VALUES ('val2');
mysql> INSERT INTO my_tbl (my_field) VALUES ('val3');

Once you've completed these steps, you can exit the MySQL shell:

mysql> exit;

You've now created a database, defined a table, and inserted sample data, setting the stage for further development and testing.

Certainly, here's the content rephrased in a friendly and professional tone for developers:

Taking a Full MySQL Database Backup

In this step, we will perform a full backup of the current MySQL database. You can achieve this by executing the following command:

mysqldump -uroot -p --all-databases --single-transaction --flush-logs --master-data=2 > full_backup.sql

The --flush-logs option closes the current log file (typically named "mysql-bin.000001") and initiates a new log file (usually named "mysql-bin.000002"). To confirm the creation of the new MySQL binary log file, you can use the following command:

ls -l /var/log/mysql/

You should see an output similar to this:

-rw-r----- 1 mysql adm    6117 Jul 20 09:13 error.log
-rw-r----- 1 mysql mysql  2036 Jul 20 09:25 mysql-bin.000001
-rw-r----- 1 mysql mysql   156 Jul 20 09:25 mysql-bin.000002
-rw-r----- 1 mysql mysql    64 Jul 20 09:25 mysql-bin.index

From this point on, all database modifications will be recorded in the "mysql-bin.000002" file.

To further illustrate the concept, let's log in to MySQL once more and insert additional rows:

mysql> USE mydb;
mysql> INSERT INTO my_tbl (my_field) VALUES ('val4');
mysql> INSERT INTO my_tbl (my_field) VALUES ('val5');
mysql> INSERT INTO my_tbl (my_field) VALUES ('val6');
mysql> exit;

After the full backup, these new database changes will be saved in the "mysql-bin.000002" file, ensuring the continuity of your incremental backup strategy.

Deleting and Restoring a MySQL Database

In this section, we'll demonstrate how to delete a MySQL database, recreate it, and then restore it from a full backup.

1. You can delete the "mydb" database with the following command:

   mysql> DROP DATABASE mydb;

2. Now, let's create the "mydb" database again:

   mysql> CREATE DATABASE mydb;
   mysql> EXIT;

3. To restore the database from the "full_backup.sql" file, use this command:

   mysql -u root -p mydb < full_backup.sql

4. Log back into the MySQL shell and check the contents of the table:

   mysql
   mysql> USE mydb;
   mysql> SELECT * FROM my_tbl;

   You should observe only three rows in the table.

5. Next, to restore data from the binary log saved in "mysql-bin.000002," execute the following command:

   mysqlbinlog /var/log/mysql/mysql-bin.000002 | mysql -uroot -p mydb

6. Reconnect to MySQL and check the table's contents again:

   mysql
   mysql> USE mydb;
   mysql> SELECT * FROM my_tbl;

   You should now see that all rows have been successfully restored.

Congratulations! You've learned how to delete, recreate, and restore a MySQL database, as well as how to set up MySQL incremental backups.

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.

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.

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.

Selecting your MySQL 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.

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.

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.

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.

Choosing where to store the backup

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.

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.

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.

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.

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.