通过top -p mysqlpid -H 可以找到thread_os_id ->

                    通过thread_os_id 和  performance_schema.threads 找到thread_id ->
                     通过thread_id和performance_schema.events_statements_current 找到sql语句

[root@node2 ~]# ps -ef | grep -i mysql
mysql 2296 1211 18 14:17 ? 00:03:46 /home/db/mysql/product/bin/mysqld --basedir=/home/db/mysql/product --datadir=/mysqldata/data --plugin-dir=/home/db/mysql/product/lib/plugin --user=mysql --log-error=/mysqldata/logs/mysql_error.log --pid-file=/mysqldata/data/mysqld.pid --socket=/home/db/mysql/product/mysql.sock --port=13306

[root@node2 ~]# top -p 2296 -H
top - 14:59:55 up 42 min, 4 users, load average: 0.41, 0.81, 1.39
Threads: 67 total, 2 running, 65 sleeping, 0 stopped, 0 zombie
%Cpu(s): 94.7 us, 4.7 sy, 0.0 ni, 0.0 id, 0.0 wa, 0.0 hi, 0.7 si, 0.0 st
KiB Mem : 2238208 total, 77696 free, 943520 used, 1216992 buff/cache
KiB Swap: 2621436 total, 2621436 free, 0 used. 1139404 avail Mem
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAN
2966 mysql 20 0 6212608 868916 30128 R 97.3 38.8 11:46.51 mysqld
2460 mysql 20 0 6212608 868916 30128 R 0.3 38.8 0:09.28 mysqld
2296 mysql 20 0 6212608 868916 30128 S 0.0 38.8 0:00.79 mysqld

系统PID=2966 占用CPU 97%
通过performance_schema.threads.thread_os_id=2966可以找到thread_id,processlist_id ;

+-----------+---------------------------+----------------+--------------+

通过performance_schema.events_statements_current.thread_id = 78 可以找到当前占用cpu的SQL：

DIGEST_TEXT 的长度由变量max_digest_length控制:

参数group_replication_consistency：
1.EVENTUAL
RO 和 RW事务不会等待在当前之前事务执行完才执行。RW事务不会等待其他成员执行完，意味着一个事物可能单独在一个成员中存在比其它先。当发生failover时，新primary能在当前之前的事务执行被应用之前，接收新的RO和RW. RO可以结果是旧数据，RW可能导致冲突而引起回滚。

2.BEFORE_ON_PRIMARY_FAILOVER
新选取的主库，当前旧主库的blacklog时，新的RO和RW事各会一直held住，直到backlog被应用完。
当failover时，client能看到主库最新的值，确保一致性。但会client会在处理blacklog时产生延时，延时的大小取决于blacklog日志大小。

3。BEFORE
RW事务会等待之前事务完成才会被应用，RO事务会等待之前事物完成才会被执行。确保读事务永远是最新值。This reduces the overhead of synchronization on every RW transaction, by ensuring synchronization is used only on RO transactions.
（读少写多，一致性包含BEFORE_ON_PRIMARY_FAILOVER）

4。AFTER
RW事务一直等到其它成员已经应用后。对RO事务没影响。当一个事务本地提交时，随后其它节点能读到最新的值。主导RO。This reduces the overhead of synchronization on every RO transaction, by ensuring synchronization is used only on RW transactions
（读多写少，一直性包含BEFORE_ON_PRIMARY_FAILOVER）

5。BEFORE_AND_AFTER
RW事务等待 1。之前事务完成，2。更改被其他成员应用。RO会等待之前事务完成才发生。
（BEFORE_ON_PRIMARY_FAILOVER ）

BEFORE / BEFORE_AND_AFTER : RO,RW
AFTER : no impact RO

怎么选：
1.读多写少 -> AFTER
2 读少写多 -》 BEFORE
3。总是读到最新值 -》 BEFORE

1. 主导RO事务，只要rw事务一旦提交，随后能读到最新的 AFTER
2. 主导RO事务，RW事总是读最新数据，一旦提交并应用到其他成员中，随后读最新数据　－》BEFORE_AND_AFTER
   ６.　系统处理时，一些需求要一致性，一些不需要一致性

SET @@SESSION.group_replication_consistency= 'AFTER'
SET @@SESSION.group_replication_consistency= 'EVENTUAL'
７.在６基础之上，有些在需求要读最新值
SET @@SESSION.group_replication_consistency= ‘BEFORE’

