数据意味着企业的命脉，如果，数据丢失，就表示企业就要面临巨大的经济损失，甚至是更加严重的后果，所以，作为运维人员，对于数据库的备份与恢复是一种非常重要的任务，下面，我将介绍三种备份方式，分别为：mysqldump备份（mysql自带的），逻辑卷备份，xtrabackup备份（专用于InnoDB存储引擎）。
  
    首先，先介绍mysqldump这个备份工具，实现的主要是物理备份，之后，还需要根据二进制日志的事件进行增量备份，这主要是针对InnoDB存储引擎的，MyISAM存储引擎，只需要将数据库目录下的数据拷贝到另一个主机即可；这种备份方式实现的是：完全备份+增量备份。
   
备份的策略：最好一周进行一次完全备份，每天进行一次增量备份

mysqldump的备份及数据恢复实验：

我们先单独创建一个目录，作为存放二进制日志文件的位置，最后是另一个磁盘上


进行备份：此时，MySQL应该为启动状态

[root@localhost ~]# mysqldump -B hellodb -lock-all-tables -master-data=2 -flush-logs -u root -h localhost -p > 
/myback/hellodb-`date +%F-%T`.sql     
#-h，这里指localhost，但是，实际应该是MySQL服务器的地址，客户端为另一个地址
Enter password:

上面的认证信息可以写到当前用户的家目录下

 

备份的数据库文件中，有如下语句

– CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000005', MASTER_LOG_POS=245;    #增量备份从这里开始


登录到数据库上，在hellodb的数据库中创建一个表

MariaDB [hellodb]> create table test(id int);

插入数据

MariaDB [hellodb]> insert into test values (1),(11);

在增量备份前，滚动一下日志文件

MariaDB [(none)]> flush logs;

生成环境中，可能会自动滚动多个文件，备份时，要备份多个文件

 

进行增量备份：

根据时间进行增量备份，起始时间位增量备份的时间

[root@localhost ~]# mysqlbinlog –start-datetime '2014-09-22 18:20:43' 
–stop-datetime '2014-09-22 18:23:23' /MySQL/binlogs/mysql-bin.* > 
/myback/icrm-`date +%F-%T`.sql

此时，再次登录数据库，进行一些操作

MariaDB [(none)]> USE hellodb
MariaDB [hellodb]> insert into test values (100),(200);

由于可能的误操作，hellodb数据库被删除了

MariaDB [hellodb]> DROP DATABASE hellodb;

此时，我们就要保护好我们的二进制日志文件了，我们将数字最大的二进制日志导出到临时文件，查找到DROP DATABASE这一栏目，记下其执行的编号

[root@localhost ~]# mysqlbinlog /MySQL/binlogs/mysql-bin.000006 > /myback/bny.sql
[root@localhost ~]# vim /myback/bny.sql
# at 444     #事件的序号为444
#140922 18:26:48 server id 1  end_log_pos 531   Query   thread_id=7     exec_time=0     error_code=0
SET TIMESTAMP=1411381608/*!*/;
drop database hellodb
/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

重新生成二进制日志的文件

[root@localhost ~]# mysqlbinlog –stop-position 444 /MySQL/binlogs/mysql-bin.000006 > /myback/bny.sql

可将原来生成的进行覆盖

 

连接到MariaDB上，将二进制日志记录的功能暂时关闭

MariaDB [(none)]> set session sql_log_bin=0;

还原数据库，依次导入，这里可以在另一个终端上导入

导入第一次完全备份的数据

[root@localhost ~]# mysql < /myback/hellodb-2014-09-18-00\:15\:22.sql

此时，后面的数据都没有，test表都不存在

 

导入第一次增量备份

[root@localhost myback]# mysql < icrm-2014-09-22-18\:24\:39.sql

导入二进制日志的操作部分

[root@localhost myback]# mysql < bny.sql

 

逻辑备份对于数据集非常大的时候，效率非常的差，可以执行物理备份，直接拷贝数据

使用rsync，锁定mysql，将数据拉到远程的客户端主机上

  
下面要介绍的是基于LVM逻辑卷的一种物理备份，前提是，我们的MariaDB服务的数据库目录是基于LVM的逻辑卷挂载的目录，我们在做逻辑卷的时候，要预留一部分空间，要进行物理备份的时候，将所有的表施加读锁，不让用户进行写操作，同时，马上创建快照，进行挂载，及时的释放读锁，这样，物理备份就完成了，如果，数据还在不断的写入，其余的要借助于我们的二进制日志进行恢复数据了。

我们先在mysql中导入hellodb这个数据库

[root@localhost ~]# mysql < hellodb.sql

再使用mysqldump工具进行完全备份

 [root@localhost ~]# mysqldump -A –lock-all-tables > backup.sql

停止我们的mysql服务

[root@localhost ~]# service mysqld stop

首先，我们创建一个分区，并将其作为逻辑卷

