MOP 系列|MOP 三种主流数据库常用 SQL(二)

select user,host,plugin from mysql.user;

create database cc_xxl_job;
CREATE USER 'cc_xxl_job'@'%' IDENTIFIED by 'Cck8d90H6L#g';
GRANT ALL PRIVILEGES ON `cc_xxl_job`.* TO `cc_xxl_job`@`%`;
revoke all PRIVILEGES on `cc_xxl_job`.* from `cc_xxl_job`@`%`;

show create user cc_xxl_job;
show grants for cc_xxl_job;
alter user 'cc_xxl_job'@'%' IDENTIFIED by 'Cck8d9H6L#g';

show variables where variable_name in('innodb_buffer_pool_size','innodb_log_buffer_size','innodb_additional_mem_pool_size','key_buffer_size','query_cache_size' );
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| innodb_buffer_pool_size | 33554432 |
| innodb_log_buffer_size  | 8388608  |
| key_buffer_size         | 33554432 |
+-------------------------+----------+
3 rows in set (0.00 sec)

SELECT table_schema as DB_NAME,
concat(round(sum(DATA_LENGTH/1024/1024),2),'MB') as DB_DATA_SIZE,
concat(round(sum(INDEX_LENGTH/1024/1024),2),'MB') as DB_INDEX_SIZE,
concat(round(sum((DATA_LENGTH+INDEX_LENGTH)/1024/1024),2),'MB') as DB_TOTAL_SIZE
from information_schema.TABLES group by table_schema;

select
table_schema as '数据库',
sum(table_rows) as '记录数',
sum(truncate(data_length/1024/1024, 2)) as '数据容量(MB)',
sum(truncate(index_length/1024/1024, 2)) as '索引容量(MB)'
from information_schema.tables
group by table_schema
order by sum(data_length) desc, sum(index_length) desc;

select
table_schema as '数据库',
table_name as '表名',
table_rows as '记录数',
truncate(data_length/1024/1024, 2) as '数据容量(MB)',
truncate(index_length/1024/1024, 2) as '索引容量(MB)'
from information_schema.tables
order by data_length desc, index_length desc;

--例：查看 test库容量大小
select
table_schema as '数据库',
sum(table_rows) as '记录数',
sum(truncate(data_length/1024/1024, 2)) as '数据容量(MB)',
sum(truncate(index_length/1024/1024, 2)) as '索引容量(MB)'
from information_schema.tables
where table_schema='test';

例：查看test库各表容量大小
select
table_schema as '数据库',
table_name as '表名',
table_rows as '记录数',
truncate(data_length/1024/1024, 2) as '数据容量(MB)',
truncate(index_length/1024/1024, 2) as '索引容量(MB)'
from information_schema.tables
where table_schema='test'
order by data_length desc, index_length desc;

select table_schema,table_name,round((data_length+index_length)/1024/1024,2) as 'SIZE_MB',table_rows,engine from information_schema.tables where table_schema not in ('sys','mysql','information_schema','performance_schema') order by 3 desc limit 20 ;

select TABLE_SCHEMA,TABLE_NAME,TABLE_ROWS from information_schema.TABLES where TABLE_SCHEMA='test';

show global status like 'open%tables%';

select * from information_schema.PROCESSLIST where info is not null and time > 0;

show processlist;
show full processlist;
select ID,USER,DB,HOST,COMMAND,STATE,INFO,TIME from information_schema.PROCESSLIST where db='jiekexu';

select concat('kill ',id,';'),host,user,command,time,state,info from information_schema.processlist
where command !='Sleep' and user not in ('repl','system user','event_scheduler')
order by time desc limit 10;

performance_schema.replication_group_member_stats
performance_schema.replication_group_members
performance_schema.replication_connection_status
performance_schema.replication_applier_status
select * from performance_schema.replication_group_members;
--查看当前节点是否为主节点
SELECT IF((SELECT @@server_uuid) = (SELECT VARIABLE_VALUE FROM performance_schema.global_status
WHERE VARIABLE_NAME= 'group_replication_primary_member'), 1, 0) as is_primary_node;
--查看主节点信息
SELECT *
FROM performance_schema.replication_group_members
WHERE MEMBER_ID = (
SELECT VARIABLE_VALUE
FROM performance_schema.global_status
WHERE VARIABLE_NAME= 'group_replication_primary_member'
);

