文章目录
一，MySQL用户与权限管理
安装 MySQL 时会自动安装一个名为 mysql 的数据库，这个数据库下面存储的都是权限表，这些权限表会在数据库启动的时候就载入内存。作为一个多用户数据库系统，MySQL 会在验证用户身份后，根据这些权限表的内容为每个用户赋予相应的权限。
数据库的权限和数据库的安全是息息相关的，不当的权限设置可能会导致各种各样的安全隐患，操作系统的某些设置也会对 MySQL 的安全造成影响。
目标：
- 了解权限系统的工作原理
- 熟练掌握账号的管理和使用方法
（一）权限表
MySQL服务器通过权限表来控制用户对数据库的访问，这些权限表中最重要的是user表 db表。
mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| demo |
| example |
| information_schema |
| mysql |
| performance_schema |
| rksms |
| sys |
+--------------------+
7 rows in set (0.00 sec)
mysql> USE mysql; # mysql 数据库下面存储的都是权限表
Database changed
mysql> SHOW TABLES;
+------------------------------------------------------+
| Tables_in_mysql |
+------------------------------------------------------+
| columns_priv |
| component |
| db | #
| default_roles |
| engine_cost |
| func |
| general_log |
| global_grants |
| gtid_executed |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| innodb_index_stats |
| innodb_table_stats |
| password_history |
| plugin |
| procs_priv |
| proxies_priv |
| replication_asynchronous_connection_failover |
| replication_asynchronous_connection_failover_managed |
| role_edges |
| server_cost |
| servers |
| slave_master_info |
| slave_relay_log_info |
| slave_worker_info |
| slow_log |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user | # 重要
+------------------------------------------------------+
35 rows in set (1.65 sec)
- 在 MySQL 5.6.7中删除了 mysql.host 表。
1，user 表
user 表存储可以连接到服务器的用户基本信息。
先来看看 8.0.23 版本中的 user 表结构：
mysql> DESC user;
+--------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Host | char(255) | NO | PRI | | |
| User | char(32) | NO | PRI | | |
| Select_priv | enum('N','Y') | NO | | N | |
| Insert_priv | enum('N','Y') | NO | | N | |
| Update_priv | enum('N','Y') | NO | | N | |
| Delete_priv | enum('N','Y') | NO | | N | |
| Create_priv | enum('N','Y') | NO | | N | |
| Drop_priv | enum('N','Y') | NO | | N | |
| Reload_priv | enum('N','Y') | NO | | N | |
| Shutdown_priv | enum('N','Y') | NO | | N | |
| Process_priv | enum('N','Y') | NO | | N | |
| File_priv | enum('N','Y') | NO | | N | |
| Grant_priv | enum('N','Y') | NO | | N | |
| References_priv | enum('N','Y') | NO | | N | |
| Index_priv | enum('N','Y') | NO | | N | |
| Alter_priv | enum('N','Y') | NO | | N | |
| Show_db_priv | enum('N','Y') | NO | | N | |
| Super_priv | enum('N','Y') | NO | | N | |
| Create_tmp_table_priv | enum('N','Y') | NO | | N | |
| Lock_tables_priv | enum('N','Y') | NO | | N | |
| Execute_priv | enum('N','Y') | NO | | N | |
| Repl_slave_priv | enum('N','Y') | NO | | N | |
| Repl_client_priv | enum('N','Y') | NO | | N | |
| Create_view_priv | enum('N','Y') | NO | | N | |
| Show_view_priv | enum('N','Y') | NO | | N | |
| Create_routine_priv | enum('N','Y') | NO | | N | |
| Alter_routine_priv | enum('N','Y') | NO | | N | |
| Create_user_priv | enum('N','Y') | NO | | N | |
| Event_priv | enum('N','Y') | NO | | N | |
| Trigger_priv | enum('N','Y') | NO | | N | |
| Create_tablespace_priv | enum('N','Y') | NO | | N | |
| ssl_type | enum('','ANY','X509','SPECIFIED') | NO | | | |
| ssl_cipher | blob | NO | | NULL | |
| x509_issuer | blob | NO | | NULL | |
| x509_subject | blob | NO | | NULL | |
| max_questions | int unsigned | NO | | 0 | |
| max_updates | int unsigned | NO | | 0 | |
| max_connections | int unsigned | NO | | 0 | |
| max_user_connections | int unsigned | NO | | 0 | |
| plugin | char(64) | NO | | caching_sha2_password | |
| authentication_string | text | YES | | NULL | |
| password_expired | enum('N','Y') | NO | | N | |
| password_last_changed | timestamp | YES | | NULL | |
| password_lifetime | smallint unsigned | YES | | NULL | |
| account_locked | enum('N','Y') | NO | | N | |
| Create_role_priv | enum('N','Y') | NO | | N | |
| Drop_role_priv | enum('N','Y') | NO | | N | |
| Password_reuse_history | smallint unsigned | YES | | NULL | |
| Password_reuse_time | smallint unsigned | YES | | NULL | |
| Password_require_current | enum('N','Y') | YES | | NULL | |
| User_attributes | json | YES | | NULL | |
+--------------------------+-----------------------------------+------+-----+-----------------------+-------+
51 rows in set (0.00 sec)
这张表总共有 51 个字段：
# 查询表有多少字段
mysql> SELECT COUNT(*)
-> FROM information_schema.COLUMNS
-> WHERE TABLE_SCHEMA='mysql' and table_name='user';
+----------+
| COUNT(*) |
+----------+
| 51 |
+----------+
1 row in set (0.00 sec)
这些字段按照实际功能的不同可分为4种。
（1）用户列
user 表中的用户列包括
- Host：
- User：
- Password_reuse_history:
- Password_reuse_time
- Password_require_current
（2）权限列
user表的权限列是一系列以 priv 结尾的字段：
（3）安全列
user 表的安全列只有 4 个字段， 分别是 ssl_type、ssl_cipher、x509_issuer 和 x509_subject。
（4）资源控制列
user 表的 4 个资源控制列包括：
- max_questions
- max_updates
- max_connections
- max_user_connections
看看具体有哪些用户：
mysql> SELECT * FROM mysql.user;
+-----------+------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------------------+--------------------------+----------------------------+---------------+-------------+-----------------+----------------------+-----------------------+------------------------------------------------------------------------+------------------+-----------------------+-------------------+----------------+------------------+----------------+------------------------+---------------------+--------------------------+-----------------+
| Host | User | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv | Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv | Repl_slave_priv | Repl_client_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Create_user_priv | Event_priv | Trigger_priv | Create_tablespace_priv | ssl_type | ssl_cipher | x509_issuer | x509_subject | max_questions | max_updates | max_connections | max_user_connections | plugin | authentication_string | password_expired | password_last_changed | password_lifetime | account_locked | Create_role_priv | Drop_role_priv | Password_reuse_history | Password_reuse_time | Password_require_current | User_attributes |
+-----------+------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------------------+--------------------------+----------------------------+---------------+-------------+-----------------+----------------------+-----------------------+------------------------------------------------------------------------+------------------+-----------------------+-------------------+----------------+------------------+----------------+------------------------+---------------------+--------------------------+-----------------+
| localhost | mysql.infoschema | Y | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | | NULL | NULL | NULL | 0 | 0 | 0 | 0 | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | N | 2021-03-21 13:22:27 | NULL | Y | N | N | NULL | NULL | NULL | NULL |
| localhost | mysql.session | N | N | N | N | N | N | N | Y | N | N | N | N | N | N | N | Y | N | N | N | N | N | N | N | N | N | N | N | N | N | | NULL | NULL | NULL | 0 | 0 | 0 | 0 | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | N | 2021-03-21 13:22:26 | NULL | Y | N | N | NULL | NULL | NULL | NULL |
| localhost | mysql.sys | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | | NULL | NULL | NULL | 0 | 0 | 0 | 0 | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | N | 2021-03-21 13:22:27 | NULL | Y | N | N | NULL | NULL | NULL | NULL |
| localhost | root | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | | NULL | NULL | NULL | 0 | 0 | 0 | 0 | caching_sha2_password | $A$005$+d_ Pp7#{P0Y<>UintbPyy.HD4/0A0rIpRnrzqnswBL61aM/u/Dbtx1NC | N | 2021-03-21 13:25:59 | NULL | N | Y | Y | NULL | NULL | NULL | NULL |
+-----------+------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------------------+--------------------------+----------------------------+---------------+-------------+-----------------+----------------------+-----------------------+------------------------------------------------------------------------+------------------+-----------------------+-------------------+----------------+------------------+----------------+------------------------+---------------------+--------------------------+-----------------+
5 rows in set (0.00 sec)
2，db 表
db 表中存储了某个用户对一个数据库的权限。
先来看看 8.0.23 版本中的 db 表结构：
mysql> DESC db;
+-----------------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------------+---------------+------+-----+---------+-------+
| Host | char(255) | NO | PRI | | |
| Db | char(64) | NO | PRI | | |
| User | char(32) | NO | PRI | | |
| Select_priv | enum('N','Y') | NO | | N | |
| Insert_priv | enum('N','Y') | NO | | N | |
| Update_priv | enum('N','Y') | NO | | N | |
| Delete_priv | enum('N','Y') | NO | | N | |
| Create_priv | enum('N','Y') | NO | | N | |
| Drop_priv | enum('N','Y') | NO | | N | |
| Grant_priv | enum('N','Y') | NO | | N | |
| References_priv | enum('N','Y') | NO | | N | |
| Index_priv | enum('N','Y') | NO | | N | |
| Alter_priv | enum('N','Y') | NO | | N | |
| Create_tmp_table_priv | enum('N','Y') | NO | | N | |
| Lock_tables_priv | enum('N','Y') | NO | | N | |
| Create_view_priv | enum('N','Y') | NO | | N | |
| Show_view_priv | enum('N','Y') | NO | | N | |
| Create_routine_priv | enum('N','Y') | NO | | N | |
| Alter_routine_priv | enum('N','Y') | NO | | N | |
| Execute_priv | enum('N','Y') | NO | | N | |
| Event_priv | enum('N','Y') | NO | | N | |
| Trigger_priv | enum('N','Y') | NO | | N | |
+-----------------------+---------------+------+-----+---------+-------+
22 rows in set (1.59 sec)
这张表总共有 22 个字段：
mysql> SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA='mysql' and table_name='db';
+----------+
| COUNT(*) |
+----------+
| 22 |
+----------+
1 row in set (0.00 sec)
3，tables_priv 表
mysql> DESC tables_priv;
+-------------+-----------------------------------------------------------------------------------------------------------------------------------+------+-----+-------------------+-----------------------------------------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-----------------------------------------------------------------------------------------------------------------------------------+------+-----+-------------------+-----------------------------------------------+
| Host | char(255) | NO | PRI | | |
| Db | char(64) | NO | PRI | | |
| User | char(32) | NO | PRI | | |
| Table_name | char(64) | NO | PRI | | |
| Grantor | varchar(288) | NO | MUL | | |
| Timestamp | timestamp | NO | | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP |
| Table_priv | set('Select','Insert','Update','Delete','Create','Drop','Grant','References','Index','Alter','Create View','Show view','Trigger') | NO | | | |
| Column_priv | set('Select','Insert','Update','References') | NO | | | |
+-------------+-----------------------------------------------------------------------------------------------------------------------------------+------+-----+-------------------+-----------------------------------------------+
8 rows in set (0.00 sec)
4，columns_priv 表
mysql> DESC columns_priv;
+-------------+----------------------------------------------+------+-----+-------------------+-----------------------------------------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+----------------------------------------------+------+-----+-------------------+-----------------------------------------------+
| Host | char(255) | NO | PRI | | |
| Db | char(64) | NO | PRI | | |
| User | char(32) | NO | PRI | | |
| Table_name | char(64) | NO | PRI | | |
| Column_name | char(64) | NO | PRI | | |
| Timestamp | timestamp | NO | | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP |
| Column_priv | set('Select','Insert','Update','References') | NO | | | |
+-------------+----------------------------------------------+------+-----+-------------------+-----------------------------------------------+
7 rows in set (0.00 sec)
5，procs_priv 表
mysql> DESC procs_priv;
+--------------+----------------------------------------+------+-----+-------------------+-----------------------------------------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+----------------------------------------+------+-----+-------------------+-----------------------------------------------+
| Host | char(255) | NO | PRI | | |
| Db | char(64) | NO | PRI | | |
| User | char(32) | NO | PRI | | |
| Routine_name | char(64) | NO | PRI | | |
| Routine_type | enum('FUNCTION','PROCEDURE') | NO | PRI | NULL | |
| Grantor | varchar(288) | NO | MUL | | |
| Proc_priv | set('Execute','Alter Routine','Grant') | NO | | | |
| Timestamp | timestamp | NO | | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP |
+--------------+----------------------------------------+------+-----+-------------------+-----------------------------------------------+
8 rows in set (0.00 sec)
（二）账户管理
1，登录和退出 MySQL 服务器
登录 MySQL 服务器
mysql -h hostname|hostlP -P port -u username -p DatabaseName -e "SQL 语句"
-h参数后面接主机名或者主机 IP。 hostname为主机名， hostIP为主机IP；-P参数后面接MySQL服务的端口。port为连接的端口号，默认端口是3306。-u参数后面接用户名。 username为用户名。-p参数会提示输入密码。DatabaseName参数指明登录到哪一个数据库中。默认会直接登录到 MySQL 数据库中， 然后可以使用USE命令来选择数据库。-e参数后面的 SQL语句会在登陆后立即执行，然后退出MySQL服务器。
举个例子🌰：指定用户通过输入密码的方式登录 MySQL：
# 先启动 MySQL 服务：net start mysql
C:\WINDOWS\system32>mysql -u root -p
Enter password: ****
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 16
Server version: 8.0.23 MySQL Community Server - GPL
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
- 紧接着在
-p参数后指定密码，可免提示输入密码。
举个例子🌰：用户登录指定数据库并执行指定操作后退出登录。
C:\WINDOWS\system32>mysql -h localhost -u root -p mysql -e "DESC func;"
Enter password: ****
+-------+------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------------------+------+-----+---------+-------+
| name | char(64) | NO | PRI | | |
| ret | tinyint | NO | | 0 | |
| dl | char(128) | NO | | | |
| type | enum('function','aggregate') | NO | | NULL | |
+-------+------------------------------+------+-----+---------+-------+
C:\WINDOWS\system32>
- 使用
EXIT或QUIT（缩写为\q）即可退出登录。
2，新建普通用户
必须有全局的 CREATE USER 权限，或者 mysql 数据库的 INSERT 权限。当启用只读系统变量时，CREATE USER 额外需要CONNECTION ADMIN 特权。
（1）用 CREATE USER 语句来新建普通用户
CREATE USER 语句详情：
mysql> help CREATE USER;
Name: 'CREATE USER'
Description:
Syntax:
CREATE USER [IF NOT EXISTS]
user [auth_option] [, user [auth_option]] ...
DEFAULT ROLE role [, role ] ...
[REQUIRE {NONE | tls_option [[AND] tls_option] ...}]
[WITH resource_option [resource_option] ...]
[password_option | lock_option] ...
[COMMENT 'comment_string' | ATTRIBUTE 'json_object']
user:
(see )
auth_option: {
IDENTIFIED BY 'auth_string'
| IDENTIFIED BY RANDOM PASSWORD
| IDENTIFIED WITH auth_plugin
| IDENTIFIED WITH auth_plugin BY 'auth_string'
| IDENTIFIED WITH auth_plugin BY RANDOM PASSWORD
| IDENTIFIED WITH auth_plugin AS 'auth_string'
}
tls_option: {
SSL
| X509
| CIPHER 'cipher'
| ISSUER 'issuer'
| SUBJECT 'subject'
}
resource_option: {
MAX_QUERIES_PER_HOUR count
| MAX_UPDATES_PER_HOUR count
| MAX_CONNECTIONS_PER_HOUR count
| MAX_USER_CONNECTIONS count
}
password_option: {
PASSWORD EXPIRE [DEFAULT | NEVER | INTERVAL N DAY]
| PASSWORD HISTORY {DEFAULT | N}
| PASSWORD REUSE INTERVAL {DEFAULT | N DAY}
| PASSWORD REQUIRE CURRENT [DEFAULT | OPTIONAL]
| FAILED_LOGIN_ATTEMPTS N
| PASSWORD_LOCK_TIME {N | UNBOUNDED}
}
lock_option: {
ACCOUNT LOCK
| ACCOUNT UNLOCK
}
The CREATE USER statement creates new MySQL accounts. It enables
authentication, role, SSL/TLS, resource-limit, and password-management
properties to be established for new accounts. It also controls whether
accounts are initially locked or unlocked.
To use CREATE USER, you must have the global CREATE USER privilege, or
the INSERT privilege for the mysql system schema. When the read_only
system variable is enabled, CREATE USER additionally requires the
CONNECTION_ADMIN privilege (or the deprecated SUPER privilege).
As of MySQL 8.0.22, CREATE USER fails with an error if any account to
be created is named as the DEFINER attribute for any stored object.
(That is, the statement fails if creating an account would cause the
account to adopt a currently orphaned stored object.) To perform the
operation anyway, you must have the SET_USER_ID privilege; in this
case, the statement succeeds with a warning rather than failing with an
error. Without SET_USER_ID, to perform the user-creation operation,
drop the orphan objects, create the account and grant its privileges,
and then re-create the dropped objects. For additional information,
including how to identify which objects name a given account as the
DEFINER attribute, see
https://dev.mysql.com/doc/refman/8.0/en/stored-objects-security.html#st
ored-objects-security-orphan-objects.
CREATE USER either succeeds for all named users or rolls back and has
no effect if any error occurs. By default, an error occurs if you try
to create a user that already exists. If the IF NOT EXISTS clause is
given, the statement produces a warning for each named user that
already exists, rather than an error.
URL: https://dev.mysql.com/doc/refman/8.0/en/create-user.html
举个例子🌰：创建用户并指定密码。
mysql> CREATE USER 'test1'@'localhost' IDENTIFIED BY 'pwtestl';
Query OK, 0 rows affected (1.79 sec)
- 注意用户的格式
'user_name'@'host'。 - 这里直接用
IDENTIFIED BY 'auth_string'指定密码。
（2）用 INSERT 语句来新建普通用户
INSERT 语句可以向 user 表插入数据的方式创建普通用户，但默认情况下是不允许这样做的。
非要这样做，需要先修改配置文件 my.conf：
将:
sql-mode=STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
改为:
sql-mode=NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
再重启数据库服务：
service mysql restart
举个例子🌰：用 INSERT 语句来新建普通用户
mysql> insert into user (host,user,authentication_string,select_priv,insert_priv,update_priv,ssl_cipher,x509_issuer,x509_subject) values(‘localhost’,‘guest’,MD5(‘guest123’),‘Y’,‘Y’,‘Y’,‘0x’,‘0x’,‘0x’);
mysql8.0 以前的版本可以使用 GRANT 在授权的时候隐式的创建用户，8.0 以后已经不支持，会报 You are not allowed to create a user with GRANT 错误，所以必须先创建用户，然后再授权。
举个例子🌰：先用 CREATE USER 创建用户，再用 GRANT 语句来设置用户权限
mysql> CREATE USER 'root'@'%' IDENTIFIED BY 'Hadoop3!';
Query OK, 0 rows affected (0.04 sec)
mysql> grant all privileges on *.* to 'root'@'%';
Query OK, 0 rows affected (0.03 sec)
3，删除普通用户
（1）用 DROP USER 语句删除普通用户
必须拥有 DROP USER 权限。
mysql> help DROP USER;
Name: 'DROP USER'
Description:
Syntax:
DROP USER [IF EXISTS] user [, user] ...
The DROP USER statement removes one or more MySQL accounts and their
privileges. It removes privilege rows for the account from all grant
tables.
Roles named in the mandatory_roles system variable value cannot be
dropped.
To use DROP USER, you must have the global CREATE USER privilege, or
the DELETE privilege for the mysql system schema. When the read_only
system variable is enabled, DROP USER additionally requires the
CONNECTION_ADMIN privilege (or the deprecated SUPER privilege).
As of MySQL 8.0.22, DROP USER fails with an error if any account to be
dropped is named as the DEFINER attribute for any stored object. (That
is, the statement fails if dropping an account would cause a stored
object to become orphaned.) To perform the operation anyway, you must
have the SET_USER_ID privilege; in this case, the statement succeeds
with a warning rather than failing with an error. For additional
information, including how to identify which objects name a given
account as the DEFINER attribute, see
https://dev.mysql.com/doc/refman/8.0/en/stored-objects-security.html#st
ored-objects-security-orphan-objects.
DROP USER either succeeds for all named users or rolls back and has no
effect if any error occurs. By default, an error occurs if you try to
drop a user that does not exist. If the IF EXISTS clause is given, the
statement produces a warning for each named user that does not exist,
rather than an error.
The statement is written to the binary log if it succeeds, but not if
it fails; in that case, rollback occurs and no changes are made. A
statement written to the binary log includes all named users. If the IF
EXISTS clause is given, this includes even users that do not exist and
were not dropped.
Each account name uses the format described in
https://dev.mysql.com/doc/refman/8.0/en/account-names.html. For
example:
DROP USER 'jeffrey'@'localhost';
The host name part of the account name, if omitted, defaults to '%'.
URL: https://dev.mysql.com/doc/refman/8.0/en/drop-user.html
举个例子🌰：用 DROP USER 语句删除普通用户
mysql> DROP USER 'test1'@'localhost';
Query OK, 0 rows affected (1.80 sec)
mysql> SELECT * FROM user;
+-----------+------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------------------+--------------------------+----------------------------+---------------+-------------+-----------------+----------------------+-----------------------+------------------------------------------------------------------------+------------------+-----------------------+-------------------+----------------+------------------+----------------+------------------------+---------------------+--------------------------+-----------------+
| Host | User | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv | Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv | Repl_slave_priv | Repl_client_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Create_user_priv | Event_priv | Trigger_priv | Create_tablespace_priv | ssl_type | ssl_cipher | x509_issuer | x509_subject | max_questions | max_updates | max_connections | max_user_connections | plugin | authentication_string | password_expired | password_last_changed | password_lifetime | account_locked | Create_role_priv | Drop_role_priv | Password_reuse_history | Password_reuse_time | Password_require_current | User_attributes |
+-----------+------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------------------+--------------------------+----------------------------+---------------+-------------+-----------------+----------------------+-----------------------+------------------------------------------------------------------------+------------------+-----------------------+-------------------+----------------+------------------+----------------+------------------------+---------------------+--------------------------+-----------------+
| localhost | mysql.infoschema | Y | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | | NULL | NULL | NULL | 0 | 0 | 0 | 0 | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | N | 2021-03-21 13:22:27 | NULL | Y | N | N | NULL | NULL | NULL | NULL |
| localhost | mysql.session | N | N | N | N | N | N | N | Y | N | N | N | N | N | N | N | Y | N | N | N | N | N | N | N | N | N | N | N | N | N | | NULL | NULL | NULL | 0 | 0 | 0 | 0 | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | N | 2021-03-21 13:22:26 | NULL | Y | N | N | NULL | NULL | NULL | NULL |
| localhost | mysql.sys | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | | NULL | NULL | NULL | 0 | 0 | 0 | 0 | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | N | 2021-03-21 13:22:27 | NULL | Y | N | N | NULL | NULL | NULL | NULL |
| localhost | root | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | | NULL | NULL | NULL | 0 | 0 | 0 | 0 | caching_sha2_password | $A$005$+d_ Pp7#{P0Y<>UintbPyy.HD4/0A0rIpRnrzqnswBL61aM/u/Dbtx1NC | N | 2021-03-21 13:25:59 | NULL | N | Y | Y | NULL | NULL | NULL | NULL |
+-----------+------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------------------+--------------------------+----------------------------+---------------+-------------+-----------------+----------------------+-----------------------+------------------------------------------------------------------------+------------------+-----------------------+-------------------+----------------+------------------+----------------+------------------------+---------------------+--------------------------+-----------------+
4 rows in set (0.00 sec)
（2）用 DELETE 语句删除普通用户
使用 DELETE 语句直接将用户的信息从 mysql.user 表中删除。 但必须拥有对 ysql.user 表的 DELETE 权限。
官方也不推荐用这种方法。
4，root用户修改用户密码
MySQL 8.0 开始不支持使用 mysqladmin 命令、 SET 语句、GRANT 语句来修改密码！应使用 ALTER 语句。
举个例子🌰：使用 ALTER 语句修改用户密码
mysql> ALTER USER 'root'@'localhost' identified by "rootroot";
Query OK, 0 rows affected (1.81 sec)
（三）用户权限管理
在创建用户之后可以进行权限管理操作，包括授权、查看权限和收回权限等。
1，授予用户权限
授予的权限可以分为多个层级：
- 全局层级：适用于一个给定服务器中的所有数据库
- 数据库层级：适用于一个给定数据库中的所有表，权限存储在 mysql.db 表。
- 表层级：适用于一个给定表中的所有列，权限存储在 mysql.tables_priv 表。
- 列层级：适用于一个给定表中的单一列，权限存储在 mysq1.columns_priv 表
- 程序层级：适用于己存储的子程序，权限存储在 mysql.procs_priv 表。
在 MySQL 中可以使用 GRANT 语句或 REVOKE 语句为用户授予权限。
GRANT 语句详情：
mysql> help GRANT;
Name: 'GRANT'
Description:
Syntax:
GRANT
priv_type [(column_list)]
[, priv_type [(column_list)]] ...
ON [object_type] priv_level
TO user_or_role [, user_or_role] ...
[WITH GRANT OPTION]
[AS user
[WITH ROLE
DEFAULT
| NONE
| ALL
| ALL EXCEPT role [, role ] ...
| role [, role ] ...
]
]
}
GRANT PROXY ON user_or_role
TO user_or_role [, user_or_role] ...
[WITH GRANT OPTION]
GRANT role [, role] ...
TO user_or_role [, user_or_role] ...
[WITH ADMIN OPTION]
object_type: {
TABLE
| FUNCTION
| PROCEDURE
}
priv_level: {
*
| *.*
| db_name.*
| db_name.tbl_name
| tbl_name
| db_name.routine_name
}
user_or_role: {
user (see https://dev.mysql.com/doc/refman/8.0/en/account-names.html)
| role (see https://dev.mysql.com/doc/refman/8.0/en/role-names.html)
}
The GRANT statement enables system administrators to grant privileges
and roles, which can be granted to user accounts and roles. These
syntax restrictions apply:
o GRANT cannot mix granting both privileges and roles in the same
statement. A given GRANT statement must grant either privileges or
roles.
o The ON clause distinguishes whether the statement grants privileges
or roles:
o With ON, the statement grants privileges.
o Without ON, the statement grants roles.
o It is permitted to assign both privileges and roles to an account,
but you must use separate GRANT statements, each with syntax
appropriate to what is to be granted.
For more information about roles, see
https://dev.mysql.com/doc/refman/8.0/en/roles.html.
To grant a privilege with GRANT, you must have the GRANT OPTION
privilege, and you must have the privileges that you are granting.
(Alternatively, if you have the UPDATE privilege for the grant tables
in the mysql system schema, you can grant any account any privilege.)
When the read_only system variable is enabled, GRANT additionally
requires the CONNECTION_ADMIN privilege (or the deprecated SUPER
privilege).
GRANT either succeeds for all named users and roles or rolls back and
has no effect if any error occurs. The statement is written to the
binary log only if it succeeds for all named users and roles.
The REVOKE statement is related to GRANT and enables administrators to
remove account privileges. See [HELP REVOKE].
Each account name uses the format described in
https://dev.mysql.com/doc/refman/8.0/en/account-names.html. Each role
name uses the format described in
https://dev.mysql.com/doc/refman/8.0/en/role-names.html. For example:
GRANT ALL ON db1.* TO 'jeffrey'@'localhost';
GRANT 'role1', 'role2' TO 'user1'@'localhost', 'user2'@'localhost';
GRANT SELECT ON world.* TO 'role3';
The host name part of the account or role name, if omitted, defaults to
'%'.
Normally, a database administrator first uses CREATE USER to create an
account and define its nonprivilege characteristics such as its
password, whether it uses secure connections, and limits on access to
server resources, then uses GRANT to define its privileges. ALTER USER
may be used to change the nonprivilege characteristics of existing
accounts. For example:
CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'password';
GRANT ALL ON db1.* TO 'jeffrey'@'localhost';
GRANT SELECT ON db2.invoice TO 'jeffrey'@'localhost';
ALTER USER 'jeffrey'@'localhost' WITH MAX_QUERIES_PER_HOUR 90;
From the mysql program, GRANT responds with Query OK, 0 rows affected
when executed successfully. To determine what privileges result from
the operation, use SHOW GRANTS. See [HELP SHOW GRANTS].
URL: https://dev.mysql.com/doc/refman/8.0/en/grant.html
举个例子🌰：创建用户，并赋予权限：
mysql> CREATE USER 'test1'@'localhost' IDENTIFIED BY 'pwtestl';
Query OK, 0 rows affected (0.47 sec)
mysql> GRANT ALL ON *.* TO 'test1'@'localhost';
Query OK, 0 rows affected (0.07 sec)
2，查看用户权限
使用 SHOW GRANTS 语旬可以查看指定用户的权限信息：
mysql> help SHOW GRANTS;
Name: 'SHOW GRANTS'
Description:
Syntax:
SHOW GRANTS
[FOR user_or_role
[USING role [, role] ...]]
user_or_role: {
user (see )
| role (see .
}
This statement displays the privileges and roles that are assigned to a
MySQL user account or role, in the form of GRANT statements that must
be executed to duplicate the privilege and role assignments.
*Note*:
To display nonprivilege information for MySQL accounts, use the SHOW
CREATE USER statement. See [HELP SHOW CREATE USER].
SHOW GRANTS requires the SELECT privilege for the mysql system schema,
except to display privileges and roles for the current user.
To name the account or role for SHOW GRANTS, use the same format as for
the GRANT statement (for example, 'jeffrey'@'localhost'):
mysql> SHOW GRANTS FOR 'jeffrey'@'localhost';
+------------------------------------------------------------------+
| Grants for jeffrey@localhost |
+------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `jeffrey`@`localhost` |
| GRANT SELECT, INSERT, UPDATE ON `db1`.* TO `jeffrey`@`localhost` |
+------------------------------------------------------------------+
The host part, if omitted, defaults to '%'. For additional information
about specifying account and role names, see
https://dev.mysql.com/doc/refman/8.0/en/account-names.html, and
https://dev.mysql.com/doc/refman/8.0/en/role-names.html.
To display the privileges granted to the current user (the account you
are using to connect to the server), you can use any of the following
statements:
SHOW GRANTS;
SHOW GRANTS FOR CURRENT_USER;
SHOW GRANTS FOR CURRENT_USER();
If SHOW GRANTS FOR CURRENT_USER (or any equivalent syntax) is used in
definer context, such as within a stored procedure that executes with
definer rather than invoker privileges, the grants displayed are those
of the definer and not the invoker.
In MySQL 8.0 compared to previous series, SHOW GRANTS no longer
displays ALL PRIVILEGES in its global-privileges output because the
meaning of ALL PRIVILEGES at the global level varies depending on which
dynamic privileges are defined. Instead, SHOW GRANTS explictly lists
each granted global privilege:
mysql> SHOW GRANTS FOR 'root'@'localhost';
+---------------------------------------------------------------------+
| Grants for root@localhost |
+---------------------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, |
| SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, |
| SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION |
| SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, |
| ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, |
| CREATE ROLE, DROP ROLE ON *.* TO `root`@`localhost` WITH GRANT |
| OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION |
+---------------------------------------------------------------------+
Applications that process SHOW GRANTS output should be adjusted
accordingly.
At the global level, GRANT OPTION applies to all granted static global
privileges if granted for any of them, but applies individually to
granted dynamic privileges. SHOW GRANTS displays global privileges this
way:
o One line listing all granted static privileges, if there are any,
including WITH GRANT OPTION if appropriate.
o One line listing all granted dynamic privileges for which GRANT
OPTION is granted, if there are any, including WITH GRANT OPTION.
o One line listing all granted dynamic privileges for which GRANT
OPTION is not granted, if there are any, without WITH GRANT OPTION.
With the optional USING clause, SHOW GRANTS enables you to examine the
privileges associated with roles for the user. Each role named in the
USING clause must be granted to the user.
Suppose that user u1 is assigned roles r1 and r2, as follows:
CREATE ROLE 'r1', 'r2';
GRANT SELECT ON db1.* TO 'r1';
GRANT INSERT, UPDATE, DELETE ON db1.* TO 'r2';
CREATE USER 'u1'@'localhost' IDENTIFIED BY 'u1pass';
GRANT 'r1', 'r2' TO 'u1'@'localhost';
SHOW GRANTS without USING shows the granted roles:
mysql> SHOW GRANTS FOR 'u1'@'localhost';
+---------------------------------------------+
| Grants for u1@localhost |
+---------------------------------------------+
| GRANT USAGE ON *.* TO `u1`@`localhost` |
| GRANT `r1`@`%`,`r2`@`%` TO `u1`@`localhost` |
+---------------------------------------------+
Adding a USING clause causes the statement to also display the
privileges associated with each role named in the clause:
mysql> SHOW GRANTS FOR 'u1'@'localhost' USING 'r1';
+---------------------------------------------+
| Grants for u1@localhost |
+---------------------------------------------+
| GRANT USAGE ON *.* TO `u1`@`localhost` |
| GRANT SELECT ON `db1`.* TO `u1`@`localhost` |
| GRANT `r1`@`%`,`r2`@`%` TO `u1`@`localhost` |
+---------------------------------------------+
mysql> SHOW GRANTS FOR 'u1'@'localhost' USING 'r2';
+-------------------------------------------------------------+
| Grants for u1@localhost |
+-------------------------------------------------------------+
| GRANT USAGE ON *.* TO `u1`@`localhost` |
| GRANT INSERT, UPDATE, DELETE ON `db1`.* TO `u1`@`localhost` |
| GRANT `r1`@`%`,`r2`@`%` TO `u1`@`localhost` |
+-------------------------------------------------------------+
mysql> SHOW GRANTS FOR 'u1'@'localhost' USING 'r1', 'r2';
+---------------------------------------------------------------------+
| Grants for u1@localhost |
+---------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `u1`@`localhost` |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `db1`.* TO `u1`@`localhost` |
| GRANT `r1`@`%`,`r2`@`%` TO `u1`@`localhost` |
+---------------------------------------------------------------------+
*Note*:
A privilege granted to an account is always in effect, but a role is
not. The active roles for an account can differ across and within
sessions, depending on the value of the activate_all_roles_on_login
system variable, the account default roles, and whether SET ROLE has
been executed within a session.
MySQL 8.0.16 and higher supports partial revokes of global privileges,
such that a global privilege can be restricted from applying to
particular schemas (see
https://dev.mysql.com/doc/refman/8.0/en/partial-revokes.html). To
indicate which global schema privileges have been revoked for
particular schemas, SHOW GRANTS output includes REVOKE statements:
mysql> SET PERSIST partial_revokes = ON;
mysql> CREATE USER u1;
mysql> GRANT SELECT, INSERT, DELETE ON *.* TO u1;
mysql> REVOKE SELECT, INSERT ON mysql.* FROM u1;
mysql> REVOKE DELETE ON world.* FROM u1;
mysql> SHOW GRANTS FOR u1;
+--------------------------------------------------+
| Grants for u1@% |
+--------------------------------------------------+
| GRANT SELECT, INSERT, DELETE ON *.* TO `u1`@`%` |
| REVOKE SELECT, INSERT ON `mysql`.* FROM `u1`@`%` |
| REVOKE DELETE ON `world`.* FROM `u1`@`%` |
+--------------------------------------------------+
SHOW GRANTS does not display privileges that are available to the named
account but are granted to a different account. For example, if an
anonymous account exists, the named account might be able to use its
privileges, but SHOW GRANTS does not display them.
URL: https://dev.mysql.com/doc/refman/8.0/en/show-grants.html
举个例子🌰：使用 SHOW GRANTS 语旬查看用户的权限信息
mysql> SHOW GRANTS FOR 'test1'@'localhost';
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for test1@localhost |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO `test1`@`localhost` |
| GRANT APPLICATION_PASSWORD_ADMIN,AUDIT_ADMIN,BACKUP_ADMIN,BINLOG_ADMIN,BINLOG_ENCRYPTION_ADMIN,CLONE_ADMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,FLUSH_OPTIMIZER_COSTS,FLUSH_STATUS,FLUSH_TABLES,FLUSH_USER_RESOURCES,GROUP_REPLICATION_ADMIN,INNODB_REDO_LOG_ARCHIVE,INNODB_REDO_LOG_ENABLE,PERSIST_RO_VARIABLES_ADMIN,REPLICATION_APPLIER,REPLICATION_SLAVE_ADMIN,RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,ROLE_ADMIN,SERVICE_CONNECTION_ADMIN,SESSION_VARIABLES_ADMIN,SET_USER_ID,SHOW_ROUTINE,SYSTEM_USER,SYSTEM_VARIABLES_ADMIN,TABLE_ENCRYPTION_ADMIN,XA_RECOVER_ADMIN ON *.* TO `test1`@`localhost` |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
也能通过查看 user 表中的用户信息获得用户权限信息：
mysql> SELECT * FROM mysql.user WHERE user='test1';
+-----------+-------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------------------+--------------------------+----------------------------+---------------+-------------+-----------------+----------------------+-----------------------+------------------------------------------------------------------------+------------------+-----------------------+-------------------+----------------+------------------+----------------+------------------------+---------------------+--------------------------+-----------------+
| Host | User | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv | Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv | Repl_slave_priv | Repl_client_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Create_user_priv | Event_priv | Trigger_priv | Create_tablespace_priv | ssl_type | ssl_cipher | x509_issuer | x509_subject | max_questions | max_updates | max_connections | max_user_connections | plugin | authentication_string | password_expired | password_last_changed | password_lifetime | account_locked | Create_role_priv | Drop_role_priv | Password_reuse_history | Password_reuse_time | Password_require_current | User_attributes |
+-----------+-------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------------------+--------------------------+----------------------------+---------------+-------------+-----------------+----------------------+-----------------------+------------------------------------------------------------------------+------------------+-----------------------+-------------------+----------------+------------------+----------------+------------------------+---------------------+--------------------------+-----------------+
| localhost | test1 | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | N | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | | NULL | NULL | NULL | 0 | 0 | 0 | 0 | caching_sha2_password | $A$005$O6xWn
ySVk:Eyu84Pgf4pizsW2E61uyWC/mv5.oRY903YXCKgZ.3UK9 | N | 2022-03-31 19:17:10 | NULL | N | Y | Y | NULL | NULL | NULL | NULL |
+-----------+-------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------------------+--------------------------+----------------------------+---------------+-------------+-----------------+----------------------+-----------------------+------------------------------------------------------------------------+------------------+-----------------------+-------------------+----------------+------------------+----------------+------------------------+---------------------+--------------------------+-----------------+
1 row in set (0.00 sec)
3，撤销用户权限
在 MySQL 中使用 REVOKE 吾旬撤销用户权限:
mysql> help REVOKE;
Name: 'REVOKE'
Description:
Syntax:
REVOKE
priv_type [(column_list)]
[, priv_type [(column_list)]] ...
ON [object_type] priv_level
FROM user_or_role [, user_or_role] ...
REVOKE ALL [PRIVILEGES], GRANT OPTION
FROM user_or_role [, user_or_role] ...
REVOKE PROXY ON user_or_role
FROM user_or_role [, user_or_role] ...
REVOKE role [, role ] ...
FROM user_or_role [, user_or_role ] ...
user_or_role: {
user (see https://dev.mysql.com/doc/refman/8.0/en/account-names.html)
| role (see https://dev.mysql.com/doc/refman/8.0/en/role-names.html.
}
The REVOKE statement enables system administrators to revoke privileges
and roles, which can be revoked from user accounts and roles.
For details on the levels at which privileges exist, the permissible
priv_type, priv_level, and object_type values, and the syntax for
specifying users and passwords, see [HELP GRANT].
For information about roles, see
https://dev.mysql.com/doc/refman/8.0/en/roles.html.
When the read_only system variable is enabled, REVOKE requires the
CONNECTION_ADMIN or privilege (or the deprecated SUPER privilege), in
addition to any other required privileges described in the following
discussion.
REVOKE either succeeds for all named users and roles or rolls back and
has no effect if any error occurs. The statement is written to the
binary log only if it succeeds for all named users and roles.
Each account name uses the format described in
https://dev.mysql.com/doc/refman/8.0/en/account-names.html. Each role
name uses the format described in
https://dev.mysql.com/doc/refman/8.0/en/role-names.html. For example:
REVOKE INSERT ON *.* FROM 'jeffrey'@'localhost';
REVOKE 'role1', 'role2' FROM 'user1'@'localhost', 'user2'@'localhost';
REVOKE SELECT ON world.* FROM 'role3';
The host name part of the account or role name, if omitted, defaults to
'%'.
To use the first REVOKE syntax, you must have the GRANT OPTION
privilege, and you must have the privileges that you are revoking.
To revoke all privileges, use the second syntax, which drops all
global, database, table, column, and routine privileges for the named
users or roles:
REVOKE ALL PRIVILEGES, GRANT OPTION
FROM user_or_role [, user_or_role] ...
REVOKE ALL PRIVILEGES, GRANT OPTION does not revoke any roles.
To use this REVOKE syntax, you must have the global CREATE USER
privilege, or the UPDATE privilege for the mysql system schema.
The syntax for which the REVOKE keyword is followed by one or more role
names takes a FROM clause indicating one or more users or roles from
which to revoke the roles.
Roles named in the mandatory_roles system variable value cannot be
revoked.
A revoked role immediately affects any user account from which it was
revoked, such that within any current session for the account, its
privileges are adjusted for the next statement executed.
Revoking a role revokes the role itself, not the privileges that it
represents. Suppose that an account is granted a role that includes a
given privilege, and is also granted the privilege explicitly or
another role that includes the privilege. In this case, the account
still possesses that privilege if the first role is revoked. For
example, if an account is granted two roles that each include SELECT,
the account still can select after either role is revoked.
REVOKE ALL ON *.* (at the global level) revokes all granted static
global privileges and all granted dynamic privileges.
User accounts and roles from which privileges and roles are to be
revoked must exist, but the privileges and roles to be revoked need not
be currently granted to them.
A revoked privilege that is granted but not known to the server is
revoked with a warning. This situtation can occur for dynamic
privileges. For example, a dynamic privilege can be granted while the
component that registers it is installed, but if that component is
subsequently uninstalled, the privilege becomes unregistered, although
accounts that possess the privilege still possess it and it can be
revoked from them.
URL: https://dev.mysql.com/doc/refman/8.0/en/revoke.html
举个例子🌰：取消用户全局的查询权限
mysql> REVOKE SELECT ON *.* FROM 'test1'@'localhost';
Query OK, 0 rows affected (0.46 sec)
mysql> SELECT * FROM mysql.user WHERE user='test1';
+-----------+-------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------------------+--------------------------+----------------------------+---------------+-------------+-----------------+----------------------+-----------------------+------------------------------------------------------------------------+------------------+-----------------------+-------------------+----------------+------------------+----------------+------------------------+---------------------+--------------------------+-----------------+
| Host | User | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv | Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv | Repl_slave_priv | Repl_client_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Create_user_priv | Event_priv | Trigger_priv | Create_tablespace_priv | ssl_type | ssl_cipher | x509_issuer | x509_subject | max_questions | max_updates | max_connections | max_user_connections | plugin | authentication_string | password_expired | password_last_changed | password_lifetime | account_locked | Create_role_priv | Drop_role_priv | Password_reuse_history | Password_reuse_time | Password_require_current | User_attributes |
+-----------+-------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------------------+--------------------------+----------------------------+---------------+-------------+-----------------+----------------------+-----------------------+------------------------------------------------------------------------+------------------+-----------------------+-------------------+----------------+------------------+----------------+------------------------+---------------------+--------------------------+-----------------+
| localhost | test1 | N | Y | Y | Y | Y | Y | Y | Y | Y | Y | N | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | | NULL | NULL | NULL | 0 | 0 | 0 | 0 | caching_sha2_password | $A$005$O6xWn
ySVk:Eyu84Pgf4pizsW2E61uyWC/mv5.oRY903YXCKgZ.3UK9 | N | 2022-03-31 19:17:10 | NULL | N | Y | Y | NULL | NULL | NULL | NULL |
+-----------+-------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------------------+--------------------------+----------------------------+---------------+-------------+-----------------+----------------------+-----------------------+------------------------------------------------------------------------+------------------+-----------------------+-------------------+----------------+------------------+----------------+------------------------+---------------------+--------------------------+-----------------+
1 row in set (0.00 sec)
举个例子🌰：取消用户全局的所有权限
mysql> REVOKE ALL ON *.* FROM 'test1'@'localhost';
Query OK, 0 rows affected (1.90 sec)
mysql> SELECT * FROM mysql.user WHERE user='test1';
+-----------+-------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------------------+--------------------------+----------------------------+---------------+-------------+-----------------+----------------------+-----------------------+------------------------------------------------------------------------+------------------+-----------------------+-------------------+----------------+------------------+----------------+------------------------+---------------------+--------------------------+-----------------+
| Host | User | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv | Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv | Repl_slave_priv | Repl_client_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Create_user_priv | Event_priv | Trigger_priv | Create_tablespace_priv | ssl_type | ssl_cipher | x509_issuer | x509_subject | max_questions | max_updates | max_connections | max_user_connections | plugin | authentication_string | password_expired | password_last_changed | password_lifetime | account_locked | Create_role_priv | Drop_role_priv | Password_reuse_history | Password_reuse_time | Password_require_current | User_attributes |
+-----------+-------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------------------+--------------------------+----------------------------+---------------+-------------+-----------------+----------------------+-----------------------+------------------------------------------------------------------------+------------------+-----------------------+-------------------+----------------+------------------+----------------+------------------------+---------------------+--------------------------+-----------------+
| localhost | test1 | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | | NULL | NULL | NULL | 0 | 0 | 0 | 0 | caching_sha2_password | $A$005$O6xWn
ySVk:Eyu84Pgf4pizsW2E61uyWC/mv5.oRY903YXCKgZ.3UK9 | N | 2022-03-31 19:17:10 | NULL | N | N | N | NULL | NULL | NULL | NULL |
+-----------+-------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------------------+--------------------------+----------------------------+---------------+-------------+-----------------+----------------------+-----------------------+------------------------------------------------------------------------+------------------+-----------------------+-------------------+----------------+------------------+----------------+------------------------+---------------------+--------------------------+-----------------+
1 row in set (0.00 sec)
（四）重置 root 密码
（五）MySQL 8.0的新特性— —管理角色
在MySQL 8.0数据库中， role 角色可以看成是一些权限的集合，与 Linux 中用户组的概念相同。
使用 CREATE ROLE 语句创建角色：
mysql> help CREATE ROLE;
Name: 'CREATE ROLE'
Description:
Syntax:
CREATE ROLE [IF NOT EXISTS] role [, role ] ...
CREATE ROLE creates one or more roles, which are named collections of
privileges. To use this statement, you must have the global CREATE ROLE
or CREATE USER privilege. When the read_only system variable is
enabled, CREATE ROLE additionally requires the CONNECTION_ADMIN
privilege (or the deprecated SUPER privilege).
A role when created is locked, has no password, and is assigned the
default authentication plugin. (These role attributes can be changed
later with the ALTER USER statement, by users who have the global
CREATE USER privilege.)
CREATE ROLE either succeeds for all named roles or rolls back and has
no effect if any error occurs. By default, an error occurs if you try
to create a role that already exists. If the IF NOT EXISTS clause is
given, the statement produces a warning for each named role that
already exists, rather than an error.
The statement is written to the binary log if it succeeds, but not if
it fails; in that case, rollback occurs and no changes are made. A
statement written to the binary log includes all named roles. If the IF
NOT EXISTS clause is given, this includes even roles that already exist
and were not created.
Each role name uses the format described in
https://dev.mysql.com/doc/refman/8.0/en/role-names.html. For example:
CREATE ROLE 'administrator', 'developer';
CREATE ROLE 'webapp'@'localhost';
The host name part of the role name, if omitted, defaults to '%'.
For role usage examples, see
https://dev.mysql.com/doc/refman/8.0/en/roles.html.
URL: https://dev.mysql.com/doc/refman/8.0/en/create-role.html
举个例子🌰：创建角色
mysql> CREATE ROLE test;
Query OK, 0 rows affected (1.75 sec)
- 角色名与用户名的存储机制一样：没指定 host 则用 % 替代。
从 user 表中查看角色信息：
mysql> SELECT *
-> FROM mysql.user
-> WHERE host = '%'
-> AND NOT LENGTH(authentication_string);
+------+------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------------------+--------------------------+----------------------------+---------------+-------------+-----------------+----------------------+-----------------------+-----------------------+------------------+-----------------------+-------------------+----------------+------------------+----------------+------------------------+---------------------+--------------------------+-----------------+
| Host | User | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv | Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv | Repl_slave_priv | Repl_client_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Create_user_priv | Event_priv | Trigger_priv | Create_tablespace_priv | ssl_type | ssl_cipher | x509_issuer | x509_subject | max_questions | max_updates | max_connections | max_user_connections | plugin | authentication_string | password_expired | password_last_changed | password_lifetime | account_locked | Create_role_priv | Drop_role_priv | Password_reuse_history | Password_reuse_time | Password_require_current | User_attributes |
+------+------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------------------+--------------------------+----------------------------+---------------+-------------+-----------------+----------------------+-----------------------+-----------------------+------------------+-----------------------+-------------------+----------------+------------------+----------------+------------------------+---------------------+--------------------------+-----------------+
| % | test | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | | NULL | NULL | NULL | 0 | 0 | 0 | 0 | caching_sha2_password | | Y | 2022-03-31 20:41:21 | NULL | Y | N | N | NULL | NULL | NULL | NULL |
+------+------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------------------+--------------------------+----------------------------+---------------+-------------+-----------------+----------------------+-----------------------+-----------------------+------------------+-----------------------+-------------------+----------------+------------------+----------------+------------------------+---------------------+--------------------------+-----------------+
1 row in set (0.00 sec)
- 角色的本质就是没有密码的但可以被一些用户关联的用户。
使用 GRANT 语句授予角色权限，举个例子🌰：
mysql> GRANT SELECT ON *.* TO "test";
Query OK, 0 rows affected (0.14 sec)
mysql> GRANT INSERT ON *.* TO "test";
Query OK, 0 rows affected (0.05 sec)
mysql> SELECT * FROM mysql.user WHERE host = '%' AND NOT LENGTH(authentication_string);
+------+------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------------------+--------------------------+----------------------------+---------------+-------------+-----------------+----------------------+-----------------------+-----------------------+------------------+-----------------------+-------------------+----------------+------------------+----------------+------------------------+---------------------+--------------------------+-----------------+
| Host | User | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv | Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv | Repl_slave_priv | Repl_client_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Create_user_priv | Event_priv | Trigger_priv | Create_tablespace_priv | ssl_type | ssl_cipher | x509_issuer | x509_subject | max_questions | max_updates | max_connections | max_user_connections | plugin | authentication_string | password_expired | password_last_changed | password_lifetime | account_locked | Create_role_priv | Drop_role_priv | Password_reuse_history | Password_reuse_time | Password_require_current | User_attributes |
+------+------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------------------+--------------------------+----------------------------+---------------+-------------+-----------------+----------------------+-----------------------+-----------------------+------------------+-----------------------+-------------------+----------------+------------------+----------------+------------------------+---------------------+--------------------------+-----------------+
| % | test | Y | Y | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | | NULL | NULL | NULL | 0 | 0 | 0 | 0 | caching_sha2_password | | Y | 2022-03-31 20:41:21 | NULL | Y | N | N | NULL | NULL | NULL | NULL |
+------+------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------------------+--------------------------+----------------------------+---------------+-------------+-----------------+----------------------+-----------------------+-----------------------+------------------+-----------------------+-------------------+----------------+------------------+----------------+------------------------+---------------------+--------------------------+-----------------+
使用 REVOKE 语句撤销角色权限，举个例子🌰：
mysql> REVOKE INSERT ON *.* FROM "test";
Query OK, 0 rows affected (1.72 sec)
使用 GRANT 语句将角色赋予用户，举个例子🌰：
mysql> GRANT "test" TO 'test1'@'localhost';
Query OK, 0 rows affected (1.79 sec)
从 role_edges 表中查看用户与角色的关联关系，举个例子🌰：
mysql> SELECT * FROM mysql.role_edges;
+-----------+-----------+-----------+---------+-------------------+
| FROM_HOST | FROM_USER | TO_HOST | TO_USER | WITH_ADMIN_OPTION |
+-----------+-----------+-----------+---------+-------------------+
| % | test | localhost | test1 | N |
+-----------+-----------+-----------+---------+-------------------+
1 row in set (0.00 sec)
使用SELECT CURRENT_ROLE();语句查询角色激活情况，举个例子🌰：
mysql> SELECT CURRENT_ROLE();
+----------------+
| CURRENT_ROLE() |
+----------------+
| NONE |
+----------------+
1 row in set (0.00 sec)
- 角色可以在用户会话中处于活动状态或非活动状态。如果处于非活动状态，则用户不具有该角色拥有的权限，因此需要通过指定哪个角色处于活动状态来修改当前用户在当前会话中的有效权限。
激活角色，举个例子🌰：
# 非永久激活
mysql> SET ROLE granted_role_1, granted_role_2,
# 永久激活：在用户连接到服务器时自动激活所有显式授权和强制角色
SET global activate_all_roles_on_login=ON;
使用 DROP 语句删除角色，举个例子🌰：
mysql> DROP ROLE test;
Query OK, 0 rows affected (0.18 sec)
二，数据备份与恢复
尽管采取了一些管理措施来保证数据库的安全， 但是不确定的意外情况总是有可能造成数据的损失，例如意外的停电、管理员不小心的操作失误都可能会造成数据的丢失。
保证数据安全最重要的一个措施是确保对数据进行定期备份。如果数据库中的数据丢失或者出现错误，可以使用备份的数据进行恢复。
目标：
- 了解什么是数据备份
- 掌握各种数据备份的方法
- 掌握各种数据恢复的方法
- 掌握数据库迁移的方法
- 掌握表的导入和导出方法
- 熟练掌握综合案例中数据备份与恢复的方法和技巧
（一）数据备份
备份策略
根据备份的方法不同可以将备份分为：
- Hot Backup（热备）：是指数据库运行中直接备份，对正在运行的数据库操作没有任何的影响。
- Cold Backup（冷备）：是指数据库停止的情况下进行备份。
- Warm Backup（温备）：在数据库运行中加一个全局读锁（保证数据的一致性）后进行备份。
按照备份后文件的内容，备份又可以分为：
- 逻辑备份：是指备份出的文件内容是可读的，一般是文本文件。内容一般是由一条条SQL语句，或者是表内实际数据。一般适用于数据库的升级、迁移等工作，恢复所需要的时间往往较长。
- 裸文件备份：是指复制数据库的物理文件，既可以在数据库运行中的复制（如ibbackup、xtrabackup这类工具），也可在数据库服务停止后直接进行数据文件复制。备份的恢复时间往往较逻辑备份短很多。
按照备份数据库的内容来分，备份又可以分为：
- 完全备份：是指对数据库进行一个完整的备份。
- 增量备份：是指在上次完全备份的基础上，对于更改的数据进行备份。
- 日志备份：是指对MySQL数据库二进制日志的备份，通过对一个完全备份进行二进制日志的重做（replay）来完成数据库的point-in-time的恢复工作。
1，使用 mysqldump 命令备份
mysqldump 命令执行后，会将数据库备份成一个文本文件， 该文件包含了多个 SQL 语句， 使用这些语句可以重新创建表和插入数据，并恢复相关的环境变量。
mysqldump可以导出存储过程、导出触发器、导出事件、导出数据，但是却不能导出视图。因此，如果用户的数据库中还使用了视图，那么在用mysqldump备份完数据库后继续导出视图的定义，或者备份视图定义的 frm 文件，并在恢复时进行导入，这样才能保证完全恢复。
# 将数据库中的表备份到指定文件
mysqldump [options] db_name [tbl_name ...] > filename.sql
# 将数据库备份到指定文件
mysqldump [options] --databases db_name ... > filename.sql
# 将所有数据库备份到指定文件
mysqldump [options] --all-databases > filename.sql
举个例子🌰：备份某个数据库
C:\WINDOWS\system32>mysqldump -u root -p --databases demo > F:\ALL-TEST\backup\20220401.sql
Enter password: ****
看一下对应的文件：
基本信息：
-- MySQL dump 10.13 Distrib 8.0.23, for Win64 (x86_64)
--
-- Host: localhost Database: demo
-- ------------------------------------------------------
-- Server version 8.0.23
这里保存了一些环境变量：
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!50503 SET NAMES utf8mb4 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
--
-- Current Database: `demo`
--
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `demo` /*!40100 DEFAULT CHARACTER SET utf8 */ /*!80016 DEFAULT ENCRYPTION='N' */;
USE `demo`;
--
-- Table structure for table `test`
--
DROP TABLE IF EXISTS `test`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `test` (
`barcode` varchar(255) DEFAULT NULL,
`goodsname` varchar(255) DEFAULT NULL,
`price` decimal(10,2) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `test`
--
LOCK TABLES `test` WRITE;
/*!40000 ALTER TABLE `test` DISABLE KEYS */;
INSERT INTO `test` VALUES ('0001','book',3.00),('0002','pen',2.00),('0003','橡皮',5.00);
/*!40000 ALTER TABLE `test` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
-- Dump completed on 2022-04-01 14:22:17
2，直接复制数据存储目录
因为MySQL表保存为文件方式，所以可以直接复制MySQL数据库的存储目录 data 及其中的文件进行备份，
这是一种简单、 快速、 有效的备份方式。
要想保持备份的一致性，备份前需要对相关表执行 LOCK TABLES 操作， 然后对表执行 FLUSH TABLES 来确保开始备份前将所有激活的索引页写入硬盘。这样当复制数据库目录中的文件时，允许其他客户继续查询表。
也可以停止MySQL服务再使用这种备份操作。
这种方法对 InnoDB 存储引擎的表不适用。使用这种方法备份的数据最好恢复到相同版本的服务器中，不同的版本可能不兼容。
3，使用 MySQLhotcopyI 具快速备份
MySQLhotcopy 是一个 Perl 脚本，它使用 LOCK TABLES、
和 cp 或 scp 来快速备份数据库，是备份数据库或单个表最快的途径，但它只能运行在数据库目录所在的机器上，并且只能备份 MylSAM 类型的表。
（二）数据恢复
1，使用MySQL命令恢复
非登陆时可执行下面的命令：
mysql -u user -p [dbname] < filename.sql
登陆后可直接使用 SOURCE 命令：
mysql> use dbname;
mysql> SOURCE filename;
2，直接复制到数据库目录
如果数据库通过复制数据库文件备份， 可以直接复制备份的文件到MySQL数据目录下实现恢复。
- 通过这种方式恢复时，保存备份数据的数据库和待恢复的数据库服务器的主版本号必须相同。
- 只对 MylSAM 引擎的表有效。
- 执行恢复以前先关 闭MySQL 服务， 然后将备份的文件或目录覆盖 MySQL 的 data 目录， 再启动 MySQL 服务。
- 对于 Linux/UNIX 操作系统来说， 复制完文件需要将文件的用户和组更改为MySQL运行的用户和组 通常用户是MySQL，组也是MySQL。
3，MySQLhotcopy 快速恢复
在MySQL服务器停止运行后，将备份的数据库文件复制到MySQL存放数据的位置再重新启动MySQL服务即可。
如果以 root 用户执行该操作， 必须先指定数据库文件的所有者：
$ chown -R mysql.mysql /var/lib/mysql/dbname
$ cp -R /usr/backup/dbname /usr/lib/mysql/data
（三）数据库迁移
数据库迁移就是把数据从一个系统移:动到另一个系统上:
- 需要安装新的数据库服务器。
- MySQL版本更新。
- 数据库管理系统的变更(如从Microsoft SQL Server迁移到MySQL)。
1，相同版本的MySQL数据库之间的迁移
这种迁移过程其实就是对源数据库备份和对目标数据库恢复过程的组合。
最常用和最安全的方式是使用MySQLdump命令导出数据， 然后在目标数据库服务器使用 MySQL命令导入。
举个例子🌰：将www.abc.com主机上的MySQL数据库全部迁移到www.bcd.com主机上
www.abc.com$ mysqldump -h www.bac.com -uroot -ppassword dbname | mysql -h www.bcd.com -uroot -ppassword
2，不同版本的MySQL数据库之间的迁移
MySQL服务器升级时， 需要先停止服务， 然后卸载旧版本， 并安装新版的MySQL。
- 如果想保留旧版本中的用户访问控制信息，就需要备份MySQL中的MySQL数据库，在新版本MySQL安装完成之后，重新读入MySQL备份文件中的信息。
- 旧版本与新版本的MySQL可能使用不同的默认字符集， 例如MySQL 8.0版本之前， 默认字符集为 latinl 而 MySQL 8.0版本默认字符集为 utf8mb4。数据库中有中文数据的， 迁移过程中需要对默认字符集进行修改，不然可能无法正常显示结果。
- 新版本会对旧版本有一定兼容性。 从旧版本的MySQL向新版本的MySQL迁移时， 对于 MylSAM 引擎的表，可以直接复制数据库文件，也可以使用 MySQLhotcopy 工具、MySQLdump工具。 对于InnoDB引擎的表， 一般只能使用 MySQLdump 将数据导出。 然后使用MySQL命令导入到目标服务器上。
3，不同数据库之间的迁移
不同数据库之间的数据迁移方案设计及迁移工具选择
数据迁移 —— 从 PostgreSQL 到 MySQL
从 MySQL 迁移到 PostgreSQL 方案调研
（四）表的导出和导入
1，使用 SELECT…INTO OUTFILE 导出到文本文件
SELECT...INTO 命令可以将查询结果存储在变量中或写入文件。
SELECT columnlist FROM table WHERE condition INTO OUTFILE 'filename'
[OPTIONS]
FIELDS TERMINATED BY '字符串'：设置字符串为字段之间的分隔符，可以为单个或多个字符，默认情况下为制表符‘\t’。
FIELDS [OPTIONALLY] ENCLOSED BY '字符'：设置字符来括上 CHAR、VARCHAR 和 TEXT 等字符型字段。如果使用了 OPTIONALLY 则只能用来括上 CHAR 和 VARCHAR 等字符型字段。
FIELDS ESCAPED BY '字符'：设置如何写入或读取特殊字符，只能为单个字符，即设置转义字符，默认值为‘\’。
LINES STARTING BY '字符串'：设置每行开头的字符，可以为单个或多个字符，默认情况下不使用任何字符。
LINES TERMINATED BY '字符串'：设置每行结尾的字符，可以为单个或多个字符，默认值为‘\n’ 。
举个例子🌰：将指定表导出到指定文件：
mysql> SELECT * FROM example.department INTO OUTFILE 'F:\ALL-TEST\backup\department.txt';
ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
- 因为MySQL默认对导出的目录有权限限制，查看可导出的目的目录：
mysql> show global variables like '%secure%';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| require_secure_transport | OFF |
| secure_file_priv | NULL |
+--------------------------+-------+
2 rows in set, 1 warning (1.68 sec)
- 如果这里 secure_file_priv 并没有指定具体的目录，则需要手动修改 mysql.ini 进行配置：
...
# secure 目录
secure-file-priv="D:/tools_software/mysql/mysql-8.0.23-winx64/export_data/"
- 配置好之后，重新启动服务，再执行导出命令：
mysql> SELECT * FROM example.department INTO OUTFILE 'D:/tools_software/mysql/mysql-8.0.23-winx64/export_data/department.txt';
参考效果如下：
1001 科研部 新产品研发 2号楼5层
1002 生产部 管理公司生产作业 2号楼4层
1003 人事部 管理公司人事变动 2号楼3层
1004 法务部 处理公司法务业务 2号楼3层
1005 市场部 负责产品推广、销售与运营 2号楼4层
1006 客服部 提供品售后服务 \N
1007 财务部 负责财务结算、开支管理与工资发放 2号楼3层
可以指定不同的选项实现不同的输出格式：
mysql> SELECT * FROM example.department INTO OUTFILE 'D:/tools_software/mysql/mysql-8.0.23-winx64/export_data/department.txt';
FIELDS
TERMINATED BY ','
ENCLOSED BY '\"1
ESCAPED BY '\'
LINES
TERMINATED BY '\r\n';
参考效果如下：
"1","Green","21","Lawyer"
"2","Suse","22","dancer"
"3","Mary","24","Musician"
"4","Wiliam","20","sports man"
"5","Laura","25",'N'
"6","Evans","27","secretary"
"7","Dale","22","cook"
"8","Edison","28","singer"
"9","Harry","21","magician"
"10","Harriet","19","pianist"
mysql> SELECT * FROM example.department INTO OUTFILE 'D:/tools_software/mysql/mysql-8.0.23-winx64/export_data/department.txt';
LINES
STARTING BY '> 1
TERMINATED BY '<end>\r\n';
参考效果如下：
> 1 Green 21 Lawyer <end>
> 2 Suse 22 dancer <end>
> 3 Mary 24 Musician <end>
> 4 Wiliam 20 sports man <end>
> 5 Laura 25 \N <end>
> 6 Evans 27 secretary <end>
> 7 Dale 22 cook <end>
> 8 Edison 28 singer <end>
> 9 Harry 21 magician <end>
> 10 Harriet 19 pianist <end>
2，使用MySQLdump命令导出文本文件
MySQLdump不仅可以将数据导出为包含 SQL 语句的sql文件， 也可以导出为纯文本文件：
mysqldump -T path-u root -p dbname [tables] [OPTIONS]
--OPTIONS 选项
--fields-terminated-by=value
--fields-enclosed-by=value
--fields-optionally-enclosed-by=value
--fields-escaped-by=value
--lines-terminated-by=value
-T参数指定为导出纯文本文件。
3，使用MySQL命令导出
如果MySQL服务器是单独的机器，用户是在client上导出数据。 可以使用musql -e语句将查询结果导出到指定文件:
mysql -u root -p --execute="SELECT语句” dbname > filename.txt
--execute选项表示执行该选项后面的语句并退出。- 导出的文件中不同列之间使用制表符分隔。
举个例子🌰：
mysql -u root -p --execute="SELECT * FROM person;" test db > D:\person3.txt
# -vertical参数显示结果
mysql -u root -p --vertical --execute="SELECT * FROM person;" test_db > D:\person4.txt
# -html选项表示将结果导出到html文件，
mysql -u root -p --html --execute="SELECTD:\person5.html
4，使用LOAD DATA INFILE方式导入文本文件
LOAD DATA INFILE语句用于高速地从一个文本文件中读取行， 并装入一个表中：
LOAD DATA INFILE filename INTO TABLE tablename [OPTIONS] [IGNORE number LINES]
--OPTIONS选项
FIELDS TERMINATED BY 'value'
FIELDS [OPTIONALLY] ENCLOSED BY 'value'
FIELDS ESCAPED BY 'value'
LINES STARTING BY 'value'
LINES TERMINATED BY 'value
举个例子🌰：
# 恢复之前，要将person表中的数据全部删除
mysql> USE test_db;
Database changed;
mysql> DELETE FROM person;
Query OK, 10 rows affected (0.00 sec)
# 再恢复
mysql> LOAD DATA INFILE 'D:\person0.txt' INTO TABLE test_db.person;
Query OK, 10 rows affected (0.00 sec)
Records: 10 Deleted: 0 Skipped: 0 Warnings: 0
5，使用mysqlimport命令导入文本文件
使用 mysqlimport 命令可以导入文本文件， 并且不需要登录MySQL客户端：
--user-thread参数可并发地导入多个文件。
mysqlimport -u root-p dbname [filename1 filename2...] [OPTIONS]
--OPTIONS 选项
--fields-terminated-by=value
--fields-enclosed-by=value
--fields-optionally-enclosed-by=value
--fields-escaped-by=value
--lines-terminated-by=value
--ignore-lines=n
举个例子🌰：
mysql> mysqlimport --use-threads=2 test/home/mysql/t.txt /home/mysql/s.txt
三，MySQL日志
MySQL日志记录了 MySQL数据库日常操作和错误信息。从日志当中可以查询到MySQL数据库的运行情况、 用户操作、 错误信息等， 可以为MySQL管理和优化提供必要的信息。
（一）日志简介
MySQL日志主要分为4类：
- 错误日志： 记录MySQL服务的启动、 运行或停止MySQL服务时出现的问题。
- 查询日志： 记录建立的客户端连接和执行的语句。
- 二进制日志： 记录所有更改数据的语句， 可以用于数据复制。
- 慢查询日志： 记录所有执行时间超过 long_query_time 的所有查询或不使用索引的查询。
启动日志功能会降低MySQL数据库的性能。 例如在查询非常频繁的MySQL数据库系统中，如果开启了通用查询日志和慢查询日志，MySQL数据库会花费很多时间记录日志。 同时日志会占用大量的磁盘空间。
（二）二进制日志
二进制日志主要记录MySQL数据库的变化，包括数据和结构。
尽管运行启动运行二进制日志的服务器会有轻微的性能损失，但从数据安全角度来说是划算的：
- 有利于使用主从复制机制实现数据冗余备份。
- 有利于使用时间点恢复机制实现故障排除。
1，启动和设置二进制日志
默认情况下二进制日志是开启的，可以通过修改MySQL的配置文件来启动和设置：
# 开启二进制日志
log-bin [=path/ [filename]]
- path 指定日志文件所在的目录路径。
- filename 指定日志文件的名称。
# 关闭二进制日志
skip-log-bin
# 以秒为单位设置二进制日志过期时间
binlog_expire_logs_seconds
- 默认值为2592000，即 30 天。
# 单个文件的大小限制，如果日志大小超给定值，就会发生滚动（关闭当前文件，重新打开一个新的日志文件）
max_binlog_siz
- 默认值为 1GB。
更多配置项请参考官方文档：17.1.6.4 Binary Logging Options and Variables
举个例子🌰：配置好日志后，重启服务，再查询日志设置详情
mysql> SHOW VARIABLES LIKE 'log_%';
+----------------------------------------+----------------------------------------------------------------+
| Variable_name | Value |
+----------------------------------------+----------------------------------------------------------------+
| log_bin | ON |
| log_bin_basename | D:\tools_software\mysql\mysql-8.0.23-winx64\data\binlog |
| log_bin_index | D:\tools_software\mysql\mysql-8.0.23-winx64\data\binlog.index |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
| log_error | D:\tools_software\mysql\mysql-8.0.23-winx64\data\DangFuLin.err |
| log_error_services | log_filter_internal; log_sink_internal |
| log_error_suppression_list | |
| log_error_verbosity | 2 |
| log_output | FILE |
| log_queries_not_using_indexes | OFF |
| log_raw | OFF |
| log_slave_updates | ON |
| log_slow_admin_statements | OFF |
| log_slow_extra | OFF |
| log_slow_slave_statements | OFF |
| log_statements_unsafe_for_binlog | ON |
| log_throttle_queries_not_using_indexes | 0 |
| log_timestamps | UTC |
+----------------------------------------+----------------------------------------------------------------+
19 rows in set, 1 warning (0.03 sec)
2，查看二进制日志
在数据目录下看看有哪些二进制日志文件：
- 数据库文件最好不要与日志文件放在同一个磁盘上，避免被“一锅端”。
MySQL服务重新启动一次， 以“.000001”为后缀的文件会增加一个， 并且后缀名加1递增。
举个例子🌰：查看二进制日志概述
mysql> SHOW BINARY LOGS;
+---------------+-----------+-----------+
| Log_name | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000046 | 156 | No |
| binlog.000047 | 3155 | No |
| binlog.000048 | 4713 | No |
| binlog.000049 | 156 | No |
+---------------+-----------+-----------+
4 rows in set (0.00 sec)
举个例子🌰：查看二进制日志详情
C:\WINDOWS\system32>mysqlbinlog binlog.000046;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
mysqlbinlog: File 'binlog.000046;' not found (OS errno 2 - No such file or directory)
ERROR: Could not open log file
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
mysqlbinlog命令的更多用法请参考官方文档： 4.6.9 mysqlbinlog — Utility for Processing Binary Log Files
3，删除二进制日志
MySQL的二进制文件可以配置自动删除，也支持安全的手动删除方法。
举个例子🌰：使用RESET MASTER语句删除所有二进制日志文件
mysql> RESET MASTER;
Query OK, 0 rows affected (2.07 sec)
mysql> SHOW BINARY LOGS;
+---------------+-----------+-----------+
| Log_name | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000001 | 156 | No |
+---------------+-----------+-----------+
1 row in set (0.00 sec)
- 所有二进制日志将被删除，MySQL会重新创建二进制日志，新的日志文件扩展名将重新从000001开始编号。
举个例子🌰：使用PURGE MASTER LOGS语句删除指定日志文件
# 删除指定二进制日志
mysql> PURGE MASTER LOGS TO 'binlog.000001';
Query OK, 0 rows affected (0.17 sec)
# 删除指定时间之前的二进制日志
mysql> PURGE MASTER LOGS BEFORE '20220401';
Query OK, 0 rows affected, 1 warning (0.74 sec)
4，使用二进制日志恢复数据库
可以使用 mysqlbinlog 工具从指定的时间点开始直到现在， 或另一个指定的时间点的日志中恢复数据。
5，暂停与恢复二进制日志功能
# 暂停
mysql> SET sql_log_bin = 0;
Query OK, 0 rows affected (1.58 sec)
# 恢复
mysql> SET sql_log_bin = 1;
Query OK, 0 rows affected (0.00 sec)
（三）错误日志
错误日志文件包含了当MySQLd启动和停止时， 以及服务器在运行过程中发生任何严重错误时的相关信息。
1，启动和设置错误日志
在默认情况下， 错误日志会记录到数据库的数据目录下。 如果没有在配置文件中指定文件名，则文件名默认为 $hostname.err：
# 启动错误日志功能
log_error=[path / [file_name]]
更多配置项请参考官方文档：5.1.8 Server System Variables
2，查看错误日志
举个例子🌰：查询错误日志的存储路径和文件名
mysql> SHOW VARIABLES LIKE 'log_error';
+---------------+----------------------------------------------------------------+
| Variable_name | Value |
+---------------+----------------------------------------------------------------+
| log_error | D:\tools_software\mysql\mysql-8.0.23-winx64\data\DangFuLin.err |
+---------------+----------------------------------------------------------------+
1 row in set, 1 warning (0.00 sec)
举个例子🌰：可直接用记事本查看错误日志的内容
2022-03-30T09:21:28.585030Z 0 [System] [MY-010116] [Server] D:\tools_software\mysql\mysql-8.0.23-winx64\bin\mysqld (mysqld 8.0.23) starting as process 17720
2022-03-30T09:21:30.342248Z 0 [Warning] [MY-013242] [Server] --character-set-server: 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
2022-03-30T09:21:30.493316Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2022-03-30T09:21:32.566420Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2022-03-30T09:21:33.000541Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060
2022-03-30T09:21:33.602588Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2022-03-30T09:21:33.603104Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
2022-03-30T09:21:33.967541Z 0 [System] [MY-010931] [Server] D:\tools_software\mysql\mysql-8.0.23-winx64\bin\mysqld: ready for connections. Version: '8.0.23' socket: '' port: 3306 MySQL Community Server - GPL.
2022-04-01T08:50:22.899584Z 0 [System] [MY-013105] [Server] D:\tools_software\mysql\mysql-8.0.23-winx64\bin\mysqld: Normal shutdown.
2022-04-01T08:50:27.504986Z 0 [System] [MY-010910] [Server] D:\tools_software\mysql\mysql-8.0.23-winx64\bin\mysqld: Shutdown complete (mysqld 8.0.23) MySQL Community Server - GPL.
2022-04-01T08:50:32.653442Z 0 [System] [MY-010116] [Server] D:\tools_software\mysql\mysql-8.0.23-winx64\bin\mysqld (mysqld 8.0.23) starting as process 7792
2022-04-01T08:50:33.197101Z 0 [Warning] [MY-013242] [Server] --character-set-server: 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
2022-04-01T08:50:33.555368Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2022-04-01T08:50:57.914489Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2022-04-01T08:51:06.657996Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060
2022-04-01T08:51:09.835447Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2022-04-01T08:51:09.835946Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
2022-04-01T08:51:12.481986Z 0 [System] [MY-010931] [Server] D:\tools_software\mysql\mysql-8.0.23-winx64\bin\mysqld: ready for connections. Version: '8.0.23' socket: '' port: 3306 MySQL Community Server - GPL.
3，删除错误日志
MySQL的错误日志是以文本文件的形式存储在文件系统中的， 可以直接删除。
- 如果执行了
FLUSH LOGS语句 或flush-log命令，则会关闭并重新打开服务器正在写入的任何日志文件。
（四）通用查询日志
通用查询日志记录MySQL的所有用户操作，包括启动和关闭服务、执行查询和更新语句等。
1，启动通用查询日志
MySQL服务器默认情况下并没有开启通用查询日志。启动后，文件名默认为 $hostname.log：
举个例子🌰：手动启动与关闭通用查询日志：
mysql> SHOW VARIABLES LIKE '%general%';
+------------------+----------------------------------------------------------------+
| Variable_name | Value |
+------------------+----------------------------------------------------------------+
| general_log | OFF |
| general_log_file | D:\tools_software\mysql\mysql-8.0.23-winx64\data\DangFuLin.log |
+------------------+----------------------------------------------------------------+
2 rows in set, 1 warning (0.00 sec)
mysql> set @@global.general_log=1;
Query OK, 0 rows affected (1.86 sec)
mysql> SHOW VARIABLES LIKE '%general%';
+------------------+----------------------------------------------------------------+
| Variable_name | Value |
+------------------+----------------------------------------------------------------+
| general_log | ON |
| general_log_file | D:\tools_software\mysql\mysql-8.0.23-winx64\data\DangFuLin.log |
+------------------+----------------------------------------------------------------+
2 rows in set, 1 warning (0.00 sec)
mysql> set @@global.general_log=0;
Query OK, 0 rows affected (0.15 sec)
mysql> SHOW VARIABLES LIKE '%general%';
+------------------+----------------------------------------------------------------+
| Variable_name | Value |
+------------------+----------------------------------------------------------------+
| general_log | OFF |
| general_log_file | D:\tools_software\mysql\mysql-8.0.23-winx64\data\DangFuLin.log |
+------------------+----------------------------------------------------------------+
2 rows in set, 1 warning (0.00 sec)
2，查看通用查询日志
可以使用文本编辑器直接打开：
D:\tools_software\mysql\mysql-8.0.23-winx64\bin\mysqld, Version: 8.0.23 (MySQL Community Server - GPL). started with:
TCP Port: 3306, Named Pipe: MySQL
Time Id Command Argument
2022-04-02T02:29:35.711248Z 23 Query SHOW VARIABLES LIKE '%general%'
2022-04-02T02:29:46.044595Z 23 Query set @@global.general_log=0
3，删除通用查询日志
通用查询日志本身就是一个可以直接删除的文件。这个文件的大小通常会议比较快的速度增长，因此可以定期删除部分内容，或者做日志切割。
（五）慢查询日志
慢查询日志是记录查询时长超过指定时间的日志。通过慢查询日志可以找出执行时间较长、执行效率较低的语句，然后进行优化。
1，启动和设置慢查询日志
MySQL中慢查询日志默认是关闭的。
可以通过配置文件中的 log-slow-queries 选项打开，也可以在MySQL服务启动的时候使用 --log-slow-queries[=file_name] 启动慢查询日志。同时需要再配置文件中通过 long_query_time 选项指定记录阈值。
除了在配置文件中修改配置，还能使用 SET 语句通过设置全局变量的方式配置日志启动：
mysql> SHOW VARIABLES LIKE '%slow_query%';
+---------------------+---------------------------------------------------------------------+
| Variable_name | Value |
+---------------------+---------------------------------------------------------------------+
| slow_query_log | OFF |
| slow_query_log_file | D:\tools_software\mysql\mysql-8.0.23-winx64\data\DangFuLin-slow.log |
+---------------------+---------------------------------------------------------------------+
2 rows in set, 1 warning (0.00 sec)
# 配置日志输出路径
set global log_output='TABLE';
# 打开慢查询日志
mysql> set global slow_query_log='ON';
Query OK, 0 rows affected (0.36 sec)
# 设置时间阈值为 2 秒
mysql> set global long_query_time=2;
Query OK, 0 rows affected (0.00 sec)
# 关闭慢查询日志
set global slow_query_log='OFF';
2，查看慢查询日志
MySQL的慢查询日志也是以文本形式存储的，可以直接使用文本编辑器查看。
C:\Program Files\MySQL\MySQL Server 8.0\bin\mysqld.exe, Version: 8.0.13 (MySQL
Community Server - GPL). started with:
TCP Port: 3306, Named Pipe: MySQL
Time Id Command Argument
# Time: 181230 17:50:35
# User@Host: root[root] @ localhost [127.0.0.1]
# Query_time: 136.500000 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 0
SET timestamp=1314697835;
SELECT BENCHMARK(100000000, PASSWORD(1newpwd'));
- 借助慢查询日志分析工具，比如 MySQL Dump Slow、MySQL SLA、MySQL Log Filter、MyProfi，可以更加方便地分析慢查询语句。
3，删除慢查询日志
和通用查询日志一样， 慢查询日志也可以直接删除。
删除后在不重启服务器的情况下，需要执行MySQLadmin -u root-p flush-logs重新生成日志文件， 或者在客户端登录到服务器执行flush logs语句重建日志文件。
四，MySQL监控
五，锁与事务
（一）事务概述
1，为什么需要事务
想象一个场景：A 通过银行向 B 转账。银行应该这样做：
- 从 A 账户上减少一笔钱
- 向 B 账户上增加同样数量的一笔钱
看起来其实够简单，但中途可能存在多种意外导致这次转账失败，比如银行突然停电、转账系统被攻击后崩溃等等，这就会导致一些问题，比如 A 账户上钱被划走了但没到 B 的账户、A 账户上钱没被划走但 B 的账户收到了钱等等，总之就是会出现不应该出现的状况。而且因为银行的转账系统是多人使用，考虑到意外情况，不确定的结果就更加复杂。
显然，简单地将数据库操作中的状态转换映射到现实中的状态转换是不容易的，因此我们需要一种机制来保障这种状态转换的准确性。这种机制就是事务管理。
这里的事务就是一组用户定义的数据库操作序列，是一个不可分割的最小工作单位。
2，事务的 ACID 特性
事务有着极其严格的定义，它必须同时满足四个特性：
- 原子性（Atomicity）。
- 一致性（Consistency）。
- 隔离性（Isolation）。
- 持久性（Durability）。
（1）原子性
原子性要求事务的所有的操作，要么全部完成，要么全不完成。
因为一个事务往往包含多个操作，比如一个取款的流程为：
- 登录ATM机平台，验证密码。
- 从远程银行的数据库中，取得账户的信息。
- 用户在ATM机上输入欲提取的金额。
- 从远程银行的数据库中，更新账户信息。
- ATM机出款。
- 用户取钱。
整个取款的操作过程应该视为原子操作，要么都做，要么都不做。
只有使事务中所有的数据库操作都执行成功，才算整个事务成功。如果事务中任何一个 SQL 语句执行失败，那么已经执行成功的 SQL 语句都必须撤销，并让数据库状态应该退回到执行事务前的状态。
如果事务中的操作都是只读的，要保持原子性是很简单的。一旦发生任何错误，要么重试，要么返回错误代码。因为只读操作不会改变系统中的任何相关部分。
但是，当事务中的操作需要改变系统中的状态时，例如插入、更新或删除记录，情况可能就不像只读操作那么简单了。如果操作失败，很有可能引起状态的变化，因此必须要保护系统中并发用户访问受影响的部分数据。
（2）一致性
一致性要求事务的结果使得数据库从一个一致性状态转变为另一个一致性状态。
比如在表中有一个存在唯一约束的字段，如果在一个事务完成后，这个字段变得非唯一了，这就破坏了事务的一致性要求，即事务将数据库从一种一致性状态变为了一种不一致性状态。
事务被提交后有两种结果，一种是成功完成，另一种是未完成，但做的操作都必须被撤销以返回执行事务之前的状态。只有成功完成或撤销才能保证结果状态一致。
（3）隔离性
隔离性要求每个事务的读写对象与其他事务的读写对象能相互隔离的、互不可见的。
比如在表中有一个存在一个表示商品数量的字段，假设最初的值为10，A 看到剩余 10 个就打算买 2 个则应该只剩 8 个，且几乎同时的稍后一点时刻 B 看到剩余 8 个就打算买 8 个则应该剩 0 个，最后其他用户看到只剩 0 个后放弃购买打算。总之，不能说 A 买 2 个，B 还能买 10 个，C 再买 2 个，最后导致剩余 -4 的情况。
隔离性在多用户并发操作同一资源时相当重要，通常这使用锁来实现。当前数据库系统中都提供了一种粒度锁（granular lock）的策略，允许事务仅锁住一个实体对象的子集，以此来提高事务之间的并发度。
（4）持久性
持久性要求事务应该永久保留其结束后产生的状态转换的结果。
两人相互转账成功后的结果应该是能后被持久化的，不会因为时间的变化而变化，也不会因为修复了转账后崩溃的数据库而导致各自的余额变化。
持久性是保证事务系统的高可靠性的重要部分，与其他工具配合才能完成高可用性。
ACID 都讲完之后，再来看看事务的定义：事务（Transaction）就是需要保证原子性、隔离性、一致性和持久性的一个或多个数据库操作。
3，事务所处的状态与分类
根据事务中的操作所在的不同执行阶段，可以把事务大致划分成五个状态：
- 活动的（active）：事务对应的数据库操作正在执行过程中。
- 部分提交的（partiaIly committed）：事务中的最后一个操作执行完成，但由于操作都在内存中执行，所操作的结果并没有刷新到磁盘。
- 失败的（failed）：事务处于活动的状态或者部分提交的状态 时，可能遇到了某些意外而无法继续执行，或者入为停止了当前事务的执行。
- 中止的（aborted）： 当因为事务未执行完而失败时，将已经做的操作都撤销以恢复到最初的数据状态。
- 提交的（committed）：一个处于部分提交的状态的事务将修改过的数据都刷新到磁盘中。
从事务理论的角度来说，可以把事务分为以下五种类型：
-
扁平事务（Flat Transaction）：所有操作都处于同一层次，要么都执行，要么都回滚。
-
带有保存点的扁平事务（Flat Transactions with Savepoints）：在扁平事务的基础上添加保存点（Savepoint）来通知系统记住事务当前的状态，以便当之后发生错误时事务能回到保存点当时的状态。
-
链事务（Chained Transactions）：在带有保存点的扁平事务的基础上以保存点为分割点，将事务拆分为更轻量的事务，通过一个事务提交触发下一个事务的开始。
-
嵌套事务（Nested Transactions）：将链式结构转为树状结构，各个轻量级的扁平事务作为叶结点，并统一由作为根节点的顶层事务进行逻辑控制。
-
分布式事务（Distributed Transactions）：需要在分布式环境下相互配合才能运行的扁平事务。
（二）简单使用事务管理机制
InnoDB 存储引擎中的事务完全符合 ACID 的特性，这里就使用InnoDB 存储引擎。
1，开启事务
可以使用下面两种语句来开启一个事务.
BEGIN [WORK];
或
START TRANSACTION [ OPTIONS ];
- READ ONLY：标识当前事务是一个只读事务，也就是属于该事务的数据库操作只能读取数据，而不能修改数据。
- READ WR1TE：标识当前事务是一个读写事务。
- WlTH CONSISTENT SNAPSHOT：启动一致性读。
举个例子🌰：开始事务
mysql> USE bank;
Database changed
mysql> SHOW TABLES;
+----------------+
| Tables_in_bank |
+----------------+
| account |
+----------------+
1 row in set (0.00 sec)
mysql> DESC account;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| name | varchar(10) | NO | PRI | NULL | |
| amount | int | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> SELECT * FROM account;
+------+--------+
| name | amount |
+------+--------+
| A | 100 |
| N | 200 |
+------+--------+
2 rows in set (0.00 sec)
# 开始事务
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)
# 获得 A 账户余额
mysql> SELECT amount INTO @a.amt FROM account WHERE name='A';
Query OK, 1 row affected (0.00 sec)
# 从 A 账户余额转出 10
mysql> UPDATE account SET amount=@a.amt-10 WHERE name='A';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
# 获得 B 账户余额
mysql> SELECT amount INTO @b.amt FROM account WHERE name='B';
Query OK, 0 rows affected, 1 warning (0.12 sec)
# 向 B 账户余额转如 10
mysql> UPDATE account SET amount=@b.amt+10 WHERE name='B';
Query OK, 0 rows affected (1.62 sec)
Rows matched: 0 Changed: 0 Warnings: 0
2，提交事务
如果事务成功完成，则需要使用 COMMIT 语句进行提交，以确保事务结果被持久化。
- 在 Inno DB中，所有用户的每条单独的语句都发生在事务中。默认情况下，MySQL 为每个启用的新连接启动 autocommit （可通过
SET autocommit = 0关闭），因此如果该语句没有返回错误，MySQL 会在每个 SQL 语句之后执行自动提交。
mysql> show variables like "autocommit";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit | ON |
+---------------+-------+
1 row in set, 1 warning (0.00 sec)
- 在事务处理中，提交不会隐含地进行，需要显式地使用
COMMIT语句进行提交。
举个例子🌰：提交事务
mysql> COMMIT;
Query OK, 0 rows affected (1.90 sec)
以下这些 SQL 语句会产生一个隐式的提交操作：
- DDL语句：ALTER DATABASE…UPGRADE DATA DIRECTORY NAME，ALTER EVENT，ALTER PROCEDURE，ALTER TABLE，ALTER VIEW，CREATE DATABASE，CREATE EVENT，CREATE INDEX，CREATE PROCEDURE，CREATE TABLE，CREATE TRIGGER，CREATE VIEW，DROP DATABASE，DROP EVENT，DROP INDEX，DROP PROCEDURE，DROP TABLE，DROP TRIGGER，DROP VIEW，RENAME TABLE，TRUNCATE TABLE。
- 用来隐式地修改MySQL架构的操作：CREATE USER、DROP USER、GRANT、RENAME USER、REVOKE、SET PASSWORD。
- 管理语句：ANALYZE TABLE、CACHE INDEX、CHECK TABLE、LOAD INDEX INTO CACHE、OPTIMIZE TABLE、REPAIR TABLE。
3，使用保存点
在事务中可以通过 SAVEPOINT 语句指定事务的一个保存点，然后可以通过 ROLLBACK TO 语句回滚到指定的保存点。可以通过 RELEASE 语句释放保存点。
- 对于复杂的事务，可以定义多个不同的保存点以在不同的条件下恢复到不同的状态。
举个例子🌰：使用保存点
# 初始状态
mysql> SELECT * FROM account;
+------+--------+
| name | amount |
+------+--------+
| A | 100 |
| B | 200 |
+------+--------+
2 rows in set (0.00 sec)
# 开启事务
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT amount INTO @a.amt FROM account WHERE name='A';
Query OK, 1 row affected (0.00 sec)
mysql> UPDATE account SET amount=@a.amt-10 WHERE name='A';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
# 设置保存点
mysql> SAVEPOINT decr_10_from_A;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT amount INTO @b.amt FROM account WHERE name='B';
Query OK, 1 row affected (0.00 sec)
mysql> UPDATE account SET amount=@b.amt+10 WHERE name='B';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
# 设置保存点
mysql> SAVEPOINT incr_10_to_B;
Query OK, 0 rows affected (0.00 sec)
# 查看操作结果
mysql> SELECT * FROM account;
+------+--------+
| name | amount |
+------+--------+
| A | 90 |
| B | 210 |
+------+--------+
2 rows in set (0.00 sec)
# 假设这里出现操作错误
mysql> UPDATE account SET amount=@b.amt+30 WHERE name='B';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> SELECT * FROM account;
+------+--------+
| name | amount |
+------+--------+
| A | 90 |
| B | 230 |
+------+--------+
2 rows in set (0.00 sec)
# 回滚到指定的保存点
mysql> ROLLBACK TO incr_10_to_B;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM account;
+------+--------+
| name | amount |
+------+--------+
| A | 90 |
| B | 210 |
+------+--------+
2 rows in set (0.00 sec)
# 提交事务
mysql> COMMIT;
Query OK, 0 rows affected (0.16 sec)
#
mysql> SELECT * FROM account;
+------+--------+
| name | amount |
+------+--------+
| A | 90 |
| B | 210 |
+------+--------+
2 rows in set (0.00 sec)
4，回滚事务
如果在事务还未结束之前发现某条操作语句有误，则可以使用 ROLLBACK 语句回滚事务，从而使数据库恢复到事务执行之前的状态。
5，对于事务操作的统计
由于 InnoDB 存储引擎是支持事务的，因此在考虑每秒请求数（Question Per Second，QPS）的同时，应该关注每秒事务处理的能力（Transaction Per Second，TPS）。
计算 TPS 的方法是 (com_commit+com_rollback) / time。
- 但是利用这种方法进行计算的前提是：所有的事务必须都是显式提交的，如果存在隐式地提交和回滚（默认autocommit=1），不会计算到 com_commit 和 com_rollback 变量中。
mysql＞SHOW GLOBAL STATUS LIKE'com_commit'\G;
***************************1.row***************************
Variable_name:Com_commit
Value:5
1 row in set(0.00 sec)
mysql＞INSERT INTO t SELECT 3;
Query OK,1 row affected(0.00 sec)
Records:1 Duplicates:0 Warnings:0
mysql＞SELECT*FROM t\G;
***************************1.row***************************
a:1
***************************2.row***************************
a:2
***************************3.row***************************
a:3
3 rows in set(0.00 sec)
mysql＞SHOW GLOBAL STATUS LIKE'com_commit'\G;
***************************1.row***************************
Variable_name:Com_commit
Value:5
1 row in set(0.00 sec)
（三）事务并发问题及隔离级别
想象一种情况：在多人操作的 MySQL 数据库中有两个事务：
- 事务一删除了表中的一行，但未结束事务。
- 事务二紧接着来查询被事务一删除的那样的数据。那么事务二会获得怎样的查询结果呢？
事务一和事务二就是是一个路口上行驶的车，路口就是两者操作的数据：
在多个事务同时操作一项资源时，可能产生以下问题：
1，脏读、不可重复读、幻读
脏读：一个事务读到了另一个未提交的事务修改过的数据。
不可重复读：一个事务只能读到另一个已经提交的事务修改后的数据即最新值，但需要读的是未修改的数据。
幻读：一个事务先根据某些条件查询一些记录但因为不存在而读不出来，就准备插入相关记录，但在插入完成之前，另一个事务已经向表中插入了符合这些条件的记录，原先的事务会因为记录已存在而无法插入，看起来第一次读就像产生了读不出来的幻觉一样。
2，四个隔离级别
如何保证两辆车安全地通过路口？这就诞生了事务隔离机制，事务隔离机制提供了四中隔离级别来划分一个事务与其他事务在资源或数据修改方面的隔离程度。
SQL标准定义的四个隔离级别为：
- 未提交的读（READ UNCOMMITTED）：事务可以读到其他事务写入但未提交的记录。
- 已提交的读（READ COMMITTED）：事务可以读到其他事务写入并已提交的记录。
- 可重复的读（REPEATABLE READ）：保证同一事务读到相同的结果，无论在此期间是否有别的事务对数据进行了修改。
- 序列化（SERIALIZABLE）：最高的隔离级别，它通过强制事务排序（使用锁），使之不可能相互冲突。
各个隔离级别能解决哪些问题：
官方文档：15.7.2.1 Transaction Isolation Levels
MySQL的四种事务隔离级别及实例演示
3，使用隔离级别
1，查看当前会话的隔离级别：
mysql> SHOW VARIABLES LIKE 'transaction_isolation';
+-----------------------+-----------------+
| Variable_name | Value |
+-----------------------+-----------------+
| transaction_isolation | REPEATABLE-READ |
+-----------------------+-----------------+
1 row in set, 1 warning (0.00 sec)
mysql> SELECT @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| REPEATABLE-READ |
+-------------------------+
1 row in set (0.00 sec)
2，设置当前会话的隔离级别：
使用语句：
SET [GLOBAL|SESSION] TRANSACTION ISOLATION LEVEL level;
level: {
REPEATABLE READ
| READ COMMITTED
| READ UNCOMMITTED
| SERIALIZABLE
}
或命令：
--transaction-isolation=level
官方文档：设置隔离级别：13.3.7 SET TRANSACTION Statement
（四）事务的实现之 redo 与 undo
事务的实现，就是对 ACID 的实现，前三者通过数据库的 redo log 和 undo log 来完成。
redo 和 undo 的作用都可以视为是一种恢复操作，redo 恢复因提交事务修改的页操作，undo 回滚行记录到某个特定版本。因此两者记录的内容不同，redo 通常是物理日志，记录的是页的物理修改操作。undo 是逻辑日志，根据每行进行记录。
1，redo
在前面事务的操作与特性中说到，事务只有在提交之后，才能保证新状态的持久性。整个过程大概是这样的：
- 开启事务；
- 数据库在进行增删改之前，先将管理表空间的页从磁盘缓存到内存上；
- 在内存上实现增删改；
- 提交事务；
- 将内存中对页的修改写入磁盘。
该可能存在这样一种情况：在最后刷盘未完成时，系统崩溃，导致持久化失败。
一种解决办法就是将刷盘悄悄提到提交之前进行，再加上各处添加的事务保存点，能较小意外情况带来的损失。
但这样还是有些问题：
- 如果我们只在页面中做了极小的修改，那么刷盘整个页面是不是有点不划算？
- 如果一条语句修改的内容存在于多个页面，那么可能产生的随机页面刷盘是不是就会产生 I/O 性能损失？
要是能进一步缩减刷盘量，让系统只刷盘真正的修改之处，显然就会减少意外的担心，因为在恢复数据时只需要恢复一点点数据就行。
因为在系统奔溃重启时需要按照上述内容所记录的步骤重新更新数据页，所以上述内容也被称之为重做日志，即 redo log，它有以下优点：
- redo 日志占用的空间小。
- redo 日志按顺序将内容刷新到磁盘。
为了确保每次日志都写入重做日志文件，在每次将重做日志缓冲写入重做日志文件后，InnoDB 存储引擎都需要调用一次 fsync 操作。
总之，重做日志就是用来实现事务的持久性的。
官方文档：15.6.5 Redo Log
Why MYSQL still use fsync() to flush the data when the option is O_DIRECT?
2，undo
重做日志记录了事务的行为，可以很好地通过其对页进行 redo 操作。但是事务有时还需要进行回滚操作——保证事务原子性。
为了能实现回滚，就要求我们对一条记录做改动时（这里的改动 可以指INSERT 、DELETE 、UPDATE ），都需要留一手 ——把回滚时所需的信息都给记下来，比方说：
- 你插入一条记录时，至少要把这条记录的主键值记下来，之后回滚的时候只需要把这个主键值对应的记录删掉就好了。
- 你删除了一条记录，至少要把这条记录中的内容都记下来，这样之后回滚时再把由这些内容组成的记录插入到表中就好了。
- 你修改了一条记录，至少要把修改这条记录前的旧值都记录下来，这样之后回滚时再把这条记录更新为旧值就好了。
为了回滚而记录的这些信息称之为撤销日志，即 undo log。不同类型的操作产生的 undo log 的格式也是不同的。
当事务提交之后，undo log 并不能立马被删除，而是放入待清理的 list，由 purge 线程判断是否有其他事务在使用 undo 段中上一个事务之前的旧信息，再决定是否可以清理 undo log 空间。
（五）事务的实现之锁与 MVCC
开发多用户、数据库驱动的应用时，最大的一个难点是：一方面要最大程度地利用数据库的并发访问，另外一方面还要确保每个用户能以一致的方式读取和修改数据。为此就有了锁（locking）机制，同时这也是数据库系统区别于文件系统的一个关键特性。
正常情况下在多线程中可能通过显式加锁来保证临界数据资源的安全性，因此事务 ACID 的第三项特性可以由锁来完成，但实际上具体由底层的 MVCC 多版本控制机制实现。
版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 举报，一经查实，本站将立刻删除。
文章由极客之音整理，本文链接：https://www.bmabk.com/index.php/post/98051.html
