How to Backup MySQL Databases with 5 Different Methods?

As more and more data is stored in the database, data backup becomes more and more important. Database backup is a very critical task to ensure that companies or individuals will not suffer huge losses due to data loss. MySQL is a popular open source relational database management system, which is widely used in enterprises. This will discuss how to backup MySQL database.

Types of MySQL database backup

According to the size of the database, there are four types of backups, which are used in different occasions:

Full backup

This is a common method used by most people. It can back up the entire database, including all database objects such as user tables, system tables, indexes, views, and storing procedures. But it takes more time and space, so it is generally recommended to do a full backup once a week.

Transaction log backup

The transaction log is a separate file that records changes to the database. Backups only need to copy the changes made to the database since the last backup, so it takes very little time. For database robustness, hourly or even more frequent backups of the transaction log are recommended.

Differential backup

It is another method of backing up only part of the database, it does not use the transaction log, instead, it uses a new image of the entire database. It is smaller than the original full backup because it only contains the databases that have changed since the last full backup. It has the advantage of fast storage and recovery. It is recommended to do a differential backup every day.

File backup

A database can consist of many files on the hard disk. If the database is very large and cannot be backed up overnight, file backups can be used to back up part of the database every night. Since databases are generally not so large that multiple file stores must be used, this type of backup is not very common.

According to the status of the database, it can be divided into three types:

1. Cold backup

Cold backups are backups that are performed on data while the database is offline and not accessible to users. Since the backups are performed while the database is offline, cold backups are also often called offline backups. Cold backups often consume fewer resources and since no new data can be added (the database is offline), the backup process can back up all of the data in one go. Cold backups, of course, have a limitation in the fact that the database cannot be accessed when the backup operation is in progress.

2. Hot backup

Hot backups are backups that are performed on data while the database is online and accessible to users. Hot backups are often a very convenient solution because they do not require downtime and the disruption of operating system is very minimal, but they also have certain downsides: if the data that is being backed up is altered in any way while a backup is in progress, the backup might not be the same as the final version of the data in the database.

3. Logical backup

Logical backup uses software to extract data from a database and writes the results to a file.

How to backup MySQL databases?

There are logical backup and physical backup for MySQL according to the backup recovery method. Logical backup is to back up SQL statements, and execute them during recovery to reproduce the database data. Physical backup copies database files in the same format, in which they are stored on the disk.

These two types of backups have their own advantages and disadvantages. Generally speaking, the recovery speed of physical backup is faster but takes up more space, while the speed of logical backup is slower but takes up less space.

1. Using mysqldump

Mysqldump is a backup tool that comes with MySQL, under the bin directory: /usr/local/mysql/bin/mysqldump, which supports hot backup based on innoDB. However, because it is a logical backup, the speed is not very fast, and it is suitable for scenarios where the backup data is relatively small. Mysqldump full backup + binary log can achieve point-in-time recovery.

For the tables of the MyISAM storage engine, only hot backup can be used. At this time, data writing should be prevented, so first add a read lock. At this time, you can also enter the database to manually add a read lock, but this is troublesome. You can directly have a locking option in the mysqldump tool, which is --lock-all-tables, such as mysqldump --databases test --lock-all- tables --flush-logs > /tmp/backup_test_`date+%F-%H-%M`.sql.

If you are backing up a single table, just add the table name after the library name test.

For innoDB storage engine tables, hot backup can be performed without locking the database. Add an option to perform hot backup: --single-transaction, for example: mysqldump --databases test --single-transaction --flush-logs --master-data=2> /tmp/backup_test_`date+%F-%H-%M`.sql.

P.S. Remember to close the binary log when restoring:

mysql> set sql_log_bin=0;

Because this is based on a logical backup method, executing SQL will insert data and record it in the binary log. Because this is recovery, the inserted binary log can be turned off to shorten the recovery time.

Click and learn more about MySQL database backup and recovery with/without the Mysqldump.

2. Backup based on LVM snapshot

In terms of physical backup, there is a physical backup based on the file system (LVM snapshot), and you can also directly use commands such as tar to package and backup the entire database directory, but these can only be used for cold backup. Different storage engines back up different contents. MyISAM automatically backs up to the table level, while innoDB can only back up the entire database if it does not enable the independent table space.

