mysql 数据库快照迁移_Mysql数据库备份—-通过LVM快照实现备份还原

一、实验环境

一台测试机：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的记录，待恢复完成后开启

六、备份脚本