13-MySQL 全面优化+故障+PT工具使用

简介

1. 优化的哲学

1.1 什么时候优化 ？
优化有风险，涉足请谨慎！！
优化一定是由业务需求触发的。

1.2 数据库的优化谁来参与 ？ 
硬件工程师 
网落管理员
系统管理员
开发人员
DBA 
领导



1.3 数据库优化的思路
1.3.1 大面 
性能  
安全 
1.3.2 框架
（1）硬件、网络、存储
（2）操作系统、文件系统
（3）数据库实例 （链接层、SQL层、存储引擎层）
（4）业务应用（库、表、索引、SQL语句、锁等）
（5）架构选型（高可用、读写分离、分布式、NoSQL）

2. 工具介绍

2.1 操作系统和硬件层面 
cpu             : top 、htop 、sar 、vmstat 
mem、swap         ：top 、 free  、vmstat 、sar 
IO（disk、net） ：iotop、iostat 


2.3 高级工具 
PT 
压测工具 ： FIO、mysqlslap、sysbench、tpc-C mysql tpc-H mysql ...
IS、PS、SYS

3.0 数据库实例

show processlist;
db01 [(none)]>show status\G;
show engine innodb status \G

3. 优化细节

3.1  硬件选型建议
3.1.1 服务器 ：
# MySQL  :  
PC Server: 
        OLTP: 在线事务处理系统。IO密集性，高并发。
            CPU ： 志强 16核+ 
            MEM :  64G+
            IO  :  
                磁盘IO: 
                    硬盘：   SAS 、 PCI-E SSD 、Flash 
                    RAID卡： RAID 5 、RAID 10
                网络IO： 网卡。单口单卡。bound网卡绑定（主备、轮询）。网络交换机要做堆叠。

阿里（RDS、DRDS 、PolarDB\PolarDB box)        

++++++++++++++++++++++++                    
OLAP：计算类的业务。CPU密集型。
            CPU:   I系列 主频高
阿里（RDS、DRDS 、PolarDB\PolarDB box)
腾讯云：     
# Oracle :  小机 （IBM PowerPC\HP小机）、PC Server、Oracle 一体机

+++++++++++++++++++++++++++
# 网络设备 
        网络交换机  ： vlan
        光纤交换机  ： zone
堆叠。




# 存储设备 
RAID 10。
注意： 不要过度条带化。可能导致IOPS过高，甚至被打满。
IOPS： 一块硬盘，最高每秒的IO次数。是个定值。

一般情况下，新规划架构时，一定要考虑好。

1-系统工具应用

1-CPU

top 
%Cpu(s): CPU的平均值使用率，按“1”会展开看到每颗cpu占比。
    us ：用户进程占用的CPU时间。我们希望看到这个越多越好。
    sy ：系统、内核工作时候占用的CPU比例。资源监控、分配、回收、系统调用。我们希望越低越好。
    id ：空闲CPU的时间。  
    wa ：花在等待上的时间。等资源：处理队列、IO。

数据库服务器： 
    如果： wa很高， 大几率 ： 大事务、全表扫描、随机IO过多、锁等待
    锁等待也会导致 sys过高。全表扫描也会导致us过高。
    wa ：花在等待上的时间。等资源：处理队列、IO

 mysqlslap --defaults-file=/etc/my.cnf --concurrency=10 --iterations=1 --create-schema='test' --query="select * from test.t100w where k2='FGCD'" engine=innodb --number-of-queries=1000 -uroot -p123 -verbose

2-MySQL 服务器 ，CPU 暴涨，排查思路

# 1. 找 mysql 的进程号
[root@db01 data]# cat /data/3306/data/db01.pid  #数据目录下或者ps
97798

# 2. 通过 进程找问题线程
top -Hp 97798  
 PID USER      PR  NI    VIRT    RES    SHR S %CPU %MEM     TIME+ COMMAND                                                                
 13974 mysql     20   0 1163280 254104  12948 R 10.9 12.5   1:03.22 mysqld        


OS_thread_ID  24331

# 3. 通过OS thread找到数据库内部线程

