本文首发在《旺旺知识库》

使用mysqldump备份时，如果存储引擎为MyISAM，则只能实现温备份，并需使用选项--lock-all-tables锁定所有表。如果存储引擎为InnoDB，则加上--single-transaction选项，可以实现热备。

使用mysqldump进行逻辑备份，还存在如下问题：

  • 浮点数据丢失精度；

  • 备份出的数据更占用空间；不过可压缩后以大大节省空间

  • 不适合对大数据库（如一个库超过10G）做完全备份

  • 另外对InnoDB而言，需要使用mysql> FLUSH TABLES WITH READ LOCK;刷新并锁定表时，可能需要花大量的时间

  • 对InnoDB而言，即使锁定了，也不一定以为着没有数据的写入，此时事务日志可能还在同步到永久存储

一、备份单表数据另一个手段：

1.1 基本语法：

备份：

SELECT * INTO OUTFILE '/path/to/somefile.txt' FROM tb_name [WHERE clause];

还原：

LOAD DATA INFILE '/path/to/somefile.txt' INTO TABLE tb_name;

1.2 示例：

备份表：

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

注意：导出的仅仅是数据，表结构并不会被导出。

创建用于恢复数据的空表：

mysql> CREATE TABLE test_tb LIKE tutors;
Query OK, 0 rows affected (1.28 sec)

还原数据到新表：

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

使用LVM快照进行数据库备份_mysqldump

2.2.2 通过另一个终端，保存二进制日志文件及相关位置信息；

$ mysql -uroot -p -e 'SHOW MASTER STATUS\G' > /path/to/master-`date +%F`.info

使用LVM快照进行数据库备份_mysqldump_02

查看当前日志：

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)

备份增量日志：需要注意的是，由于我使用了[FLUSH LOGS]滚动日志,因此理论上使用lvm快照卷备份后有两个日志的增量数据需要备份，即：mysqld-binlog.000006和mysqld-binlog.000007

查看备份开始时的日志位置：

[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:

导出日志：

[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

2.3 恢复测试：

2.3.1 模拟损坏：

我这里还是采用直接删除数据文件目录：

<a href="http://www.toxingwang.com/wp-content/uploads/2013/10/mysql4.jpg" class="cboxElement" rel="example4" 1523"="" style="text-decoration: none; color: rgb(1, 150, 227);">使用LVM快照进行数据库备份_mysql_03

此时已经无法正常停止mysqld了：

[root@localhost data]# service mysqld stop
ERROR! MySQL server PID file could not be found!

[root@localhost data]# killall mysqld

2.3.2 恢复完全备份：直接cp

[root@localhost data]# cp -R /data/backup/full-bak-2013-09-23/mysql /data/
cp：是否覆盖"/data/mysql/localhost.localdomain.err"？ yes

[root@localhost mysql]# chown mysql.mysql -R /data/mysql

2.3.3 启动服务器：

[root@localhost data]# service mysqld start
Starting MySQL SUCCESS!

2.3.4 导入增量备份：

[root@localhost ~]# mysql -uroot -p </data/backup/06.sql

[root@localhost ~]# mysql -uroot -p </data/backup/07.sql

2.3.5 使用LVM快照卷备份注意事项：如果需要备份单一库，则InnoDB需设置为独立表空间

设置每个表独立使用一个表空间
[root@localhost ~]# echo "innodb_file_per_table = 1" >>/etc/my.cnf
重启生效：
[root@localhost ~]# service mysqld restart