MySQL之数据备份与和即时点还原

uytyrt

Raid1，Raid10：仅仅是保证硬件损坏时，业务不需要中止；而像（drop table tb_name 和 rm 这种在软件层面的误删除就不能恢复）

cp命令：cp命令需要时间进行复制，在服务器业务未中止时容易造成复制的数据在时间点上不一致。

备份类型：

• 热备份 读、写不受影响，在备份时服务器可正常提供有业务
  
• 温备份 在进行数据备份时，只能进行读操作
  
• 冷备份 在备份时必须中止业务，读、写操作都不行
  
• 物理备份 直接复制数据文件，速度快
  
• 逻辑备份 将数据导出至文本文件中，要从表中先将数据读出来，再导入到其他地方，其速度比较慢
  
• 完全备份 将整个数据进行备份
  
• 增量备份 仅备份上次完全备份后或者增量备份后变化的数据
  
• 差异备份 仅备份上次完全备份后变化的数据
  
  

备份的时候备份什么？

数据、配置文件、二进制日志、事务日志（防止有未完成的事务）

MyISam：几乎不可能热备份（要借助逻辑卷LVM使用快照，锁定MyISam中所有表，且要以共享方式（read）锁定,这样数据就更改不了了，再将数据复制一份）
Innodb：可以进行热备份，有专门的热备份工具，perconna提供的xtrabackup就能实现热备，而且属于物理备份

但是为了数据的安全，能进行离线备份（冷备份）就进行离线备份；可以借助主从架构中的从服务器实施离线备份（这样不会影响主服务器的业务，且高效安全）

到底选择物理备份还是逻辑备份？
物理备份的速度快
逻辑备份的速度慢，会丢失浮点数；但是数据的移植性强且可方便使用文本工具对备份数据直接进行处理


备份策略：
完全备份+增量备份；完全备份+差异备份

如一周做一次完全备份，一天做一次增量备份（备份频率取决于你数据的变化量，及你可以忍受的还原时长）

MySQL的备份工具

mysqldump:逻辑备份工具，对MyISam为温备份，对Innodb为热备份
mysqlhotcopy:物理备份工具，温备份


文件系统工具：

• 直接cp: 冷备份，会导致备份数据在时间点上出现不一致，但借助lv快照后可以实现几乎热备份
  
• LV:逻辑卷快照功能，几乎热备
  >flush tables;
  >lock tables;
  >创建快照
  >释放锁
  >复制数据
  
  

如果是对Innodb创建快照的话，必须等待缓存区中的数据写入到磁盘上，而这个时间可能会持续很长时间

第三方工具：
ibbackup:商业工具，每台server的授权费用很高
xtrabackup:开源的免费工具，比ibbackup性能还好

mysqldump命令及参数：

db_name 备份指定的数据库

--master-data={0|1|2}
0:不记录二进制文件及时间位置
1：以change master to的方式记录事件位置，可用于恢复后直接启动服务器
2：以change master to的方式记录事件位置，但默认被注释

--single-transaction
如果指定的备份数据库中的表存储引擎为Innodb，可以用--single-transaction启动热备份，不能与lock-all-tables一起使用（因为是热备份）；此选项开启后会启动一个很长的事务，在繁忙的server上可能会消耗很长的时间


备份多个库

--all-databases 备份所有库，在进行数据还原时不必提前创建库或者表，会自动创建

--databases db_name,db_name.....备份多个指定库，在进行数据还原时不需要先创建库，会自动创建

--events 备份时间调度器的

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

--triggers 备份触发器的

--lock-all-tables 在进行备份之前自动锁定所有表；但如果只进行备份一个表的话直接用>lock tables read 便可，否则会将所有表都锁定

--flush-logs 在备份之前自动进行日志滚动；在备份之前最好将二进制日志滚动一下

例：
mysql> select * from students.test1;
+-----+---------+-----+
| cid | name    | sid |
+-----+---------+-----+
|   1 | zxl     | A   |
|   2 | jiamian | B   |
|   3 | fade    | C   |
|   4 | faded   | D   |
+-----+---------+-----+

[iyunv@node1 ~]# mysqldump -uroot -p students  > /root/students.sql
Enter password:  对students库进行数据备份，保存文件为/root/students.sql
[iyunv@node1 ~]# vim /root/students.sql 可以查看一下

-- MySQL dump 10.13  Distrib 5.6.34, for Linux (x86_64)
--
-- Host: localhost    Database: students
-- ------------------------------------------------------
-- Server version       5.6.34-log

/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `mytest`
--

