MySQL备份与恢复的三种方法

古城微笑少年丶 2022-07-14 02:21 220阅读 0赞

1.利用mysqldump实现从逻辑角度完全备份mysql，配合二进制日志备份实现增量备份

2.利用lvs快照从物理角度实现几乎热备的完全备份，配合二进制日志备份实现增量备份

3.利用percona公司的xrabackup实现完全热备份与增量热备份

实验环境：RHEL5.8 ,SElinux关闭，MySQL是tar包初始化安装版本5.5.28

一.测试环境准备

1.1 mysql的安装就不说了，见[http://laoguang.blog.51cto.com/6013350/1039208][http_laoguang.blog.51cto.com_6013350_1039208]

1.2 编缉/etc/my.cnf把二进制日志存放目录改到其它非数据目录,innodb每表一文件

建立一目录用于存放二进制日志

1.  mkdir /mybinlog  
2.  chown mysql:mysql /mybinlog

修改my.cnf

1.  vim /etc/my.cnf
2.  log-bin=/mybinlog/mysql-bin    \#\#二进制日志目录及文件名前缀
3.  innodb\_file\_per\_table = 1      \#\#启用InnoDB表每表一文件，默认所有库使用一个表空间

启动mysqld

1.  service mysqld start

1.3 创建一个测试库与测试表

1.  mysql> create database laoguang;
2.  mysql> use laoguang;
3.  mysql> create table linux  (id tinyint auto\_increment primary key,name char(10));
4.  mysql> insert into linux (name) values ('apache'),('nginx'),('php');

1.4 创建用于存放备份的目录

1.  mkdir /myback
2.  chown -R mysql:mysql /myback

二,用mysqldump实现备份

2.1 mysqldump用来温备，所以我们得为所有库加读锁,并且滚动一下二进制日志，并记录当前二进制文件位置

1.  mysqldump --all-databases --lock-all-tables  --routines --triggers \--master-data=2 \\
2.  \--flush-logs > /myback/2012-12-3.19-23.full.sql
3.    
    
4.  \--all-databases 备份所有库
5.  \--lock-all-tables 为所有表加读锁
6.  \--routines 存储过程与函数
7.  \--triggers 触发器
8.  \--master-data=2 在备份文件中记录当前二进制日志的位置，并且为注释的，1是不注释掉在主从复制中才有意义
9.  \--flush-logs 日志滚动一次

查看有没有备份成功，有没有启用新二进制的日志，查看备份的文件中有没有记录完整备份后二进制的位置

备份二进制日志

1.  cp /mybinlog/mysql-bin.000001 /myback/2012-12-3.19-23.full.00001

2.2 模拟数据库意外损坏，测试完整恢复

1.  rm -rf /data/mydata/\*  
2.  rm -rf
3.  /mybinlog/\*

初始化mysql并启动mysql

1.  cd /usr/local/mysql
2.  ./scripts/mysql\_install\_db \--user=mysql\--datadir=/data/mydata
3.  rm -rf /mybinlog/\* \#\#因为我们不是全新初始化的，可能会有报错的二进制日志，我们不需要
4.  service mysqld start  \#\#启动时会重新生成新的二进制日志的

恢复到备份状态,备份前先关闭对恢复过程的二进制日志记录，因为记录恢复语句是毫无意义的

1.  mysql> set global sql\_log\_bin=0;
2.  mysql < /myback/2012-12-3.19-23.full.sql \#\#如果有账号密码记的-u -h哦
3.  打开记录并查看恢复状况
4.  mysql> set global sql\_log\_bin=1;
5.  mysql> show databases;

打开二进制记录并查看恢复状况

1.  mysql> set global sql\_log\_bin=1;
2.  mysql> show databases;

2.3 模拟一种场景，我往linux表中新添加了数据，然后不小心将这个表删了，我们要恢复到删除之前的状态，并且新加的数据还存在。

2.3.1 新增数据

1.  mysql> use laoguang;
2.  mysql> insert into linux  (name) values ('haddop'), ('mysql');
3.  mysql> drop table linux;
4.  mysql> show master status;   \#\#查看当前所在二进制日志中的位置
5.  \+------------------+----------+--------------+------------------+
6.  | File             | Position | Binlog\_Do\_DB | Binlog\_Ignore\_DB |
7.  \+------------------+----------+--------------+------------------+
8.  | mysql-bin.000001 |     9005 |              |                  |  
9.  \+------------------+----------+--------------+------------------+

我们先恢复完整数据，再恢复完整备份后到删除之前的数据，对应二进制日志就是完整备份后的二进制日志位置到删除表之前的位置

2.3.2 先恢复完整备份，同样恢复过程不要记录日志

1.  mysql > set global sql\_log\_bin=0;
2.  mysql < /myback/2012-12-3.19-23.full.sql

2.3.3 查看删除表时的记录位置

1.  mysqlbinlog /mybinlog/mysql-bin.000001  
2.  \# at 8893
3.  \#121202 14:14:07 server id 1  end\_log\_pos 9005  Query  thread\_id=5exec\_time=0error\_code=0
4.  SET TIMESTAMP=1354428847/\*!\*/;
5.  DROP TABLE \`linux\` /\* generated by server \*/
6.  /\*!\*/;
7.  DELIMITER ;
8.  \# End of log file

2.3.4 由上图可知删除是在8893时做的，将二进制文件中完整备份到删除表之前的记录导出

1.  mysqlbinlog \--stop-position=8893 /mybinlog/mysql-bin.000001  > /tmp/change.sql
2.  \--start-position 指定从哪开始导出二进制日志
3.  \--stop-position 指定到哪结束
4.  \--start-datetime 从哪个时间开始格式如"2005-12-25 11:25:56"
5.  \--stop-datetime 到哪个时间结束

由于这个二进制日志是我们完整恢复后才启用的，所以我们直接从头开始即可，如果你的二进制日志很多，请查看完整备份中记录的备份时的位置，从那开始到删除之前即可

将这段二进制记录应用到mysql的库中

1.  mysql < /tmp/change.sql

进入数据库查看数据有没有恢复

1.  mysql> select \* from linux;

基于mysqldump通常我们就是完整备份+二进制日志来进行恢复的。

三，利用lvm的快照来备份MySQL

要求你的MySQL的数据目录必须在lvm卷上，下面来演示过程

3.1 建立lvm卷组，挂载到/data/mydata下，这个我就不演示了

3.2 初始化MySQL时将数据目录指向/data/mydata,安装过程见上链接

3.3 同样如第一步那样准备环境

3.4 在MySQL中为所有表加读锁，不要关闭终端，否则锁将失效,滚动日志

1.  mysql> flush tables with read lock;
2.  mysql> flush logs;
3.  mysql> show master status;
4.  \+------------------+----------+--------------+------------------+
5.  | File             | Position | Binlog\_Do\_DB | Binlog\_Ignore\_DB |
6.  \+------------------+----------+--------------+------------------+
7.  | mysql-bin.000002 |      107 |              |                  |  
8.  \+------------------+----------+--------------+------------------+

3.5 另开一终端速度建立快照,我的那个卷组是/dev/myvg/mydata

1.  lvcreate -L 200M -n mysql-snap -s -p r /dev/myvg/mydata

3.4 速度释放读锁

1.  mysql> unlock  tables;

3.5 挂载快照，拷备出来，卸载快照，删除快照

1.  mount /dev/myvg/mysql-snap /mnt
2.  mkdir /myback/lvm
3.  cp -pR /mnt/\* /myback/lvm  
4.  umount /mnt
5.  lvremove /dev/myvg/mysql-snap

3.6 就这样一次完整备份就完成了，下面来测试能否正常使用

1.  servivce mysqld stop
2.  rm -R /data/mydata/\*  
3.  cp -Rp /myback/lvm/\* /data/mydata
4.  service mysqld start  \#\#如果能正常启动代表没有问题，起不来请看数据目录权限

3.7 如果在完整备份后MySQL出现故障，与mysqldump一样，先恢复上次的完整备份，再利用二进制日志恢复，二进制恢复再啰嗦一遍，找到完整备份时的二进制位置，把从那时到故障前的日志用mysqlbinlog导出来，然后批处理方式导入到MySQL中。这个同mysqldump中实验一致就不重复了。

用lvm的快照来备份速度是非常快的，而且几乎热备，恢复也很快速，操作也简单，完整恢复后再将相应二进制恢复即可。

四:基于xtrabackup来完全备份，增量备份，热备份MySQL

下载地址:[http://www.percona.com/software/percona-xtrabackup][http_www.percona.com_software_percona-xtrabackup]

4.1 下载安装xtrabackup，我用的是percona-xtrabackup-2.0.3-470.rhel5.i386.rpm

1.  yum install perl-DBD-MySQL
2.  rpm -ivh percona-xtrabackup-2.0.3-470.rhel5.i386.rpm

4.2 MySQL基本环境与第一步的一致

4.3 为备份建立一个只有备份权限的用户

1.  mysql> create user 'percona'@'localhost' identified by 'redhat';
2.  mysql> revoke all privileges,grant option from 'percona'@'localhost';
3.  mysql> grant reload,lock tables,replication client on \*.\* to 'percona'@'localhost';
4.  mysql> flush privileges;

4.4 完整备份一次MySQL

1.  innobackupex \--host=locahost \--user=percona \--password=redhat --defaults-file=/usr/local/mysql/my.cnf  /myback/

数据会完整备份到/myback/中目录名字为当前的日期，extrabackup会备份所有的InnoDB表，MyISAM表只是复制表结构文件、以及MyISAM、MERGE、CSV和ARCHIVE表的相关文件，同时还会备份触发器和数据库配置信息相关的文件。除了保存数据外还生成了一些extrabackup需要的数据文件

1)xtrabackup\_checkpoints 备份类型（如完全或增量）、备份状态（如是否已经为prepared状态）和LSN(日志序列号)范围信息；每个InnoDB页(通常为16k大小)都会包含一个日志序列号，即LSN。LSN是整个数据库系统的系统版本号，每个页面相关的LSN能够表明此页面最近是如何发生改变的。

2)xtrabackup\_binlog\_info mysql服务器当前正在使用的二进制日志文件及至备份这一刻为止二进制日志事件的位置。

3)xtrabackup\_binlog\_pos\_innodb  二进制日志文件及用于InnoDB或XtraDB表的二进制日志文件的当前position。

4)xtrabackup\_binary  备份中用到的xtrabackup的可执行文件；

5)backup-my.cnf 备份命令用到的配置选项信息；

4.4 测试恢复MySQL,用extrabackup来完整恢复

1.  service mysqld stop
2.  rm -Rf /data/mydata
3.  innobackupex --apply-log /myback/2012-12-02\_20-06-12/
4.  \--apply-log 的意义在于把备份时没commit的事务撤销，已经commit的但还在事务日志中的应用到数据库
5.  innobackupex --copy-back /myback/2012-12-02\_20-06-12/
6.  \--copy-back数据库恢复，后面跟上备份目录的位置
7.  chown -R mysql:mysql /data/mydata  
8.  service mysqld start \#\#如果能启动代表恢复正常

4.5 我们来实验一下增量备份

4.5.1 在表中新增一些数据

1.  mysql> insert into linux (name) values ('tomcat'), ('memcache'), ('varnish');

4.5.2 增量备份

1.  innobackupex \--user=percona\--password=redhat --incremental \\
2.  \--incremental-basedir=/myback/2012-12-02\_20-06-12/ /myback/
3.    
    
4.  \--incremental 指定是增量备份
5.  \--incremental-basedir 指定基于哪个备份做增量备份，最后是增量备份保存的目录

增量备份只能对InnoDB引擎做增量备份，对MyISAM的表是完全复制

4.6 测试增量备份恢复

1.  service mysqld stop
2.  rm -Rf /data/mydata/\*
3.  innobackupex --apply-log --redo-only /myback/2012-12-02\_20-06-12/
4.    
    
5.  \--redo-only 指的是把备份时commit的但还在事务日志中的应用到时数据，但是还没提交的不撤消，
6.  因为这个事务可能在增量备份中提交，假如的撤消了增量备份中就提交不，因为事务已经不完整

将增量备份全并到完整备份中去

1.  innobackupex --apply-log /myback/2012-12-02\_20-06-12/ \\
2.  \--incremental-dir=/myback/2012-12-02\_20-28-49/
3.    
    
4.  /myback/2012-12-02\_20-06-12/ 这个是完整备份的目录
5.  \--incremental-dir 后跟的是增量备份的目录
6.  这个会使增量备份中的的数据合并到完整备份中，如果还有增量备份，继续合并，恢复时恢复完整备份即可

恢复数据，并起动MySQL

1.  innobackupex --copy-back /myback/2012-12-02\_20-06-12/
2.  chown -R mysql:mysql /data/mydata
3.  service mysqld start

查看数据有没丢失

如果在增量备份后数据库出现故障，我们需要通过完整备份+到现在为止的所有增量备份+最后一次增量备份到现在的二进制日志来恢复。

到此总结完成，有些细节可能把握的不够准确，如有什么错误，欢迎留言指正，感谢！

[http_laoguang.blog.51cto.com_6013350_1039208]: http://laoguang.blog.51cto.com/6013350/1039208
[http_www.percona.com_software_percona-xtrabackup]: http://www.percona.com/software/percona-xtrabackup