MySQL数据库备份与恢复_数据库备份库回放-CSDN博客

本文链接：https://blog.csdn.net/jiaoshu__/article/details/118878391

文章目录

一、mysql数据库的日志
二、数据库备份与恢复实验

一、mysql数据库的日志

mysql的日志默认保存位置为/usr/local/mysql/data

1.1mysql日志类型和作用

redo(重做日志)

达到事务一致性，每次重启之后会重做
作用：
确保日志的持久性，防止在发生故障，脏页未写入磁盘，重启数据库会进行redo log执行重做，达到事务一致性

undo（回滚日志）

作用：
保证数据的原子性，记录事务发生之前的一个版本，用于回滚，innodb事务可重复读和读取已提交，隔离级别就是通过mvcc+undo实现

errorlog 错误日志

作用：
Mysql本身启动，停止，运行期间发生的错误信息

slow query log 慢查询日志

作用：
　记录执行时间过长的sql，时间阈值（10s）可以配置，只记录执行成功
另一个作用：在于提醒优化

bin log 二进制日志

作用：用于主从复制，实现主从同步记录的内容是：数据库中执行的sql语句

relay log 中继日志

作用：用于数据库主从同步，将主库发来的bin log保存在本地，然后从库进行回放

7.general log 普通日志

作用：记录数据库的操作明细，默认关闭，开启后会降低数据库性能

1.2mysql配置文件

[mysqld]
##错误日志，用来记录当MySQL启动、停止或运行时发生的错误信息，默认已开启
log-error=/usr/local/mysql/data/mysql_error.log     #指定日志的保存位置和文件名

##通用查询日志，用来记录MySQL的所有连接和语句，默认是关闭的
general_log=ON
general_log_file=/usr/local/mysql/data/mysql_general.log

##二进制日志(binlog)，用来记录所有更新了数据或者已经潜在更新了数据的语句，记录了数据的更改，可用于数据恢复，默认已开启
log-bin=mysql-bin      
或
log_bin=mysql-bin

##中继日志
一般情况下它在Mysql主从同步（复制）、读写分离集群的从节点开启。主节点一般不需要这个日志
##慢查询日志，用来记录所有执行时间超过long_query_time秒的语句，可以找到哪些查询语句执行时间长，以便提醒优化，默认是关闭的
s1ow_query_log=ON
slow_query_log_file=/usr/local/mysql/data/mysql_slow_query.log
long_query_time=5        #设置超过5秒执行的语句被记录，缺省时为10秒

在这里插入图片描述

[root@localhost ~]# systemctl restart mysqld //重启

1.3 查询日志

查看通用查询日志是否开启

mysql> show variables like 'general%';

在这里插入图片描述

查看二进制日志是否开启

mysql> show variables like 'log_bin%';

在这里插入图片描述

查看慢查询日志功能是否开启

mysql> show variables like 'slow%';

在这里插入图片描述

查看慢查询时间设置

mysql> show variables like 'long_query_time%';

在这里插入图片描述

在数据库中设置开启慢查询的方法

mysql> set global slow_query_log=ON;

注意：variables表示like表示模糊查询

二进制日志开启后，重启mysql会在目录中查看到二进制日志

[root@localhost bin]# cd /usr/local/mysql/data
[root@localhost data]# ls
[root@localhost data]# cat mysql-bin.index

在这里插入图片描述

①重启mysql的时候会更新索引文件，用于记录新的更新语句
②索引二进制日志

mysql-bin.index  //二进制日志文件的索引

在这里插入图片描述

二、数据库备份与恢复实验

实验环境

use school;
create table if not exists info (
id int(4) not null auto_increment,
name varchar(10) not null,
age char(10) not null,
hobby varchar(50),
primary key (id));  //创建表info

insert into info values(1,'user1',20,'running');
insert into info values(2,'user2',30,'singing');

在这里插入图片描述

2.1物理冷备份与恢复

压缩备份

[root@localhost data]# systemctl stop mysqld //关闭数据库
[root@localhost data]# yum -y install xz  //安装以xz的格式
[root@localhost data]# tar Jcvf /opt/mysql_all_$(date +%F).tar.gz /usr/local/mysql/data/  //压缩备份
mv /usr/local/mysql/data/ /opt/

在这里插入图片描述

[root@localhost opt]# systemctl start mysqld //开启数据库
[root@localhost mysql]# mysql -uroot -p  //登录数据库
mysql> show databases;
mysql> drop database school;
mysql> show databases;

在这里插入图片描述

[root@localhost opt]# cd /usr/local/mysql/data/
[root@localhost data]# ls
[root@localhost data]# cd ..
[root@localhost mysql]# ls

在这里插入图片描述

解压恢复

