Use LVM snapshot for Database Backup

When using mysqldump for backup, if the storage engine is MyISAM, you can only implement warm backup, and you need to use the option -- lock-all-tables to lock all tables. If the storage engine is Inno

When using mysqldump for backup, if the storage engine is MyISAM, you can only implement warm backup, and you need to use the option -- lock-all-tables to lock all tables. If the storage engine is Inno

When using mysqldump for backup, if the storage engine is MyISAM, you can only implement warm backup, and you need to use the option -- lock-all-tables to lock all tables. If the storage engine is InnoDB, The -- single-transaction option can be added to implement hot backup.
Using mysqldump for logical backup also has the following problems:
Floating point data loss precision;

The data backed up occupies more space. However, it can be compressed to greatly save space.

It is not suitable for full backup of large databases (such as a database larger than 10 Gb)

In addition, for InnoDB, it may take a lot of time to use mysql> flush tables with read lock; to refresh and LOCK the table

For InnoDB, even if it is locked, it is not necessarily considered that there is no data writing. At this time, transaction logs may still be synchronized to permanent storage.

I. Another method for backing up a single table:
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 * into outfile '/tmp/tutors.txt' FROM tutors;
Query OK, 8 rows affected (0.31 sec)
[Root @ localhost ~] # Cat/tmp/tutors.txt
2 HuangYaoshi M 63
3 Miejueshitai F 72
4 OuYangfeng M 76
6 YuCanghai M 56
7 Jinlunfawang M 67
8 HuYidao M 42
9 NingZhongze F 49
14 HuFei M 31
Note: Only data is exported, and the table structure is not exported.
Create an empty table for data recovery:
Mysql> create table test_tb LIKE tutors;
Query OK, 0 rows affected (1.28 sec)
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 store binary log files and related location information through another terminal;
$ Mysql-uroot-p-e 'show master status \ G'>/path/to/master-'date + % F'. info

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 of incremental LOGS: note that, since I use the [FLUSH LOGS] Rolling log, theoretically two LOGS of incremental data need to be backed up after the lvm snapshot volume backup, that is: mysqld-binlog.000006 and mysqld-binlog.000007
View the log location when the backup starts:
[Root @ localhost ~] # 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 logs:
[Root @ localhost ~] # Mysqlbinlog -- start-position = 120/data/mysql/mysqld-binlog.000006>/data/backup/06. SQL
[Root @ localhost ~] # Mysqlbinlog/data/mysql/mysqld-binlog.000007>/data/backup/07. SQL

For more details, please continue to read the highlights on the next page:

Related reading:

Mysqldump and LVM logical volume Snapshot

Comprehensive Introduction and implementation of LVM

MySQL high-performance backup solution for uninterrupted Data Access (LVM Snapshot Backup)

Implement ASM on LVM