全面优化

0. 优化思路

硬件       : DELL R730 40C 128G 10台 SAS(8块:4块raid10 2块热备)+SSD 512G日志  网卡 4块网卡 bonding 1
操作系统   : C7.6,numa  THP 关闭 , XFS  ,Swap ,sysctl.conf ,limits.conf nofile , 防火墙 selinux 关闭,deadline,No LVM
数据库实例 ：
SQL规范    ：
索引       ：
事务和锁   ：
架构      ：
安全      ： 

---

1.硬件层面优化

1.0 硬件选配 

1.0 硬件选配
DELL、HP、IBM、华为、浪潮。
CPU：I、E

内存：ECC 
IO : SAS 、 pci-e SSD 、 Nvme flash
raid卡：Raid10

网卡： 单卡单口  bonding  + 交换机堆叠
云服务器： ECS 、RDS 、TDSQL、PolarxDB

1.1 关闭NUMA 

1.1 关闭NUMA
SMP   NUMA

a. bios级别:
在bios层面numa关闭时，无论os层面的numa是否打开，都不会影响性能。 

# numactl --hardware
available: 1 nodes (0)       #如果是2或多个nodes就说明numa没关掉

b. OS grub级别:
vi /boot/grub2/grub.cfg
#/* Copyright 2010, Oracle. All rights reserved. */
 
default=0
timeout=5
hiddenmenu
foreground=000000
background=ffffff
splashimage=(hd0,0)/boot/grub/oracle.xpm.gz
 
title Trying_C0D0_as_HD0
root (hd0,0)
kernel /boot/vmlinuz-2.6.18-128.1.16.0.1.el5 root=LABEL=DBSYS ro bootarea=dbsys rhgb quiet console=ttyS0,115200n8 console=tty1 crashkernel=128M@16M numa=off
initrd /boot/initrd-2.6.18-128.1.16.0.1.el5.img

在os层numa关闭时,打开bios层的numa会影响性能，QPS会下降15-30%;

c. 数据库级别:

mysql> show variables like '%numa%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| innodb_numa_interleave | OFF   |
+------------------------+-------+

或者： 
vi /etc/init.d/mysqld
找到如下行
# Give extra arguments to mysqld with the my.cnf file. This script
# may be overwritten at next upgrade.
$bindir/mysqld_safe --datadir="$datadir" --pid-file="$mysqld_pid_file_path" $other_args >/dev/null &

wait_for_pid created "$!" "$mysqld_pid_file_path"; return_value=$?
将$bindir/mysqld_safe --datadir="$datadir"这一行修改为：

/usr/bin/numactl --interleave all $bindir/mysqld_safe --datadir="$datadir" --pid-file="$mysqld_pid_file_path" $other_args >/dev/null &
wait_for_pid created "$!" "$mysqld_pid_file_path"; return_value=$?

1.2 阵列卡配置建议 

1.3 关闭THP 

1.4 网卡绑定 

1.5 多路径储存 

1.2 阵列卡配置建议
raid10(推荐)
SSD或者PCI-E或者Flash
强制回写（Force WriteBack）
BBU 电池 ： 如果没电会有较大性能影响、定期充放电，如果UPS、多路电源、发电机。可以关闭。
关闭预读
有可能的话开启Cache(如果UPS、多路电源、发电机。)

 1.3 关闭THP
vi /etc/rc.local
在文件末尾添加如下指令：
if test -f /sys/kernel/mm/transparent_hugepage/enabled; then
echo never > /sys/kernel/mm/transparent_hugepage/enabled
fi
if test -f /sys/kernel/mm/transparent_hugepage/defrag; then
echo never > /sys/kernel/mm/transparent_hugepage/defrag
fi
[root@master  ~]# cat /sys/kernel/mm/transparent_hugepage/enabled 
always madvise [never]
[root@master  ~]# cat  /sys/kernel/mm/transparent_hugepage/defrag 
always madvise [never]


