mysql迁移 脏日志_2019-06-25 Day09～Mysql~日志管理及恢复

1. 上节遗留问题

RC模式: 可以屏蔽脏读,但是会出现不可重复读和幻读

2.重点参数 innodb_flush_log_at_trx_commit

作用： 控制redo buffer 刷写磁盘的策略

0： redo buffer -----> 每秒 -----> os buffer -----> 每秒 -----> 磁盘

如果 出现宕机，有可能丢失一秒的事务。

如果你的业务，对数据丢失有一定的容忍度，可以使用(类似zabbix)

1： redo buffer -----> commit -----> os buffer -----> commit -----> 磁盘

#工作中重点使用

2： redo buffer -----> commit -----> os buffer -----> 每秒 -----> 磁盘

innodb_flush_method=O_DIRECT

作用:

控制 redo buffer 和 data buffer 刷磁盘时是否使用 os buffer 数据刷盘,不使用 os buffer;；日志刷盘,使用 os buffer

innodb_buffer_pool_size 50-80%

===================================

1、日志管理

1.1 排错

1.1.1 错误日志

现位置：

image.png

默认位置：

DATADIR/hostname.err

配置方式：

vim /etc/my.cnf

log_error=/data/mysql/data/mysql.log

使用方法：

查看 [ ERROR ] 上下文

1.2 数据恢复

binlog(二进制日志)

1.2.1 作用：

数据恢复

主从复制

1.2.2 如何配置？

log_bin

1. 开关

2. 设定存放位置

server_id

1. 5.6 中不需要

2. 5.7 使用以上参数时必须加上server_id

注意：

生产要求！！！

日志要和数据分开存放！

使用不同的物理磁盘进行存放！

配置二进制日志：

mkdir /data/binlog -p #创建存放目录

chown -R mysql. /data #授权

vim /etc/my.cnf #编辑配置文件

log_bin=/data/mysql/data/mysql-bin

/etc/init.d/mysqld restart # 重启

image.png

1.2.3 如何查看配置

mysql> show variables like '%log_bin%';

image.png

1.2.4 binlog 记录了什么？

###1. 大面上说明：

记录了数据库中所有变更类的操作

DDL

DCL

DML

###2. 详细的说明

(1)

对于DDL和DCL语句，记录发生过的语句

(2)DML(IUD)

前提：已经提交的事务IUD

关于记录格式：

ROW ：RBR 行记录模式 ，记录的是行的变化

STATEMENT ：SBR 语句记录模式，记录操作语句本身

MIXED ：MBR 混合记录模式

电话面试的题目：

delete from city where id>1000；

记录方式：

RBR,逐行记录日志,日志量很大,可读性差。但是够严谨,不会出现记录错误

SBR,只记录语句本身, 日志量很少,可读性较强。对于函数类的操作,将来恢复时会有错误

5.7 版本，默认是RBR格式，也是企业建议的模式。

二进制日志记录格式查看

mysql> select @@binlog_format;

image.png

1.2.5 二进制日志事件(event)

简介

二进制日志的最小记录单元

对于DDL, DCL,一个语句就是一个event

对于DML语句来讲:只记录已提交的事务。

例如以下列子,就被分为了4个event

position号码(截取日志使用)

beqin; 120 - 340

DML1 340 - 460

DML2 460 - 550

commit; 550 - 760

### event的组成

三部分构成:

(1)事件的开始标识

(2)事件内容

(3)事件的结束标识

Position:

开始标识: at 194

结束标识: end_log_pos 254 1

194? 254?

某个事件在binlog中的相对位置号

位置号的作用是什么?

为了方便我们截取事件

1.2.6 二进制日志的查看

image.png

查看正在使用的二进制日志

mysql> show binary logs;

image.png

log_name：目前Mysql存在的二进制日志名字

file_size：目前Mysql用到哪个polation号

mysql> show master status ;

image.png

1. 2. 7 查看二进制文件内容*****

(1)查看二进制日志事件

确认当前在用的binlog

mysql> show master status ;

image.png

查看binlog事件

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

image.png

注释：每一行都是一个事件

每列说明：

Log_name ：日志名

Pos ：事件开始的position *****

Event_type ：事件类型

Server_id ：发生在那台机器上的事件

End_log_pos ：事件结束的位置号 *****

Info ：事件内容 *****

(2)查看二进制日志内容

平看日志信息：

mysqlbinlog /data/binlog/mysql-bin.000001 |grep -v "SET"

详细查看日志信息

mysqlbinlog --base64-output=decode-rows -vvv /data/binlog/mysql-bin.000001

1.2.8 基于二进制日志数据恢复案例

如何按需截取日志

1. 基于position号的截取

