conanan's blog
conanan
2021-01-06

MySQL

# MySQL 运维

# MySQL 安装

可通过安装程序、Docker 等安装

可参考：

# 重要文件

# 文件夹

  • C:\Program Files\MySQL\MySQL Server 8.0：DBMS管理程序

  • C:\ProgramData\MySQL\MySQL Server 8.0\data：DBMS数据库文件（卸载MySQL时不会删除）

    每个目录表示一个数据库，MySQl8的每个数据库目录下会有0~N个扩展名为ibd的table文件

同样的，5.7版本的如下：

  • C:\Program Files\MySQL\MySQL Server 5.7

  • C:\ProgramData\MySQL\MySQL Server 5.7\Data

# bin目录下的可执行文件

如下为5.7的

.
├── mysql.exe
├── mysqladmin.exe
├── mysqlbinlog.exe
├── mysqlcheck.exe
├── mysqld.exe
├── mysqldump.exe
├── mysqlimport.exe
├── mysqlpump.exe
... (省略其他文件)
0 directories, 32 files
1
2
3
4
5
6
7
8
9
10
11

可以将该bin目录的路径加入到环境变量Path中

  • C:\Program Files\MySQL\MySQL Server 8.0\bin\ mysqld.exe：服务器程序，必须先启动它

  • C:\Program Files\MySQL\MySQL Server 8.0\bin\ mysql.exe：客户端程序操作服务器，服务器需先开启

    mysqld 是 MySQL 的主程序，服务器端。mysql 是 MySQL 的命令行工具，客户端。

# 其他文件

  • C:\ProgramData\MySQL\MySQL Server 8.0\ my.ini：服务器配置文件，之前版本放在安装目录中bin下

    • 配置MySQL的端口：默认为3306（一般不建议修改，本电脑MySQL5为3306、MySQL8为3308端口）
    • 配置字符编码：（一般不建议修改）
      • [mysql]下配置默认客户端编码：default-character-set=gbk
      • [mysqld]下配置默认服务器编码：character-set-server=utf8
    • 配置二进制数据大小上限：（一般不建议修改）
      • 在[mysqld]下配置：max_allowed_packet=8M

# 服务器程序和客户端程序

# 服务器程序

我的服务名称为mysql8

通过服务启动

  • 开启服务器(必须保证mysql为windows服务)：net start mysql8，mysqld.exe进程存在
  • 关闭服务器(必须保证mysql为windows服务)：net stop mysql8，mysqld.exe进程不存在

当然也可以通过 mysqld 启动

在使用mysqld可执行文件启动MySQL服务器程序时，它默认并不会将路径设置为数据目录的路径，这时就需要我们在启动MySQL服务器时显式地指定一个称之为datadir的参数，该参数就代表着数据目录的路径，所以使用下边的命令启动MySQL服务器就不会报错了：

mysqld --datadir="C:\ProgramData\MySQL\MySQL Server 5.7\Data"
1

如果我们想查看在使用mysqld可执行文件启动MySQL服务器的过程中输出的日志信息，可以在上述命令后边加上--console，就像这样：

mysqld --datadir="C:\ProgramData\MySQL\MySQL Server 5.7\Data" --console
1

# 客户端操作

在服务器程序启动之后（不管是使用mysqld可执行文件启动的还是使用Windows服务的方式启动的），就可以接着启动客户端程序来连接到这个服务器喽。bin目录下有许多客户端程序，比方说mysqladmin、mysqldump、mysqlcheck等等等等（好多呢，就不一一列举了）。这里我们重点要关注的是可执行文件mysql，通过这个可执行文件可以让我们和服务器程序进程交互，也就是发送请求以及接收服务器的处理结果。启动这个可执行文件时一般需要一些参数，格式如下：

mysql -h主机名  -u用户名 -p密码
1

各个参数的意义如下：