[root@localhost ~]# fdisk /dev/sda     #创建10G分区
[root@localhost ~]# kpartx -af /dev/sda
[root@localhost ~]# partx -a /dev/sda
 
[root@localhost ~]# pvcreate /dev/sda3 
[root@localhost ~]# vgcreate myvg /dev/sda3 
[root@localhost ~]# lvcreate -L 6G -n mydata myvg
 
[root@localhost ~]# mke2fs -t ext4 /dev/myvg/mydata
[root@localhost ~]# mount /dev/myvg/mydata /MySQL/data/
[root@localhost ~]# chown -R mysql:mysql /MySQL/data/

重新初始化

[root@localhost mysql]# cd /usr/local/mysql/
[root@localhost mysql]# ./scripts/mysql_install_db –user=mysql –datadir=/MySQL/data/

启动MySQL服务

[root@localhost mysql]# service mysqld start

恢复前面备份的数据

[root@localhost ~]# mysql < backup.sql

上面的操作是为了建立将数据库目录建立在逻辑卷上的数据库
 

下面就基于逻辑卷做物理备份

将所有的表锁定

MariaDB [(none)]> flush tables with read lock;     #会话不能退出

查看一下我们当前的数据所处的二进制日志位置，也可以滚动下日志

如果在脚本中查看，使用：mysql  -e  'show master status' > /backup/binary-log

另起一个终端，创建快照卷

[root@localhost data]# lvcreate -L 1G -n mydata_snap  -s -p r   /dev/myvg/mydata

此时，立刻释放前一个会话中的锁

MariaDB [(none)]> unlock tables;

将快照卷进行挂载

[root@localhost ~]# mkdir /snap
[root@localhost ~]# mount /dev/myvg/mydata_snap /snap/

此时，在/snap目录下就是备份的数据

 

将快照卷备份的数据移到另一块磁盘中去

[root@localhost ~]# cp -a /snap/ /backup/bk-`date +%F-%T`

就在此刻，有个用户登录到数据库上，操作了一下命令

MariaDB [(none)]> use hellodb
MariaDB [hellodb]> drop table toc;

toc表被删除了，在mysql的数据库目录中也没有这个表的数据了

但是，快照卷上还是有toc这个表的数据的

我们该如何将toc这个表恢复回来呢？

[root@localhost ~]# mysqlbinlog /MySQL/data/mysql-bin.000003     #查看删除toc表的序号
 [root@localhost ~]# mysqlbinlog –stop-position=245 /MySQL/data/mysql-bin.000003 > /backup/bk.sql

然后，我们实行全库恢复

[root@localhost ~]# service mysqld stop
[root@localhost ~]# rm -rf /MySQL/data/*     #删除现在的数据
[root@localhost ~]# cp -a /backup/bk-2014-09-21-20:39:55/*  /MySQL/data/ 
#将备份的数据复制过去

 

启动mysql，将二进制日志时间导入

[root@localhost ~]# service mysqld start
[root@localhost ~]# mysql < /backup/bk.sql

登录数据库，验证

下面是最后一种备份，，使用xtrabackup备份工具备份数据，这是一种对于InnoDB存储引擎来说是较优的热备工具，是由percona研发，同时，这种备份工具还支持增量备份

安装xtrabackup

[root@localhost src]# yum install percona-xtrabackup-2.2.3-4982.el6.x86_64.rpm

这样安装会自动解决依赖关系

 

查看我们的xtrabackup软件生成了哪些文件

[root@localhost src]# rpm -ql percona-xtrabackup
/usr/bin/innobackupex
/usr/bin/xbcrypt
/usr/bin/xbstream
/usr/bin/xtrabackup
/usr/share/doc/percona-xtrabackup-2.2.3
/usr/share/doc/percona-xtrabackup-2.2.3/COPYING

innobackupex：客户端工具，要以mysql协议连入mysqld，不支持离线备份

 

 实现完整备份：

innobackupex  –user=DBUSER  –password=DBUSERPASS  /path/to/BACKUP-DIR
     –user=DBUSR
     –password=DBUSREPASS

备份时，最小使用一个全新最小的用户备份，下面我们就创建这么个用户

MariaDB [(none)]> create user 'bkuser'@'localhost' identified by 'bk@123';     #创建用户
MariaDB [(none)]> grant reload,lock tables,replication client on *.* to 'bkuser'@'localhost';     #赋予指定权限
MariaDB [(none)]> flush privileges;

进行备份：

[root@localhost ~]# innobackupex –user=bkuser –password=bk@123 /backup

然后，备份到一半时，就开始报错了

InnoDB: Error: log file ./ib_logfile0 is of different size 5242880 bytes
InnoDB: than specified in the .cnf file 50331648 bytes!
innobackupex: Error: The xtrabackup child process has died at /usr/bin/innobackupex line 2672.

解决方法，修改mysql的配置文件：innodb_log_file_size=5M     #大小改为5M

 