mysql> select thread_id,PROCESSLIST_ID ,THREAD_OS_ID  from performance_schema.threads where THREAD_OS_ID=24331;
+-----------+----------------+--------------+
| thread_id | PROCESSLIST_ID | THREAD_OS_ID |
+-----------+----------------+--------------+
|        82 |             57 |       121855 |
+-----------+----------------+--------------+


# 4. 通过 thread_id找到问题SQL语句 

mysql> select THREAD_ID ,SQL_TEXT from performance_schema.events_statements_history where THREAD_ID=82;
+-----------+------------------------------------------+
| THREAD_ID | SQL_TEXT                                 |
+-----------+------------------------------------------+
|        82 | select * from test.t100w where k2='FGCD' |
|        82 | select * from test.t100w where k2='FGCD' |
|        82 | select * from test.t100w where k2='FGCD' |
|        82 | select * from test.t100w where k2='FGCD' |
|        82 | select * from test.t100w where k2='FGCD' |
|        82 | select * from test.t100w where k2='FGCD' |
|        82 | select * from test.t100w where k2='FGCD' |
|        82 | select * from test.t100w where k2='FGCD' |
|        82 | select * from test.t100w where k2='FGCD' |
|        82 | select * from test.t100w where k2='FGCD' |
+-----------+------------------------------------------+

# MEM、SWAP 
MEM 主要看 avail Mem  、 buff/cache
64G  
不会超过90%

3-SWAP ： MySQL建议不使用。

什么时候算不

centos7 中默认物理内存70%, 时会使用swap。
centos6 中默认物理内存40% ,时会使用swap。


[root@db01 data]# cat /proc/sys/vm/swappiness 
30
[root@db01 data]# echo 0 >/proc/sys/vm/swappiness 
[root@db01 data]# cat /proc/sys/vm/swappiness 
0

/etc/sysctl.conf
上添加vm.swappiness=0（永久）
sysctl -p

4-IO

说明： 
deadline(适合SAS盘)
noop    (适合ssd,flash)
centos 7 默认是 deadline

查看：
cat   /sys/block/sda/queue/scheduler

修改： 
# 临时
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

其他建议：
    raid
    no lvm
    ext4或xfs
    ssd
提前规划好以上所有问题，减轻MySQL优化的难度。

5-实例层面优化(参数)

连接层 
--skip-networking   # 跳过网络TCP连接协议，如果你的应用和数据放在一起。
--skip-grant-tables # 跳过授权表
--skip-name-resolve # 跳过域名解析

vim /etc/my.cnf  
[mysqld]
skip-name-resolve
或者： 
skip-name-resolve=1

# 连接数：
mysql> select @@max_connections;
+-------------------+
| @@max_connections |
+-------------------+
|               151 |
+-------------------+
1 row in set (0.00 sec)

建议： 单节点阈值，3000-4000 

设置依据：
    show processlist;  # 当前正在并发的。

    #历史最高最大连接数
    mysql> show status like '%Max_used_connect%';


故障  ： too many  connections
IE  409错误。

6-连接线程回收

mysql> select @@wait_timeout;
作用： 非交互式链接，连接超时时间。

mysql> select @@interactive_timeout;
作用：交互式链接，连接超时时间。



彩蛋： 

案例1： 文件句柄
连接数设置不生效的问题，214问题。
/etc/security/limits.conf 
*　　soft　　nofile　　65536
*　　hard　　nofile　　65536



案例2：MySQL 连接长时间(7200和1200秒)无法释放 #备份原因
场景： MySQL 5.7  ， DELL730 E5-2650  96G内存  1主2从
Keepalive + LVS + 1主 2从 


处理方法：
ipvsadmin -l -timeout    #心跳检查时间
Timeout (tcp  tcpfin  udp ):  90 120 300  #心跳检查时间


net.ipv4.tcp_keepalive_time = 60   #心跳检查时间

7-会话级别的参数（每个连接都有关）

 thread_cache_size=100  （单位:个数）

拿内存换CPU时间。

依据： 
mysql>  show status like 'threads_%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Threads_cached    | 8     |     # 剩余的个数
| Threads_connected | 2     |     # 已经产生的线程
| Threads_created   | 4783  |     # 全新创建的线程个数
| Threads_running   | 1     |     #  
+-------------------+-------+

