mariadb二进制日志和备份恢复

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
日志服务 SLS,月写入数据量 50GB 1个月
简介:

一、日志及其说明  

1、错误日志 

    主要记录服务器启动和关闭过程中的信息,服务器运行过程中的错误信息,事务调度器运行一个事件是产生的日志信息,在从服务器上启动从服务器上启动从服务器进程是产生的日志信息。 

2、一般查询日志 

general_log、general_log_file 、log、log_output   

3、慢查询日志 

    查询执行时长超过指定的查询时长,即为慢查询。主要的配置文件有:slow_query_log,slow_query_log_file /mydata/data/hostname.log   

4、二进制日志 

    任何引起或可能引起数据库改变的操作如复制、即时点恢复等。二进制日志记录着每一个ddl,dml,dcl命令及其重启等操作导致数据库发生变化的数据。  

5、中继日志 

    relay_log_purge={ON|OFF}#是否自动清理不再需要中继日志  

6、事务日志

    将随机IO换装为顺讯IO,保证数据的一致性、原子性、完整性 


二、二进制日志详解

1、二进制日志的格式

基于语句:statement;基于行:row;混合模式:mixed   

2、二进制日志文件

索引文件(mysql-bin.index),二进制日志文件(mysql-bin.00000x) 

3、二进制日志的功能

即时点恢复、复制

4、常用的二进制日志命令 

显示所有的二进制日志文件:mysql>show binary logs;

执行滚动,即二进制日志会自动滚动一次:mysql>flush logs;

显示当前使用的二进制日志文件:mysql>show master status; 

命令行查看二进制日志文件内容:#mysqlbinlog mysql-bin.000001   

查看二进制日志文件内容:mysql>show binlog events in 'mysql-bin.000001';  

mysqlbinlog常用的选项: --start-time --stop-time --start-position --stop-position 


图示:


显示二进制日志文件

wKiom1f7od3BxS-7AAA21Ze0JOY973.png


