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;