MySQL管理和配置

10. optimize table

命令：optimize [ NO_WRITE_TO_BINLOG | LOCAL ] table「tbl_name」,「tbl_name」...

功能： 重新组织表数据和相关索引数据的物理存储，减少存储空间，并提高表的I/O访问效率。

应用场景：

        ① InnoDB表在创建的时候，若开启了innodb_file_per_table选项，该表将拥有属于自己的.idb文件。对这种InnoDB表做了大量的insert, update, delete操作后，可以使用optimize table命令重组表和索引，并归还磁盘空间以供操作系统使用；

        ② 对MyISAM和ARCHIVE表做了大量删除操作，或者对具有可变长度行的MyISAM和ARCHIVE表（即拥有VARCHAR, VARBINARY, BLOB或TEXT类型字段的表）做了大量修改后，删除掉的行被维护在一个链表中，后续insert操作可以重用旧行的位置，此时你可以使用optimize table命令回收未使用的空间并对数据文件进行碎片整理。在对表做了大量修改后，此命令可能会显著提高使用该表的语句的性能；

权限：要求对表具有select和insert权限

适用范围：存储引擎为InnoDB, MyISAM, ARCHIVE的表和分区表，不支持视图

日志记录：默认情况下，服务器将optimize table语句写入二进制日志，以便它们复制到slave。要禁止日志记录，请指定NO_WRITE_TO_BINLOG或LOCAL关键字

输出格式：

见下图

InnoDB说明：

对于InnoDB表，optimize table被映射为alter table，此操作会重建表以更新索引统计信息，并释放聚簇索引中未使用的空间。它无法使用快速索引创建。同时辅助索引也无法被高效地创建，因为辅助索引的键是按照它们在主键中出现的顺序插入的。

在考虑是否运行优化时，请考虑以下几点：

◇ 一定程度的碎片是可取的。InnoDB仅填充页面容积的93%（填充因子15/16），留下少量空间，从而使得更新操作无需分页；

◇ 删除操作会在页面内留下间隙，从而使得页面填充不完全，此时可以考虑运行优化；

◇ 当有足够的空间可用时，对行的更新通常只是重写同一页面中的数据，具体取决于数据类型和行格式；

◇ 随着时间的推移，高并发的工作负载可能会在索引中留下间隙，因为InnoDB使用MVCC机制保存相同数据的多个版本；

说明：

① optimize table对一个表所做的修改，取决于该表使用的存储引擎

② 请注意，在optimize table执行期间，MySQL会锁住整张表

9. flush privileges

功能：命令服务器重新加载权限表，从而使设置生效

说明：

① 服务器每次启动时，读取权限表的内容，到内存

② 如在服务器运行过程中，修改了权限，为使设置生效，需执行该命令

③ 否则服务器重启，设置方可生效

④ 只有直接修改权限表，如mysql.user的情况下，才需重新加载

⑤ 使用账号管理命令，如「create user」「grant」「revoke」「rename」「drop user」「set password」的情况下，无需重载权限表

⑥ 要执行「flush」命令需要有reload权限

8. 字符编码

萨达「」

7. 连接验证

连接验证，是MySQL根据你发起连接的client主机和你指定的用户名，与mysql.user表中的「Host」「User」「Password」三个字段，进行匹配的过程

7.1 验证过程

无论何时MySQL将user表中的内容读入内存，都会对其排序

当一个client尝试连接时，MySQL按序查找内存中已排序的记录行

匹配client主机名和用户名的第一个条目，即为结果

7.2 排序规则

字段的排序优先级为：「Host」>「User」，即先按「Host」排，「Host」相同的然后按照「User」排

同一字段，按照字段值识别度由高到低的顺序排列，即：

「Host」为主机名或者IP地址字面值的条目，排在最前；「%」代表任意主机，识别度最低，排在最后；「''」空字符串同样代表任意主机，但排在「%」之后；对于「Host」值相同的条目，「User」识别度最高的排在最前；「User」为空时，可以匹配任意用户，识别度最低，排在最后；对于「Host」和「User」识别度相同的两个条目，两者顺序不确定

示例

假设user表中数据如下图所示

经过排序后如下图所示

7.3 要点

◇ 如果一个client最终匹配的是，user表中「User」字段为空的条目，那么无论client实际指定的用户名为甚，该client均以匿名用户的身份登录

◇ 登录成功后，如果你发现当前账号权限与你期望的不同，那么有一种可能是你被作为其它账号而登录，此时，你可以使用CURRENT_USER()函数查看当前账号

7.4 问题

特定主机下的匿名账号，会妨碍其他账号对数据库的访问。例如：假设同时存在如下两个账号

""@"host""rongxj"@"%"

