初识MariaDB之3——MariaDB备份与还原

一、基础知识

数据的重要性无需多说，需要强调的是备份不是目的，能恢复才是目的，以下就Mariadb的一些概念进行说明。

1.存储引擎的分类

Mariadb5.1以后的版本，默认使用支持事务的InnoDB存储引擎，之前的MyISAM存储引擎不支持事务，所谓事务型存储引擎，必须满足ACID原则：

A：atom（原子性），即一个事务中所有的操作必须全部完成执行或不执行

C：consistent（一致性），一个事务从开始到结束状态必须保持一致

I：isolation（隔离性），相同的数据不允许多个事务同时访问

D：durability （持久性），一经修改永久保存

2.事务隔离级别

InnoDB存储引擎分为4种隔离级别：

read-uncommitted：读未提交，可以读取其他会话未提交的事务，称为脏读

read-committed：读提交，可以读取其他会话已提交的事务，解决了脏读，引发不可重读

repeatable-read：可重读（默认），通过MVCC多版本机制让已发起的事务在提交前读取到的数据不发生变化，即使有其他会话已提交了事务，解决了不可重读，引发幻读

serializable：串行提交，在每个读的行上施加共享锁，在写的行上施加排他锁，解决了幻读，但是降低了并发

3.锁机制

行锁和表锁：依据粒度不同进行的划分，行锁的并发能力更强

读锁和写锁：读锁又称共享锁，施加读锁后所有事务只能进行读操作，不能再进行写操作；写锁又称排他锁，施加写锁后除当前事务外的其他事务不能进行读写操作

InnoDB存储引擎的行锁其实是加在索引上的

4.备份方式

数据库按照备份方法分为物理备份和逻辑备份，按照服务运行的状态又可以分为冷备和热备，对于不支持事物的MyISAM存储引擎，可以采用温备的方式进行（前提是必须使用lvm2或者Btree文件系统）。

物理备份：基于文件系统层的备份，优点是速度快，缺点是收到文件系统的约束

逻辑备份：备份时将数据库抽取为sql语句，优点是不受操作系统约束，可移植性强，缺点是当数据库较大时速度慢

二、使用工具

1.mysqldump

--single-transaction 只对InnoDB引擎有效，用于保护数据的一致性，备份发起前，创建一个数据快照，直到备份结束都不会读取备份开始后提交的任何事务

--quick 配合--single-transaction加速大空间表备份

--all-databases（-A） 备份所有数据库

--where（-w） 按条件筛选要备份的数据

--databases（-B） 备份指定数据库，多个数据库之间用","分隔

--master-date=VALUE VALUE值有1|2两个值，如果是1会在备份出的文件添加一个CHANGE MASTER TO语句；如果是2会在备份出的文件添加一个CHANGE MASTER TO语句并注释，备份时建议使用该选项并将VALUE设置为2，原因是该参数备份时会携带当前时刻binlog日志的名称和位置，便于后期使用binlog恢复

需要注意的是：mysqldump如果不带-A|-B选项默认不备份数据结构，在恢复时需要先创建数据库和表结构。单表恢复时只需要指定库名，不需要指定表名。

2.mysqlbinlog

mysqlbinglog是一个客户端工具，它可以在其他终端以客户端的身份访问mysql服务器

--database=DATABASE 只适用于本地日志，查看二进制日志中指定数据库的信息（默认是所有库），导出二进制日志时建议使用该选项，否则还原时遇到该服务器上没有的数据库会报错，无法正常恢复

--start-position=N 查看指定位置以后的事务

--to-last-log（-t） 在MySQL服务器中请求的二进制日志结尾处不停止，而是继续打印直到最后一个二进制日志的结尾，如果发送给同一台服务器则会无限循环。需要同时使用--read-from-remote-server选项

--result-file=FILENAME 输出为指定文件，同重定向">"

三、操作步骤

本次实验操作系统版本为CentOS7.4，数据库版本为Mariadb5.5.6，为避免干扰，关闭selinux和iptables，172.16.10.30/24为数据库服务器，172.16.10.40/24为备用服务器

1.冷备

（1）停止172.16.10.30/24的MariaDB服务

systemctl stop mariadb.service

