Linux学习-MySQL-week03

日志管理
1.1 错误日志
1.1.1作用
记录MySQL工作过程中，状态，警告，报错。。。。
数据库启动后自动打开的，存放在datadir/hostname.err
1.1.2 配置
vim /etc/my.cnf
log_error=/data/mysql/data/mysql.log
touch /data/mysql/data/mysql.log
chown -R mysql.mysql /data/*
/etc/init.d/mysqld restart
1.1.3 如何查看
关注[ERROR]日志行。观察上下文。

1.2 二进制日志
1.2.1 作用
以event的形式，记录MySQL中发生过的所有变更的语句。
可以提供数据恢复，主从复制

1.2.2 配置
log_bin=/data/binlog/mysql-bin
binlog_format=row
sync_binlog=1
expire_logs_days=30
server_id=6

mkdir -p /data/binlog
chown -R mysql.mysql /data/*

1.2.3 参数说明
（1）开关和文件名定制
log_bin=/data/binlog/mysql-bin

（2）日志格式
binlog_format=row/statement/mixed

DDL ,DCL : 记录语句本身（statement模式）。一条DDL语句就是一个event。
insert,update, delete,会受到binlog_format影响，所以有三种记录格式
row （RBR） : 数据行的变化。记录严谨准确。日志量大。可读性差。
statement（SBR）：记录语句本身。记录不够严谨。日志量小。可读性强。
mixed （MBR）：混合

（3）日志过期时间
expire_logs_days=30
设置依据：
至少是一轮 2*全备周期+1

（4） binlog刷盘策略
sync_binlog=1
每次事务提交，binlog立即写入磁盘文件。

mysql> flush logs;
Query OK, 0 rows affected (0.01 sec)

mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 201 |
| mysql-bin.000002 | 201 |
| mysql-bin.000003 | 201 |
| mysql-bin.000004 | 154 |
+------------------+-----------+

mysql> show binlog events in 'mysql-bin.000004';
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| mysql-bin.000004 | 4 | Format_desc | 6 | 123 | Server ver: 5.7.26-log, Binlog ver: 4 |
| mysql-bin.000004 | 123 | Previous_gtids | 6 | 154 | |
| mysql-bin.000004 | 154 | Anonymous_Gtid | 6 | 219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000004 | 219 | Query | 6 | 321 | create database a charset utf8mb4 |
| mysql-bin.000004 | 321 | Anonymous_Gtid | 6 | 386 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000004 | 386 | Query | 6 | 488 | create database b charset utf8mb4 |
| mysql-bin.000004 | 488 | Anonymous_Gtid | 6 | 553 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000004 | 553 | Query | 6 | 655 | create database c charset utf8mb4 |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+

（3）查看binlog内容
[root@db01 binlog]# mysqlbinlog --base64-output=decode-rows -v mysql-bin.000004 |grep -v 'SET'

1.2.5 截取日志
[root@db01 binlog]# mysqlbinlog --start-position=xxxx --stop-position=xxxx mysql-bin.000004>/data/bin.sql

1.2.6 故障案例模拟
mysql> create database binlog charset utf8mb4;
mysql> use binlog
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;

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

截取起点：
| mysql-bin.000004 | 1371 | Query | 6 | 1488 | create database binlog charset utf8mb4 |

截取终点：

| mysql-bin.000004 | 2490 | Query | 6 | 2588 | drop database binlog

mysqlbinlog --start-position=1371 --stop-position=2490 mysql-bin.000004>/data/bin.sql

mysql> set sql_log_bin=0;
mysql> source /data/bin.sql
mysql> set sql_log_bin=1;

实际情况：

截取日志有别的库或表
mysqlbinlog -d binlog --start-position=1371 --stop-position=2490 mysql-bin.000004>/data/bin.sql

或者借助于临时库，将备份和日志回放，然后导出故障数据，倒回到生产。

误删除的binlog库是3年前就有了。有什么恢复思路？
全备+binlog截取

1.2.7 日志滚动策略
flush logs ;
select @@max_binlog_size;
+-------------------+
| @@max_binlog_size |
+-------------------+
| 1073741824 |
+-------------------+
重启数据库会自动滚动
备份时加了参数，自动滚动

1.2.8 日志清理
expire_logs_days=30

mysql> PURGE BINARY LOGS TO 'mysql-bin.000010';
Query OK, 0 rows affected (0.01 sec)

mysql> reset master;

1.3 慢日志
1.3.1 作用
记录MySQL工作过程中，运行较慢的语句
帮助我们定义TOP 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)

[root@db01 data]# mysqldumpslow -s c -t 5 /data/mysql/data/db01-slow.log

备份恢复
2.1 mysqldump
2.2.1 介绍
MySQL自带工具。
逻辑备份工具： SQL语句备份（create database create table inser into）
优点：文本形式可读性比较高易于处理压缩比较高跨平台性好
缺点：备份逻辑复杂，时间较长
比较适合：小数据量

2.2.2 使用
连接参数：
-u
-p
-h
-P
-S

备份参数：
-A 全库
[root@db01 data]# mysqldump -uroot -p123456 -A >/data/full.sql

-B 单库或多库备份
[root@db01 data]# mysqldump -uroot -p123456 -B test world >/data/db.sql

单表或多表备份
[root@db01 data]# mysqldump -uroot -p123456 test t100w t200w >/data/t100w.sql
说明：单表，多表备份，需要恢复时，建立好库，并use进去。

--master-data=2
（1）记录备份时刻的binlog位置点，作为恢复数据时日志截取起点。
（2）自动锁表

--single-transaction
开启快照备份（InnoDB表）

--triggers
-R
-E

--max_allowed_packet=64M

完整备份语句：
[root@db01 data]# mysqldump -uroot -p123456 -A --master-data=2 --single-transaction --triggers -R -E --max_allowed-packet=64M |gzip >/data/full_date +%F.gz

-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=154;

2.2.3 故障演练
(1) 模拟数据
create database mdp charset utf8mb4;
use mdp;
create table t1(id int);
insert into t1 values(1);
commit;
insert into t1 values(2);
commit;
insert into t1 values(3);
commit;
(2) 模拟全备
mysqldump -uroot -p123456 -A --master-data=2 --single-transaction --triggers -R -E --max_allowed-packet=64M |gzip >/data/full_date +%F.gz
(3) 模拟新的数据变化
use mdp;
create table t2(id int);
insert into t2 values(1);
commit;
insert into t2 values(2);
commit;
insert into t2 values(3);
commit;

(4) 模拟破坏
mysql> drop database mdp;

(5) 恢复数据
检查全备：
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=1240;

截取二进制日志：
起点： mysql-bin.000002, MASTER_LOG_POS=1240
终点：| mysql-bin.000002 | 2218 | Query | 6 | 2307 | drop database mdp

恢复数据：
set sql_log_bin=0 ;
source /data/full_2020-01-12;
source /data/bin.sql；
set sql_log_bin=1;

2.2 xtrabackup 物理备份工具
2.2.1 介绍
percona 公司研发的开源的MySQL物理备份的工具。

2.2.2 安装
[root@db01 data]# yum install -y percona-xtrabackup-24-2.4.12-1.el7.x86_64.rpm

2.2.3 备份逻辑
1、 checkpoint ----》将已提交的内存脏页刷新到磁盘，记录LSN号
2、拷贝InnoDB表的数据文件，ibdata ，ibd，frm。
3、拷贝过程，自动截取redo中新的变化日志，直到备份结束。
4、 FTWRL（全局读锁），Flush table with read lock。
5、拷贝非InnoDB表
6、解锁
7、再次记录LSN

2.2.4 工具使用
[client]
socket=/tmp/mysql.sock

[root@db01 data]# mkdir -p /data/backup

(1) 全备
[root@db01 full_2020-01-12]# innobackupex --user=root --password=123456 --no-timestamp /data/backup/full_date +%F

模拟损坏数据，全备恢复：

Prepared backup

--apply-log : undo 和 redo 应用
innobackupex --apply-log /data/backup/full_2020-01-12

Copyback

[root@db01 full_2020-01-12]# cp -a * /data/mysql/data/
[root@db01 full_2020-01-12]# chown -R mysql.mysql /data

启动数据库

(1) 增量备份

周日发起全备

[root@db01 backup]# innobackupex --user=root --password=123456 --no-timestamp /data/backup/full_date +%F

模拟周一白天数据变化

create database xbk charset utf8mb4;
use xbk;
create table t1(id int);
insert into t1 values(1);
commit;
insert into t1 values(2);
commit;
insert into t1 values(3);
commit;

周一晚上，增量备份

innobackupex --user=root --password=123456 --no-timestamp --incremental --incremental-basedir=/data/backup/full_2020-01-12 /data/backup/inc1

模拟周二白天数据变化

use xbk;
create table t2(id int);
insert into t2 values(1);
commit;
insert into t2 values(2);
commit;
insert into t2 values(3);
commit;

周二晚上，增量备份

innobackupex --user=root --password=123456 --no-timestamp --incremental --incremental-basedir=/data/backup/inc1 /data/backup/inc2

模拟周三白天数据变化

use xbk;
create table t3 (id int);
insert into t3 values(1);
commit;
insert into t3 values(2);
commit;
insert into t3 values(3);
commit;

模拟数据库故障，使用xtrabackup full+inc+binlog实现故障恢复
[root@db01 backup]# pkill mysqld
[root@db01 data]# \rm -rf /data/mysql/data/*

检查所有备份和日志

准备并合并增量到全备。

(1) 基础全备的准备
innobackupex --apply-log --redo-only /data/backup/full_2020-01-12

(2) 合并inc1 到 full，并且准备
[root@db01 data]# innobackupex --apply-log --redo-only --incremental-dir=/data/backup/inc1 /data/backup/full_2020-01-12/

(3) 合并inc2 到 full ，并且准备
innobackupex --apply-log --incremental-dir=/data/backup/inc2 /data/backup/full_2020-01-12/

(4) 再次检查full备份的状态
innobackupex --apply-log /data/backup/full_2020-01-12

(5) 恢复数据
[root@db01 data]# innobackupex --copy-back /data/backup/full_2020-01-12/
[root@db01 data]# chown -R mysql.mysql /data/

(6) binlog截取
起点:
[root@db01 inc2]# cat xtrabackup_binlog_info
mysql-bin.000003 2152

[root@db01 inc2]# mysqlbinlog --start-position=2152 /data/binlog/mysql-bin.000003 >/data/bin.sql

主从复制
3.1 介绍
2台以上的数据库实例，通过binlog实现自动同步数据。
3.2 前提（主从搭建过程）
(1) 2台以上的MySQL实例。每台机器都有一个唯一的编号（server_id）
(2) 主库实例。需要开启二进制日志
(3) 多实例之间，网络畅通。
(4) 主库要有专门的复制用户：repl@'10.0.0.%':123 , replication slave 专用权限。
(5) 备份恢复主库数据，恢复到从库。
(6) 通知从库(change master to) : ip port user password ,复制的起点
(7) 从库开启专用复制线程(start slave;): IO(请求binlog),SQL(回放日志)。
3.3 主从复制搭建
(1)节点准备
[root@db01 ~]# systemctl start mysqld3307
[root@db01 ~]# systemctl start mysqld3308
[root@db01 ~]# systemctl start mysqld3309
[root@db01 ~]# netstat -tulnp

[root@db01 ~]# mysql -S /tmp/mysql3307.sock -e "select @@server_id"
+-------------+
| @@server_id |
+-------------+
| 3307 |
+-------------+
[root@db01 ~]# mysql -S /tmp/mysql3308.sock -e "select @@server_id"
+-------------+
| @@server_id |
+-------------+
| 3308 |
+-------------+
[root@db01 ~]# mysql -S /tmp/mysql3309.sock -e "select @@server_id"
+-------------+
| @@server_id |
+-------------+
| 3309 |
+-------------+
(2) 检查主库二进制日志
[root@db01 ~]# mysql -S /tmp/mysql3307.sock -e "select @@log_bin"
+-----------+
| @@log_bin |
+-----------+
| 1 |
+-----------+

(3) 主库建用户
mysql -S /tmp/mysql3307.sock -e "grant replication slave on . to repl@'10.0.0.%' identified by '123'"

(4) 全备主库数据
mysqldump -S /tmp/mysql3307.sock -A --master-data=2 --single-transaction --triggers -R -E --max_allowed-packet=64M >/tmp/full.sql

-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=444;

[root@db01 ~]# mysql -S /tmp/mysql3308.sock < /tmp/full.sql
[root@db01 ~]# mysql -S /tmp/mysql3309.sock < /tmp/full.sql

(5) 通知从库复制信息，并开启复制线程
mysql -S /tmp/mysql3308.sock
CHANGE MASTER TO
MASTER_HOST='10.0.0.51',
MASTER_USER='repl',
MASTER_PASSWORD='123',
MASTER_PORT=3307,
MASTER_LOG_FILE='mysql-bin.000003',
MASTER_LOG_POS=444,
MASTER_CONNECT_RETRY=10;
start slave;

mysql -S /tmp/mysql3309.sock
CHANGE MASTER TO
MASTER_HOST='10.0.0.51',
MASTER_USER='repl',
MASTER_PASSWORD='123',
MASTER_PORT=3307,
MASTER_LOG_FILE='mysql-bin.000003',
MASTER_LOG_POS=444,
MASTER_CONNECT_RETRY=10;
start slave;

(6) 状态查看
[root@db01 ~]# mysql -S /tmp/mysql3308.sock -e "show slave status \G" |grep Running:
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
[root@db01 ~]# mysql -S /tmp/mysql3309.sock -e "show slave status \G" |grep Running:
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
[root@db01 ~]#

3.4 主从复制工作原理

1.change master to 时，ip pot user password binlog position写入到master.info进行记录

start slave 时，从库会启动IO线程和SQL线程
3.IO_T，读取master.info信息，获取主库信息连接主库
主库会生成一个准备binlog DUMP线程，来响应从库
IO_T根据master.info记录的binlog文件名和position号，请求主库DUMP最新日志
DUMP线程检查主库的binlog日志，如果有新的，TP(传送)给从从库的IO_T
IO_T将收到的日志存储到了TCP/IP 缓存，立即返回ACK给主库，主库工作完成
8.IO_T将缓存中的数据，存储到relay-log日志文件,更新master.info文件binlog 文件名和postion，IO_T工作完成
9.SQL_T读取relay-log.info文件，获取到上次执行到的relay-log的位置，作为起点，回放relay-log
10.SQL_T回放完成之后，会更新relay-log.info文件。
relay-log会有自动清理的功能。
细节：
1.主库一旦有新的日志生成，会发送“信号”给binlog dump ，IO线程再请求

3.5 主从复制监控
主库：
show processlist;
show slave hosts;
从库：
mysql> show slave status \G

主库相关信息监控
Master_Host: 10.0.0.51
Master_User: repl
Master_Port: 3307
Master_Log_File: mysql-bin.000005
Read_Master_Log_Pos: 444

从库中继日志的应用状态
Relay_Log_File: db01-relay-bin.000002
Relay_Log_Pos: 485

从库复制线程有关的状态
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:

过滤复制有关的状态
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:

主从延时相关状态(非人为)
Seconds_Behind_Master: 0

延时从库有关的状态(人为)
SQL_Delay: 0
SQL_Remaining_Delay: NULL

GTID 复制有关的状态
Retrieved_Gid_Set:
Executed_Gtid_Set:
Auto_Position: 0

3.6 主从故障

3.6.1 监控
show slave status \G
从库复制线程有关的状态
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:

3.6.2 IO线程故障
（1）连接主库
连接信息有误
网络不通
防火墙
主库连接数上限

show slave status \G
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 1286

stop slave;
reset slave all;
CHANGE MASTER TO
MASTER_HOST='10.0.0.51',
MASTER_USER='repl',
MASTER_PASSWORD='123',
MASTER_PORT=3307,
MASTER_LOG_FILE='mysql-bin.000003',
MASTER_LOG_POS=1286,
MASTER_CONNECT_RETRY=10;
start slave;

（2）请求二进制日志
主库日志损坏，缺失。

reset master;

stop slave;
reset slave all;
CHANGE MASTER TO
MASTER_HOST='10.0.0.51',
MASTER_USER='repl',
MASTER_PASSWORD='123',
MASTER_PORT=3307,
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=154,
MASTER_CONNECT_RETRY=10;
start slave;

3.6.3 SQL线程故障

SQL语句为什么会执行失败。
从库发生写入：
新建的对象已存在
操作的对象不存在
约束冲突。
stop slave;
set global sql_slave_skip_counter = 1;

将同步指针向下移动一个，如果多次不同步，可以重复操作。

start slave;
SQL_MODE不兼容。
参数不兼容。

替代方案：
将从库只读。
read_only=ON
super_read_only=ON

自己扩展：
pt-table-checksum
pt-table-sync

3.7 主从延时问题

3.7.1主库原因：
(1) binlog写入不及时
解决方案： sync_binlog=1

(2) dump日志传送不及时
主库压力大
主库的dump是串行，但事务时并行。
解决方案：
开启GTID 后，可以实现并行传输binlog到从库。
但是还是怕频繁的大事务出现。
拆分大事务
长时间锁定。

3.7.2 从库原因
SQL线程串行工作的
使用5.7版本基于 Logical_clock的多SQL线程回放机制
减少大事务。

主从复制演变；
高可用
读写分离
分布式架构

人面猴
序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
沈念sama阅读 214,029评论 6赞 493
死咒
序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
沈念sama阅读 91,238评论 3赞 388
救了他两次的神仙让他今天三更去死
文/潘晓璐我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
开封第一讲书人阅读 159,576评论 0赞 349
道士缉凶录：失踪的卖姜人
文/不坏的土叔我叫张陵，是天一观的道长。经常有香客问我，道长，这世上最难降的妖魔是什么？我笑而不...
开封第一讲书人阅读 57,214评论 1赞 287
港岛之恋（遗憾婚礼）
正文为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
茶点故事阅读 66,324评论 6赞 386
恶毒庶女顶嫁案：这布局不是一般人想出来的
文/花漫我一把揭开白布。她就那样静静地躺着，像睡着了一般。火红的嫁衣衬着肌肤如雪。梳的纹丝不乱的头发上，一...
开封第一讲书人阅读 50,392评论 1赞 292
城市分裂传说
那天，我揣着相机与录音，去河边找鬼。笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。我是一名探鬼主播，决...
沈念sama阅读 39,416评论 3赞 412
双鸳鸯连环套：你想象不到人心有多黑
文/苍兰香墨我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
开封第一讲书人阅读 38,196评论 0赞 269
万荣杀人案实录
序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
沈念sama阅读 44,631评论 1赞 306
护林员之死
正文独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋以下内容为张勋视角年9月15日...
茶点故事阅读 36,919评论 2赞 328
白月光启示录
正文我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
茶点故事阅读 39,090评论 1赞 342
活死人
序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
沈念sama阅读 34,767评论 4赞 337
日本核电站爆炸内幕
正文年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
茶点故事阅读 40,410评论 3赞 322
男人毒药：我在死后第九天来索命
文/蒙蒙一、第九天我趴在偏房一处隐蔽的房顶上张望。院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
开封第一讲书人阅读 31,090评论 0赞 21
一桩弑父案，背后竟有这般阴谋
文/苍兰香墨我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。一阵脚步声响...
开封第一讲书人阅读 32,328评论 1赞 267
情欲美人皮
我被黑心中介骗来泰国打工，没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。一个月前我还...
沈念sama阅读 46,952评论 2赞 365
代替公主和亲
正文我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
茶点故事阅读 43,979评论 2赞 351