Linux implementation of MYSQL data backup and Restore command using the full Raiders _mysql

In order to ensure data security, the data needs to be backed up regularly. There are a number of ways to backup, and the effect is different. Once there is an error in the data in the database, you need to use the backed-up data for restore recovery. Thus minimizing the loss. Let's take a look at MySQL common three types of backup recovery methods:

1, using mysqldump+ binary log to achieve backup
2, using LVM snapshot + binary log to achieve backup
3. Use Xtrabackup Backup

First: Experimental Environment Introduction:

System Introduction: Centos6.4_x64
Database version: mysql-5.5.33

II: Backup recovery based on mysqldump command

2.1. Concept of Ideas

Mysqldump is a logical backup command, which means that the data in the database is backed up into a text file, or that the structure and data of the table are stored in a text file.

The mysqldump command works very simply by identifying the structure of the table that needs to be backed up, and then generating a CREATE statement in a text file. Then, convert all the records in the table to a Instert statement. These create statements and Instert statements are used when restoring. You can create a table by using the CREATE statement in the data when you restore it. Use the INSERT statement to restore the data. It can implement a full server backup, or it can implement a single or partial database, a single or partial table, some rows in a table, stored procedures, stored functions, triggers, and automatically record the binary log files and corresponding locations for the backup time. For the InnoDB storage engine, support for hot standby based on single transaction mode is supported, and the maximum support for MyISAM is Win Bei.

2.2. Backup strategy

Mysqldump full standby + binary log preparation

2.3. Process Realization

(1) Mysqldump fully prepared
Because the MySQL database defaults to the MyISAM storage engine, it only uses Win Bei (the backup supports only read requests), so we'll add read locks for all databases

[Root@stu18 ~]
#mysqldump-uroot-pmypass--lock-all-tables--master-data=2--events--routines--all-databases >/zhao/database_ ' Date +%f '. sql

Parsing: –lock-all-tables represents the imposition of read locks for all tables; –master-data=2 represents the location of the current binary log in the backup file, and the backup time Scheduler code while the –events represents the backup data; Routines represents backing up stored procedures and stored functions while backing up data; –all-databases indicates that all libraries are backed up.

