一、实验环境
一台测试机:A(172.18.30.1)
操作系统:Centos7
操作对象数据库版本:mariadb-10.2
二、实现目的
从A机器(172.18.30.1)简单搭建数据库,创建测试数据,使用逻辑卷快照实现数据的备份,执行误操作,通过备份实现数据的还原
三、简单实验步骤
创建卷组和逻辑卷
挂载逻辑卷到指定位置
配置mysql的rpm源
A机器yum安装maraidb
配置mysql配置文件
创建测试数据
锁定数据库写操作
执行LVM的快照操作,对数据库状态进行快照
记录当前binlog的position
解锁数据库写操作
挂载快照,将快照中的数据拷贝到备份目录
删除快照
登录数据库执行误操作
使用LVM快照进行恢复
通过BINlog删除执行的误操作指令,并还原数据库
四、实验步骤
1、创建卷组和两个逻辑卷,并将逻辑卷分别挂载在存放数据库的目录与存放binlog的目录
使用A机器现有的磁盘sdb创建逻辑卷(在实际的生产线上建议使用不同的vg来创建lv)
创建逻辑卷
pvcreate /dev/sdb
vgcreate sql_data_dump /dev/sdb
lvcreate -n sql_data -L 5G sql_data_dump #存放数据库的lv
lvcreate -n sql_binlog -L 5G sql_data_dump #存放binlog的lv
为逻辑卷创建文件系统
mkfs.xfs /dev/sql_data_dump/sql_data
mkfs.xfs /dev/sql_data_dump/sql_binlog
现将两个lv分别挂载到不同的目录
mount /dev/sql_data_dump/sql_data /data
mount /dev/sql_data_dump/sql_binlog /binlog
修改目录权限
chown mysql.mysql /data /binlog
2、配置mysql的rpm源
vim /etc/yum.repos.d/mariadb.repo
[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/10.2/rhel7-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1
注意:rpm安装的mariadb需要个epel源端的包,epel源可以在http://mirrors.aliyun.com/repo/下载
2、安装mariadb
yum install -y mariadb-server
3、修改maraidb的配置文件
vim /etc/my.cnf.d/server.cnf
在[mysqld]下添加如下项目
innodb_file_per_table #每个标的库数据存放在单独的位置
log_bin=/binlog/mariadb-bin #保存的二进制文件头
datadir = /data
4、启动数据库
因为我们修改了默认的数据库位置,所以需要先初始化数据库
mysql_install_db --user=mysql
启动数据库
systemctl start mariadb
查看数据库和binlog文件是否正常
[root@localhost ~]# ll /data/
总用量 122912
-rw-rw---- 1mysql mysql 163842月 2510:53aria_log.00000001
-rw-rw---- 1mysql mysql 522月 2510:53aria_log_control
-rw-rw---- 1mysql mysql 27952月 2510:53ib_buffer_pool
-rw-rw---- 1mysql mysql 125829122月 2510:54ibdata1
-rw-rw---- 1mysql mysql 503316482月 2510:54ib_logfile0
-rw-rw---- 1mysql mysql 503316482月 2510:53ib_logfile1
-rw-rw---- 1mysql mysql 125829122月 2510:54ibtmp1
-rw-rw---- 1mysql mysql 62月 2510:54localhost.pid
-rw-rw---- 1mysql mysql 02月 2510:54multi-master.info
drwx------ 2mysql root 40962月 2510:53mysql
drwx------ 2mysql mysql 202月 2510:53performance_schema
drwx------ 2mysql root 62月 2510:53test
[root@localhost ~]# ll /binlog/
总用量 40
-rw-rw---- 1mysql mysql 290172月 2510:53mariadb-bin.000001
-rw-rw---- 1mysql mysql 3442月 2510:54mariadb-bin.000002
-rw-rw---- 1mysql mysql 542月 2510:54mariadb-bin.index
5、创建数据
[root@localhost ~]# mysql
MariaDB [(none)]> use test
Database changed
MariaDB [test]> show tables;
Empty set(0.00sec)
#test库是空的,我们创建一个新表
MariaDB [test]> CREATE TABLE IF NOT EXISTS `test`(
-> `runoob_id` INT UNSIGNED AUTO_INCREMENT,
-> `runoob_title` VARCHAR(100)NOT NULL,
-> `runoob_author` VARCHAR(40)NOT NULL,
-> `submission_date` DATE,PRIMARY KEY(`runoob_id` )
-> )ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0rows affected(0.01sec)
插入3条数据
MariaDB [test]> insert into test(runoob_title,runoob_author,submission_date)values('test1','stuff',now());
Query OK, 1row affected, 1warning(0.01sec)
MariaDB [test]> insert into test(runoob_title,runoob_author,submission_date)values('test2','stuff',now());
Query OK, 1row affected, 1warning(0.01sec)
MariaDB [test]> insert into test(runoob_title,runoob_author,submission_date)values('test3','Goodluck',now());
Query OK, 1row affected, 1warning(0.01sec)
查看我们创建的数据
MariaDB [test]> select * from test;
+-----------+--------------+---------------+-----------------+
| runoob_id | runoob_title | runoob_author | submission_date |
+-----------+--------------+---------------+-----------------+
| 1| test1 | stuff | 2018-02-25|
| 2| test2 | stuff | 2018-02-25|
| 3| test3 | Goodluck | 2018-02-25|
+-----------+--------------+---------------+-----------------+
3rows in set(0.00sec)
MariaDB [test]>
6、创建数据库的快照实现数据库的备份
首先创建备份目录
mkdir /backup
对数据添加写锁
[root@localhost ~]# mysql -e 'FLUSH TABLES WITH READ LOCK;'
记录binlog当前的position并记录到文件
[root@localhost ~]# mysql -e 'show binary logs' | tail -n 1 > /backup/binlog_position
创建数据库快照
[root@localhost ~]# lvcreate -n sql_data_snapshot -L 1G -s -p r /dev/sql_data_dump/sql_data
生成新的binlog文件
[root@localhost ~]# mysql -e 'flush logs;'
解除数据库锁定
[root@localhost ~]# mysql -e 'unlock tables;'
7、挂载快照备份数据
[root@localhost ~]# mkdir /snapshot_tmp
[root@localhost ~]# mount -o nouuid,norecovery /dev/sql_data_dump/sql_data_snapshot /snapshot_tmp/
[root@localhost ~]# /usr/bin/cp -ra /snapshot_tmp /backup/mariadb_backup.`date +%F`
[root@localhost ~]# umount /snapshot_tmp/
由于我们在生产中可能会有多个全库的备份,所以我们将”/backup/binlog_position”文件移动到对应的数据库备份目录内
mv /backup/binlog_position /backup/mariadb_backup.`date +%F`
8 、删除快照
注意:为什么要删除快照,因为如果有快照的存在,当数据库在有写操作时,实际上是写了双份数据,在实际的逻辑卷上写了一份,同时在快照空间中也写了一份,IO的性能会受到影响,所以在做完备份之后首先要做的便是删除快照
[root@localhost sql_data_dump]# lvremove -y /dev/sql_data_dump/sql_data_snapshot
9、我们再次为数据库添加一条数据,并做删表操作
[root@localhost ~]# mysql
MariaDB [test]> use test;
MariaDB [test]> insert into test(runoob_title,runoob_author,submission_date)values('Thanks a lot','sun',now());
MariaDB [test]> drop tables test;
10、数据库的恢复
注意:LVM恢复数据库的方案需要停止数据库的运行,还好恢复速度相对会比较快
[root@localhost /]# systemctl stop mariadb
[root@localhost /]# rm -rf /data/*
[root@localhost /]# /usr/bin/cp -ra /backup/mariadb_backup.2018-02-25/* /data/
使用binlog恢复误操作之前的数据
[root@localhost mariadb_backup.2018-02-25]# cd /binlog
[root@localhost binlog]# cat /backup/mariadb_backup.2018-02-25/binlog_position
mariadb-bin.0000021522
[root@localhost binlog]# mysqlbinlog --start-position=1522 mariadb-bin.000002 > /tmp/binlog.`date +%F`
[root@localhost binlog]# mysqlbinlog mariadb-bin.000003 >> /tmp/binlog.`date +%F`
[root@localhost binlog]# mysqlbinlog mariadb-bin.000004 >> /tmp/binlog.`date +%F`
删除binlog生成的sql文件中的误操作
[root@localhost binlog]# vim /tmp/binlog.2018-02-25
删除误操作指令
11、隔离mysql环境,通过一定手段禁止其他用户执行写操作
方法:
1)、开启mysql的read_only功能,禁止非管理员用户修改数据(不推荐,有可能会产生问题,因为在启动数据库之后,设置这条属性之前有可能已经插入了数据)
mysql -e 'set global read_only =1'
2)、设置防火墙策略,禁用mysql数据库连接端口,使mysql与外界连接隔离
做好隔离后启动数据库
[root@localhost binlog]# systemctl start mysql
将sql导入到库中
[root@localhost binlog]# mysql -e 'set global sql_log_bin=0' #关闭binlog的记录
[root@localhost binlog]# mysql < /tmp/binlog.2018-02-25
[root@localhost binlog]# mysql -e 'set global sql_log_bin=1' #开启binlog的记录
五、LVM实现数据恢复的重要事项
备份数据库时必须要锁表
恢复数据库时需要停止mysql的运行
备份完整的数据库成功后需删除快照
使用binlog恢复数据要注意其他用户的权限问题,如果在恢复数据的过程中有其他用户插入数据会造成数据不一致(mysql环境隔离)
使用binlog恢复数据时,必须关闭binlog的记录,待恢复完成后开启
六、备份脚本