参数名 含义
-h 表示启动服务器程序的计算机的域名或者IP地址，如果服务器程序就运行在本机的话，可以省略这个参数，也可以填localhost或者127.0.0.1。也可以写作 --host=主机的形式。
-P 大写的P，端口号，不写默认为3306
-u 表示用户名，我们刚刚安装完，作为超级管理员的我们的用户名是root。也可以写作 --user=用户名的形式。
-p 表示密码。也可以写作 --password=密码的形式。

比如我这样执行下边这个可执行文件(用户名密码按你的实际情况填写)，就可以启动MySQL客户端，并且连接到服务器了。

mysql -hlocalhost -uroot -p123456
1

也可以这么写：

mysql --host=localhost --user=root --password=123456
1

我们看一下连接成功后的界面：

mysql: [Warning] Using a password on the command line interface can be insecure.

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.27 MySQL Community Server (GPL)

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

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>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

最后一行的mysql>是一个客户端的提示符，之后客户端发送给服务器的命令都需要写在这个提示符后边。

如果我们想断开客户端与服务器的连接并且关闭客户端的话，可以在mysql>提示符后输入下边任意一个命令：

  1. quit
  2. exit
  3. \q

比如我们输入quit试试：

mysql> quit
Bye
1
2

输出了Bye说明客户端程序已经关掉了。注意注意注意，这是关闭客户端程序的方式，不是关闭服务器程序的方式

如果你愿意，你可以多打开几个黑框框，每个黑框框都使用可以执行mysql -hlocalhost -uroot -p123456命令，从而达到运行多个客户端程序的效果，每个客户端程序都是互不影响的。如果你有多个电脑，也可以试试把它们用局域网连起来，在一个电脑上启动MySQL服务器程序，在另一个电脑上执行mysql命令时使用IP地址作为主机名来连接到服务器。

# 连接注意事项

  • 最好不要在一行命令中输入密码。

    我们直接在黑框框里把密码敲出去被别人看到咋办，这和你当别人的面输入银行卡密码没啥区别，所以我们在执行mysql连接服务器的时候可以不显式的写出密码，就像这样：

    mysql -hlocalhost -uroot -p
    1

    点击回车之后才会提示你输入密码：

    Enter password:
    1

    不过这回你输入的密码不会被显示出来，心怀不轨的人也就看不到了，输入完成点击回车就成功连接到了服务器。

  • 如果你非要在显式的把密码输出来，那密码值和-p之间不能有空白字符，其他参数名之间可以有空白字符，就像这样：

    mysql -h localhost -u root -p123456
    1

    如果加上了空白字符就是错误的，比如这样：

    mysql -h localhost -u root -p 123456
    1

  • mysql的各个参数的摆放顺序没有硬性规定，也就是说你也可以这么写：

    mysql -p  -u root -h localhost
    1

  • 如果你的服务器和客户端安装在同一台机器上，-h参数可以省略，就像这样：

    mysql -u root -p
    1

  • 如果你使用的是类Unix系统，并且省略-u参数后，会把你登陆操作系统的用户名当作MySQL的用户名去处理。

    比方说我用登录操作系统的用户名是xiaohaizi，那么在我的机器上下边这两条命令是等价的：

    mysql -u xiaohaizi -p
mysql -p
    1
    2

    对于Windows系统来说，默认的用户名是ODBC，你可以通过设置环境变量USER来添加一个默认用户名。

# 参数设置

# 启动选项

  • 命令行
  • 配置文件中
  • 区别

# 系统变量

MySQL服务器在运行过程中会用到的影响程序行为的变量，如允许同事连入的客户端数量max_connections等

  • 查看系统变量

    SHOW VARIABLES [LIKE 匹配的模式];
    1
    SHOW VARIABLES LIKE '%character%';
    1

  • 启动项设置系统变量

  • 服务器运行过程中设置系统变量

    SET [GLOBAL | SESSION] 系统变量名 = 值;
