CentOS7_mysql数据库备份

简介

关于备份

备份技术：

• 物理备份/冷备
  直接复制数据文件，适用于大型数据库环境，不受存储引擎的限制，但不能恢复到不同的mysql服务器
  tar,cp,scp
  拷贝数据，优点快，缺点需要服务停止
• 逻辑备份/热备
  备份的是建表、建库、插入等操作所执行的SQL语句，适用于中小型数据库环境
  mysqldump，mydumper
  效率相对较低

percona-xtrabackup

percona简介

• 它是开源免费的支持MySQL数据库热备份的软件，它能对innoDB和XtraDB存储引擎的数据库非阻塞的备份
  它不暂停服务创建innoDB热备份；
  为mysql做增量备份；
  在mysql服务器之间做在线表迁移；
  使创建replication更加容易；
  备份mysql而不增加服务器的负载；
  percona是一家老牌的mysql技术资讯公司。它不仅提供mysql的技术支持、培训、咨询，还发布了mysql的分支版本–percona server。并围绕percona server还发布了一系列的mysql工具
• 获得软件包
  官方站点
  https://www.percona.com/
  选择版本

percona安装

一、安装percona需要的mysql包
1.安装mysql官方源

[root@pg0 ~]# yum install -y https://repo.mysql.com/mysql80-community-release-el7-3.noarch.rpm

2.安装yum工具

[root@pg0 ~]# yum install -y yum-utils

3.在yum仓库中禁用8.0版本开启5.7版本

4.安装共享兼容性库

[root@pg0 ~]# yum install -y mysql-community-libs-compat

5.安装percona官方yum库

[root@pg0 ~]# yum -y install https://repo.percona.com/yum/percona-release-latest.noarch.rpm

6.yum 安装percona-xtrabackup

[root@pg0 ~]# yum -y install percona-xtrabackup-24.x86_64

1-1完全备份流程

完整备份：

	这是大多数人常用的方式，它可以备份整个数据库，包含用户表、系统表、索引、视图和存储过程等所有数据库对象。
但它需要花费更多的时间和空间，所以，一般推荐一周做一次完全备份。

innobackupex --user=root --password=‘root密码’ 需要创建备份的目录

[root@pg0 ~]# innobackupex --user=root --password='123' /databak

此条命令执行完成之后会在/databak/文件夹下生成一个以当前系统时间命名的文件夹，文件家中xtrabackup_binlog_info里存储这二进制日志文件

1-2完全恢复流程

1.停止数据库

[root@pg0 ~]# systemctl stop mysqld

2.清理mysql数据，模拟数据库损坏
mysql数据库实体默认存放在/var/lib/mysql/下，只需要删除此文件夹下所有数据就可以清空数据库

[root@pg0 ~]# rm -rf /var/lib/mysql/*
[root@pg0 ~]# ls /var/lib/mysql/

3.启动数据库查询状态

[root@pg0 ~]# systemctl start mysqld
[root@pg0 ~]# mysql -uroot -p'123456'
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

以上显示信息表示数据库已经损坏
4.生成回滚日志
innobackupex --apply-log 备份目录的路径

[root@pg0 ~]# innobackupex --apply-log /databak/2021-05-16_13-00-27/
...
...
210516 13:39:26 completed OK!

显示OK表示成功
5.恢复文件
innobackup --copy-back 备份目录的路径

[root@pg0 ~]# innobackup --copy-back /databak/2021-05-16_13-00-27/
...
...
Original data directory /var/lib/mysql is not empty!

如果显示如上错误，表示mysql没有清理感觉，/var/lib/mysql/目录下不能有文件，所以必须再次将其清理（注意，清理时要停止数据库）

再次执行以上命令时显示OK表示成功
6.更改目录访问权限
由于恢复的文件默认的属主属组是root，mysql将无法访问，所以需要将/var/lib/mysql/目录下的所有文件的属主和属组改为mysql

[root@pg0 ~]# chown mysql.mysql /var/lib/mysql/ -R

7.开启mysql，登录测试

[root@pg0 ~]# systemctl start mysqld
[root@pg0 ~]# mysql -uroot -p'123456'
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.34-log MySQL Community Server (GPL)

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> 

2-1增量备份流程

增量备份：

它只复制上次备份以来改变了的所有文件。
增量备份是针对于上一次备份（无论是哪种备份）：备份上一次备份后，所有发生变化的文件。