[Root@stu18 Zhao]
# less Database_2013-08-13.sql--#表示注释项--   
Position to
start replication or point-in-time recovery from ---Change
MASTER to master_log_file= ' mysql-bin.000001 ', master_log_pos=14203; 
#这里表示当前处于mysql-bin.000001 This binary log, the event is 14203 this is generated by--master-data=2----current
Database: ' Hellodb '
--
CREATE DATABASE/*!32312 IF not exists*/' Hellodb '/*!40100 DEFAULT CHARACTER SET UTF8 * *;

(2) Full preparation of binary system

Method One: Export the contents of the binary log file

[Root@stu18 Data]
# Mysqlbinlog mysql-bin.000001 >/zhao/binlog_ ' Date +%f '. sql

Method Two: Scroll log copy files

mysql> flush logs; 
#滚动日志
[root@stu18 data]
# CP mysql-bin.000001/zhao/mysql-bin.000001 #复制导出二进制文件

(3) Binary preparation
First Add data information

mysql> use Hellodb;
Mysql> INSERT into students (Name,age,gender,classid,teacherid) values (' Yang Kang ', DE, ' M ', 3, 3);

Then binary preparation

[Root@stu18 Data]
# mysqlbinlog--start-position=14203--stop-position=14527 mysql-bin.000001 >/zhao/binlog_ ' Date +%F_%H '. sql

Parsing: –start-position=14203 is the binary event position after the last full preparation, –stop-position=14527 the binary event location for the most recent day.

2.4, the simulation of database damage, to achieve recovery work

mysql> DROP DATABASE Hellodb;    
#删除数据库
########### #下面这些过程要在离线状态下执行 ############
mysql> SET sql_log_bin=0;     
#先关闭二进制日志
mysql> flush logs;      
#滚动日志
[root@stu18 ~]
# Mysql-uroot-pmypass </zhao/database_2013-08-13.sql #导入数据库备份文件
[root@stu18 ~]< c10/># Mysql-uroot-pmypass </zhao/binlog_2013-08-13_19.sql #导入增量备份文件
[root@stu18 ~]
# mysql-uroot– Pmypass #登录查看, restore complete
mysql> SET sql_log_bin=1;

This backup method is simple to restore, but the index will have an error to rebuild after the restore restore, and the backup result will occupy a lot of space, please use it as appropriate.

Summary of Ps:mysqldump Common commands
commands for backing up the MySQL database

Mysqldump-hhostname-uusername-ppassword databasename > Backupfile.sql

Backing up the MySQL database for a deleted table format

Backing up the MySQL database is a format with a deleted table that allows the backup to overwrite the existing database without having to manually delete the original database.

Mysqldump-–add-drop-table-uusername-ppassword databasename > Backupfile.sql

Directly compress MySQL database to backup

Mysqldump-hhostname-uusername-ppassword DatabaseName | gzip > backupfile.sql.gz

Backing up a MySQL database (some) tables

Mysqldump-hhostname-uusername-ppassword databasename specific_table1 specific_table2 > Backupfile.sql

Backup multiple MySQL databases at the same time

Mysqldump-hhostname-uusername-ppassword–databases databasename1 databasename2 databasename3 > Multibackupfile.sql

Just back up the database structure

Mysqldump–no-data–databases databasename1 databasename2 databasename3 > Structurebackupfile.sql

Back up all databases on the server

Mysqldump–all-databases > Allbackupfile.sql

command to restore MySQL database

Mysql-hhostname-uusername-ppassword DatabaseName < Backupfile.sql

Restoring a compressed MySQL database

Gunzip < backupfile.sql.gz | Mysql-uusername-ppassword DatabaseName

Transferring a database to a new server

Mysqldump-uusername-ppassword DatabaseName | Mysql–host=*.*.*.*-C DatabaseName

Three, based on LVM snapshots to achieve backup recovery

3.1, train of thought detail

(1) LVM This type of backup requires MySQL data to be kept on logical volumes
(2) need to apply read lock to MySQL server (Mysql>flush TABLES with Readlock;), you can not exit the server directly
(3) Another terminal creates a snapshot (Lvcreate) for the volume on which the data resides, ensuring that the transaction log and data files must be on the same volume (creating separate data files and transaction log inconsistencies, which may result in an inability to recover normally)

3.2. Backup strategy

LVM snapshot full + binary log provisioning (restore to subsequent binary locations for Point-in-time restores)

3.3. Prerequisite conditions

(1) Create logical volumes and mount logical volumes, this procedure is not demonstrated at this point

(2) Initialize MySQL to point its data directory to/mydata/data

[Root@stu18 ~]
# cd/usr/local/mysql/
[root@stu18 MySQL]
# scripts/mysql_install_db--user=mysql--datadir=/mydata/data

(3) Edit view configuration file, restart service

[Root@stu18 MySQL]
# vim/etc/my.cnf
datadir =/mydata/data 
#查看此项是否定义数据目录位置
sync_binlog=1 
#添加此项, when each transaction is committed, Write the transaction log from the buffer to the log file, and refresh the log file data to disk;
[root@stu18 MySQL]
# service mysqld start

3.4. Process Display

(1) Ensure that transaction logs and data files must be on the same volume

[Root@stu18 ~]
# ls/mydata/data/
hellodb  MyClass   mysql-bin.000003 stu18.magedu.com.err
ibdata1  MySQL    mysql-bin.000004 stu18.magedu.com.pid
ib_logfile0 mysql-bin.000001 mysql-bin.index student
Ib_logfile1 mysql-bin.000002 Performance_schema Test

Parsing: Where IB_LOGFILE0 and ib_logfile1 are log files
(2) Apply global lock and scroll log

Mysql> FLUSH TABLES with READ LOCK;
Mysql> FLUSH LOGS;

(3) View and save the binary log currently in use and the currently executing binary log location (very important)

Mysql> show MASTER STATUS;
+------------------+----------+--------------+------------------+
| File    | Position | binlog_do_db | binlog_ignore_db |
+------------------+----------+--------------+------------------+
| mysql-bin.000004 |  187 | | |
+------------------+----------+--------------+------------------+
[root@stu18 Zhao]
# mysql-uroot- Pmypass-e ' show MASTER STATUS; ' >/zhao/lvmback-2013-08-14/binlog.txt

(4) Create a snapshot volume

[Root@stu18 Zhao]
# lvcreate-l 100m-s-P r-n mydata-lvm/dev/vg1/mydata

(5) Immediately switch terminal release lock

Mysql> UNLOCK TABLES;

(6) Backup data

[Root@stu18 Data]
# Cp-a */zhao/lvmback-2013-08-14/

