1、上节遗留问题
1.1、RC模式：可以屏蔽脏读，但是会出现不可重复读和幻读
image.png
image.png
必须建索引
image.png
1.2、重点参数
1）innodb_flush_log_at_trx_commit
作用：控制redo buffer 刷写磁盘的策略
等于0：redo buffer --------每秒----> os buffer ----------每秒------->磁盘
如果出现宕机，有可能丢失一秒的事务。
如果说你的业务，对数据丢失有一定容忍度，可以使用
等于1：redo buffer --------commit----> os buffer ----------commit------->磁盘
等于2：redo buffer --------commit----> os buffer ----------每秒------->磁盘
2） innodb_flush_method=O_DIRECT
作用：控制redo buffer和 data buffer 刷写磁盘时是否使用os buffer
数据磁盘，不使用os buffer
日志刷盘，使用os buffer
3) innodb_buffer_pool_size 50~80%
2、日志管理
2.1、排错
2.1.1、错误日志
2.1.2、怎么配置？
默认位置：datadir /hostname.err
配置方式：vim /etc/my.cnf
log_error=/data/mysql/mysql.log
使用方法：
查看error 上下文
2.2、数据恢复
2.2.1、binlog(二进制日志）
2.2.2、作用
数据恢复
主从复制
2.2.3、如何配置
lod_bin ------->打开二进制功能，设定存放位置
server_id --------->5.6中不需要，5.7以上参数时必须加server_id
注意：生产的要求，日志和数据分开存放
log_bin=/data/binlog/mysql-bin
server_id=6
systemctl restart mysqld
2.2.4、如何查看配置
mysql> show variables like '%log_bin%';
+---------------------------------+------------------------------+
| Variable_name | Value |
+---------------------------------+------------------------------+
| log_bin | ON |
| log_bin_basename | /data/binlog/mysql-bin |
| log_bin_index | /data/binlog/mysql-bin.index |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
| sql_log_bin | ON |
+---------------------------------+------------------------------+
2.2.5、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;
+-----------------+
| @@binlog_format |
+-----------------+
| ROW |
+-----------------+
2.2.6、二进制日志事件（event)
二进制日志记录的最小单元
对于DDL,DCL，一个语句就是一个event
对于DML语句来讲：只记录已提交的事务。
例如以下列子，就被分为4个event
position号码
begin; 120 - 340
DML1 340 - 460
DML2 460 - 550
commit; 550 - 760
event的组成
三部分构成
（1）事件的开始标志
（2）事件内容
（3）事件的结束标志
position:
开始标识: at 194
结束标识 : end_log_pos 254
194? 254?
某个事件在
2.2.7 二进制日志位置的查看
查看二进制日志所在位置
mysql> show variables like '%log_bin%';
ls -l /data/binlog/
-rw-r----- 1 mysql mysql 154 6月 25 09:34 mysql-bin.000001
-rw-r----- 1 mysql mysql 30 6月 25 09:34 mysql-bin.index
mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 154 |
+------------------+-----------+
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 154 | | | |
+------------------+----------+--------------+------------------+-------------------+
2.2.8、查看二进制文件内容*****
mysql> show master status; --->确认当前在用的Binlog
mysql> show binlog events in 'mysql-bin.000001';
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| mysql-bin.000001 | 4 | Format_desc | 6 | 123 | Server ver: 5.7.26-log, Binlog ver: 4 |
| mysql-bin.000001 | 123 | Previous_gtids | 6 | 154 | |
| mysql-bin.000001 | 154 | Anonymous_Gtid | 6 | 219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000001 | 219 | Query | 6 | 322 | create database oldboy2 |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
注释：每一行都是一个事件
log_name ：日志名
Pos ：事件开始的posiyion ****
Event_type ：事件类型
Server_id ：发生在哪台机器上的事件
End_log_pos ：事件结束的位置号 *****
Info : 事件内容 *****
查看二进制日志内容
mysqlbinlog mysql-bin.000001|grep -v "SET"
mysqlbinlog --base64-output=decode-rows -vvv mysql-bin.000001
2.2.9、基于二进制日志数据恢复案列
如何按需截取日志
1、基于position号的截取 *****
--start-position=###
--stop-position=###
截取二进制日志核心在于找起点和终点
mysqlbinlog --start-position=551 --stop-position=582 /data/binlog/mysql-bin.000001 >/tmp/bin.sql
恢复：
drop database oldboy2;
show databases;
set sql_log_bin=0;
source /tmp/bin.sql
案列
环境准备
#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;
第一步：找到起点和终点
show master status;
第二步：查看事件
show binlog events in 'mysql-bin.000002';
mysql-bin.000002 | 219 | Query | 6 | 335 | create database binlog charset utf8mb4 |
mysql-bin.000002 | 1272 | Anonymous_Gtid | 6 | 1337 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
第三步：mysqlbinlog --start-position=219 --stop-position=1272 /data/binlog/mysql-bin.000002 >/tmp/bin.sql
第四步：set sql_log_bin=0;
source /tmp/bin.sql;
第五步：use binlog
select * from t1;
2.2.10、开启GTID功能的二进制日志管理
思考问题？下面怎么恢复？、
#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;
truncate table t1;
insert into t1 values(3);
commit;
#4.
drop database binlog;
基于position号恢复需要多次截取，找起点和重点店过程很复杂。
什么是GTID（全局事务编号）
5.6 版本新加的特性，5.7中做了加强
5.6中不开启，没有这个功能
5.7中的GTID即使不开也会有自动生成
select @@session.GTID_NEXT='ANONYMOUS';
说明：
DDL DCL，一条语句（事件）就是一个事务，占一个GTID号
DML：一个完整的事务（begin ---> commit），是一个事务，占一个GTID号
vim /etc/my.cnf
gtid-mode=on
enforce-gtid-consistency=true
mysql> create database gg;
mysql>show master status;
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;
mysql>show master status;
mysql>drop database gg;
基于GTID截取二进制日志
mysqlbinlog --include-gtids='ac73f334-8d93-11e9-986d-000c2987e498:1-3' /data/binlog/mysql-bin.000003 >/tmp/gtid.sql
注意：以上日志截取出来的日志不能直接恢复？为啥呢？
gtid 的幂等性
正确的截取方法 --skip-gtids
mysqlbinlog --skip-gtids --include-gtids='ac73f334-8d93-11e9-986d-000c2987e498:1-3' /data/binlog/mysql-bin.000003 >/tmp/gtid.sql
set sql_log_bin=0;
source /tmp/gtid.sql
use gg;
select * from t1;
跳过某些gtid不截取
mysqlbinlog --skip-gtids --include-gtids='ac73f334-8d93-11e9-986d-000c2987e498:1-3' --exclude-gtids='ac73f334-8d93-11e9-986d-000c2987e498:2' /data/binlog/mysql-bin.000003 >/tmp/gtid.sql
2.2.11、二进制日志其他操作
临时关闭
set sql_log_bin=0 ---------->临时关闭二进制日志记录，退出mysql窗口可以恢复
做数据恢复之前，使用上述参数
自动清理
参数：select @@expire_logs_days;
设置依据？
至少是一个全备的周期，企业建议至少2个全备周期+1
怎么设置？
set global expire_logs_days=8;
退出生效，重启失效
永久生效加入/etc/my.cnf
expire_logs_days=8
手工清理
show binary logs; --------检查
PURGE BINARY LOGS BEFORE now() - INTERVAL 3 day;
PURGE BINARY LOGS TO 'mysql-bin.000002'; ------------清理
注意：不要手工 rm binlog文件
1、my.cnf binlog关闭掉，启动数据库
2、把数据库关闭，开启binlog,启动数据库
删除所有binlog,并从00001开始重新记录日志
日志滚动
重启数据库(两个都可以）
flush logs; 生成一个全新的日志
mysqladmin -uroot -p flush logs
show variables like '%max_binlog_size';
备份时加一些参数会触发滚动日志
2.3、优化相关日志 -slowlog
2.3.1、作用
记录慢SQL语句的日志，定位低效SQL语句的工具日志
mysql> select @@slow_query_log;
mysql> select @@slow_query_log_file;
mysql> select @@long_query_time;
slow_query_log=1
slow_query_log_file=/data/mysql/slow.log
long_query_time=0.1
log_queries_not_using_indexes
分析慢日志
mysqldumpslow -s c -t 10 /data/mysql/slow.log
备份恢复与迁移*****
1、DBA 在数据库备份恢复方面的职责
1.1、设计备份策略
全备：数据量小的
增量：数据量大的
时间：
自动：
200G数据备份在20-35分钟以内，全量+增量或者全量都可以，晚上11点用crontab备份
1.2、日常备份检查
备份存在性
备份空间够用否
1.3、定期恢复演练（测试库）
一季度或者半年
1.4、故障恢复
通过现有备份，能够将数据库恢复到故障之前的时间点。
1.5、迁移
1. 停止时间
2. 回退方案
2、备份类型
2.1、热备
在数据库正常业务时，备份数据，并且能够一致性恢复（只能是innodb）
对业务影响非常小
2.2、温备
锁表备份，只能查询不能修改（myisam）
影响到写入操作
2.3、冷备
关闭数据库业务，数据库没有任何变更的情况下，进行备份数据。
业务停止
3、备份方式及工具介绍
3.1、逻辑备份工具
mysqldump (MDP) *****
mysqlbinlog *****
3.2、物理备份工具
基于磁盘数据文件备份
xtrabackup (XBK) : percona 第三方 *****
MySQL Entherprise Backup (MEB)
4、逻辑备份和物理备份的比较
4.1、mysqldump (MDP)
优点：
1. 不需要下载安装
2. 备份出来的是SQL，文本格式，可读性高，便于备份处理
3. 压缩比较高，节省备份的磁盘空间
缺点：
4. 依赖与数据库引擎，需要从磁盘把数据读出
然后转化成SQL进行转储，比较消耗资源，数据量大的话效率比较低
建议：
100G以内的数据量级，可以使用mysqldump
超过TB以上，我们也可能选择的是mysqldump，配合分布式的系统
1EB =1024 PB = 1000000 TB
4.2、xtrabackup(XBK)
优点：
1. 类似于直接cp数据文件，不需要管逻辑结构，相对来说性能较高
缺点：
2. 可读性差
100GB-1TB之间建议用
5、备份策略
备份方式：
全备：全库备份，备份所有数据
增量：备份变化的数据
逻辑备份=mysqdump+mysqlbinlog
物理备份=xtrabackup_full+xtrabackup_incr+binlog 或者 xtrabackup_full+binlog
备份周期：
根据数据量设计备份周期
比如：周日全备，周一到周六增量
其他：通过主从复制备份
6、逻辑备份工具 -mysqldump
6.1、客户端通用命令,和连接有关
-u
-p
-h
-S
-P
本地备份连接方式：
mysqldump -uroot -p123 -S /tmp/mysql/.sock
远程备份方式连接方式：
mysqldump -uroot p123 -h 10.0.0.51
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
库名 表名 ：某个库下的一张或多张表
例子：备份world数据库下的city和country表
mysqldump -uroot -p123 world city country -S /tmp/mysql.sock >/data/backup/tab.sql
注意：此种方法只会备份建表语句和insert语句，所以恢复前需要把库建好，而且要use到库中去
6.3、必加参数（1）
-R 在备份时,同时备份存储过程和函数，如果没有会自动忽略
-E 在备份时，同时备份event,如果没有会自动忽略
--triggers 在备份时，同时备份触发器,如果没有会自动忽略
6.4、必加参数（2）
--master-data=2
记录备份开始时position号，可以作为将来日志截取的起点
功能：
1. 记录备份时的Position
2.自动锁表
3.配合--single-transaction，减少锁的（innodb引擎）
--single-transaction
对于Innodb的表，实现快照备份，不需要锁表
6.5、其他参数
-F
--set-gtid-purged=auto 在你的备份文件删除gtid号
auto ,on
off
--max-allowed-packet=#
网友评论