研究Mysq三高，l随心笔记

文章有点杂乱无章，不建议阅读，如果阅读也能get到许多新的知识点
5.7.33 版本以及以上
mysqldump的优点 逻辑备份自带的工具 占用空间少 可阅读
mysqldump -uroot -h127.0.0.1 -proot -P3306 库名 > d:/user.sql
source /XXXX.sql

--single-transaction 保持数据的一致性 InnoDB数据库
如果是 MyISAM 可以使用 --lock-all--tables
如果是 MyISAM 可以使用 --lock-tables 使READ LOCAL锁当前库的表（备份哪张表就锁定哪张表）
mysqldump的缺点：
1.导出数据比较慢
2.还原需要执行sql，速度也是比较慢的 。
3.备份还原性能不如物理备份
说明一句mysql MVCC，备份出同一时刻的数据
备份所有数据库：
mysqldump -uroot -p12346 --all-databases > /backup/mysqldump/all.sql
备份指定数据库：

mysqldump -uroot -p123456 --databases test --single-transaction > /backup/mysqldump/test.sql
备份指定数据库指定表(多个表以空格间隔)
mysqldump -uroot -p mysql db event > /backup/mysqldump/2table.sql
备份指定数据库排除某些表
mysqldump -uroot -p test --ignore-table=test.t1 --ignore-table=test.t2 > /backup/mysqldump/test2.sql
修改数据库的密码复杂LOW
mysql> set global validate_password_policy=LOW;
表示密码最小长度为4 或者 validate_password_length=0;测试环境下可以使用
mysql> set global validate_password_length=4;

show status; 查看数据库的工作状态 事务等
远程客户端连接不上 以为防火墙开启这呢 需要把3306端口打开 permanent永久的
firewall-cmd --zone=public --add-port=3306/tcp --permanent
开启成功 返回success
firewall-cmd --reload
必须从新加载一下

设置远程登录 可以使用这里的账号密码进行远程登录 PRIVILEGES 给与特权
GRANT ALL PRIVILEGES ON . TO 'root'@'%' IDENTIFIED BY 'youpassword' WITH GRANT OPTION;

Excel最大支持65535 行
事件驱动架构
Mysql的架构-------微核架构
JDBC-- 连接器 缓存 分析器 优化器 执行器 -文件系统
插件--->InnoDB MyISAM。。。。。
Mysql的连接方式
1.TCP/IP 连接 任何平台都提供的连接方法
TCP的三次握手 认证连接 认证通过开始交互 断开
指令举例： 切换数据库 0X02 查询命令 0X03
2.命名管道
同一台服务器通讯
3.共享内存
通过一片内存进行通信需要配置
4.UNIX域套接字
在系统里面的scket，服务端和客户端都需要配置 同一台电脑
缓存：KV之前执行过的语句会缓存的内存里面失效比较快，比较浪费CPU 在8.0已经去除
分析器：分析关键字
优化器：知道怎么做，最主要的工作是索引
执行器：校验权限，调用存储引擎
存储引擎：InnoDB 。MyISAM5.5.5默认的存储引擎B+树。。Memory Archive归档
InnoDB ：5.5.5之后默认 支持事务 外键 崩溃修复和并发控制
Memory ： 所有数据在内存，速度快 安全性差
Archive：插入快，可利用空间多 取出慢 查询差 不支持索引

关于mysql如何建表设计表如何维护表：
索引组织表：Index Organized Table
并不是一种组织表
索引：某一列和多列值进行预排序的数据结构
索引可以理解为目录
Primary Key
每个表都有一个主键NOT NULL Unique
desc tables；
如何使用视图，不存在的表
使用视图可以创建不存在的虚拟表
视图算法的选择 MERGE优于 --> TEMPTABLE
动态结果不适合使用MERGE 尽量避免使用关键字 尽量使用MERGE算法
explain 语句查看执行计划；
insert into 新表 查询旧表 插入数据；
show create table 表名；查看建表脚本；
show index from 索引表；

---