1.4 网卡绑定
bonding技术，业务数据库服务器都要配置bonding继续。建议是主备模式。
交换机一定要堆叠。

1.5 存储多路径
使用独立存储设备的话，需要配置多路径。
linux 自带 : multipath
厂商提供    :

2.系统层面优化

a. 更改文件句柄和进程数
内核优化 /etc/sysctl.conf
vm.swappiness = 5
vm.dirty_ratio = 20
vm.dirty_background_ratio = 10
net.ipv4.tcp_max_syn_backlog = 819200
net.core.netdev_max_backlog = 400000
net.core.somaxconn = 4096
net.ipv4.tcp_tw_reuse=1
net.ipv4.tcp_tw_recycle=0

limits.conf 
nofile 63000


b. 防火墙
禁用selinux ： /etc/sysconfig/selinux 更改SELINUX=disabled.
iptables如果不使用可以关闭。可是需要打开MySQL需要的端口号

c. 文件系统优化
推荐使用XFS文件系统
MySQL数据分区独立 ，例如挂载点为: /data
mount参数 defaults, noatime, nodiratime, nobarrier 如/etc/fstab：
/dev/sdb /data                   xfs     defaults,noatime,nodiratime,nobarrier        1 2

d. 不使用LVM 

e. io调度
SAS ：      deadline
SSD&PCI-E： noop

centos 7 默认是deadline
cat   /sys/block/sda/queue/scheduler

#临时修改为deadline(centos6)
echo deadline >/sys/block/sda/queue/scheduler 
vi /boot/grub/grub.conf
更改到如下内容:
kernel /boot/vmlinuz-2.6.18-8.el5 ro root=LABEL=/ elevator=deadline rhgb quiet

3. 数据库版本选择

1、稳定版：选择开源的社区版的稳定版GA版本。
2、选择mysql数据库GA版本发布后6个月-12个月的GA双数版本，大约在15-20个小版本左右。
3、要选择前后几个月没有大的BUG修复的版本，而不是大量修复BUG的集中版本。
4、要考虑开发人员开发程序使用的版本是否兼容你选的版本。
5、作为内部开发测试数据库环境，跑大概3-6个月的时间。
6、优先企业非核心业务采用新版本的数据库GA版本软件。
7、向DBA高手请教，或者在技术氛围好的群里和大家一起交流，使用真正的高手们用过的好用的GA版本产品。

最终建议： 8.0.20是一个不错的版本选择。向后可以选择双数版。  

4.💖数据库三层结构及核心参数优化

4.1 连接层

max_connections=1000         #*****       最大连接数
max_connect_errors=999999                 数据库开启以来失败连接次数
wait_timeout=600             #*****       连接超时时间
interactive_wait_timeout=3600             交互式连接超时时间
net_read_timeout  = 120                   网络读超时时间
net_write_timeout = 120                   网络写超时时间
max_allowed_packet= 32M      #*****       允许的数据包大小

4.2 Server层

sql_safe_updates                =1                 # *****             update delete语句必须有where条件 有索引
slow_query_log                  =ON                                    是否开启慢日志
slow_query_log_file             =/data/3307/slow.log   # *****         慢日志存放位置
long_query_time                 =1                  # *****            多久才算慢日志
log_queries_not_using_indexes   =ON                 # *****            没走索引的
log_throttle_queries_not_using_indexes = 10         # *****            没走索引的  相同语句最多记录次数
sort_buffer 					= 1M
join_buffer 					= 1M
read_buffer						= 1M
read_rnd_buffer                 = 1M
tmp_table  						= 16M
heap_table 						= 16M
max_execution_time              = 28800                                SQL最大执行时间
lock_wait_timeout               = 60                 # *****           MDL锁等待时间
lower_case_table_names          =1                   # *****           建库建表时强制转换小写
thread_cache_size               =64                  
log_timestamps                  =SYSTEM              # *****           日志时间与系统时间一致
init_connect                    ="set names utf8"    # *****           客户端字符集设置成'utf8'
event_scheduler                 =OFF                                   计划任务
secure-file-priv                =/tmp                # *****           导数据传到指定目录
binlog_expire_logs_seconds      =2592000             # *****           二进制日志过期时间
sync_binlog                     =1                   # *****           二进制日志文件每次写的时候同步到磁盘
log-bin                         =/data/3307/mysql-bin
log-bin-index                   =/data/3307/mysql-bin.index
max_binlog_size                 =500M
binlog_format                   =ROW

