Mysql_日志、备份与恢复

最新推荐文章于 2024-11-08 22:59:30 发布
最新推荐文章于 2024-11-08 22:59:30 发布
阅读量249 收藏 2
点赞数 1
文章标签： mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/Wsxyi/article/details/115697696
版权

备份与恢复

一.概述

1.备份

备份的主要目的是灾难恢复，备份还可以测试应用、回滚数据修改、查询历史数据、审计

2.日志

 备份、恢复中，日志起到了重要的作用

- 常用的日志文件在（/etc/my.cnf中[mysqld] 客户端配置中修改）
- 默认在 /usr/local/mysql/data/中

二.日志分类

1.错误日志

用于记录Mysql 启动、停止、或运行时产生的错误信息，默认开启
  • 可通过已下字段进行更新：
log-error=/usr/local/mysql/data/mysql_error.log

##指定日志的保存位置和文件名

2.二进制日志

用来记录所有更新的数据或者已近潜在更新了数据的语句，记录了数据的更改，可用于数据恢复，默认开启
  • 开启方式：
log-bin=mysql-bin
或者
log_bin=mysql-bin

3.中继日志

 一般情况下它在Mysql主从同步（复制）、读写分离集群的节点才开启。主节点一般不需要整个日志

4.慢查询日志

用来记录所有执行时间超过long_query_time秒的语句，可以找到哪些查询语句执行时间长，以便于优化，默认时关闭的

slow_query_log=ON
slow_query_log_file=/usr/local/mysql/data/mysql_slow_query.log
##指定文件路径和名称
long_query_time=5
#设置执行超过5秒的语句会被记录，缺省时为10秒

5.通用查询日志

 用来记录MySQL的所有连接和语句，默认是关闭的

general_log=ON
general_log_file=/usr/local/mysql/data/mysql_general.log

在这里插入图片描述

在这里插入图片描述在这里插入图片描述

三.查看日志状态

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

mysql -u root -p
show variables like 'general%';

在这里插入图片描述
在这里插入图片描述

2.查看二进制日志是否开启

show variables like 'log_bin%';

在这里插入图片描述

3.查询慢查询日志

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

show variables like '%slow%';

在这里插入图片描述

查看慢查询时间设置

show variables like 'long_query_time';

在这里插入图片描述

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

set global show_query_log=ON;

四.mysql 完全备份与恢复

1.数据备份的重要性

  • 备份的目的时灾难恢复
  • 在生产环境中，数据的安全性至关重要
  • 任何数据的丢失都可能产生严重的后果

2.造成数据丢失的原因

  • 程序错误
  • 人为操作错误
  • 运算错误
  • 磁盘故障
  • 灾难

3.数据库备份的分类

从物理与逻辑的角度分类

1)物理备份

  • 对数据库操作系统的物理文件（如数据文件、日志文件等）的备份
物理备份的方法：
  • 冷备份：关闭数据库的时候进行
  • 热备份：数据库处于运行状态，依赖于数据库的日志文件
  • 温备份：数据库锁定表格（不可写入但可读）的状态下进行备份操作

2)逻辑备份

  • 对数据库逻辑组件(表数据库对象）的备份

从数据库的备份策略角度分类

1)完全备份

 每次对数据进行完整的备份，对整个数据库、数据库结构和文件结构的备份，是差异备份与增量备份的基础 
 数据存在大量的重复，并且会占用大量的磁盘空间，备份时间长

2）差异备份

 备份那些自从上次完全备份之后修改过的所有文件，备份的时间节点是从上次完整备份起，备份数据量会越来越大，恢复数据时，只需恢复上次的完全备份与最近一次的差异备份

3) 增量备份

 只有那些在上次完全备份或者增量备份后被修改的文件才会被备份。
 以上次完整备份或上次增量备份的时间为时间点，仅备份这之间的数据变化，因而备份的数据量下，占用空间下，备份速度快。但恢复时，需要上一次的完整备份开始到最后一次增量备份的所有增量依次恢复
如中间某次的备份数据损坏，将导致数据的丢失

4.常见的备份方法

1）物理冷备份

需要在数据库处于关闭状态下，保证数据库的完整性

速度快，恢复时也是最为简单的

直接打包数据库文件夹/usr/local/mysql/data)来实现备份

2）专用备份工具Mysqldump或Mysqlhotcopy