关于mysql备份
hot back 热备份
备份时数据库的状态
Hot Backup （热备）：正在运行中直接备份
Cold Backup （冷备）：完全停止后备份、
Warm Backup （温备）：数据库只读
备份文件的格式
逻辑备份：输出文本或者SQL语句
物理备份（裸文件）：备份数据库底层文件
备份的内容
完全备份：备份完整数据
增量备份：备份数据差异
日志备份：备份Binlog（也是一种物理备份）
举例工具
mysqldump ： 逻辑 备份，热 全量备份
xtrabackup ：物理备份，热，全量+增量备份、

OUTFILE 备份
mysql原生的sql指令，最原始的备份方式
备份的功能和效果取决于如何写sql语句
首先查询mysql的导出路径
show variables like '%secure';
使用into outfile 指令将查询结果导出到某个文件
select * into outfile '/var/lib/mysql-files/out_file_test' from Z ;
由于Mysql有MVCC功能；进入数据
begin;开始这一时间的一致性 备份出来是同一时刻的内容
每个字段使用逗号隔开
select * into outfile '/var/lib/mysql-files/out_file_test' fields terminated by ',' from Z ;
修改分隔符：fields terminated by
修改换行符：lines terminated by
outfile的缺陷
输出的文本过于简略
很难直接还原，只能简单的导出数据
mysqldump的备份--可以看上面的

mysqldump的增量备份---思路
1.binlog 忠实的记录了mysql的数据变化
2.mysqldump做全量备份之后，可以使用binlog作为增量别分
3.nysqldump全量备份时，切换binlog文件
4.从零还原时，采用全量还原+binlog还原

/var/lib/mysql ls
binlog文件
mysql-bin.00001.......不能随便拿走这个文件
--flush-logs 备份后切换bin-log文件
--master-data=2 切换后的bin-log文件名，=2是以注释的形式告诉你从哪里开始

mysqldump -uroot -p123456 --databases d1 --single-transaction --flush-logs --master-data=2 > test.sql
步骤2：
1.需要增量备份时，切换binlog文件,直接写下一文件
mysqldump -u root -p123456 flush-logs
增量的binlog文件备份可以拷贝
3.还原
3.1首先恢复旧的全量备份
进入mysql > source test.sql;
3.2然后将binlog增量备份还原至数据库
mysqlbinlog MYSQL-bin.00000X .....| mysql -u root -p 123456

4.如何使用XtraBackup物理备份
优点
4.1 直接备份InnoDB底层数据文件
4.2 导出不需要转换，速度快
4.3 工作时对数据库的压力较小
4.4 更容易实现增量备份
问题
----直接拷贝裸文件可行吗 ？ 可行
1.理论上可行，但是有很多问题
2.同时需要备份frm文件 idb文件 binlog文件 redo log 文件 等有先后顺序。基本不可行
3.不同版本的数据库和操作系统上还原的兼容问题
4.必须冷备份，影响业务，必须停掉数据库。

实现 1
var/lib/mysql
ib_logfile === redo文件
每个数据库一个文件夹
文件夹的xxx.idb非常大，考备份的时候非常大，需要去监听 redo log
，bin_log在一起，进入d1文件举例d1
数据首先写到ibd然后才刷到xxx.idb文件
2.
如何实现 物理+热+全量备份
思路：利用redo log 备份ibd文件+备份期间的redo log
1.启动redo log监听线程，开始收集redo log
2.拷贝idb数据文件
3.停止收集redo log
4.加FTWRL锁拷贝元数据frm

实现物理 + 热备+增量 备份
思路：和全量级别相同
如何实现：可以根据每页的LSN号。确定页的变化

如何实现物理还原
思路：和mysqld crash 崩溃恢复流程一样
1.还原数据文件，重放redo log

实操：ibbackup 工具
idb--->innodb官方出品工具--->ibd
73G 数据使用mysqldump使用4h17分 使用ibbackup 用时间5min
不过目前收费了 现在名字叫 Mysql Enterprise Back InnoDB

