> For the complete documentation index, see [llms.txt](https://yangsx95.gitbook.io/notes/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://yangsx95.gitbook.io/notes/database/mysql.md). # Mysql ## 登录MySQL ### 首次登录 通过 `mysql -hlocalhost -P3306 -uroot -p` 进行登录，在Enter password：录入初始化密码 ### 修改密码 因为初始化密码默认是过期的，所以查看数据库会报错，使用如下命令修改密码： ```sql ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password'; ``` 5.7版本之后（不含5.7），mysql加入了全新的密码安全机制。设置新密码太简单会报错。 改为更复杂的密码规则之后，设置成功，可以正常使用数据库了： ### 设置远程登录 在用SQLyog或Navicat中配置远程连接Mysql数据库时遇到如下报错信息，这是由于Mysql配置了不支持远程连接引起的。 1. 在远程机器上使用ping ip地址 保证网络畅通 2. 在远程机器上使用telnet命令 保证端口号开放访问：`telnet xxx 3306` 3. 如果不通，有可能是防火墙导致的关闭防火墙，或者开放端口3306 4. 设置mysql远程访问权限： ```sql update user set host = '%' where user ='root'; ``` ## 密码强度安全策略 ### 不同版本设置密码 * MySQL5.7中：成功 ```mysql mysql> alter user 'root' identified by 'abcd1234'; Query OK, 0 rows affected (0.00 sec) ``` * MySQL8.0中：失败 ```sql mysql> alter user 'root' identified by 'abcd1234'; # HelloWorld_123 ERROR 1819 (HY000): Your password does not satisfy the current policy requirements ``` 这是因为MySQL的密码安全策略导致的。 ### MySQL8之前的密码安全策略 在MySQL 8.0之前，MySQL使用的是`validate_password`插件检测、验证账号密码强度，保障账号的安全性。 #### 启用插件：方式1 在参数文件`my.cnf`中添加参数： ```ini [mysqld] plugin-load-add=validate_password.so #ON/OFF/FORCE/FORCE_PLUS_PERMANENT: 是否使用该插件(及强制/永久强制使用) validate-password=FORCE_PLUS_PERMANENT ``` 1. `plugin library`中的`validate_password`文件名的后缀名根据平台不同有所差异。 对于Unix和 Unix-like系统而言，它的文件后缀名是`so`，对于Windows系统而言，它的文件后缀名是`dll`。 2. 修改参数后必须重启MySQL服务才能生效。 3. 参数`FORCE_PLUS_PERMANENT`是为了防止插件在MySQL运行时的时候被卸载。当你卸载插件时就会报错。如下所示: #### 启用插件：方式2 ```sql mysql> INSTALL PLUGIN validate_password SONAME 'validate_password.so'; Query OK, 0 rows affected, 1 warning (0.11 sec) ``` ### MySQL8的密码安全策略 #### validate\_password MySQL 8.0，引入了服务器组件（Components）这个特性，validate\_password插件已用服务器组件重新实现。8.0.25版本的数据库中，默认自动安装validate\_password组件。 未安装插件前，执行如下两个指令 ，执行效果： ```sql mysql> show variables like 'validate_password%'; Empty set (0.04 sec) mysql> SELECT * FROM mysql.component; ERROR 1146 (42S02): Table 'mysql.component' doesn't exist ``` 安装插件后，执行如下两个指令 ，执行效果： ```sql mysql> SELECT * FROM mysql.component; +--------------+--------------------+------------------------------------+ | component_id | component_group_id | component_urn | +--------------+--------------------+------------------------------------+ | 1 | 1 | file://component_validate_password | +--------------+--------------------+------------------------------------+ 1 row in set (0.00 sec) mysql> show variables like 'validate_password%'; +--------------------------------------+--------+ | Variable_name | Value | +--------------------------------------+--------+ | validate_password.check_user_name | ON | | validate_password.dictionary_file | | | validate_password.length | 8 | | validate_password.mixed_case_count | 1 | | validate_password.number_count | 1 | | validate_password.policy | MEDIUM | | validate_password.special_char_count | 1 | +--------------------------------------+--------+ 7 rows in set (0.01 sec) ``` 关于 validate\_password 组件对应的系统变量说明： | 选项 | 默认值 | 参数描述 | | ---------------------------------------- | ------ | ------------------------------------------------------------------------------------------------------------------------ | | validate\_password\_check\_user\_name | ON | 设置为ON的时候表示能将密码设置成当前用户名。 | | validate\_password\_dictionary\_file | | 用于检查密码的字典文件的路径名，默认为空。 | | validate\_password\_length | 8 | 密码的最小长度，也就是说密码长度必须大于或等于8 | | validate\_password\_mixed\_case\_count | 1 | 如果密码策略是中等或更强的，validate\_password要求密码具有的小写和大写字符的最小数量。对于给定的这个值密码必须有那么多小写字符和那么多大写字符。 | | validate\_password\_number\_count | 1 | 密码必须包含的数字个数 | | validate\_password\_policy | MEDIUM | 密码强度检验等级，可以使用数值0、1、2或相应的符号值`LOW`、`MEDIUM`、`STRONG`来指定。 `0/LOW` ：只检查长度。`1/MEDIUM` ：检查长度、数字、大小写、特殊字符。 `2/STRONG`：检查长度、数字、大 | | validate\_password\_special\_char\_count | 1 | 密码必须包含的特殊字符个数 | > 组件和插件的默认值可能有所不同。例如，MySQL 5.7. validate\_password\_check\_user\_name的默认值为OFF。 #### 修改安全策略 ```shell SET GLOBAL validate_password_policy=LOW; SET GLOBAL validate_password_policy=MEDIUM; SET GLOBAL validate_password_policy=STRONG; SET GLOBAL validate_password_policy=0; # For LOW SET GLOBAL validate_password_policy=1; # For MEDIUM SET GLOBAL validate_password_policy=2; # For HIGH # 注意，如果是插件的话,SQL为set global validate_password_policy=LOW ``` #### 密码强度测试 如果你创建密码是遇到“Your password does not satisfy the current policy requirements”，可以通过函数组件去检测密码是否满足条件： 0-100。当评估在100时就是说明使用上了最基本的规则：大写+小写+特殊字符+数字组成的8位以上密码 ```sql mysql> SELECT VALIDATE_PASSWORD_STRENGTH('medium'); +--------------------------------------+ | VALIDATE_PASSWORD_STRENGTH('medium') | +--------------------------------------+ | 25 | +--------------------------------------+ 1 row in set (0.00 sec) ``` ```sql mysql> SELECT VALIDATE_PASSWORD_STRENGTH('K354*45jKd5'); +-------------------------------------------+ | VALIDATE_PASSWORD_STRENGTH('K354*45jKd5') | +-------------------------------------------+ | 100 | +-------------------------------------------+ 1 row in set (0.00 sec) ``` \*\*注意：\*\*如果没有安装`validate_password`组件或插件的话，那么这个函数永远都返回`0`。 关于密码复杂度对应的密码复杂度策略。如下表格所示 | Password Test | Return Value | | ------------------------------------------ | ------------ | | Length < 4 | 0 | | Length ≥ 4 and < validate\_password.length | 25 | | Satisfies policy 1 (LOW) | 50 | | Satisfies policy 2 (MEDIUM) | 75 | | Satisfies policy 3 (STRONG) | 100 | ### 卸载插件/组件 卸载插件: ```sql mysql> UNINSTALL PLUGIN validate_password; Query OK, 0 rows affected, 1 warning (0.01 sec) ``` 卸载组件: ```sql mysql> UNINSTALL COMPONENT 'file://component_validate_password'; Query OK, 0 rows affected (0.02 sec) ``` ## SQL的大小写规范 1. 在 SQL 中，**关键字和函数名是不用区分字母大小写的**，比如 `SELECT`、`WHERE`、`ORDER`、`GROUP BY` 等关键字，以及 `ABS`、`MOD`、`ROUND`、`MAX` 等函数名。 2. 另外，列名（或字段名）与列的别名（或字段别名）在所有的情况下均是忽略大小写的。 3. 数据库名、表名、表的别名、变量名 则是根据当前的大小写配置决定的 ### Windows**和**Linux平台区别 不过在 SQL 中，你还是要确定大小写的规范，因为在 Linux 和 Windows 环境下，你可能会遇到不同的大小写问题。 **windows系统默认大小写不敏感 ，但是 linux系统是大小写敏感的** 。 **通过如下命令查看：** ```sql SHOW VARIABLES LIKE '%lower_case_table_names%' ``` **Windows系统下：默认不区分大小写** **Linux系统下：默认区分大小写** 其中，`lower_case_table_names`为0时，代表大小写敏感，所以MySQL在Linux下默认是区分大小写的。 ### 通过配置文件配置大小写规则 修改 my.cnf 配置文件： ```ini lower_case_table_names=1 ``` 并重启mysql服务器。 需要注意的是： 1. 在重启数据库实例之前就需要将原来的数据库和表转换为小写，否则将找不到数据库名 2. 此参数适用于MySQL5.7。在MySQL 8下禁止在重新启动 MySQL 服务时将 `lower_case_table_names`设置成不同于初始化 MySQL 服务时设置的`lower_case_table_names` 值。如果非要将MySQL8设置为大小写不敏感，具体步骤为： ``` 1、停止MySQL服务 2、删除数据目录，即删除 /var/lib/mysql 目录 3、在MySQL配置文件（ /etc/my.cnf ）中添加 lower_case_table_names=1 4、启动MySQL服务 ``` ### SQL编写建议 1. 关键字和函数名称全部大写； 2. 数据库名、表名、表别名、字段名、字段别名等全部小写； 3. SQL 语句必须以分号结尾。 ## 时区 修改Mysql的时区： ```shell vim /etc/mysql/mysql.conf.d/mysqld.cnf ``` 在 `[mysqld]` 下增加： ``` default-time-zone = '+08:00' ``` ## sql\_mode ### 宽松模式 如果设置的是宽松模式，那么我们在插入数据的时候，即便是给了一个错误的数据，也可能会被接受，并且不报错。 **举例** ：我在创建一个表时，该表中有一个字段为name，给name设置的字段类型时 char(10) ，如果我在插入数据的时候，其中name这个字段对应的有一条数据的 长度超过了10 ，例如'1234567890abc'，超过了设定的字段长度10，那么不会报错，并且取前10个字符存上，也就是说你这个数据被存为了'1234567890'，而'abc'就没有了。但是，我们给的这条数据是错误的，因为超过了字段长度，但是并没有报错，并且mysql自行处理并接受了，这就是宽松模式的效果。 **应用场景** ：通过设置sql mode为宽松模式，来保证大多数sql符合标准的sql语法，这样应用在不同数据库之间进行 迁移 时，则不需要对业务sql 进行较大的修改。 ### 严格模式 出现上面宽松模式的错误，应该报错才对，所以MySQL5.7版本就将sql\_mode默认值改为了严格模式。所以在 生产等环境 中，我们必须采用的是严格模式，进而 开发、测试环境 的数据库也必须要设置，这样在开发测试阶段就可以发现问题。并且我们即便是用MySQL5.6，也应该自行将其改为严格模式。 **开发经验** ：MySQL等数据库总想把关于数据的所有操作都自己包揽下来，包括数据的校验，其实开发中，我们应该在自己 开发的项目程序级别将这些校验给做了 ，虽然写项目的时候麻烦了一些步骤，但是这样做之后，我们在进行数据库迁移或者在项目的迁移时，就会方便很多。 \*\*改为严格模式后可能会存在的问题：\*\*若设置模式中包含了 NO\_ZERO\_DATE ，那么MySQL数据库不允许插入零日期，插入零日期会抛出错误而不是警告。例如，表中含字段TIMESTAMP列（如果未声明为NULL或显示DEFAULT子句）将自动分配`DEFAULT '0000-00-00 00:00:00'`（零时间戳），这显然是不满足sql\_mode中的NO\_ZERO\_DATE而报错。 ### 模式查看和设置 查看当前的sql\_mode： ```sql select @@session.sql_mode select @@global.sql_mode # 或者 show variables like 'sql_mode'; ``` 设置sql\_mode： 1. 临时设置 1. 当前会话级别 ```shell SET SESSION sql_mode = 'modes...'; #当前会话 # 举例 # 改为严格模式。此方法只在当前会话中生效，关闭当前会话就不生效了。 set SESSION sql_mode='STRICT_TRANS_TABLES'; ``` 2. 全局级别 ```shell SET GLOBAL sql_mode = 'modes...'; # 全局 # 举例 #改为严格模式。此方法在当前服务中生效，重启MySQL服务后失效。 set GLOBAL sql_mode='STRICT_TRANS_TABLES'; ``` 2. 永久设置： 1. 在my.cnf文件(windows系统是my.ini文件)，新增 ```ini [mysqld] sql_mode=ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION ``` 2. 重启MySQL。 3. 生产环境上是禁止重启MySQL服务的，所以采用 `临时设置方式 + 永久设置`方式 来解决线上的问题，那么即便是有一天真的重启了MySQL服务，也会永久生效了 ## 字符集 在MySQL 8.0版本之前，默认字符集为 latin1 ，utf8字符集指向的是 utf8mb3 。网站开发人员在数据库设计的时候往往会将编码修改为utf8字符集。如果遗忘修改默认的编码，就会出现乱码的问题。从MySQL 8.0开始，数据库的默认编码将改为 utf8mb4 ，从而避免上述乱码的问题。 ### 查看默认使用的字符集 ```sql show variables like 'character%'; # 或者 show variables like '%char%'; ``` * **MySQL8下：** * \*\*MySQL5.7下：\*\*默认的客户端和服务器都用了 latin1 ，如果向该字符的表添加中文将会发生错误。 ### 修改默认的字符集 ```shell vim /etc/my.cnf # 添加 character_set_server=utf8 ``` 然后重启mysql服务 ```shell systemctl restart mysqld ``` > 注意，原库原表的设定不会发生变化，修改只是修改默认值，新创建的表会使用新的默认字符编码 ### 已有库/表字符集的变更 MySQL5.7版本中，以前创建的库，创建的表字符集还是latin1。我们可以修改已经创建数据库的字符集： ```sql alter database dbtest1 character set 'utf8'; ``` 修改已经创建的表的字符集： ```sql alter table t_emp convert to character set 'utf8'; ``` 注意，这时，字符串数据有可能变为乱码，需要先将数据导出，然后重新迁移进去。 ### 各级别的字符集 * 执行 `show variables like 'character%';`： * `character_set_server`：服务器级别的字符集 * `character_set_database`：当前数据库的字符集 * `character_set_client`：服务器解码请求时使用的字符集 * `character_set_connection`：服务器处理请求时会把请求字符串从`character_set_client`转为`character_set_connection` * `character_set_results`：服务器向客户端返回数据时使用的字符集 #### 服务器级别 `character_set_server` ：服务器级别的字符集。 我们可以在启动服务器程序时通过启动选项或者在服务器程序运行过程中使用 SET 语句修改这两个变量的值。比如我们可以在配置文件中这样写： ```ini # 默认字符集 character_set_server=gbk # 对应的默认的比较规则 collation_server=gbk_chinese_ci ``` #### 数据库级别 `character_set_database` ：当前数据库的字符集 我们在创建和修改数据库的时候可以指定该数据库的字符集和比较规则，具体语法如下： ```sql CREATE DATABASE 数据库名 [[DEFAULT] CHARACTER SET 字符集名称] [[DEFAULT] COLLATE 比较规则名称]; ALTER DATABASE 数据库名 [[DEFAULT] CHARACTER SET 字符集名称] [[DEFAULT] COLLATE 比较规则名称]; ``` #### 表级别 我们也可以在创建和修改表的时候指定表的字符集和比较规则，语法如下： ```sql CREATE TABLE 表名 (列的信息) [[DEFAULT] CHARACTER SET 字符集名称] [COLLATE 比较规则名称]] ALTER TABLE 表名 [[DEFAULT] CHARACTER SET 字符集名称] [COLLATE 比较规则名称] ``` > 如果创建和修改表的语句中没有指明字符集和比较规则，将使用该表所在数据库的字符集和比较规则作为该表的字符集和比较规则。 #### 列级别 对于存储字符串的列，同一个表中的不同的列也可以有不同的字符集和比较规则。我们在创建和修改列定义的时候可以指定该列的字符集和比较规则，语法如下： ```sql CREATE TABLE 表名( 列名 字符串类型 [CHARACTER SET 字符集名称] [COLLATE 比较规则名称], 其他列... ); ALTER TABLE 表名 MODIFY 列名 字符串类型 [CHARACTER SET 字符集名称] [COLLATE 比较规则名称]; ``` > 对于某个列来说，如果在创建和修改的语句中没有指明字符集和比较规则，将使用该列所在表的字符集和比较规则作为该列的字符集和比较规则。 ## 其他命令 ### tee命令 作用：tee 命令会将在控制台的执行操作过程，包含结果，输出到一个文件中。 进入mysql，输入： ``` tee /home/mypath/myfile.txt ``` 你在控制台做的每个操作，都会记录到 `myfile.txt` 文件中 **通过启动参数带入tee：** ``` mysql -uroot -p --tee=/home/mysql/tmp/ceshi.log ``` **配置配置文件，自动启用tee:** ``` [client]port = 3306 socket = /usr/local/mysql/tmp/3306/mysql.sock default-character-set = utf8 tee = /home/mysql/tmp/result.log ``` ### \g、\G、分号的区别 \g 的作用是分号和在sql语句中写’;’是等效的 \G 的作用是将查到的结构旋转90度变成纵向 ```sql mysql> create table mytable(id int)\g Query OK, 0 rows affected (0.21 sec) mysql> show create table mytable \g +---------+-------------------------------------------------------------------------------------------+ | Table | Create Table | +---------+-------------------------------------------------------------------------------------------+ | mytable | CREATE TABLE `mytable` ( `id` int(11) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 | +---------+-------------------------------------------------------------------------------------------+ 1 row in set (0.04 sec) ``` 列明过多，不好观察： ``` mysql> show create table mytable \G *************************** 1. row *************************** Table: mytable Create Table: CREATE TABLE `mytable` ( `id` int(11) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec) ``` ## 系统数据库 ### 查看mysql系统数据库 ```sql mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | +--------------------+ 6 rows in set (0.00 sec) ``` 共有四张默认就存在的数据库，他们分别是： 1. `mysql`： MySQL 系统自带的核心数据库，它存储了MySQL的用户账户和权限信息，一些存储过程、事件的定义信息，一些运行过程中产生的日志信息，一些帮助信息以及时区信息等。 2. `information_schema`：MySQL系统自带的数据库，这个数据库库保存着MySQL服务器 维护的所有`其他数据库的信息` ，比如有哪些表、哪些视图、哪些触发器、哪些列、哪些索引。这些信息并不是真实的用户数据，而是一些描述性信息，有时候也称之为 元数据。在系统数据库 `information_schema` 中提供了一些以 `innodb_sys` 开头的表，用于表示内部系统表。 3. `performance_schema`，MySQL 系统自带的数据库，这个数据库里主要保存MySQL服务器运行过程中的一些`状态信息`，可以用来`监控 MySQL 服务的各类性能指标`。包括统计最近执行了哪些语句，在执行过程的每个阶段都花费了多长时间，内存的使用情况等信 息。 4. `sys`，MySQL 系统自带的数据库，这个数据库主要是通过 视图 的形式把`information_schema`和`performance_schema`结合起来，帮助系统管理员和开发人员监控 MySQL 的技术性能。 ## MySQL的数据目录 ### 数据库的主要目录结构 ```shell find / -name mysql ``` 1. 数据文件存放路径 可通过命令 `show variables like 'datadir';` 查询，默认在 `/var/lib/mysql/` 下 2. 相关命令存放路径 主要有 `/usr/bin` 以及 `/usr/sbin` 包含`mysqladmin`、`mysqlbinlog`、`mysqldump`等可执行文件 3. 配置文件存放路径：`/usr/share/mysql-8.0` ### 数据库在文件系统中的表示 ```shell sh-4.4# pwd /var/lib/mysql sh-4.4# ls -al total 195096 -rw-r----- 1 mysql mysql 196608 Feb 22 04:45 '#ib_16384_0.dblwr' -rw-r----- 1 mysql mysql 8585216 Feb 21 05:22 '#ib_16384_1.dblwr' drwxr-x--- 2 mysql mysql 4096 Feb 21 05:22 '#innodb_temp' drwxrwx--- 1 mysql mysql 4096 Feb 21 06:43 . drwxr-xr-x 1 root root 4096 Jan 19 13:35 .. -rw-r----- 1 mysql mysql 56 Feb 21 05:22 auto.cnf -rw-r----- 1 mysql mysql 180 Feb 21 05:22 binlog.000001 -rw-r----- 1 mysql mysql 51102 Feb 22 04:44 binlog.000002 -rw-r----- 1 mysql mysql 32 Feb 21 05:22 binlog.index -rw------- 1 mysql mysql 1680 Feb 21 05:22 ca-key.pem -rw-r--r-- 1 mysql mysql 1112 Feb 21 05:22 ca.pem -rw-r--r-- 1 mysql mysql 1112 Feb 21 05:22 client-cert.pem -rw------- 1 mysql mysql 1680 Feb 21 05:22 client-key.pem drwxr-x--- 2 mysql mysql 4096 Feb 22 04:44 exercise # 自己创建的exercise数据库 -rw-r----- 1 mysql mysql 5610 Feb 21 05:22 ib_buffer_pool -rw-r----- 1 mysql mysql 50331648 Feb 22 04:45 ib_logfile0 -rw-r----- 1 mysql mysql 50331648 Feb 21 05:22 ib_logfile1 -rw-r----- 1 mysql mysql 12582912 Feb 22 04:44 ibdata1 -rw-r----- 1 mysql mysql 12582912 Feb 21 05:22 ibtmp1 drwxr-x--- 2 mysql mysql 4096 Feb 21 05:22 mysql # 数据库 mysql 对应的目录 -rw-r----- 1 mysql mysql 31457280 Feb 22 04:44 mysql.ibd srwxrwxrwx 1 mysql mysql 0 Feb 21 05:22 mysql.sock -rw------- 1 mysql mysql 2 Feb 21 05:22 mysql.sock.lock drwxr-x--- 2 mysql mysql 4096 Feb 21 05:22 performance_schema # 数据库 performance_schema 对应的目录 -rw------- 1 mysql mysql 1680 Feb 21 05:22 private_key.pem -rw-r--r-- 1 mysql mysql 452 Feb 21 05:22 public_key.pem -rw-r--r-- 1 mysql mysql 1112 Feb 21 05:22 server-cert.pem -rw------- 1 mysql mysql 1676 Feb 21 05:22 server-key.pem drwxr-x--- 2 mysql mysql 4096 Feb 21 05:22 sys # 数据库sys对应的目录 drwxr-x--- 2 mysql mysql 4096 Feb 21 05:34 test # 自己创建的test数据库 -rw-r----- 1 mysql mysql 16777216 Feb 22 04:45 undo_001 -rw-r----- 1 mysql mysql 16777216 Feb 22 04:45 undo_002 ``` > 除了`information_schema`数据库外，其他的任何一个库都会在这个目录下对应一个文件夹。