mysql解析二进制日志_MySQL二进制日志详解及Mysql备份和恢复

二进制日志:“修改”

也叫复制日志

position:位置

time:时间

滚动:

1、大小

2、时间

二进制日志的功用:

即时点恢复:

复制:

mysql>SHOW MASTER STAUS;

mysql>FLUSH LOGS;

mysql>SHOW BINARY LOGS;

mysql>SHOW BINLOG EVENTS IN 'log_file';

#mysqlbinlog

--start-time

--stop-time

--start-position

--stop-position

server-id :服务器身份标识

MYSQL记录二进制日志的格式:

基于语句:statement

基于行: row

UPDATE tb1 SET salary=salary+1000

混合模式:mixed

二进制日志文件内容格式:

时间发生的日期和时间

服务器ID

时间的结束位置

事件的类型

原服务器生成此时间的线程ID

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

错误代码

事件内容

事件位置,相当于下一事件的开始位置

服务器参数:

log-bin = {ON|OFF},还可以是个文件路径

log-bin-trust-function-creators

sql_log_bin = {ON|OFF}

sync_binlog

binlog_format = {statement|row|mixed}

max_binlog_cache_size =

二进制日志缓冲空间大小,仅用于缓冲事务类的语句:

max_binlog_stmt_cache_size

max_binlog_size

二进制日志文件上限

建议:切勿将二进制日志与数据文件放在同一设备上:

中继日志:

relay_log_purge={ON|OFF}

是否自动清理不再需要的中继日志

备份和恢复:

1、灾难恢复:

2、审计:

3、测试:

备份:目的用于恢复:对于备份数据做恢复测试:

备份类型:

根据备份时,数据库服务器是否在线:

冷备:cold backup

温备:warm backup

热备:hot backup

根据备份的数据集:

完全备份:full

部分备份:partial backup

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

物理备份:直接复制(归档)数据文件的备份方式:physical backup

逻辑备份:把数据从库中提出来保存为文本文件:logical backup

mysqldump

根据备份时是备份整个数据还是仅备份变化的数据:

完全备份:full backup

增量备份:incremental backup

差异备份:different backup

备份策略:

选择备份方式

选择备份时间

考虑到恢复成本

恢复时长

备份成本:

锁时间

备份时长

备份负载

备份对象:

数据

配置文件:

代码:存储过程,存储函数,触发器

OS相关的配置文件,如crontab配置计划及相关的脚本

跟复制相关的配置:

二进制日志文件

备份工具:

mysqldump:逻辑备份工具

InnoDB热备、MyISAM温备、Aria温备

备份和恢复过程比较慢

mysqldumper:多线程备份工具

很难实现差异或增量备份:

lvm-snapshot:

接近于热备的工具:因为要先请求全局锁,而后创建快照,并在创建快照完成后释放全局锁:

使用cp、tar等工具进行物理备份:

备份和恢复速度较快:

很难实现增量备份,并且请求全局需要等待一段时间,在繁忙的服务器上油漆如此:

SELECT   clause      INTO OUTFILE   '/path/to/somefile'

LOAD DATA INFILE '/path/from/somefile'

部分备份工具,不会备份关系定义,仅备份表中的数据:

逻辑备份工具,快于mysqldump

Innobase:商业备份工具,innobackup

Xtrabackup:由Percona提供的开源备份工具

InnoDB热备,增量备份:

MyISAM温备,不支持增量:

物理备份,速度快:

mysqlhotcopy:几乎冷备

mysqldump:

mysqldump [option] [tb1_name...]

备份单个库:mysqldump [option] db_name

恢复时:如果目标不存在,需要事先手动创建

--all-databases: 备份所有库

--databases db1 db2        ...    备份前加锁

--lock-all-tables:请求锁定所有表之后再备份,对MyISAM、InnoDB、Aria做温备

--single-transaction:能够对InnoDB存储引擎实现热备:

备份代码:

--events:备份事件调度器代码

--routines:备份存储过程和存储函数

--triggers:备份触发器

备份时滚动日志:

--flush-logs:备份前、请求到锁之后滚动日志

复制时的同步位置标记:

--master-data=【0|1|2】

0:不记录

1:记录为CHANGE MASTER语句

2:记录为注释的CHANGE MASTER语句

使用mysqldump备份:

请求锁:  --lock-all-tables或使用--single-trasaction进行innodb热备:

滚动日志: --flush-logs

选定要备份的库: --databases

记录二进制日志文件及位置

恢复:

建议:关闭二进制日志,关闭其它用户连接:

备份策略:基于mysqldump

备份: mysqldump+二进制日志文件:

周日做一次完全备份:备份的同事滚动日志

周一至周六:备份二进制日志

恢复:

完全备份+二进制日志文件中至此刻的事件

对于MySQL配置文件,以及与MySQL相关的OS配置文件在每次修改后都应该直接进行备份:

lvm-snapshot:基于LVM快照的备份

1、事务日志跟数据文件必须在同一个卷上:

2、创建快照卷之前,要请求MySQL的全局锁,在快照创建完成之后释放锁:

3、请求全局锁之后,做一次日志滚动:做二进制日志文件及位置标记(手动进行):

步骤:

1、请求全局锁,并滚动日志

mysql>FLUSH TABLES WITH READ LOCK;

mysql> FLUSH LOGS;

2、做二进制日志文件及位置标记(手动进行);

#mysql -e 'show master status' > /path/to/somefile

3、创建快照卷

lvcreate -L -s -n -p r /path/to/some_lv

4、释放全局锁

msyql > UNLOCK TABLES;

5、卸载快照卷并备份

# cp

6、备份完成之后,删除快照卷

恢复:

1、二进制日志保存好:

提取备份之后所有事件至某sql脚本中;

2、还原数据,修改权限及属主属组等,并启动mysql

3、做即时还原

1、 查看所有的数据库

MariaDB [hellodb]> show databases;

+--------------------+

| Database           |

+--------------------+

| information_schema |

| hellodb            |

| mysql              |

| performance_schema |

| test               |

+--------------------+

5 rows in set (0.01 sec)

2、备份单个数据库,这里是备份hellodb,把hellodb备份到/tmp下命名为hellodb.sql

[root@localhost ~]# mysqldump hellodb > /tmp/hellodb.sql

3、查看刚才备份的数据库hellodb.sql

[root@localhost tmp]# cat hellodb.sql

-- MySQL dump 10.14  Distrib 5.5.41-MariaDB, for Linux (x86_64)

--

-- Host: localhost    Database: hellodb

-- ------------------------------------------------------

-- Server version    5.5.41-MariaDB

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;

/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;

/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;

/*!40101 SET NAMES utf8 */;

/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;

/*!40103 SET TIME_ZONE='+00:00' */;

/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;

/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;

/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;

/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--

-- Table structure for table `classes`

--

DROP TABLE IF EXISTS `classes`;

/*!40101 SET @saved_cs_client     = @@character_set_client */;

/*!40101 SET character_set_client = utf8 */;