--start-position=###

--stop-position=###

截取二进制日志核心在于找起点和终点

image.png

截取日志

[root@db01 ~]# mysqlbinlog --start-position=219 --stop-position=322 /data/binlog/mysql-bin.000002 >/tmp/bin.sql

恢复：

mysql> drop database oldboy1;

mysql> show databases;

mysql> set sql_log_bin=0;

mysql>source /tmp/bin.sql

2. 基于时间点的截取(需求不大，了解即可)

--start-datetime

--stop-datetime

for example: 2004-12-25 11:25:56

《按需恢复详解》

案例: 使用binlog日志进行数据恢复

模拟:

#1.

create database binlog charset utf8mb4;

#2.

use binlog;

create table t1(id int);

#3.

insert into t1 values(1);

commit;

insert into t1 values(2);

commit;

insert into t1 values(3);

commit;

#4.

drop database binlog;

恢复：

1.找到起点和终点

mysql> show master status;

2.查看事件

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

3. 截取日志

mysqlbinlog --start-position=259 --stop-position=1377 /data/binlog/mysql-bin.000003 >/tmp/bin.sql

4. 进行恢复

mysql> set sql_log_bin=0;

mysql> source /tmp/bin.sql

5. 验证数据

mysql> show databases;

1.2.9 开启GTID功能的二进制日志管理

什么是GTID

全局事务编号

开启GTID

vim /etc/my.cnf

gtid-mode=on

enforce-gtid-consistency=true

/etc/init.d/mysqld restart

查看本机GTID信息

环境模拟：

mysql> create database gg;

mysql> show master status;

mysql> use gg;

mysql> create table t1 (id int);

mysql> insert into t1 values(1);

mysql> commit;

mysql> insert into t1 values(2);

mysql> commit;

mysql> insert into t1 values(3);

mysql> commit;

drop database gg;

基于GTID截取二进制日志

mysqlbinlog --include-gtids='1357a112-8d94-11e9-8bda-000c29c21dbb:7-11' /data/binlog/mysql-bin.000004 >/tmp/gtid.sql

注意：

以上日志截取出来的日志不能直接恢复！

gtid的幂等性

正确的截取方式：

mysqlbinlog --skip-gtids --include-gtids='1357a112-8d94-11e9-8bda-000c29c21dbb:7-11' /data/binlog/mysql-bin.000004 >/tmp/gtid.sql

恢复

mysql> set sql_log_bin=0;

mysql> source /tmp/gtid.sql

跳过某些gtid不截取(不连续取号或排除)

mysqlbinlog --skip-gtids --include-gtids='ee956c61-9653-11e9-8518-000c29099eb6:1-5' --exclude-gtids='ee956c61-9653-11e9-8518-000c29099eb6:2,ee956c61-9653-11e9-8518-000c29099eb6:4' /data/binlog/mysql-bin.000003 >/tmp/gtids.sql

1.2.10 二进制日志其他操作

(1)临时关闭

set sql_log_bin=0;

说明：

临时关闭二进制日志记录，退出mysql窗口可以恢复

做数据恢复之前，使用以上参数。

(2)自动清理

参数：

mysql> select @@expire_logs_days;

设置依据？

至少是一个全备周期 +1，企业建议至少2个全被周期 +1

临时设置：重启失效

mysql> set global expire_logs_days=8';

永久设置：重启生效

vim /etc/my.cnf

expire_logs_days=8

(3)手工清理

PURGE BINARY LOGS BEFORE now() - INTERVAL 3 day;

PURGE BINARY LOGS TO 'mysql-bin.000010';

注意:不要手工rm binlog文件

1. my.cnf binlog关闭掉,启动数据库

2. 数据库关闭,开启binlog,启动数据库

删除所有binlog，并从000001开始重新记录日志

注意，禁用：

删除所有binlog，从000001开始删除(危险！！！！！)

mysql>reset master;

(4)日志滚动

mysql> flush logs;

mysql> show binary logs;

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

| Log_name | File_size |

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

| mysql-bin.000003 | 1498 |

| mysql-bin.000004 | 1451 |

| mysql-bin.000005 | 194 |

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

mysql> show variables like '%max_binlog_size%';

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

| Variable_name | Value |

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

| max_binlog_size | 1073741824 |

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

备份加一些参数，会触发滚动日志

1. 3 优化相关日志— slowlog

1.3.1 作用

记录慢SQL语句的日志，定位低效SQL语句的工具日志。

1.3.2 开启慢日志，默认没有开启

mysql> select @@slow_query_log;

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

| @@slow_query_log |

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

| 0 |

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

1 row in set (0.00 sec)

mysql> select @@slow_query_log_file;

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