(7) Binary implementation of incremental backup

mysql> use Hellodb;   
#指定默认数据库
Database changed
mysql> CREATE TABLE testtb (id int,name CHAR ());  
#创建表
Query OK, 0 rows affected (0.35 sec)
mysql> INSERT into TESTTB VALUES (1, ' Tom ');   
#添加数据
Query OK, 1 row affected (0.09 sec)
[root@stu18 data]
# Mysqlbinlog--start-position=187 mysql-bin.000004 >/zhao/lvmlogbin_2013-08-14/binlog.sql   #日志实现增量备份

(8) Simulated database crashes

[Root@stu18 ~]
# service mysqld Stop
[root@stu18 ~]
# cd/mydata/data/
[root@stu18 data]
# RM-RF *

(9) Recover data

[Root@stu18 ~]
# cp/zhao/lvmback-2013-08-14/*/mydata/data/-a   #完全备份恢复
[root@stu18 ~]
# cd/mydata/data/    #查看恢复数据内容
[root@stu18 data]
# chown-r Mysql.mysql * #更改属主属组
[root@stu18 Data]
# service mysqld start  #启动服务 C11/>[root@stu18 data]
# Mysql-uroot–pmypass #登录测试

Mysql> show DATABASES;  
#查看数据完整性, no test table TESTTD uses binary recovery
mysql> SET sql_log_bin=0 
#关闭二进制日志
mysql> source/zhao/lvmlogbin_ 2013-08-14/binlog.sql; 
#二进制恢复
mysql> show TABLES;   
#查看恢复结果
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes   |
| coc    |
| courses   |
| scores   |
| Students | | teachers | | | testtb | |
toc    |
+-------------------+
mysql> SET sql_log_bin=1; 
#开启二进制日志

This tool is implemented close to hot standby, and it is very fast to back up the recovery data in this way.

IV: Based on xtrabackup to achieve backup recovery

4.1. Advantages and characteristics

Completely in the form of hot standby, able to achieve fast and reliable full backup and partial backup, support incremental backup, support Point-in-time restore, the backup process will not disturb the transaction operations, can achieve network transmission and compression functions to effectively save disk space, after the completion of the backup can automatically verify the availability of data, recovery faster and so on. For more advantage features please refer to Http://www.percona.com/software/percona-xtrabackup

Note: These advantages can only be achieved perfectly on the InnoDB engine, while the MyISAM storage engine remains up to Win Bei and does not support incremental backups.
In addition Xtrabackup more advanced features also rely on the MySQL database for InnoDB to achieve a separate table space, otherwise there is no way to implement a single table import and export view is as follows:

Mysql> show GLOBAL VARIABLES like ' innodb_file% ';
+--------------------------+----------+
| Variable_name   | Value |
+--------------------------+----------+
| Innodb_file_format  | Antelope |
| Innodb_file_format_check | On  |
| innodb_file_format_max | Antelope |
| innodb_file_per_table | On  |
+--------------------------+----------+

Where the innodb_file_per_table is on means that a single form space is implemented. If off, you need to use mysqldump and then change the configuration file to delete the original data file and reinitialize the server and finally re-import the data. Therefore, it is recommended that you install the MySQL server at a later time by default setting the option to 1 (innodb_file_per_table = 1). The data display form of single form space is:

[Root@stu18 Hellodb]
# ls
classes.frm coc. MyD  courses. Myi scores. Myi teachers.frm testtb.ibd
classes. MyD coc. Myi  db.opt  students.frm teachers. MyD toc.frm
classes. Myi courses.frm scores.frm students. MyD teachers. Myi TOC. MyD
coc.frm  courses. MyD scores. MYD students. Myi testtb.frm TOC. Myi

4.2. Installation Xtrabackup