1.准备工作

mysql>create database testdb;		//创建测试数据库
mysql>use test;		//进入数据库
mysql>create table test(id int);	//创建测试数据表
mysql>insert into test values (1);	//在表中插入一行数据

2.完整备份
第一天开始使用完整备份，完整备份请参照上方所诉
查看备份文件

3.模拟第二天
使用date命令将时间修改为17日（具体以自己系统为准）
时间来到了第二天
进行增量备份

[root@pg0 ~]# mysql -u root -p'123456' -e 'insert into testdb.test values(2);'	//插入新数据
[root@pg0 ~]# innobackupex --user=root --password='123456' \
--incremental /databak/ \	//第二天备份的路径
--incremental-basedir /databak/2021-05-16_15-39-49			//基于昨天的备份的路径

4.模拟第三天
使用date命令将时间修改为18日（具体以自己系统为准）
时间来到了第三天
进行增量备份

[root@pg0 ~]# mysql -u root -p'123456' -e 'insert into testdb.test values(3);'	//插入新数据
[root@pg0 ~]# innobackupex --user=root --password='123456' \
--incremental /databak/ \	//第三天备份的路径
--incremental-basedir /databak/2021-05-17_00-04-09		//基于昨天的备份的路径

2-2增量恢复流程

1.停止数据库
2.清理环境
来到第四天，突然有个小比崽子删库跑路了，需要进行恢复
3.将第一天的备份制作日志回滚

[root@pg0 ~]# innobackupex --apply-log --redo-only /databak/2021-05-16_15-39-49		//形成日志回滚，这里需要填写第一天备份的路径

4.将第二天的备份制作日志合并

[root@pg0 ~]# innobackupex --apply-log --redo-only /databak/2021-05-16_15-39-49 \		//第一天的备份路径
--incremental-dir=/databak/2021-05-17_00-04-09	//第二天的备份路径

5.将第二天的备份制作日志合并

[root@pg0 ~]# innobackupex --apply-log --redo-only /databak/2021-05-16_15-39-49 \		//第一天的备份路径
--incremental-dir=/databak/2021-05-18_00-01-29	//第三天的备份路径

6.开始恢复数据

[root@pg0 ~]# innobackupex --copy-back /databak//databak/2021-05-16_15-39-49		//第一天的备份路径

7.给数据文件更改属主属组
8.登录验证数据库

[root@pg0 ~]# mysql -uroot -p'123456' -e 'show databases;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database           |
+--------------------+
| information_schema |
| hhh                |
| mysql              |
| performance_schema |
| sys                |
| testdb             |
+--------------------+

3-1差异备份流程

差异备份：

备份自上一次完全备份之后有变化的数据。差异备份过程中，只备份有标记的那些选中的文件和文件夹。
它不清除标记，也即备份后不标记为已备份文件。
换言之，不清除存档属性。

1.第一天使用完整备份（略）
2.第二天进行增量备份
3.第三天代码与第二天代码完全一样，同样的–innocrement-basedir值也为前一次完整备份的路径

3-2差异恢复流程

[root@pg0 ~]# innobackupex --appli-log --redo-only /databak/第前一次完整备份的路径
[root@pg0 ~]# innobackupex --apply-log --redo-only /databak/第前一天完整备份的路径 \
--incremental-dir=/databak/这里如果想恢复第几天之前的数据就填第几天差异备份的路径

mysqldump + binlog

概述

在日常MySQL数据库运维过程中，可能会遇到用户误删除数据，常见的误删除数据操作有：

• 用户执行delete，因为条件不对，删除了不应该删除的数据(DML操作)；
• 用户执行update，因为条件不对，更新数据出错（DML操作）；
• 用户误删除表drop table（DDL操作）；
• 用户误清空表truncate(DDL操作)；
• 用户删除数据库drop database，跑路（DDL操作） …等

恢复原理

如果要将数据库恢复到故障点之前，那么需要有数据库全备和全备之后产生的所有二进制日志。

全备作用 ：使用全备将数据库恢复到上一次完整备份的位置；

二进制日志作用：利用全备的备份集将数据库恢复到上一次完整备份的位置之后，需要对上一次全备之后数据库产生的所有动作进行重做，而重做的过程就是解析二进制日志文件为SQL语句，然后放到数据库里面再次执行。

