mysql 常用命令

-- 连接
mysql -h host -P 3306 -u user -p
mysql -h host -u user -p dbname

-- 版本
SELECT VERSION();
SHOW VARIABLES LIKE 'version%';

-- 当前连接信息
SHOW PROCESSLIST;
SHOW FULL PROCESSLIST;   -- 显示完整 SQL

-- 当前库/用户
SELECT DATABASE();
SELECT USER(), CURRENT_USER();

-- 查看所有库
SHOW DATABASES;

-- 查看表
SHOW TABLES;
SHOW TABLES LIKE 't%';

-- 全局状态
SHOW GLOBAL STATUS;
SHOW GLOBAL STATUS LIKE 'Threads%';
SHOW GLOBAL STATUS LIKE 'Questions';
SHOW GLOBAL STATUS LIKE 'Uptime';

-- 关键指标
SHOW GLOBAL STATUS LIKE 'Connections';        -- 总连接数
SHOW GLOBAL STATUS LIKE 'Max_used_connections'; -- 峰值连接
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool%'; -- 缓冲池
SHOW GLOBAL STATUS LIKE 'Innodb_rows%';        -- 行操作
SHOW GLOBAL STATUS LIKE 'Com_select';          -- 查询数
SHOW GLOBAL STATUS LIKE 'Slow_queries';        -- 慢查询数

-- 引擎状态
SHOW ENGINE INNODB STATUS\G   -- 死锁、锁等待、事务

-- 主从状态
SHOW MASTER STATUS;
SHOW SLAVE STATUS\G
SHOW REPLICA STATUS\G         -- 8.0.22+

-- 查看变量
SHOW VARIABLES LIKE 'max_connections';
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
SELECT @@max_connections;

-- 会话级设置
SET SESSION max_connections = 1000;

-- 全局设置（重启失效）
SET GLOBAL max_connections = 1000;

-- 持久化设置（8.0+，重启保留）
SET PERSIST max_connections = 1000;
SET PERSIST_ONLY max_connections = 1000;

-- 查看连接相关
SHOW VARIABLES LIKE 'max_connections';
SHOW VARIABLES LIKE 'wait_timeout';
SHOW VARIABLES LIKE 'interactive_timeout';

-- 查看进程
SHOW PROCESSLIST;

-- 从 information_schema 查（更详细）
SELECT * FROM information_schema.PROCESSLIST;

-- 按时间排序找长事务
SELECT id, user, host, db, command, time, state, info
FROM information_schema.PROCESSLIST
WHERE command != 'Sleep'
ORDER BY time DESC;

-- 杀连接
KILL <id>;
KILL QUERY <id>;   -- 只杀查询，不断连接

-- 批量杀 Sleep 连接
SELECT CONCAT('KILL ', id, ';')
FROM information_schema.PROCESSLIST
WHERE command = 'Sleep' AND time > 300;

-- 当前运行的事务
SELECT * FROM information_schema.INNODB_TRX;

-- 当前锁
SELECT * FROM information_schema.INNODB_LOCKS;      -- 8.0 前
SELECT * FROM performance_schema.data_locks;         -- 8.0+

-- 锁等待
SELECT * FROM information_schema.INNODB_LOCK_WAITS; -- 8.0 前
SELECT * FROM performance_schema.data_lock_waits;   -- 8.0+

-- 死锁信息
SHOW ENGINE INNODB STATUS\G
-- 开启死锁日志
SET GLOBAL innodb_print_all_deadlocks = ON;

-- 事务隔离级别
SELECT @@transaction_isolation;
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- 慢查询开关
SHOW VARIABLES LIKE 'slow_query_log';
SHOW VARIABLES LIKE 'long_query_time';
SHOW VARIABLES LIKE 'slow_query_log_file';

-- 动态开启
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;
SET GLOBAL log_queries_not_using_indexes = ON;

-- 查看慢查询数量
SHOW GLOBAL STATUS LIKE 'Slow_queries';

-- 慢查询日志内容
SHOW GLOBAL VARIABLES LIKE 'slow_query_log_file';
-- 命令行分析
-- mysqldumpslow -s t /var/log/mysql/slow.log
-- pt-query-digest /var/log/mysql/slow.log

-- 执行计划
EXPLAIN SELECT * FROM t WHERE a = 1;
EXPLAIN FORMAT=JSON SELECT ...;
EXPLAIN ANALYZE SELECT ...;   -- 8.0.18+ 实际执行

-- 查看索引
SHOW INDEX FROM t;
SHOW CREATE TABLE t;

-- 索引基数
SHOW INDEX FROM t;
ANALYZE TABLE t;   -- 更新统计信息

-- 强制索引
SELECT * FROM t FORCE INDEX(idx_a) WHERE a = 1;

-- 表状态
SHOW TABLE STATUS LIKE 't'\G
SHOW TABLE STATUS FROM dbname;

-- 表大小
SELECT table_name,
       ROUND(data_length/1024/1024, 2) AS data_mb,
       ROUND(index_length/1024/1024, 2) AS index_mb,
       ROUND((data_length+index_length)/1024/1024, 2) AS total_mb
