MySQL 数据库运维与管理相关命令

最开始，服务器部署使用的宝塔面板，数据库运维管理全都是在宝塔上可视化操作，并没有真正的面对数据库执行过命令。出于某些原因，后续不准备再继续使用宝塔面板，所以，记录一下关于 MySQL 数据库管理方面相关的方法和命令。

一、控制台

在服务器上，可使用 mysql 命令打开控制台：

mysql -uroot -p
Enter password: [输入密码]

二、用户管理

2.1 查询用户信息

进入 mysql 库，查询 user 表可查看用户信息：

• User：用户名
• Host：允许登录用户的主机，% 表示允许所有主机登录。

use mysql;
select * from user;

2.2 开启远程登录权限

同理，命令中的 % 表示允许所有远程主机登录，127.0.0.1 或 localhost 表示允许本机登录。

# 赋予权限并修改密码
grant all privileges on *.* to 'root'@'%' with grant option;
alter user 'root'@'%' identified with mysql_native_password by 'root账户密码';
# 重新加载权限表
flush privileges;

2.3 创建新用户

1. 使用 CREATE USER 创建用户

   命令语法：

   CREATE USER 'username'@'host' [ IDENTIFIED BY [ PASSWORD ] 'password' ] [ ,'username'@'host' [ IDENTIFIED BY [ PASSWORD ] 'password' ]]
   

   使用示例：

   CREATE USER 'test'@'localhost' IDENTIFIED BY 'testpassword';
   

2. 使用 GRANT 创建用户

   该命令可以在创建用户同时指定用户的访问权限

   命令语法：

   GRANT <privileges> ON database.table TO user [IDENTIFIED BY [PASSWORD] 'password']
   

   使用示例：

   # 'test'.* 表示对 test库下的所有表具有ALL权限
   GRANT ALL ON test.* TO 'test'@localhost IDENTIFIED BY 'testpassword';
   

2.4 权限管理

权限类型（<privileges>）：

• SELECT：表示授予用户可以使用 SELECT 语句访问特定数据库中所有表和视图的权限。
• INSERT：表示授予用户可以使用 INSERT 语句向特定数据库中所有表添加数据行的权限。
• DELETE：表示授予用户可以使用 DELETE 语句删除特定数据库中所有表的数据行的权限。
• UPDATE：表示授予用户可以使用 UPDATE 语句更新特定数据库中所有数据表的值的权限。
• REFERENCES：表示授予用户可以创建指向特定的数据库中的表外键的权限。
• CREATE：表示授权用户可以使用 CREATE TABLE 语句在特定数据库中创建新表的权限。
• ALTER：表示授予用户可以使用 ALTER TABLE 语句修改特定数据库中所有数据表的权限。
• SHOW VIEW：表示授予用户可以查看特定数据库中已有视图的视图定义的权限。
• CREATE ROUTINE：表示授予用户可以为特定的数据库创建存储过程和存储函数的权限。
• ALTER ROUTINE：表示授予用户可以更新和删除数据库中已有的存储过程和存储函数的权限。
• INDEX：表示授予用户可以在特定数据库中的所有数据表上定义和删除索引的权限。
• DROP：表示授予用户可以删除特定数据库中所有表和视图的权限。
• CREATE TEMPORARY TABLES：表示授予用户可以在特定数据库中创建临时表的权限。
• CREATE VIEW：表示授予用户可以在特定数据库中创建新的视图的权限。
• EXECUTE ROUTINE：表示授予用户可以调用特定数据库的存储过程和存储函数的权限。
• LOCK TABLES：表示授予用户可以锁定特定数据库的已有数据表的权限。
• ALL 或 ALL PRIVILEGES：表示以上所有权限。

指定授权范围：

• *：表示当前数据库中的所有表。
• *.*：表示所有数据库中的所有表。
• db_name.*：表示某个数据库中的所有表，db_name 指定数据库名。
• db_name.tbl_name：表示某个数据库中的某个表或视图，db_name 指定数据库名，tbl_name 指定表名或视图名。
• tbl_name：表示某个表或视图，tbl_name 指定表名或视图名。
• db_name.routine_name：表示某个数据库中的某个存储过程或函数，routine_name 指定存储过程名或函数名。

具体命令：

1. 查询指定用户权限

   show grants for root;
   

2. 授予用户权限

   命令语法：

   GRANT <privileges> ON database.table TO 'username'@'host';
   

   使用示例：

   # 为 test 用户授予 test 库下所有表的 SELECT, INSERT 权限
   GRANT SELECT, INSERT ON test.* TO 'test'@'%';
   

3. 回收用户权限

   命令语法

   REVOKE <privileges> ON database.table FROM 'username'@'host';
   

   使用示例：

   # 回收 test 用户在 test 库下所有表的 SELECT, INSERT 权限
   REVOKE SELECT, INSERT ON test.* FROM 'test'@'%';
   

2.5 删除用户

可使用 DROP 命令删除用户，删除用户时需指定 host，默认删除 host 为 % 的账户，如果有其他账户不会被一起删除，需要指定 host 才能删除。

命令语法：

DROP USER 'username'@'host' [ , 'username'@'host' ]

使用示例：

# 删除 test 用户的 localhost 主机
DROP USER 'test'@'localhost';

2.6 修改用户密码

1. 修改登录用户的密码

   ALTER USER USER() IDENTIFIED BY 'new_password';
   

三、库管理

3.1 创建库

命令语法：

CREATE DATABASE [IF NOT EXISTS] <数据库名>
[[DEFAULT] CHARACTER SET <字符集名>] 
[[DEFAULT] COLLATE <校对规则名>];

命令说明：

• IF NOT EXISTS：在创建数据库之前进行判断，只有该数据库目前尚不存在时才能执行操作。此选项可以用来避免命令因数据库已经存在而报错。
• [DEFAULT] CHARACTER SET：指定数据库的字符集。指定字符集的目的是为了避免在数据库中存储的数据出现乱码的情况。如果在创建数据库时不指定字符集，那么就使用系统的默认字符集。
• [DEFAULT] COLLATE：指定字符集的默认校对规则。

常见字符集说明：

常见校对规则说明：

校验规则指的是字符集比较大小的时候依据的准则，每种字符集都包含自己默认的校验规则。针对规则名称最后面的后缀，我们可以总结如下：

• _ai ：不区分重音

• _as ：区分重音

• _ci ：不区分大小写

• _cs ：区分大小写

• _bin ：二进制

使用示例：

# 创建 test 数据库
CREATE DATABASE test;

# 创建 test 数据库，并指定字符集和校验规则
CREATE DATABASE test DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_chinese_ci;

3.2 查询所有库

SHOW DATABASES;

3.3 查看字符集

SHOW CREATE DATABASE <数据库名>;

3.4 删除库

DROP DATABASE <数据库名>;