select case 
  when min_commit_timestamp is null then 0
  else unix_timestamp(now(6)) - unix_timestamp(min_commit_timestamp)
 end as seconds_behind_master
from (
 select min(applying_transaction_original_commit_timestamp) as min_commit_timestamp
 from performance_schema.replication_applier_status_by_worker
 where applying_transaction <> ''
) t;

select * from mysql.innodb_table_stats where database_name='JiekeXu' and table_name='tab_name'; 
--索引统计信息
select * from mysql.innodb_index_stats where database_name='JiekeXu' and table_name='tab_name' and index_name='idx_name';

select * from performance_schema.setup_instruments where name like 'stage/innodb/alter%';
select * from performance_schema.setup_consumers where name like '%stages%';

--如果上面查询结果为NO，则需要做如下配置：
update set_instrucments set enabled = 'YES' where name like 'stage/innodb/alter%';
update set_consumers set enabled = 'YES' where name like '%stages%';

-- 查询 DDL 执行的进度：
select stmt.sql_text,
       stage.event_name,
       concat(work_completed, '/', work_estimated) as progress,
       concat(round(100 * work_completed / work_estimated, 2), ' %') as processing_pct,
       sys.format_time(stage.timer_wait) as time_costs,
       concat(round((stage.timer_end - stmt.timer_start) / 1e12 *
                    (work_estimated - work_completed) / work_completed,
                    2),
              ' s') as remaining_seconds
  from performance_schema.events_stages_current     stage,
       performance_schema.events_statements_current stmt
 where stage.thread_id = stmt.thread_id
   and stage.nesting_event_id = stmt.event_id\G

SELECT TABLE_SCHEMA,TABLE_NAME,TABLE_COLLATION
 from information_schema.TABLES
WHERE TABLE_COLLATION NOT LIKE 'utf8%'
 AND table_schema NOT IN ('information_schema' ,'mysql','performance_schema', 'sys');

SELECT T1.TABLE_SCHEMA,
       T1.TABLE_NAME
FROM INFORMATION_SCHEMA.COLUMNS T1 JOIN INFORMATION_SCHEMA.TABLES T2 ON T1.TABLE_SCHEMA=T2.TABLE_SCHEMA AND T1.TABLE_NAME=T2.TABLE_NAME
WHERE T1.TABLE_SCHEMA NOT IN ('sys','mysql','information_schema','performance_schema')
AND T2.TABLE_TYPE='BASE TABLE'
GROUP BY T1.TABLE_SCHEMA,
         T1.TABLE_NAME HAVING group_concat(COLUMN_KEY) NOT REGEXP 'PRI|UNI';

SELECT  TABLE_SCHEMA , COUNT(TABLE_NAME) 
FROM information_schema.VIEWS
WHERE TABLE_SCHEMA not in ('mysql','information_schema','performance_schema','sys')
GROUP BY TABLE_SCHEMA ;
SELECT TABLE_SCHEMA,TABLE_NAME AS VIEW_NAME
FROM information_schema.VIEWS
WHERE TABLE_SCHEMA not in ('mysql','information_schema','performance_schema','sys')
ORDER BY TABLE_SCHEMA ;

SELECT infotb.TABLE_SCHEMA,
infotb.TABLE_NAME,
infotb.AUTO_INCREMENT,
infocl.COLUMN_TYPE,
infocl.COLUMN_NAME
FROM information_schema.TABLES  as infotb INNER JOIN information_schema.COLUMNS infocl
ON infotb.TABLE_SCHEMA = infocl.TABLE_SCHEMA
AND infotb.TABLE_NAME = infocl.TABLE_NAME
AND infocl.EXTRA='auto_increment';

SELECT TABLE_SCHEMA,
       TABLE_NAME,
       TABLE_COLLATION,
       ENGINE,
       TABLE_ROWS
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA NOT IN ('INFORMATION_SCHEMA' ,
                           'SYS',
                           'MYSQL',
                           'PERFORMANCE_SCHEMA')
  AND TABLE_TYPE='BASE TABLE'
  AND ENGINE NOT IN ('INNODB')
ORDER BY TABLE_ROWS DESC ;