ibbackup —>开源 Xtrabackup 开源公司Percona公司开发
目前存在的版本 ：
XtraBackup8.0 ---> Mysql8.0
XtraBackup2.4 ---> Mysql5.1 5.5 5.6 5.7
安装：XtraBackup
wget 直接下载
wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.9/binary/redhat/7/x86_64/percona-xtrabackup-24-2.4.9-1.el7.x86_64.rpm
rpm -ivh --nodeps --foce 下载的包
yum install -y percona-xtrabackup-24-2.4.9-1.el7.x86_64.rpm
rpm -qa |grep xtrabackup
常用参数
--host 指定主机
--user 指定用户名
--password 指定密码
--port 指定端口
--databases 指定数据库
--incremental 创建增量备份
--incremental-basedir 指定包含完全备份的目录
--incremental-dir 指定包含增量备份的目录
--apply-log 对备份进行预处理操作
一般情况下，在备份完成后，数据尚且不能用于恢复操作，因为备份的数据中可能会包含尚未提交的事务或已经提交但尚未同步至数据文件中的事务。因此，此时数据文件仍处理不一致状态。“准备”的主要作用正是通过回滚未提交的事务及同步已经提交的事务至数据文件也使得数据文件处于一致性状态。
--redo-only 不回滚未提交事务
--copy-back 恢复备份目录
XtraBackup的全量备份使用方法 ：
XtraBackup封装的innobackupex
bakdir/存放路径 会生成一个小文件以日期为格式
备份： innobackupex --user=root --password=123456 bakdir/
数据还原（停止掉mysqld）
innobackupex --copy-back bakdir/XXXX-XX-XX/

XtraBackup的增量备份使用方法 ：
增量备份：
--incremental 增量的 后面的bakdir输出的
--incremental-dirbase 以全量为基础的 bakdir/XXXX-XX-XX/
innobackupex --user=root --password=123456 --incremental bakdir/ --incremental-dirbase='bakdir/XXXX-XX-XX/'
增量备份合并至全量备份：
让Y 合并到 X
innobackupex --apply-log bakdir/XXXX-XX-XX/ --incremental-dirbase=bakdir/YYYY-YY-YY/
缺点：无法阅读

创新备份工具：
mylvmbackup ：备份整个磁盘
1.物理，温备 只读
2.利用逻辑卷LVM 逻辑卷管理器
3.直接备份磁盘数据

mydumper：
跟mysqldump类似的工具
因为mysqldump一条一条的备份
1.实现了多线程并发的备份还原
2.速度比较快

Zmanda Recovery Manager ------ZRM
图形化界面：
1.能非常强大的备份恢复工具
2.集成了多种备份工具
3.集成了binlog分析工具

防患于未然：
1.权限隔离
给业务应用分配的账号只给MDL权限
给开发只读账号
DBA平时使用只读账号，特殊操作时切换账号

SQl审计：
DBA在开发环境审计即将上线的SQL语句
开发在线修改在线数据，提交给DBA执行
inception 自动审核工具

未删表
删表之前修改名字，观察是否影响业务
不直接删除表，给表名字添加特殊后缀，用脚本删除

完善流程、
上线的回滚，上线之前的备份数据
准备生产环境事故预案

mysql三高架构
1.高并发 ：同时处理的是事务数高
2.高性能 ：SQL执行的速度
3.高可用 ：可用时间高

三高是目的不是手段：
复制 扩展 和切换就能达到三高
复制：
目的：数据冗余
手段：binlog传送
收获：并发量提升，可用性提升
问题：占用更多的硬件资源

扩展：
目的：扩展数据库的容量
手段：数据库的分库分表
收获：性能 并发量的提升
问题：可能降低了可用性

切换：
目的：提高可用性
手段：主从身份切换
收获：并发量提升
问题：丢失切换时期的数据

三高的实现：
高并发：可通过复制和拓展，将数据分散到多节点
高性能：复制提升速度，拓展提升容量
高可用：节点时间身份的切换保证随时可用

Master 和 2 Slave
master 有 dump_thread
slave 有 io_thread 接收binlog 保存为relay_log称为中继日志 sql-thread
读取中继日志 重放一下read_read 产生一个binlog
有master发送binlog slave接受binlog