另外关注1：内存的剩余量。
另外关注2： CPU的 SYS

8-彩蛋： CPU SYS 暴增。

show processlist ; 
mysql>  show status like 'threads_%';

建议调大，不断观察、监控  show status like 'threads_%'、CPU  SYS、内存的剩余量。

9- innodb_thread_concurrenc==y 线程并发==

依据：  
1. 
在官方doc上，对于innodb_thread_concurrency的使用，也给出了一些建议，如下：
如果一个工作负载中，并发用户线程的数量小于64，建议设置innodb_thread_concurrency=0；
如果工作负载一直较为严重甚至偶尔达到顶峰，建议先设置innodb_thread_concurrency=128，
并通过不断的降低这个参数，96, 80, 64等等，直到发现能够提供最佳性能的线程数，
例如，假设系统通常有40到50个用户，但定期的数量增加至60，70，甚至200。你会发现，
性能在80个并发用户设置时表现稳定，如果高于这个数，性能反而下降。在这种情况下，
建议设置innodb_thread_concurrency参数为80，以避免影响性能。
如果你不希望InnoDB使用的虚拟CPU数量比用户线程使用的虚拟CPU更多（比如20个虚拟CPU），
建议通过设置innodb_thread_concurrency 参数为这个值（也可能更低，这取决于性能体现），
如果你的目标是将MySQL与其他应用隔离，你可以l考虑绑定mysqld进程到专有的虚拟CPU。
但是需 要注意的是，这种绑定，在myslqd进程一直不是很忙的情况下，可能会导致非最优的硬件使用率。在这种情况下，
你可能会设置mysqld进程绑定的虚拟 CPU，允许其他应用程序使用虚拟CPU的一部分或全部。
在某些情况下，最佳的innodb_thread_concurrency参数设置可以比虚拟CPU的数量小。
定期检测和分析系统，负载量、用户数或者工作环境的改变可能都需要对innodb_thread_concurrency参数的设置进行调整。

2. show processlist;  # 查看当前的并发连接数据队列。
建议： 先设置较小值。8 ，不断观察队列。

3. 观察 TOP中，每颗CPU的负载情况。
案例： top cpu负载十分不均匀

key_buffer_size

功能： 
     1. MyISAM索引缓冲。XXX
     2. 内存临时表缓冲。（join  group by   disticnt   union ）
     临时表： 
           1. 磁盘，ibtmpN
           2. 缓冲区
设定依据：  
mysql> show status like "created_tmp%";
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 10    |
| Created_tmp_files       | 6     |
| Created_tmp_tables      | 100   |
+-------------------------+-------+
磁盘临时表控制在10%以内， 如果超过，两种方案优化

1. 加大 key_buffer_size
tmp_table_size=200M

2. 业务上减少结果集大小。尽量控制查询条件，精细化。

糗事：

学员添加了监控项，监控：磁盘临时表百分比。告警。
每到夜黑风高的时候，都会手机告警。组里也有领导。
mysqldump 备份时，会大量使用磁盘临时表。

在调用mysqldump备份数据时，大概执行步骤如下：
180322 17:39:33       7 Connect     root@localhost on
7 Query       /*!40100 SET @@SQL_MODE='' */
7 Init DB     guo
7 Query       SHOW TABLES LIKE 'guo'
7 Query       LOCK TABLES `guo` READ /*!32311 LOCAL */
7 Query       SET OPTION SQL_QUOTE_SHOW_CREATE=1
7 Query       show create table `guo`
7 Query       show fields from `guo`
7 Query       show table status like 'guo'
7 Query       SELECT /*!40001 SQL_NO_CACHE */ * FROM `guo`
7 Query       UNLOCK TABLES

sort_buffer_size     ： 排序缓冲区。 索引设计不好。    order by  desc (8.0解决) 、group by 自动排序(8.0解决) 
join_buffer_size     :  BNL  BKA 。  索引设计不好。   
read_rnd_buffer_size :  MRR 


优化目标： 越小越好。
方式： 1. 减少排序结果集  2. 优化索引

2-SQL层面

query_cache_size :
作用：SQL层查询缓存，select HASH_ID + 结果。