##MySQL8
SELECT  Routine_schema, Routine_type 
FROM information_schema.Routines
WHERE  Routine_schema not in ('mysql','information_schema','performance_schema','sys') 
AND ROUTINE_TYPE='PROCEDURE'
GROUP BY Routine_schema, Routine_type;

show index from db_name.tab_name;

select * from sys.schema_unused_indexes where object_schema not in ('performance_schema');

select * from sys.schema_redundant_indexes;

SELECT trx_mysql_thread_id AS PROCESSLIST_ID,NOW(),TRX_STARTED,
       TO_SECONDS(now()) - TO_SECONDS(trx_started) AS TRX_LAST_TIME ,
       USER,HOST,DB,TRX_QUERY
FROM INFORMATION_SCHEMA.INNODB_TRX trx
JOIN sys.innodb_lock_waits lw ON trx.trx_mysql_thread_id=lw.waiting_pid
JOIN INFORMATION_SCHEMA.processlist pcl ON trx.trx_mysql_thread_id=pcl.id
WHERE trx_mysql_thread_id != connection_id()
  AND TO_SECONDS(now())-TO_SECONDS(trx_started) >= 20;

select * from sys.innodb_lock_waits\G

--Waiting for table metadata lock
SELECT
   a.OBJECT_SCHEMA AS locked_schema,
   a.OBJECT_NAME AS locked_table,
   "Metadata Lock" AS locked_type,
   c.PROCESSLIST_ID AS waiting_processlist_id,
   c.PROCESSLIST_TIME AS waiting_age,
   c.PROCESSLIST_INFO AS waiting_query,
   c.PROCESSLIST_STATE AS waiting_state,
   d.PROCESSLIST_ID AS blocking_processlist_id,
   d.PROCESSLIST_TIME AS blocking_age,
   d.PROCESSLIST_INFO AS blocking_query,
   concat('KILL ', d.PROCESSLIST_ID) AS sql_kill_blocking_connection
FROM
   performance_schema.metadata_locks a
JOIN performance_schema.metadata_locks b ON a.OBJECT_SCHEMA = b.OBJECT_SCHEMA
AND a.OBJECT_NAME = b.OBJECT_NAME
AND a.lock_status = 'PENDING'
AND b.lock_status = 'GRANTED'
AND a.OWNER_THREAD_ID <> b.OWNER_THREAD_ID
AND a.lock_type = 'EXCLUSIVE'
JOIN performance_schema.threads c ON a.OWNER_THREAD_ID = c.THREAD_ID
JOIN performance_schema.threads d ON b.OWNER_THREAD_ID = d.THREAD_ID\G

SELECT thr.processlist_id AS mysql_thread_id, concat(PROCESSLIST_USER,'@',PROCESSLIST_HOST) User, Command, FORMAT_PICO_TIME(trx.timer_wait) AS trx_duration, current_statement as `latest_statement` 
FROM performance_schema.events_transactions_current trx
INNER JOIN performance_schema.threads thr USING (thread_id) 
LEFT JOIN sys.processlist p
ON p.thd_id=thread_id WHERE thr.processlist_id IS NOT NULL AND PROCESSLIST_USER IS NOT NULL AND trx.state = 'ACTIVE' GROUP BY thread_id, timer_wait ORDER BY TIMER_WAIT DESC LIMIT 10;

SELECT QUERY_SAMPLE_TEXT,COUNT_STAR,FIRST_SEEN,LAST_SEEN
FROM performance_schema.events_statements_summary_by_digest ORDER BY COUNT_STAR DESC LIMIT 10\G

SELECT QUERY_SAMPLE_TEXT,AVG_TIMER_WAIT FROM performance_schema.events_statements_summary_by_digest ORDER BY AVG_TIMER_WAIT DESC limit 10\G

SELECT QUERY_SAMPLE_TEXT,SUM_SORT_ROWS FROM performance_schema.events_statements_summary_by_digest ORDER BY SUM_SORT_ROWS DESC LIMIT 10\G

SELECT QUERY_SAMPLE_TEXT,SUM_ROWS_EXAMINED FROM performance_schema.events_statements_summary_by_digest ORDER BY SUM_ROWS_EXAMINED DESC LIMIT 10\G

SELECT QUERY_SAMPLE_TEXT,SUM_CREATED_TMP_TABLES,SUM_CREATED_TMP_DISK_TABLES FROM performance_schema.events_statements_summary_by_digest ORDER BY SUM_CREATED_TMP_TABLES DESC LIMIT 10\G