SET [@@(GLOBAL | SESSION).]系统变量名 = 值;
    1
    2

# 状态变量

如 Threads_connected 表示当前有多少客户端与服务器建立了连接

  • 查看状态变量

    SHOW [GLOBAL | SESSION] STATUS [LIKE 匹配的模式];
    1

  • 不能设置哦

# 字符集和乱码问题

# 客户端和服务端通信过程中使用的字符集

# 1、客户端发送请求

  • 类 UNIX 系统根据 LC_ALL > LC_CTYPE > LANG 三个环境变量来决定使用哪个字符集，优先级按照从大到小排序

    可通过 echo $LC_ALL 等查看。底层调用系统函数 nl_langinfo(CODESET)

  • Win 根据代码页（code page）决定，一个代码页与一个唯一的数字关联。如 936 代表 GBK 字符集；65001 代表 UTF-8 字符集。在 win 命令行窗口即可看到代码页，cmd 默认为 936 即 GBK，也可以执行chcp命令查看。底层调用系统函数GetConsoleCP

    但是在启动 mysql 客户端时若携带了 default-character-set启动项，则 mysql 客户端会按照该启动项指定的字符集进行编码（启动项不适用于类 UNIX）

# 2、服务端接收请求

服务器接收到字节序列，并将其看作是使用系统变量character_set_client代表的字符集进行编码的字节序列。该系统变量是每个客户端与服务器建立连接后，服务器都会为该客户端维护一个单独的SESSION级别的character_set_client变量。

也就是说客户端实际使用的字符集与服务端认为该客户端发送的字节序列使用的编码字符集是独立不同的。一般情况下，需要保持这两个字符集一致！

可以使用SET character_set_client = ascii命令修改，当其不能解析请求的字节序列时，服务器会发出警告。如客户端实际使用 UTF-8，而 character_set_client 为 ASCII：

mysql> select '我';
+---------+
|   ???   |
+---------+
|   ???   |
+---------+
1 row in set, 1 warning (0.00 sec)

mysql> show warnings\G
**************** 1. row ****************
  Level: Warning
   Code: 1300
Message: Invalid ascii character string: '\xE6\x88\x91'
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14

上述可以看出，"我"对应的 UTF-8 编码"0xE68891"无法通过 ASCII 来解码

# 3、服务端处理请求

虽然服务器会将请求的字节序列当做采用 character_set_client 对应的字符集进行编码的字节序列，但是在真正处理请求时会将其转换为使用 SESSION 级别的系统变量 character_set_connection对应的字符集进行编码的字节序列。同样可以使用SET character_set_connection = gbk命令修改

这个主要用于比较、排序（配合 collation_connection 校对集），比如是否与大小相关等

mysql> select 'a' = 'A';
+---------+
|'a' = 'A'|
+---------+
|1        |
+---------+
1
2
3
4
5
6

并且列的字符集和排序规则优于上述系统变量设置的

# 4、服务端生成响应

服务器在查询完数据后，会将期转换为character_set_result系统变量对应的字符集编码后的字节序列，然后发送给客户端。当然也可以通过SET character_set_result = gbk来修改

# 5、客户端接收响应

  • 类UNIX系统会根据系统默认字符集来解析
  • Win系统会根据命令行如cmd（默认为GBK）来解析，当然还会根据default-character-set启动项解析

# 三个系统变量总结

系统变量 描述
character_set_client 服务器认为请求是按照该系统变量指定的字符集进行编码的，实际可能不是
character_set_connection 服务器在处理请求时，会把请求字节序列从character_set_client 转换为 character_set_connection，用于比较排序等
character_set_result 服务器采用该系统变量指定的字符集对返回给客户端的字符串进行编码

上述三个系统变量的作用范围都是 SESSION 级别。

