1, 隔离级别
RU : 读未提交.脏读,不可重复读,幻读
RC : 读已提交. 不可重复读,幻读
RR : 可重复读.幻读,可用GAP next lock
SR : 可串行化.
2, 幻读 不可重复读
3, insert buffer
4, 共享表空间
innodb_data_file_path=ibdata1:512M;ibdata2:512M:autoextend
5, 事务
autocommit=1
begin;
asdasd
asdasd
commit
autocommit=0
begin
a
b
c
commit;
show status like 'innodb_rows_lock%'
select * from information_schema.innodb_trx;
select * from sys.innodb_lock_waits;
select * from performance_schema.threads;
select * from performance_schema.events_statements_current;
select * from performance_schema.events_statements_history;
==========================================================
- InnoDB存储引擎核心特性-参数补充
1.1 默认存储引擎查看
show engines;
show variables like 'default_storage_engine';
select @@default_storage_engine;
1.2 修改表的存储引擎
(1) 通过参数设置默认引擎
(2) 建表的时候进行设置
(3) alter table t1 engine=innodb;
1.3 共享表空间设置
innodb_data_file_path
一般是在初始化数据之前就设置好
例子:
innodb_data_file_path=ibdata1:512M:ibdata2:512M:autoextend
1.5 独立表空间
show variables like 'innodb_file_per_table';
1.6 缓冲区池
mysql> select @@innodb_buffer_pool_size;
生产一般不要超过75%
生产故障:
内存OOM,问题: buffer设置过大.
1.7 innodb_flush_log_at_trx_commit=1 ******
"双1标准" 之一
作用: 控制redo log 什么时候刷写磁盘.
1: 每次事务commit; 都会立即将redo刷写os cache,并立即刷写到磁盘
0 : 每秒钟完成,redo刷写os cache,并立即刷写到磁盘
2: 每次事务commit; 都会立即将redo刷写os cache,并每秒写入磁盘一次.
1.8 innodb_flush_method ******
作用:
1. 控制数据刷盘
2. 控制redo刷盘
O_DIRECT : buffer pool 刷盘是跨过文件系统缓存,直接写磁盘,redo buffer 先写 os cache在写磁盘
FSYNC : buffer pool ,redo buffer 先写 os cache在写磁盘
O_DIRECT模式一般配合SSD盘或flash更佳.
1.9 redo相关
innodb_log_file_size=256M
innodb_log_files_in_group =4
innodb_log_buffer_size=2G
=========================================
第六章节 日志管理
通用日志 **
0.1 作用
记录MySQL发生过的所有命令. 一般可以用作审计.
0.2 配置
general_log=ON
general_log_file=/data/mysql/data/db01.log错误日志 ****
1.1 作用:
记录MySQL 启动,关闭,运行过程中的报错,状态等信息
1.2 配置
默认是开启的.存放在datadir/hostname.err
log_error=/tmp/mysql.err
1.3 怎么看?
关注 [ERROR]
- 二进制日志 ******
2.1 作用 :
用来记录 MySQL工作过程中,所有变更类的语句.
可以实现备份恢复,主从复制功能.
2.2 配置 :
默认二进制日志没有开启.
log_bin=/data/binlog/mysql-bin
server_id=6
[root@db01 ~]# mkdir -p /data/binlog
[root@db01 ~]# chown -R mysql.mysql /data/*
2.3 二进制日志记录了什么?
记录的就是SQL语句.
2.3.1 记录的语句类型
DDL
DCL
DML ( 只记录已经commit语句 )
2.3.2 日志记录格式
DDL ,DCL : statement 语句.执行什么就记录什么.
DML : binlog_format参数控制
SBR : statement 语句模式. 执行什么就记录什么.
RBR : ROW 行模式. 记录数据行的变化.
MBR : mixed 混合模式.自动判断用什么方式记录
默认格式: ROW
SBR: 日志量少,可读性强. 有可能出现记录错误.
RBR: 日志量大,可读性弱. 记录更严谨.
2.3.3 日志的记录内容格式 ---事件
binlog中最小的记录单元是事件(event).
DDL ,DCL:
一个语句就是一个事件
DML语句:
begin;
DML1
DML2
commit;
相对位置号: position
create table t1
begin;
insert into t1
update t1
commit;
drop table t1;
2.5 二进制日志管理
2.5.1 二进制日志基本信息查看
mysql> show binary logs;
mysql> show master status ;
2.5.2 二进制日志事件查看(events)
mysql> show binlog events in 'mysql-bin.000002';
Pos : 每个事件的起始位置点
End_log_pos:每个事件的结束位置点
可以用作将来的日志截取标记.
Info : 每个事务做的操作.
[root@db01 /data/binlog]# mysql -uroot -p123 -e "show binlog events in 'mysql-bin.000002' "|grep drop
2.5.3 二进制日志内容查看
[root@db01 /data/binlog]# mysqlbinlog /data/binlog/mysql-bin.000002
[root@db01 /data/binlog]# mysqlbinlog --base64-output=decode-rows -vvv /data/binlog/mysql-bin.000002 |grep -v SET
[root@db01 /data/binlog]# mysqlbinlog -d xxx /data/binlog/mysql-bin.000002 |grep -v SET
2.5.5 二进制日志截取及数据恢复
思路:
分析日志: 得到截取日志起点和终点.
mysql> show binlog events in 'mysql-bin.000002';
起点: 找到建库的位置 : 219
终点: 删除之前位置点 : 787进行截取
mysqlbinlog --start-position=219 --stop-position=787 /data/binlog/mysql-bin.000002 >/tmp/binlog.sql
截取000002二进制文件 219-787恢复日志
mysql> set sql_log_bin=0; ----> 临时关闭binlog日志记录
mysql> source /tmp/binlog.sql;
mysql> set sql_log_bin=1; 开启binlog
思考问题:
需要的日志在多个日志文件怎么截取?
方法1:
1-100000 219 219 100000
1-2020 2020 154 2020
方法2 :
--start-datetime=xxxx --stop-datetime=xxxxx file1 file >aa.sql以上案例,如果xxx库在2年前创建的,怎么恢复?
最近的一次全备+备份之后一直到误删除之前的所有日志
full+binlog
2.6 基于GTID方式记录的二进制日志管理
2.6.1 GTID介绍
从MySQL 5.6 版,加入的新特性.唯一编号,用来标识每一个二进制日志"事务".
事务1: begin; dml1;dml2;commit; 会产生一个GTID编号.
事务2: DDL或者DCL,一个event就是一个事务.会产生一个GTID编号.
2.6.2 GTID 编号格式
server_uuid:TID
[root@db01 /data/mysql/data]# cat auto.cnf
[auto]
server-uuid=79f1839c-0439-11ea-83bb-000c29d354c0
2.6.3 GTID开启
gtid_mode=ON
enforce_gtid_consistency=ON
log_slave_updates=ON
2.6.5 gtid模式日志管理
(1) 查看事件
mysql> show binlog events in 'mysql-bin.000003';
SET @@SESSION.GTID_NEXT= '79f1839c-0439-11ea-83bb-000c29d354c0:1'
(2) 截取日志
mysqlbinlog --include-gtids='79f1839c-0439-11ea-83bb-000c29d354c0:1-3' --exclude-gtids='79f1839c-0439-11ea-83bb-000c29d354c0:2' --skip-gtids
注意: 开了gtid模式 ,一定要添加--skip-gtids ,因为mysql会自动检查gtid幂等性
2.7 二进制日志的其他操作
2.7.1 二进制日志滚动
(1) 重启MySQL
(2) flush logs;
(3) max_binlog_size=1073741824
每天备份日志:
flush logs;
cp
mysqldump -F
mysqladmin -uroot -p123 flush-logs
a.000001 a.000002 a.000003 a.000004
2.7.2 二进制日志删除
(1) 配置自动删除
mysql> select @@expire_logs_days;
至少预留一轮全备周期+1的过期时间
生产一般建议保留至少两轮全备周期+1的时间
(2) 手工删除
Examples:
PURGE BINARY LOGS TO 'mysql-bin.010';
PURGE BINARY LOGS BEFORE '2008-04-02 22:46:26';
(3) 全部删除
mysql> reset master;
注意安全.
- 慢日志 ******
3.1 作用
记录MySQL运行过程中执行较慢的语句. 辅助管理员优化语句.
数据库较慢的场景:- 应急性
show processlist;
-----> select
-----> explain - 持续性
slowlog ---> SQL
----> select
----> explain
3.2 配置
mysql> show variables like '%slow%';
slow_query_log=ON
slow_query_log_file=/data/mysql/db01-slow.log
long_query_time=0.1
log_queries_not_using_indexes
- 应急性
=================================
压力测试
mysqlslap --defaults-file=/etc/my.cnf --concurrency=100 --iterations=1 --create-schema='test' --query="select * from test.t100w where k2='780P'" engine=innodb --number-of-queries=2000 -uroot -p123 -verbose
=================================
3.3 模拟慢语句,分析
[root@db01 /data/mysql]# mysqldumpslow -s c -t 10 /data/mysql/db01-slow.log
推荐工具:
pt-query-digest + Anemometer
pt-query-digest /data/mysql/db01-slow.log
ELK
========================================================================
第七章节 备份恢复
- 运维在数据库备份恢复方面的职责
1.1 设计备份策略
(1) 备份什么数据?
数据部分
二进制日志
配置文件
(2) 什么时间备份
低估期
(3) 备份周期
每周,每天...
(4) 备份的方式
逻辑备份: mysqldump,binlog
物理备份: xtrabackup
全备
增量
热备
冷备
(5) 备份脚本设计
略
1.2 备份的检查
(1) 日志
(2) 备份集的大小和完整性
1.3 备份的恢复演练
1.5 故障恢复
1.6 迁移
同版本
异构平台
简介
优势
技能
工作经历
项目
个人评价
==========================
备份类型
2.1 热备 : 在线备份. 不停业务进行备份,对业务影响小. Innodb支持
2.2 温备 : 锁表备份. 备份过程只能查询,不能修改. MyISAM
2.3 冷备 : 业务停止备份.备份方式
逻辑备份 : SQL语句的备份.
物理备份 : 基于数据文件的备份.备份工具:
5.1
mysqldump : MDP
逻辑备份工具,备份的就是SQL语句(Create database create table insert)
文本形式,可读性好,便于管理和备份处理,压缩比更高.
适用于数据量小的库备份.
建议:
<100G
TB: 分布式备份
100PB 怎么备份?
xtrabackup : XBK , PBK
物理备份工具.备份快.原生态支持增量备份.
100 <1T
