MySQL数据库管理指南

kryiea2024年1月1日

MySQL数据库管理指南

本文详细介绍MySQL数据库的管理、维护及日常运维操作，帮助您高效管理数据库系统。

1. MySQL服务管理

1.1 启动/停止MySQL服务器

1.1.1 Linux系统

在Linux系统上，可以使用以下命令来启动和停止MySQL服务器。请注意，确保有足够的权限执行这些操作。

启动MySQL服务器：

sudo service mysql start

或者，取决于你的Linux发行版，也可以使用以下命令：

sudo systemctl start mysql

停止MySQL服务器：

sudo service mysql stop

或者，取决于你的Linux发行版，也可以使用以下命令：

sudo systemctl stop mysql

这些命令将启动或停止MySQL服务器。如果你正在使用不同的发行版或MySQL版本，请查阅相应的文档以获取更多信息。

1.1.2 Windows系统

-- 启动MySQL
net start mysql
-- 创建Windows服务
sc create mysql binPath= mysqld_bin_path(注意：等号与值之间有空格)

基本连接命令：

mysql -h 地址 -P 端口 -u 用户名 -p 密码
SHOW PROCESSLIST -- 显示哪些线程正在运行
SHOW VARIABLES -- 显示系统变量信息

或者

在 Windows 上，MySQL 也会安装为一个服务。请通过一下步骤管理 MySQL 服务器：

进入服务管理页面，找到 mysql 服务
双击 mysql 服务，弹出服务对话框
选择相应的操作来启动，停止或重启动 MySQL 服务器

2. 用户管理

2.1 创建用户

CREATE USER [IF NOT EXISTS] account_name IDENTIFIED BY 'password';