mysqldump程序和mysqlhotcopy都可以做备份
mysqldump 是客户端常用逻辑备份程序，能够产生一组被执行以后再现原始数据库对象定义和表数据的SQL语句
mysqldump 更为通用，可备份各种表
 mysqlhotcopy 仅适用于某些存储引擎

3）启用二进制日志进行增量备份mysql

支持增量备份，进行增量备份时必须启用二进制日志
二进制日志文件为用户提供复制，对执行备份点后进行的数据库更改所需的信息进行恢复
进行增量备份，需要刷新二进制日志

4）第三方工具备份

免费的Mysql热备份软件 Percona XtraBackup
支持在线热备份 Innodb 和XtraDB ,也可支持Mysql表备份，不过myISAM 表的备份要在表锁得情况下进行

五.Mysql 完全备份

InnoDB存储引擎的数据库在磁盘上存储成三个文件：db.opt(表属性文件)、表名.frm(表结构文件)、表名.ibd(表数据文件)。

1.物理冷备份与恢复

systemctl stop mysqld
yum -y install xz					#xz是一个压缩工具
#压缩备份
tar Jcvf /opt/mysql_all_$(date +%F).tar.xz /usr/local/mysql/data/
#解压恢复
tar Jxvf /opt/mysql_all_2021-04-14.tar.xz -C /usr/local/mysql/data


systemctl start mysqld

在这里插入图片描述
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述

2.mysqldump 备份与恢复

1）完全备份一个或多个完整的库（包括其中所有的表）
#导出的备份文件就是数据库脚本文件
mysqldump -u root -p[密码] --databases 库名1 [库名2] … > /备份路径/备份文件名.sql
例：
mysqldump -u root -p --databases school > /opt/school.sql
mysqldump -u root -p --databases mysql school > /opt/mysql-school.sql

在这里插入图片描述
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述

2）完全备份mysql服务器中的所有库

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

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

例：
mysqldump -u root -p --all-databases > /opt/all.sql

在这里插入图片描述
在这里插入图片描述

3）完全备份指定库中的部分表

mysqldump -u root -p[密码] [-d] 库名 [表名1] [表名2] … > /备份路径/备份文件名.sql
#使用“ -d ”选项，说明只保存数据库的表结构
#不使用“ -d ”选项，说明表数据也进行备份
例：
mysqldump -u root -p -d school test1 > /opt/school_test1.sq1

在这里插入图片描述

4） 查看备份文件

cat /opt/备份的文件 |grep -v "^--" | grep -v "^/" | grep -v "^$"

例：
cat /opt/school_class.sql |grep -v "^--" | grep -v "^/" | grep -v "^$"

在这里插入图片描述
在这里插入图片描述

六.mysql 完全恢复

1.恢复数据库

#“-e”选项，用于指定连接 MySQL 后执行的命令，命令执行完后自动退出
mysql -u root -p -e 'drop database school;'
mysql -u root -p -e 'show databases;'

mysql -u root -p < /opt/school.sql
mysql -u root -p -e 'show databases;'

在这里插入图片描述

2）恢复数据表

当备份文件中只包含表的备份，而不包含创建的库的语句时，执行导入操作时必须指定库名，且目标库必须存在。

mysql -u root -p -e 'drop table school.test2;'
mysql -u root -p -e 'show tables from school;'



mysql -u root -p school < /opt/school_test2.sql
mysql -u root -p -e 'show tables from school;'

在这里插入图片描述

七.mysql 增量备份与恢复

1） Mysql 增量备份

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

systemctl start mysqld

cd /usr/local/mysql/data/
 ls



ls -l /usr/local/mysql/data/mysql-bin.*

在这里插入图片描述

在这里插入图片描述

2.可每周对数据库或表进行完全备份


mysqldump -u root -p school test1 > /opt/school_test1_$(date +%F).sql

mysqldump -u root -p school > /opt/school_$(date +%F).sql

 
 crontab -e

0 2 * * 1 mysqldump -u root -p school test1 > /opt/school_test1_$(date +%F).sql

0 2 * * 1 mysqldump -u root -p school > /opt/school_$(date +%F).sql

在这里插入图片描述

在这里插入图片描述
在这里插入图片描述

3．可每天进行增量备份操作，生成新的二进制日志文件(例如 mysql-bin.000002)

mysqladmin -u root -p flush-logs

在这里插入图片描述

4.插入新数据，以模拟数据的增加或变更

mysql -u root -p


use school;
show tables;

insert into test1 values(3,'王五',40),(4,'李六',50);

在这里插入图片描述
在这里插入图片描述

