MySQL管理之数据备份及恢复

MySQL管理之备份及恢复

想必各位都清楚，数据的重要性了，这里还得要再简单介绍一些备份的重要性：

备份是确保可用性的一种手段但不能确保万无一失，那么对数据可用性比较高的场景，则必须需要备份

备份是如何进行的以及备份考虑事项：

备份无非是将数据集另存为一个副本集，一般来讲，通常公司的备份和恢复前后不能超过半个或一个小时，需要非常高的恢复效率

备份数据的意义：

·灾难恢复

·需求改变

·测试

为了能够让备份正常进行又不影响线上业务，我们需要选择备份手段、备份类型、备份介质等等，考虑到不会特别影响线上业务通常都在业务量最小的时候进行的，如果是跨国性的业务的话则是另一码事儿了。所以各种因素都要考虑周全，因此备份都是自动进行的而且通常依赖于脚本方式。

但无论怎么去备份都需要事先考虑以下几个问题：

1、容忍地丢失多长时间的数据

如果能容忍几天的数据，那么备份恢复则特别容易

2.恢复必须要在多长时间完成

不同的备份手段执行的备份效率是不一样的，简单的备份工具执行速度可能慢反则复杂的备份工具可能效率要高的多，所以必须选择适合当时环境适用的备份工具

3.是否需要持续提供服务

4.需要恢复什么

整个数据库库服务器或单个库或一个或多个表，或某条语句

#以上几点事先一定要做调研不然可能无法满足恢复的需求

常用的MySQL备份工具

mysqldump     逻辑备份工具

经典的备份工具,对innodb表来讲支持热备；而对MyISAM表最多支持温备（可读但不可写），备份和恢复的速度都比较慢；

mysqldumper     mysqldump的完善版本,也是逻辑备份工具

支持并行备份，对速度有提高,对输出有了简便的机制

lvm-snapshot    lvm快照，基于快照备份，在创建快照的那一刻起，必须锁表，但速度非常快，只要能够请求到施加读锁则可以瞬间完成，接近于热备工具

备份的数据在时间上是一致的，属于物理备份

由于要施加全局读锁，所以读锁请求时间对于一个非常忙的在线事物处理系统来讲可能需要非常长的时间，但无论如何速度还是比较快的

select内置备份工具：可以将数据使用select intooutfile挑选出来之后保存至某个文件中，之后还可以使用load data命令来实现对其加载

语法如下

    select into outfile  ‘/path/to/xx.sql‘  #通过select 还可以跟where子句过滤出需要的结果并对其进行备份

    load data  infile ‘/path/to/xx.sql‘

这种备份方式不支持sql语句，备份恢复时候必须使用load data

其性能快于mysqldump 并支持备份某个条件的数据，同时备份出的文件比较小

ibbackup、xtrabackup:

·遵循gpl协定

·物理备份工具

·支持真正意义上的innodb热备，不像于mysqldump

·对Myisam只支持温备

·速度很快

mysqlhotcopy： 几乎冷备工具

·速度很慢

·不好用

从备份中恢复需要的操作步骤

（1）停止mysql服务（冷备和快照的恢复必须停止

（2）记录服务的配置和文件权限

（3）复制备份文件至数据目录

（4）按需调整配置

（5）按需改变文件权限

（6）尝试启动服务，但是要限制访问权限

对于二进制文件的恢复步骤

（1）装载逻辑备份

（2）检查或重放二进制日志

（3）检测数据还原正常完成

（4）而后对以完全权限重启服务

事实上不同的备份方式的加载过程只是上面的一部分而非是全部过程

mysqldump备份工具的使用

如果我们想备份一张表的数据到数据文件中，可以使用mysqldump、mysqldumper 等

显示年纪大于30的用户

mysql> select *from students where age>30;

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

| name  | id  | Age  | class   |

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

| bob   |    1|   40 | 1 class |

| jerry |    3 |  33 | 2 class |

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

2 rows in set (0.00sec)

使用select命令将其备份出来

目录的路径必须是当前运行mysql服务的用户有访问权限，所以目前来说除了data目录就只有tmp了

mysql> select *from students where age > 30 into outfile‘/tmp/students.sql‘;

