Mysql里的备份和还原 及常见日志

日志

一、日志的定义和作用

1、定义：记录mysql发生的事情
2、作用

1.恢复还原数据--》二进制日志
2.排错
3.了解程序运行情况--》了解mysql性能
1
2
3

二、日志的类型

1.错误日志 error log:

1.存放的路径--》/data/mysql，文件名字--》主机名.err
2.查看：select @@log-error
1
2

2、通用日志 general log：–》 会记录mysql里面的所有操作

1、缺点：消耗cpu、内存、磁盘空间
2、系统默认不开启；
	临时开启：set global genera_log = 1;
3、查看：select @@general_log
1
2
3
4

3、慢日志 slow query log

1.记录消耗时间比较长的SQL语句，方便日后进行优化
2.系统默认不开启
3.查看状态：select @@slow_query_log
1
2
3

4、二进制日志 binary log：文件里存放的是二进制数据，不是普通文件，使用cat，tail看不了，要使用特殊的工具查看。

4.1、开启二进制日志的时候，必须设置服务器id，每台服务器的id不能一样，不然会导致主从复制失败
4.2、系统默认不开启
4.3、查看：select @@log_bin
4.4、查看二进制内容：mysqlbinlog -vv 二进制文件
4.5、查看当前使用的是哪个二进制文件：show master status;
	查看所有的二进制文件和大小：show binary logs;
4.6、二进制日志作用
	1.用来恢复数据
	2.用来做主从复制
4.7、产生新的二进制日志的三种方式
	1、service mysqld restart
	2、flush logs
	3、内存达到1G的时候
4.8、两种恢复数据的方式
	1、根据时间，把时间段内的操作再做一遍
	2、根据位置号，从某个位置号开始把操作重做一遍
4.9、日志的格式
	row level：记录每一行的数据，表里面的哪些行发生了变化
	statement level：记录用户输入的SQL语句
	mixed level：混合日志
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

5、innodb 存储引擎产生的日志
5.1、重做日志 redo log：记录的是脏数据的变化

1、文件的路径：/data/mysql
2、文件的名字：ib_logfile0、ib_logfile1
3、作用：确保事务的持久性，mysql意外宕机重启也不要紧，只要在重启时解析redo log中的事务而后重放一遍，将Buffer Pool中的缓存页重做成脏页，后续再在合适的时机将该胜页刷入磁盘便可
1
2
3

5.2、undo log：记录某数据被修改前的值

1、文件的路径：/data/mysql
2、文件的名字：ibdata1
3、作用：方便回滚
1
2
3

备份和还原

一、常见的备份方式

1、远程备份软件：rsync

rsync是linux系统下的数据镜像备份工具，使用快速增量备份工具Remote Sync可以远程同步，支持本地复制，或者与其他SSH、rsync主机同步
1

2、物理备份和逻辑备份

1.物理备份：直接备份数据
2.逻辑备份：备份的时表结构和产生数据的过程
1
2

3、冷备和热备

1.热备 online：Mysql服务是运行的
2.冷备 offline：Mysql服务是关闭的，不关闭机器
1
2

4、异地备份

1.rsync
2.scp
3.mysqldump
4.云存储
1
2
3
4

5、备份的方案

1.完全备份
2.增量备份
3.差异备份
1
2
3

6、数据的还原：备份的方案+二进制日志

二、常见的备份操作

1、相关命令

备份：mysqldump
还原：mysql
查看二进制日志：mysqlbinlog
1
2
3

2、mysqldump：不仅能备份表，还能备份库

2、实现备份还原案例，基于时间点恢复

步骤：
1.做一个有数据的库，进行完全备份	tangyuhao   t1
mysqldump -uroot -p123456 --databases tangyuhao>/tangyuhao_backup/tangyuhao.sql

2.做DML操作，增加数据
mysql>insert into t1(id,name) values(4,'cali'),(55,'wang'),(6,'zhang');

3.做删除数据库操作
drop database tangyuhao;

4.恢复全备
mysql -uroot -p123456 <tangyuhao.sql

5.查找删库附近的截止时间点或者截止位置号
mysqlbinlog -v mysql-bin.000002|grep -C 20 "drop database"
截至时间stop：220728 21:08:41

6.找到起始时间点或者位置号
mysqlbinlog -v mysql-bin.000002|
起始时间start：220728 21:07:57
# at 154
#220728 21:07:57	在位置号之后的时间

7.再根据二进制日志做增量恢复
mysqlbinlog -v --start-datetime="2022-07-28 21:07:57" --stop-datetime="2022-07-28 21:08:41" mysql-bin.000002 |mysql -uroot -p123456
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25