5．再次生成新的二进制日志文件(例如 mysql-bin.000003)

mysqladmin -u root -p flush-logs
#之前的步骤4的数据库操作会保存到mysql-bin.000002文件中，之后数据库数据再发生变化则保存在mysql-bin.000003文件中

在这里插入图片描述

6．查看二进制日志文件的内容

cp /usr/local/mysql/data/mysql-bin.000002 /opt/
mysqlbinlog --no-defaults --base64-output=decode-rows -v /opt/mysql-bin.000002
#--base64-output=decode-rows：使用64位编码机制去解码并按行读取
#-v：显示详细内容

在这里插入图片描述
在这里插入图片描述

2）Mysql 增量恢复

1.一般恢复

（1）模拟丢失更改的数据的恢复步骤


mysql -u root -p123456
use school;
delete from test1 where id=3;
delete from test1 where id=4;

select * from test1;

 mysqlbinlog --no-defaults /opt/mysql-bin.000002 | mysql -u root -p
mysql -u root -p -e 'use school;select * from test1';

在这里插入图片描述
在这里插入图片描述
在这里插入图片描述

（2）模拟丢失所有数据的恢复步骤


mysql -u root -p123456

use school;
show tables;
drop table test1;
show tables;


 mysql -u root -p school < /opt/school_test1_2021-04-15.sql

mysqlbinlog --no-defaults /opt/mysql-bin.000002 | mysql -u root -p

在这里插入图片描述在这里插入图片描述

在这里插入图片描述

2.断点恢复

mysqlbinlog --no-defaults --base64-output=decode-rows -v /opt/mysql-bin.000002

# at 4
#210415 23:26:24 server id 1  end_log_pos 123 CRC32 0x56b1b3a0 	Start: binlog v 4, server v 5.7.20-log created 210415 23:26:24
# Warning: this binlog is either in use or was not closed properly.
# at 123
#210415 23:26:24 server id 1  end_log_pos 154 CRC32 0xa2773434 	Previous-GTIDs
# [empty]
# at 154
#210415 23:32:12 server id 1  end_log_pos 219 CRC32 0xa641e810 	Anonymous_GTID	last_committed=0	sequence_number=1	rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 219
#210415 23:32:12 server id 1  end_log_pos 366 CRC32 0xf6614805 	Query	thread_id=73	exec_time=0	error_code=0
use `school`/*!*/;
SET TIMESTAMP=1618500732/*!*/;
SET @@session.pseudo_thread_id=73/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1437073414/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
create table test6(
id int(5) not null,
name varchar(40),
age int(5))
/*!*/;
# at 366
#210415 23:33:57 server id 1  end_log_pos 431 CRC32 0xdf1b202e 	Anonymous_GTID	last_committed=1	sequence_number=2	rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 431
#210415 23:33:57 server id 1  end_log_pos 514 CRC32 0xd2405743 	Query	thread_id=73	exec_time=0	error_code=0
SET TIMESTAMP=1618500837/*!*/;
BEGIN
/*!*/;
# at 514
#210415 23:33:57 server id 1  end_log_pos 679 CRC32 0xf6c239c2 	Query	thread_id=73	exec_time=0	error_code=0
SET TIMESTAMP=1618500837/*!*/;
insert into test6 values(1,'张三',10),(2,'李四',20),(3,'王五',30),(4,'李六',40)
/*!*/;
# at 679
#210415 23:33:57 server id 1  end_log_pos 710 CRC32 0xd3f77820 	Xid = 848
COMMIT/*!*/;
# at 710
#210415 23:36:03 server id 1  end_log_pos 775 CRC32 0x49540414 	Anonymous_GTID	last_committed=2	sequence_number=3	rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 775
#210415 23:36:03 server id 1  end_log_pos 858 CRC32 0xf5e69fc1 	Query	thread_id=73	exec_time=0	error_code=0
SET TIMESTAMP=1618500963/*!*/;
BEGIN
/*!*/;
# at 858
#210415 23:36:03 server id 1  end_log_pos 964 CRC32 0x95f76436 	Query	thread_id=73	exec_time=0	error_code=0
SET TIMESTAMP=1618500963/*!*/;
delete from test6 where id=1
/*!*/;
# at 964
#210415 23:36:03 server id 1  end_log_pos 995 CRC32 0xc0f11b56 	Xid = 850
COMMIT/*!*/;
# at 995
#210415 23:36:41 server id 1  end_log_pos 1060 CRC32 0x8e8f14df 	Anonymous_GTID	last_committed=3 sequence_number=4	rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 1060
#210415 23:36:41 server id 1  end_log_pos 1143 CRC32 0x6b1fc9ea 	Query	thread_id=73	exec_time=0	error_code=0
SET TIMESTAMP=1618501001/*!*/;
BEGIN
/*!*/;
# at 1143
#210415 23:36:41 server id 1  end_log_pos 1260 CRC32 0xe432fdeb 	Query	thread_id=73	exec_time=0	error_code=0
SET TIMESTAMP=1618501001/*!*/;
insert into test6 values(5,'赵七',50)
/*!*/;
# at 1260
#210415 23:36:41 server id 1  end_log_pos 1291 CRC32 0x0faff734 	Xid = 851
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;