查看配置文件，如下所示，我们已将年纪大于30的用户的信息导出出来了

[root@test ~]# cat/tmp/students.sql

bob  1    40   1 class

jerry    3    33   2 class

但是内容是纯文本信息，所以不能直接使用mysql导入进去，必须使用load data in file的方式来实现

删除表中年纪大于30的用户信息并对其进行恢复

mysql> deletefrom students where age>30;

Query OK, 2 rowsaffected (0.07 sec)

mysql> select *from students where age > 30;

Empty set (0.00sec)明确说明将文件恢复到students表中去

mysql> load data infile ‘/tmp/students.sql‘ into tablestudents;

Query OK, 2 rowsaffected (0.11 sec)

Records: 2  Deleted: 0 Skipped: 0  Warnings: 0

使用mysqldump备份mysql数据库

mysqldump特性：

·是mysql最经典最传统的备份工具之一

·可以备份整个服务器

·可以备份单个数据库或部分数据库

·可以备份单个或部分表

·可以备份表中的某些行

·可以备份存储函数、触发器

·能自动记录备份时的二进制的文件名和位置

·对于innodb能够基于单事物型的热备份

mysql参数说明：

鉴链接http://hi.baidu.com/ququ_s/item/e45e35e204193af62b09a43d

使用mysqldump同时备份多个库

如果环境是个在线的服务器，备份的同时有人在向数据库中写入数据，因此就算进行了备份，在逻辑上也是不完整的，因此只能对库加读锁进行温备，所有人只能进行读，但不能进行写操作：

步骤：

1.直接使用--lock-all-tables 进行事先锁表再进行备份

2. 手动使用连接在连接中使用FLUSH TABLES WITH READ LOCK; 将内存中的表统统同步到磁盘中，同步完成后立刻对其进行锁表。

命令虽然简单，但是对于innodb表来讲可能有些问题，因为对于innodb引擎虽然能够请求到读锁，但也不一定意味着所有请求都同步到硬盘中去，在内部很可能有它的buffer pool，所有有些数据表面看上去已经没有人在写了，但是bufferpool中还在往磁盘上同步。

所以对innodb来说以上两种方法都不靠谱，必须使用命令不断进行观察，查看其如果真实没有数据同步才可以开始数据的备份

mysql> showengine innodb  status\G;

***************************1. row ***************************

 Type: InnoDB

 Name:

Status:

=====================================

140402 19:23:05INNODB MONITOR OUTPUT

=====================================

Per second averagescalculated from the last 25 seconds

-----------------

BACKGROUND THREAD

-----------------

srv_master_threadloops: 110 1_second, 110 sleeps, 9 10_second, 22 background, 22 flush

srv_master_threadlog flush and writes: 112

----------

SEMAPHORES

----------

OS WAIT ARRAY INFO:reservation count 20, signal count 20

Mutex spin waits 4,rounds 120, OS waits 4

RW-shared spins 16,rounds 480, OS waits 16

RW-excl spins 0,rounds 0, OS waits 0

Spin rounds perwait: 30.00 mutex, 30.00 RW-shared, 0.00 RW-excl

------------

TRANSACTIONS

------------

Trx id counter 31F

Purge done fortrx‘s n:o < 31C undo n:o < 0

History list length3

LIST OFTRANSACTIONS FOR EACH SESSION:

---TRANSACTION 31E,not started

MySQL thread id 4,OS thread handle 0x7f0dcd9a6700, query id 72 localhost root

show engineinnodb  status

#查看启动的IO线程，查看read线程是否在工作

--------

FILE I/O      

--------

I/O thread 0 state:waiting for completed aio requests (insert buffer thread)

I/O thread 1 state:waiting for completed aio requests (log thread)

I/O thread 2 state:waiting for completed aio requests (read thread)

I/O thread 3 state:waiting for completed aio requests (read thread)

I/O thread 4 state:waiting for completed aio requests (read thread)

I/O thread 5 state:waiting for completed aio requests (read thread)

I/O thread 6 state:waiting for completed aio requests (write thread)

I/O thread 7 state:waiting for completed aio requests (write thread)

I/O thread 8 state:waiting for completed aio requests (write thread)

I/O thread 9 state:waiting for completed aio requests (write thread)