语法

mysqldump -h 服务器 -u用户名 -p密码 数据库名 > 备份文件

备份实战

1.准备两套密码

• 密码1：Xyz@123456（配置到当前数据库中）
• 密码2：Zyx@123456（备用）

2.准备库

2.执行备份

[root@pg0 ~]# mysqldump -p'Xyz@123456' \
> --all-databases \			//所有的库
> --single-transaction \	//保持数据的可用性
> --master-data=2 \							//注释掉日志记录
> --flush-logs \							//将日志裁断
> >/databak/`date +%F-%H`-mysql-all.sql		//重定向到指定文件

命令结束后会在/databak/文件夹下生成一个以时间格式开头以-mysql-all.sql结尾的备份文件，如图：

3.观察备份文件细节

[root@pg0 ~]# vim /databak/2021-05-18-01-mysql-all.sql

第22行：
CHANGE MASTER TO MASTER_LOG_FILE=‘localhost-bin.000004’,MASTER_LOG_POS=154
4.业务正常推进
模拟用户在数据库中创库，删库等行为

mysql>create database testdb1;
mysql>create table testdb1.test (id int);
mysql>drop database testdb1;

恢复实战

[root@pg0 ~]# cp /var/lib/mysql/*bin* .		//备份二进制日志文件
[root@pg0 ~]# systemctl stop mysqld			//停止数据库
[root@pg0 ~]# rm -rf /var/lib/mysql/*		//模拟数据库损坏
[root@pg0 ~]# systemctl start mysqld		//启动数据库
[root@pg0 ~]# grep 'password' /var/log/mysqld.log 	//查询初始密码
[root@pg0 ~]# mysqladmin -uroot -p'Swvwprim-840' password 'Zyx@123456'	//更改我们准备的第二个密码

登录数据库发现之前创建的testdb数据库不存在了，是一个全新的数据库
1.开始恢复数据

[root@pg0 ~]# mysql -p'Zyx@123456' < /databak/2021-05-18-01-mysql-all.sql 
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@pg0 ~]# mysql -uroot -p'Zyx@123456'	//登录数据库
mysql>flush privileges;		//刷新数据库
mysql>quit					//退出数据库

2.登录数据库
再次使用’Zyx@123456’密码显示错误，因为数据库已经恢复了

需要使用密码1才能再次登录，登录之后发现数据库数据都回来了

3.二进制日志文件恢复
我们对数据进行了备份但是，备份完之后用户又进行了一系列操作，后面的一系列从操作是不会被备份的，但是操作都记录在了二进制日志文件当中，所以，当数据库崩溃了我们却没有备份，可以通过二进制文件恢复，由于之前已经恢复了pg0-bin.00003之前的数据了，所以下面将恢复00003之后的数据

[root@pg0 ~]# mysqlbinlog pg0-bin.000002 --start-position=154 | mysql -p'Xyz@123456'
mysql: [Warning] Using a password on the command line interface can be insecure.

当我们使用此命令恢复数据的同时，此操作也会记录在二进制日志文件当中，所以，我们可以在恢复数据之前输入以下命令临时关闭二进制日志文件

mysql> set sql_log_bin =0;		//关闭二进制日志文件
mysql> source /databak/2021-05-18-01-mysql-all.sql
mysql> set sql_log_bin =1;		//开启二进制日志文件

执行之后就不会再被服务器纪录一遍

记录的导入和导出

记录导出

1.创建导出的目录，并给mysql权限

[root@pg0 ~]# mkdir /backup
[root@pg0 ~]# chown mysql.mysql /backup/ -R

更改配置文件/etc/my.cnf
在配置文件[mysqld]下方添加secure-file-priv=/backup,使mysql信任该目录
重启mysqld服务
2.开始导出

mysql>select * from testdb1.t1 into outfile '/backup/textdb1.t1.txt' 

记录导入

同样需要给放置待导入文件目录设置权限
并且在配置文件中信任该目录
重启mysql
模拟数据库丢失

mysql>delete from testdb.t1;

开始导入

mysql> load data infile '/backup/textdb1.t1.txt' into table testdb1.t1;

注意：表的导出和导入只会备份表数据，不会备份表结构，文本文件中的数据列数要和待导入数据库中的字段数一样（使用此语法可以添加列数：alter table add 列名 约束）