4.3 存储引擎层

transaction-isolation               ="READ-COMMITTED"    # *****       隔离级别
innodb_data_home_dir                =/xxx                              ibdata位置点
innodb_log_group_home_dir           =/xxx                              redolog位置点
innodb_log_file_size                =2048M                             redolog大小 （512M-4G)
innodb_log_files_in_group           =3                                 redolog组
innodb_flush_log_at_trx_commit      =2                   # *****       事务提交redolog立即刷新到磁盘
innodb_flush_method                 =O_DIRECT            # *****       刷新策略
innodb_io_capacity                  =1000                # *****       iops刷新多少数据页
innodb_io_capacity_max              =4000         
innodb_buffer_pool_size             =64G                 # *****       50%-75%物理内存
innodb_buffer_pool_instances        =4                   # *****
innodb_log_buffer_size              =64M                 # *****       redo buffer大小 (512M-4G)
innodb_max_dirty_pages_pct          =85                  # *****       脏页占比  超过出发ckpt刷新到磁盘
innodb_lock_wait_timeout            =10                  # *****       行锁超时时间
innodb_open_files                   =63000               # *****       文件句柄
innodb_page_cleaners                =4                                 刷写数据页线程数量
innodb_sort_buffer_size             =64M                               排序缓冲区
innodb_print_all_deadlocks          =1                   #             监控死锁信息 自动打印到日志
innodb_rollback_on_timeout          =ON                                事务超时是否自动回滚
innodb_deadlock_detect              =ON

4.4 复制

relay_log                       =/opt/log/mysql/blog/relay
relay_log_index                 =/opt/log/mysql/blog/relay.index
max_relay_log_size              =500M
relay_log_recovery              =ON

rpl_semi_sync_master_enabled                =ON
rpl_semi_sync_master_timeout                =1000
rpl_semi_sync_master_trace_level            =32
rpl_semi_sync_master_wait_for_slave_count   =1
rpl_semi_sync_master_wait_no_slave          =ON
rpl_semi_sync_master_wait_point             =AFTER_SYNC
rpl_semi_sync_slave_enabled                 =ON
rpl_semi_sync_slave_trace_level             =32


binlog_group_commit_sync_delay              =1
binlog_group_commit_sync_no_delay_count     =1000

gtid_mode                       =ON
enforce_gtid_consistency        =ON

skip-slave-start                =1
#read_only                      =ON
#super_read_only                =ON
log_slave_updates               =ON
server_id                       =2330602
report_host                     =xxxx
report_port                     =3306
slave_parallel_type                         =LOGICAL_CLOCK
slave_parallel_workers                      =4
master_info_repository                      =TABLE
relay_log_info_repository                   =TABLE

4.5 其它

客户端配置： 
[mysql]
no-auto-rehash

5.开发规范

5.1 字段规范

1. 每个表建议在30个字段以内(了解三大范式)。
2. 需要存储emoji字符的，则选择utf8mb4字符集。
3. 机密数据，加密后存储。
4. 整型数据，默认加上UNSIGNED。
5. 存储IPV4地址建议用bigINT UNSIGNE，查询时再利用INET_ATON()、INET_NTOA()函数转换。
6. 如果遇到BLOB、TEXT大字段单独存储表或者附件形式存储。
7. 选择尽可能小的数据类型，用于节省磁盘和内存空间。
8. 存储浮点数，可以放大倍数存储。
9. 每个表必须有主键，INT/BIGINT并且自增做为主键，分布式架构使用sequence序列生成器保存。
10. 每个列使用not null，或增加默认值。

