Shulou(Shulou.com)05/31 Report--

Editor to share with you the types of MySQL database backup and which backup tools are commonly used. I hope you will gain something after reading this article. Let's discuss it together.

1. Types of database backups

There are four types of MySQL backups based on database size, which are used in different situations. Here is a brief introduction:

1.1 full backup

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

1.2 transaction log backup

The transaction log is a separate file that records changes to the database. The backup only needs to copy the changes made to the database since the last backup, so it takes very little time. To make the database robust, it is recommended to back up transaction logs every hour or even more frequently.

1.3 differential backup

Differential backup, also known as incremental backup, is another way to back up only part of the database. It does not use transaction logs. Instead, it uses a new image of the entire database. It is smaller than the original full backup because it contains only the databases that have changed since the last full backup. Its advantage is fast storage and recovery. It is recommended to make a differential backup once a day.

1.4 File backup

The database can be made up of many files on the hard disk. If the database is very large and cannot be backed up overnight, you can use file backup to back up part of the database every night. This kind of backup is not very common because the database is not so large that you have to use multiple file stores.

According to the state of the database, it can be divided into three categories:

1. Cold backup, when the database is closed, can better ensure the integrity of the database.

two。 Hot backup, the database is running, this method depends on the database [1] log file for backup.

3. Logical backup, using software to extract data from the database and write the results to a file.

2. Brief introduction of backup tools

According to the backup recovery mode, MySQL is divided into logical backup and physical backup. Logical backup is the backup sql statement, when the backup sql statement is executed to reproduce the database data, the physical backup is the backup data file, the more vivid point is the data file under cp, but the real backup is naturally not as simple as cp. These two kinds of backups have their own advantages and disadvantages. Generally speaking, the recovery speed of physical backup is relatively fast, the occupation of space is relatively large, the speed of logical backup is relatively slow, and the occupation of space is relatively small.

Official address: http://dev.mysql.com/doc/refman/5.6/en/backup-and-recovery.html

2.1 mysqldump tools

Mysqldump is a backup tool that comes with mysql. The directory is under the bin directory: / usr/local/mysql/bin/mysqldump. Hot backup based on innodb is supported. However, because it is a logical backup, the speed is not very fast, so it is suitable for scenarios where backup data is relatively small. Mysqldump full backup + binary log enables point-in-time recovery.

For the tables of the myisam storage engine, only warm backup can be used, so read locks are added first to prevent data writing. At this time, you can also enter the database to manually add read locks, but this is troublesome. You can directly choose to add locks 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, simply add the table name after the library name test.

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

PS: note that when you restore, remember to turn off the binary log:

Mysql > set sql_log_bin=0

Because this is based on logical backup, executing sql will insert the data and record it in the binary log. Because of the recovery, the inserted binary log is basically meaningless and can be closed to shorten the recovery time.

2.2 LVM snapshot based backup

In the physical backup, there is a physical backup based on the file system (snapshot of LVM), or you can directly use commands such as tar to pack and back up the entire database directory, but these can only be backed up by cold backup, and different storage engines can back up the same. Myisam automatically backs up to the table level, while innodb can only back up the entire database if the independent tablespace is not enabled.

Here's how to use LVM's snapshot feature for backup. For security, first impose a read lock on the database.

Mysql > FLUSH TABLES WITH READ LOCK

Refresh the binary log to facilitate point-in-time recovery

Mysql > FLUSH LOGS

Then create a snapshot volume

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

Finally, enter the database to release the read lock.

UNLOCK TABLES

Mount snapshot volumes for backup

Mount-r / dev/myvg/data-snap / mnt/snap

Then package and back up the files under / mnt/snap

When restoring, close mysqld, then back up the binary log and restore the original backup files, and then restore the binary log to the point in time where the error occurred (don't forget to close the binary log temporarily when restoring the point in time via binary)

Between 2010 and 2012, mysql databases were deployed in Amazon's cloud environment, and they provided LVM snapshots, which were quick and convenient, and recovered very quickly under Amazon's 10-Gigabit network using lvm snapshots.

2.3 tar package backup

0, prepare for the first data backup from the library, temporarily lock all tables, 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)

PS: this window cannot be exit, so keep it until tar is finished.

-- reopen a shell window and 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 compression completed

Switch to window 1 and execute the unlock command

Mysql > unlocktables

Query OK, 0 rows affected (0.00 sec)

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

2.4 xtrabackup tools provided by percona

Support innodb physical hot backup, support full backup, incremental backup, and very fast, support innodb storage caused by data migration between different databases, support replication mode slave backup recovery backup recovery, in order to allow xtrabackup to support more functional expansion, you can set up independent tablespaces, open innodb_file_per_table function, enable to support separate table backup.

Download address: http://www.percona.com/software/percona-xtrabackup

Reference article address: http://www.linuxidc.com/Linux/2015-03/115293.htm

Support online hot backup and recovery

When there is a large amount of data, the backup recovery is relatively fast.

Xtrabackup can achieve full backup, incremental backup, and partial backup.

Principle of xtrabackup backup

XtraBackup is based on the crash-recovery function of InnoDB. It will copy the data file of innodb, because the table is not locked, the copied data is inconsistent, and crash-recovery is used during recovery to make the data consistent.

InnoDB maintains a redo log, also known as transaction log, a transaction log that contains all changes to innodb data. When InnoDB starts, it checks data file and transaction log first, and does two steps:

When xtraBackup backs up, it copies innodb data page by page without locking the table. At the same time, XtraBackup has another thread monitoring transactions log, and once the log changes, it copies the changed log pages away. Why the rush to copy away? Because the size of the transactions log file is limited, when it is full, it will start all over again, so the new data may overwrite the old data.

During the prepare process, XtraBackup uses the copied transactionslog to crash recovery the backed up innodb data file.

After reading this article, I believe you have a certain understanding of "MySQL database backup types and common backup tools". If you want to know more about it, you are welcome to follow the industry information channel. Thank you for reading!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.