Pending normal aioreads: 0 [0, 0, 0, 0] , aio writes: 0 [0, 0, 0, 0] ,

#io线程是否在工作：

ibuf aio reads: 0, log i/o‘s: 0, sync i/o‘s: 0

Pending flushes(fsync) log: 0; buffer pool: 0

0 OS file reads,477 OS file writes, 73 OS fsyncs

0.00 reads/s, 0 avgbytes/read, 0.00 writes/s, 0.00 fsyncs/s

-------------------------------------

INSERT BUFFER ANDADAPTIVE HASH INDEX

-------------------------------------

Ibuf: size 1, freelist len 0, seg size 2, 0 merges

merged operations:

insert 0, delete mark 0, delete 0

discardedoperations:

insert 0, delete mark 0, delete 0

Hash table size276707, node heap has 1 buffer(s)

0.00 hashsearches/s, 0.00 non-hash searches/s

---

LOG

---

Log sequence number1613931

Log flushed upto   1613931

Last checkpointat  1613931

0 pending logwrites, 0 pending chkp writes

43 log i/o‘s done,0.00 log i/o‘s/second

----------------------

BUFFER POOL ANDMEMORY

----------------------

Total memoryallocated 137363456; in additional pool allocated 0

Dictionary memory allocated37760

Buffer poolsize   8192

Free buffers       7871                                  #要看清楚buffer给的信息

Database pages     320

Old database pages0

Modified dbpages  0

Pending reads 0

Pending writes: LRU0, flush list 0, single page 0

Pages made young 0,not young 0

0.00 youngs/s, 0.00non-youngs/s

Pages read 0,created 320, written 392

0.00 reads/s, 0.00creates/s, 0.00 writes/s

No buffer pool pagegets since the last printout

Pages read ahead0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s

LRU len: 320, unzip_LRUlen: 0

I/O sum[0]:cur[0],unzip sum[0]:cur[0]                  #查看某些线程是否在工作

--------------

ROW OPERATIONS

--------------

0 queries insideInnoDB, 0 queries in queue

1 read views openinside InnoDB

Main thread processno. 2470, id 139697271506688, state: waiting for server activity

Number of rowsinserted 9, updated 0, deleted 2, read 52

0.00 inserts/s,0.00 updates/s, 0.00 deletes/s, 0.00 reads/s

----------------------------

END OF INNODBMONITOR OUTPUT

============================

1 row in set (0.14sec)

确保无误可以手动施加读锁进行备份

备份多个库

[root@test ~]#mysqldump -uroot --databaseswpdb mydb --lock-all-tables > /tmp/testdb.sql

如果不使用--local-all-tables 则可以进入mysql会话锁表并导出：

mysql>  FLUSH TABLES WITH READ LOCK;

Query OK, 0 rowsaffected (0.00 sec)

如果提示ok则可以继续备份，备份完成后要对其解锁

mysql> unlocktables;
Query OK, 0 rows affected (0.00 sec)

以上两种加锁方式都可以实现，但是如果在数据量超大的生产环境这种方式是不推荐使用的，或者可在从库上进行锁表备份，当然这些都是后话了

一次完整的备份恢复过程

首先，我们的备份一定要有备份策略，根据环境定义最适合自己的方案

这里我们的备份策略如下：

·每周一次完整备份

·每天做一次增量备份

接下来我们完全使用mysqldump以及二进制日志文件方式来实现

完全备份则使用mysqldump

增量备份则使用二进制日志（因为我们无从得知增量的具体数据内容，所以最好的办法就是备份当前产生的二进制文件的内容）

如果确保二进制日志文件是没有问题的不用去做增量也可以，但为了保险起见要每天复制二进制日志文件或让二进制日志文件每天回滚一次，再将回滚日志备份至其他主机

例子：假如服务器在备份后某个时间段崩溃那么如何恢复：

完全备份+昨天的增量备份+今天的凌晨到目前时间服务器尚且未备份为增量的事件（二进制日志文件）

模拟操作

首先确保目前所使用的存储引擎为innodb

mysql> select @@global.default_storage_engine;

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

|@@global.default_storage_engine |

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

| InnoDB                          |

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

1 row in set (0.00sec)

可以看到，存储引擎是innodb所以可以使用热备（innodb可使用single-transaction参数进行热备）