Download Percona-xtrabackup The latest version is 2.1.4 (percona-xtrabackup-2.1.4-656.rhel6.x86_64.rpm)
Installation:

[Root@stu18 ~]
# RPM-IVH percona-xtrabackup-2.1.4-656.rhel6.x86_64.rpm

Install Perl-dbd-mysql Dependency Pack If there are errors that cannot be installed

[Root@stu18 ~]
# yum-y Install Perl-dbd-mysql

Note: There may be multiple relational packages depending on the environment, please follow the prompts to configure

4.3. Full backup

When using a Innobakupex backup, it invokes Xtrabackup to back up all innodb tables, copying all relevant files (. frm) about the table structure definition, and related files for the MyISAM, MERGE, CSV, and archive tables. It also backs up files related to triggers and database configuration information. These files are saved to a time-ordered directory. Full backup commands are as follows:

# Innobackupex--user=dbuser--password=dbuserpass/path/to/backup-dir/

Implementation process and Description:

[Root@stu18 ~]
# mkdir/innobackup    #创建备份文件目录
[root@stu18 ~]
# Innobackupex--user=root--password=mypass/innobackup/# Full backup
############### #如果执行正确其后输出的几行信息通常如下 ###############
xtrabackup:transaction log of LSN (1604655) to ( 1604655) was copied. 
#二进制日志的位置 (LSN)
130814 07:04:55 innobackupex:all tables Unlocked
innobackupex:backup created in directory '/ innobackup/2013-08-14_07-04-49 '
#备份文件保存的位置
innobackupex:mysql binlog position:filename ' Mysql-bin.000003 ', Position 538898
130814 07:04:55 innobackupex:connection to database server closed
130814 07:04:55 innobackupex:completed

Ok! Backup complete
Switch to the backup file directory to view the Backed-up data information and create the generated files:

 
[Root@stu18 ~]
# cd/innobackup/2013-08-14_07-04-49/
[root@stu18 2013-08-14_07-04-49]
# ls
backup-my.cnf MyClass    Student   xtrabackup_binlog_info
hellodb  mysql    test    xtrabackup_checkpoints
ibdata1  Performance_schema xtrabackup_binary Xtrabackup_logfile

For file resolution:

(1) xtrabackup_checkpoints--the type of backup (such as full or incremental), backup state (such as whether it is already prepared state) and LSN (log sequence number) range information;
Each InnoDB page (typically 16k size) contains a log sequence number, which is the LSN. LSN is the system version number of the entire database system, and each page-related LSN can indicate how the page has changed recently.
(2) The binary log file that the Xtrabackup_binlog_info--mysql server is currently using, and the location of the binary log event until this moment of backup.
(3) The Xtrabackup executable file used in xtrabackup_binary--backup;
(4) backup-my.cnf--the configuration option information used in the backup, that is, the related file configuration of mysqld in the configuration file;
(5) xtrabackup_logfile--non-text file is the log file of Xtrabackup itself;

4.4. Prepare a full backup

Typically, after a backup completes, the data cannot be used for recovery operations, because the backed-up data may contain transactions that have not yet been committed or that have been committed but have not yet been synchronized to the data file. Therefore, the data file is still in an inconsistent state. The primary role of "preparation" is to make the data file in a consistent state by rolling back uncommitted transactions and synchronizing the committed transactions to the data file.

The –apply-log option for the Innobakupex command can be used to implement these functions. As the following command:

[Root@stu18 ~]
# innobackupex-apply-log/innobackup/2013-08-14_07-04-49/
############ #如果执行正确, the last few lines of information that it outputs are usually as follows ################
xtrabackup:starting shutdown with innodb_fast_shutdown = 1
130814 7:39:33 innodb:starting shutdown ...
130814 7:39:37 Innodb:shutdown completed; Log sequence number 1606156
130814 07:39:37 innobackupex:completed ok!

4.5. Simulate database crash to achieve full recovery

(1) Simulation crash

[Root@stu18 ~]
# service mysqld Stop
[root@stu18 ~]
# cd/mydata/data/
[root@stu18 data]
# RM-RF *