（2）使用scp命令将172.16.10.30/24服务器上的hellodb数据库拷到172.16.10.40/24服务器datadir目录下

scp -r hellodb root@172.16.10.40:/var/lib/mysql/

（3）修改拷贝后172.16.10.40/24服务器hellodb数据库的属主和属组

chown -R mysql.mysql /var/lib/mysql/hellodb

（4）启动172.16.10.40/24的MariaDB服务

systemctl start mariadb.service

2.热备

（1）备份172.16.10.30/24上的数据库

mysqldump -uroot -ppassword1! --database hellodb --master-data=2 --single-transaction --quick > /tmp/hellodb.sql

（2）根据/tmp/hellodb.sql中记录备份时使用的binlog及其位置将后续的sql语句备份到指定文件中，如果不止一个二进制日志后续以追加方式进行

mysqlbinlog --database hellodb --start-position=245  mysql-binlog.000005 > /tmp/binlog.sql

（3）将hellodb.sql和/tmp/binlog.sql传输到172.16.10.40/24服务器

scp -r {binlog.sql,hellodb.sql} root@172.16.10.40:/tmp/

（4）导入数据库过程无需记录二进制日志，把以先暂时关闭

MariaDB [hellodb]>set sql_log_bin=0

（5）在172.16.10.40/24服务器执行sql语句恢复数据库

mysql -uroot -p < hellodb.sql

mysql -uroot -p < binlog.sql

（6）开启二进制日志

MariaDB [hellodb]>set sql_log_bin=1

由于二进制日志的重要性，生产环境中建议将数据文件和二进制文件放在不同的磁盘上，如果有专门的日志服务器也可以远程同步二进制日志中的sql语句

mysqlbinlog  --read-from-remote-server --host=172.16.10.30 --port=3306  --user=root --password=password1! --start-position=733 --to-last-log  mysql-binlog.000005 --result-file=/tmp/bin_log.sql

3.温备

温备是针对不支持事务的MyISAM存储引擎采取的一种折中的备份方式，它通过lvm2的快照功能结合二进制日志，实现了几乎热备的效果

（1）将172.16.10.30/24服务器上的表同步到磁盘并且锁表

MariaDB [hellodb]> flush tables with read lock;

（2）刷新172.16.10.30/24服务器日志

MariaDB [hellodb]> flush logs;

（3）记录172.16.10.30/24服务器上当前二进制日志文件及位置

[root@host3 ~]# mysql -uroot -ppassword1! -e 'show master status;' > /tmp/position.txt

（4）为datadir目录所在的设备创建一个快照

[root@host3 ~]# lvcreate -L 1G -s -n mylv_snap /dev/myvg/mylv

（5）解除读锁

MariaDB [hellodb]> unlock tables;

（6）挂载快照

mount /dev/myvg/mylv_snap /mnt/

（7）将备份拷贝至172.16.10.40/24服务器，并修改属主和属组

[root@host3 mysql]#scp -r /mnt/hellodb root@172.16.10.40:/data/mysql

[root@host3 mysql]#chown -R mysq.mysq /data/mysql/

（8）导入数据库过程无需记录二进制日志，把以先暂时关闭

MariaDB [hellodb]>set sql_log_bin=0

（9）将172.16.10.30/24上的二进制文件同步至172.16.10.40/24

[root@host4 mysql]# mysqlbinlog  --read-from-remote-server --host=172.16.10.30 --port=3306  --user=root --password=password1! --start-position=245 --to-last-log  mysql-binlog.000006 --result-file=/tmp/bin_log.sql

（10）执行快照之后的sql语句

[root@host4 mysql]# mysql -uroot -p'password1!' < /tmp/bin_log.sql

（11）开启二进制日志

MariaDB [hellodb]>set sql_log_bin=1

（12）删除172.16.10.30/24上的快照，至此操作完成

[root@host3 mysql]#umount /mnt/

[root@host3 ~]# lvremove mylv_snap

补充说明：

mysql5.6以后的版本中可以将远程服务器上的服务器复制到本地，其中mysqldump命令中的-raw（以binlog形式将远程服务器的日志存储到本地）和--stop-never（连接远程mysql服务器读取日志，知道服务器关闭后才会退出或被kill掉）选项在mariadb10.1中未找到，不确定是否有该功能