CREATE USER 关键字后指定帐户名称。帐户名称由两部分组成：username 和 hostname，以 @ 符号分隔：username@hostname
username 是用户的名称，而 hostname 是用户连接到 MySQL 服务器的主机名
hostname 帐户名称的部分是可选的。如果省略它，用户可以从任何主机连接
没有主机名的帐户名等效于：username@%
如果 username 和 hostname 中包含空格或 - 等特殊字符，则需要将用户名和主机名分别按如下方式引用：'username'@'hostname'
除了单引号 (')，还可以使用反引号 (``) 或双引号 (")
IDENTIFIED BY 关键字后指定用户的密码
IF NOT EXISTS 选项仅在新用户不存在时有条件地创建新用户
请注意，CREATE USER 语句创建了一个没有任何权限的新用户。要向用户授予权限，请使用 GRANT 语句。

2.2 删除用户

DROP USER sqliz@localhost;

如果删除的用户在删除之前已经登录了一个会话，删除后并不会影响此会话，直到会话结束。这也会会带来危险。

2.3 重命名用户

首先，使用管理员权限登录到MySQL服务器：

mysql -u 管理员用户名 -p

接下来，执行以下SQL语句，将'olduser'替换为要重命名的用户，而'newuser'是新的用户名：

RENAME USER 'olduser'@'localhost' TO 'newuser'@'localhost';

这个命令将在localhost上的MySQL中重命名用户。如果用户存在于不同的主机上，请相应地更改主机部分。

如果用户有全局权限，你可能需要同时重命名用户及其对应的权限。例如：

RENAME USER 'olduser'@'localhost' TO 'newuser'@'localhost';
UPDATE mysql.user SET user = 'newuser' WHERE user = 'olduser';
FLUSH PRIVILEGES;

记得替换'olduser'和'newuser'为实际的用户名。最后，使用FLUSH PRIVILEGES;来确保更改立即生效。

2.4 修改用户密码

在 MySQL 中，可以使用 UPDATE、SET PASSWORD、ALTER USER 语句来更改用户的密码

UPDATE语法：

MySQL 5.7.6 前

MySQL 5.7.6 后用户表使用列 authentication_string 来存储密码。并且，它删除了 password 列。

SET PASSWORD语法：

ALTER USER语法：

2.5 列出用户

查看全部用户：

SELECT user, host FROM mysql.user; -- 只输出 usr、host 列
DESC mysql.user; -- 输出完整的用户表

查看当前用户的认证信息：

SELECT current_user();

查看当前用户的信息：

SELECT user();

查看当前登录的用户：

SHOW PROCESSLIST;

2.6 锁定用户账户

查询用户的锁定状态：

SELECT user, host, account_locked FROM mysql.user;

锁定一个现有用户：

ALTER USER 'sqliz'@'%' ACCOUNT LOCK;

创建一个锁定的用户：

CREATE user 'sqliz2'@'%'
IDENTIFIED by 'SqLiZ9879123!'
ACCOUNT LOCK;

锁定用户的连接次数：

MySQL 维护了一个变量 Locked_connects，它用来保存锁定的用户尝试连接到服务器的次数。

当锁定的帐户尝试登录时，Locked_connects 变量的值将加 1。

2.7 解锁用户

ALTER USER 'sqliz'@'%', 'sqliz2'@'%' ACCOUNT UNLOCK;

3. 权限管理

3.1 用户授权

当创建了一个新用户之后，这个新的用户可以登录 MySQL 数据库服务器，但是他可能没有任何权限。只有在赋予他数据库和相关表的权限之后，他才可以进行选择数据库和查询等操作。

语法：

GRANT privilege_type [,privilege_type],...
ON privilege_object
TO user_account;

privilege_type：要赋予给用户的权限。常用的包括：ALL, SELECT, UPDATE, DELETE, ALTER, DROP 以及 INSERT 等。官方文档参考：MySQL权限
privilege_object：权限对象。可以是所有对象，也可以是某个数据库中的所有对象，表等。常用的包括：*、database.*、db_name.*、db_name.table_name、table_name 等。
user_account：用户账户。它使用 username@host 的形式。

权限举例：

3.2 撤销用户权限

REVOKE语法：

REVOKE
   priv1 [, priv2 [, ...] ]
ON [object_type] privilege_level
FROM user1 [, user2 [, ...]];

撤销权限类型：

表列级别

数据库级别
全局级别

revoke生效时机：

3.3 显示用户权限

显示当前用户权限：

SHOW GRANTS FOR CURRENT_USER;

4. 角色管理

4.1 角色概述

对于一个线上的 MySQL 数据库服务器来说，它可能有很多使用者。其中某些用户可能具有相同的权限。
作为一个数据库管理员或者运维人员，可能要对多个用户赋予相同的权限。这个过程很耗时，也很容易带来错误。
MySQL 中的角色可以让你简化授权的过程。您只需要为权限相同的用户创建一个角色，并将角色赋予这些用户即可。
还可以创建多个角色，授权给不同的用户。也可以为一个用户赋予多个不同的角色。

4.2 角色操作语法

创建角色：CREATE ROLE role_name1 [, role_name2 [, ...]];
删除角色：DROP ROLE role_name1 [, role_name2 [, ...]];
授权角色：

撤权角色：

4.3 角色操作示例

创建角色

授权角色

分配角色

显示角色

5. 数据库备份与恢复

5.1 备份数据库

MySQL 提供了 mysqldump 工具用于从 MySQL 数据库服务器中导出数据库结构和数据。

mysqldump 介绍：

mysqldump 工具一般会随着安装 MySQL 数据库时自动安装。它能将一个或者多个数据库，或者数据库中的一个或者多个表导出为一个 SQL 文件，包括 DDL 语句和 DML 语句。
通常，安装完 MySQL 服务器后，您可以直接使用 mysqldump 工具。
如果找不到 mysqldump 工具，请将 MySQL 安装目录下的 bin 目录配置到环境变量 PATH 中。或者导航到 MySQL 安装目录下的 bin 目录下再使用 mysqldump 工具。

mysqldump 语法：

mysqldump --user=<username> --password=<password> --result-file=<path_to_backup_file> --databases <database_name>

<username> 是登录到 MySQL 服务器的用户帐户。
<password> 是 <username> 的密码。
<path_to_backup_file> 是备份文件的路径。
<database_name> 是你要备份的数据库的名称。如果要备份多个数据库，请用逗号分隔多个数据库的名字。

实例：

5.2 恢复数据库

登录后用 source 命令运行 SOURCE 命令恢复数据库，如：
```
SOURCE /bak/sakila.sql
```

不登录直接恢复

mysql --user=root --password=<password> < /bak/sakila.sql