MySQL Learning Note 12: Data backup and Recovery

MySQL Learning Note 12: Data backup and Recovery

Data backup

1. Physical Backup and logical backup

Physical backup

Physical backup is to copy the database data files, configuration files, log files and so on to other paths, this backup speed is generally faster, because only I/O operations. When you perform a physical backup, you generally need to shut down the MySQL server, or lock the objects that need to be backed up, or you can easily create inconsistencies in the backup, and you may lose data when recovering. Physical backups are available in many ways, such as operating system command copy (CP), Scp,mysqlbackup, and the mysqlhotcopy of the MyISAM table.

Logical backup

A logical backup is a logical structure of the database (create Database,create table), and its stored data (converted to insert into) is backed up. This kind of backup is very flexible, but for large systems, the performance of a logical backup is slow to recover. The logical Backup tool has mysqldump,select...into outfile and so on.

2. Online Backup and offline backup

An online backup is also called a hot backup, which is a backup when the MySQL server is running, an offline backup, a cold backup, and a backup after the MySQL server stops running.

The advantage of a hot backup is that it does not affect other clients accessing the MySQL service and can still read the data normally. However, when you perform a hot backup, you need to avoid data modifications that affect the consistency and integrity of your backups.

Cold backup is a bit of no client connection, directly to the backup. However, in order to reduce the impact on the business, try to backup on the slave side.

3. Full and incremental backups

A full backup is a backup of all the data in the entire database, and the incremental backup is the modification sent after the specified point in time, and the incremental backup is based on the MySQL binary log.

Physical Backup of 4.MyISAM tables

Method One: Lock table in Copy, example

Mysql> Flush Tables StuWithReadLock Query OK,0 rows affected (0.00SEC) Copy "D:\ProgramData\MySQL\MySQL Server5.5\data\test\t2. * "F:\backupd:\ Programdata\mysql\mysql Server 5.5 \data\test\t2.frmd:\programdata\mysql\mysql Server 5.5\data\test\t2. Mydd:\programdata\mysql\mysql Server 5.5 \data\test\t2. Myi copied 3 files. Mysql> unlock tables; Query OK, 0 rows affected (0.00 sec)               

Method Two: Use the mysqlhotcopy command

The use of mysqlhotcopy under the Windows platform requires the installation of Perl and the DBD-MYSQL,DBI module, but the Mysqlhotcopy command also invokes the operating system command (CP,SCP) to copy the file, so it cannot be used under the Windows platform. So you can experiment on the Linux platform.

