MySQL 日常维护

-- 查看所有用户及其主机信息
SELECT User, Host FROM mysql.user;

-- 查看用户详细信息
SELECT User, Host, authentication_string FROM mysql.user;

-- 查看用户权限信息
SELECT User, Host, Select_priv, Insert_priv, Update_priv, Delete_priv
FROM mysql.user;

-- 查看特定主机的用户
SELECT User, Host FROM mysql.user WHERE Host = 'localhost';

-- 查看远程用户
SELECT User, Host FROM mysql.user WHERE Host != 'localhost';

-- 查看用户创建时间（MySQL 5.7+）
SELECT User, Host, Create_time FROM mysql.user;

-- 查看用户最后修改密码时间
SELECT User, Host, password_last_changed FROM mysql.user;

-- 创建用户并设置密码
CREATE USER 'username'@'host' IDENTIFIED BY 'password';

-- 示例：创建本地用户
CREATE USER 'testuser'@'localhost' IDENTIFIED BY 'password123';

-- 示例：创建远程用户
CREATE USER 'remoteuser'@'%' IDENTIFIED BY 'password123';

-- 删除用户
DROP USER 'username'@'host';

-- 示例
DROP USER 'testuser'@'localhost';

-- 修改用户密码
ALTER USER 'username'@'host' IDENTIFIED BY 'new_password';

-- 示例
ALTER USER 'testuser'@'localhost' IDENTIFIED BY 'newpassword123';

-- 授予所有权限
GRANT ALL PRIVILEGES ON database_name.* TO 'username'@'host';

-- 授予特定权限
GRANT SELECT, INSERT, UPDATE, DELETE ON database_name.* TO 'username'@'host';

-- 授予所有数据库的所有权限（超级用户）
GRANT ALL PRIVILEGES ON *.* TO 'username'@'host';

-- 刷新权限
FLUSH PRIVILEGES;

-- 撤销所有权限
REVOKE ALL PRIVILEGES ON database_name.* FROM 'username'@'host';

-- 撤销特定权限
REVOKE DELETE ON database_name.* FROM 'username'@'host';

-- 刷新权限
FLUSH PRIVILEGES;

-- 查看当前用户权限
SHOW GRANTS;

-- 查看指定用户权限
SHOW GRANTS FOR 'username'@'host';

-- 分析表
ANALYZE TABLE table_name;

-- 优化表
OPTIMIZE TABLE table_name;

-- 检查表
CHECK TABLE table_name;

-- 修复表
REPAIR TABLE table_name;

-- 查看系统变量
SHOW VARIABLES;

-- 查看系统状态
SHOW STATUS;

-- 查看进程列表
SHOW PROCESSLIST;

-- 查看引擎状态
SHOW ENGINE INNODB STATUS;

-- 启用慢查询日志
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 2;

-- 查看慢查询日志位置
SHOW VARIABLES LIKE 'slow_query_log_file';

-- 查看当前连接数
SHOW STATUS LIKE 'Threads_connected';

-- 查看最大连接数
SHOW VARIABLES LIKE 'max_connections';

-- 查看缓存命中率
SHOW STATUS LIKE 'Qcache_hits';
SHOW STATUS LIKE 'Qcache_inserts';

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

-- 查看二进制日志
SHOW BINARY LOGS;

-- 查看当前二进制日志
SHOW MASTER STATUS;

-- 清理二进制日志
PURGE BINARY LOGS TO 'mysql-bin.000010';

-- 清理所有二进制日志
PURGE BINARY LOGS BEFORE DATE(NOW() - INTERVAL 7 DAY);

-- 删除匿名用户
DELETE FROM mysql.user WHERE User='';

-- 删除测试数据库
DROP DATABASE IF EXISTS test;
DELETE FROM mysql.db WHERE Db='test' OR Db='test\\_%';

-- 刷新权限
FLUSH PRIVILEGES;

-- 更新统计信息
ANALYZE TABLE table_name;

-- 清理临时表
DROP TEMPORARY TABLE IF EXISTS temp_table_name;

-- 检查表完整性
CHECK TABLE table_name;

-- 查看连接超时设置
SHOW VARIABLES LIKE 'wait_timeout';
SHOW VARIABLES LIKE 'interactive_timeout';

-- 查看当前连接
SHOW PROCESSLIST;

-- 查看当前锁
SHOW ENGINE INNODB STATUS;

-- 查看锁等待
SELECT * FROM information_schema.INNODB_LOCK_WAITS;

-- 查看内存使用情况
SHOW STATUS LIKE 'Created_tmp%';
SHOW VARIABLES LIKE 'tmp_table_size';
SHOW VARIABLES LIKE 'max_heap_table_size';