Mysql

登录MySQL

首次登录

通过 mysql -hlocalhost -P3306 -uroot -p 进行登录，在Enter password：录入初始化密码

修改密码

因为初始化密码默认是过期的，所以查看数据库会报错，使用如下命令修改密码：

ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password';

5.7版本之后（不含5.7），mysql加入了全新的密码安全机制。设置新密码太简单会报错。

改为更复杂的密码规则之后，设置成功，可以正常使用数据库了：

设置远程登录

在用SQLyog或Navicat中配置远程连接Mysql数据库时遇到如下报错信息，这是由于Mysql配置了不支持远程连接引起的。

在远程机器上使用ping ip地址保证网络畅通
在远程机器上使用telnet命令保证端口号开放访问：telnet xxx 3306
如果不通，有可能是防火墙导致的关闭防火墙，或者开放端口3306

设置mysql远程访问权限：

update user set host = '%' where user ='root';

密码强度安全策略

不同版本设置密码

MySQL5.7中：成功

mysql> alter user 'root' identified by 'abcd1234'; 
Query OK, 0 rows affected (0.00 sec)

MySQL8.0中：失败

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中添加参数：

[mysqld] 

plugin-load-add=validate_password.so 
#ON/OFF/FORCE/FORCE_PLUS_PERMANENT: 是否使用该插件(及强制/永久强制使用) 
validate-password=FORCE_PLUS_PERMANENT

plugin library中的validate_password文件名的后缀名根据平台不同有所差异。对于Unix和 Unix-like系统而言，它的文件后缀名是so，对于Windows系统而言，它的文件后缀名是dll。
修改参数后必须重启MySQL服务才能生效。
参数FORCE_PLUS_PERMANENT是为了防止插件在MySQL运行时的时候被卸载。当你卸载插件时就会报错。如下所示:

启用插件：方式2

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组件。

未安装插件前，执行如下两个指令，执行效果：

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

安装插件后，执行如下两个指令，执行效果：

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	密码必须包含的特殊字符个数

validate_password_check_user_name

设置为ON的时候表示能将密码设置成当前用户名。

validate_password_dictionary_file

用于检查密码的字典文件的路径名，默认为空。

validate_password_length

密码的最小长度，也就是说密码长度必须大于或等于8

validate_password_mixed_case_count

如果密码策略是中等或更强的，validate_password要求密码具有的小写和大写字符的最小数量。对于给定的这个值密码必须有那么多小写字符和那么多大写字符。

validate_password_number_count

密码必须包含的数字个数

validate_password_policy

MEDIUM

密码强度检验等级，可以使用数值0、1、2或相应的符号值LOW、MEDIUM、STRONG来指定。 0/LOW ：只检查长度。1/MEDIUM ：检查长度、数字、大小写、特殊字符。 2/STRONG：检查长度、数字、大

validate_password_special_char_count

密码必须包含的特殊字符个数

组件和插件的默认值可能有所不同。例如，MySQL 5.7. validate_password_check_user_name的默认值为OFF。

修改安全策略

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位以上密码

mysql> SELECT VALIDATE_PASSWORD_STRENGTH('medium');
+--------------------------------------+
| VALIDATE_PASSWORD_STRENGTH('medium') |
+--------------------------------------+
| 25 |
+--------------------------------------+
1 row in set (0.00 sec)

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

卸载插件/组件

卸载插件:

mysql> UNINSTALL PLUGIN validate_password; 
Query OK, 0 rows affected, 1 warning (0.01 sec)

卸载组件:

mysql> UNINSTALL COMPONENT 'file://component_validate_password'; 
Query OK, 0 rows affected (0.02 sec)

SQL的大小写规范

在 SQL 中，关键字和函数名是不用区分字母大小写的，比如 SELECT、WHERE、ORDER、GROUP BY 等关键字，以及 ABS、MOD、ROUND、MAX 等函数名。
另外，列名（或字段名）与列的别名（或字段别名）在所有的情况下均是忽略大小写的。
数据库名、表名、表的别名、变量名则是根据当前的大小写配置决定的

Windows和Linux平台区别

不过在 SQL 中，你还是要确定大小写的规范，因为在 Linux 和 Windows 环境下，你可能会遇到不同的大小写问题。 windows系统默认大小写不敏感，但是 linux系统是大小写敏感的 。

通过如下命令查看：

SHOW VARIABLES LIKE '%lower_case_table_names%'

Windows系统下：默认不区分大小写

Linux系统下：默认区分大小写

其中，lower_case_table_names为0时，代表大小写敏感，所以MySQL在Linux下默认是区分大小写的。

通过配置文件配置大小写规则

修改 my.cnf 配置文件：

lower_case_table_names=1

并重启mysql服务器。

需要注意的是：

在重启数据库实例之前就需要将原来的数据库和表转换为小写，否则将找不到数据库名
此参数适用于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编写建议

关键字和函数名称全部大写；
数据库名、表名、表别名、字段名、字段别名等全部小写；
SQL 语句必须以分号结尾。

时区

修改Mysql的时区：

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：

select @@session.sql_mode
select @@global.sql_mode
# 或者
show variables like 'sql_mode';

设置sql_mode：