复制的类型：
异步复制：（Asynchronous Replication）
原理简单
对网络延迟要求小
不能保证数据被传送到了备库，可能丢失数据
半同步复制：（Semisynchronous Replication）
为了解决异步复制：多了一个ACK
原理简单
对网络延迟有一定要求，最好在一个机房
可以保证日志被传送到了备库，不易丢失数据
rpl_semi_sync_master_timeout 参数可以调整为脱口为10秒，超过会切换到异步复制
组复制：（Group Replication）
有一个共识机制
原理比较复制
需要依赖共识算法
实际应用比较少
是数据库走向原生分布式的示范
实操：
主库有遗留数据，从库新库
1.打开binlog 配置my.cmf
不是必要的东西
log-bin=/var/lib/mysql/mysql-bin 位置
server-id 就是一个数字 需要不一样即可
server-id=123456
2.左边库上全局锁，右边还原，因为同步的是增量
flush table with read_lock; 大写

show master status \G;
查看到那个binlog了 大写 pos是多少都可以查看到
使用mysqldump 备份所有的库 all-databases
然后 UNLOCK TABLES；解锁 数据库可以读写，往后的的binlog就可以同步了
3.从库恢复 ，查看从状态
show slave status \G;
stop slave;停止
reset slave；被重置了

然后可以重置master了
命令：
change master to
MASTER_HOST='127.13.15.156'，
MASTER_USER='root'，
MASTER_LOG_FILE='mysql-bin.00002',
MASTER_LOG_POS=194;
然后保存：
start slave；保存 大写
查看状态 show slave status \G;
Slave_IO_running YES
Slave_SQL_running YES
完成；

以上为异步的同步
以下是半同步的内容：
因为半同步的机制是插件所以：
可以直接安装plugin-load也可以在配置文件里面写
直接安装方式：
install plugin rpl_semi_sync_master SONAME 'semisync_master.so';
install plugin rpl_semi_sync_slave SONAME 'semisync_slave.so';
或者在配置my.cnf
比较重要的三行
'''
plugin-load="rpl_semi_sync_master=semisync_master.so; plugin rpl_semi_sync_slave=semisync_slave.so"
rpl-semi-sync-master-enable=1
rpl-semi-sync-slave-enable=1
'''
配置好就OK了
查看脱库的时间
show variable like 'rpl_semi_sync_maste_timeout' 结果我 10000毫秒等于10秒
show processlist; 多出个binlogdump线程
slave多出来两个线程，wait for master to send

简单配置思路：
LOG_FIILE+LOG_POS不方便
根本原因是：备库不知道该从哪个log开始复制
思路：跟每个事务分配一个唯一id

1. 5.6引入GTID 全局事务id
2. GTID= server_uuid:gno
3. server_uuid:节点的UUID 自己生成的id
4. gno事务流水号（回滚会回收）

如何使用GTID
gtid_mode = on 开启GTID
enforce_gtid_consistency = on 开启增强GTID
主从配完成以后，使用GTID进行主从复制
change master to
master_host='192.168.200.12',
master_user='root',
master_anto_position=1;
自动找位置，记得重启
查看slave的状态
show slave status；最下面基于有GTID的信息

为什么binlog的格式会影响复制？statement原文记录
statement 格式的binlog
5.0之前的mysql默认使用的是statement格式的binlog
binlog记录的就是sql语句的原文
由于主备库对于sql执行不一致，可能导致数据安全
实验：set binlog_format = statement
查看binlog的格式
show variable like 'binlog_format'; 结论为ROW
show warnings; 报出1592warnings，结果是主从不一致
show master status \G;
show binlog events in 'mysql-bin.0005';查看写入的是不是原文

线上记得slave开启只读，在配置文件添加
read-only=1

后来发展出来ROW格式的binlog：
1.不记录SQl语句的原文
2.记录数据行的变化
3.不是物理日志，还是逻辑日志
4.占空间比较大
实验：修改回来默认的 set binlog-format=ROW；
1.随便删除一条数据
2.查看binlog发现记录的不是原文，只记录了id

