MySQL 数据库管理与操作指南

MySQL 数据库管理与操作指南

在日常的数据库管理过程中，我们经常需要执行各种操作，比如忘记密码后的应急处理、数据库备份与恢复、用户权限管理、数据库引擎查看与切换等。本文将对这些常见操作进行整理和说明，便于大家参考和学习。

1. 忘记 MySQL 密码的处理方法

当忘记 MySQL 的 root 密码时，可以按照以下步骤进行重置：

1. 打开 MySQL 配置文件 /etc/my.cnf，在 [mysqld] 部分添加 skip-grant-tables，然后重启数据库。

   systemctl restart mysqld
   

2. 使用以下命令进入 MySQL 并修改 root 密码：

   set password for 'root'@'localhost'=password('newpassword');
   

3. 修改完成后，记得将 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 删除表

删除表的方法有三种，操作强度从强到弱依次为：

1. DROP TABLE：直接删除表，数据不可恢复。

   DROP TABLE table_name;
   

2. TRUNCATE TABLE：删除表中所有数据，不可与 WHERE 一起使用，且不可回滚。

   TRUNCATE TABLE table_name;
   

3. 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 的区别

1. 事务支持：TRUNCATE 不支持事务回滚，而 DELETE 支持。
2. 重置自增 ID：TRUNCATE 会重置自增 ID，而 DELETE 不会。
3. 触发器：TRUNCATE 不会触发 DELETE 触发器，而 DELETE 会。
4. 执行效率：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 数据库，并且拥有所有数据库的所有权限。