临时设置

当前会话级别

SET SESSION sql_mode = 'modes...'; #当前会话

# 举例
# 改为严格模式。此方法只在当前会话中生效，关闭当前会话就不生效了。 
set SESSION sql_mode='STRICT_TRANS_TABLES';

全局级别

SET GLOBAL sql_mode = 'modes...';  # 全局

# 举例
#改为严格模式。此方法在当前服务中生效，重启MySQL服务后失效。 
set GLOBAL sql_mode='STRICT_TRANS_TABLES';

永久设置：

在my.cnf文件(windows系统是my.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

重启MySQL。

生产环境上是禁止重启MySQL服务的，所以采用 临时设置方式 + 永久设置方式来解决线上的问题，那么即便是有一天真的重启了MySQL服务，也会永久生效了

字符集

在MySQL 8.0版本之前，默认字符集为 latin1 ，utf8字符集指向的是 utf8mb3 。网站开发人员在数据库设计的时候往往会将编码修改为utf8字符集。如果遗忘修改默认的编码，就会出现乱码的问题。从MySQL 8.0开始，数据库的默认编码将改为 utf8mb4 ，从而避免上述乱码的问题。

查看默认使用的字符集

show variables like 'character%'; 
# 或者 
show variables like '%char%';

MySQL8下：
**MySQL5.7下：**默认的客户端和服务器都用了 latin1 ，如果向该字符的表添加中文将会发生错误。

修改默认的字符集

vim /etc/my.cnf
# 添加
character_set_server=utf8

然后重启mysql服务

systemctl restart mysqld

注意，原库原表的设定不会发生变化，修改只是修改默认值，新创建的表会使用新的默认字符编码

已有库/表字符集的变更

MySQL5.7版本中，以前创建的库，创建的表字符集还是latin1。我们可以修改已经创建数据库的字符集：

alter database dbtest1 character set 'utf8';

修改已经创建的表的字符集：

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 语句修改这两个变量的值。比如我们可以在配置文件中这样写：

# 默认字符集 
character_set_server=gbk 
# 对应的默认的比较规则
collation_server=gbk_chinese_ci

数据库级别

character_set_database ：当前数据库的字符集

我们在创建和修改数据库的时候可以指定该数据库的字符集和比较规则，具体语法如下：

CREATE DATABASE 数据库名 
  [[DEFAULT] CHARACTER SET 字符集名称] 
  [[DEFAULT] COLLATE 比较规则名称]; 
ALTER DATABASE 数据库名 
  [[DEFAULT] CHARACTER SET 字符集名称] 
  [[DEFAULT] COLLATE 比较规则名称];

表级别

我们也可以在创建和修改表的时候指定表的字符集和比较规则，语法如下：

CREATE TABLE 表名 (列的信息) 
  [[DEFAULT] CHARACTER SET 字符集名称] 
  [COLLATE 比较规则名称]] 
ALTER TABLE 表名 
  [[DEFAULT] CHARACTER SET 字符集名称] 
  [COLLATE 比较规则名称]

如果创建和修改表的语句中没有指明字符集和比较规则，将使用该表所在数据库的字符集和比较规则作为该表的字符集和比较规则。

列级别

对于存储字符串的列，同一个表中的不同的列也可以有不同的字符集和比较规则。我们在创建和修改列定义的时候可以指定该列的字符集和比较规则，语法如下：

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度变成纵向

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系统数据库

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
6 rows in set (0.00 sec)

共有四张默认就存在的数据库，他们分别是：

mysql： MySQL 系统自带的核心数据库，它存储了MySQL的用户账户和权限信息，一些存储过程、事件的定义信息，一些运行过程中产生的日志信息，一些帮助信息以及时区信息等。
information_schema：MySQL系统自带的数据库，这个数据库库保存着MySQL服务器维护的所有其他数据库的信息 ，比如有哪些表、哪些视图、哪些触发器、哪些列、哪些索引。这些信息并不是真实的用户数据，而是一些描述性信息，有时候也称之为元数据。在系统数据库 information_schema 中提供了一些以 innodb_sys 开头的表，用于表示内部系统表。
performance_schema，MySQL 系统自带的数据库，这个数据库里主要保存MySQL服务器运行过程中的一些状态信息，可以用来监控 MySQL 服务的各类性能指标。包括统计最近执行了哪些语句，在执行过程的每个阶段都花费了多长时间，内存的使用情况等信息。
sys，MySQL 系统自带的数据库，这个数据库主要是通过视图的形式把information_schema和performance_schema结合起来，帮助系统管理员和开发人员监控 MySQL 的技术性能。

MySQL的数据目录

数据库的主要目录结构

find / -name mysql

数据文件存放路径
可通过命令 show variables like 'datadir'; 查询，默认在 /var/lib/mysql/ 下
相关命令存放路径
主要有 /usr/bin 以及 /usr/sbin
包含mysqladmin、mysqlbinlog、mysqldump等可执行文件
配置文件存放路径：/usr/share/mysql-8.0

数据库在文件系统中的表示

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数据库外，其他的任何一个库都会在这个目录下对应一个文件夹。

上一页IBM DB2 下一页安装Mysql

最后更新于1年前