每个mysql客户端都维护着一个客户端默认字符集，客户端在启动时自动检测所在操作系统当前使用的字符集（环境变量等），并按照一定规则映射成mysql支持的字符集，然后将该字符集作为客户端默认的字符集。特殊情况是操作系统使用 ascii 字符集时会被映射为 latin1 字符集。且当mysql 不支持操作系统当前使用的字符集时，会设置为mysql 默认的字符集。MySQL5.7以及之前的版本中，默认字符集为 latin1，自8.0版本开始改为 utf8mb4。当然启动客户端时设置了default-character-set启动选项，则按该值作为字符集。

在连接服务器时，客户端将默认的字符集信息与用户名密码等信息一起发送给服务器，服务器在收到后会将上述3个系统变量的值初始化为客户端的默认字符集。

连接成功后，可通过 set 命令一个个修改，也可以通过set names gbk来一次性全部修改（没有"="哦）

# 校对集—排序规则

一个字符集可以有若干种比较规则，其中有一个默认的比较规则。一个比较规则必须对应一个字符集。

创建数据库、表时需要填写的校对集（排序规则）

CREATE TABLE `demo`.`无标题`  (
  `name` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `value` int NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
1
2
3
4

# 常用命令总结

# 查看支持的字符集

SHOW (CHARACTER SET | CHARSET) [LIKE 匹配的模式]
1
mysql> show character set;
+----------+---------------------------------+---------------------+--------+
| Charset  | Description                     | Default collation   | Maxlen |
+----------+---------------------------------+---------------------+--------+
| armscii8 | ARMSCII-8 Armenian              | armscii8_general_ci |      1 |
| ascii    | US ASCII                        | ascii_general_ci    |      1 |
| big5     | Big5 Traditional Chinese        | big5_chinese_ci     |      2 |
| binary   | Binary pseudo charset           | binary              |      1 |
| cp1250   | Windows Central European        | cp1250_general_ci   |      1 |
| cp1251   | Windows Cyrillic                | cp1251_general_ci   |      1 |
| cp1256   | Windows Arabic                  | cp1256_general_ci   |      1 |
| cp1257   | Windows Baltic                  | cp1257_general_ci   |      1 |
| cp850    | DOS West European               | cp850_general_ci    |      1 |
| cp852    | DOS Central European            | cp852_general_ci    |      1 |
| cp866    | DOS Russian                     | cp866_general_ci    |      1 |
| cp932    | SJIS for Windows Japanese       | cp932_japanese_ci   |      2 |
| dec8     | DEC West European               | dec8_swedish_ci     |      1 |
| eucjpms  | UJIS for Windows Japanese       | eucjpms_japanese_ci |      3 |
| euckr    | EUC-KR Korean                   | euckr_korean_ci     |      2 |
| gb18030  | China National Standard GB18030 | gb18030_chinese_ci  |      4 |
| gb2312   | GB2312 Simplified Chinese       | gb2312_chinese_ci   |      2 |
| gbk      | GBK Simplified Chinese          | gbk_chinese_ci      |      2 |
| geostd8  | GEOSTD8 Georgian                | geostd8_general_ci  |      1 |
| greek    | ISO 8859-7 Greek                | greek_general_ci    |      1 |
| hebrew   | ISO 8859-8 Hebrew               | hebrew_general_ci   |      1 |
| hp8      | HP West European                | hp8_english_ci      |      1 |
| keybcs2  | DOS Kamenicky Czech-Slovak      | keybcs2_general_ci  |      1 |
| koi8r    | KOI8-R Relcom Russian           | koi8r_general_ci    |      1 |
| koi8u    | KOI8-U Ukrainian                | koi8u_general_ci    |      1 |
| latin1   | cp1252 West European            | latin1_swedish_ci   |      1 |
| latin2   | ISO 8859-2 Central European     | latin2_general_ci   |      1 |
| latin5   | ISO 8859-9 Turkish              | latin5_turkish_ci   |      1 |
| latin7   | ISO 8859-13 Baltic              | latin7_general_ci   |      1 |
| macce    | Mac Central European            | macce_general_ci    |      1 |
| macroman | Mac West European               | macroman_general_ci |      1 |
| sjis     | Shift-JIS Japanese              | sjis_japanese_ci    |      2 |
| swe7     | 7bit Swedish                    | swe7_swedish_ci     |      1 |
| tis620   | TIS620 Thai                     | tis620_thai_ci      |      1 |
| ucs2     | UCS-2 Unicode                   | ucs2_general_ci     |      2 |
| ujis     | EUC-JP Japanese                 | ujis_japanese_ci    |      3 |
| utf16    | UTF-16 Unicode                  | utf16_general_ci    |      4 |
| utf16le  | UTF-16LE Unicode                | utf16le_general_ci  |      4 |
| utf32    | UTF-32 Unicode                  | utf32_general_ci    |      4 |
| utf8     | UTF-8 Unicode                   | utf8_general_ci     |      3 |
| utf8mb4  | UTF-8 Unicode                   | utf8mb4_0900_ai_ci  |      4 |
+----------+---------------------------------+---------------------+--------+
41 rows in set (0.10 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47

# 查看支持的比较规则

SHOW COLLATION [LIKE 匹配的模式]
1

太多了，自己看吧

# 查询系统各个属性字符集 🔥

mysql> show variables like '%character%';
+------------------------+------------------------------+
|Variable_name           |Value                         |
+------------------------+------------------------------+
|character_set_client    |utf8mb4                       |
|character_set_connection|utf8mb4                       |
|character_set_database  |utf8mb4                       |
|character_set_filesystem|binary                        |
|character_set_results   |utf8mb4                       |
|character_set_server    |utf8mb4                       |
|character_set_system    |utf8                          |
|character_sets_dir      |/usr/share/mysql-8.0/charsets/|
+------------------------+------------------------------+
8 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14

上面的代码显示是绝对可以保证不乱码。但是一旦遇到如下：

mysql> show variables like '%character%';
+------------------------+------------------------------+
|Variable_name           |Value                         |
+------------------------+------------------------------+
|character_set_client    |gbk                           |
|character_set_connection|gbk                           |
|character_set_database  |utf8mb4                       |
|character_set_filesystem|binary                        |
|character_set_results   |gbk                           |
|character_set_server    |utf8mb4                       |
|character_set_system    |utf8                          |
|character_sets_dir      |/usr/share/mysql-8.0/charsets/|
+------------------------+------------------------------+
8 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
  • 客户端即 character_set_client 是 gbk编码
  • 服务端即 character_set_server 是 utf8mb4
  • 返回结果即 character_set_results gbk
  • 中间连接器即 character_set_connection 是 gbk。这个用来统一转换客户端和服务端的字节编码，进行比较（collation_connection）

只要 character_set_client 和 character_set_results 一致，就基本不会乱码。整个流程如下：

客户端 insert “男哥”，此时是 gbk，到达连接器时 转为 服务端需要的 utf8mb4 并存储。连接器只要不是选择特别小的字符集（如 ASCII 码）即可保证不会丢失数据，也会保证转换正常。

客户端查询时，需要返回的是 gbk 和 客户端编码（cmd默认为gbk）一致，所以不会乱码。

# 修改字符集、比较规则 🔥

需要修改字符集只需执行如下操作即可

mysql> set character_set_client=gbk
1

若是要同时修改则执行如下操作

mysql> set names gbk;
1

修改比较规则略。还是推荐图形化修改吧

# 创建表时需要填写的表字符集

CREATE TABLE `demo`.`无标题`  (
  `name` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `value` int NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
1
2
3
4
编辑
上次更新: 2021/02/02, 16:04:51
InnoDB
Oracle

← Oracle→

最近更新
01
线程生命周期
 07-06
02
线程安全理论
 06-24
03
并发简史
 06-24
更多文章>
Theme by Vdoing | Copyright © 2019-2021 Evan Xu | MIT License
×