(2) Recover data from a full backup (remember: Do not initialize the database and start the service until the data is recovered)
The –copy-back option for the Innobackupex command is used to perform a recovery operation by replicating all data-related files to the MySQL server DataDir directory to perform the recovery process. Innobackupex through BACKUP-MY.CNF to obtain information about the DataDir directory.

[Root@stu18 ~]
# Innobackupex--copy-back/innobackup/2013-08-14_07-04-49/
############ #如果执行正确, the final output of a few lines of information is usually as follows ############### #
innobackupex:starting to copy InnoDB log files
innobackupex:in '/innobackup/2013-08-14_07-04-49 '
Innobackupex:back to original InnoDB log directory '/mydata/data '
innobackupex:copying '/innobackup/2013-08-14_ 07-04-49/ib_logfile0 ' to '/mydata/data '
innobackupex:copying '/innobackup/2013-08-14_07-04-49/ib_logfile1 ' to ' /mydata/data '
innobackupex:finished copying back files.
130814 07:58:22 innobackupex:completed ok!

(3) When the data is restored to the data directory, it is also necessary to ensure that all data files of the owners and groups are correct users, such as MySQL, otherwise, before the start of the mysqld also need to modify the data file of the owners and groups.

# Chown-r mysql:mysql/mydata/data/

(4) Start server login to view restore complete.

[Root@stu18 Data]
# service Mysqld Start

Note: Be sure to redo a full backup once every recovery is complete!!

4.6. Use Innobackupex for incremental backup

Description: Each InnoDB page contains an LSN information, and the LSN of the associated page grows automatically whenever the relevant data changes. This is the basis for an incremental backup of the InnoDB table, which is innobackupex by backing up pages that have changed since the last full backup.
Change data for the first time to implement an incremental backup
Implementing an incremental backup can be done using the following command:

[Root@stu18 Data]
# Innobackupex--user=root--password=mypass--incremental/innobackup--incremental-basedir=/innobackup/2013-08-14_ 08-14-12/

Where/innobackup refers to the directory where the full backup resides, the Innobackupex command creates a new, time-named directory in the/backup directory to hold all incremental backup data at the end of the command execution. –incremental-basedir refers to the directory where the full backup is up once.

Second change data for incremental backups:

[Root@stu18 ~]
# Innobackupex--user=root--password=mypass--incremental/innobackup--incremental-basedir=/innobackup/2013-08-14_ 08-29-05/

The second incremental backup has the same execution command as the first time, and only its –incremental-basedir should point to the directory where the last incremental backup was located.

The third change data has not been incrementally backed up

Mysql> Delete from CoC where id=14;

4.7, using Innobackupex based on full + incremental + binary log recovery data

(1) Since the author writes binary logs and data files in the same file directory, you must first copy the binary log files before simulating the database crash. Therefore, we suggest that the audience will keep the data directory and binary directory separately, and do not make the same mistake as the author. The method is as follows:
The prerequisite is to do the following before the server has just been set up and not started;

Mkdir/mybinlog 
#建立一目录用于存放二进制日志
chown mysql:mysql/mybinlog 
#更改权限
vim/etc/my.cnf 
#修改配置文件
log-bin=/mybinlog/mysql-bin 
#二进制日志目录及文件名前缀, add the

Okay, come on. Copy binary log files:

[Root@stu18 Data]
# CP mysql-bin.000001/innobackup/

(2) Simulation server crashes

[Root@stu18 ~]
# service mysqld Stop
[root@stu18 ~]
# cd/mydata/data/
[root@stu18 data]
# RM-RF *

(3) Prepare backup

The first thing to note is that the "Prepare for" incremental backup is different from organizing a full backup, especially to note that:
1 The committed transaction needs to be "replayed" on each backup (including full and incremental backups). After replay, all the backup data is merged into a full backup.
2 "rollback" of uncommitted transactions based on all backups.

Full backup "Ready"

[Root@stu18 ~]
# Innobackupex--apply-log--redo-only/innobackup/2013-08-14_08-14-12/

First incremental backup "ready" means merging the first incremental backup into a full backup

[Root@stu18 ~]
# Innobackupex--apply-log--redo-only/innobackup/2013-08-14_08-14-12/--incremental-dir=/innobackup/2013-08-14_ 08-29-05/

