MySQL 管理

一、MySQL 访问控制系统

MySQL 实现了复杂的访问控制和权限系统，允许您创建用于处理客户端操作的全面的访问规则，并有效防止 未经授权的客户端访问数据库系统。

当客户端连接到服务器时，MySQL 访问控制有两个阶段：

• 连接验证：连接到 MySQL 数据库服务器的客户端需要有一个有效的用户名和密码。

• 请求验证：当连接成功建立后，对于客户端发出的每个语句，MySQL 会检查客户端是否具有足够的权限来执行该特定语句。

二、用户账户管理

1、查看用户

MySQL 在安装时会自动创建一个名为 mysql 的数据库，mysql 数据库中有一张 user 表，包含用户账户及权限信息

查询数据库当前用户信息

-- 使用 mysql 数据库
use mysql; 
-- 查询用户信息
select * from user;

2、创建用户

创建一个新用户 dbadmin，密码为 pwd123

create user dbadmin@localhost identified by 'pwd123';
select * from user;

查看新用户 dbadmin 的权限

show grants for dbadmin@localhost;

3、用户授权

给新用户 dbadmin 授予超级管理员权限

-- 授予全部权限
grant all on *.* to 'dbadmin'@'localhost' with grant option;
-- 查询权限
show grants for dbadmin@localhost;

也可以授予用户部分权限，例如：

CREATE USER rfc IDENTIFIED BY 'mypasswd';
GRANT SELECT, UPDATE, DELETE ON test.* TO rfc;

• 语法解析：上面代码表示创建一个 rfc 账号，密码为 mypasswd，该用户只能操作 test 数据库中的表，并且只能查询、修改和删除表中的数据，如果用 rfc 用户在 test 数据库创建表就会报错。

使用 rfc 用户连接 mysql，然后在 test 数据库中新建一张 emp 表，观察是否能够成功。rfc 连接 mysql：

• rfc 的权限不能够在 test 数据库创建表，但是可以查询

4、撤销权限

在 MySQL 数据库中，可以使用 REVOKE 撤销用户权限。

撤销 rfc 用户的 select 权限

revoke select on test.* from rfc;
show GRANTS for rfc;

注意：

• 要从用户帐户撤销权限，必须具有 GRANT OPTION 权限和要撤销的权限；

• 注意授权是 grant…to…，而撤销权限是 revoke…from…。

因此上面的代码需要先用 admin 账户连接 mysql 才能执行，撤销权限后，再用 rfc 账户连接到 mysql，进行查 询操作，代码如下：

select * from users;

三、数据表维护

1、查看表

查看 test 数据库中的所有表和视图

use test;-- 使用 test 数据库
show tables;-- 显示表

Show tables 命令可以查看指定数据库中的所有表和视图，如果需要显示类型可以使用 show full tables，例如：

use test;-- 使用 test 数据库
show full tables;-- 显示表

也可以加上查询条件，例如：

use test;-- 使用 test 数据库 
show tables like '%t%';-- 显示表

2、分析表

• MySQL 查询优化器是 MySQL 服务器的重要组成部分，为查询创建了一个最佳的查询执行计划。查询优化器使用存储的密钥分发和其他因素来决定在执行连接时应将表进行连接的顺序

• 然而，密钥分发可能有时不准确，例如，在表中进行了大量数据更改(包括插入，删除或更新)之后。如果密 钥分发不准确，则查询优化器可能会选择可能导致严重性能问题的错误查询执行计划。

• 要解决此问题，您可以为该表运行 ANALYZE TABLE 语句。

分析 test 数据库中的 sx_target 表

ANALYZE TABLE sx_target;

如果运行 ANALYZE TABLE 语句后表没有变化，MySQL 将不会再次分析表

3、优化表

在使用数据库时，可以进行许多更改，如插入，删除或更新表中的数据，这可能会导致表的物理存储碎片化,因此，数据库服务器的性能下降。

对 test 数据库中的 goods 表进行碎片整理

OPTIMIZE table goods;

4、检查表

数据库服务器可能会发生错误，例如，服务器宕机、意外关闭，将数据写入硬盘时出错，等等。这些情况可能导致数据库运行不正确，并且在最坏的情况下可能会崩溃。

检查 test 数据库中的 goods 表的完整性

check table goods;

CHECK TABLE 语句仅检测数据库表中的问题，但不会修复它们。要修复表，请使用 REPAIR TABLE 语句。

5、修复表

REPAIR TABLE 语句允许您修复数据库表中发生的一些错误。MySQL 不保证 REPAIR TABLE 语句可以修复表所有可能的错误，并且 REPAIR TABLE 仅适用于 MyISAM，ARCHIVE 和 CSV 表。

假设 test 数据库中的 goods 表有错误，修复 goods 表

REPAIR table goods;

四、数据库维护

1、备份数据库

• 数据库备份很重要，没有备份，一旦出错只能跑路。要备份 MySQL 数据库，数据库首先必须存在于数据库服务器中，并且您也可以访问该服务器。

• 要使用 mysqldump 工具，如果是远程服务器，先连接数据库所在的服 务器，然后打开命令行工具，目录切换到 mysql 的 bin 目录，例如我的目录是：C:\Program Files\MySQL\MySQL Server 5.7\bin。

备份数据库语法如下：

mysqldump -u [username] –p[password] [database_name] > [dump_file.sql]

参数解释：

[username]：有效的 MySQL 用户名；

[password]：用户的有效密码。请注意，-p 和密码之间没有空格；

database_name]: 要备份的数据库名称；

如果只想备份数据库结构而不需要备份数据，那么只需要添加一个选项–no-data 来告诉 mysqldump 只需要导出数据库结构即可，例如：

mysqldump -u root -padmin --no-data test > D:\test.sql

如果要将多个数据库备份到一个数据文件，代码如下：

mysqldump -u[username] -p[password] --databases [dbname1 dbname2] > filename.sql