Backing up a MySQL database regularly is an important task of any system administrator to prevent data loss in case of application bugs or system failure. There are two types of backup full and incremental. First, a full backup contains a collection of all MySQL queries. Second, an incremental backup is a type of backup that only saves data that has been changed or created since It conducted the previous backup activity. Third, incremental backup saves storage space and uses fewer resources. So it is a recommended solution for cloud backup. Show
Table of Contents
Table of Contents
This guide will explain how to perform incremental MySQL backup with Mysqldump and Binary log. Install MySQL Server 8First, install the MySQL server version 8 by running the following command:
After installing the MySQL server, start the MySQL service and enable it to start at system reboot:
Enable Binary LoggingFirst, you must enable binary logging to perform an incremental backup. You can enable it by editing the MySQL default configuration file:
Add or modify the following lines:
Save and close the file, then restart the MySQL service to apply the changes:
Now, check the MySQL binary log with the following command:
You should see MySQL binary log file in the following output:
As you can see, mysql-bin.000001 is a MySQL binary log file. All changes in all MySQL databases will be stored in this file. Create a Database and TableNext, we will create a test database and table and insert some rows in the table. First, connect to MySQL with the following command:
Once you are connected, create a database named mydb with the following command:
Next, change the database to mydb and create a new table named my_tbl:
Next, insert some rows with the following command:
Next, exit from the MySQL shell:
Take a Full MySQL MySQL BackupNext, we will take a full backup of the current MySQL database. You can do it with the following command:
Where:
You can check the new MySQL binary log file with the following command:
You should see the following output:
Now, all database changes will be written in mysql-bin.000002 file. Next, login to MySQL again and insert more rows:
We have new database changes saved in the file mysql-bin.000002 after the full backup. Take an Incremental MySQL BackupTo take an incremental backup, you must flush the binary log and save the binary logs created from the last full backup. To flush the binary log, run the following command:
This will close the mysql-bin.000002 file and create a new one. You can check it with the following command:
You should see the following output:
You can also check the current state of the table with the following command:
You should see the following output:
Delete a MySQL Database and Restore it From Incremental BackupNext, login to MySQL again and delete a mydb database with the following command:
Next, create a mydb database again with the following command:
Next, restore the MySQL data from the full_backup.sql:
Now, login to MySQL shell and check the content of the table:
You should see just three rows:
You will need to restore the MySQL data from the binary log saved in the mysql-bin.000002 file. Run the following command to restore the incremental backup.
Now, login to MySQL again and check the content of the table:
You should see that all rows are restored:
Now, exit from MySQL with the following command:
ConclusionThe above guide taught you how to perform incremental backup in MySQL using binary log and Mysqldump. It is always recommended to save the binary log on some remote location. So you can restore the complete database in the event of system failure. How do I do a full incremental backup and restore in MySQL?Incremental directory backups can be recovered using a series of copy-back-and-apply-log commands. As an alternative, you can update your complete backup with your incremental backup at any moment after taking an incremental backup but before the data is restored. What is the difference between incremental and full backup?Every block that has been used in the data file is included in a full backup of the data file. An image copy or backup set can both be considered full backups. An incremental backup copies only the blocks of a data file that have changed between backups. What are the 3 types of backups?The most common backup types are a full backup, incremental backup and differential backup. Other backup types include synthetic full backups and mirroring. What are the types of backup method?There are mainly three types of backup: full, differential, and incremental. Which type of backup includes during the backup process in MySQL?When connected to a running MySQL server, MySQL Enterprise Backup backs up all MyISAM and other non-InnoDB tables using the warm backup technique after all InnoDB tables have already been backed up with the hot backup method. What is full backup in MySQL?2 Making a Full Backup. Most backup strategies start with a complete backup of the MySQL server, from which you can restore all databases and tables. After you have created a full backup, you might perform incremental backups (which are smaller and faster) for the next several backup tasks. |