mixed格式binlog
1.两种格式混合使用
2.有数据风险的语句使用ROW
3.无数据风险的使用statement
建议有空间还是使用ROW格式

基于语句或者行的赋值
基于语句的赋值=statement
基于行的复制=row

备库延迟太大怎么办？
查看slave的状态
show slave status \G;
1.延迟的原因在于中继日志，重放日志比较耗时间；
2.备库性能不如主库
3.备库承担了很多分析SQL
4.主库的长时间事务未提交，binlog收不到commit，读取不了

处理方法
1.主备使用相同的配置机器，如果存在备库不如主库的时候
2.备库关闭log实时落盘
3.增加从库的数量，应对分析SQL
4.binlog传送到大数据系统，供分析，建议不使用mysql数据库
5.大事务一分多
根本原因:备库的硬件资源利用条件天然不如主库
备库是单线程
主库是多线程

并行复制的思路
能不能把重放的中继日志多线程
slave 多一个 worker，难点在分配 relay_log 策略
思路：
按表分发，按行分发
Mysql 5.6 并行复制从这里开始

1. Mysql 5.6开始使用按并行的策略
2. 优点：分发选择快，支持各种log格式
   3.缺点：库粒度大，很难负载均衡
3. slave-parallel-type = DATABASE
   其实不是很理想，基本是跑到一个worker上面去

解决方案：Mysql5.7使用按事务组并行的策略

1. binlog刷盘其实是两步动作
2. 把binlog从binlog cache 中写到内存中的binlog文件
3. 调用fsync持久化至硬盘

Mysql5.7使用按事务组并行的策略 prepare（准备）

1. Mysql 5.7 使用按照事务并行的策略
2. 同时处于prepare状态的事务，在备库执行时是可以并行的，有锁的时候不可能同时出现在prepare里
   主库：
3. binlog_group_commit_sync_delay;
   延迟多少微秒后才调用fsync
4. binlog_group_commit_sync_no_delay_count;
   累积多少次以后才调用fsync
5. 以上两个条件是或的关系，只要有一个满足就执行
   4.slave-parallel-type = LOGICAL_CLOCK

5.7.22并行复制
bin-transaction-dependency-tracking参数；
commit_order:按照事务并行（5.7）
WRITESET ：没有修改相同行事务可以并行
WRITESET_SESSION;
同一个线程先后执行的两个事务不能并行

研究：
如何在备库读到最新的数据？
强制走主库：
如何判断备库已经追上了？
1.强制延时
2.secons_behind_master=0
3.对比binlog执行位点
4.对比GTID的执行情况

说明：
备库延迟理论上无法消灭

1. binlog传送、中继日志重放需要时间
2. 理论上、备库延迟只能减小，无法消灭

判断具体事务是否已经重放

1. 等待binlog位点
   seelct master_pos_wait(file,pos[,timeout]);
2. 等待GTID（5.7.6之后可以返回每次的GTID）
   seelct wait_for_executed_gtid_set(gtid_set,1);

怎样实现最简单的高可用架构

1. 两个节点都是Master
   2.两个节点互相为Slave
   3.当一个节点出现故障时，无需切换

MasterA 可读可写
MasterB 只读
出现故障，把B的只读关了
先start slave；
给A配置上
change master to
master_host='192.168.200.11',
master_user='root',
master_password='12346',
master_anto_position=1;
然后启动 start slave；
查看
show slave status \G;

主主架构
存在问题：

1. 如果没有开启只读，两遍插入相同的ID，可能出现冲突
2. 两边约定好插入不同的ID
3. 只写一个主，另外一个读
4. 只有切换过快的数据丢失问题

客户端切换：

1. 应用自己切换比较麻烦
2. 使用keepalived 等手段可以完成自动切换

循环复制
1.理论有问题
2. 循环执行双方的binlog
3. 未开GTID ：使用serverID过滤
4. GTID ：天然避免这个问题