mysql数据库的快照 mysql快照恢复数据

转载

lingyuli 2023-08-04 19:24:00

文章标签 mysql数据库的快照 mysql sql 数据库 文章分类 MySQL 数据库

一、实验环境

一台测试机：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源

1. vim /etc/yum.repos.d/mariadb.repo
2. [mariadb]
3. name = MariaDB
4. baseurl = http://yum.mariadb.org/10.2/rhel7-amd64
5. gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
6. gpgcheck=1

注意：rpm安装的mariadb需要个epel源端的包

2、安装mariadb

yum install -y mariadb-server

3、修改maraidb的配置文件

vim /etc/my.cnf.d/server.cnf
在[mysqld]下添加如下项目

1. 
2. innodb_file_per_table #每个标的库数据存放在单独的位置
3. log_bin=/binlog/mariadb-bin #保存的二进制文件头
4. datadir = /data

4、启动数据库

因为我们修改了默认的数据库位置，所以需要先初始化数据库
mysql_install_db --user=mysql
启动数据库
systemctl start mariadb

查看数据库和binlog文件是否正常

1. [root@localhost ~]# ll /data/
2. 总用量 122912
3. -rw-rw---- 1163842月 2510:53.00000001
4. -rw-rw---- 1522月 2510:53 aria_log_control
5. -rw-rw---- 127952月 2510:53 ib_buffer_pool
6. -rw-rw---- 1125829122月 2510:54 ibdata1
7. -rw-rw---- 1503316482月 2510:54 ib_logfile0
8. -rw-rw---- 1503316482月 2510:53 ib_logfile1
9. -rw-rw---- 1125829122月 2510:54 ibtmp1
10. -rw-rw---- 162月 2510:54 localhost.pid
11. -rw-rw---- 102月 2510:54 multi-master.info
12. drwx------ 240962月 2510:53 mysql
13. drwx------ 2202月 2510:53 performance_schema
14. drwx------ 262月 2510:53 test
15. [root@localhost ~]# ll /binlog/
16. 总用量 40
17. -rw-rw---- 1290172月 2510:53.000001
18. -rw-rw---- 13442月 2510:54.000002
19. -rw-rw---- 1542月 2510:54 mariadb-bin.index

5、创建数据

1. [root@localhost ~]# mysql
2. MariaDB [(none)]> use test
3. Database changed
4. MariaDB [test]> show tables;
5. Empty set(0.00)
6. 
7. #test库是空的，我们创建一个新表
8. MariaDB [test]> CREATE TABLE IF NOT EXISTS `test`(
9.  -> `runoob_id` INT UNSIGNED AUTO_INCREMENT,
10. VARCHAR(100) NOT NULL,
11. VARCHAR(40) NOT NULL,
12. KEY()
13. )ENGINE=InnoDB DEFAULT CHARSET=utf8;
14. Query OK, 0affected(0.01)
15. 
16. 
17. 插入3条数据
18. 
19. MariaDB [test]> insert into test(runoob_title,runoob_author,submission_date)values('test1','stuff',now());
20. Query OK, 11warning(0.01)
21. 
22. MariaDB [test]> insert into test(runoob_title,runoob_author,submission_date)values('test2','stuff',now());
23. Query OK, 11warning(0.01)
24. 
25. MariaDB [test]> insert into test(runoob_title,runoob_author,submission_date)values('test3','Goodluck',now());
26. Query OK, 11warning(0.01)
查看我们创建的数据
1. MariaDB [test]> select * from test;
2. +-----------+--------------+---------------+-----------------+
3. | runoob_id | runoob_title | runoob_author | submission_date |
4. +-----------+--------------+---------------+-----------------+
5. | 12018-02-25 |
6. | 22018-02-25 |
7. | 32018-02-25 |
8. +-----------+--------------+---------------+-----------------+
9. 3set(0.00)
10. 
11. MariaDB [test]>
6、创建数据库的快照实现数据库的备份
1. 首先创建备份目录
2. mkdir /backup
3. 
4. 对数据添加写锁
5. [root@localhost ~]# mysql -e 'FLUSH TABLES WITH READ LOCK;'
6. 
7. 记录binlog当前的position并记录到文件
8. [root@localhost ~]# mysql -e 'show binary logs' | tail -n 1 > /backup/binlog_position
9. 创建数据库快照
10. [root@localhost ~]# lvcreate -n sql_data_snapshot -L 1G -s -p r /dev/sql_data_dump/sql_data
11. 
12. 生成新的binlog文件
13. [root@localhost ~]# mysql -e 'flush logs;'
14. 
15. 解除数据库锁定
16. [root@localhost ~]# mysql -e 'unlock tables;'

7、挂载快照备份数据

1. [root@localhost ~]# mkdir /snapshot_tmp
2. [root@localhost ~]# mount -o nouuid,norecovery /dev/sql_data_dump/sql_data_snapshot /snapshot_tmp/ 
3. [root@localhost ~]# /usr/bin/cp -ra /snapshot_tmp /backup/mariadb_backup.`date +%F`
4. [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、我们再次为数据库添加一条数据,并做删表操作

1. [root@localhost ~]# mysql
2. MariaDB [test]> use test;
3. MariaDB [test]> insert into test(runoob_title,runoob_author,submission_date)values('Thanks a lot','sun',now());
4. MariaDB [test]> drop tables test;

10、数据库的恢复

注意：LVM恢复数据库的方案需要停止数据库的运行，还好恢复速度相对会比较快

1. [root@localhost /]# systemctl stop mariadb
2. [root@localhost /]# rm -rf /data/*
3. [root@localhost /]# /usr/bin/cp -ra /backup/mariadb_backup.2018-02-25/* /data/

使用binlog恢复误操作之前的数据

1. [root@localhost mariadb_backup.2018-02-25]# cd /binlog
2. [root@localhost binlog]# cat /backup/mariadb_backup.2018-02-25/binlog_position
3. mariadb-bin.0000021522
4. [root@localhost binlog]# mysqlbinlog --start-position=1522 mariadb-bin.000002 > /tmp/binlog.`date +%F`
5. [root@localhost binlog]# mysqlbinlog mariadb-bin.000003 >> /tmp/binlog.`date +%F`
6. [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与外界连接隔离

做好隔离后启动数据库

1. [root@localhost binlog]# systemctl start mysql

将sql导入到库中

1. [root@localhost binlog]# mysql -e 'set global sql_log_bin=0' #关闭binlog的记录
2. [root@localhost binlog]# mysql < /tmp/binlog.2018-02-25 
3. [root@localhost binlog]# mysql -e 'set global sql_log_bin=1' #开启binlog的记录