5.2 SQL语句规范

### 1. 去掉不必要的括号
如：      ((a AND b) AND c OR (((a AND b) AND (c AND d)))) 
修改成    (a AND b AND c) OR (a AND b AND c AND d)
### 2.  去掉重叠条件
如：      (a<b AND b=c) AND a=5
修改成    b>5 AND b=c AND a=5
如：      (B>=5 AND B=5) OR (B=6 AND 5=5) OR (B=7 AND 5=6)
修改成    B=5 OR B=6

### 3. 避免使用not in、not exists 、<>、like %%
### 4. 多表连接，小表驱动大表
### 5. 减少临时表应用，优化order by 、group by、union、distinct、join等
### 6. 减少语句查询范围，精确查询条件
### 7. 多条件，符合联合索引最左原则
### 8. 查询条件减少使用函数、拼接字符等条件、条件隐式转换
### 9. union all 替代 union
### 10.减少having子句使用
### 11.如非必须不使用 for update语句 
### 12.update和delete，开启安全更新参数
### 13.减少inset  ... select语句应用
### 14.使用load 替代insert录入大数据
### 15.导入大量数据时，可以禁用索引、增大缓冲区、增大redo文件和buffer、关闭autocommit、RC级别可以提高效率 
### 16.优化limit，最好业务逻辑中先获取主键ID，再基于ID进行查询 
	limit 5000000,10     limit 10 , 200
### 17. DDL执行前要审核
### 18. 多表连接语句执行前要看执行计划

6. 索引优化

1. 非唯一索引按照“i_字段名称_字段名称[_字段名]”进行命名。
2. 唯一索引按照“u_字段名称_字段名称[_字段名]”进行命名。
3. 索引名称使用小写。
4. 索引中的字段数不超过5个。
5. 唯一键由3个以下字段组成，并且字段都是整形时，使用唯一键作为主键。
6. 没有唯一键或者唯一键不符合5中的条件时，使用自增id作为主键。
7. 唯一键不和主键重复。
8. 索引选择度高的列作为联合索引最左条件
9. ORDER BY，GROUP BY，DISTINCT的字段需要添加在索引的后面。
10. 单张表的索引数量控制在5个以内，若单张表多个字段在查询需求上都要单独用到索引，需要经过DBA评估。
    查询性能问题无法解决的，应从产品设计上进行重构。
	
11. 使用EXPLAIN判断SQL语句是否合理使用索引，尽量避免extra列出现：Using File Sort，Using Temporary。

12. UPDATE、DELETE语句需要根据WHERE条件添加索引。

13. 对长度大于50的VARCHAR字段建立索引时，按需求恰当的使用前缀索引，或使用其他方法。

14. 下面的表增加一列url_crc32，然后对url_crc32建立索引，减少索引字段的长度，提高效率。

CREATE TABLE all_url(ID INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
url VARCHAR(255) NOT NULL DEFAULT 0,      
url_crc32 INT UNSIGNED NOT NULL DEFAULT 0,
index idx_url(url_crc32));

15. 合理创建联合索引（避免冗余），(a,b,c) 相当于 (a) 、(a,b) 、(a,b,c)。

16. 合理利用覆盖索引，减少回表。

17. 减少冗余索引和使用率较低的索引
mysql> select * from sys.schema_unused_indexes;
mysql> select * from sys.schema_redundant_indexes\G

7.锁优化✨✨✨

7.1 全局锁 Global Read lock

a. 介绍

全局读锁。
加锁方法： FTWRL，flush tables with read lock.
解锁方法： unlock tables;

出现场景： 
	mysqldump  --master-data  
	xtrabackup（8.0之前早期版本）等备份时。
