MySQL数据库常用脚本
- 显示数据库版本信息:
SELECT @@version;
- 显示数据库实例信息:
SHOW VARIABLES LIKE "%version%";
- 显示数据库表空间使用情况:
SELECT table_schema AS "Database", SUM(data_length + index_length) / 1024 / 1024 AS "Size (MB)" FROM information_schema.tables GROUP BY table_schema;
- 显示数据库连接信息:
SHOW PROCESSLIST;
- 示数据库查询缓存命中率:
SHOW STATUS LIKE 'Qcache_hits';
- 显示数据库锁信息:
SHOW OPEN TABLES WHERE In_use > 0;
- 显示数据库慢查询:
SELECT * FROM mysql.slow_log;
- 显示数据库索引统计信息:
SHOW INDEX FROM table_name;
- 显示数据库存储过程:
SHOW PROCEDURE STATUS;
- 显示数据库的函数:
SHOW FUNCTION STATUS;
- 显示数据库备份脚本:
mysqldump -u username -p database_name > backup.sql
- 显示数据库恢复脚本:
mysql -u username -p database_name < backup.sql
- 查询数据库用户列表:
SELECT User, Host FROM mysql.user;
- 创建新用户并授权:
CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON database_name.* TO 'newuser'@'localhost';
FLUSH PRIVILEGES;
- 修改用户密码:
ALTER USER 'username'@'host' IDENTIFIED BY 'new_password';
FLUSH PRIVILEGES;
- 优化表:
OPTIMIZE TABLE table_name;
- 查看数据库状态:
SHOW STATUS;