初识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.mysqlbinlogmysqlbinglog是一个客户端工具，它可以在其他终端以客户端的身份访问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.service2.热备（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]>setsql_log_bin=0（5）在172.16.10.40/24服务器执行sql语句恢复数据库mysql -uroot -p mysql -uroot -p （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.sql3.温备温备是针对不支持事务的MyISAM存储引擎采取的一种折中的备份方式，它通过lvm2的快照功能结合二进制日志，实现了几乎热备的效果（1）将172.16.10.30/24服务器上的表同步到磁盘并且锁表MariaDB [hellodb]> flush tables with r开发云主机域名ead 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]>setsql_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!’ （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中未找到，不确定是否有该功能

相关推荐: MYSQL中如何实现binlog优化

这篇文章主要讲解了MYSQL中如何实现binlog优化，内容清晰明了，对此有兴趣的小伙伴可以学习一下，相信大家阅读完之后会有帮助。问题问题1：如何解决事务提交时flush redo log带来的性能损失WAL是实现事务持久性(D)的一个常用技术，基本原理是将事…