属于类型： MDL（matedatalock）层面锁
影响情况： 加锁期间，阻塞所有事务写入，阻塞所有已有事务commit。

MDL，等待时间受 lock_wait_timeout=31536000

b. 检测方法🕵️‍♂️

## 8.0之前需要手工配置开启.
UPDATE performance_schema.setup_instruments
SET ENABLED = 'YES', TIMED = 'YES'
WHERE NAME = 'wait/lock/metadata/sql/mdl';

mysql> select * from performance_schema.metadata_locks\G

mysql> select OBJECT_SCHEMA ,OBJECT_NAME ,LOCK_TYPE,LOCK_DURATION,LOCK_STATUS ,OWNER_THREAD_ID,OWNER_EVENT_ID from performance_schema.metadata_locks;

mysql> show processlist;
mysql> select * from sys.schema_table_lock_waits;

场景:  业务反馈所有写入做不了.
mysql> show processlist;
mysql> select * from performance_schema.metadata_locks\G
mysql> select * from sys.schema_table_lock_waits;


沟通后决定怎么处理?
kill ?

经典故障案例1：5.7 xtrabackup/mysqldump备份时数据库出现hang状态，所有修改查询都不能进行

session1: 模拟一个大的查询或事务
mysql> select id,sleep(100)  from city where id<100  for update ;

session2: 模拟备份时的FTWRL 
mysql> flush tables with read lock;
-- 此时发现命令被阻塞

session3: 发起查询，发现被阻塞
mysql> select * from world.city where id=1 for update;

结论： 备份时，一定要选择业务不繁忙期间，否则有可能会阻塞正常业务。

 

故障案例2： 5.7版本  innobackupex备份全库，进程死了，mysql里就是全库读锁，后边insert 全阻塞了

#分析思路： 
show processlist  ---->  select * from performance_schema.metadata_locks;  ---> pending ---->granted ----> OWNER_THREAD_ID: 66
---->  select * from threads  \G ----->processlist_Id---->  show processlist ----->  kill processlist_Id
分析过程:
pending 	 56   54  57
events_statements_history
56: 	
SQL_TEXT: select * from city where id=500 for update
54:
SQL_TEXT: flush tables with read lock
57:
SQL_TEXT: select * from user where id=1 for update
granted      55 	
SQL_TEXT: select id,sleep(100)  from city where id<100  for update
55  ---> 54 ----> 56 ,57
55?  ---->

#故障模拟： 

7.2 row lock wait 锁等待

a.介绍

record lock 、gap、next lock
都是基于索引加锁,与事务隔离级别有关。

b. 行锁监控及分析🕵️‍♂️

# 查询锁等待详细信息
select * from sys.innodb_lock_waits;   ----> blocking_pid(锁源的连接线程)

# 通过连接线程找SQL线程
select * from performance_schema.threads;

# 通过SQL线程找到 SQL语句
select * from performance_schema.events_statements_history;

c. 优化方向

1. 优化索引
2. 减少事务的更新范围
3. RC
4. 拆分语句： 
例如：  update t1 set num=num+10 where k1 <100;  k1 是辅助索引,record lock gap next
	   改为:
	   select id from t1 where  k1 <100; ---> id: 20,30,50
	   update t1 set num=num+10   where id in (20,30,50);

故障案例3 :  16C    top  CPU使用总量 1200%-1300, 平均 80%+   MySQL数据库服务器

#分析思路： 
top -Hp  MYSQLID   ---> os_id   ----> P_S.threads ---> processlist_id ,  thread_id
IO  ---> top  wait
可能是什么原因?
IOPS? 回表太多?---> 索引
吞吐? 索引?
硬件? raid IO调度  .....
大事务.
参数.
SQL :
events_statements_history/current
类型:
select  ----> explain  --->索引     SQL本身 --> kill
DML     ----> 锁  大事务 ---> 索引  SQL本身 ---> 评估
DDL     ---->  MDL     ----> kill

