MySQL之select和LVM快照实现数据备份

逻辑备份：

• 可能让浮点数丢失精度
• 因为逻辑备份会保存至纯文本文件中，而这些纯文本文件一般比原数据还大（32888本来只需两个字节，纯文本要四个字节保存）
• 速度慢，不适合做数据量大的备份

mysqldump和select命令都是逻辑备份工具：即将数据导出至文本文件中，再导入其他地方，其备份速度相对较慢，且可能会丢失浮点精度，但是可以方便的使用备份工具直接对其出路，可移植能力强

mysqldump对Innodb来说是热备份：

--single-transaction 自动启用一个长事务，直到你完成备份，但要借助MVCC功能，最好将隔离几倍设置为 repetable-read（可重读）

--flush-logs 进行日志刷新

--master-data=0|1|2

--events  备份事件调度器

--routines 备份处处过程和存储函数

--triggers 备份触发器

注：对innodb进行热备不能用--lock-all-tables参数

对Innodb来说即使执行了>lock tables with read lock; 因为写操作的优先级要比读操作的优先级高，当别人启动了一个很大的事务时，可能要等待很久才能加上read锁；且就算锁上，也不一定没有写入操作，以为innodb支持事务日志，后台的MySQL线程会没隔一段时间以自我调度的方式将事务日志中数据同步到MySQL数据库中（将缓存区写入磁盘），所以你要等待事务日志完成

select命令进行单表备份，且备份为纯文本格式（.txt）,而不是sql语句或者.sql文件；但select备份的数据在还原之前必须要先创建表，且还支持where 字句进行条件式还原；其特点是速度快，备份单表，节省空间

例：

mysql> select * into outfile '/tmp/a.txt' from test1 ; 进行表备份为纯文本格式

mysql> create table TEST1 like test1; 还原前先仿照原表创建一个格式一样的新表

mysql> drop table test1; 删除原表

mysql> load data infile < '/tmp/a.txt' into table TEST1;进行表数据还原

如果在进行数据恢复时，开启了二进制日志记录，会产生大量的I/O操作；所以恢复数据时一定要关闭二进制日志；相关参数为：

sql_log_bin=0|1 0表示关闭；1表示开启

例：

mysql> flush logs;

Query OK, 0 rows affected (0.15 sec)

mysql> show master status; 事件位置120

+-------------------+----------+--------------+------------------+-------------------+

| File        | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

+-------------------+----------+--------------+------------------+-------------------+

| master-bin.000014 |    120 |              |                  |                  |

+-------------------+----------+--------------+------------------+-------------------+

mysql> drop database my; 进行数据改变的操作

Query OK, 0 rows affected (0.27 sec)

mysql> show master status; 事件位置还是120

+-------------------+----------+--------------+------------------+-------------------+

| File        | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

+-------------------+----------+--------------+------------------+-------------------+

| master-bin.000014 |    120 |      |                                |

+-------------------+----------+--------------+------------------+-------------------+

1 row in set (0.00 sec)

mysql> set sql_log_bin=1; 开启二进制日志记录

Query OK, 0 rows affected (0.00 sec)

mysql> create database my; 进行数据改变的操作

Query OK, 1 row affected (0.01 sec)

mysql> show master status; 事件位置变成了208

+-------------------+----------+--------------+------------------+-------------------+

| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

+-------------------+----------+--------------+------------------+-------------------+

| master-bin.000014 |    208 |              |                  |                 

+-------------------+----------+--------------+------------------+-------

mysql> show binlog events in 'master-bin.000014'; 可以看到已近记录了位置和操作

+-------------------+-----+-------------+-----------+-------------+------------------------

| Log_name      | Pos | Event_type  | Server_id | End_log_pos | Info                                 

+-------------------+-----+-------------+-----------+-------------+------------------------

| master-bin.000014 |  4| Format_desc |      1 |    120 | Server ver: 5.6.34-lo

| master-bin.000014 | 120 | Query    |      1 |    208 | create database my                   

+-------------------+-----+-------------+-----------+-------------+------------------------

2 rows in set (0.00 sec)

LVM+快照：实现几乎热备份

前提：

数据文件必须在逻辑卷上保存

此逻辑卷所在的空间必须有足够大的空间保存快照

事务日志一定要和数据文件在同一个逻辑卷上，来保证事务日志的快照与数据文件的快照在一个时间点上，从而保证数据的ACID和时间上的一致

#mysql

>flush tables with read lock; 刷新表并加共享锁

>flush logs;

>show master status;

mysql> show master status;

+-------------------+----------+--------------+------------------+-------------------+

| File        |  Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

+-------------------+----------+--------------+------------------+-------------------+

| master-bin.000014 |    208 |        |            |                 

+-------------------+----------+--------------+------------------+-------------------+

1 row in set (0.04 sec)

一定不要退出，可以重开一个会话

#mysql -e 'show master status\G' > /root/backup-master-'data+%F'.info

将当前二进制日志的日期备份下来

创建一个50M的快照（快照大小要根据你平时的经验判断在备份时间内数据的变化大小来决定快照的大小，一般取二倍便可）

#lvcreate -L 50M -s -p -r -n mydata-snap /dev/myvg/mydata

-r  创建只读的快照

mydata-snap 为快照名称

/dev/myvg/mydata 为数据文件所在的逻辑卷

>unlock tables; 回到原来的会话释放锁

#mount  -o ro /dev/myvg/mydata-snap /mnt

以只读的方式将快照挂载到/mnt目录下

#cd /mnt/data

#cp -a ./* /root/backup-full-'date+%F'  进行数据的完全备份(-a 在cp时保存文件所有属性）

#umount /mnt 卸载快照卷

#lvremove --force /dev/myvg/mydata-snap 移除快照

#cd /root/backup-full-2017-2-15

#rm -rf master-bin.*  删除没用的二进制文件

总结：

• 打开会话，施加读锁，锁定所有表
• 通过另一个终端，保存二进制日志及相关位置
• 创建快照卷
• 释放锁
• 挂载快照卷
• 进行数据备份（cp -a）
• 卸载快照卷，并移除快照
• 增量备份二进制日志