CREATE TABLE `classes` (

`CLASSID` tinyint(4) NOT NULL,

`Class` varchar(30) NOT NULL,

`NumOfStu` tinyint(4) NOT NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/*!40101 SET character_set_client = @saved_cs_client */;

--

-- Dumping data for table `classes`

--

LOCK TABLES `classes` WRITE;

/*!40000 ALTER TABLE `classes` DISABLE KEYS */;

INSERT INTO `classes` VALUES (1,'Shaolin Pai',10),(2,'Emei Pai',7),(3,'QingCheng Pai',11),(4,'Wudang Pai',12),(5,'Riyue Shenjiao',31),(6,'Lianshan Pai',27),(7,'Ming Jiao',27),(8,'Xiaoyao Pai',15),(9,'Liangshan',22);

/*!40000 ALTER TABLE `classes` ENABLE KEYS */;

UNLOCK TABLES;

/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;

/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;

/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;

/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;

/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2017-03-26 15:56:56

4、备份指定的多个库,这里备份hellodb test 两个库,需要加--databases

[root@localhost tmp]# mysqldump --databases hellodb test > /tmp/test.sql

5、查看备份的库test.sql的库

[root@localhost tmp]# cat test.sql

-- MySQL dump 10.14  Distrib 5.5.41-MariaDB, for Linux (x86_64)

--

-- Host: localhost    Database: hellodb

-- ------------------------------------------------------

-- Server version    5.5.41-MariaDB

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;

/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;

/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;

/*!40101 SET NAMES utf8 */;

/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;

/*!40103 SET TIME_ZONE='+00:00' */;

/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;

/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;

/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;

/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--

-- Current Database: `hellodb`

--

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `hellodb` /*!40100 DEFAULT CHARACTER SET latin1 */;

USE `hellodb`;

--

-- Table structure for table `classes`

--

DROP TABLE IF EXISTS `classes`;

/*!40101 SET @saved_cs_client     = @@character_set_client */;

/*!40101 SET character_set_client = utf8 */;

CREATE TABLE `classes` (

`CLASSID` tinyint(4) NOT NULL,

`Class` varchar(30) NOT NULL,

`NumOfStu` tinyint(4) NOT NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/*!40101 SET character_set_client = @saved_cs_client */;

--

-- Dumping data for table `classes`

--

LOCK TABLES `classes` WRITE;

/*!40000 ALTER TABLE `classes` DISABLE KEYS */;

INSERT INTO `classes` VALUES (1,'Shaolin Pai',10),(2,'Emei Pai',7),(3,'QingCheng Pai',11),(4,'Wudang Pai',12),(5,'Riyue Shenjiao',31),(6,'Lianshan Pai',27),(7,'Ming Jiao',27),(8,'Xiaoyao Pai',15),(9,'Liangshan',22);

/*!40000 ALTER TABLE `classes` ENABLE KEYS */;

UNLOCK TABLES;

--

-- Current Database: `test`

--

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `test` /*!40100 DEFAULT CHARACTER SET latin1 */;

USE `test`;

/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;

/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;

/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;

/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;

/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2017-03-26 16:01:30

6、事实上,真正的现网操作需要先锁定所有的表,再进行备份。需要使用mysqldump 后面跟上--lock-all-tables参数进行备份。

[root@localhost tmp]# mysqldump --databases hellodb --lock-all-tables  --flush-logs > /tmp/hellodb.sql

7、在mysql库中演示一个完整的备份,手动备份

(1)、对所有表加锁,手动请求全局锁

MariaDB [(none)]> FLUSH TABLES WITH READ LOCK;

Query OK, 0 rows affected (0.00 sec)

(2)、刷新日志

MariaDB [(none)]> FLUSH LOGS;

Query OK, 0 rows affected (0.02 sec)

(3)、查看二进制文件位置

MariaDB [(none)]> SHOW MASTER STATUS;

+------------------+----------+--------------+------------------+

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+------------------+----------+--------------+------------------+

| mysql-bin.000002 |      245 |              |                  |

+------------------+----------+--------------+------------------+

1 row in set (0.07 sec)

(4)重新备份hellodb数据库,把数据库备份到/tmp下并命名为hellodb1.sql

[root@localhost tmp]# mysqldump --databases hellodb > /tmp/hellodb1.sql

(5)备份完释放表锁定

MariaDB [(none)]> UNLOCK TABLES;

Query OK, 0 rows affected (0.01 sec)

8、对Innodb的库进行热备

[root@localhost tmp]# mysqldump --databases hellodb --single-transaction --flush-logs > /tmp/hello2.sql

阅读(857) | 评论(0) | 转发(0) |

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
©️2022 CSDN 皮肤主题:游动-白 设计师:我叫白小胖 返回首页
评论

打赏作者

陆帅全

你的鼓励将是我创作的最大动力

¥2 ¥4 ¥6 ¥10 ¥20
输入1-500的整数
余额支付 (余额:-- )
扫码支付
扫码支付:¥2
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值