Lin Hong's TECH Blog! 刀不磨要生锈,人不学习要落后 - Thinking ahead

MySQL 8.0 Study 000 Tips

2023-03-01

MySQL 8.0 Study 000 Tips

常用sql

  • MySQL 对象容量查看sql
  • Mysqladmin 查看QPS
  • MySQL 创建测试表数据
  • MySQL 查看长事务
  • MySQL 查看索引
  • MySQL Innodb 内存使用情况(innodb_buffer_pool)
  • MySQL 缓存命中率统计和库表读写统计
  • MySQL Top SQL统计
  • MySQL 所有链接的客户端ip
  • MySQL 检查对比两个库conf文件
  • MySQL 慢日志检查脚本示例

MySQL 对象容量查看sql

1.查看所有数据库各容量大小

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;

2.查看所有数据库各表容量大小

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;

3.查看指定数据库容量大小

例:查看mysql库容量大小

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='mysql';

4.查看指定数据库各表容量大小 例:查看mysql库各表容量大小

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='mysql'
order by data_length desc, index_length desc;
SELECT
    table_schema AS '库名',
    table_name AS '表名',
    ENGINE AS '存储引擎',
    table_rows AS '行数',
    trim(
        concat(
            round(DATA_LENGTH / 1024 / 1024, 1)
        )
    ) AS '数据大小MB',
    trim(
        round(index_length / 1024 / 1024, 1)
    ) AS '索引大小MB',
    trim(
        round(DATA_FREE / 1024 / 1024, 1)
    ) AS '碎片大小MB'
FROM
    information_schema. TABLES
WHERE
    table_schema NOT IN (
        'information_schema',
        'phpmyadmin',
        'scripts',
        'test',
        'performance_schema',
        'mysql'
    )
-- AND DATA_FREE / 1024 / 1024 > 1000
ORDER BY
    DATA_FREE DESC;

Mysqladmin 查看QPS

mysqladmin -ulin -h127.0.0.1 -P8032 -p'mysql' extended-status -i1 | awk '/Queries/{q=$4-qp;qp=$4} /Threads_connected/{tc=$4} /Threads_running/{printf "%5d %5d %5d\n",q,tc,$4}'

第一列:每秒查询量   
第二列:链接数
第三列:当前执行的链接数
mysqladmin -ulin -h127.0.0.1 -P8032 -p'mysql' extended-status -i1|awk 'BEGIN{local_switch=0;print "-------- Time -------| QPS   Commit Rollback   TPS    Threads_con Threads_run \n----------------------------------------------------------------------------- "}
    $2 ~ /Queries$/            {q=$4-lq;lq=$4;}
    $2 ~ /Com_commit$/         {c=$4-lc;lc=$4;}
    $2 ~ /Com_rollback$/       {r=$4-lr;lr=$4;}
    $2 ~ /Threads_connected$/  {tc=$4;}
    $2 ~ /Threads_running$/    {tr=$4;
    if(local_switch==0)
            {local_switch=1; count=0}
    else {
            if(count>10){
                    count=0;
                    print "-----------------------------------------------------------------------------";
                    print "-------- Time -------| QPS   Commit Rollback   TPS    Threads_con Threads_run";
                    print "----------------------------------------------------------------------------- ";
                    }
            else{
                    count+=1;
                    printf "%s | %-6d %-8d %-7d %-8d %-10d %d \n", strftime("%Y/%m/%d/ %H:%M:%S"),q,c,r,c+r,tc,tr;
            }
    }
}'


mysqladmin -ulin -h127.0.0.1 -P8032 -p'mysql' extended-status -i1|awk 'BEGIN{local_switch=0}
     $2 ~ /Queries$/            {q=$4-lq;lq=$4;}
     $2 ~ /com_commit$/         {c=$4-lc;lc=$4;}
     $2 ~ /Com_rollback$/       {r=$4-lr;lr=$4;}
     $2 ~ /Com_select$/       {s=$4-ls;ls=$4;}
     $2 ~ /Com_update$/       {u=$4-lu;lu=$4;}
     $2 ~ /Com_insert$/       {i=$4-li;li=$4;}
     $2 ~ /Com_delete$/       {d=$4-ld;ld=$4;}
     $2 ~ /Innodb_rows_read$/       {irr=$4-lirr;lirr=$4;}
     $2 ~ /Innodb_rows_deleted$/       {ird=$4-lird;lird=$4;}
     $2 ~ /Innodb_rows_inserted$/       {iri=$4-liri;liri=$4;}
     $2 ~ /Innodb_rows_updated$/       {iru=$4-liru;liru=$4;}
     $2 ~ /Innodb_buffer_pool_read_requests$/       {ibprr=$4-libprr;libprr=$4;}
     $2 ~ /Innodb_buffer_pool_reads$/       {ibpr=$4-libpr;libpr=$4;}
     $2 ~ /Threads_connected$/  {tc=$4;}
     $2 ~ /Threads_running$/    {tr=$4;
        if(local_switch==0)
                {local_switch=1; count=16}
        else {
                if(count>15) {
                    count=0;
                    print "----------------------------------------------------------------------------------------------------------------------------------------------- ";
                    print "-------- Time -------|  QPS | Commit Rollback TPS | select insert update delete |  read inserted updated deleted | logical physical | Tcon Trun";
                    print "----------------------------------------------------------------------------------------------------------------------------------------------- ";
                }else{
                    count+=1;
                    printf "%s | %-5d| %-6d %-7d %-5d| %-7d %-7d %-5d %-6d| %-7d %-7d %-7d %-7d| %-6d  %-9d| %-4d %-2d \n", strftime("%Y/%m/%d/ %H:%M:%S"),q,c,r,c+r,s,u,i,d,irr,ird,iri,iru,ibprr,ibpr,tc,tr;
                }
        }
}'

mysqladmin -ulin -h127.0.0.1 -P8032 -p'mysql' extended-status -i1 |\
awk -F"|" \
"BEGIN{ count=0; }"\
'{ if($2 ~ /Variable_name/ && ((++count)%20 == 1)){\
print "---------------------- - MySQL Command Status - -------------- Innodb Row Operation -------------- Buffer Pool Read--" ; \
print "--------Time----------|---Qps---|select insert  update delete|   read inserted updated deleted|   logical    physical";\
}\
else if ($2 ~ /Queries /){queries=$3;}\
else if ($2 ~ /Com_select /) {com_select=$3;}\
else if ($2 ~ /Com_insert /) {com_insert=$3;}\
else if ($2 ~ /Com_update /) {com_update=$3;}\
else if ($2 ~ /Com_delete /) {com_delete=$3;}\
else if ($2 ~ /Innodb_rows_read /) {innodb_rows_read=$3;}\
else if ($2 ~ /Innodb_rows_updated /) {innodb_rows_updated=$3;}\
else if ($2 ~ /Innodb_rows_deleted /) {innodb_rows_deleted=$3;}\
else if ($2 ~ /Innodb_rows_inserted /) {innodb_rows_inserted=$3;}\
else if ($2 ~ /Innodb_buffer_pool_read_requests /) {innodb_lor=$3;}\
else if ($2 ~ /Innodb_buffer_pool_reads /) {innodb_phr=$3;}\
else if ($2 ~ /Uptime / && count >= 2) {\
  printf(" %s |%9d",strftime ("%Y/%m/%d/ %H:%M:%S") , queries);\
  printf("|%6d %6d %6d %6d", com_select, com_insert, com_update, com_delete);\
  printf("|%6d %8d %7d %7d", innodb_rows_read, innodb_rows_inserted, innodb_rows_updated, innodb_rows_deleted );\
  printf("|%10d %11d\n", innodb_lor,innodb_phr);\
}}'