学员案例： 
案例 ： 开QC ，导致性能降低。 QPS ，TPS降低。
没开起的时候。QPS 2000 TPS 500 
开了之后直接降低到 800，200 

为什么呢？
分区表。Query Cache 不支持。
select * from city where id=10

1- InnoDB参数

# innodb_adaptive_hash_index （AHI） 
# innodb_buffer_pool_instances=4-8个
# innodb_buffer_pool_size =50%-80%，建议不要超过75% 
# innodb_file_per_table  # 独立表空间
# innodb_data_file_path # 共享表空间，建议2-3个  512M-4G
# innodb_doublewrite    
# 双写。8.0.20之前在，ibdataN ,2M空间。checkpoint时，分两次，每次1M写入DWB。然后刷脏页到ibd
# innodb_flush_log_at_trx_commit
# 双一之一： 
    1. 每次事务提交，立即触发刷写，立即fsync()将redo落盘。
    0. 每秒事务提交，立即触发刷写，立即fsync()将redo落盘。 
    2. 每次事务提交，立即触发刷写OS cache，每秒fsync()将redo落盘。 

# innodb_flush_method
  fsync   :  redo和数据刷盘都经历OS cache，再到磁盘
  O_Direct： 数据直接刷盘，日志经历OS cache ，再到磁盘

2-看磁盘类型设置

innodb_io_capacity  #一次性写多少到磁盘 并发多少数据也
innodb_io_capacity_max

| innodb_io_capacity                                       | 200                                                                                      |
| innodb_io_capacity_max                                   | 2000   

3-锁等待时间。

innodb_lock_wait_timeout=10

4- redo有关 ，TPS有关

innodb_log_buffer_size=文件倍数   
innodb_log_file_size=512M-4G
innodb_log_files_in_group=2-4组         



# checkpoint 相关机制
# 1. sharp ckpt （必然发生的）
数据库关机触发。

# 2. fuzzy ckpt （可能发生的）
# master thread checkpoint
每1秒有可能
每10秒有可能

# flush_lru_list checkpoint
innodb_lru_scan_depth=1024

# async/sync flush checkpoint
2个redo ,1G ==>    2G
75%    1.5G    ---》 可能发生的
90%    1.8G    ---》 必然


# dirty page too much checkpoint
innodb_max_dirty_pages_pct=75%  

# innodb_open_files   InnoDB能够打开文件句柄。

# innodb_temp_data_file_path               | ibtmp1:12M:autoextend  |


# undo 的设置：3-4个,1-4G左右
innodb_max_undo_log_size                  | 1073741824 |
innodb_undo_directory                     | ./         |
innodb_undo_log_truncate                 | OFF        |
innodb_undo_logs                        | 128        |
innodb_undo_tablespaces                  | 0          |

3-业务层面优化建议

  用户权限优化建议 
规范： 
     1. 用户要和业务有关。
     例如：zbx_user  zbx_admin ..
     2. 密码复杂度
     建议3种复杂度，12位以上。
     定期进行更改。
     3.用户密码专人管理，超级管理员多人持有部分。
     4.开发或业务用户，不直接登陆数据库。经过审计平台、堡垒机等机制。
     5. 权限最小化。
3.4.2 SQL规范 
     1. schema 设计
        库： 大小写、业务相关、字符集、校对规则。
        表： 一大堆规范、范式。    
     2. DROP操作避免使用
     3. ON-DDL
     4. SQL 优化
     5. 合理的优化器算法
     6. 大事务：批量更新、批量删除、批量的插入，夜里去做，拆分小事务。


3.4.3 索引引用
    聚簇索引数字自增
    降低索引树高度
    联合索引覆盖长度
    减少回表。
    创建索引规范。
    不走索引情况。
    慢日志分析。

3.4.4 锁方面优化 


3.5 架构优化 
主从       ：5.7 以上GTID 。 
高可用     : MHA 、PXC 、ORCH 、InnoDB CLuster（MGR）
读写分离   : ProxySQL  
分布式架构 : Mycat 、 DBLE

NoSQL 架构 ：MongoDB、Redis、ES

1-锁的监控及处理思路

