投稿
  1. 首页
  2. 随笔

MySQL的日志与备份

衍生证券 • 2022-12-15 • 随笔 • 阅读 27

MySQL的日志与备份,第1张

MySQL的日志与备份

目录

一.日志

1.日志类型

1.redo重做日志：达到事务一致性（每次重启会重做）

2.undo回滚日志

3.errorlog错误日志

4.通用查询日志

4.slow query log慢查询日志

5.bin log二进制日志

6.relay log 中继日志

7.general log 普通日志

2.查看日志状态

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

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

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

4.查询慢查询时间设置

5.设置慢查询的方法（临时）

二.MySQL备份与恢复

1.数据备份的重要性

2.造成数据丢失的原因

3.物理和逻辑的角度分为

4.从数据库的备份策略角度分为

5.常见的备份方法

6.完全备份

1.创建表，添加表内容

2.冷备份

3. mysqldump备份

4. mysqldump恢复

7.增量备份与恢复

1.一般恢复

2.基于位置恢复

3.基于时间点  

一.日志

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

1.日志类型 1.redo重做日志：达到事务一致性（每次重启会重做）

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

2.undo回滚日志

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

3.errorlog错误日志

作用:Mysql本身启动，停止，运行期间发生的错误信息，默认已开启

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

log-error=/usr/local/mysql/data/mysql_error.log

[root@localhost ~]# vim /etc/my.cnf 

4.通用查询日志

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

general_log=ON

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

 [root@localhost ~]# vim /etc/my.cnf 

4.slow query log慢查询日志

