一、基础知识

数据的重要性无需多说,需要强调的是备份不是目的,能恢复才是目的,以下就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恢复

1.png

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

2.png

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.png

(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中未找到,不确定是否有该功能