Using LVM snapshots for database backup

With mysqldump backups, if the storage engine is MyISAM, only warm backups are implemented and all tables are locked with the option--lock-all-tables. If the storage engine is InnoDB, add the--single-transaction option to enable hot provisioning.
With mysqldump for logical backups, the following problems exist:
Loss accuracy of floating-point data;

The backed up data is more space-intensive, but can be compressed to greatly save space

Not suitable for full backups of large databases (such as a library exceeding 10G)

For InnoDB, it is also necessary to use mysql> flush TABLES with READ lock, which may take a significant amount of time to refresh and lock the table

For InnoDB, even if it is locked, it does not necessarily mean that there is no data written, and that the transaction log may still be synchronizing to persistent storage

I. Backup of single-table data another means:
1.1 basic syntax:
Backup:
SELECT * into OUTFILE '/path/to/somefile.txt ' from tb_name [WHERE clause];
Restore:
LOAD DATA INFILE '/path/to/somefile.txt ' into TABLE tb_name;
1.2 Example:
Backup table:
mysql> SELECT * to OUTFILE '/tmp/tutors.txt ' from tutors; 
Query OK, 8 rows affected (0.31 SEC)
[[email protected] ~]# cat/tmp/tutors.txt 
2 Huangyaoshi M 63 
3 Miejueshitai F 72 < Br>4 Ouyangfeng m 76 
6 Yucanghai m 56 
7 Jinlunfawang m 67 
8 Huyidao m 42 
9 Ningzhongze F 49 
Hufei M to
Note: Only the data is exported, and the table structure is not exported.
Create an empty table for recovering data:
mysql> CREATE table test_tb like tutors; 
Query OK, 0 rows affected (1.28 sec)
To restore data to a new table:
mysql> LOAD DATA INFILE '/tmp/tutors.txt ' into TABLE test_tb; 
Query OK, 8 rows affected (0.17 sec)  records:8 deleted:0 skipped:0 warnings:0

2.2.2 Save binary log files and related location information through another terminal;
$ mysql-uroot-p-E ' SHOW MASTER status\g ' >/path/to/master-' Date +%f '. Info

To view the current log:
Mysql> Show master status;
+----------------------+----------+--------------+------------------+-------------------+
| File | Position | binlog_do_db | binlog_ignore_db | Executed_gtid_set |
+----------------------+----------+--------------+------------------+-------------------+
| mysqld-binlog.000007 | 650 | | | |
+----------------------+----------+--------------+------------------+-------------------+
1 row in Set (0.00 sec)
Backup Delta logs: It is important to note that since I used the [FLUSH LOGS] scrolling log, it is theoretically necessary to back up two log incremental data after using the LVM snapshot volume backup, namely: mysqld-binlog.000006 and mysqld-binlog.000007
To view the log location at the beginning of the backup:
[Email protected] ~]# Cat/data/backup/master-2013-09-23.info
1. Row ***************************
file:mysqld-binlog.000006
position:120
binlog_do_db:
binlog_ignore_db:
Executed_gtid_set:
Export log:
[Email protected] ~]# Mysqlbinlog--start-position=120/data/mysql/mysqld-binlog.000006 >/data/backup/06.sql
[Email protected] ~]# mysqlbinlog/data/mysql/mysqld-binlog.000007 >/data/backup/07.sql

for more details, please read on to the next page. Highlights : http://www.linuxidc.com/Linux/2013-10/91062p2.htm

Related reading:

Mysqldump and LVM Logical volume snapshots http://www.linuxidc.com/Linux/2013-09/90382.htm

Comprehensive introduction and implementation of LVM http://www.linuxidc.com/Linux/2013-03/81482.htm

MySQL High-performance backup solution for uninterrupted data access (LVM snapshot-mode backup) http://www.linuxidc.com/Linux/2013-07/87887.htm

Implementing ASM http://www.linuxidc.com/Linux/2012-11/73475.htm on LVM

2.3 Recovery test:
2.3.1 Analog corruption:
I'm still using the direct delete data file directory:

This has not stopped properly mysqld:
[[email protected] data]# Service mysqld stop 
error! MySQL server PID file could not being found!
[[email protected] data]# killall mysqld
2.3.2 Restore full backup: direct CP
[[email protected] data]# cp-r/data/ backup/full-bak-2013-09-23/mysql/data/ 
CP: Do you want to overwrite "/data/mysql/localhost.localdomain.err"? Yes
[[email protected] mysql]# chown mysql.mysql-r/data/mysql
2.3.3 Boot server:
[[email protected] data]# service mysqld start 
Starting MySQL success!
2.3.4 Import Incremental Backup:
[[email protected] ~]# mysql-uroot-p </data/backup/06.sql
[[email protected] ~]# Mysql-uroot-p </data/backup/07.sql
2.3.5 using LVM Snapshot Volume backup considerations: If you need to back up a single library, InnoDB need to be set to stand-alone table space
Set each table to use a single table space  
[[email protected] ~]# echo "innodb_file_per_table = 1" >>/ETC/MY.CNF&NBSP;
Restart effective:  
[email  protected] ~]# service mysqld restart