FROM information_schema.TABLES
WHERE table_schema = 'dbname'
ORDER BY total_mb DESC;

-- 库大小
SELECT table_schema,
       ROUND(SUM(data_length+index_length)/1024/1024, 2) AS total_mb
FROM information_schema.TABLES
GROUP BY table_schema;

-- 修改表
ALTER TABLE t ADD COLUMN c INT;
ALTER TABLE t ADD INDEX idx_c(c);
ALTER TABLE t ENGINE=InnoDB;

-- 在线 DDL
ALTER TABLE t ADD COLUMN c INT, ALGORITHM=INPLACE, LOCK=NONE;

-- 查看用户
SELECT user, host FROM mysql.user;

-- 创建用户
CREATE USER 'u'@'%' IDENTIFIED BY 'pwd';

-- 授权
GRANT SELECT, INSERT ON db.* TO 'u'@'%';
GRANT ALL PRIVILEGES ON *.* TO 'u'@'%';
FLUSH PRIVILEGES;

-- 查看权限
SHOW GRANTS FOR 'u'@'%';

-- 回收权限
REVOKE INSERT ON db.* FROM 'u'@'%';

-- 改密码
ALTER USER 'u'@'%' IDENTIFIED BY 'newpwd';

-- 删除用户
DROP USER 'u'@'%';

# 逻辑备份
mysqldump -u root -p dbname > db.sql
mysqldump -u root -p --all-databases > all.sql
mysqldump -u root -p --single-transaction --routines --triggers dbname > db.sql

# 恢复
mysql -u root -p dbname < db.sql
source db.sql;

# 物理备份（XtraBackup）
xtrabackup --backup --target-dir=/backup
xtrabackup --prepare --target-dir=/backup
xtrabackup --copy-back --target-dir=/backup

-- 导入导出
SELECT * FROM t INTO OUTFILE '/tmp/t.csv' FIELDS TERMINATED BY ',';
LOAD DATA INFILE '/tmp/t.csv' INTO TABLE t FIELDS TERMINATED BY ',';

-- binlog 恢复
SHOW BINARY LOGS;
SHOW BINLOG EVENTS IN 'mysql-bin.000001';
-- mysqlbinlog mysql-bin.000001 | mysql -u root -p

-- 主库
SHOW MASTER STATUS;
SHOW BINARY LOGS;
PURGE BINARY LOGS TO 'mysql-bin.000010';
PURGE BINARY LOGS BEFORE '2024-01-01';

-- 从库
SHOW SLAVE STATUS\G
SHOW REPLICA STATUS\G

-- 启动/停止
START SLAVE;
STOP SLAVE;
START REPLICA;
STOP REPLICA;

-- 跳过错误
STOP SLAVE;
SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
START SLAVE;

-- 重设复制
STOP SLAVE;
RESET SLAVE ALL;
CHANGE MASTER TO
  MASTER_HOST='host',
  MASTER_USER='user',
  MASTER_PASSWORD='pwd',
  MASTER_LOG_FILE='mysql-bin.000001',
  MASTER_LOG_POS=154;
START SLAVE;

-- 并行复制
SET GLOBAL slave_parallel_workers = 4;
SET GLOBAL slave_parallel_type = 'LOGICAL_CLOCK';

-- 只读
SET GLOBAL read_only = ON;
SET GLOBAL super_read_only = ON;

-- 错误日志位置
SHOW VARIABLES LIKE 'log_error';

-- binlog
SHOW VARIABLES LIKE 'log_bin';
SHOW VARIABLES LIKE 'binlog_format';
SHOW VARIABLES LIKE 'sync_binlog';

-- redo log
SHOW VARIABLES LIKE 'innodb_log%';
SHOW VARIABLES LIKE 'innodb_flush_log_at_trx_commit';

-- 通用日志
SET GLOBAL general_log = ON;
SET GLOBAL general_log_file = '/tmp/general.log';

# 连接
mysql -h host -u user -p

# 执行 SQL
mysql -e "SHOW PROCESSLIST;"

# 导入
mysql dbname < db.sql

# 导出
mysqldump -u root -p dbname > db.sql

# 慢日志分析
mysqldumpslow -s t -t 10 slow.log
pt-query-digest slow.log

# 检查大表
mysql -e "SELECT ..."

# 监控
mysqladmin -u root -p status
mysqladmin -u root -p extended-status
mysqladmin -u root -p processlist
mysqladmin -u root -p kill <id>
mysqladmin -u root -p shutdown

# 主从
mysql -e "SHOW SLAVE STATUS\G"

SHOW VARIABLES LIKE 'max_connections';
SHOW GLOBAL STATUS LIKE 'Max_used_connections';
SHOW PROCESSLIST;

SHOW GLOBAL STATUS LIKE 'Slow_queries';
SHOW VARIABLES LIKE 'slow_query_log%';
EXPLAIN SELECT ...;

SHOW ENGINE INNODB STATUS\G
SELECT * FROM information_schema.INNODB_TRX;
SELECT * FROM performance_schema.data_lock_waits;

SHOW SLAVE STATUS\G   -- 看 Seconds_Behind_Master
SHOW PROCESSLIST;     -- 看 SQL 线程状态

SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool%';