二进制文件格式如下:

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
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
[root@chen data] # mysqlbinlog --stop-position=1451 '/mydata/binlog/master-bin.000001'
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#160929 11:41:17 server id 1  end_log_pos 245 Start: binlog v 4, server v 5.5.44-MariaDB-log created 160929 11:41:17 at startup
ROLLBACK/*!*/;
BINLOG '
XY3sVw8BAAAA8QAAAPUAAAAAAAQANS41LjQ0LU1hcmlhREItbG9nAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAABdjexXEzgNAAgAEgAEBAQEEgAA2QAEGggAAAAICAgCAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAZwK5ww==
'/*!*/;
# at 245
#160929 13:39:36 server id 1  end_log_pos 332 Querythread_id=4exec_time=0error_code=0
SET TIMESTAMP=1475127576/*!*/;
SET @@session.pseudo_thread_id=4/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=0/*!*/;
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 database testdb
/*!*/;
# at 332
#160929 13:40:40 server id 1  end_log_pos 454 Querythread_id=5exec_time=0error_code=0
use `testdb`/*!*/;
SET TIMESTAMP=1475127640/*!*/;
SET @@session.foreign_key_checks=0, @@session.unique_checks=0/*!*/;
SET @@session.sql_mode=524288/*!*/;
DROP TABLE IF EXISTS `students` /* generated by server */
/*!*/;
# at 454
#160929 13:40:40 server id 1  end_log_pos 752 Querythread_id=5exec_time=0error_code=0
SET TIMESTAMP=1475127640/*!*/;
CREATE TABLE `students` (
   ` id ` int(10) unsigned NOT NULL,
   `name` varchar(20) DEFAULT NULL,
   `age` tinyint(3) unsigned DEFAULT NULL,
   `gender` enum( 'f' , 'm' ) DEFAULT NULL,
   PRIMARY KEY (` id `)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
/*!*/;
# at 752
#160929 13:40:40 server id 1  end_log_pos 864 Querythread_id=5exec_time=0error_code=0
SET TIMESTAMP=1475127640/*!*/;
/*!40000 ALTER TABLE `students` DISABLE KEYS */
/*!*/;
# at 864
#160929 13:40:40 server id 1  end_log_pos 975 Querythread_id=5exec_time=0error_code=0
SET TIMESTAMP=1475127640/*!*/;
/*!40000 ALTER TABLE `students` ENABLE KEYS */
/*!*/;
# at 975
#160929 13:40:40 server id 1  end_log_pos 1091 Querythread_id=5exec_time=0error_code=0
SET TIMESTAMP=1475127640/*!*/;
DROP TABLE IF EXISTS `t2` /* generated by server */
/*!*/;
# at 1091
#160929 13:40:40 server id 1  end_log_pos 1240 Querythread_id=5exec_time=0error_code=0
SET TIMESTAMP=1475127640/*!*/;
CREATE TABLE `t2` (
   ` id ` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
/*!*/;
# at 1240
#160929 13:40:40 server id 1  end_log_pos 1346 Querythread_id=5exec_time=0error_code=0
SET TIMESTAMP=1475127640/*!*/;
/*!40000 ALTER TABLE `t2` DISABLE KEYS */
/*!*/;
# at 1346
#160929 13:40:40 server id 1  end_log_pos 1451 Querythread_id=5exec_time=0error_code=0
SET TIMESTAMP=1475127640/*!*/;
/*!40000 ALTER TABLE `t2` ENABLE KEYS */
/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
[root@chen data] #


上述格式说明: 

    事件发生的日期和时间

    服务器id

    时间的结束位置

    事件的类型

    原服务器生成此事件的线程id

    语句的时间戳和写入二进制日志文件的时间差

    错误代码

    事件内容

    下一个事件的开始位置


常用服务器参数:

    log_bin = {ON|OFF},此可以为一个文件路径

    log_bin_trust_funcition_creators 

    sql_log_bin = {ON|OFF}

    sync_binlog

    binlog_format = mixed {statement|row|mixed}

    max_binlog_cache_size =  #二进制日志的缓冲区大小,仅用于缓冲事务类的语句

    max_binlog_stmt_cache_size =  #状态缓冲区大小

    max_binlog_size =  #二进制日志文件的上限,超过则会自动滚动 

    set session sql_log_bin=0; #可使得不记录二进制日志文件


注意:切将二进制日志文件和数据文件存放在同一文件中


图示:


所有与日志相关变量

wKioL1f7oE7BnHVhAABhB7xrKHo993.png

wKiom1f7qTqj-x_MAABN483Z0Dc954.png

迁移二进制文件至其他目录

wKiom1f7qO_Bp1n2AAA-V4q2NoY841.png


三、数据备份及其恢复

    数据备份的主要目的是为了数据恢复,对备份的数据做恢复测试,审计,及其测试等相关操作。

1、备份的类型

冷备:cold backup 温备:warm backup 热备:hot backup   

2、根据备份的数据集分为

完全备份:full backup 部分备份:partial backup  

3、根据备份时的接口(直接备份数据文件还是通过mysql服务器导出数据)分为

物理备份:直接复制(归档)数据文件的备份方式,physical backup(对数据量很大时适用)

逻辑备份:把数据从数据库中提取出来保存为文件,logical backup(文件大小大于10G不适用),主要的备份工具为(mysqldump)。MYISAM只支持温备,且不支持增量备份,而INNODB则为热备,为增量备份。

4、备份工具(mysqldump) 

myslqdump为逻辑备份工具,备份和恢复速度比较慢,只适合数据量不是很大的数据库。


mysqldump命令: 

    mysqldump [options] [db_name [tbl_name ...]]

备份单个数据库:mysqldump db_name 

实例:mysqldump -uroot -hlocalhost -p testdb >/tmp/testdb.sql

恢复数据时需事先创建库再执行数据恢复:mysql -uroot -p testdb</tmp/testdb.sql

备份所有数据库:mysqldump --all-databases >/tmp/all.sql 

备份多个数据库:mysqldump -utestuser -h10.1.10.1 -p --databases testdb test >/tmp/testdbs.sql

恢复时无需先创建库:mysql -utestuser -h10.1.10.1 -p </tmp/testdbs.sql  


图示:


单表备份恢复时需事先准备库才能恢复到数据库

wKiom1f8P7HjnD5bAABMyDCjtLE856.png

备份多个数据库及其恢复

wKioL1f8QtKgor1iAABPxNx7ahc062.png


注意:上诉备份方案存在一定的弊端如:用户正在执行数据操作时,数据则会丢失等情况。 

解决方案:在备份是给库表加上锁,保证数据的完整性。 

--lock-all-tables:请求锁定所有表 

实例:mysqldump --databases testdb --lock-all-tables >/tmp/testdb.sql  

--single-transaction:单事务,能够对innodb存储引擎做热备 

--events:备份事件调度器代码    --routines:备份存储过程和存储函数    --triggers:备份触发器  


备份时请求锁之后滚动日志:--flush-logs

复制时同步位置标记:--master-data=[0|1|2]  


注意:恢复时需关闭二进制文件,恢复成功后则开启 

关闭二进制日志:set session sql_log_bin=0 

开启二进制日志:set session sql_log_bin=1 


锁表备份步骤: 

1)锁表并且给只读权限

flush tables with read lock; 

2)滚动日志