Second incremental backup "prep" means merging the second incremental backup into a full backup

[Root@stu18 ~]
# Innobackupex--apply-log--redo-only/innobackup/2013-08-14_08-14-12/--incremental-dir=/innobackup/2013-08-14_ 09-08-39/

Where –redo-only is the only committed transaction synchronized to the data file, uncommitted transaction logs are not rolled back.

(4) Restore data (based on Innobackupex based on full + increment)

[Root@stu18 ~]
# Innobackupex--copy-back/innobackup/2013-08-14_08-14-12/

(5) Change of group owner

[Root@stu18 ~]
# cd/mydata/data/
[root@stu18 data]
# chown-r Mysql:mysql *

(6) Start view

[Root@stu18 ~]
# Mysql-uroot-pmypas
mysql> select * from COC;
+----+---------+----------+
| ID | ClassID | CourseID |
+----+---------+----------+
| 1|  1 |  2 |
| 2|  1 |  5 |
| 3|  2 |  2 |
| 4|  2 |  6 |
| 5|  3 |  1 |
| 6|  3 |  7 |
| 7|  4 |  5 |
| 8|  4 |  2 |
| 9|  5 |  1 |
| Ten |  5 |  9 |
| One |  6 |  3 |
| |  6 |  4 |
| |  7 |  4 |
| |  7 |  3 |
+----+---------+----------+
rows in Set (0.00 sec)

The results show that the data is correct and complete, but the third change is not in effect.

(7) Data recovery based on binary log
View the location of the last incremental backup binary log:

[Root@stu18 Data]
# cd/innobackup/2013-08-14_09-08-39/
[root@stu18 2013-08-14_09-08-39]
# cat Xtrabackup_binlog_info
MYSQL-BIN.000001 780

Viewing the binary log file exports the binary log of the data that is not backed up

[Root@stu18 Innobackup]
# Mysqlbinlog mysql-bin.000001
# at 780
#130814 9:20:19 server ID 1 end_log_pos 851 Query thread_id=7 exec_time=0 Error_code=0
SET timestamp=1376443219/*!*/;
BEGIN
/*!*/;
# at 851
#130814 9:20:19 server ID 1 end_log_pos 944 Query thread_id=7 exec_time=0 error_code=0
SET timestamp=1376 443219/*!*/;
Delete from COC where id=14
/*!*/;
# at 944
#130814 9:20:19 server ID 1 end_log_pos 1016 Query thread_id=7 exec_time=0 error_code=0
SET timestamp=1 376443219/*!*/;
COMMIT
/*!*/;
DELIMITER;
# End of log file
ROLLBACK/* Added by Mysqlbinlog * *;
/*!50003 SET completion_type= @OLD_COMPLETION_TYPE * *;
/*!50530 SET @ @SESSION. pseudo_slave_mode=0*/;
[Root@stu18 Innobackup]
# Mysqlbinlog--start-position=780 mysql-bin.000001 >/all.sql  #导出数据

Recover data

[Root@stu18 ~]
# mysql-uroot–pmypass
mysql> SET sql_log_bin=0;   
#关闭二进制日志
mysql> source/innobackup/all.sql 
#导入数据
mysql> SET sql_log_bin=1;   
#开启二进制日志
mysql> select * from COC;   
#查看数据, Restore completed
+----+---------+----------+
| ID | ClassID | CourseID |
+----+---------+----------+
| 1 |  1 |  2 |
| 2 |  1 |  5 |
| 3 |  2 |  2 |
| 4 |  2 |  6 |
| 5 |  3 |  1 |
| 6 |  3 |  7 |
| 7 |  4 |  5 |
| 8 |  4 |  2 |
| 9 |  5 |  1 |
| Ten |  5 |  9 |
| One |  6 |  3 |
| |  6 |  4 |
| |  7 |  4 |
+----+---------+----------+
rows in Set (0.00 sec)

This backup recovery method is fully backup and incremental backup and binary log restore data, and recovery is very fast, is the best way to restore backup!!

Summary: the above three kinds of backup recovery can be based on binary log files, thus reflecting the importance of binary log, so that the importance of mapping the log; So learning to view the use of log files is the most important to learn MySQL!