MySQL
SELECT @@sql_mode;
SELECT @@global.sql_mode
SET @global.sql_mode = ‘STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION’
SET @@sql_mode = ‘STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION’
set @@GLOBAL.sql_mode=’STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION’
SHOW GLOBAL VARIABLES LIKE ‘innodb_lock_wait_timeout’;
SET GLOBAL innodb_lock_wait_timeout=120;
SELECT @@TRANSACTION_ISOLATION;
— SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;
INSERT INTO TEST
(ID
, NAME
) VALUES (‘4’, ‘OK’);
SELECT * FROM TEST
;
— ROLLBACK;
COMMIT;
进程&连接情况
SHOW STATUS LIKE ‘%Threads_connected%’;
SHOW VARIABLES LIKE ‘%max_connections%’;
— SET GLOBAL max_connections=1500;
— SET GLOBAL mysqlx_max_connections=1500;
SHOW STATUS;
SHOW PROCESSLIST;
KILL ID;
SELECT * FROM performance_schema
.data_locks;
SELECT * FROM performance_schema
.data_lock_waits;
SELECT * FROM performance_schema
.metadata_locks;
SELECT * FROM performance_schema
.rwlock_instances;
SELECT * FROM performance_schema
.table_lock_waits_summary_by_table;
慢SQL
— 可以用这个查询所有的变量
SHOW VARIABLES LIKE ‘%slow_query%’;
SHOW VARIABLES LIKE ‘%log_output%’;
SHOW VARIABLES LIKE ‘%long_query_time%’;
第一步
— 开启慢日志,纪录到 mysql.slow_log 表
SET GLOBAL log_output = ‘TABLE’;
— 设置超过2秒的查询为慢查询
SET GLOBAL long_query_time = 3;
— 打开慢日志记录
SET GLOBAL slow_query_log = ‘ON’;
第二步 运行一下比较慢的功能,执行下面的语句
— 查询慢sql的 日志
SELECT
CONVERT (sql_text USING utf8) sql_text
FROM
mysql.slow_log;
第三步 记得关上日志
— 如果不用了记得关上日志
SET GLOBAL slow_query_log = ‘OFF’;
— 清除日志
SET GLOBAL slow_query_log = ‘OFF’;
ALTER TABLE mysql.slow_log RENAME mysql.slow_log_drop;
CREATE TABLE mysql
.slow_log
(start_time
TIMESTAMP (6) NOT NULL DEFAULT CURRENT_TIMESTAMP (6) ON UPDATE CURRENT_TIMESTAMP (6),user_host
MEDIUMTEXT NOT NULL,query_time
time(6) NOT NULL,lock_time
time(6) NOT NULL,rows_sent
INT (11) NOT NULL,rows_examined
INT (11) NOT NULL,db
VARCHAR (512) NOT NULL,last_insert_id
INT (11) NOT NULL,insert_id
INT (11) NOT NULL,server_id
INT (10) UNSIGNED NOT NULL,sql_text
MEDIUMBLOB NOT NULL,thread_id
BIGINT (21) UNSIGNED NOT NULL
) ENGINE = CSV DEFAULT CHARSET = utf8 COMMENT = ‘Slow log’;
SET GLOBAL slow_query_log = ‘ON’;
DROP TABLE mysql.slow_log_drop;