# 查看活动会话
select * from information_schema.processlist where command <> 'Sleep' and id <> connection_id();
select * from performance_schema.processlist where command <> 'Sleep' and id <> connection_id();
# 列出 MySQL 服务器运行各种状态值
show global status;
# 查询 MySQL 服务器配置信息语句
show variables;
# 慢查询
show variables like '%slow%';
# MySQL 服务器最大连接数
show variables like 'max_connections';
# 服务器响应的最大连接数
show global status like 'Max_used_connections';
# 查看试图连接到 MySQL(不管是否连接成功)的连接数
show status like 'connections';
# 创建临时表
show global status like 'created_tmp%';
# MySQL 服务器对临时表的配置
show variables where Variable_name in ('tmp_table_size', 'max_heap_table_size');
# 打开表的数量
show global status like 'open%tables%';
# table 高速缓存的数量
show variables like 'table_open_cache';
# 查看 MySQL 服务器的线程信息
show global status like 'Thread%';
# 查看当前运行的 sql
SELECT * FROM `information_schema`.`PROCESSLIST` WHERE `info` IS NOT NULL and TIME > 0;
# 当客户端断开之后，服务器处理此客户的线程将会缓存起来以响应下一个客户而不是销毁(前提是缓存数未达上限)
show variables like 'thread_cache_size';
# 查询缓存
show global status like 'qcache%';
# 查询缓存适用于特定的场景，建议充分测试后，再考虑开启，避免引起性能下降或引入其他问题
show variables like 'query_cache%';
# 排序使用情况
show global status like 'sort%';
# 文件打开数
show global status like 'open_files';
# 表锁情况
show global status like 'table_locks%';
# 表扫描情况
show global status like 'handler_read%';
# 服务器完成的查询请求
show global status like 'com_select';
# 查询当前 MySQL 本次启动后的运行统计时间
show status like 'uptime';
# 查看本次 MySQL 启动后执行的 select 语句的次数
show status like 'com_select';
# 查看本次 MySQL 启动后执行 insert 语句的次数
show global status like 'com_insert';
# 查看本次 MySQL 启动后执行 update 语句的次数
show global status like 'com_update';
# 查看本次 MySQL 启动后执行 delete 语句的次数
show global status like 'com_delete';
# 查看立即获得的表的锁的次数
show status like 'table_locks_immediate';
# 查看不能立即获得的表的锁的次数。如果该值较高，并且有性能问题，你应首先优化查询，然后拆分表或使用复制
show status like 'table_locks_waited';
# 查看查询时间超过 long_query_time 秒的查询的个数
show status like 'slow_queries';
# 通过 mysql 自带 profiling(性能分析)工具可以诊断某个 sql 各个执行阶段消耗的时间，每个执行阶段在 cpu disk io 等方面的消耗情况。
show variables like '%profiling%';
#查看 profiles
show profiles;
show profile for query 2;
show profile cpu, block io for query 2;

su - docker
docker ps

CONTAINER ID        IMAGE                                        COMMAND                  CREATED             STATUS              PORTS                     NAMES
d53608bc5053        192.168.26.12/public/mysql:mysql-80-centos7   "container-entrypoin鈥?   2 minutes ago       Up 2 minutes        0.0.0.0:13329->3306/tcp   dtke_dd1
bc45e03f5976        192.168.26.15/public/mysql:mysql-57-centos7   "container-entrypoin鈥?   7 hours ago         Up 7 hours          0.0.0.0:13360->3306/tcp   jie_biz_zjbh

docker ps -a

启动容器
docker start 容器ID

docker cp 源 目标
容器外执行，在宿主机和容器间拷贝文件
--从容器 59 拷贝到 文件系统
docker cp e3fed7f6ce59:/var/lib/mysql/data/dump13397/FULL_P13397_0526.sql /home/docker/
--从文件系统拷贝到 容器 90 
docker cp /home/docker/FULL_P13397_0526.sql 18836b25bb90:/var/lib/mysql/data/dump

docker exec -it d53608bc5053 bash
bash-4.2$ mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.17 Source distribution

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;

身份验证插件
alter user dtjieke_dd1 identified  with mysql_native_password by "dtji0#My07";

mysql> select user,host,plugin from mysql.user;