#故障模拟：

8.架构优化

#高可用架构：
	MHA+ProxySQL+GTID+增强半同步   99.99%   高可用+ 读写分离--> 中小型企业
	MGR\InnoDB Cluster            
	PXC 
#读写分离： 
	ProxySQL、MySQL-router
#分布式架构： 
	MYcat  Polardb TDSQL   

#NoSQL: 
	Redis+sentinel(哨兵),Redis Cluster
	MongoDB RS/MongoDB SHARDING Cluster 
	ES  solr

9.安全优化

1、 使用普通nologin用户管理MySQL
2、 合理授权用户、密码复杂度及最小权限、系统表保证只有管理员用户可访问。
3、 删除数据库匿名用户
4、 锁定非活动用户
5、 MySQL尽量不暴露互联网,需要暴露互联网用户需要设置明确白名单、替换MySQL默认端口号、使用ssl连接
6、 优化业务代码，防止SQL注入。

10.常用工具介绍

一、 PT（percona-toolkits）工具的应用

1. pt工具安装

[root@master ~]# <font style="color:#F5222D;">yum install -y  percona-toolkit-3.1.0-2.el7.x86_64.rpm</font> 

2. 常用工具使用介绍

2.1 pt-archiver 归档表

场景： 
	面试题： 亿级的大表，delete批量删除100w左右数据。 #批量删除功能
	面试题： 定期按照时间范围，进行归档表。           #归档功能---并发归档

#重要参数
--limit 100         每次取100行数据用pt-archive处理    
--txn-size  100     设置100行为一个事务提交一次，    
--where 'id<3000'   设置操作条件    
--progress 5000     每处理5000行输出一次处理信息    
--statistics        输出执行过程及最后的操作统计。（只要不加上--quiet，默认情况下pt- archive都会输出执行过程的）    
--charset=UTF8      指定字符集为UTF8—这个最后加上不然可能出现乱码。    
--bulk-delete       批量删除source上的旧数据(例如每次1000行的批量删除操作)

注意:  需要归档表中至少有一个索引,做好是where条件列有索引


使用案例：
1.归档到数据库      ---远程归档居多
#1.1 获得建表语句

#1.2 备份
db01 [test]> create table test1 like t100w;
pt-archiver --source h=10.0.0.51,D=test,t=t100w,u=oldguo,p=123 --dest h=10.0.0.51,D=test,t=test1,u=oldguo,p=123 --where 'id<10000' --no-check-charset --no-delete --limit=1000 --commit-each --progress 1000 --statistics

2.只清理数据
pt-archiver --source h=10.0.0.51,D=test,t=t100w,u=oldguo,p=123 --where 'id<10000' --purge --limit=1 --no-check-charset

3.只把数据导出到外部文件，但是不删除源表里的数据
pt-archiver --source h=10.0.0.51,D=world,t=city,u=root,p=123 --where '1=1' --no-check-charset --no-delete --file="/tmp/archiver.dat" 

2.2 pt-osc 修改表结构

#场景：  
	  修改表结构、索引创建删除
	  不能加快速度，但能减少业务影响（锁）。
	  
#面试题 ： 	  	  
pt-osc工作流程：
1、检查更改表是否有主键或唯一索引，是否有触发器
2、检查修改表的表结构，创建一个临时表，在新表上执行ALTER TABLE语句
create table  bak like t1; 
alter table bak add telnum char(11) not null;


3、在源表上创建三个触发器分别对于INSERT UPDATE DELETE操作
create trigger 
a 
b 
c

4、从源表拷贝数据到临时表，在拷贝过程中，对源表的更新操作会写入到新建表中


5、将临时表和源表rename（需要元数据修改锁，需要短时间锁表）

6、删除源表和触发器，完成表结构的修改。


