Mysql之备份及还原相关实验

本节索引

实验一:冷备份数据库并还原

实验二:结合LVM逻辑卷实现几乎热备

实验三:数据库数据文件损坏后,利用mysqldump还原至最新状态

实验四:误删除表后,利用mysqldump还原至最新状态

实验五:Xtrabackup完全备份及还原

实验六:Xtrabackup完全,增量备份及还原

实验七:Xtrabackup单表导出和导入

 

实验准备:

CentOS虚拟机两台,一台作为数据库服务器,一台作为中间存放备份的服务器

数据库服务器:

操作系统:CentOS7.4              IP:192.168.30.10       提示符颜色:蓝色

中间服务器:

操作系统:CentOS7.5             IP:192.168.30.17         提示符颜色:黄色

两台主机安装MariaDB 5.5.56,并且开启二进制日志功能

 

实验一:冷备份数据库并还原

备份:

数据库初始状态为:

备份前

1停止mysql服务

systemctl stop mariadb.service

2打包并压缩备份文件

(注:生产环境应将备份文件与数据库服务器分开存放)

tar Jcvf /data/all.tar.xz /var/lib/mysql/

3模拟破坏数据库

rm -rf /var/lib/mysql/*

 

还原:

tar xvf /data/all.tar.xz

mv  var/lib/mysql/* /var/lib/mysql/

确认文件已生成

ls /var/lib/mysql/

2

重新启动服务:

systemctl start mariadb.service

确认数据库已恢复

备份前

 

实验二:结合LVM逻辑卷实现几乎热备

备份:

1.创建逻辑卷

新建一个磁盘分区/dev/sda6

pvcreate /dev/sda6

vgcreate vg0 /dev/sda6

lvcreate -L 4G -n lv_mysql vg0

lvcreate -L 4G -n lv_binlog vg0

mkfs.xfs lv_mysql lv_binlog

mkdir /data/{mysql,binlog}

mount /dev/vg0/lv_mysql /data/mysql/

mount /dev/vg0/lv_binlog /data/binlog/

1

2修改目录权限

chown -R mysql.mysql /data/mysql/

chown -R mysql.mysql /data/binlog/

2修改目录权限

3修改配置文件

vim /etc/my.cnf

[mysqld]

datadir=/data/mysql

log_bin=/data/binlog/mysql-bin

3

4重启mysql服务

systemctl restart mariadb

5添加读锁,防止有用户继续写入数据

mysql> flush tables with read lock;

刷新日志

mysql> flush logs;

查看当前二进制日志

mysql> show master logs;

记录最新的二进制日志为:mariadb-bin.000004

4

6创建快照

lvcreate -L 1G -n lv_mysql_snap -s -p r /dev/vg0/lv_mysql

5

7解锁数据库

Mysql> unlock tables;

8挂载逻辑卷快照

mount -o nouuid,norecovery /dev/vg0/lv_mysql_snap  /mnt

6

9将逻辑卷文件备份至backup目录

mkdir /backup

cp -a /mnt/* /backup/

7

10删除快照,否则影响用户访问数据库速度

umount /mnt

lvremove /dev/vg0/lv_mysql_snap

备份过程结束

 

Mysql >  create databases db2

Mysql >  create databases db3

 

模拟数据库损坏:

rm –rf  /data/mysql/*

 

还原:

停止mysql服务

systemctl stop mariadb

拷贝备份文件至数据库目录

cp -av /backup/*  /data/mysql/

开启mysql服务

systemctl  start mariadb

此时数据库已还原至快照时的状态

9

二进制继续修复db1,db2

mysql> flush tables with read lock;

mysqlbinlog –start-position=245 mysql-bin.000004 > /backup/bin.sql

mysqlbinlog mysql-bin.000005 >> /backup/bin.sql

确保恢复过程中,没有用户可以读取或写入

vim /etc/my.cnf

[mysqld]

skip_networking

iptables –A

重启mysql服务使生效

systemctl  restart mariadb

导入二进制文件

mysql < /backup/bin.sql

此时数据库已还原至最新状态

8

最后,恢复用户访问

vim /etc/my.cnf

[mysqld]

skip_networking              去掉

或清除防火墙

systemctl restart mariadb

 

 

实验三:数据库数据文件损坏后,利用mysqldump还原至最新状态

初始数据库为:

1初始状态

利用mysqldump生成备份文件

mysqldump -A -F –single-transaction –master-data=2>/backup/fullbak_`date +%F`.sql

ls /backup -l

(}{FM3NQF{)%U[HU75G2IAE

mysql> create database db2

mysql> create database db3

此时数据库为:

2

最新二进制日志为:mariadb-bin.000006

3

模拟日常将备份传至中间主机:192.168.30.17

4

接下来为了方便演示,我们之间在中间主机进行数据库恢复:

rm  -rf  /var/lib/mysql

 

还原:

利用mysqlbinlog命令可查看二进制日志,看到创建db2,db3发生在position245之后

mysqlbinlog mariadb-bin.000006

5

首先还是停止mysql服务

systemctl stop mariadb.service

禁止其他用户访问或修改数据库

vim /etc/my.cnf

[msyqld]

skip_networking

生成二进制备份文件

mysqlbinlog –start-position=245  mysql-bin.000012 > bin.sql

重新开启mysql服务

systemctl start mariadb.service

修复至mysqldump全备份阶段

mysql < fullbak_2018-06-14.sql

6

继续二进制修复db2,db3

mysql < bin.sql

7

此时,数据库修复完成,最后别忘了修改配置文件

vim /etc/my.cnf

[msyqld]

skip_networking   去掉此行

重启mysql服务

systemctl restart mysql

 

 

 

实验四:误删除表后,利用mysqldump还原至最新状态

前期准备:开启二进制日志功能

实验场景:模拟周日进行了全备份,周一上午10表被误删除,10点之前与之后均发生了其他操作,尝试还

原数据库中误删除的表并且操作不发生丢失

数据库初始状态

1

模拟周日进行完全备份:

2

模拟周一上午10点数据发生了修改(students表原总行数为25)

Mysql >  insert students values(26,’mysql’,’30’,’M’,2,3);

模拟周一上午10点某张表被删除

Mysql >  drop table students;

模拟周一上午10点至10点10分间又进行了其他操作

insert teachers values(5,’mht’,’46’,’m’);

 

周一上午10点10分,发现students表丢失

 

还原:

Mysql >  flush tables with read lock;

查看当前二进制日志为:mariadb-bin.000003

Mysql >  show master logs;

刷新二进制日志

Mysql >  flush logs

3

查看全备份的position,确定全备份到目前之间的所有日志文件及position

less /backup/fullbak

4

mysqlbinlog –start-position=245 /var/lib/mysql/mariadb-bin.000003 > /backup/bin.sql

mysqlbinlog /var/lib/mysql/mariadb-bin.000004 >> /backup/bin.sql

找到想要撤销的操作,删除

vim /backup/bin.sql

5

停止mysql服务

systemctl stop mariadb.service

禁止其他用户访问或修改数据库

vim /etc/my.cnf

[msyqld]

skip_networking

删除库下数据(不用紧张^^)

rm -rf /var/lib/mysql/*

重新启动mysql服务

systemctl start mariadb.service

导入周日的全备份

mysql < /backup/fullbak_2018-06-14.sql

继续导入修改过的二进制备份

mysql < /backup/bin.sql

我们看到students表还原成功,而且不论是表删除之前还是之后发生的操作也都恢复回来了

6

此时,数据库修复完成,最后别忘了修改配置文件

vim /etc/my.cnf

[msyqld]

skip_networking   去掉此行

重启mysql服务

systemctl restart mysql

 

 

实验五:Xtrabackup完全备份及还原

安装xtrabackup包(epel)

yum install percona*

数据库初始为:

1初始状态

利用innobackupex命令完全备份

Innobackupex  [ –user=root –password=!@#$% ]  /backup/

2

模拟日常将备份传至中间主机:192.168.30.17

scp -r /backup/ 192.168.30.17:/

 

还原:

接下来为了方便演示,我们之间在中间主机进行数据库恢复:

停止mysql服务

systemctl stop mariadb.service

清空数据库

rm –rf /var/lib/mysql/*

整合完全备份

innobackupex –apply-log /backup/2018-06-14_17-08-53/

3

自动复制到数据库目录,但所属主为root,需要变更

innobackupex –copy-back /backup/2018-06-14_17-08-53/

4

chown -R mysql.mysql /var/lib/mysql/

重新开启mysql服务

systemctl start mariadb.server

数据库恢复成功

5

 

 

实验六:Xtrabackup完全,增量备份及还原

初始数据库为:

1初始状态

mkdir /backup/

mkdir /backup/{inc1,inc2}

Innobackupex  [ –user=root –password=!@#$% ]  /backup/

模拟第一次修改数据库

mysql> insert students values(26,’test1′,18,’M’,1,2);

第一次增量备份

innobackupex  –incremental /backup/inc1  –incremental-basedir=

/backup/2018-06-14_17-50-43

模拟第二次修改数据库

mysql> insert students values(27,’test1′,28,’M’,1,2);

第二次增量备份

Innobackupex  –incremental/backup/inc2  –incremental-basedir=

/backup/inc1/2018-06-14_17-55-52

此时backup目录结构

2

复制到远程主机做备份:

scp -r /backup/ 192.68.30.17:/

 

还原:

接下来为了方便演示,我们之间在中间主机进行数据库恢复:

停止mysql服务

systemctl stop mariadb.service

清空数据库

rm –rf /var/lib/mysql/*

整合完全备份:

innobackupex –apply-log –redo-only /backup/2018-06-14_17-50-43/

整合第一次增量备份:

innobackupex –apply-log –redo-only /backup/2018-06-14_17-50-43/ –incremental-dir /backup/inc1/2018-06-14_17-55-52

整合第二次增量备份:

innobackupex –apply-log –redo-only /backup/2018-06-14_17-50-43/ –incremental-dir /backup/inc2/2018-06-14_17-59-00

自动复制到数据库目录,但所属主为root,需要变更

innobackupex –copy-back /backup/2018-06-14_17-50-43/

3

更改属主为mysql

chown -R mysql.mysql /var/lib/mysql/*

重新开启mysql服务

systemctl start mariadb.service

此时,我们看到第一次修改,第二次修改的数据也已还原

4

 

 

 

实验七:Xtrabackup单表导出和导入

前期准备:必须设置独立表空间

vim /etc/my.cnf

[mysqld]

innodb_file_per_table

初始表信息为:

初始

1单表备份

innobackupex –include=”hellodb.students” /backup/

2备份表结构

mysql -e ‘show create table hellodb.students’ > students.sql

删除其中的多余部分,留下下图所示内容

3

vim students.sql

3模拟破坏表

mysql -e ‘drop table hellodb.students’

4 (2)

还原:

整合备份

innobackupex –apply-log –export /backup/2018-06-14_19-03-21

导入表结构

mysql hellodb < students.sql

此时表结构已生成,但数据为空

5

删除表空间

mysql -e ‘alter table hellodb.students discard tablespace’

复制相关文件到hellodb目录

cd /backup/2018-06-14_19-37-02/hellodb/

cp students.cfg students.exp students.ibd /data/mysql/hellodb/

修改hellodb目录下文件权限

chown mysql.mysql /data/mysql/hellodb/*

mysql>alter table students import tablespace;

此时数据已恢复完成

初始

 

本文来自投稿,不代表Linux运维部落立场,如若转载,请注明出处:http://www.178linux.com/101028

发表评论

登录后才能评论

This site uses Akismet to reduce spam. Learn how your comment data is processed.

联系我们

400-080-6560

在线咨询:点击这里给我发消息

邮件:1823388528@qq.com

工作时间:周一至周五,9:30-18:30,节假日同时也值班