背景: 
硬件环境: DELL R720,E系列16核,96G MEM,SAS*900G*6,RAID10
在例行巡检时,发现9-11点时间段的CPU压力非常高(80-90%)

2. 项目的职责
    2.1 通过top详细排查,发现mysqld进程占比达到了700-800%
    2.2 其中有量的CPU是被用作的SYS和WAIT,us处于正常
    2.3 怀疑是MySQL 锁 或者SQL语句出了问题
    2.4 经过排查slowlog及锁等待情况,发现有大量锁等待及少量慢语句    
    (1) pt-query-diagest 查看慢日志  
    (2) 锁等待有没有?
    db03 [(none)]>show status like 'innodb_row_lock%';
    +-------------------------------+-------+
    | Variable_name                 | Value |
    +-------------------------------+-------+
    | Innodb_row_lock_current_waits | 0     |
    | Innodb_row_lock_time          | 0     |
    | Innodb_row_lock_time_avg      | 0     |
    | Innodb_row_lock_time_max      | 0     |
    | Innodb_row_lock_waits         | 0     |
    +-------------------------------+-------+
    情况一:
            有100多个current_waits,说明当前很多锁等待情况
    情况二:
            1000多个lock_waits,说明历史上发生过的锁等待很多
    2.5 查看那个事务在等待(被阻塞了)
    2.6 查看锁源事务信息(谁锁的我)
    2.7 找到锁源的thread_id 
    2.8 找到锁源的SQL语句
3. 找到语句之后,和应用开发人员进行协商   
    (1)
    开发人员描述,此语句是事务挂起导致
    我们提出建议是临时kill 会话,最终解决问题
    (2) 
    开发人员查看后,发现是业务逻辑问题导致的死锁,产生了大量锁等待
    临时解决方案,将阻塞事务的会话kill掉.
    最终解决方案,修改代码中的业务逻辑
项目结果:
    经过排查处理,锁等待的个数减少80%.解决了CPU持续峰值的问题.

    锁等待   db01 [(none)]>show variables like 'innodb_lock_%';

    +--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| innodb_lock_wait_timeout       | 50    |
| innodb_locks_unsafe_for_binlog | OFF   |
+--------------------------------+-------+

2-锁监控设计到的命令:

db01 [test]>show status like '%Innodb_row_lock%';
| Variable_name                 | Value |
+-------------------------------+-------+
| Innodb_row_lock_current_waits | 1     |
| Innodb_row_lock_time          | 11019 |
| Innodb_row_lock_time_avg      | 5509  |
| Innodb_row_lock_time_max      | 11019 |
| Innodb_row_lock_waits         | 2     |
+-------------------------------+-------+

select * from information_schema.innodb_trx;

select * from sys.innodb_lock_waits;
db01 [information_schema]>select  locked_table,  locked_index , locked_type , waiting_trx_id , waiting_query,blocking_pid,sql_kill_blocking_query,sql_kill_blocking_connection from sys.innodb_lock_waits\G

*************************** 1. row ***************************
                locked_table: `oldboy`.`t_100w`
                locked_index: GEN_CLUST_INDEX
                 locked_type: RECORD
              waiting_trx_id: 36476
               waiting_query: delete from t_100w where id<100
                blocking_pid: 4159
     sql_kill_blocking_query: KILL QUERY 4159
sql_kill_blocking_connection: KILL 4159
1 row in set, 3 warnings (0.00 sec)



select * from performance_schema.threads;
db01 [oldboy]>select * from performance_schema.threads where  PROCESSLIST_ID=4159\G


*************************** 1. row ***************************
          THREAD_ID: 4184
               NAME: thread/sql/one_connection
               TYPE: FOREGROUND
     PROCESSLIST_ID: 4159
   PROCESSLIST_USER: root
   PROCESSLIST_HOST: localhost
     PROCESSLIST_DB: oldboy
PROCESSLIST_COMMAND: Query
   PROCESSLIST_TIME: 0
  PROCESSLIST_STATE: Sending data
   PROCESSLIST_INFO: select * from performance_schema.threads where  PROCESSLIST_ID=4159
   PARENT_THREAD_ID: NULL
               ROLE: NULL
       INSTRUMENTED: YES
            HISTORY: YES
    CONNECTION_TYPE: Socket
       THREAD_OS_ID: 23254