因为相比于「%」，「host」识别度更高，所以""@"host"应排在"rongxj"@"%"之前

当用户「rongxj」从「host」主机登录MySQL时，匹配的将是账号""@"host"，而非"rongxj"@"%"，从而引发账号权限问题

解决方法：删除所有匿名账号

6. user表

mysql库的user表，负责存储账号相关的所有信息，包括「主机」+「用户」+「密码」+「账号权限」，分别对应字段「host」「user」「password」「opt_priv」

6.1 描述

◇ MySQL中的账号，全部存储于mysql库的user表中，每一个账号都对应有一个条目，其中的用户名和主机名分开存储，分别对应字段「user」和「host」

◇「user」若为空（空字符串），则匹配所有用户名，例如，假设user表中的一个账号，它的「user」字段为空，则从特定主机「host」连接的所有用户，都可以匹配该条目

◇ 与「user」不同，「password」字段为空，并不意味着能够匹配任意密码，而是要求client连接时，一定不能指定密码，否则无法匹配

◇「host」既可以是真正的主机名，也可以是主机IP，它们都支持通配符「百分号%」和「下划线_」，用法和运算符「LIKE」中的用法相同，例如，若「host」为'%'，则可以匹配所有主机；若「host」为'%.mysql.com'，则可以匹配mysql.com域名下的所有主机；若「host」为'192.168.1.%'，则将匹配'192.168.1'网段下的所有主机

◇ 使用名字格式的主机名，具有不确定性，建议「host」字段使用「ip」

注意，对user表的任何修改，都要执行「FLUSH PRIVILEGES」命令，让服务器重新加载权限表，从而使设置生效

6.2 账号操作

通过user表，可以对账号进行操作

添加

insert into mysql.user(host, user, password) values("host", "user", password("some_pass"));

查看

select host, user, password from mysql.user;

更新

update mysql.user set「column」= "new_value" where「column_other」= "some_value";

删除

delete from mysql.user where「column」= "value"

6.3 权限操作

mysql.user表中存储的是全局权限，操作对象为「all_databases.all_tables」

授权

update mysql.user set「opt_priv」= "Y" where user = "some_value";

上述语句等同于：grant select on *.* to "test"@"%";

销权

update mysql.user set「opt_priv」= "Y" where user = "some_value";

上述语句等同于：revoke select on *.* from "test"@"%";

6.4 说明

✪ 操作账号和权限，我们首选的方法应该是账号管理语句，如「create user」「grant」，因为相比于直接操作权限表，账号管理语句更加简洁，针对性更强， 而且账号管理语句是通过服务器对底层权限表进行正确的修改，所以更不容易出错。因此，我们不鼓励直接操作权限表，如果通过这种方式对权限表的某行所做的修改导致行格式非法，服务器将忽略该行，请参考Section6.3.2 "Adding User Accounts"；

✪ 一个用户一旦拥有了对「mysql.user」表的「update | reload」或「insert | reload」的权限组合，则等同于拥有「root」权限。因为它可以通过「修改」或「插入」操作「mysql.user」表，然后执行「flush privileges」，以窃取任何想要的权限。因此，授权时应慎重，为安全起见，任何时候都应该只授予最小范围的最小权限；

5. 账号

◇ MySQL的账号由「用户名」和「主机名」两部分构成，所以相同的「用户名」、不同的「主机名」属于两个不同的账号

◇ 账号名的格式为：'user_name'@'host_name'，一个仅包含「用户名」的账号，实际上相当于'user_name'@'%'，即账号'me'等价于'me'@'%'

◇「用户名」和「主机名」可以不加引号，但是当

        ① 「用户名」中含有特殊字符，如「空格 」、「减号-」

        ② 「主机名」中含有特殊字符或通配符，如「点号.」、「百分号%」