下面测试mysql_safe启动失败后的问题：
5.7下运行：
CREATE TABLE employees (
id INT NOT NULL,
fname VARCHAR(30),
lname VARCHAR(30),
hired DATE NOT NULL DEFAULT '1970-01-01',
separated DATE NOT NULL DEFAULT '9999-12-31',
job_code INT NOT NULL,
store_id INT NOT NULL
) engine=Myisam
PARTITION BY RANGE (store_id) (
PARTITION p0 VALUES LESS THAN (6),
PARTITION p1 VALUES LESS THAN (11),
PARTITION p2 VALUES LESS THAN (16),
PARTITION p3 VALUES LESS THAN (21)
) ;

替换成8.0的程序，运行
mysql@node1:/home/db/mysql$ mysqld_safe --user=mysql --datadir=/mysqldata/data
2020-02-22T03:37:50.656134Z mysqld_safe Logging to '/mysqldata/logs/mysql_error.log'.
2020-02-22T03:37:50.682648Z mysqld_safe Starting mysqld daemon with databases from /mysqldata/data
2020-02-22T03:38:18.557227Z mysqld_safe mysqld from pid file /mysqldata/data/mysqld.pid ended

error.log显示：
2020-02-22T11:38:13.629178+08:00 0 [Warning] [MY-013129] [Server] A message intended for a client cannot be sent there as no client-session is attached. Therefore, we're sending the information to the error-log instead: MY-001287 - 'validate password plugin' is deprecated and will be removed in a future release. Please use validate_password component instead
2020-02-22T11:38:15.154654+08:00 2 [ERROR] [MY-013140] [Server] The 'partitioning' feature is not available; you need to remove '--skip-partition' or use MySQL built with '-DWITH_PARTITION_STORAGE_ENGINE=1'
2020-02-22T11:38:15.154693+08:00 2 [ERROR] [MY-013140] [Server] Can't find file: './test/employees.frm' (errno: 0 - Success)
2020-02-22T11:38:15.154703+08:00 2 [ERROR] [MY-013137] [Server] Can't find file: './test/employees.frm' (OS errno: 0 - Success)
2020-02-22T11:38:15.154711+08:00 2 [ERROR] [MY-010348] [Server] Error in reading file ./test/employees.frm
2020-02-22T11:38:15.154726+08:00 2 [ERROR] [MY-010758] [Server] Error in creating TABLE_SHARE from employees.frm file.
2020-02-22T11:38:16.311130+08:00 0 [ERROR] [MY-010022] [Server] Failed to Populate DD tables.
2020-02-22T11:38:16.311156+08:00 0 [ERROR] [MY-010119] [Server] Aborting
2020-02-22T11:38:16.614585+08:00 0 [Warning] [MY-011068] [Server] The syntax 'validate password plugin' is deprecated and will be removed in a future release. Please use validate_password component instead.
2020-02-22T11:38:18.515869+08:00 0 [System] [MY-010910] [Server] /home/db/mysql/product/bin/mysqld: Shutdown complete (mysqld 8.0.11) MySQL Community Server - GPL.
2020-02-22T03:38:18.557227Z mysqld_safe mysqld from pid file /mysqldata/data/mysqld.pid ended

重新用5.7程序启动，登录数据库解决问题
mysql@node1:/home/db/mysql$ mysqld_safe --user=mysql --datadir=/mysqldata/data
root@localhost 11:48:37 [(none)]>use test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed, 1 warning
root@localhost 11:49:10 [test]>alter table test.employees engine=innodb ;
Query OK, 0 rows affected (0.18 sec)
Records: 0 Duplicates: 0 Warnings: 0
root@localhost 11:49:11 [test]>shutdown ;
Query OK, 0 rows affected (0.00 sec)

替换8.0启动
mysql@node1:/home/db/mysql$ mysqld_safe --user=mysql --datadir=/mysqldata/data

升级：
mysql_upgrade -uroot -pRoot@0101

重启：
mysqladmin -u root -pRoot@0101 shutdown
service mysqld start

注意：
1、替换为8.0程序启动失败时，数据库可以退回到5.7，手动解决不兼容问题，重复操作可成功
2、替换为8.0程序启动成功时，数据库不可以退回到5.7

5.7 -> 8.0
checkForServerUpgrade （mysql shell 8.0工具）用法：
[root@node1 mysql]# yum -y isntall mysql-shell-8.0.19-1.el7.x86_64.rpm

mysql@node1:/home/db/mysql$ mysqlsh -- util check-for-server-upgrade { --user=root --host=localhost --port=13306 } --target-version=8.0.11 --output-format=JSON --config-path=/etc/my.cnf

工具很详细地说明了，升级之前要修改的错误，以及版本升级后默认参数更改的注意事项

