MySQL database backup and a collection of commonly used backup tools

1. Types of database backup

According to the size of the database backup, there are 4 types, which are used in different occasions. The following is a brief introduction:

1.1 Full backup

This is the way most people back up an 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 hourly or more frequently.

1.3 Differential backup

Also called incremental backup. It is another way of backing up only part 1 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. It has the advantage of fast storage and recovery. It is recommended to do a differential backup once a day.

1.4 File backup

A database can consist of many files on a hard disk. If the database is very large and cannot be backed up overnight, use file backup to back up a portion of the database each night. This type of backup is not often used because databases are generally not large enough to require multiple file stores.

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

1. Cold backup. At this time, the database is in a closed state, which can better ensure the integrity of the database.

2. Hot backup. The database is running. This method relies on the [1] log file of the database for backup.

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

2. Introduction to backup tools

mysql is divided into logical backup and physical backup according to the backup recovery mode. Logical backup is to back up sql statement, the sql statement that executes backup during recovery realizes the reproduction of database data, the physical backup is to back up data files, the comparison image point is the data file under cp, but the real backup is naturally not so simple as cp.

These 2 kinds of backup each have pros and cons, 1 generally speaking, the physical backup recovery speed is faster, take up more space, logical backup speed is slower, take up less space.

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

2.1 mysqldump tools

Mysqldump is the backup tool, mysql directory under the bin directory: / usr/local/mysql/bin/mysqldump, support based on innodb hot backup. However, because it is a logical backup, the speed is not very fast, suitable for small backup data scenario. Mysqldump full backup + base 2 log enables point-in-time recovery.

For the tables of the myisam storage engine, only warm backups can be used to prevent the data from being written, so first add a read lock. At this time, you can enter the database to read the lock manually, but this is more troublesome, you can directly have a lock in the mysqldump tool, is - ES70en-ES71en-ES72en, for example, mysqldump-ES74en test - ES76en-ES77en-ES78en - flush-ES80en > / tmp backup_test_ ` date + % H - % % F M `. sql.

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

For innodb storage engine table, can be hot backup, do not have to lock the database operation, add 1 option can be hot backup, -- ES96en-ES97en, for example: mysqldump --databases test -- ES101en-ES102en -- ES103en-ES104en -- ES105en-ES106en =2 > / tmp backup_test_ ` date + % H - % % F M `. sql.

PS: Be careful to turn off the base 2 log when resuming:

mysql > set sql_log_bin=0;
Since this is based on the logical backup method, sql inserts data, which is logged into the base 2 log. Because this is restored, the inserted base 2 log is basically meaningless and can be turned off to shorten the recovery time.

2.2 LVM snapshot based backup

In physical backup, there are based on the physical backup file system (LVM snapshot), may also directly with commands like tar packaging on the whole database directory for backup, but these only ling backup, backup of different storage engines don't 1 kind, myisam level of automatic backup to the table, but innodb don't open separate tablespace can backup the entire database.

Using the snapshot feature of LVM for backup is described below. To be safe, first apply a read lock on the database

mysql > FLUSH TABLES WITH READ LOCK
Refresh base 1 log for point-in-time recovery

mysql > FLUSH LOGS
The snapshot volume is then created

1 G lvcreate � L � s � n data - snap � p � r/dev/myvg/mydata
Finally, enter the database to release the read lock

UNLOCK TABLES
Mount the snapshot volume for backup

mount � r/dev/myvg/data - snap/mnt/snap
Then pack the files under /mnt/snap for backup

When restoring, close mysqld, then back up the base 2 log and restore the original backup file to the error point through the base 2 log (don't forget to temporarily close the base 2 log when restoring the point of time through the base 2 log)

Between 2010 and 2012, the mysql database was deployed in Amazon's cloud environment. They provided LVM snapshot, which was very convenient and fast. Using lvm snapshot, it recovered very quickly under Amazon's 10 gigabit network.

2.3 tar package backup

0, prepare 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 exit. Keep 1 until tar is finished.

Re-open an shell window and go to the tar package directory

Open Window 2

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

Switch to window 1 and execute the unlock command

mysql> unlocktables; 
Query OK, 0 rows affected (0.00 sec) 
mysql> 
 copy tar Package to another 1 a mysql Library server, overwrite data Directory, and then restart mysql Database services. 

2.4 xtrabackup tools provided by percona

Supports innodb physical hot backup, a full backup, incremental backup, and very fast, support innodb storage of data migration between different database, supports replication mode from machine backup restore backup recovery, in order to make xtrabackup support more extension function, can set up a separate table space, open innodb_file_per_table function, enable after backup can support a single table.

Support online hot standby and recovery

When there is a large amount of data, backup and recovery are faster.

xtrabackup enables full backup, incremental backup, and partial backup.

xtrabackup Backup principle

xtraBackup crash-recovery function based on InnoDB. It will copy THE data file of innodb. Since the table is not locked, the copied data will be different. When restoring, it will use ES243en-ES244en to restore the data to 1.

InnoDB maintains one redo log, also known as transaction log, transaction log, which contains all changes to innodb data. When InnoDB is started, it will first check data file and transaction log, and will do 2 steps:

At the time of the backup, xtraBackup copied innodb's data page by page without locking the table. Meanwhile, XtraBackup had another thread monitoring transactions log. Once log changed, the changed log pages was copied away. Why the rush to copy? Because the transactions log file size is limited, it will start from scratch when it is full, so the new data may overwrite the old data.

In the process of prepare, XtraBackup USES the copied transactionslog to perform crash recovery for the backed up innodb data file.

Domestic windows server users can use:

Guardian Good Backup Software Free version (sqlserver, automatic file backup)

Please click es298EN8net mysql management tool MyAdmin v1.0 (mysql timed Backup tool)

Above is MySQL database backup and common backup tools summary, I hope you can enjoy.