文章目录
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 数据库,并且拥有所有数据库的所有权限。