| @@slow_query_log_file |

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

| /data/mysql/data/db01-slow.log |

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

1 row in set (0.00 sec)

mysql> select @@long_query_time;

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

| @@long_query_time |

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

| 10.000000 |

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

1 row in set (0.00 sec)

mysql> select @@log_queries_not_using_indexes;

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

| @@log_queries_not_using_indexes |

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

| 0 |

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

1 row in set (0.00 sec)

最终设置：

slow_query_log=1

slow_query_log_file=/data/mysql/slow.log

long_query_time=0.1

log_queries_not_using_indexes

1.3.3 mysqldumpslow 分析慢日志

mysqldumpslow -s c -t 10 /data/mysql/slow.log

image.png

备份恢复与迁移 ******

1、DBA、运维在数据库备份恢复方面的职责

1.1 设计备份策略

全备

增量

时间

自动

1.2 日常备份检查!

备份存在性—检查备份路径下是否有真实数据

定期检查备份空间是否充足

1.3 定期恢复演练(测试库)

一季度 或者 半年

1.4 故障恢复

通过现有备份,能够将数据库恢复到故障之前的时间点.

1.5 迁移

停机时间

回退方案

2、备份类型

2.1 热备

在数据库正常业务时，备份数据,并且能够一致性恢复(只能是innodb)

对业务影响非常小

2.2 温备

锁表备份，只能查询不能修改(myisam)

影响到写入操作

2.3 冷备

关闭数据库业务,数据库没有任何变更的情况下,进行备份数据.业务停止

3、备份方式及工具介绍

3.1 逻辑备份工具

基于SQL语句进行备份

mysqldump(MDP) *****

mysqlbinlog *****

3.2 物理备份工具

基于磁盘数据文件备份

xtrabackup (XBK) : percona第三方 *****

MysQL Enterprise Backup (MEB,企业版收费的)

4、逻辑备份和物理备份的比较

4.1 mysqldump (MDP)

优点:

不需要下载安装.

备份出来的是SQL,文本格式,可读性高,便于备份处理

压缩比较高,节省备份的磁盘空间

缺点

依赖于数据库引擎,需要从磁盘把数据读出

然后转换成SQL进行转储,比较耗费资源,数据量大的话效率较低

建议:

100G以内的数据量级,可以使用mysqldump

超过TB以上,我们也可能选择的是mysqldump,配合分布式的系统

1EB =1024 PB =1000000 TE

4.2 xtrabackup (XBK)

优点:

类似于直接cp数据文件,不需要管逻辑结构,相对来说性能较高

缺点:

可读性差

压缩比低,需要更多磁盘空间

建议:

100GKTB

5、备份策略

备份方式:

全备：全库备份,备份所有数据

增量：备份变化的数据

逻辑备份=mysqldump+mysqlbinlog

物理备份=xtrabackup_full+xtrabackup_incr+binlog或者xtrabackup_full+binlog

备份周期:

根据数据量设计备份周期

比如：周日全备,周1 - 周6增量

其他：通过主从复制备份

6、逻辑备份工具-mysqldump

6. 1 客户端通用命令，和链接有关

-u

-p

-S

-h

-P

本地备份连接方式：

mysqldump -uroot -pxxx -S /tmp/myql.sock

远程备份连接方式：

mysqldump -uroot -pxxx -h xxx -P xxx

6.2 基本备份参数

-A 全库备份

例子：实现全库备份

mkdir -p /data/backup

mysqldump -uroot -p123 -A -S /tmp/mysql.sock >/data/backup/full.sql

-B 备份单个库或多个库数据

例子：备份oldboy和world数据库

mysqldump -uroot -p123 -B world oldboy -S /tmp/mysql.sock >/data/backup/db.sql

库名 表名 ： 备份某个库下的1张或多张表

例子：备份world数据库下的city和country表

mysqldump -uroot -p123 world city country -S /tmp/mysql.sock >/data/backup/tab.sql

注意：

此种方法，只会备份建表 + 插入语句

所以恢复前需要把库建好，而且要use到库中。

6. 3 必加参数 (1)

-R

在备份时，同时备份存储过程和函数，如果没有会自动忽略

-E

在备份时，同时备份EVENT，如果没有会自动忽略

--triggers

在备份时，同时备份触发器，如果没有会自动忽略

6. 4 必加参数(2)重要参数

--master-data=2

记录备份开始时 position号，可以作为将来做日志截取的起点。

功能：

记录备份时的position

自动锁表

配合--single-transaction，减少锁的(innodb引擎)

--single-transaction

对于innodb的表，实现快照备份，不锁表

6.5 其他参数(不是必加，了解即可)

-F

--set-gtid-purged=auto

--max-allowed-packet