Mysql database backup and common backup tools collection _mysql

Type of database backup

According to the database size backup, there are four types, applied to different occasions, the following briefly describes:

1.1 Full backups

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's generally recommended to do a full backup once a week.

1.2 Transaction Log Backups

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

1.3 Differential backups

Also called an incremental backup. It is another way to back up only a subset of the database, instead of using the transaction log, which 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 storing and recovering faster. It is recommended to make a differential backup every 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, you can use a file backup to back up a portion of the database nightly. This kind of backup is not commonly used because the database is typically not large to have to use multiple file stores.

According to the state of the database can be divided into three kinds:

1. Cold Backup , at this time the database is in the shutdown state, can better guarantee the integrity of the database.

2. Hot backup , the database is running, this method relies on the database's [1] log files for backup.

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

Introduction of Backup Tools

MySQL is divided into logical and physical backups in the form of backup recovery. Logical backup is a backup of SQL statements, in the recovery of the SQL statement to perform the backup of the database data, the physical backup is the backup data file, the comparison image point is CP under the data file, but the real backup when the nature is not the CP so simple.

These 2 kinds of backup have advantages and disadvantages, generally speaking, physical backup recovery faster, occupy space is relatively large, logical backup speed is relatively slow, occupy 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 MySQL-backed backup tool, with directories under the bin directory:/usr/local/mysql/bin/mysqldump, which supports INNODB based hot backup. However, because it is a logical backup, the speed is not very fast, suitable for backup data relatively small scenarios. Mysqldump full backup + binary logging enables Point-in-time recovery.

For MyISAM storage engine tables, you can only use warm backup, this time to prevent data writing, so first add read lock. This time can also enter the database manual read lock, but this is more cumbersome, you can in the Mysqldump tool directly have a lock option, 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, add the table name directly after the library name test.

For InnoDB storage engine tables, you can hot standby, do not have to lock the database operation, plus an option to hot backup,--single-transaction, for example: mysqldump--databases test- Single-transaction--flush-logs--master-data=2>/tmp/backup_test_ ' Date +%f-%h-%m '. sql.

PS: Note the point, restore the time to remember to turn off the binary log:

Mysql> set sql_log_bin=0;
Because this is based on logical backup, the Execute SQL inserts the data and logs it into the binary log because it is restored, so the inserted binary log has little meaning, can be turned off, and the recovery time is shortened.

2.2 Based on LVM snapshot backup

In a physical backup, there is a physical backup based on the file system (a snapshot of LVM), or you can package the entire database directory directly with commands such as tar, but these can only be used for the cold backup, different storage engine backup is not the same, MyISAM automatic backup to the table level, InnoDB can only back up the entire database without opening the standalone tablespace.

The following is a brief introduction to using the LVM snapshot feature for security, read locks are first applied to the database

Mysql>flush TABLES with READ LOCK
Refresh binary log for point-in-time recovery

Mysql>flush LOGS
Then create the 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 pack up the files under/mnt/snap

When you restore, close mysqld, then back up the binary log and restore the original backup file, and then restore to the wrong point of time through the binary log (do not forget to temporarily close the binary log at the time of the binary restore point)

Between 2010 and 2012, the MySQL database is deployed in the Amazon Cloud environment, they provide an LVM snapshot, very convenient, using LVM snapshots, in the Amazon's million-gigabit network, recover quickly incomparable.

2.3 Tar Pack Backup

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

PS: This window can not exit, to remain aware of the tar end of the package until.

--Reopen a shell window, go to data file directory tar Package

Open Window 2

[Root@myfstv_21_11 data]# TAR-ZCVF mysqla1.tar.gz Mysqla 

Switch to Window 1, execute unlock command

Mysql> Unlocktables; 
Query OK, 0 rows Affected (0.00 sec) 
mysql> 
 Copy tar package to another MySQL library server, overwrite the data directory, and then restart the MySQL database service.

Xtrabackup tools provided by 2.4 Percona

Supports InnoDB physical hot backup, supports full backups, incremental backups, and is very fast, supports the migration of data caused by InnoDB storage between different databases, supports backup recovery from backup in replication mode, and enables Xtrabackup to support more functional extensions. You can set up a separate table space, turn on the innodb_file_per_table feature, and enable individual table backups to be supported.

Support Online hot standby and recovery

When the volume of data is large, backup recovery is relatively fast.

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

Xtrabackup Backup principle

Xtrabackup is based on the InnoDB crash-recovery function. It will copy the InnoDB data file, because the table is not locked, the copy is inconsistent, in the recovery of the use of crash-recovery, so that data recovery consistent.

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

Xtrabackup copies the InnoDB data one page at a time of backup and does not lock the table, while Xtrabackup has another thread monitoring the transactions log and copying the changed log pages once the log changes. Why rush to copy away? Because the transactions log file size is limited, it will be written from the beginning, 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.

Domestic Windows Server users can use:

Good security. Backup software free Edition (SQL Server, file automatic backup)

Please platform p8net MySQL management tools MyAdmin v1.0 (MySQL timed Backup tool)

The above is MySQL database backup and common Backup tool Summary, I hope you can like.