flush logs;

3)查看二进制日志信息

show master status; 

4)使用mysqldump进行数据备份

mysqldump --databases testdb >/tmp/testdb.sql

5)备份完成之后则需解锁 

unlock tables;  


命令行备份实例如下:

mysqldump -p --databases testdb --lock-all-tables --flush-logs >/tmp/testdb2.sql 

mysqldump -p --databases testdb --single-trasaction --flush-logs >/tmp/testdb3.sql 

mysqldump -p --databases testdb --lock-all-tables --flush-logs --master-data=2 >/tmp/testdb4.sql


图示:


锁表并在此实行备份,备份完后解锁

wKiom1f8S2OBF-NvAAAkQmCh4mo358.png

wKioL1f8S2ThhkydAABrEdLlN5M190.png



本文转自chengong1013 51CTO博客,原文链接:http://blog.51cto.com/purify/1860516,如需转载请自行联系原作者

相关实践学习
日志服务之使用Nginx模式采集日志
本文介绍如何通过日志服务控制台创建Nginx模式的Logtail配置快速采集Nginx日志并进行多维度分析。
相关文章
|
7月前
|
SQL 关系型数据库 MySQL
MySQL数据库,可以使用二进制日志(binary log)进行时间点恢复
对于MySQL数据库,可以使用二进制日志(binary log)进行时间点恢复。二进制日志是MySQL中记录所有数据库更改操作的日志文件。要进行时间点恢复,您需要执行以下步骤: 1. 确保MySQL配置文件中启用了二进制日志功能。在配置文件(通常是my.cnf或my.ini)中找到以下行,并确保没有被注释掉: Copy code log_bin = /path/to/binary/log/file 2. 在需要进行恢复的时间点之前创建一个数据库备份。这将作为恢复的基准。 3. 找到您要恢复到的时间点的二进制日志文件和位置。可以通过执行以下命令来查看当前的二进制日志文件和位
613 1
|
6月前
|
SQL 存储 关系型数据库
|
6月前
|
存储 关系型数据库 MySQL
|
6月前
|
存储 SQL 关系型数据库
|
2月前
|
SQL 存储 关系型数据库
Mysql主从同步 清理二进制日志的技巧
Mysql主从同步 清理二进制日志的技巧
31 1
|
5月前
|
存储 关系型数据库 MySQL
|
6月前
|
SQL 运维 关系型数据库
|
6月前
|
SQL 监控 关系型数据库
解密MySQL二进制日志:深度探究mysqlbinlog工具
解密MySQL二进制日志:深度探究mysqlbinlog工具
918 3
|
7月前
|
SQL 存储 关系型数据库
②⑩ 【MySQL Log】详解MySQL日志:错误日志、二进制日志、查询日志、慢查询日志
②⑩ 【MySQL Log】详解MySQL日志:错误日志、二进制日志、查询日志、慢查询日志
146 0
|
7月前
|
SQL 存储 关系型数据库
认真学习MySQL中的二进制日志(binlog)与中继日志(Relay log)
认真学习MySQL中的二进制日志(binlog)与中继日志(Relay log)
303 0

推荐镜像

更多