mysql日志备份恢复

mysql相关知识学习

一、相关命令

1、查看是否开启 binlog

（默认不开启，log_bin值为OF）及日志位置

show variables like '%log_bin%';

日志关闭的状态

日志开启的状态

2、查看产生的 日志文件。

show binary logs;

3、查看当前使用的日志文件

即记录操作的最后一个日志文件

show master status;

4、查看日志事件信息 ，开始、结束操作位置等

show binlog events;

5、查看mysql-bin.000001日志文件的事件信息 ，开始、结束操作位置等

show binlog events in 'mysql-bin.000001';

6、产生新日志，后面的操作会写到新日志中，日志名mysql-bin.00000X+1

flush logs;

7、清空所有的日志，从mysql-bin.000001开始重新记录日志

reset master;

8、查询mysql-bin.000001中记录的操作，不显示sql，要进入到mysql的安装目录中

mysqlbinlog mysql-bin.000001;

9、查询mysql-bin.000001中记录的操作，会显示sql语句

cmd

mysqlbinlog --base64-output=decode-rows -v 'mysql-bin.000001';
mysqlbinlog --no-defaults mysql-bin.000001

mysqlbinlog查看二进制日志的问题

在使用mysqlbinlog查看日志的时候碰到了一个问题，
错误提示如下：
/usr/local/mysql/bin/mysqlbinlog: unknown variable ‘default-character-set=utf8’

产生这个问题的原因是因为我在my.cnf中的client选项组中添加了
default-character-set=utf8

要解决这个bug的方法还是有的，
一种方法是使用：–no-defaults

./mysqlbinlog --no-defaults mysql-bin.000063

另一种方法：使用mysqlbinlog工具查看二进制日志时会重新读取的mysql的配置文件my.cnf(windows下是my.ini),而不是服务器已经加载进内存的配置文件。也就是说只要修改并保存了my.cnf文件，而不需要重起mysql服务器，则使用mysqlbinlog查看时修改后的my.cnf配置文件对mysqlbinlog而言已经生效。所以这里可以使用此方法：把client选项组中default-character-set=utf8选项屏蔽掉，如
#default-character-set=utf8
然后运行mysqlbinlog工具，则不会产生任何问题了。当然记得在不在使用mysqlbinlog工具时，把#default-character-set=utf8
选项恢复为default-character-set=utf8

二、修改配置

在my.ini [mysqld] 下面添加配置

#  log_bin=E:\mysql\mysql-5.7.35-winx64\backup\mysql-bin
log_bin=mysql-bin  
binlog_format=Row
server-id = 1

mysql binlog的三种工作模式

（1）ROW:基于行的复制 -----日志中会记录每一行数据被修改的形式

（2）Statement:基于sql语句的复制---- 每一条会修改数据的sql都会记录到master的bin-log中

（3）MIXED:混合模式复制—在Mixed模式下,MySQL会执行的每一条具体的sql语句来区分对待记录的日志格式。在Statement和Row之间选择一种

说明：

1）看准log_bin是下划线，不是横杠，mysql-bin是定义的 产生日志的文件名前缀。

2）server-id的值随便指定 ，集群环境下不能重复。

3）配置完成后，需要重启数据库，此时/var/lib/mysql下已经产生日志文件mysql-bin.index、mysql-bin.000001

补充：

#在[mysqld]下配置错误日志,查询日志,慢日志
#错误日志
log-error=/usr/local/mysql/log/error.log

#查询日志,对所有执行语句进行记录
general_log=on
general_log_file=/usr/local/mysql/log/mysql.log  

#开启慢查询
slow_query_log = on
#慢查询中记录没有使用索引的query
log-queries-not-using-indexes=on
#返回较慢的日志mysql5.6版本以上，取消了参数log-slow-queries，更改为slow-query-log-file 
slow-query-log-file= /usr/local/mysql/log/slowquery.log
#慢查询时间,这里为2秒,超过2秒会被记录
long_query_time=2

#日志输出到文件/数据库,都输出用,分割,默认只输出到FILE.加上TABLE后会输出在mysql.general_log,slow_log
#log_output='FILE,TABLE'

#二进制日志
server_id=1918         
log_bin = mysql-bin
binlog_format = ROW

三、演示数据恢复

3.1查看当前日志文件

​

当前记录日志文件mysql-bin.000001，初始位置154。

3.2新建数据库user，新建表t_test1，并准备数据如下

​

3.3再次查看记录位置，发现记录位置已改变

3.4、查看mysql-bin.000002日志文件的事件信息 ，开始、结束操作位置等

3.5、查看记录日志的详细信息，可以看到sql语句-- 未执行出来 报错

cmd中输入

[root@localhost mysql]# mysqlbinlog --base64-output=decode-rows -v mysql-bin.000002;
[root@localhost mysql]# mysqlbinlog --no-defaults mysql-bin.000002

3.6、重新开启新日志

3.7、开始演示恢复表操作，首先删除表

3.8、查看建表位置

mysqlbinlog --base64-output=decode-rows -v mysql-bin.000001;

9）执行恢复语句，查询结果，表及数据恢复完成。

–start-position 开始点

–stop-position 结束点

–start-date 开始时间

–stop-date 结束时间

mysqlbinlog --no-defaults --start-position=682 mysql-bin.000001 |mysql -uroot -proot

四、数据备份

·cp，tar物理备份

mysql> show variables like '%char%dir%';
+--------------------+----------------------------------------------+
| Variable_name      | Value                                        |
+--------------------+----------------------------------------------+
| character_sets_dir | E:\mysql\mysql-5.7.35-winx64\share\charsets\ |
+--------------------+----------------------------------------------+

·mysqldump备份

备份所有

mysqldump -uroot -p --all-databases >alldump.sql

备份部分数据库

不带–databases的备份语句在生成的.sql文件中没有create database和use 语句，需要手动建立数据库，可能导致数据库名与之前不一致

mysqldump -uroot -p   --database area>area.sql

备份数据库中的几张表

mysqldump -uroot -p    area a b c>abc.sql

恢复sql格式的student数据库的备份文件（注意：指明use database 同时避免孤表的存在 孤表：外部data中.frm文件被移除而Innodb中的内部数据字典造成冲突，解决办法：建立相同数据结构的表拷贝.frm文件到data中，在drop删除表重建）

source area.sql