[root@localhost mysql]# tar Jxvf /opt/mysql_all_2021-07-19.tar.xz -C /usr/local/mysql/data/  //解压恢复
cd /usr/local/mysql/data
mv /usr/local/mysql/data/* ./

在这里插入图片描述

2.2mysqldump备份（温备份）

（1）完全备份一个或多个完整的库 (包括其中所有的表)

在命令行对表的内容进行操作

[root@localhost data]# mysql -u root -pabc123 -e 'show databases;'

在这里插入图片描述

mysqldump -u root -p[密码] --databases 库名1 [库名2] ... > /备份路径/备份文件名.sql    #导出的就是数据库脚本文件

备份一个库

[root@localhost data]# mysqldump -u root -pabc123 --databases school > /opt/school.sql

备份mysql与 kgc两个库

[root@localhost data]# mysqldump -u root -pabc123 --databases mysql  school > /opt/mysql-school.sql

在这里插入图片描述
（2）完全备份MySQL服务器中的所有库
格式

mysqldump -u root -p[密码] --all-databases > /备份路径/备份文件名.sql

 mysqldump -u root -pabc123 --all-databases > /opt/all.sql

在这里插入图片描述
（3）完全备份指定库中的部分表
格式

mysqldump -u root -p[密码] 库名 [表名1] [表名2] ... > /备份路径/备份文件名.sql

[root@localhost opt]# mysqldump -u root -pabc123 school info > /opt/info.sql

在这里插入图片描述

[root@localhost opt]# mysqldump -u root -pabc123 [-d] school info > /opt/info.sql
 //使用“-d”选项，说明只保存数据库的表结构
 //不使用“-d”选项，说明表数据也进行备份
 //作为一个表结构模版

（4）查看备份文件

[root@localhost opt]# grep -v "^--" /opt/school.sql | grep -v "^/" | grep -v "^$"

在这里插入图片描述

2.3恢复数据库

使用dump导出的文件，可使用导入的方法，source命令和mysql命令
使用source恢复数据库的基本步骤
登录到MySQL数据库，执行source备份SQL脚本的路径

mysql> show databases; //查看数据库信息
mysql> drop database school;删除数据库school
mysql> show databases;

在这里插入图片描述

mysql> source /opt/school.sql; //恢复数据库信息

在这里插入图片描述
3.使用musql命令

无须登录mysql数据库
使用-e删除数据表
执行mysql备份SQL脚本的路径
配置方法一
创建备份（对表进行备份）

[root@localhost opt]# mysqldump -uroot -pabc123 school info >/opt/info.sql //对iNfo表备份
[root@localhost opt]# mysql -uroot -pabc123  //登录数据库查看

恢复数据表


mysql> source /opt/info.sql   
mysql> select * from info;  查询所有字段
mysql> show tables; 查看表信息

在这里插入图片描述
配置方法二

[root@localhost opt]# mysqldump -uroot -pabc123 school info >/opt/school.info.sql
[root@localhost opt]# mysql -uroot -pabc123 -e 'drop table school.info;'  
[root@localhost opt]# mysql -uroot -pabc123 -e 'show tables from school;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------------+
| Tables_in_school |
+------------------+
| info             |
+------------------+

mysql -uroot -pabc123 -e 'show tables from school;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------------+
| Tables_in_school |
+------------------+
| info             |
+------------------+

在这里插入图片描述

2.4 mysqldump归纳

① 当备份时加 --databases ,表示针对于school库

#备份命令
mysqldump -uroot -p123123 --databases school > /opt/school_01.sql 备份库后
#恢复命令过程为：
mysql -uroot -p123123 
drop database school;
exit
mysql -uroot -p123123 < /opt/ky11_01.sql

② 当备份时不加 --databases，表示针对school库下的所有表

#备份命令
mysqldump -uroot -p123123 school > /opt/ky11_all.sql
#恢复过程：
mysql -uroot -p123123
drop database school;
create database school;
exit
mysql -uroot -p123123 school < /opt/school_02.sql

四、mysql增量备份与恢复

1.一般恢复

将所有备份的二进制日志内容全部恢复

2.基于位置恢复

数据库在某一时间点可能既有错误的操作也有正确的操作
可以基于精准的位置跳过错误的操作
发生错误节点之前的一个节点，上一次正确操作的位置点停止

3.基于时间点恢复

跳过某个发生错误的时间点实现数据恢复
在错误时间点停止，在下一个正确时间点开始

五、MySQL增量备份实验

5.1开启二进制日志功能


vim /etc/my.cnf
[mysqld]
log-bin=mysql-bin
binlog_format = MIXED      #可选，指定二进制日志(binlog)的记录格式为MIXED（混合输入）
server-id = 1              #可加可不加该命令

#二进制日志(binlog)有3种不同的记录格式: STATEMENT (基于SQL语句)、ROW(基于行)、MIXED(混合模式)，默认格式是STATEMENT

① STATEMENT（基于SQL语句）:
每一条涉及到被修改的sql 都会记录在binlog中
缺点：日志量过大，如sleep()函数，last_insert_id()>，以及user-defined fuctions（udf）、主从复制等架构记录日志时会出现问题

② ROW(基于行)
只记录变动的记录，不记录sql的上下文环境
缺点:如果遇到update......set....where true 那么binlog的数据量会越来越大
create table info 
insert into info values(1,'zhangsan');
insert into info values(2,'lisi');
delete id=zhangsan

③ MIXED 推荐使用
一般的语句使用statement，函数使用ROW方式存储。