MySQL 创建测试表数据

创建内存表构造数据然后在导入innodb表

-- 参考: https://zhuanlan.zhihu.com/p/74788180

-- 参考: https://blog.csdn.net/a724888/article/details/79394168

-- 创建一个临时内存表
DROP TABLE IF EXISTS `vote_record_memory`;
CREATE TABLE `vote_record_memory` (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `user_id` varchar(20) NOT NULL DEFAULT '',
    `vote_num` int(10) unsigned NOT NULL DEFAULT '0',
    `group_id` int(10) unsigned NOT NULL DEFAULT '0',
    `status` tinyint(2) unsigned NOT NULL DEFAULT '1',
    `create_time` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
    PRIMARY KEY (`id`),
    KEY `index_user_id` (`user_id`) USING HASH
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

-- 创建一个普通表,用作模拟大数据的测试用例
DROP TABLE IF EXISTS `vote_record`;

CREATE TABLE `vote_record` (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `user_id` varchar(20) NOT NULL DEFAULT '' COMMENT '用户Id',
    `vote_num` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '投票数',
    `group_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '用户组id 0-未激活用户 1-普通用户 2-vip用户 3-管理员用户',
    `status` tinyint(2) unsigned NOT NULL DEFAULT '1' COMMENT '状态 1-正常 2-已删除',
    `create_time` datetime NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '创建时间',
    PRIMARY KEY (`id`),
    KEY `index_user_id` (`user_id`) USING HASH COMMENT '用户ID哈希索引'
) ENGINE = InnoDB DEFAULT CHARSET = utf8 COMMENT = '投票记录表';

-- 为了数据的随机性和真实性,我们需要创建一个可生成长度为n的随机字符串的函数。
-- 创建生成长度为n的随机字符串的函数
DELIMITER // -- 修改MySQL delimiter:'//'
DROP FUNCTION IF EXISTS `rand_string` //
SET NAMES utf8 //
CREATE FUNCTION `rand_string` (n INT) RETURNS VARCHAR(255) CHARSET 'utf8'
BEGIN 
    DECLARE char_str varchar(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';
    DECLARE return_str varchar(255) DEFAULT '';
    DECLARE i INT DEFAULT 0;
    WHILE i < n DO
        SET return_str = concat(return_str, substring(char_str, FLOOR(1 + RAND()*62), 1));
        SET i = i+1;
    END WHILE;
    RETURN return_str;
END 
//

-- ++++++++++++++++++++++++++++++++++++++++++
DROP FUNCTION IF EXISTS rand_string;
DELIMITER $$
CREATE FUNCTION rand_string(n INT)
RETURNS VARCHAR(255)
BEGIN
    DECLARE chars_str varchar(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';
    DECLARE return_str varchar(255) DEFAULT '';
    DECLARE i INT DEFAULT 0;
    WHILE i < n DO
        SET return_str = concat(return_str,substring(chars_str , FLOOR(1 + RAND()*62 ),1));
        SET i = i +1;
    END WHILE;
    RETURN return_str;
END $$
DELIMITER ;
-- ++++++++++++++++++++++++++++++++++++++++++


-- 创建插入数据的存储过程
DROP PROCEDURE IF EXISTS `add_vote_record_memory` //
CREATE PROCEDURE `add_vote_record_memory`(IN n INT)
BEGIN
    DECLARE i INT DEFAULT 1;
    DECLARE vote_num INT DEFAULT 0;
    DECLARE group_id INT DEFAULT 0;
    DECLARE status TINYINT DEFAULT 1;
    WHILE i < n DO
        SET vote_num = FLOOR(1 + RAND() * 10000);
        SET group_id = FLOOR(0 + RAND()*3);
        SET status = FLOOR(1 + RAND()*2);
        INSERT INTO `vote_record_memory` VALUES (NULL, rand_string(20), vote_num, group_id, status, NOW());
        SET i = i + 1;
    END WHILE;
END //
DELIMITER ;  -- 改回默认的 MySQL delimiter:';'

-- 查询内存表已生成记录(为了下步测试,目前仅生成了105645条)
CALL add_vote_record_memory(1000000);
SELECT count(*) FROM `vote_record_memory`;

-- 把数据从内存表插入到普通表中(10w条数据13s就插入完了)
INSERT INTO vote_record SELECT * FROM `vote_record_memory`;
SELECT count(*) FROM `vote_record`;

-- 参数n是每次要插入的条数
-- lastid是已导入的最大id
DELIMITER // -- 修改MySQL delimiter:'//'
DROP PROCEDURE IF EXISTS `copy_data_from_tmp` //
CREATE PROCEDURE `copy_data_from_tmp`(IN n INT)
BEGIN
    DECLARE lastid INT DEFAULT 0;
    SELECT MAX(id) INTO lastid FROM `vote_record`;
    INSERT INTO `vote_record` SELECT * FROM `vote_record_memory` where id > lastid LIMIT n;
END //
DELIMITER ;  -- 改回默认的 MySQL delimiter:';'

-- 调用存储过程 插入6w条
CALL copy_data_from_tmp(10000);

错误:

    ->     `create_time` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
ERROR 1067 (42000): Invalid default value for 'create_time'
mysql [localhost:8032] {msandbox} (test) > 

mysql错误总结-ERROR 1067 (42000): Invalid default value for TIMESTAMP

https://blog.csdn.net/baidu_38432732/article/details/109750336

sql_mode兼容性,MySQL 8.0 升级踩过的坑

https://cloud.tencent.com/developer/article/2005372

在MySQL 5.7 之前,DBA经常习惯使用 grant 语法来创建用户和授权。MySQL 5.7 仍然支持这种语法来创建用户,但是为了限制这种创建用户的行为,引入了NO_AUTO_CREATE_USER的sql模式。[NO_AUTO_CREATE_USER], 即在grant语句中禁止创建空密码的账户,使用grant语法创建用户必须带上 “identified by”关键字设置账户密码,否则就被认为是非法的创建语句。

而在MySQL 8.0.11版本之后,官方认为DBA们已经接受了默认使用create user语法来创建账户的行为,就直接把grant创建账户的语法给废弃了。grant语法创建账户都不允许了,那么NO_AUTO_CREATE_USER模式也就自然要退出历史舞台了,所以就在8.0.11 中同时废弃了这个模式,以后不再支持。

新增

sql_mode = ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,ALLOW_INVALID_DATES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

MySQL 8 取消了 NO_AUTO_CREATE_USER

错误:

ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)

错误分析

我们就必须指定我们的函数是否是
DETERMINISTIC 不确定的
NO SQL 没有SQl语句
READS SQL DATA 只是读取数据
MODIFIES SQL DATA 要修改数据
CONTAINS SQL 包含SQL语句
其中在function/procedure 里面,只有 DETERMINISTIC, NO SQL 和 READS SQL DATA 被支持。如果我们开启了 bin-log, 我们就必须为我们的function/procedure 指定一个参数。

解决方法
1.在mysql数据库中执行以下语句 (临时生效,重启后失效)
set global log_bin_trust_function_creators=TRUE;
或者

set global log_bin_trust_function_creators=1;
2. 在配置文件/etc/my.cnf的[mysqld]或者my-default.ini文件中配置
log_bin_trust_function_creators=1

MySQL生成百万条数据

干货,MySql插入百万级数据的几种方法

CREATE DATABASE `test_bai` -- 创建数据库
USE `test_bai` -- 切换对应的数据库

CREATE TABLE `app_user`(
	`id` INT  NOT NULL AUTO_INCREMENT COMMENT '主键',
	`name` VARCHAR(50) DEFAULT '' COMMENT '用户名称',
	`email` VARCHAR(50) NOT NULL COMMENT '邮箱',
	`phone` VARCHAR(20) DEFAULT '' COMMENT '手机号',
	`gender` TINYINT DEFAULT '0' COMMENT '性别(0-男  : 1-女)',
	`password` VARCHAR(100) NOT NULL COMMENT '密码',
	`age` TINYINT DEFAULT '0' COMMENT '年龄',
	`create_time` DATETIME DEFAULT NOW(),
	`update_time` DATETIME DEFAULT NOW(),
	PRIMARY KEY (`id`) 
)ENGINE = INNODB DEFAULT CHARSET = utf8 COMMENT='app用户表'

SET GLOBAL log_bin_trust_function_creators=TRUE; -- 创建函数一定要写这个
DELIMITER $$   -- 写函数之前必须要写,该标志

CREATE FUNCTION mock_data()		-- 创建函数(方法)
RETURNS INT 						-- 返回类型
BEGIN								-- 函数方法体开始
	DECLARE num INT DEFAULT 1000000; 		-- 定义一个变量num为int类型。默认值为100 0000
	DECLARE i INT DEFAULT 0; 
	
	WHILE i < num DO 				-- 循环条件
		 INSERT INTO app_user(`name`,`email`,`phone`,`gender`,`password`,`age`) 
		 VALUES(CONCAT('用户',i),'2548928007qq.com',CONCAT('18',FLOOR(RAND() * ((999999999 - 100000000) + 1000000000))),FLOOR(RAND()  *  2),UUID(),FLOOR(RAND()  *  100));
		SET i =  i + 1;	-- i自增	
	END WHILE;		-- 循环结束
	RETURN i;
END; 								-- 函数方法体结束

SELECT mock_data();

MySQL生成百万条数据超详细步骤

通过存储过程插入 如果我们想简单快速的插入大批量数据,存储过程是个不错的选择,下面这个存储过程,是我向表xxx_audit_order中插入100万条数据,耗时25秒左右(如果你表结构简单,插入速度将会更快),这里强烈建议:

1.插入数据前先把表中的索引去掉,数据插入完成之后,再创建索引
2.关闭事务的自动提交

以上两点对提高速度很有帮助,因为索引的维护以及每次插入都提交事务是很耗时间的

use test_db;
DROP PROCEDURE if EXISTS BatchInsert;
delimiter $$
CREATE PROCEDURE BatchInsert(IN initId INT, IN loop_counts INT)
BEGIN
    DECLARE Var INT;
    DECLARE ID INT;
    SET Var = 0;
    SET ID = initId;
    set autocommit=0; -- 关闭自动提交事务,提高插入效率
    WHILE Var < loop_counts DO
        INSERT INTO `xxx_audit_order` (`product_no`,`xxx_channel_code`,`business_no`,`xxx_product_id`,`xxx_product_name`,`xxx_audit_no`,`xxx_audit_status`,`inspection_report_no`,`audit_report_no`,`re_audit_count`,`inspector_id`,`remark`,`delete_dt`,`create_by`,`create_dt`,`update_by`,`update_dt`,`xxx_link`,`service_standard_no`,`depth_inspection`,`execute_channel`,`seller_type`) 
        VALUES (CONCAT('20220704', 100000000000 + ID),106,'RS20190719143225916727',26958,'荣耀 Play',CONCAT('C0', 512201907191454553491 + ID),FLOOR(RAND()*10) % 4,'R1152109544189558784','R1152216911870734336',2,0,null,0,6532,UNIX_TIMESTAMP() + ID ,0,Now(),FLOOR(RAND()*10) % 3,'',0,1,null);
        SET ID = ID + 1;
        SET Var = Var + 1;
    END WHILE;
    COMMIT;
END$$;

delimiter ;  -- 界定符复原为默认的分号
CALL BatchInsert(1, 1000000);  -- 调用存储过程

其他:

设置参数 log_bin_trust_function_creators:

前提说明:如果我们在创建函数时,报错:this function has none of deterministic…,这是由于开启过慢查询日志,因为开启了bin-log(二进制日志),我们就必须为我们的function指定一个参数。

简单介绍一下,当二进制日志启用后,这个变量就会启用。它控制是否可以信任存储函数创建者,不会创建写入二进制日志引起不安全事件的存储函数。如果设置为0(默认值),用户不得创建或修改存储函数,除非它们具有除CREATE ROUTINE或ALTER ROUTINE特权之外的SUPER权限,如果变量设置为1,MySQL不会对创建存储函数实施这些限制。

那么为什么MySQL有这样的限制呢? 因为二进制日志的一个重要功能是用于主从复制,而存储函数有可能导致主从的数据不一致。所以当开启二进制日志后,参数log_bin_trust_function_creators就会生效,限制存储函数的创建、修改、调用。

show varibales like ‘log_bin_trust_function_creators’;

set global log_bin_trust_function_creators = 1;

a.	在Windows系统下的配置文件中 my.ini[mysqld] 加上 log_bin_trust_function_creators = 1;
b.	在Linux下 /etc/my.cnf下my.cnf[mysqld] 加上 log_bin_trust_function_creators = 1;
-- set global  log_bin_trust_function_creators=1;

-- 建表部门表和员工表
create table dept(
	id int unsigned primary key auto_increment,
	deptno mediumint unsigned not null default 0,
	dname varchar(20) not null default "",
	loc varchar(13) not null default ""
);

create table emp(
	id int unsigned primary key auto_increment,
	empno mediumint unsigned not null default 0,
	ename varchar(20) not null default "",
	job varchar(9) not null default "",
	mgr mediumint unsigned not null default 0,
	hiredate date not null,
	sal decimal(7,2) not null,
	comn decimal(7,2) not null,
	deptno mediumint unsigned not null default 0
);

-- 随机产生字符串
DELIMITER $$ 
CREATE FUNCTION rand_string(n INT) RETURNS VARCHAR(255)
BEGIN
	DECLARE chars_str VARCHAR(100) DEFAULT 'abcdefghijklmlopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
	DECLARE return_str VARCHAR(255) DEFAULT '';
	DECLARE i INT DEFAULT 0;
	WHILE i < n DO
	SET return_str = CONCAT(return_str,SUBSTRING(chars_str,FLOOR(1+RAND()*52),1));
	SET i = i+1;
	END WHILE;
	RETURN return_str;
END $$

-- 随机产生部门编号
DELIMITER $$ 
CREATE FUNCTION rand_num() RETURNS INT(5)
BEGIN
	DECLARE i INT DEFAULT 0;
	SET i = FLOOR(100+RAND()*10);
	RETURN i;
END $$

-- 随机产生部门编号
DELIMITER $$
CREATE PROCEDURE insert_emp(IN START INT(10),IN max_num INT(10))
BEGIN
	DECLARE i INT DEFAULT 0;
	# set autocommit =0 把autocommit设置成0,把默认提交关闭
	SET autocommit = 0;
	REPEAT
	SET i = i + 1;
	INSERT INTO emp(empno,ename,job,mgr,hiredate,sal,comn,deptno) VALUES ((START+I),rand_string(6),'SALEMAN',0001,now(),2000,400,rand_num());
	UNTIL i = max_num
	END REPEAT;
	COMMIT;
END $$

-- 创建往dept表中插入数据的存储过程
DELIMITER $$
CREATE PROCEDURE insert_dept(IN START INT(10),IN max_num INT(10))
BEGIN
	DECLARE i INT DEFAULT 0;
	SET autocommit = 0;
	REPEAT
	SET i = i+1;
	INSERT  INTO dept( deptno,dname,loc) VALUES((START+i),rand_string(10),rand_string(8));
	UNTIL i = max_num
	END REPEAT;
	COMMIT;
END $$

-- 调用方法和存储过程:
delimiter ;				//结束$$的定义,变为";"

-- show variables like 'innodb_flush_log_at_trx_commit';
-- set global innodb_flush_log_at_trx_commit=2;

call insert_dept(10000,10000);

call insert_emp(10,100000);

-- set global innodb_flush_log_at_trx_commit=1;


SELECT * FROM mysql.innodb_table_stats WHERE table_name like 'emp';

SELECT * FROM mysql.innodb_table_stats WHERE table_name like 'dept';

-- show profile 是MySQL提供用来分析当前会话中语句执行的资源消耗情况,可以用于SQL的调优的测量。默认情况下,该参数处于关闭状态,并保持最近15次的运行结果。
show variables like 'profiling';
set profiling = on; //开启
show profiles;
show profile cpu,block io for query 5;
-- show profile cpu,block io for query 问题SQL数字号码;
-- all	显示所有的开销信息
-- cpu	显示CPU相关开销信息
-- Block io	显示块IO相关开销
-- ipc	显示发送和接收相关开销信息
-- memory	显示内存相关开销信息
-- Page faults	显示页面错误相关开销信息
-- swaps	显示交换次数相关开销的信息
-- source	显示和source_funciton、source_file、source_line相关的开销信息
-- Context switches	上下文切换相关开销

-- 如果在 Status 字段中出现以下描述,则需要注意:
-- (1)converting HEAP to MYISAM:查询结果太大,内存都不够用了,往磁盘上搬了;
-- (2)Creating tmp table :创建临时表(拷贝数据到临时表,用完再删除);
-- (3)Copying to tmp table on disk:把内存中临时表复制到磁盘(很危险!!!)
-- (4)Locked

MySQL生成百万条数据-其他

CREATE TABLE t_user_innodb(
  id INT NOT NULL AUTO_INCREMENT,
  first_name VARCHAR(20) NOT NULL,
  last_name VARCHAR(20) NOT NULL,
  sex VARCHAR(5) NOT NULL,
  score INT NOT NULL,
  copy_id INT NOT NULL,
  PRIMARY KEY (`id`)
) engine=innodb;

DELIMITER //
create PROCEDURE add_user_innodb(in num INT)
BEGIN
DECLARE rowid INT DEFAULT 0;
DECLARE firstname VARCHAR(10);
DECLARE name1 VARCHAR(10);
DECLARE name2 VARCHAR(10);
DECLARE lastname VARCHAR(10) DEFAULT '';
DECLARE sex CHAR(1);
DECLARE score CHAR(2);
WHILE rowid < num DO
SET firstname = SUBSTRING(md5(rand()),1,4); 
SET name1 = SUBSTRING(md5(rand()),1,4); 
SET name2 = SUBSTRING(md5(rand()),1,4); 
SET sex=FLOOR(0 + (RAND() * 2));
SET score= FLOOR(40 + (RAND() *60));
SET rowid = rowid + 1;
IF ROUND(RAND())=0 THEN 
SET lastname =name1;
END IF;
IF ROUND(RAND())=1 THEN
SET lastname = CONCAT(name1,name2);
END IF;
insert INTO t_user_innodb(first_name,last_name,sex,score,copy_id) VALUES (firstname,lastname,sex,score,rowid);  
END WHILE;
END //
DELIMITER ;

-- set global innodb_flush_log_at_trx_commit=2;
call add_user_innodb(1000000);

MySQL 查看长事务

如何找到长事务 遇到事务等待问题时,我们首先要做的是找到正在执行的事务。 information_schema.INNODB_TRX 表中包含了当前innodb内部正在运行的事务信息,这个表中给出了事务的开始时间,我们可以稍加运算即可得到事务的运行时间。

select t.*,to_seconds(now())-to_seconds(t.trx_started) idle_time from INFORMATION_SCHEMA.INNODB_TRX t \G

在结果中idle_time是计算产生的,也是事务的持续时间。但事务的trx_query是NUL,这并不是说事务什么也没执行,一个事务可能包含多个SQL,如果SQL执行完毕就不再显示了。

当前事务正在执行,innodb也不知道这个事务后续还有没有sql,啥时候会commit。 因此trx_query不能提供有意义的信息。

diff_sec和上面idle_time表示意思相同,都是代表此事务持续的秒数。SQL_TEXT表示该事务刚执行的SQL。但是呢,上述语句只能查到事务最后执行的SQL,我们知道,一个事务里可能包含多个SQL,那我们想查询这个未提交的事务执行过哪些SQL,是否可以满足呢,答案是结合events_statements_history系统表也可以满足需求。

select now(),(UNIX_TIMESTAMP(now()) - UNIX_TIMESTAMP(a.trx_started)) diff_sec,b.id,b.user,b.host,b.db,d.SQL_TEXT from information_schema.innodb_trx a inner join
information_schema.PROCESSLIST b
on a.TRX_MYSQL_THREAD_ID=b.id and b.command = 'Sleep'
inner join performance_schema.threads c ON b.id = c.PROCESSLIST_ID
inner join performance_schema.events_statements_current d ON d.THREAD_ID = c.THREAD_ID;

我们可以看到该事务从一开始到现在执行过的所有SQL,当我们把该事务相关信息都查询清楚后,我们就可以判定该事务是否可以杀掉,以免影响其他事务造成等待现象。

SELECT
  ps.id 'PROCESS ID',
  ps.USER,
  ps.HOST,
  esh.EVENT_ID,
  trx.trx_started,
  esh.event_name 'EVENT NAME',
  esh.sql_text 'SQL',
  ps.time
FROM
  performance_schema.events_statements_history esh
  JOIN performance_schema.threads th ON esh.thread_id = th.thread_id
  JOIN information_schema.PROCESSLIST ps ON ps.id = th.processlist_id
  LEFT JOIN information_schema.innodb_trx trx ON trx.trx_mysql_thread_id = ps.id
WHERE
  trx.trx_id IS NOT NULL
  AND ps.USER != 'SYSTEM_USER'
ORDER BY
  esh.EVENT_ID;

监控长事务 现实工作中我们需要监控下长事务,定义一个阈值,比如说30s 执行时间超过30s的事务即为长事务,要求记录并告警出来,提醒管理人员去处理。下面给出监控脚本,各位可以参考下,根据需求改动使用

#!/bin/bash
# -------------------------------------------------------------------------------
# FileName:    long_trx.sh
# Describe:    monitor long transaction
# Revision:    1.0
# Date:        2019/09/16
# Author:      wang

/usr/local/mysql/bin/mysql -N -uroot -pxxxxxx -e "select now(),(UNIX_TIMESTAMP(now()) - UNIX_TIMESTAMP(a.trx_started)) diff_sec,b.id,b.user,b.host,b.db,d.SQL_TEXT from information_schema.innodb_trx a inner join
information_schema.PROCESSLIST b
on a.TRX_MYSQL_THREAD_ID=b.id and b.command = 'Sleep'
inner join performance_schema.threads c ON b.id = c.PROCESSLIST_ID
inner join performance_schema.events_statements_current d ON d.THREAD_ID = c.THREAD_ID;" | while read A B C D E F G H
do
  if [ "$C" -gt 30 ]
      then
      echo $(date +"%Y-%m-%d %H:%M:%S")
      echo "processid[$D] $E@$F in db[$G] hold transaction time $C SQL:$H"
  fi
done >> /tmp/longtransaction.txt

简单说明一下,这里的-gt 30是30秒钟的意思,只要超过了30秒钟就认定是长事务,可以根据实际需要自定义。将该脚本加入定时任务中即可执行。

如何找到MySQL长事务

# 查询所有正在运行的事务及运行时间
select t.*,to_seconds(now())-to_seconds(t.trx_started) idle_time from INFORMATION_SCHEMA.INNODB_TRX t \G

# 查询事务详细信息及执行的SQL
select now(),(UNIX_TIMESTAMP(now()) - UNIX_TIMESTAMP(a.trx_started)) diff_sec,b.id,b.user,b.host,b.db,d.SQL_TEXT from information_schema.innodb_trx a inner join information_schema.PROCESSLIST b
on a.TRX_MYSQL_THREAD_ID=b.id and b.command = 'Sleep'
inner join performance_schema.threads c ON b.id = c.PROCESSLIST_ID
inner join performance_schema.events_statements_current d ON d.THREAD_ID = c.THREAD_ID;

# 查询事务执行过的所有历史SQL记录
SELECT
  ps.id 'PROCESS ID',
  ps.USER,
  ps.HOST,
  esh.EVENT_ID,
  trx.trx_started,
  esh.event_name 'EVENT NAME',
  esh.sql_text 'SQL',
  ps.time 
FROM
  PERFORMANCE_SCHEMA.events_statements_history esh
  JOIN PERFORMANCE_SCHEMA.threads th ON esh.thread_id = th.thread_id
  JOIN information_schema.PROCESSLIST ps ON ps.id = th.processlist_id
  LEFT JOIN information_schema.innodb_trx trx ON trx.trx_mysql_thread_id = ps.id 
WHERE
  trx.trx_id IS NOT NULL 
  AND ps.USER != 'SYSTEM_USER' 
ORDER BY
  esh.EVENT_ID;

 # 简单查询事务锁
 select * from sys.innodb_lock_waits\G

 # 查询事务锁详细信息
 SELECT
  tmp.*,
  c.SQL_Text blocking_sql_text,
  p.HOST blocking_host 
FROM
  (
  SELECT
    r.trx_state wating_trx_state,
    r.trx_id waiting_trx_id,
    r.trx_mysql_thread_Id waiting_thread,
    r.trx_query waiting_query,
    b.trx_state blocking_trx_state,
    b.trx_id blocking_trx_id,
    b.trx_mysql_thread_id blocking_thread,
    b.trx_query blocking_query 
  FROM
    information_schema.innodb_lock_waits w
    INNER JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id
    INNER JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id 
  ) tmp,
  information_schema.PROCESSLIST p,
  PERFORMANCE_SCHEMA.events_statements_current c,
  PERFORMANCE_SCHEMA.threads t 
WHERE
  tmp.blocking_thread = p.id 
  AND t.thread_id = c.THREAD_ID 
  AND t.PROCESSLIST_ID = p.id \G

生产之前没有部署这些监控,如果想看一下你的业务是否有大事务,也可以用以下方法查看一下

mysqlbinlog /u02/log/3308/binlog/binlog.000032 | grep "GTID$(printf '\t')last_committed" -B 1  | egrep -E '^# at|^#20' | awk '{print $1,$2,$3}' | sed 's/server//' | sed 'N;s/\n/ /' | awk 'NR==1 {tmp=$1} NR>1 {print $4,$NF,($3-tmp);tmp=$3}' | sort -k 3 -n -r | head -n 20

MySQL 查看索引

查看指定数据库之中某一张表名的索引信息

show index from	tablename;

查询某个数据库(table_schema)的全部表索引,可以从INFORMATION_SCHEMA架构中的STATISTICS表中获取索引信息

select distinct
	table_name,
	index_name 
from
	information_schema.statistics 
where
	table_schema = 'test(数据库名称)';

获取指定数据库中索引的编号以及每个表的索引名

select table_name,
       count(1) index_count,
       group_concat(distinct(index_name) separator ',\n ') indexes
from information_schema.statistics
where table_schema = 'test'
      and index_name != 'primary'
group by table_name
order by count(1) desc;

查询出只包含索引的结果集

select distinct s.*
from information_schema.statistics s
left outer join information_schema.table_constraints t 
    on t.table_schema = s.table_schema 
       and t.table_name = s.table_name
       and s.index_name = t.constraint_name 
where 0 = 0
      and t.constraint_name is null
      and s.table_schema = 'test';

MySQL Innodb 内存使用情况(innodb_buffer_pool)

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ;

SELECT 
     database_name,
     SUM(compressed_size)/1024/1024  AS allocated_memory,
     SUM(data_size)/1024/1024  AS data_memory,
     SUM(is_hashed)*16/1024 AS is_hashed_memory,
     SUM(is_old)*16/1024 AS is_old_memory
FROM 
(
    SELECT 
        case when INSTR(TABLE_NAME,'.')>0 then replace(SUBSTRING(TABLE_NAME,1,INSTR(TABLE_NAME,'.')-1),'`','')
        else     'system_database' end as database_name,
        case when INSTR(TABLE_NAME,'.')>0 then replace(SUBSTRING(TABLE_NAME,INSTR(TABLE_NAME,'.')+1),'`','')
        ELSE 'system_obj' END AS table_name,
        if(compressed_size = 0, 16384, compressed_size) AS compressed_size,
        data_size,
        if(is_hashed = 'YES',1,0) is_hashed,
        if(is_old = 'YES',1,0)  is_old
    FROM information_schema.innodb_buffer_page
    WHERE TABLE_NAME IS NOT NULL
) t
GROUP BY database_name
ORDER BY allocated_memory DESC
LIMIT 10;

MySQL 缓存命中率统计和库表读写统计

https://www.cnblogs.com/wy123/p/11431227.html

查询缓存命中率相关: information_schema.innodb_buffer_pool_stats中的数据行数是跟buffer_pool_instance一致的 也就是每个一行数据来描述一个buffer_pool_instance,这里简单取和,缓存命中率取平局值的方式来统计 需要注意的是

1,modified_database_pages是实时的,就是内存中的脏页的数量,经checkpoint之后被刷新到磁盘,因此会时大时小。
2,pages_made_young和pages_not_made_young是累积的增加的,不会减少,就是MySQL实例截止到目前位置,做了多少pages_not_made_young和pages_not_made_young。
3,hit_rate在负载较低的情况下,没有参考意义,这一点很奇怪,低负载情况下,会发现很多buffer_pool的hit_rate是0。
  反复测试的过程中突然意识到,hit_rate的计算,是不是以某个时间间隔为基准,统计这个时间段内请求的命中率,如果这一小段时间内没有请求,统计出来的hit_rate就是0。
4,与其他视图不通,information_schema.innodb_buffer_pool_stats中的数据会在服务重启后清零。

SQL:

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ;
SELECT 
    SUM(modified_database_pages) AS total_modified_database_pages,
    SUM(pages_made_young) AS total_pages_made_young,
    SUM(pages_not_made_young) AS total_pages_not_made_young,
    SUM(hit_rate)/COUNT(hit_rate)*1000 AS hit_rate
FROM

(
    SELECT 
        pool_id,
        pool_size,
        database_pages,
        old_database_pages,
        modified_database_pages,
        pages_made_young,
        pages_not_made_young,
        hit_rate
    FROM information_schema.innodb_buffer_pool_stats
)t;

库\表的读写统计,物理IO层面的热点数据统计 按照物理IO的维度统计热点数据,哪些库\表消耗了多少物理IO。 这里原始系统表中的数据是一个累计统计的值,最极端的情况就是一个表为0行,却存在大量的物理读写IO。

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ;


SELECT 
    database_name,
    IFNULL(cast(sum(total_read) as signed),0) AS total_read,
    IFNULL(cast(sum(total_written) as signed),0) AS total_written,
    IFNULL(cast(sum(total) AS SIGNED),0) AS total_read_written
FROM
(
    SELECT 
        substring(REPLACE(file, '@@datadir/', ''),1,instr(REPLACE(file, '@@datadir/', ''),'/')-1) AS database_name,
        count_read,
        case 
            when instr(total_read,'KiB')>0 then  replace(total_read,'KiB','')/1024
            when instr(total_read,'MiB')>0 then  replace(total_read,'MiB','')/1024
            when instr(total_read,'GiB')>0 then replace(total_read,'GiB','')*1024
        END AS total_read,
        case 
            when instr(total_written,'KiB')>0 then replace(total_written,'KiB','')/1024
            when instr(total_written,'MiB')>0 then replace(total_written,'MiB','')
            when instr(total_written,'GiB')>0 then replace(total_written,'GiB','')*1024
        END AS total_written,
        case 
            when instr(total,'KiB')>0 then replace(total,'KiB','')/1024
            when instr(total,'MiB')>0 then replace(total,'MiB','')
            when instr(total,'GiB')>0 then replace(total,'GiB','')*1024
        END AS total
    from sys.io_global_by_file_by_bytes 
    WHERE FILE LIKE '%@@datadir%' AND instr(REPLACE(file, '@@datadir/', ''),'/')>0 
)t
GROUP BY database_name
ORDER BY total_read_written DESC;

MySQL Top SQL统计

可以按照执行时间,阻塞时间,返回行数等等维度统计top sql。 另外可以按照时间筛选last_seen,可以统计最近某一段时间出现过的top sql

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ;

SELECT 
    schema_name,
    digest_text,
    count_star,
    avg_timer_wait/1000000000000 AS avg_timer_wait,
    max_timer_wait/1000000000000 AS max_timer_wait,
    sum_lock_time/count_star/1000000000000 AS avg_lock_time ,
    sum_rows_affected/count_star AS avg_rows_affected,
    sum_rows_sent/count_star AS avg_rows_sent ,
    sum_rows_examined/count_star AS avg_rows_examined,
    sum_created_tmp_disk_tables/count_star AS avg_create_tmp_disk_tables,
    sum_created_tmp_tables/count_star AS avg_create_tmp_tables,
    sum_select_full_join/count_star AS avg_select_full_join,
    sum_select_full_range_join/count_star AS avg_select_full_range_join,
    sum_select_range/count_star AS avg_select_range,
    sum_select_range_check/count_star AS avg_select_range,
    first_seen,
    last_seen
FROM performance_schema.events_statements_summary_by_digest
WHERE last_seen>date_add(NOW(), interval -1 HOUR)
ORDER BY 
max_timer_wait
-- avg_timer_wait
-- sum_rows_affected/count_star 
-- sum_lock_time/count_star
-- avg_lock_time
-- avg_rows_sent
DESC
limit 10;

需要注意的是,这个统计是按照MySQL执行一个事务消耗的资源做统计的,而不是一个语句,笔者一开始懵逼了一阵子,举个简单的例子。 参考如下,这里是循环写个数据的一个存储过程,调用方式就是call create_test_data(N),写入N条测试数据。 比如call create_test_data(1000000)就是写入100W的测试数据,这个执行过程耗费了几分钟的时间,按照笔者的测试实例情况,avg_timer_wait的维度,绝对是一个TOP SQL。 但是在查询的时候,始终没有发现这个存储过程的调用被列为TOP SQL,后面尝试在存储过程内部加了一个事物,然后就顺利地收集到了整个TOP SQL. 因此说performance_schema.events_statements_summary_by_digest里面的统计,是基于事务的,而不是某一个批处理的执行时间的。

执行失败的SQL 统计

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ;

select 
    schema_name,
    digest_text,
    count_star,
    first_seen,
    last_seen
from performance_schema.events_statements_summary_by_digest
where sum_errors>0 or sum_warnings>0 
order by last_seen desc;

Index使用情况统计

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ;

SELECT SUBSTRING_INDEX(NAME, '/', 1) as wait_type,COUNT(1)  
FROM performance_schema.setup_instruments
GROUP BY 1  
ORDER BY 2 DESC;


SELECT
event_name,
count_star,
sum_timer_wait
FROM performance_schema.events_waits_summary_global_by_event_name
WHERE event_name != 'idle'
order by sum_timer_wait desc
limit 100;

MySQL 所有链接的客户端ip

SELECT substring_index(host, ':',1) AS host_name,
state,count(*)
FROM information_schema.processlist GROUP BY state,host_name;

查询某个数据库中某个表的所有列名

查询某个数据库中某个表的所有列名

SELECT COLUMN_NAME FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = 'db_name' AND TABLE_NAME = 'tb_name';

查询某个数据库中某个表的所有列名,并用逗号连接

SELECT GROUP_CONCAT(COLUMN_NAME SEPARATOR ",") FROM information_schema.COLUMNS 
WHERE TABLE_SCHEMA = 'db_name' AND TABLE_NAME = 'tb_name';

MySQL 检查对比两个库conf文件

参考脚本:

#!/bin/bash
# File Name   : check_mysql_conf_diff.sh
# Ref: https://gitee.com/mo-shan/script/blob/master/check_mysql_conf_diff.sh
#########################################################################
opt="system"  #conf|system  conf表示对比配置文件,system表示对比系统变量
skip_array=(gtid_purged gtid_executed relay_log_basename port)   #表示跳过比较哪些变量
#如果是system的话,需要配置下面与mysql连接相关的参数
mysql_host1="ol8mysql"
mysql_user1="lin"
mysql_pass1="mysql"
mysql_port1="8032"
mysql_host2="ol8mysql01"
mysql_user2="lin"
mysql_pass2="mysql"
mysql_port2="8032"
mysql_path="/opt/mysql/8.0.32/bin/mysql"
function f_logging()
{
	log_mode="${1}"
	log_info="${2}"
	log_enter="${3}"
	exit_mark="${4}"
	enter_opt=""
	if [ "${log_mode}x" == "WARNx" ]
	then
		echo -e "\033[33m"
	elif [ "${log_mode}x" == "ERRORx" ]
	then
		echo -e "\033[31m"
	else
		echo -en "\033[32m"
	fi
	if [ "${log_enter}x" == "0x" ]
	then
		log_enter="-n"
	elif [ "${log_enter}x" == "2x" ]
	then
		log_enter="-e"
		enter_opt="\n"
	else
		log_enter="-e"
	fi
	echo ${log_enter} "[$(date "+%F %H:%M:%S")] [${log_mode}] [${localhost_ip}] ${log_info}${enter_opt}"
	echo -en "\033[0m"
	if [ "${log_mode}x" == "ERRORx" -a "${exit_mark}x" != "0x" ]
	then
		exit
	fi
	echo -en "\033[32m"
}
function f_format_print_info()
{
	show_str="${1}"
	if [ "$(grep -cE "^space_str|space_str$|space_strs$" <<< "${show_str}")x" == "1x" ]
	then
		show_str_len=$((${#show_str}-9))
	else
		show_str_len=${#show_str}
	fi
	max_len="${2}"
	tmp_len=$((${max_len}-${show_str_len}))
	for ((var=0;var<${tmp_len};var++))
	do
		if [ "${1}x" == "-x" ]
		then
			show_str="${show_str}-"
		else
			show_str="${show_str}space_str"
		fi
	done
}
function f_enter_str()
{
	var_pos=0
	str_type=(0 0)
	clo=(0 0)
	for var in ${@}
	do
		col_len="${col_array[${var_pos}]}"
		eval str_type[${var_pos}]="${var}"
		f_format_print_info "${str_type[${var_pos}]}" "${col_len}"
		eval clo[${var_pos}]="${show_str}"
		var_pos=$((${var_pos}+1))
	done
	show_info=""
	for ((i=0;i<${#clo[@]};i++))
	do
		if [ "${i}x" == "0x" ]
		then
			if [ "${str_type[0]}x" == "-x" ]
			then
				show_info="${show_info}\033[32m+${clo[${i}]}-+-"
			else
				show_info="\033[32m|\033[0m\033[33m${clo[${i}]}\033[0m\033[32m|\033[0m"
			fi
		elif [ "${i}x" == "$((${#clo[@]}-1))x" ]
		then
			if [ "${str_type[0]}x" == "-x" ]
			then
				show_info="${show_info}${clo[${i}]}-+\033[0m"
			else
				show_info="${show_info}\033[33m ${clo[${i}]}\033[0m\033[32m|\033[0m"
			fi
		else
			if [ "${str_type[0]}x" == "-x" ]
			then
				show_info="${show_info}${clo[${i}]}-+-"
			else
				show_info="${show_info}\033[33m ${clo[${i}]}\033[0m \033[32m|\033[0m"
			fi
		fi
	done
	echo -e "${show_info}"|sed 's/space_str/ /g'|sed 's/:=/ -/g'|sed 's/@@@/ /g'
}
file1="${1}"
file2="${2}"
tmp_file1="/tmp/.tmp1"
tmp_file2="/tmp/.tmp2"
[ ! -d "/tmp" ] && mkdir /tmp
if [ "${#}x" != "2x" -a "${opt}x" == "confx" ]
then
	f_logging "WARN" "bash ${0} file1 file2" "2"
	exit
elif [ ! -f "${file1}" -a "${opt}x" == "confx" ]
then
	f_logging "ERROR" "${file1} : No such file." "2" "1"
elif [ ! -f "${file2}" -a "${opt}x" == "confx" ]
then
	f_logging "ERROR" "${file2} : No such file." "2" "1"
fi
if [ "${opt}x" == "confx" ] 
then
	cat ${file1}|grep -v "^#"|tr -d " "|tr -d "\t" |sed 's/^ //g'|sed -n '/^\[mysqld\]$/,/\[.*\]/p'|grep "=" > ${tmp_file1}
	cat ${file2}|grep -v "^#"|tr -d " "|tr -d "\t" |sed 's/^ //g'|sed -n '/^\[mysqld\]$/,/\[.*\]/p'|grep "=" > ${tmp_file2}
elif [ "${opt}x" == "systemx" ]
then
	file1="${mysql_port1}"
	file2="${mysql_port2}"
	${mysql_path} -u${mysql_user1} -p${mysql_pass1} -P${mysql_port1} -h${mysql_host1} -NBe "select concat(VARIABLE_NAME,'=',VARIABLE_VALUE) from performance_schema.global_variables;" 2>/dev/null |sed 's/ /@@@/g' > ${tmp_file1}
	if [ ! -s "${tmp_file1}" ]
	then
		f_logging "ERROR" "The first mysql node is abnormally connected" "2" "1"
	fi
	${mysql_path} -u${mysql_user2} -p${mysql_pass2} -P${mysql_port2} -h${mysql_host2} -NBe "select concat(VARIABLE_NAME,'=',VARIABLE_VALUE) from performance_schema.global_variables;" 2>/dev/null |sed 's/ /@@@/g' > ${tmp_file2} 
	if [ ! -s "${tmp_file2}" ]
	then
		f_logging "ERROR" "The second mysql node is abnormally connected" "2" "1"
	fi
fi
col_array=(40 50 50)
f_enter_str "-" "-" "-"
f_enter_str "Variablespace_str" "${file1}" "${file2}space_str"
for var in $(cat ${tmp_file1})
do
	var_name=$(awk -F= '{print $1}' <<< "${var}")
	[ "$(echo ${skip_array[@]}|tr " " "\n"|grep -c "^${var_name}$")x" == "1x" ] && continue
	var_var=$(awk -F= '{print $2}' <<< "${var}")
	var_var2=$(grep "^${var_name}=" ${tmp_file2} |awk -F= '{print $2}'|tail -1)
	if [ "${var_var}x" != "${var_var2}x" ]
	then
		[ "${var_var2}x" == "x" ] && var_var2="default"
		f_enter_str "-" "-" "-"
		f_enter_str "${var_name}space_str" "${var_var}" "${var_var2}space_str"
	fi
done
for var in $(cat ${tmp_file2})
do
	var_name=$(awk -F= '{print $1}' <<<"${var}")
	[ "$(echo ${skip_array[@]}|tr " " "\n"|grep -c "^${var_name}$")x" == "1x" ] && continue
	var_var=$(awk -F= '{print $2}' <<<"${var}")
	var_var2=$(grep "^${var_name}=" ${tmp_file1} |awk -F= '{print $2}'|tail -1)
	if [ "${var_var}x" != "${var_var2}x" ]
	then
		[ "${var_var2}x" == "x" ] && var_var2="default" || continue
		f_enter_str "-" "-" "-"
		f_enter_str "${var_name}space_str" "${var_var2}" "${var_var}space_str"
	fi
done
f_enter_str "-" "-" "-"
[ -f "${tmp_file1}" ] && rm -f ${tmp_file1}
[ -f "${tmp_file2}" ] && rm -f ${tmp_file2}


测试结果:

[root@ol8mysql01 ~]# bash check_mysql_conf_diff.sh 
+-----------------------------------------+----------------------------------------------------+----------------------------------------------------+
|Variable                                 | 8032                                               | 8032                                               |
+-----------------------------------------+----------------------------------------------------+----------------------------------------------------+
|basedir                                  | /opt/mysql/mysql_ee/8.0.32/                        | /opt/mysql/8.0.32/                                 |
+-----------------------------------------+----------------------------------------------------+----------------------------------------------------+
|build_id                                 | cc45ab70e9c08674adaa72cd5d3f2068a4a998e9           | cd3bf925e8466bf8caf97e42a81cab170f309dd6           |
+-----------------------------------------+----------------------------------------------------+----------------------------------------------------+
|character_sets_dir                       | /opt/mysql/mysql_ee/8.0.32/share/charsets/         | /opt/mysql/8.0.32/share/charsets/                  |
+-----------------------------------------+----------------------------------------------------+----------------------------------------------------+
|general_log_file                         | /root/sandboxes/ee_8_0_32/data/ol8mysql.log        | /root/sandboxes/ee_8_0_32/data/ol8mysql01.log      |
+-----------------------------------------+----------------------------------------------------+----------------------------------------------------+
|hostname                                 | ol8mysql                                           | ol8mysql01                                         |
+-----------------------------------------+----------------------------------------------------+----------------------------------------------------+
|lc_messages_dir                          | /opt/mysql/mysql_ee/8.0.32/share/                  | /opt/mysql/8.0.32/share/                           |
+-----------------------------------------+----------------------------------------------------+----------------------------------------------------+
|license                                  | Commercial                                         | GPL                                                |
+-----------------------------------------+----------------------------------------------------+----------------------------------------------------+
|plugin_dir                               | /opt/mysql/mysql_ee/8.0.32/lib/plugin/             | /opt/mysql/8.0.32/lib/plugin/                      |
+-----------------------------------------+----------------------------------------------------+----------------------------------------------------+
|slow_query_log_file                      | /root/sandboxes/ee_8_0_32/data/ol8mysql-slow.log   | /root/sandboxes/ee_8_0_32/data/ol8mysql01-slow.log |
+-----------------------------------------+----------------------------------------------------+----------------------------------------------------+
|version                                  | 8.0.32-commercial                                  | 8.0.32                                             |
+-----------------------------------------+----------------------------------------------------+----------------------------------------------------+
|version_comment                          | MySQL Enterprise Server - Commercial       | MySQL Community Server - GPL               |
+-----------------------------------------+----------------------------------------------------+----------------------------------------------------+
[root@ol8mysql01 ~]# 

MySQL 慢日志检查脚本示例

参考脚本:

#!/bin/bash
DIR="$( cd "$( dirname "$0"  )" && pwd  )"
cd ${DIR}

#配置目标数据库的连接地址
target_db_host="127.0.0.1"
target_db_port=3306
target_db_user="root"
target_db_password="123456"
target_db_database="archery"

#被分析实例的慢日志位置,建议定期清理日志文件,否则会影响分析效率
slowquery_file="/home/mysql/log_slow.log"

#pt-query-digest可执行文件路径
pt_query_digest="/usr/bin/pt-query-digest"

#被分析实例的连接信息
hostname="mysql_host:mysql_port" # 需要和目标实例配置中的内容保持一致,用于筛选,配置错误会导致数据无法展示

#获取上次分析时间,初始化时请删除last_analysis_time_$hostname文件,可分析全部日志数据
if [[ -s last_analysis_time_${hostname} ]]; then
    last_analysis_time=`cat last_analysis_time_${hostname}`
else
    last_analysis_time='1000-01-01 00:00:00'
fi

#收集日志
#RDS需要增加--no-version-check选项
${pt_query_digest} \
--user=${target_db_user} --password=${target_db_password} --port=${target_db_port} \
--review h=${target_db_host},D=${target_db_database},t=mysql_slow_query_review  \
--history h=${target_db_host},D=${target_db_database},t=mysql_slow_query_review_history  \
--no-report --limit=100% --charset=utf8 \
--since "$last_analysis_time" \
--filter="\$event->{Bytes} = length(\$event->{arg}) and \$event->{hostname}=\"$hostname\"  and \$event->{client}=\$event->{ip} " \
${slowquery_file} > /tmp/analysis_slow_query.log

if [[ $? -ne 0 ]]; then
echo "failed"
else
echo `date +"%Y-%m-%d %H:%M:%S"`>last_analysis_time_${hostname}
fi

Ref

查看mysql数据库容量大小

Have a good work&life! 2023/01 via LinHong


Similar Posts

Comments