#pt-osc工具限制
1、源表必须有主键或唯一索引，如果没有工具将停止工作
2、如果线上的复制环境过滤器操作过于复杂，工具将无法工作
3、如果开启复制延迟检查，但主从延迟时，工具将暂停数据拷贝工作
4、如果开启主服务器负载检查，但主服务器负载较高时，工具将暂停操作
5、当表使用外键时，如果未使用--alter-foreign-keys-method参数，工具将无法执行
6、只支持Innodb存储引擎表，且要求服务器上有该表1倍以上的空闲空间。

#pt-osc之alter语句限制
1、不需要包含alter table关键字，可以包含多个修改操作，使用逗号分开，如"drop clolumn c1, add column c2 int"
2、不支持rename语句来对表进行重命名操作
3、不支持对索引进行重命名操作
4、如果删除外键，需要对外键名加下划线，如删除外键fk_uid, 修改语句为"DROP FOREIGN KEY _fk_uid"


pt-osc之命令模板
## --execute表示执行
## --dry-run表示只进行模拟测试
## 表名只能使用参数t来设置，没有长参数

pt-online-schema-change \
--host="127.0.0.1" \
--port=3358 \
--user="root" \
--password="root@root" \
--charset="utf8" \
--max-lag=10 \
--check-salve-lag='xxx.xxx.xxx.xxx' \
--recursion-method="hosts" \
--check-interval=2 \
--database="testdb1" \
  t="tb001" \
--alter="add column c4 int" \
--execute


例子：
pt-online-schema-change --user=oldguo --password=123 --host=10.0.0.51 --alter "add column state int not null default 1" D=test,t=t100w --print --execute

pt-online-schema-change --user=oldguo --password=123 --host=10.0.0.51 --alter "add index idx(num)" D=test,t=t100w --print --execute

2.3 pt-table-checksum 校验主从数据

#场景：   校验主从数据一致性，生产中可用于定时任务。
 环境准备:
	- 主从环境
  - 从库开启配置
    [mysqld]
    report_host=10.0.0.52
		report_port=3307


2.3.1 创建数据库
Create database pt CHARACTER SET utf8;

创建用户checksum并授权
create user  'checksum'@'10.0.0.%' identified with mysql_native_password by 'checksum';
GRANT ALL ON *.* TO 'checksum'@'10.0.0.%' ;
flush privileges;

2.3.2 参数: 
--[no]check-replication-filters：是否检查复制的过滤器，默认是yes，建议启用不检查模式。
--databases | -d：指定需要被检查的数据库，多个库之间可以用逗号分隔。
--[no]check-binlog-format：是否检查binlog文件的格式，默认值yes。建议开启不检查。因为在默认的row格式下会出错。
--replicate：把checksum的信息写入到指定表中。
--replicate-check-only：只显示不同步信息

pt-table-checksum --nocheck-replication-filters --no-check-binlog-format --replicate=pt.checksums --create-replicate-table --databases=test --tables=t1 h=10.0.0.51,u=checksum,p=checksum,P=3307


---->  可将命令加入到定时任务脚本。检查异步复制，是否出现问题。
cat checksum.sh
#!/bin/bash 
date >> /root/db/checksum.log
pt-table-checksum --nocheck-binlog-format --nocheck-plan --nocheck-replication-filters --replicate=pt.checksums --set-vars innodb_lock_wait_timeout=120 --databases test --tables t1 -u'checksum' -p'checksum' -h'10.0.0.51' >> /tmp/checksum.log
date >> /root/db/checksum.log

2.4 pt-table-sync 修复主从数据

2.4 pt-table-sync
主要参数介绍
--replicate ：指定通过pt-table-checksum得到的表.
--databases : 指定执行同步的数据库。
--tables ：指定执行同步的表，多个用逗号隔开。
--sync-to-master ：指定一个DSN，即从的IP，他会通过show processlist或show slave status 去自动的找主。
h= ：服务器地址，命令里有2个ip，第一次出现的是Master的地址，第2次是Slave的地址。
u= ：帐号。
p= ：密码。
--print ：打印，但不执行命令。
--execute ：执行命令。

