MySQL的备份与恢复

MySQL的备份与恢复

MySQL的备份与恢复

1.备份

按备份时服务器是否在线来划分，备份可分为：

(1).热备份：数据库在线同时服务正常运行，此时进行的备份 （非常不安全，容易造成数据不一致）

(2).温备份：数据库服务正常运行，但数据只能读不能写，此时进行的备份（较安全）

(3).冷备份：服务器关闭，此时进行的备份（最安全，但最不可行）

按备份的内容来划分：备份可分为：

(1).逻辑备份：支持跨平台，备份的是SQL语句（DDL和insert语句），以文本形式存储，较占空间，不过可使用压缩进行节约空间

(2).物理备份：直接复制数据文件进行的备份，较逻辑备份速度较快

按备份涉及的数据范围来划分，备份可分为：

(1).完全备份：备份整个数据库

(2).增量备份：数据库从上一次完全备份或者最近一次的增量备份以来改变的内容的备份

(3).差异备份：基于的是最近一次的完整数据备份，差异备份仅捕获自该次完整备份后发生更改的数据。

 

2.备份哪些东西以及备份策略：

(1).二进制日志

(2).InnoDB的日志文件

(3).mysqld的主配置文件 （例/etc/my.cnf）

(4).从服务器位置信息

(5).中继日志 （在从服务器上的）

备份策略：二进制日志要独立备份，一定不能和数据文件在同一个磁盘上，备份之前要确定备份方案，然后再进行备份。

 

3.备份工具

mysqldump实现的是逻辑备份，对于INNODB的引擎实行的是热备份，其它引擎则是温备份。

SELECT INTO OUTFILE 指定仅备份某个表的部分数据或字段

Ibbackup 是一种商业软件备份工具���实现的是物理备份，对于INNODB的引擎实行的是热备份；其它引擎则是温备份

Filesystem (copy files) 基于文件系统的备份，直接复制数据库文件，是冷备份，物理备份

Snapshot 快照备份，几乎热备份，物理备份

mysqlhotcopy 只能备份MyIsam引擎，是冷备份

 

(1).mysqldump的使用

选项：

--all-databases   备份所有数据库

--databases db_name  备份某个数据库

--lock-tables  锁定表（非常不建议使用此选项）

--lock-all-tables 锁定所有的表

--events  备份EVENT的相关信息

--no-data  只备份DDL语句、备份表结构，不备份数据（一般不使用）

--master-data=n 备份的时候同时导出二进制日志文件和位置；如果n为1，则把信息保存为CHANGE MASTER语句；如果n为2，则把信息保存为注释掉的CHANGE MASTER语句。

--routines 将存储过程和存储函数定义备份

--single-transaction   实现热备（只能热备InnoDB引擎的）

--triggers 备份触发器

举例：备份lh数据库至/backup目录下，并以日期+时间的方式命名。

# mysqldump –databases lh –lock-all-tables –flush-logs –master-data=2 > /backup/lh-`date +%F-%H-%M-%S`.sql

还原数据库时：

# mysql

mysql> SET SQL_LOG_BIN=0;

mysql > SOURCE /backup/lh-20120418-12-12-10.sql;

mysql> SET SQL_LOG_BIN=1;

说明：此时还原数据库时要关闭二进制日志。

 

(2).使用cp、tar命令进行物理备份

使用cp、tar等命令进行复制或归档数据库

# service mysqld stop

# cp –a /mydata/data/*  /backup/alldata- `date +%F-%H-%M-%S`/

# tar jcf /backup/ alldata- `date +%F-%H-%M-%S`.tar.bz2 /mydata/data/*

说明：以上两个命令的备份，必须要停止mysql服务才能备份，这里假设mysql的数据路径是:/mydata/data。

 

(3).使用xtrabackup进行备份

xtrabackup是percona组织的一款开源备份软件，可实现完全备份和增量备份，是世界上惟一一款开源的能够对innodb和xtradb引擎的数据库进行热备的工具。

完全备份：

# innobackupex --user=root --password=123456 /backup

恢复数据的时候要先准备一个完全备份才能实现恢复：

# innobackupex --apply-log  /backup/ 2012-04-18_12-12-10

# innobackupex --copy-back  /backup/2012-04-18_12-12-10

增量备份：

第一次增量备份：

# innobackupex --incremental /backup --incremental-basedir=/backup/2012-04-18_12-12-10

说明：-incremental-basedir后面跟的是完全备份的数据库的路径（要想增量备份，前提必须已经实现完全备份），并且此时在/backup目录下，生成了一个2012-04-18_13-13-10的增量备份的目录

恢复的时候也需要准备才能恢复数据，但和完全备份准备的方式不同：

# innobackupex --apply-log --redo-only /backup/2012-04-18_12-12-10

# innobackupex --apply-log --redo-only \

/backup/2012-04-18_12-12-10 --incremental-dir=/backup/2012-04-18_13-13-10

说明：此时数据恢复成功，如果有多个增量备份，则重复执行第二条语句（绿色的语句）,并且执行第二条语句的时候，第一次增量备份的先执行，依次执行后面的增量备份。