1 row in set (0.00 sec)

select * from performance_schema.events_statements_history;
db01 [oldboy]>select * from performance_schema.events_statements_history where THREAD_ID=4184\G



              THREAD_ID: 28
               EVENT_ID: 7
           END_EVENT_ID: 7
             EVENT_NAME: statement/sql/update
                 SOURCE: 
            TIMER_START: 155851067318000
              TIMER_END: 166897698699000
             TIMER_WAIT: 11046631381000
              LOCK_TIME: 93000000
               SQL_TEXT: NULL
                 DIGEST: ddce0acdde4f229129910fe210d79711
            DIGEST_TEXT: UPDATE `t100w` SET `num` = ? WHERE `id` = ? 

4- MySQL PT工具使用

[root@db01 ~]# yum install -y percona-toolkit-3.1.0-2.el7.x86_64.rpm

1. 系统配置总览

pt-summary 
作用： 系统同状态总览。


2. 表归档：pt-archiver
# 重要参数
--limit 100         每次取100行数据用pt-archive处理    
--txn-size  100     设置100行为一个事务提交一次，    
--where 'id<3000'   设置操作条件    
--progress 5000     每处理5000行输出一次处理信息    
--statistics        输出执行过程及最后的操作统计。
--charset=UTF8      指定字符集为UTF8—这个最后加上不然可能出现乱码。    
--bulk-delete       批量删除source上的旧数据(例如每次1000行的批量删除操作)
--for-update：      在每个select语句后面加入for update

2-使用案例：

归档到数据库(本地)

db01 [test]>grant all on *.* to root@'10.0.1.%' identified by '123';
---------------
db01 [world]>create table city2   like city ;
Query OK, 0 rows affected (0.06 sec)
---------------------------
pt-archiver --source h=10.0.1.51,D=world,t=city,u=root,p=123 --dest h=10.0.0.51,D=world,t=city2,u=root,p=123 --where 'id<1000' --no-check-charset --no-delete --limit=100 --commit-each --progress 200 --statistics

# 归档到数据库(异地) 3307 节点 远程登录用户
pt-archiver --source h=10.0.1.51,D=world,t=city,u=root,p=123 --dest h=10.0.1.51,D=world,t=city2,u=root,P=3307,p=123 --where 'id<1000' --no-check-charset --no-delete --limit=100 --commit-each --progress 200 --statistics

# 只清理数据  删除
pt-archiver --source h=10.0.0.51,D=world,t=city,u=root,p=123 --where 'id<1000' --purge --limit=1 --no-check-charset

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

3. pt-osc Online DDL

原理： 
1、检查更改表是否有主键或唯一索引，是否有触发器
2、检查修改表的表结构，创建一个临时表，在新表上执行ALTER TABLE语句
3、在源表上创建三个触发器分别对于INSERT UPDATE DELETE操作
4、从源表拷贝数据到临时表，在拷贝过程中，对源表的更新操作会写入到新建表中
5、将临时表和源表rename（需要元数据修改锁，需要短时间锁表）
6、删除源表和触发器，完成表结构的修改。

4-pt-osc工具限制

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

5-pt-osc之alter语句限制

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

6-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=root --password=123 --host=10.0.0.51 --alter "add column age int default 0" D=world,t=city --print --execute

5-主从一致性校验

（1） 创建数据库
Create database pt CHARACTER SET utf8;


# 创建用户checksum并授权
GRANT ALL ON *.* TO 'checksum'@'10.0.1.%' IDENTIFIED BY 'checksum';
flush privileges;

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

1-小坑

所有库： 
autocommit=1

从库 ： 
report_host=10.0.0.51
report_port=3309

# 应用：
针对表校验： 
pt-table-checksum --nocheck-replication-filters --no-check-binlog-format --replicate=pt.checksums --create-replicate-table --databases=taobao --tables=a h=10.0.0.51,u=checksum,p=checksum,P=3307



针对库校验： 
pt-table-checksum --nocheck-replication-filters --no-check-binlog-format --replicate=pt.checksums --create-replicate-table --databases=test  h=10.0.1.51,u=checksum,p=checksum,P=3306

