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 列名 约束)