或者先对其回滚一次二进制日志，但是我们这里没有锁表，因为是没有锁表的热备所以flush了日志意义也不大，最好将其事件记录下来：

创建备份目录，让其备份的文件按时间归档

[root@test ~]#mkdir -p /backup/$(date +%F)

使用single-transaction参数进行热备

master-data=2表示该选项将二进制日志的位置和文件名写入到输出中。该选项要求有RELOAD权限，并且必须启用二进制日志。如果该选项值等于1，位置和文件名被写入CHANGE MASTER语句形式的转储输出，如果你使用该SQL转储主服务器以设置从服务器，从服务器从主服务器二进制日志的正确位置开始。如果选项值等于2，CHANGE MASTER语句被写成SQL注释。如果value被省略，这是默认动作。

[root@test ~]#  mysqldump -uroot --single-transaction--master-data=2 --databases wpdb > /backup/$(date +%F)/backup_$(date+%F).sql

备份完成，之后数据库一直很稳定，我们又对数据库进行了其他操作：

mysql> createtable tb3(id INT);

Query OK, 0 rowsaffected (0.10 sec)

mysql>  insert into tb3 values (1),(2),(3);

Query OK, 3 rowsaffected (0.00 sec)

Records: 3  Duplicates: 0 Warnings: 0

mysql> select *from tb3;

+------+

| id   |

+------+

|    1 |

|    2 |

|    3 |

+------+

3 rows in set (0.00sec)

数据库依旧没有出现问题，到了晚上要开始做增量备份了：

增量备份要从上次完全备份之后到当前时间

因为我们不知道具体时间和事件的位置，所以最简单的方式就是查看当前哪个二进制文件和所在的位置

mysql> showmaster status;

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

| File             | Position | Binlog_Do_DB |Binlog_Ignore_DB |

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

| mysql-bin.000003 |    3383 |              |                  |

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

1 row in set (0.00sec)

如上可以看到，所使用的二进制文件是000003  而位置所在处是3383,我们记下这个数值，再去读完整备份的文件

找到关键字所在行

[root@test ~]# grep-i ‘change master to‘/backup/2014-04-02/backup_2014-04-02.sql

-- CHANGE MASTER TOMASTER_LOG_FILE=‘mysql-bin.000003‘,MASTER_LOG_POS=3104;

我们从MASTER_LOG_POS=3104这个位置一直到3383才算结束

[root@test ~]#mysqlbinlog --start-position=3104--stop-position=3383 /mydata/data/mysql-bin.000003 >/backup/2014-04-02/increment_$(date +%F).sql

增量备份完成

数据恢复

备份完成，此后我们又对数据库进行了操作：

mysql> insertinto tb3 values (4),(6);

Query OK, 2 rowsaffected (0.20 sec)

Records: 2  Duplicates: 0 Warnings: 0

mysql> insertinto students values (‘test1‘,‘4‘,‘30‘,‘2 class‘);

Query OK, 1 rowaffected (0.00 sec)

mysql> insertinto students values (‘test2‘,‘5‘,‘12‘,‘2 class‘);

Query OK, 1 rowaffected (0.05 sec)

然后将数据库删除

mysql> dropdatabase wpdb;

Query OK, 2 rowsaffected (0.20 sec)

恢复数据思路：

1.恢复完整备份,首先我们的数据库已经被删除，所以只能将其恢复到最近的完整备份的模样

2.恢复增量备份，由于恢复到之前的完整备份的环境，从上一次完整备份到当前时间的数据是不存在的，不过好在每天在做增量备份，使其增量备份恢复到最近时间的数据

3.从二进制日志文件里导入删除之前的数据，因为当前时间距离增量备份的时间前后还有时间，所以就算恢复了增量备份到当前时间还有那么几个小时的数据不存在，所以只能使用二进制日志来恢复

备份的时候一定要记下备份时起的位置号，因为二进制日志文件是唯一救命稻草

我们刚才使用showmaster status;所看到，其二进制位置为3383

[root@test ~]#mysqlbinlog --start-position=3383 /mydata/data/mysql-bin.000003

在最下面看到，在位置3999有我们刚才所误操作的语句

# at3999