2-脚本模板：

#!/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 -u'checksum' -p'checksum'
-h'10.0.0.11' >> /root/db/checksum.log
date >> /root/db/checksum.log

6-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 h=10.0.0.51,u=root,p=123,P=3307 --print
pt-table-sync --replicate=pt.checksums h=10.0.0.51,u=root,p=123,P=3307 --execute

1-显示主从结构：pt-slave-find

[root@db01 tmp]# pt-slave-find -h10.0.0.51  -P3307 -uchecksum -pchecksum
10.0.0.51
Version         5.7.28-log
Server ID       51
Uptime          27:57 (started 2020-05-15T13:24:15)
Replication     Is not a slave, has 1 slaves connected, is not read_only
Filters         
Binary logging  ROW
Slave status    
Slave mode      STRICT
Auto-increment  increment 1, offset 1
InnoDB version  5.7.28

+- 10.0.0.52
   Version         5.7.28-log
   Server ID       52
   Uptime          28:18 (started 2020-05-15T13:23:54)
   Replication     Is a slave, has 0 slaves connected, is not read_only
   Filters         
   Binary logging  ROW
   Slave status    0 seconds behind, running, no errors
   Slave mode      STRICT
   Auto-increment  increment 1, offset 1
   InnoDB version  5.7.28
[root@db01 tmp]# 

2-检查指定表的重复索引

pt-duplicate-key-checker 

db01 [world]>alter table city add index idx(countrycode,population);


[root@db01 tmp]#pt-duplicate-key-checker  --host=10.0.1.61 --user='checksum' --password='checksum'  --databases=world --tables=city
# ########################################################################
# world.city                                                              
# ########################################################################


# CountryCode is a left-prefix of idx
# Key definitions:
#   KEY `CountryCode` (`CountryCode`),
#   KEY `idx` (`CountryCode`,`Population`),
# Column types:
#      `countrycode` char(3) not null default ''
#      `population` int(11) not null default '0'
# To remove this duplicate index, execute:
ALTER TABLE `world`.`city` DROP INDEX `CountryCode`;

# ########################################################################
# Summary of indexes                                                      
# ########################################################################

# Size Duplicate Indexes   12564
# Total Duplicate Indexes  1
# Total Indexes            4
[root@db01 tmp]# 

3- 监控主从延时

pt-heartbeat 

主库： 
pt-heartbeat --user=root --ask-pass --host=10.0.1.61 --port=3307   --create-table -D taobao   --interval=1  --update  --replace  --daemonize


从库： 

pt-heartbeat --user=root --ask-pass --host=10.0.1.61 --port=3309  -D taobao --table=heartbeat --monitor 


8. # pt-show-grants
pt-show-grants -h10.0.1.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;

0.00s [ 20.10s,  4.02s,  1.34s ]
0.00s [ 20.10s,  4.02s,  1.34s ]
0.00s [ 20.10s,  4.02s,  1.34s ]
0.00s [ 20.10s,  4.02s,  1.34s ]
0.00s [ 20.10s,  4.02s,  1.34s ]
0.00s [ 20.10s,  4.02s,  1.34s ]
0.00s [ 20.10s,  4.02s,  1.34s ]
0.00s [ 20.10s,  4.02s,  1.34s ]
0.00s [ 20.10s,  4.02s,  1.34s ]
0.00s [ 20.10s,  4.02s,  1.34s ]
0.00s [ 20.10s,  4.02s,  1.34s ]
0.02s [ 20.10s,  4.02s,  1.34s ]
0.00s [ 20.10s,  4.02s,  1.34s ]
0.00s [ 20.10s,  4.02s,  1.34s ]
0.00s [ 20.10s,  4.02s,  1.34s ]
0.00s [ 20.10s,  4.02s,  1.34s ]
0.00s [ 20.10s,  4.02s,  1.34s ]
0.00s [ 20.10s,  4.02s,  1.34s ]
0.00s [ 20.10s,  4.02s,  1.34s ]
0.00s [ 20.10s,  4.02s,  1.34s ]

---

# 给出参数建议： 
pt-variable-advisor 10.0.0.51 -uchecksum -pchecksum