然后，备份就ok了，只要出现下面的提示就可以了

140921 22:53:05  innobackupex: Connection to database server closed
140921 22:53:05  innobackupex: completed OK!

 

在/backup目录下，会创建一个以日期命令的目录

备份的配置文件是不完整的，MySQL的配置文件还是要手动备份

 

备份的同时，在备份目录中创建如下文件

xtrabackup_checkpoints     –> 备份类型、备份状态、LSN(日志序列号)范围

     LSN是增量备份的一个重要凭据

xtrabackup_binlog_info –>MySQL服务器当前正在使用的二进制日志及北方这一刻二进制日志事件的位置

[root@localhost 2014-09-22_09-56-04]# cat xtrabackup_binlog_info 
mysql-bin.000005     7925         #备份时，二进制日志事件的位置
 backup-my.cnf     #备份命令用到的配置选项信息


准备数据：通过回滚为提交的事务及同步已经提交的事务至数据文件也使得数据文件处于一致性状态

innobackupex  –apply-log

 

此时，关闭数据库，删除数据库中的所有数据，注意，我们的二进制日志是另外存放的

 

通过innobackup还原数据

[root@localhost ~]# innobackupex –copy-back /backup/2014-09-22_09-56-04/

通过–copy-back选项，注意，还原时，MySQL可以不启动

 

查看MySQL的数据库目录

 

此时，数据都被还原了，我们的二进制默认存在其他位置

 

重要的一步，恢复的数据的属主和属组是root用户，mysql是无法读取的，要改变属主信息

[root@localhost data]# chown -R mysql:mysql ./*

启动mysql服务，登录上去，查看我们的数据是否还在

 

数据都被恢复了，此时，我们应该做一次完全备份

[root@localhost ~]# innobackupex –user=root –password='' /backup/

完全备份后的xtrabackup_checkpoints中的信息如下

然后，我们连接到我们的数据库

创建一个test的新表，并插入部分数据

MariaDB [hellodb]> create table test(id int,name varchar(20));
MariaDB [hellodb]> insert into test values(1,'luffy'),(2,'wsy'),(3,'c-shang');

然后，删除students这个数据库

MariaDB [hellodb]> drop table students;

对我们的数据库，进行增量备份

[root@localhost ~]# innobackupex –incremental /backup/ –incremental-basedir=/backup/2014-09-22_10-33-00/

这个增量是针对我们恢复数据后的完全备份而进行的

会在/backup目录下生成一个新的目录，查看新的lsn序列号

查看新的增量备份的目录下的xtrabackup_checkpoints文件的lsn

 

假设，此时，我们的用户还在使用数据库，插入了数据，但是，数据库在这时崩溃了，数据全部丢失，我们如何恢复

连接数据库，在test表中插入新的数据

MariaDB [hellodb]> insert into test values (4,'abc'),(5,'xyz');

模拟数据库服务器崩溃，二进制日志是另外存放的，如果不是的，注意保持二进制日志

[root@localhost ~]# service mysqld stop
[root@localhost data]# rm -rf /MySQL/data/*

此时，我们就要查看最后一次增量备份时，二进制日志事件的位置

[root@localhost 2014-09-22_10-37-50]# cat xtrabackup_binlog_info
mysql-bin.000001     9182

我们要利用我们的二进制日志文件，导出9182事件之后的数据库操作了

[root@localhost ~]# mysqlbinlog –start-position=9182 /binlogs/mysql-bin.000001 > /backup/bk-`date +%F-%T`.sql

还原我们的所有的数据库

准备过程，只能提交

[root@localhost ~]# innobackupex –apply-log –redo-only /backup/2014-09-22_10-33-00/

合并第一个增量

[root@localhost ~]# innobackupex –apply-log –redo-only /backup/2014-09-22_10-33-00/ 
–incremental-dir=/backup/2014-09-22_10-37-50/

最后一个增量恢复，可以不用加–redo-only了

 

将恢复的数据拷贝回去

[root@localhost ~]# innobackupex –copy-back /backup/2014-09-22_10-33-00/     #完全备份的目录
[root@localhost ~]# cd /MySQL/data/
[root@localhost data]# chown -R mysql:mysql ./*

启动mysql服务

MariaDB [(none)]> SET SESSION sql_log_bin=0;     #关闭二进制日志记录
MariaDB [hellodb]> SOURCE /back/bk-2014-09-22-10:50:42.sql;
 #可以在另一个终端中导入
[root@localhost ~]# mysql < /backup/bk-2014-09-22-10:50:42.sql     
MariaDB [hellodb]> SET SESSION sql_log_bin=1;

此时，查看我们的数据库

至此，我们关于MariaDB的三种备份方式及其恢复方法都已经介绍了，写下此篇博文，其实，也是为了强调数据的一个重要性，对于数据的备份与恢复要做到非常熟练。