时，必须加引号，可以使用的有「反引号`」、「单引号'」和「双引号"」

◇ MySQL在连接验证时，「用户名」大小写敏感，「主机名」大小写不敏感

5.1 账号管理

☆ 添加

命令：create user「Account」identified by「Password」

功能：创建账号「Account」，密码为「Password」

示例：创建新账号"test"@"%"，密码为"test"

说明：

① 若账号只包含用户名，则主机名默认为%

② 新创建的账号，需调用GRANT命令赋予权限，否则无任何权限

③ 该命令会在mysql.user表中插入一条无任何权限的新记录，对应于新创建的账号

④ identified by 指定的密码经过hash加密后，存入mysql.user表的Password字段

☆ 授权

命令：grant「Privs」on「Database.Table」to「Account」

功能：赋予账号「Account」对数据库「Database」的「Table」表的「Privs」权限

选项和参数：

all privileges：表示所有权限

usage："no privileges"的同义词，表示无任何权限，或不改变任何权限，即在保留当前权限不变的前提下，只修改特定权限

grant option：该权限允许你给予其他用户或从其他用户删除你所拥有的权限，授权方式：with grant option

示例：赋予账号"test"@"%"对所有数据库和所有表的全部权限，以及grant option权限

说明：

① 用户一旦具有对「mysql.user」表的「insert | reload」或「update | reload」的权限组合，则可以很容易窃取「root」权限，从而造成安全隐患。因此，任何时候，总是应该授予最小范围内的最小权限，即：

grant select, insert, update on「the_database」.* to "user"@"host";

杜绝使用「all privileges」选项

② 新赋予的权限对于已经登录的账号无效，此时你需要断开连接，然后重新登录该账号；

☆ 撤销权限

命令：

① revoke「Privs」on「Database.Table」from「Account」

② revoke all privileges, grant option from「Account」

功能：

① 撤销账号「Account」对数据库「Database」的「Table」表的「Privs」权限

② 撤销账号「Account」在所有级别下的全部权限，全局，数据库，表，列，例程

示例：

撤销账号的插入权限

撤销账号的全部权限

☆ 查看权限

命令：show grants for「Account」

功能：查看账号「Account」的权限

示例：查看"test"@"localhost"的权限

查看当前用户的权限

说明：

① 查看当前用户权限：show grants; show grants for current_user; show grants for current_user();

② all privileges代表所有权限，usage表示不具有任何权限

☆ 重命名

命令：rename user「Old_Account」to「New_Account」

功能：将账号「Old_Account」重命名为「New_Account」

示例：

☆ 密码

命令：

  set password = password("some_pass")

 set password for「Account」= password("some_pass")

功能：

  设置或修改当前连接的账号的密码

 设置或修改指定账号「Account」的密码

说明：

① 设置或修改当前账号的密码，不需要任何权限；
② 设置或修改其它账号的密码，需要具有对 「mysql」 库的 「update」权限；

③ 若全局系统变量「read_only」开启（即@@global.read_only = 1），执行set password命令需要额外具有「super」权限（该变量默认关闭）；

④ 若会话系统变量「old_passwords」开启（即@@session.old_passwords = 1），则使用pre-4.1密码哈希方法，哈希值16 Byte

若会话系统变量「old_passwords」关闭（即@session.old_passwords = 0），则使用4.1密码哈希方法，哈希值41 Byte，且以「*」打头

「old_passwords」变量默认关闭

示例：

修改当前用户的密码

☆ 删除

命令：drop user「Account」

功能：删除账号「Account」

说明：对于当前已打开的会话，不受影响；会话关闭后重连，或者使用该账号的新连接，登录失败

示例：

5.2 安全问题

账号操作过程中，如「create user」「grant」「set password」，可能在日志文件~/.mysql_history里面留下敏感信息，如密码明文，需要加以留意

4. mysqladmin

mysqladmin是管理工具，用于执行管理性操作

命令：mysqladmin -h [Host] -P [port] -u [UserName] -pPwd [Cmd]

使用：

每秒捕获一次SHOW GLOBAL STATUS    ：    mysqladmin ext -i1

3. mysql

mysql是命令行工具，用于执行sql语句

命令：mysql -h「Host」-P「port」-u「User」-pPwd  -Ns  -e "Cmd"「Database」

选项及参数：

-N：--skip-column-names，结果不输出列名

-s：--silent，silent模式，只产生少量输出，该模式将导致非表格输出模式以及特殊字符的转义

说明：如果没有指定「User」，默认为「root」

示例：

安全问题

该工具，会将用户在使用它的过程中执行过的所有语句，都记录到日志文件中，默认为~/.mysql_history，这将导致一些敏感信息，如密码明文，都存在于该文件里面，如下图所示

如果你不想维护该历史文件，你可以手动删除它，然后使用下列两种方式之一来防止它再次生成：

① 将环境变量「MYSQL_HISTFILE」设置为「/dev/null」，为了每次登陆时总生效，可以将该设置放入shell的启动文件

② 创建符号链接「$HOME/.mysql_history」，指向「/dev/null」

2. 查看配置文件

① 查看命令行选项--defaults-file

ps -ef | grep mysqld

② 查看默认配置文件

/path/mysqld --verbose --help 2>/dev/null | grep -A 1 "Default options" --color=always

mysql按上述顺序查找配置文件

1. 运行MySQL

命令：mysqld_safe --user=mysql &

MySQL的账号由「用户名」和「主机名」两部分构成，所以相同的「用户名」、不同的「主机名」属于两个不同的账号

账号名的格式为：'user_name'@'host_name'