#14040220:48:13 server id 1  end_log_pos 4080     Query    thread_id=4   exec_time=0   error_code=0

SETTIMESTAMP=1396442893/*!*/;

dropdatabase wpdb

/*!*/;

DELIMITER ;

# End of log file

ROLLBACK /* addedby mysqlbinlog */;

/*!50003 SETCOMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

/*!50530 SET@@SESSION.PSEUDO_SLAVE_MODE=0*/;

记下这个3999这个位置，使用position参数进行恢复二进制文件

#stop-position表示到某个位置之前

[root@test ~]#mysqlbinlog --start-position=3383 --stop-position=3999/mydata/data/mysql-bin.000003 > /backup/2014-04-02/wpdb_source_`date+%F`.sql

查看文件可以看到我们已经恢复到2465之前的位置了

[root@test ~]# tail/backup/2014-04-02/wpdb_source_`date +%F`.sql

insert intostudents values (‘test2‘,‘5‘,‘12‘,‘2 class‘)

/*!*/;

# at 3972

#140402 20:46:40server id 1  end_log_pos 3999     Xid = 313

COMMIT/*!*/;

DELIMITER ;

# End of log file

ROLLBACK /* addedby mysqlbinlog */;

/*!50003 SETCOMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

/*!50530 SET@@SESSION.PSEUDO_SLAVE_MODE=0*/;

恢复：

使其mysql离线，全局锁表或断开前端网络都可以

关闭bin_log功能，使其不记录二进制日志文件

mysql> setsql_log_bin=0;

Query OK, 0 rowsaffected (0.04 sec)

恢复完整备份

mysql> source /backup/2014-04-02/backup_2014-04-02.sql

恢复增量备份

mysql> source/backup/2014-04-02/increment_2014-04-02.sql

导入二进制日志文件（修改后）

mysql> source/backup/2014-04-02/wpdb_source_2014-04-02.sql

验证：

mysql> showdatabases;

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

| Database           |

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

|information_schema |

| mydb               |

| mysql              |

|performance_schema |

| test               |

| wpdb               |

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

6 rows in set (0.07sec)

mysql> select *from students;

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

| name  | id  | Age  | class   |

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

| bob   |    1|   40 | 1 class |

| jerry |    3 |  33 | 2 class |

| test  |    3|   30 | 2 class |

| test1 |    4 |  30 | 2 class |

| test2 |    5 |   12 | 2 class |

| tom   |    2|   20 | 1 class |

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

6 rows in set (0.00sec)

确保无误，开启binlog记录功能：

mysql> setsql_log_bin=1;

Query OK, 0 rowsaffected (0.00 sec)

通常我们在生产环境都是备份的全部数据库，不然的话从二进制导出的数据可能不仅是一个数据库，还必须手动去过滤，会耽误不少时间

[root@localhost ~]#mysqldump -uroot --single-transaction --master-data=2 --all-databases > /tmp/backup_`date+%F`.sql

而且在备份的时候还需要注意：

二进制日志文件备份不需要先导出，可以先使其回滚一次在将其备份二进制文件之后再统一导出为sql并按需恢复

总结

1.无论怎么去备份都需要事先考虑以下几个问题

·容忍地丢失多长时间的数据

·恢复必须要在多长时间完成

·是否需要持续提供服务

·需要恢复什么

2.MyISAM表是不支持热备的，Innodb是可以支持热备但需要专用的工具

3.备份的本身依赖于:

完全+增量 或完全+差异

4. 数据恢复步骤

完全+增量|差异+二进制日志

5. 一定不要将二进制日志文件与其他文件放在同一目录下以及同一磁盘上

6.要有完整的备份策略

7.从备份中恢复需要的操作：

·停止mysql服务（冷备和快照的恢复必须停止

·记录服务的配置和文件权限

·复制备份文件至数据目录

·按需调整配置

·按需改变文件权限

·尝试启动服务，但是要限制访问权限

对于二进制文件的恢复：

·装载逻辑备份

·检查或重放二进制日志

·检测数据还原正常完成

本文出自 “心情依旧” 博客，请务必保留此出处http://yijiu.blog.51cto.com/433846/1390545

MySQL管理之数据备份及恢复,古老的榕树,5-wow.com