mysqladmin -u root -p flush-logs
cd /usr/local/mysql/data

mysql -u root -p123456


mysqladmin -u root -p flush-logs

cp /usr/local/mysql/data/mysql-bin.000009 /opt
mysqlbinlog --no-defaults --base64-output=decode-rows -v /opt/mysql-bin.000009

在这里插入图片描述

在这里插入图片描述

在这里插入图片描述
在这里插入图片描述

在这里插入图片描述

基于位置恢复

  • 仅恢复到操作 ID 为“858”之前的数据，即不恢复“858”的数据
mysqlbinlog --no-defaults --stop-position='858' /opt/mysql-bin.000009 | mysql -uroot -p

mysql -u root -p123456 -e 'use school;select * from test6;'

在这里插入图片描述

  • 仅恢复“1143”的数据，跳过“858”的数据恢复
 mysqlbinlog --no-defaults --start-position='1143' /opt/mysql-bin.000009 | mysql -uroot -p

mysql -u root -p123456 -e 'use school;select * from test6;'

在这里插入图片描述

（2）基于时间点恢复
  • 仅恢复到 2021-04-15 23:36:03’之前的数据，即不恢复“本条”的数据

mysqlbinlog --no-defaults --stop-datetime='2021-04-15 23:36:03' /opt/mysql-bin.000009 |mysql -uroot -p

mysql -u root -p -e 'use school;select * from test6;'

在这里插入图片描述
在这里插入图片描述

#仅恢复“2021-04-15 23:36:41”的数据，跳过“user3”的数据恢复

mysqlbinlog --no-defaults --start-datetime='2021-04-15 23:36:41' /opt/mysql-bin.000009 |mysql -uroot -p

在这里插入图片描述