--Installation of---------------------DBD module--------------------C:\Users\Lenovo>PPM Install dbd::mysqldownloading DBD-mysql-4.029... doneunpacking DBD-mysql-4.029... donegenerating HTMLFor DBD-mysql-4.029... doneupdating FilesInchSite Area...done11Files Installed----------------mysqlhotcopy command to use------------------C:\Users\Lenovo>mysqlhotcopy.pl-U Root-P123456 Test F:/BackupFlushedTablesWithReadLock (' Test '. ' B ', ' Test '. ' Emp_date ', ' Test '. ' T1 ', ' Test '. ' T2 ', ' test '. ' T3 ', ' Test '. ' tb_1 ', ' Test '. ' tb_2 ', ' Test '. ' Tb_3 ', ' Test '. ' Tb_5 ', ' Test '. ' Tb_6 ', ' test '. ' Users 'Inch1Seconds. Locked0 Views ()Inch0Seconds. Copying22Files ...------------------Verify the results on Ubuntu---------------------Root@zhumuxian-machine:/# mysqlhotcopy-U Root-P123456 Test/data/mysql/BackupWarning:/usr/bin/mysqlhotcopyis deprecatedand would be removedInchA future version. Flushed1 tablesWithRead lock (' Test '. ' Stu ')Inch0Seconds. Locked0 Views ()Inch0Seconds. Copying4 files ... Copying indices 0 files ... Unlocked tables.mysqlhotcopy copied 1 tables (4 files) in 0 seconds (0 seconds overall). Root @zhumuxian -machine:/# ls /data/ Mysql/backup/test                 

5. Using Mysqldump for logical backups

The backup set created with the Mysqldump tool is available in two formats, one for the standard SQL statement and the other for the file in the bounding format.

Back up a single database (Specify a specific table, followed by a table name after the database name)

PS c:\users\lenovo> mysqldump-u root-p123456--default-character-set GBK zz > F:\zz.sql

Backing up multiple databases

PS f:\> mysqldump-u root-p7758520--default-character-set gbk--databases zz,test > Zztest.sql

Back up all the databases (if there is a database that does not exist, you will get an error)

PS f:\> mysqldump-u root-p7758520--default-character-set GBK--all-databases > Db_all.sql

Output into a bound format file

PS f:\> mysqldump-u root-p123456--default-character-set gbk--tab=f:\mysql_backup--fields-terminated-by ', '-- lines-terminated-by ' \ r \ n ' zz Stu---------------If the specified path does not exist, The error will be ErrorCode2-------------------------------------PS f:\mysql_backup> dirmode                lastwritetime     Length Name----                -------------     -----------A---         2015/4/21      9:18       1482 stu.sql-a---         2015/4/21      9:18        108 stu.txt    

6. Restore the backup created by the mysqldump command, using the MySQL command

SQL format Recovery

F:\mysql_backup>mysql-u root-p123456--default-character-set=UTF8  zz <. \zz_stu.sql 

Fixed-boundary format recovery

Because this backup is a backup of the Table object and its data is backed up, the Table object is first restored and then imported into the table data using the Load data infile statement.

View Code

7. Creation of incremental backups

MySQL's incremental backup mainly relies on the binary log, so you must turn on the binary logging feature, you can set the Log-bin parameter in the configuration file or specify the value of the--log-bin parameter when the MySQL service is turned on. The so-called incremental backup is the backup of the binary log generated after the cold backup to the specified path, when the need for recovery, the first cold backup recovery, and then the backup of the binary log analysis, and then get the MySQL command to execute.

Parsing the binary log and outputting it to a SQL file

[Email Protected]:/home/zhumuxian#>/data/backup/000001.sql 

SQL file content using MySQL command to perform analysis

[Email protected]:/data/backup#  

The above two commands can be combined with pipelines to write

[Email Protected]:/home/zhumuxian# Mysqlbinlog--no-defaults/data/mysql/mysql-bin.000001 | mysql-u root- p123456 

If a similar "Unknown variable Default-character-set=utf8" error occurs when you execute the mysqlbinlog command, it is generally a variable that the client cannot identify the configuration file, as follows:

Method One: In the configuration file, comment out the variable

Method Two: When you execute the mysqlbinlog command, add the--no-defaults option

Method Three: Change the variable to loose-variable-name=xxx (here I am Loose-default-character-set=utf8)

Because the MySQL binary log is recorded in row format, using Base64 encoding, almost do not understand, you can use the--base64-output option parameter to solve, the parameter has three built-in values: None (do not do row format processing), decode-rows (decoding processing, Typically used in the-v combination), auto (default, in row format), as shown below:

-----------------------Base64 encoded Information---------------------------  Moa1vq8baaaadaaaahgaaaaaaaqans42lji0lwxvzwaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaacy4dvvezgnaagaegaebaqeegaax Aaegggaaaaicagcaaaacgokgrkaaune----------------------Use decode-rows display-------------------------[email Protected]:/data/backup# mysqlbinlog--no-defaults--base64-output=decode-rows-v/data/mysql/ mysql-bin.000001   

8. Third-Party online backup tool xtrabackup

Xtrabackup is a hot backup tool that supports backup of engine objects such as innodb,myisam,csv, or is more popular.

Installation of Xtrabackup:

First to Percona official website to download the latest version of the Xtrabackup, I use the percona-xtrabackup_2.2.10-1.trusty_i386.deb, installation is very simple, just one command, as shown below.

[Email protected]:/# dpkg-i percona-xtrabackup_2.2.10-1.trusty_i386.deb 

To see if the installation was successful

[Email Protected]:/usr/bin#
Innobackupex  innochecksum  xtrabackup-----------------or----------------------[email protected]:/usr /bin# xtrabackup--versionxtrabackup version 2.2.10 based on MySQL server 5.6.22 Linux (i686) (Revision ID:) 

The Xtrabackup tool provides two commands, Xtrabackup and Innobackupex.

Xtrabackup has two modes, backup (back up) and prepare (recovery), and can only back up innodb,xtradb engine objects.

To create a backup using the Xtrabackup command

[Email protected]:/data/mydata/backup# xtrabackup--defaults-file=/etc/mysql/my.cnf--backup--terget-dir=/ Data/mydata/backup 

The above execution xtrabbackup command uses three parameters:

--defaults-files: Specify the path to the MySQL profile, the most important feature when you get the value of DataDir

--backup: Specified as Backup

--target-dir: Specify the path to save the backup set

To view the path of a backup set

[Email protected]:/# ls/data/mydata/backup/ibdata1  test                    xtrabackup_checkpointsmysql    Xtrabackup_backupfiles  xtrabackup_logfile  

As you can see from the above results, xtrabackup only backs up data files and does not back up the structure file (. frm) of the table, so you also need to copy the table structure files manually.

The Innobackupex command, which supports multiple engine objects, creates a backup, requires a connection to the database, and can create a user dedicated to backup, as shown below.

Mysql>CreateUser [email protected]‘localhost' IdentifiedBy‘123456 0 rows affected (0.00 sec) Mysql> replication client,super on Span style= "color: #808080;" >*. * to [email protected]  ' localhost "; Query OK, 0 rows affected (0.00 sec)               

Use the Innobackupex command to create a full backup, as shown below.

Root@zhumuxian-machine:--defaults-file=/etc/mysql/my.cnf--user=innobk--password= ' 123456 '/data/ mydata/backup/   

The above command uses four parameters:--defaults-file Specifies the configuration file path--user user name--password user password [backup_dir] Specifies the path to which the backup set is saved

Create an incremental backup using Innobackupex

Root@zhumuxian-machine:--defaults-file=/etc/mysql/my.cnf--user=innobk--password= ' 123456 '-- Incremental--incremental-basedir=/data/mydata/backup/2015-04-21_22-18-27/data/mydata/incremental/  

In the command that created the incremental backup above, 2 new parameters were added:--incremental Specifies the path of the full backup that the backup is based on for incremental backup--INCREMENTAL-BASEDIR the specified incremental backup.

In fact, only the InnoDB engine object can be an incremental backup, because only the engine object has an LSN (log sequence number), the incremental backup is also based on LSN, and the other engine object incremental backup is a full backup.

Use Innobackupex full-scale recovery

----------------------Application log to achieve consistency of data files----------------[email protected]:/data/mysql# Innobackupex-- DEFAULTS-FILE=/ETC/MYSQL/MY.CNF--user=innobk--password= ' 123456 '--apply-log/data/mydata/backup/2015-04-21_ 22-18-27/------------------------Perform recovery--------------------------------[email protected]:/data/mysql#  Innobackupex--defaults-file=/etc/mysql/my.cnf--user=innobk--password= ' 123456 '--copy-back/data/mydata/backup/ 2015-04-21_22-18-27/--after execution, check that the permissions are correct     

Using Innobackupex incremental Recovery

-------------------------Application Log------------------------[Email Protected]:/data/mysql#Innobackupex--defaults-file=/etc/mysql/my.cnf--user=innobk--password= ' 123456 '--apply-log--redo-only/data/ mydata/backup/2015-04-21_22-18-27///an incremental backup must be specified--redo-Only parameter, force Xtrabackup to apply incremental backups only if the redo is not rolled back----------------------------------------------[Email Protected]:/data/mysql# Innobackupex--defaults-file=/etc/mysql/my.cnf--user=innobk--password= ' 123456 '--apply-log/data/mydata/ backup/2015-04-21_22-18-27/--incremental-dir=/data/mydata/incremental/2015-04-21_22-33-40/-------------------- --------rollback-------------------------[email protected]:/data/mysql# Innobackupex--defaults-file=/etc/ MYSQL/MY.CNF--user=innobk--password= ' 123456 '--apply-log/data/mydata/backup/2015-04-21_22-18-27/--------------- -------------copy-------------------------[email protected]:/data/mysql# Innobackupex--defaults-file=/ ETC/MYSQL/MY.CNF--user=innobk--password= ' 123456 '--copy-back/data/mydata/backup/2015-04-21_22-18-27/  

Using Innobackupex to package and compress backup sets, you can use the--stream parameter to specify the stream format and support only Tar,xbstream.

[Email protected]:/tmp# Innobackupex--defaults-file=/etc/mysql/my.cnf--user=innobk--password= ' 123456 '-- stream=tar/tmp | gzip >/data/mydata/backup/bk.tar.gz//stream Format the output data is temporarily saved in the/tmp directory, and then stored in the specified path using the pipeline, you need to use the-I parameter when extracting 

MySQL Learning Note 12: Data backup and Recovery