一、5.7更新前检查：

1、 table和trigger检查
obsolete data types,
obsolete functions,
orphan .frm files,
InnoDB tables that use nonnative partitioning, or triggers that have a missing or empty definer or an invalid creation context (indicated by the character_set_client, collation_connection, Database Collation attributes displayed by SHOW TRIGGERS or the INFORMATION_SCHEMA TRIGGERS table)

mysqlcheck -u root -p --all-databases --check-upgrade
2、 没有不支持引擎的分区表
SELECT TABLE_SCHEMA, TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE ENGINE NOT IN ('innodb', 'ndbcluster')
AND CREATE_OPTIONS LIKE '%partitioned%';
办法：
ALTER TABLE table_name ENGINE = INNODB;

ALTER TABLE table_name REMOVE PARTITIONING;

3、 在5.7的mysql系统表空间中没有表与8.0数字典同名的
SELECT TABLE_SCHEMA, TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE LOWER(TABLE_SCHEMA) = 'mysql'
and LOWER(TABLE_NAME) IN
(
'catalogs',
'character_sets',
'collations',
'column_statistics',
'column_type_elements',
'columns',
'dd_properties',
'events',
'foreign_key_column_usage',
'foreign_keys',
'index_column_usage',
'index_partitions',
'index_stats',
'indexes',
'parameter_type_elements',
'parameters',
'resource_groups',
'routines',
'schemata',
'st_spatial_reference_systems',
'table_partition_values',
'table_partitions',
'table_stats',
'tables',
'tablespace_files',
'tablespaces',
'triggers',
'view_routine_usage',
'view_table_usage'
);
显示出来有同名，必须改名字
e.g
RENAME TABLE t3 to tt3 ;

4、 没有外键约束名字超过64个字符
SELECT TABLE_SCHEMA, TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME IN
(SELECT LEFT(SUBSTR(ID,INSTR(ID,'/')+1),
INSTR(SUBSTR(ID,INSTR(ID,'/')+1),'_ibfk_')-1)
FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN
WHERE LENGTH(SUBSTR(ID,INSTR(ID,'/')+1))>64);

超过64个字符更改

5、 没有table或procedure在enum 或 set 字段长度超过255个字符或1020字节
6、 5.7中没有安装在8.0中不支持的特征
6.1 表的存储引擎在8.0中是不支持
6.2 数据开启的选项和系统参数在8.0中是被移除的

二、in-place更新
1、提交或回滚XA事务
2、加密表空间
ALTER INSTANCE ROTATE INNODB MASTER KEY;
3、设置innodb_fast_shutdown
SET GLOBAL innodb_fast_shutdown = 1; -- fast shutdown
SET GLOBAL innodb_fast_shutdown = 0; -- slow shutdown
4、关库
mysqladmin -u root -p shutdown
5、解压MySQL8.0二进制安装，并替换旧

6、开启mysql8.0，用现在的数据目录
mysqld_safe --user=mysql --datadir=/path/to/existing-datadir
如果有加密表空间，带参数--early-plugin-load指定加密插件

开启时，自动清理工作：
6.1 在—datadir目录，创建一个目录backup_metadata_57，迁移.frm, .par, .TRG, .TRN, or .isl 到backup_metadata_57
backup_metadata_57 保留旧文件系统的结构
6.2 在mysql数据库里，重命名event和proc 表 为event_backup_57 和proc_backup_57

如果这步失败，服务会恢复对--datadir的所有更改。这种情况，你应该删除所有redo log ,用相同的—datadir 开启数据库，并修改问题。用slow shutdown关闭 mysql5.7并再次开启msyql8.0 服务

7、 如果8.0开启成功，执行mysql_upgrade
mysql_upgrade -u root –p
mysql_upgrade：
7.1会检查所有与当前版本不兼容的数据库里的表，
7.2执行5.7升到8.0之间需求的修改，
7.3 更新Performance Schema, INFORMATION_SCHEMA 和 sys schema objects到8.0

Mysql_upgrade 不更新help table中内容

8、 重启数据库，确保所有的更改生效
mysqladmin -u root -p shutdown
mysqld_safe --user=mysql --datadir=/path/to/existing-datadir

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

information_schema库中有三个关于锁的表:

• innodb_trx ## 当前运行的所有事务
• innodb_locks ## 当前出现的锁
• innodb_lock_waits ## 锁等待的对应关系

解决办法

select * from information_schema.innodb_trx;
select * from information_schema.innodb_locks;
select * from information_schema.innodb_lock_waits;
-- 找到一直没有提交的事务
kill <trx_mysql_thread_id>