Wsxyi
关注
MySQL_Cluster备份与恢复
10-11
MySQL_Cluster备份与恢复 MySQL_Cluster是一种高可用性的分布式关系数据库管理系统，它可以提供高性能、可扩展性和高可用性。由于其分布式架构，MySQL_Cluster需要特殊的备份和恢复机制。本文将介绍MySQL_Cluster的...
07.26MySQL日志 备份与恢复1
08-08
MySQL 日志备份与恢复 MySQL 是一种常用的关系数据库管理系统，它提供了多种日志机制来记录数据库的各种操作和错误信息。这些日志文件对于数据库的备份和恢复至关重要。本文将详细介绍 MySQL 的日志机制、备份类型...
MySQL日志管理和数据备份
guyunbingyb的博客
07-05 631
MySQL日志管理和数据备份
MySQL的备份与恢复
octoben的博客
06-06 2062
1.1 数据备份的重要性 备份的主要目的是灾难恢复 在生产环境中，数据的安全性至关重要 任何数据的丢失都可能产生严重的后果
MySQL数据库的备份与恢复
m0_71493671的博客
07-18 2518
备份数据中有重复数据备份时间与恢复时间过长。
MySQL日志、备份与恢复
xiwagogogo的博客
04-19 316
MySQL日志、备份与恢复MySQL日志MySQL常用日志类型及开启错误日志通用查询日志二进制日志（binlog)慢查询日志日志状态查看通用查询日志是否开启查看二进制日志是否开启查看慢查询日功能是否开启查看慢查询时间设置MySQL备份与恢复数据备份的重要性数据库备份的分类物理备份逻辑备份常见的备份方法MySQL完全备份与恢复MySQL 完全备份mysqldump 备份与恢复完全备份恢复MySQL 增量备份与恢复MySQL 增量备份MySQL 增量备份恢复 MySQL日志 MySQL的默认日志保存位置为/us
mysqlbackup.rar_delphi mysql_mysql 备份_mysqlbackup_备份 delphi
09-22
描述中的“mysql自动备份mysql自动备份mysql自动备份”重复强调了自动备份的重要性，这通常意味着该程序能够定期执行，无需人工干预，从而确保在数据发生意外丢失或损坏时可以恢复。 在标签中，“delphi_mysql”、...
MySQL-日志、备份和恢复
一起学习吧
06-24 141
日志管理(工具) 错误日志 1.1 作用 排错.MySQL 启动和工作过程状态,警告,错误. 1.2 配置 默认开启.存储位置, datadir/hostname.err log_error=/data/mysql/data_3306/mysql.log 1.3 查看 [ERROR] 二进制日志 2.1 作用 数据恢复,主从复制.记录的是所有变化类的操作(DDL,DML,DCL). 2.2 配置 server_id=6 log_bin=/data/mysql/b
MYSQL.rar_MYSQL_mysql 优化_mysql优化_mysql备份_sql优化
09-14
本资料包“MYSQL.rar”提供了关于MySQL性能提升、备份策略以及复制和负载平衡的重要知识。 一、MySQL优化 1. 结构优化：这涉及到数据库设计，如合理创建索引、避免全表扫描、选择合适的数据类型，以及规范化与反...
MySQL备份恢复与日志
linuxlsq的博客
09-21 3602
MySQL 数据库的备份与恢复 1.1 备份数据的意义 第一是保护公司的数据，第二是网站7*24小时提供服务 1.2 备份单个数据库参数使用 MySQL数据库自带了一个很好用的备份命令，就是mysqldump，它的基本使用如下； 语法：mysqldump -u 用户名 -p  数据库名 > 备份的文件名 mysqldump -uroot -p123456 -S /data/33
大厂基本功 | MySQL 三大日志 ( binlog、redo log 和 undo log ) 的作用？
langjiaohjiopji的博客
11-06 1179
MySQL日志 主要包括错误日志、查询日志、慢查询日志、事务日志、二进制日志几大类。其中，比较重要的还要属二进制日志binlog（归档日志）和事务日志redo log（重做日志）和undo log（回滚日志）。今天就来聊聊redo log（重做日志）、binlog（归档日志）、两阶段提交、undo log（回滚日志）。MySQL InnoDB 引擎使用redo log(重做日志)保证事务的持久性，使用undo log(回滚日志)来保证事务的原子性。MySQL数据库的数据备份、主备、主主、主从都离不开。
LINUX下的Mysql：Mysql基础
W2155的博客
11-05 424
数据库其实是一个网络服务，我下载好一个mysql 是分为两部分的，mysql客户端和mysqld服务端，只不过我们学习的时候，客户端和服务端是在同一台机器上的。使用netstat -nltp命令查看mysqld的网络端口号。通过mysql客户端 连接服务器上的mysqld，就可以修改服务器 内存中或磁盘中的数据。我在口语中说的数据库一般是指，存储在内存和磁盘中的特定结构的数据。
【MySQL】 穿透学习数据库理论与知识剖析
strive_mianyang的博客
11-04 3119
MySQL数据库 | 数据库基础概念
ubuntu 22.04 server 安装 mysql 5.7.40 LTS
abc
11-06 486
ubuntu 22.04 server 安装 mysql 5.7.40 LTS。
MySQL 索引
最新发布
果酱 の 博客
11-08 722
MySQL 索引是提高数据库性能的重要手段。通过选择合适的索引类型、创建必要的索引、定期维护索引和监控索引使用情况，可以大大提高数据库的查询性能。深入理解 MySQL 索引的原理和使用方法，对于构建高效的数据库应用至关重要。
Linux（CentOS）安装 MySQL
BillKu的博客
11-06 274
MySQL版本：MySQL Community Server 8.4.3 LTS。CentOS版本：CentOS 7。
PySimpleGUI库和pymysql库
Lonelinessser的博客
11-06 181
PySimpleGUI 是一个用于简化 GUI 编程的 Python 包，它封装了多种底层 GUI 框架（如 tkinter、Qt、WxPython 等），提供了简单易用的 API。PySimpleGUI 包含了大量的控件（也称为小部件或组件），这些控件可以帮助你快速构建用户界面说明：5.0以上的版本不免费。
MySQL 基础
jeffery 的博客
11-08 599
循序渐进理解 MySQ L的基础知识，包含 SQL、约束、函数、多表查询、事务等
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值