MySQL 数据库管理与操作指南
文章目录
- MySQL 数据库管理与操作指南
- 1. 忘记 MySQL 密码的处理方法
- 2. MySQL 数据库备份与恢复
- 2.1 数据库备份
- 2.2 数据库恢复
- 3. MySQL 用户与权限管理
- 3.1 创建用户与授权
- 3.2 查看所有用户
- 3.3 删除用户
- 4. 关闭 GTID 复制模式
- 5. 查看数据表的存储引擎
- 5.1 查看 MySQL 支持的存储引擎
- 5.2 查看当前默认存储引擎
- 5.3 查看具体表的存储引擎
- 5.4 查看所有表的状态
- 6. 数据库操作命令
- 6.1 删除表
- 6.2 MySQL 中 DELETE 与 TRUNCATE 的区别
- 7. 查看单个数据库的大小
- 8. 网络延迟、磁盘 I/O 与 CPU 优化
- 9. 支持用户远程连接 MySQL 数据库
MySQL 数据库管理与操作指南
在日常的数据库管理过程中，我们经常需要执行各种操作，比如忘记密码后的应急处理、数据库备份与恢复、用户权限管理、数据库引擎查看与切换等。本文将对这些常见操作进行整理和说明，便于大家参考和学习。
1. 忘记 MySQL 密码的处理方法
当忘记 MySQL 的 root 密码时，可以按照以下步骤进行重置：
-
打开 MySQL 配置文件
/etc/my.cnf，在[mysqld]部分添加skip-grant-tables，然后重启数据库。systemctl restart mysqld -
使用以下命令进入 MySQL 并修改 root 密码：
set password for 'root'@'localhost'=password('newpassword'); -
修改完成后，记得将
skip-grant-tables删除，并重启数据库恢复正常模式。
2. MySQL 数据库备份与恢复
2.1 数据库备份
可以使用 mysqldump 工具备份数据库，并将备份文件压缩：
/usr/local/mysql/bin/mysqldump -uroot -p'password' database_name | gzip > backup.sql.gz
2.2 数据库恢复
恢复备份数据时，首先解压备份文件，然后导入到数据库：
gunzip < backup.sql.gz | mysql -u root -p'password' database_name
3. MySQL 用户与权限管理
3.1 创建用户与授权
CREATE USER 'username'@'%' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON database_name.* TO 'username'@'%';
FLUSH PRIVILEGES;
3.2 查看所有用户
SELECT DISTINCT CONCAT('User: ''',user,'''@''',host,''';') AS query FROM mysql.user;
3.3 删除用户
DROP USER 'username'@'localhost';
4. 关闭 GTID 复制模式
在某些情况下，可能需要关闭 MySQL 的 GTID 复制模式，步骤如下：
SET GLOBAL GTID_MODE = ON_PERMISSIVE;
SET GLOBAL GTID_MODE = OFF_PERMISSIVE;
SET GLOBAL GTID_MODE = OFF;
SET @@global.enforce_gtid_consistency = OFF;
5. 查看数据表的存储引擎
5.1 查看 MySQL 支持的存储引擎
SHOW ENGINES;
5.2 查看当前默认存储引擎
SHOW VARIABLES LIKE '%storage_engine%';
5.3 查看具体表的存储引擎
SHOW CREATE TABLE table_name;
5.4 查看所有表的状态
SHOW TABLE STATUS;
6. 数据库操作命令
6.1 删除表
删除表的方法有三种，操作强度从强到弱依次为：
-
DROP TABLE：直接删除表，数据不可恢复。
DROP TABLE table_name; -
TRUNCATE TABLE：删除表中所有数据，不可与
WHERE一起使用，且不可回滚。TRUNCATE TABLE table_name; -
DELETE FROM：删除表中指定行的数据，可回滚。
DELETE FROM table_name WHERE condition;举例
delect from slams_app where id >=31 and id <= 208;这是删除31条到208条，包括31和208
6.2 MySQL 中 DELETE 与 TRUNCATE 的区别
- 事务支持：
TRUNCATE不支持事务回滚，而DELETE支持。 - 重置自增 ID：
TRUNCATE会重置自增 ID，而DELETE不会。 - 触发器：
TRUNCATE不会触发DELETE触发器，而DELETE会。 - 执行效率：
TRUNCATE比DELETE更高效，尤其在删除大表数据时。
7. 查看单个数据库的大小
SELECT CONCAT(TRUNCATE(SUM(data_length)/1024/1024,2),'MB') AS data_size, CONCAT(TRUNCATE(SUM(max_data_length)/1024/1024,2),'MB') AS max_data_size, CONCAT(TRUNCATE(SUM(data_free)/1024/1024,2),'MB') AS data_free, CONCAT(TRUNCATE(SUM(index_length)/1024/1024,2),'MB') AS index_size
FROM information_schema.tables
WHERE table_schema = 'database_name';
8. 网络延迟、磁盘 I/O 与 CPU 优化
在数据库管理中，除了 SQL 语句的优化外，还需考虑网络延迟、磁盘 I/O 与 CPU 的影响。通过监控这些系统指标，可以有效提高数据库的性能。
9. 支持用户远程连接 MySQL 数据库
为了让 MySQL 用户可以从远程主机连接数据库，需要授予相应的权限：
GRANT ALL PRIVILEGES ON *.* TO 'username'@'%' IDENTIFIED BY 'password' WITH GRANT OPTION;
FLUSH PRIVILEGES;
'username'@'%'：%表示允许从任何远程主机连接。如果只允许特定主机连接，可以将%替换为特定主机的 IP 地址。WITH GRANT OPTION：允许该用户将权限授予其他用户。
例如：
GRANT ALL PRIVILEGES ON *.* TO 'cbcioscar'@'%' IDENTIFIED BY 'Cbcioscar!@#' WITH GRANT OPTION;
FLUSH PRIVILEGES;
通过上述命令，cbcioscar 用户可以从任何远程主机连接到 MySQL 数据库，并且拥有所有数据库的所有权限。