作用:记录执行时间过长的sql，时间阈值(10s）可以配置，只记录执行成功另一个作用:在于提醒优化。默认是关闭的

slow_query_log=ON

slow_query_log_file=/usr/local/mysql/data/mysql_slow_query.log

long_query_time=5

[root@localhost ~]# vim /etc/my.cnf  

5.bin log二进制日志

作用:用于主从复制，实现主从同步记录的内容是:数据库中执行的sql语句。默认是关闭的

log-bin=mysql-bin

或log_bin=mysql-bin

[root@localhost ~]# vim /etc/my.cnf 

 每次重启服务就会生成一个二进制日志。

每次重启都是保留当前重启前的数据命令 *** 作。

6.relay log 中继日志

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

7.general log 普通日志

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

2.查看日志状态

xxx%：表示匹配以xxx开头

%xxx：表示匹配以xxx结尾

%xxx%：表示含有xxx都匹配

xxx：表示精确匹配

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

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

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

4.查询慢查询时间设置

5.设置慢查询的方法（临时）

(root@localhost) [(none)]> set global slow_query_log=ON;

二.MySQL备份与恢复 1.数据备份的重要性
  • 在生产环境中，数据的安全性至关重要
  • 任何数据的丢失都可能产生严重的后果
2.造成数据丢失的原因
  • 程序错误
  • 人为 *** 作错误
  • 运算错误
  • 磁盘故障
  • 灾难（如火灾、地震）和盗窃
3.物理和逻辑的角度分为

物理备份：对数据库 *** 作系统的物理文件（如数据文件、日志文件等）的备份

  • 物理备份方法       
    • 冷备份（脱机备份）：是在关闭数据库的时候进行的
    • 热备份（脱机备份）：数据库处于运行状态，依赖于数据库的日志文件
    • 温备份：数据库锁定表格（不可写入但不可读）的状态进行备份 *** 作

逻辑备份：对数据库逻辑组件（如：表等数据库对象）的备份

4.从数据库的备份策略角度分为
  • 完全备份：每次对数据库进行完整的备份

是对整个数据库、数据库结构和文件结构的备份
保存的是备份完成时刻的数据库
是差异备份与增量备份的基础

优点：备份与恢复 *** 作简单方便
缺点：数据存在大量的重复、占用大量的备份空间及备份与恢复时间长

  • 差异备份：备份自从上次完全备份之后被修改过的文件
  • 增量备份：只有在上次完全备份或者增量备份后被修改的文件才会被备份
5.常见的备份方法
  • 物理冷备
  • 专业备份工具mysqldump或mysqlhotcopy
  • 启用二进制日志进行增量备份
    • 进行增量备份，需要刷新二进制日志
  • 第三方工具备份
    • 热备份软件Percona XtraBackup
6.完全备份 1.创建表，添加表内容

(root@localhost) [oyyy]> create table oyyy(id int,name varchar(20),age int,address varchar(40),hobby varchar(50));

(root@localhost) [oyyy]> insert into oyyy values(1,'aaa',18,'nj','game');

(root@localhost) [oyyy]> insert into oyyy values(2,'bbb',19,'bj','music');

2.冷备份 
[root@localhost ~]# systemctl stop mysqld.service 
[root@localhost mysql]# mkdir /backup
[root@localhost mysql]# tar zcf /backup/mysql_all_$(date +%F).tar.gz /usr/local/mysql/data
[root@localhost mysql]# systemctl start mysqld.service 
##删掉数据库oyyy
[root@localhost mysql]# mv data/ data.bak/
[root@localhost backup]# tar zxf mysql_all_2021-11-29.tar.gz 
[root@localhost mysql]# cp -r data/ /usr/local/mysql/
[root@localhost mysql]# systemctl restart mysqld.service 
Job for mysqld.service failed because the control process exited with error code. See "systemctl status mysqld.service" and "journalctl -xe" for details.
## 此时重启服务报错是因为属主和属组不对
[root@localhost mysql]# chown -R mysql.mysql ./data
[root@localhost mysql]# systemctl restart mysqld.service 
##此时再查看数据库时oyyy库就恢复了

在数据库中 

3. mysqldump备份

当你用mysqldump命令备份还原数据库时,你会发现会有提示信息,此时呢数据库也是没有被导出的.

 此时就需要修改配置文件/etc/my.cnf,在client下添加主机用户和密码

[root@localhost mysql]# mysqldump -uroot -p123123 --databases oyyy > /backup/oyyy.sql

此时输入mysqldump备份虽然还是会报错,但是已经备份成功了

 

 或者还可以使用以下方法备份

[root@localhost mysql]# mysqldump --defaults-extra-file=/etc/my.cnf oyyy >/backup/oyyy1.sql

备份全部数据库

[root@localhost mysql]# mysqldump -uroot -p123123 --databases > /backup/all.sql  

备份数据库 数据表

[root@localhost mysql]# mysqldump -uroot -p123123 oyyy oyyy > /backup/oyyy_oyyy.sql

只备份数据表 使用“-d”选项，说明只保存数据库的表结构

[root@localhost mysql]# mysqldump -uroot -p123123 -d oyyy oyyy > /backup/oyyy_oyyy-d.sql

 查看备份文件

[root@localhost backup]# cat oyyy_oyyy.sql |grep -v '^/'|grep -v '^$'|grep -v '^-'

4. mysqldump恢复

备份恢复1

先将oyyy数据库删掉 

然后将之前备份的文件导进去

[root@localhost backup]# mysql -u root -p123123 < /backup/oyyy.sql  

 此时oyyy数据库就又恢复了

 备份恢复2

将数据库oyyy删掉之后再创建一个oyyy数据库

将之前备份的文件导进去

[root@localhost backup]# mysql -u root -p123123 oyyy< /backup/oyyy_oyyy.sql 

要指定数据库

  此时oyyy数据库和数据表就又恢复了

 备份恢复3

将表oyyy删掉

将之前备份的文件导进去

[root@localhost backup]# mysql -u root -p123123 oyyy< /backup/oyyy_oyyy-d.sql

要指定数据库

  此时oyyy数据表就又恢复了,但是内容为空了

7.增量备份与恢复 1.一般恢复

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

二进制日志（binlog）有3种不同的记录格式：

（STATMENT）基于SQL语句

（ROW）基于行

（MIXED）混合模式

默认格式为STATMENT

日志格式STATEMENT（基于SQL语句，默认）ROW（基于行）MIXED（混合模式）说明每一条涉及到被修改的 sql 都会记录在 binlog 中只记录变动的记录，不记录 sql 的上下文一般语句使用 STATEMENT，函数使用 ROW缺点日志量过大，如函数、主从复制等构架记录日志时会出现问题如果遇到 updata…set…where true，那么日志数据量就会很大推荐使用

[root@localhost mysql]# vim /etc/my.cnf

log-bin=mysql-bin
binlog_format=MIXED

[root@localhost mysql]# systemctl restart mysqld.service 

向数据表中添加id=3的数据 

[root@localhost data]# mysqladmin -u root -p123123 flush-logs

进行刷新，生成新的二进制文件相当于重启mysql服务

mysql-bin-000003就是插入id=3的数据内容

 向数据表中删除id=3的数据 

[root@localhost data]# mysqladmin -u root -p123123 flush-logs

mysql-bin-000004就是删除id=3的数据内容

[root@localhost data]# mysqlbinlog --no-defaults --base64-output=decode-rows -v /usr/local/mysql/data/mysql-bin.000003

查看二进制日志文件

[root@localhost data]# mysqlbinlog --no-defaults /usr/local/mysql/data/mysql-bin.000003 |mysql -u root -p123123

恢复数据插入id=3的数据

 此时查看数据表，就有id=3的数据内容了

2.基于位置恢复

向数据表中删除id=3的数据 

[root@localhost data]# mysqlbinlog --no-defaults --base64-output=decode-rows -v /usr/local/mysql/data/mysql-bin.000003

查看二进制日志文件

所以298是插入id=3的数据内容的起始位置 

[root@localhost data]# mysqlbinlog --no-defaults --start-position='298' /usr/local/mysql/data/mysql-bin.000003 |mysql -u root -p123123

根据位置恢复数据插入id=3的数据

此时查看数据表，就有id=3的数据内容了

 

3.基于时间点  

向数据表中删除id=3的数据 

[root@localhost data]# mysqlbinlog --no-defaults --base64-output=decode-rows -v /usr/local/mysql/data/mysql-bin.000003

查看二进制日志文件

所以21 11 30 14:57:59是插入id=3的数据内容的起始时间

[root@localhost data]# mysqlbinlog --no-defaults --start-datetime='2021-11-30 14:57:59' /usr/local/mysql/data/mysql-bin.000003 |mysql -u root -p123123

根据时间恢复数据插入id=3的数据

此时查看数据表，就有id=3的数据内容了

 

欢迎分享，转载请注明来源：内存溢出

原文地址: http://outofmemory.cn/zaji/5624742.html

赞 (0)
打赏 微信扫一扫 微信扫一扫 支付宝扫一扫 支付宝扫一扫
衍生证券 衍生证券 一级用户组
0 0
上一篇 2022-12-15
下一篇 2022-12-15

发表评论

登录后才能评论

评论列表（0条）

保存