The following is an introduction to using the snapshot function of LVM for backup.

1) For safety, first impose a read lock on the database

mysql>FLUSH TABLES WITH READ LOCK

2) Refresh the binary log for point-in-time recovery

mysql>FLUSH LOGS

3) Then create the snapshot volume

lvcreate –L 1G –s –n data-snap –p –r /dev/myvg/mydata

4) Enter the database to release the read lock

UNLOCK TABLES

5) Mount the snapshot volume for backup

mount –r /dev/myvg/data-snap /mnt/snap

6) Finally package and backup the files under /mnt/snap

For restoring:,

1) close mysqld

2) then back up the binary log

3) restore the original backup file

4) then restore to the point in time of the error through the binary log (don’t forget to temporarily close the binary log when restoring the time point through the binary)

Between 2010 and 2012, MySQL database was deployed in Amazon's cloud environment. They provided LVM snapshots, which are very convenient and fast. Using LVM snapshots, the recovery is also extremely fast under Amazon’s 10G network.

3. Tar backup

Prepare for the first data backup from the database, temporarily lock all tables, and open window 1

mysql> flush tables with read lock;

Query OK, 0 rows affected (0.00 sec)

mysql> show master status;

+++++

| File | Position | Binlog_Do_DB |Binlog_Ignore_DB |  

| mysql-bin.000003 | 194554 | | |  

1 row in set (0.00 sec)  

This window cannot exit, and it must be kept until the tar package is finished.

--Re-open a shell window, go to the data file directory tar package

open window 2

[root@myfstv_21_11 data]# tar -zcvf mysqla1.tar.gz mysqla

tar -zcvf mysqla1.tar.gz mysqla Compressed

Switch to window 1 and execute the unlock command

mysql> unlocktables;

Query OK, 0 rows affected (0.00 sec)

mysql>  

Copy the tar package to another MySQL library server, overwrite the data directory, and then restart the MySQL database service.

4. xTraBackup provided by Percona

It supports physical hot backup of innoDB, supports full backup and incremental backup, and the speed is very fast, supports data migration between different databases caused by innoDB storage, and supports slave backup and recovery in replication mode. In order for xTraBackup to support more functional extensions, you can set up an independent table space and enable the innoDB_file_per_table function. Then, you can support individual table backups.

xTraBackup backup principle:

xTraBackup is based on the crash-recovery function of innoDB. It will copy the data file of innoDB. Since the table is not locked, the copied data is inconsistent. When recovering, use crash-recovery to make the data consistent.

innoDB maintains a redo log, also known as transaction log, which contains all changes to innoDB data. When innoDB starts, it will first check the data file and transaction log, and will do following steps:

When xTraBackup is backing up, it copies innoDB data page by page without locking the table. At the same time, xTraBackup has another thread to monitor the transactions log. Once the log changes, it will copy the changed log pages away. Because the size of the transactions log file is limited, it will start writing from the beginning after it is full, so new data may overwrite old data.

During the process of preparing, xTraBackup uses the copied transactionslog to perform crash recovery on the backed up innoDB data file.

5. Backup MySQL databases with Vinchin Backup & Recovery

Vinchin Backup & Recovery delivers powerful functionality to protect your databases in both virtual machines and physical servers. By cooperating well with VM-level backups, dual insurance is given to the users of virtual environments for their key business data and information systems.

Vinchin Backup & Recovery supports protection of Oracle DB, MySQL, SQL Server, PostgreSQL, Postgres Pro, and MariaDB installed on both physical and virtual machines with powerful database backup and restore features. It also provides full backup, differential backup, incremental backup and transaction log backup strategies for you to set your own backup plan on demand.

Vinchin Backup & Recovery supports efficient hot backup of MySQL, SQL Server and Oracle without affecting the normal operation of databases.

You can start to use this powerful system with a 60-day full-featured free trial. Just click the button to get the installation package. You can click here to learn more about how to backup MySQL with Vinchin Backup & Recovery.

Sum up

MySQL database is widely used in many industries. In order to ensure its security, we share with you five different MySQL backup data methods in this article. You can choose a more suitable method according to your actual situation. what are you waiting for? Hurry up and take a backup of your MySQL database!