mariadb二进制日志和备份恢复

一、日志及其说明

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

图示：

显示二进制日志文件

二进制文件格式如下：

<code>[root@chen data]</code><code># mysqlbinlog --stop-position=1451 '/mydata/binlog/master-bin.000001'</code>

<code>/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;</code>

<code>/*!40019 SET @@session.max_insert_delayed_threads=0*/;</code>

<code>/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;</code>

<code>DELIMITER /*!*/;</code>

<code>#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</code>

<code>ROLLBACK/*!*/;</code>

<code>BINLOG '</code>

<code>XY3sVw8BAAAA8QAAAPUAAAAAAAQANS41LjQ0LU1hcmlhREItbG9nAAAAAAAAAAAAAAAAAAAAAAAA</code>

<code>AAAAAAAAAAAAAAAAAABdjexXEzgNAAgAEgAEBAQEEgAA2QAEGggAAAAICAgCAAAAAAAAAAAAAAAA</code>

<code>AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA</code>

<code>AAAAAAAAAAAAZwK5ww==</code>

<code>#160929 13:39:36 server id 1 end_log_pos 332 Querythread_id=4exec_time=0error_code=0</code>

<code>SET TIMESTAMP=1475127576/*!*/;</code>

<code>SET @@session.pseudo_thread_id=4/*!*/;</code>

<code>SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;</code>

<code>SET @@session.sql_mode=0/*!*/;</code>

<code>SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;</code>

<code>SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;</code>

<code>SET @@session.lc_time_names=0/*!*/;</code>

<code>SET @@session.collation_database=DEFAULT/*!*/;</code>

<code>create database testdb</code>

<code>#160929 13:40:40 server id 1 end_log_pos 454 Querythread_id=5exec_time=0error_code=0</code>

<code>use `testdb`/*!*/;</code>

<code>SET TIMESTAMP=1475127640/*!*/;</code>

<code>SET @@session.foreign_key_checks=0, @@session.unique_checks=0/*!*/;</code>

<code>SET @@session.sql_mode=524288/*!*/;</code>

<code>DROP TABLE IF EXISTS `students` /* generated by server */</code>

<code>#160929 13:40:40 server id 1 end_log_pos 752 Querythread_id=5exec_time=0error_code=0</code>

<code>CREATE TABLE `students` (</code>

<code> </code><code>`</code><code>id</code><code>` int(10) unsigned NOT NULL,</code>

<code> </code><code>`name` varchar(20) DEFAULT NULL,</code>

<code> </code><code>`age` tinyint(3) unsigned DEFAULT NULL,</code>

<code> </code><code>`gender` enum(</code><code>'f'</code><code>,</code><code>'m'</code><code>) DEFAULT NULL,</code>

<code> </code><code>PRIMARY KEY (`</code><code>id</code><code>`)</code>

<code>) ENGINE=InnoDB DEFAULT CHARSET=utf8</code>

<code>#160929 13:40:40 server id 1 end_log_pos 864 Querythread_id=5exec_time=0error_code=0</code>

<code>/*!40000 ALTER TABLE `students` DISABLE KEYS */</code>

<code>#160929 13:40:40 server id 1 end_log_pos 975 Querythread_id=5exec_time=0error_code=0</code>

<code>/*!40000 ALTER TABLE `students` ENABLE KEYS */</code>

<code>#160929 13:40:40 server id 1 end_log_pos 1091 Querythread_id=5exec_time=0error_code=0</code>

<code>DROP TABLE IF EXISTS `t2` /* generated by server */</code>

<code>#160929 13:40:40 server id 1 end_log_pos 1240 Querythread_id=5exec_time=0error_code=0</code>

<code>CREATE TABLE `t2` (</code>

<code> </code><code>`</code><code>id</code><code>` int(11) DEFAULT NULL</code>

<code>#160929 13:40:40 server id 1 end_log_pos 1346 Querythread_id=5exec_time=0error_code=0</code>

<code>/*!40000 ALTER TABLE `t2` DISABLE KEYS */</code>

<code>#160929 13:40:40 server id 1 end_log_pos 1451 Querythread_id=5exec_time=0error_code=0</code>

<code>/*!40000 ALTER TABLE `t2` ENABLE KEYS */</code>

<code>DELIMITER ;</code>

<code>ROLLBACK /* added by mysqlbinlog */;</code>

<code>/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;</code>

<code>/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;</code>

上述格式说明：

事件发生的日期和时间

服务器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; #可使得不记录二进制日志文件

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

所有与日志相关变量

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

三、数据备份及其恢复

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

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

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

备份多个数据库及其恢复

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

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

--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

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

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

mariadb二进制日志和备份恢复

继续阅读

Ubuntu16.04安装Apache+MySQL+PHP1. 安装Apache2. 安装MySQL3. 安装PHP4. 安装phpMyAdmin

Linxu常用命令技巧汇总

ERROR 1 (HY000): Can't create/write to file '/tmp/#sql_4188_1.MYI' (Errcode: 28)

艰难安装LDAP,SSL认证

《Linux命令行与Shell脚本编程大全第2版.布卢姆》pdf

MySQL的4种隔离级别？出现问题

XX系统实施过程问题总结

无组件上传图片到数据库中，最完整解决方案

【MySQL数据库】数据库索引事务1.索引2.事务

neo4j之cypher使用文档

NOSQL安全攻击

mybatis_入门程序Mybatis入门

mysql使用source命令导入.sql文件

登录plsql 报错 the account is locked --用户被锁

sqlServer根据经纬查距离

SequoiaDB巨杉数据库C++驱动概述