DROP TABLE IF EXISTS `mytest`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `mytest` (
  `cid` tinyint(3) unsigned NOT NULL AUTO_INCREMENT,
  `Course` varchar(50) NOT NULL,
  `starttime` date DEFAULT '2017-02-12',
  PRIMARY KEY (`cid`),
  UNIQUE KEY `course` (`Course`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
.........

[iyunv@node1 ~]# mysql
mysql> drop database students; 登录后删除students库
Query OK, 5 rows affected (1.05 sec)

mysql> \q

[iyunv@node1 ~]# mysql mydb < /root/students.sql 依靠保存的/root/students.sql文件还原数据库，且新库名为mydb
ERROR 1049 (42000): Unknown database 'mydb' 报错误，因为备份单个库|表，还原前要先创建库

[iyunv@node1 ~]# mysql  < /root/students.sql
ERROR 1046 (3D000) at line 22: No database selected

[iyunv@node1 ~]# mysql
mysql> create database mydb; 登录创建一个新库mydb
mysql> \q
[iyunv@node1 ~]# mysql mydb < /root/students.sql 数据还原
[iyunv@node1 ~]# mysql
mysql> show databases ; 还原成功
+--------------------+
| Database       |
+--------------------+
| mydb          |


mysql> select * from mydb.test1; 库中表也还原回来了
+-----+---------+-----+
| cid | name    | sid |
+-----+---------+-----+
|   1 | zxl     | A   |
|   2 | jiamian | B   |
|   3 | fade    | C   |
|   4 | faded   | D   |
+-----+---------+-----+
4 rows in set (0.00 sec)

例：将日志和事务所处的位置也保存下来，方便还原
[iyunv@node1 ~]# mysqldump -uroot -p --master-data=2  mydb > /root/students-'data+%F-%H-%S'.sql
Enter password:

[iyunv@node1 ~]# ls /root/
students-data+%F-%H-%S.sql  
[iyunv@node1 ~]# less /root/students-data+%F-%H-%S.sql
-- CHANGE MASTER TO MASTER_LOG_FILE='master-bin.000023', MASTER_LOG_POS=4270;

可以看到当前使用的日志文件为'master-bin.000023',事务位置为 MASTER_LOG_POS=4270;这样从4270这个位置进行二进制日志的备份便可，而4270之前的数据可以用备份直接进行还原；这样就做到了即时点数据还原


下面将演示完全备份+增量备份+数据即时点还原


[iyunv@node1 data]#  mysqldump -uroot -p --master-data=2 --flush-logs --all-databases  --lock-all-tables  > /root/All.sql 做完全备份
Enter password:
[iyunv@node1 data]# mysql
mysql> use mydb;

Database changed
mysql> select * from test1；
+-----+-------+-----+
| cid | name  | sid |
+-----+-------+-----+
|   1 | zxl   | A   |
|   3 | fade  | C   |
|   4 | faded | D   |
|   5 | my   | E   |
+-----+-------+-----+
4 rows in set (0.00 sec)

mysql> delete from test1 where name='my'; 第一天进行了数据删除
Query OK, 1 row affected (0.01 sec)

mysql> flush logs;
Query OK, 0 rows affected (0.04 sec)

mysql> \q

[iyunv@node1 data]# mysql -e 'show master status' 查看当前使用的二进制日志是哪个
+-------------------+----------+--------------+------------------+-------------------+
| File         | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| master-bin.000027 |    120 |          |                         |                |
+-------------------+----------+--------------+------------------+-------------------+

可以看到当前使用的日志为 master-bin.000027 ，则master-bin.000026为过去一天的增量产生的二进制日志

[iyunv@node1 data]# cp master-bin.000026 /root/monday-increment.sql 进行第一次增量备份
或者用mysqlbinlog命令复制
[iyunv@node1 data]# mysqlbinlog master-bin.000026 /root/monday-increment.sql
[iyunv@node1 ~]# mysql
mysql> use mydb;
Database changed
mysql> insert into test1 (cid,name,sid) values (6,'jiamian','F'); 第二天进行了数据插入
Query OK, 1 row affected (0.06 sec)

mysql> \q

[iyunv@node1 data]# cp master-bin.000027 /root/tuesday-increment.sql 进行第二次增量备份

[iyunv@node1 ~]# cd /mydata/data/
[iyunv@node1 data]# rm -rf ./* 删除所有数据，模拟数据崩溃
[iyunv@node1 data]# service mysqld stop
ERROR! MySQL server PID file could not be found!
[iyunv@node1 data]# killall mysqld

[iyunv@node1 data]# cd /usr/local/mysql/
[iyunv@node1 mysql]# scripts/mysql_install_db --usr=mysql --datadir=/mydata/data/ 先初始化
[iyunv@node1 data]# service mysqld start 在启动mysqld
Starting MySQL SUCCESS!
进行数据恢复
[iyunv@node1 data]# mysql -uroot -p < /root/All.sql 导入完全备份
[iyunv@node1 data]# mysql -uroot -p < /root/monday-increment.sql 导入第一次增量备份
[iyunv@node1 data]# mysql -uroot -p < /root/tuesday-increment.sql 导入第二次增量备份


也可以将上面过程写成一个脚本，让其在半夜自动执行