pt-table-sync --replicate=pt.checksums --databases test  --tables t1 h=10.0.0.51,u=checksum,p=checksum,P=3307 h=10.0.0.51,u=checksum,p=checksum,P=3306 --print

pt-table-sync --replicate=pt.checksums --databases test  --tables t1 h=10.0.0.51,u=checksum,p=checksum,P=3307 h=10.0.0.51,u=checksum,p=checksum,P=3307 --execute

2.5. # pt-show-grants (备份数据库用户的方法)✨

#作用:  用户和权限信息迁移。
pt-show-grants -h10.0.0.51  -P3307  -uchecksum -pchecksum 


----------------->  导出结果： 
-- Grants dumped by pt-show-grants
-- Dumped from server 10.0.0.51 via TCP/IP, MySQL 5.7.28-log at 2020-05-15 17:11:06
-- Grants for 'checksum'@'10.0.0.%'
CREATE USER IF NOT EXISTS 'checksum'@'10.0.0.%';
ALTER USER 'checksum'@'10.0.0.%' IDENTIFIED WITH 'mysql_native_password' AS '*E5E390AF1BDF241B51D9C0DBBEA262CC9407A2DF' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK;
GRANT ALL PRIVILEGES ON *.* TO 'checksum'@'10.0.0.%';
-- Grants for 'mysql.session'@'localhost'
CREATE USER IF NOT EXISTS 'mysql.session'@'localhost';
ALTER USER 'mysql.session'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT LOCK;
GRANT SELECT ON `mysql`.`user` TO 'mysql.session'@'localhost';
GRANT SELECT ON `performance_schema`.* TO 'mysql.session'@'localhost';
GRANT SUPER ON *.* TO 'mysql.session'@'localhost';
-- Grants for 'mysql.sys'@'localhost'
CREATE USER IF NOT EXISTS 'mysql.sys'@'localhost';
ALTER USER 'mysql.sys'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT LOCK;
GRANT SELECT ON `sys`.`sys_config` TO 'mysql.sys'@'localhost';
GRANT TRIGGER ON `sys`.* TO 'mysql.sys'@'localhost';
GRANT USAGE ON *.* TO 'mysql.sys'@'localhost';
-- Grants for 'repl'@'10.0.0.%'
CREATE USER IF NOT EXISTS 'repl'@'10.0.0.%';
ALTER USER 'repl'@'10.0.0.%' IDENTIFIED WITH 'mysql_native_password' AS '*23AE809DDACAF96AF0FD78ED04B6A265E05AA257' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK;
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'10.0.0.%';
-- Grants for 'root'@'10.0.0.%'
CREATE USER IF NOT EXISTS 'root'@'10.0.0.%';
ALTER USER 'root'@'10.0.0.%' IDENTIFIED WITH 'mysql_native_password' AS '*23AE809DDACAF96AF0FD78ED04B6A265E05AA257' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK;
GRANT ALL PRIVILEGES ON *.* TO 'root'@'10.0.0.%';
-- Grants for 'root'@'localhost'
CREATE USER IF NOT EXISTS 'root'@'localhost';
ALTER USER 'root'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*23AE809DDACAF96AF0FD78ED04B6A265E05AA257' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK;
GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION;
GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION;

2.5  pt-slave-find  显示主从结构 (可了解下ORCH)

[root@db01 tmp]#   pt-slave-find -h10.0.0.51  -P3307 -uchecksum -pchecksum 

2.6  监控主从延时

#2.6监控主从延时自带判定方法:
show slave status \G
second _behind_master===> 从库sQr回放的Ts-主从执行是的Ts-主从的时间差异(主从系统时间延时会自动补偿)
exec_master_log_pos ---> 异步复制较准确/ 但SQL多线程回放 如: loigcal_colck