IPv6 for MySQL

MySQL对IPv6的支持包括这些功能:
.MySQL服务器可以接受客户端通过IPv6进行TCP/IP连接。例如下面的命令是在本机通过IPv6来连接MySQL服务器:

[mysql@localhost ~]$ mysql -h ::1 -uusername -ppassword

要使用这个功能必须满足以下两个条件;
.服务器操作系统必须被配置支持IPv6
.MySQL服务器缺省配置必须除了支持IPv4的连接之外还要支持IPv6的连接。为了改变缺省配置可以在启动MySQL服务器时使用合适的–bind-address选项。

.MySQL账号名允许使用IPv6地址能让DBA为通过IPv6连接到服务器的客户端指定权限。例如:

mysql> create user 'v6root'@'::1' identified by "abcd";
mysql> grant select on mysql.* to 'v6root'@'::1';

.IPv6功能支持在字符串和内部格式IPv6地址格式之间进行转换并且检查其是否为有效的IPv6地址。例如
INET6_ATON()和INET6_NTOA()类似于INET_ATON()和INET_NTOA(),但除了能处理IPv4地址之外还能处理IPv6地址。

验证操作系统是否支持IPv6
在MySQL服务器可以接受IPv6的连接之前,服务器所在操作系统主机必须支持IPv6。例如在Linux主机上执行以下命令:

[root@localhost ~]# ping6 ::1
PING ::1(::1) 56 data bytes
64 bytes from ::1: icmp_seq=1 ttl=64 time=0.100 ms
64 bytes from ::1: icmp_seq=2 ttl=64 time=0.056 ms
64 bytes from ::1: icmp_seq=3 ttl=64 time=0.035 ms
64 bytes from ::1: icmp_seq=4 ttl=64 time=0.039 ms
....

为了对操作系统网络接口生成详细的描述,执行ifconfig -a并查看输出中的IPv6地址。

[root@localhost ~]# ifconfig -a
docker0: flags=4163  mtu 1500
        inet 172.17.0.1  netmask 255.255.0.0  broadcast 172.17.255.255
        inet6 fe80::42:15ff:fe1d:e05a  prefixlen 64  scopeid 0x20
        ether 02:42:15:1d:e0:5a  txqueuelen 0  (Ethernet)
        RX packets 15  bytes 3638 (3.5 KiB)
        RX errors 0  dropped 0  overruns 0  frame 0
        TX packets 33  bytes 3825 (3.7 KiB)
        TX errors 0  dropped 0 overruns 0  carrier 0  collisions 0

ens160: flags=4163  mtu 1500
        inet 192.168.1.250  netmask 255.255.255.0  broadcast 192.168.1.255
        inet6 fe80::250:56ff:fea3:90e6  prefixlen 64  scopeid 0x20
        ether 00:50:56:a3:90:e6  txqueuelen 1000  (Ethernet)
        RX packets 5634313  bytes 364548809 (347.6 MiB)
        RX errors 0  dropped 0  overruns 0  frame 0
        TX packets 19054  bytes 2337541 (2.2 MiB)
        TX errors 0  dropped 0 overruns 0  carrier 0  collisions 0

如果你的操作系统不支持IPv6,查看操作系统文档查找相关指令来启用IPv6。这可能只需要重新配置现有的网络接口来增加一个IPv6地址。或者需要执行更多的修改,比如使用IPv6选项来重建内核。

配置MySQL服务器允许IPv6连接
MySQL服务器对于TCP/IP连接会监听单个网络套接字。这种套接字被绑定到单个地址,但是可能一个地址映射到多个网络接口。为了指定一个地址,在服务器启动时使用–bind-address=addr选项,addr是IPv4或IPv6地址或主机名。(在MySQL 5.5.3之前不支持IPv6地址)。如果addr是主机名,服务器会解析这个主机名为一个IP地址并绑定到这个地址。

服务器对不同类型地址的处理如下:
.如果地址为*,如果服务器主机支持IPv6那么服务器可以接受来自所有服务器上所有IPv6和IPv4网络接口的TCP/IP连接,或者否则只会接受来自所有服务器上IPv4地址的TCP/IP连接。使用这个地址允许接受来自所有服务器网络接口的IPv4和IPv6连接。这个值是缺省值。

.如果地址为0.0.0.0,服务器只能接受来自所有服务器上IPv4网络接口的TCP/IP连接。

.如果地址为::,服务器接受来自所有服务器上的IPv4和IPv6网络接口的TCP/IP连接,这使用这个地址允许接受来自所有服务器网络接口的IPv4和IPv6连接。

.如果地址为IPv4映射地址,服务器接受来自这个地址的TCP/IP连接,这个地址是IPv4或IPv6格式。例如,如果服务器被绑定到::ffff:127.0.0.1,客户端可以使用–host=127.0.0.1或–host=::ffff:127.0.0.1来进行连接。

.如果地址为常规的IPv4或IPv6地址(比如127.0.0.1或::1),服务器只能接受IPv4或IPv6地址的TCP/IP连接。

如果倾向于绑定服务器到一个特定地址,确保mysql.user授权表包含一个有管理权限的用户可以使用这个地址进行连接。否则你将不能关闭服务器。例如,如果你倾向绑定服务器到*,你可以使用现有的所有账号来连接。但是如果你绑定服务器到::1,它只能接受这个地址的连接。在这种情况下,首先确保’root’@’::1’账号在mysql.user表中存在,所以可以仍然连接到服务器并关闭它。

使用IPv6本地主机地址连接MySQL服务器
下面介绍如何配置MySQL允许客户端通过IPv6(::1本地主机地址)连接到本地服务器。
1.在启动MySQL服务器时使用合适的–bind-address选项来允许服务器接受IPv6连接。例如,在my.cnf选项文件中设置bind-address并重启服务器

[mysqld]
......
bind-address=*
......

[root@localhost ~]# systemctl stop mysqld
[root@localhost ~]# systemctl start mysqld
[root@localhost ~]# systemctl status mysqld
* mysqld.service - LSB: start and stop MySQL
   Loaded: loaded (/etc/rc.d/init.d/mysqld; bad; vendor preset: disabled)
   Active: active (running) since 二 2020-01-14 16:56:48 CST; 5s ago
     Docs: man:systemd-sysv-generator(8)
  Process: 23257 ExecStart=/etc/rc.d/init.d/mysqld start (code=exited, status=0/SUCCESS)
    Tasks: 28
   Memory: 168.2M
   CGroup: /system.slice/mysqld.service
           |-23273 /bin/sh /mysqlsoft/mysql/bin/mysqld_safe --datadir=/mysqldata/mysql --pid-file=/mysqldata/mysql/mysqld.pid
           `-23591 /mysqlsoft/mysql/bin/mysqld --basedir=/mysqlsoft/mysql --datadir=/mysqldata/mysql --plugin-dir=/mysqlsoft/mysql/lib/plugin --user=mysql --log-error=/mysqldata/mysql/mysql.err --pid-file=/mysqldata/mysql/mysqld.pid ...

1月 14 16:56:46 localhost.localdomain systemd[1]: Starting LSB: start and stop MySQL...
1月 14 16:56:48 localhost.localdomain mysqld[23257]: Starting MySQL.. SUCCESS!
1月 14 16:56:48 localhost.localdomain systemd[1]: Started LSB: start and stop MySQL.

另外,你也可以绑定服务器到::1,但这会使用服务器更加受限于TCP/IP连接。它只能接受单个IPv6地址的连接并且会拒绝IPv4的连接。

2.作为管理员,连接到服务器创建一个本地用户账号它将使用::1本地IPv6主机地址进行连接

mysql> create user 'ipv6root'@'::1' identified by "abcd";
Query OK, 0 rows affected (0.01 sec)


mysql> grant select on *.* to 'ipv6root'@'::1';
Query OK, 0 rows affected (0.02 sec)

3.调用mysql客户端工具使用新创建的账号连接服务器

[mysql@localhost mysql]$ mysql -h ::1 -uipv6root -pabcd mysql
mysql: [Warning] Using a password on the command line interface can be insecure.
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.7.26-log Source distribution

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> select host,user from user;
+-----------+---------------+
| host      | user          |
+-----------+---------------+
| %         | csroot        |
| %         | root          |
| ::1       | ipv6root      |
| localhost | mysql.session |
| localhost | mysql.sys     |
+-----------+---------------+
5 rows in set (0.00 sec)

4.执行status命令来显示连接信息

mysql> status
--------------
mysql  Ver 14.14 Distrib 5.7.26, for Linux (x86_64) using  EditLine wrapper

Connection id:          7
Current database:       mysql
Current user:           ipv6root@localhost
SSL:                    Cipher in use is DHE-RSA-AES256-SHA
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server version:         5.7.26-log Source distribution
Protocol version:       10
Connection:             ::1 via TCP/IP
Server characterset:    utf8mb4
Db     characterset:    utf8mb4
Client characterset:    gb2312
Conn.  characterset:    gb2312
TCP port:               3306
Uptime:                 11 min 47 sec

Threads: 1  Questions: 263  Slow queries: 0  Opens: 336  Flush tables: 1  Open tables: 329  Queries per second avg: 0.371
--------------

mysql> SELECT CURRENT_USER(), @@bind_address;
+----------------+----------------+
| CURRENT_USER() | @@bind_address |
+----------------+----------------+
| ipv6root@::1   | *              |
+----------------+----------------+
1 row in set (0.00 sec)

使用IPv6非本地主机地址连接MySQL服务器
下面将介绍如何配置MySQL服务器允许远程主机通过IPv6来连接MySQL服务器。这类似于在本地主机使用IPv6连接MySQL服务器一样,但服务器与客户端在不同的主机上,每台主机有不同的IPv6地址。
服务器主机的IPv6地址为2018::1

[root@shard1 network-scripts]# ifconfig -a
ens160: flags=4163  mtu 1500
        inet6 2018::1  prefixlen 64  scopeid 0x0
        ether 00:50:56:a0:51:0c  txqueuelen 1000  (Ethernet)
        RX packets 27089788  bytes 2770300693 (2.5 GiB)
        RX errors 0  dropped 6  overruns 0  frame 0
        TX packets 6720001  bytes 532979878 (508.2 MiB)
        TX errors 0  dropped 0 overruns 0  carrier 0  collisions 0

客户端主机的IPv6地址为2018::2

ens160: flags=4163  mtu 1500
        inet6 2018::2  prefixlen 64  scopeid 0x0
        ether 00:50:56:a0:4e:69  txqueuelen 1000  (Ethernet)
        RX packets 25337245  bytes 1913014541 (1.7 GiB)
        RX errors 0  dropped 127  overruns 0  frame 0
        TX packets 1536650  bytes 166036937 (158.3 MiB)
        TX errors 0  dropped 0 overruns 0  carrier 0  collisions 0

具体操作如下:
1.在启动MySQL服务器时使用合适的–bind-address选项来允许服务器接受IPv6连接。例如,在my.cnf选项文件中设置bind-address并重启服务器

[mysqld]
......
bind-address=*
......

[root@localhost ~]# systemctl stop mysqld
[root@localhost ~]# systemctl start mysqld
[root@localhost ~]# systemctl status mysqld
* mysqld.service - LSB: start and stop MySQL
   Loaded: loaded (/etc/rc.d/init.d/mysqld; bad; vendor preset: disabled)
   Active: active (running) since 二 2020-01-14 16:56:48 CST; 5s ago
     Docs: man:systemd-sysv-generator(8)
  Process: 23257 ExecStart=/etc/rc.d/init.d/mysqld start (code=exited, status=0/SUCCESS)
    Tasks: 28
   Memory: 168.2M
   CGroup: /system.slice/mysqld.service
           |-23273 /bin/sh /mysqlsoft/mysql/bin/mysqld_safe --datadir=/mysqldata/mysql --pid-file=/mysqldata/mysql/mysqld.pid
           `-23591 /mysqlsoft/mysql/bin/mysqld --basedir=/mysqlsoft/mysql --datadir=/mysqldata/mysql --plugin-dir=/mysqlsoft/mysql/lib/plugin --user=mysql --log-error=/mysqldata/mysql/mysql.err --pid-file=/mysqldata/mysql/mysqld.pid ...

1月 14 16:56:46 localhost.localdomain systemd[1]: Starting LSB: start and stop MySQL...
1月 14 16:56:48 localhost.localdomain mysqld[23257]: Starting MySQL.. SUCCESS!
1月 14 16:56:48 localhost.localdomain systemd[1]: Started LSB: start and stop MySQL.

另外,可以绑定服务器到2018:db1:0:f01::1,但这会使用服务器更加受限于TCP/IP连接。它只能接受单个IPv6地址的连接并且会拒绝IPv4的连接。

2.在服务器中(2018::1)创建一个账号将从客户端主机(2018::2)上连接服务器

mysql> create user 'csroot'@'%' identified by "123456";
Query OK, 0 rows affected (0.01 sec)

mysql> GRANT ALL PRIVILEGES ON *.* TO 'csroot'@'%' IDENTIFIED BY '123456' WITH GRANT OPTION;
Query OK, 0 rows affected (0.00 sec)

3.在客户端主机上(2018::2),调用mysql客户端使用创建的新账号来连接服务器(我这时使用MariaDB作为客户端工具)

-bash-4.2$ mysql -h 2018::1 -ucsroot -p123456 mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

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

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MySQL [mysql]>

4.执行status命令来显示连接信息

MySQL [mysql]> status
--------------
mysql  Ver 15.1 Distrib 10.0.38-MariaDB, for Linux (x86_64) using readline 5.1

Connection id:          6
Current database:       mysql
Current user:           csroot@2018::2
SSL:                    Not in use
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server:                 MySQL
Server version:         5.6.23 MySQL Community Server (GPL)
Protocol version:       10
Connection:             2018::1 via TCP/IP
Server characterset:    latin1
Db     characterset:    latin1
Client characterset:    gb2312
Conn.  characterset:    gb2312
TCP port:               3306
Uptime:                 34 days 50 min 24 sec

Threads: 2  Questions: 175  Slow queries: 0  Opens: 87  Flush tables: 1  Open tables: 80  Queries per second avg: 0.000
--------------

MySQL [mysql]> select current_user(),@@bind_address;
+----------------+----------------+
| current_user() | @@bind_address |
+----------------+----------------+
| csroot@%       | *              |
+----------------+----------------+
1 row in set (0.00 sec)

MySQL Strict SQL MODE

严格SQL模式控制MySQL如何处理数据改变语句(insert或update)中的无效或缺失值。一个值可能由于各种原因而无效。例如,它对于列来说有错误的数据类型,或者超过了列的范围。当新记录被插入而对于非NULL且没有显式在定义时指定DEFAULT子句的列没有包含值就会出现缺失值的情况。(对于一个NULL列,如果缺失值就会插入NULL值)严格SQL模式也会影响DDL语句比如create table。

如果严格SQL模式没有生效,MySQL对于无效或者缺失值会插入调整值并生成一个警告。在严格SQL模式中,可以通过使用insert ignore或udpate ignore来产生这种行为。

对于select这样不改变数据的语句,在严格SQL模式中无效值会生成一个警告而不是错误。

严格SQL模式对于试图创建一个键值而超过列的最大键值长度时会产生一个错误。当严格SQL模式没有启用时,会产生一个警告并且截断键值的长度使其满足最大键值长度。

严格SQL模式不影响是否对外键约束执行检查。foreign_key_checks可以被使用。

如果STRICT_ALL_TABLES或STRICT_TRANS_TABLES被启用严格SQL模式就会生效,但这些模式的影响会有不同:
.对于事务表来说,当STRICT_ALL_TABLES或STRICT_TRANS_TABLES被启用后当在数据出现无效或丢失值就会出现错误。语句就会被终止与回滚。

.对于非事务表,如果在插入或更新语句中第一行记录出现坏值这些模式的行为是一样的:语句被终止并且表仍然保持不变。如果语句插入或修改多行记录并且在第二行或之后的行记录中出现坏值,那么结果依赖于严格SQL模式是否被启用。
-对于STRICT_ALL_TABLES,MySQL会返回一个错误并忽略剩余的行记录。然而,因为早些的行记录已经被插入或被更新,会导致部分更新。为了避免这个问题,使用单行语句,就会终止而不会改变表数据。

-对于STRICT_TRANS_TABLES,MySQL会将一个无效的值转换成一个最接近的有效值并插入这个调整值。如果这个值将丢失,MySQL插入这个隐式缺省值。在这种情况下,MySQL生成一个敬告而不是一个错误并继续处理语句。

严格SQL模式对除零,零日期和日期中出现零的处理如下:
.严格SQL模式影响对除零的处理,它包括MOD(N,0):对于数据改变操作(insert,update):
-如果严格SQL模式没有被启用,除零会插入NULL并生成一个警告。
-如果严格SQL模式被启用,除非指定了ignore否则除零操作会生成一个错误。对于insert ignore和update ignore操作,除零操作会插入NULL并生成一个警告。

对于select,除零操作会返回NULL。启用严格SQL模式会导致一个警告。
.严格SQL模式会影响服务器是否允许’0000-00-00’为一个有效日期:
-如果严格SQL模式没有被启用,’0000-00-00’被允许并且插入操作不会产生警告。
-如果严格SQL模式被启用,’0000-00-00’不被允许并且插入操作会产生错误,除非你指定ignore。对于insert ignore或update ignore,’0000-00-00’被允许并且插入操作会产生警告。

.严格SQL模式影响服务器是否允许在日期中的年部分为非零但月和日部分允许为零(比如’2010-00-01’或’2010-01-00′):
-如果严格SQL模式没有被启用,有零的日期被允许并且插入操作不产生警告。
-如果严格SQL模式被启用,有零的日期不被允许并且插入操作产生错误,除非指定ignore。对于insert ignore或update ignore来说,有零的日期将以’0000-00-00’形式被插入并产生一个警告。

IGNORE关键字与严格SQL模式的对比
这里将介绍在语句执行时IGNORE关键字(它降级错误为警告)和严格SQL模式(它升级警告为错误)的对比。描述它们影响那些语句以及应用它们有那些错误。

IGNORE对语句执行的影响
MySQL中的一些语句支持可选的IGNORE关键字。此关键字将导致服务器降级某些类型的错误并生成警告。对于多行语句,IGNORE会导致语句跳到下一行,而不是中止。

例如,如果表t2有一个主键列i,试图在多行记录中插入相同的i值正常来说会产生一个重复键错误:

mysql> insert into t2(i) values(1),(1);
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'

当使用IGNORE关键字时,包含重复键值的记录仍然不会被插入,但会使用警告来代替错误:

mysql> insert ignore into t2(i) values(1),(1);
Query OK, 1 row affected, 1 warning (0.15 sec)
Records: 2  Duplicates: 1  Warnings: 1

mysql> show warnings;
+---------+------+---------------------------------------+
| Level   | Code | Message                               |
+---------+------+---------------------------------------+
| Warning | 1062 | Duplicate entry '1' for key 'PRIMARY' |
| Warning | 1062 | Duplicate entry '1' for key 'PRIMARY' |
+---------+------+---------------------------------------+
2 rows in set (0.00 sec)

以下语句支持IGNORE关键字:
.create table… select:ignore不能应用到语句的create table或select部分但对于由select语句所提供记录来执行插入语句可以应用。对于唯一键值重复的记录会被丢弃。

.delete:ignore会导致MySQL在处理删除记录时忽略错误。

.insert:使用ignore,对于唯一键值重复的记录会被丢弃。对于重复键值的行记录会导致数据转换为最接近的有效值被插入。

对于分区表当没有匹配指定值的分区被找到时,ignore会导致包含那些不匹配值的记录的插入操作失败。
.load data,load xml:使用ignore,对于唯一键值重复的记录会被丢弃。

.update:使用ignore,对于在唯一键值出现重复键值冲突的记录不会被更新。被更新的记录可能导致数据转换为最接近的有效值被插入。

ignore关键字应用到以下错误:
ER_BAD_NULL_ERROR
ER_DUP_ENTRY
ER_DUP_ENTRY_WITH_KEY_NAME
ER_DUP_KEY
ER_NO_PARTITION_FOR_GIVEN_VALUE
ER_NO_PARTITION_FOR_GIVEN_VALUE_SILENT
ER_NO_REFERENCED_ROW_2
ER_ROW_DOES_NOT_MATCH_GIVEN_PARTITION_SET
ER_ROW_IS_REFERENCED_2
ER_SUBQUERY_NO_1_ROW
ER_VIEW_CHECK_FAILED

严格SQL模式对语句执行的影响
MySQL服务器可以以不同的SQL模式进行操作并且可以应用这些不同模式到不同的客房端,这依赖于sql_mode系统变量。在严格SQL模式中,服务器会将特定的警告升级成错误。

例如,在非严格SQL模式中,向整数类型列插入字符串’abc’的结果是将这个字符串值转换为0并生成一个警告:

mysql> SET sql_mode = '';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> insert into t2(i) values('abc');
Query OK, 1 row affected, 1 warning (0.02 sec)

mysql> show warnings;
+---------+------+--------------------------------------------------------+
| Level   | Code | Message                                                |
+---------+------+--------------------------------------------------------+
| Warning | 1366 | Incorrect integer value: 'abc' for column 'i' at row 1 |
+---------+------+--------------------------------------------------------+
1 row in set (0.00 sec)

在严格SQL模式下,无效值会被拒绝并生成错误:

mysql> SET sql_mode = 'STRICT_ALL_TABLES';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> insert into t2(i) values('abc');
ERROR 1366 (HY000): Incorrect integer value: 'abc' for column 'i' at row 1

在某些条件下,某些值可能超出范围或将无效行插入或从表中删除,严格SQL模式适用于以下语句:
alter table
create table
create table … select
delete(单表和多表)
insert
load data
load xml
select sleep()
update(单表和多表)
在存储程序中,如果程序是在严格模式生效时定义的,则刚才列出的类型的各个语句将以严格SQL模式执行。

严格SQL模式应用于以下错误,代表输入值可能无效或丢失这类错误。如果对于列值使用了错误数据类型或超过了值的
范围那么值就是无效的。如果被插入的新行不包含NOT NULL列值但除了在列定义时显式指定了DEFAULT子句的那么就
是值丢失。
ER_BAD_NULL_ERROR
ER_CUT_VALUE_GROUP_CONCAT
ER_DATA_TOO_LONG
ER_DATETIME_FUNCTION_OVERFLOW
ER_DIVISION_BY_ZERO
ER_INVALID_ARGUMENT_FOR_LOGARITHM
ER_NO_DEFAULT_FOR_FIELD
ER_NO_DEFAULT_FOR_VIEW_FIELD
ER_TOO_LONG_KEY
ER_TRUNCATED_WRONG_VALUE
ER_TRUNCATED_WRONG_VALUE_FOR_FIELD
ER_WARN_DATA_OUT_OF_RANGE
ER_WARN_NULL_TO_NOTNULL
ER_WARN_TOO_FEW_RECORDS
ER_WRONG_ARGUMENTS
ER_WRONG_VALUE_FOR_TYPE
WARN_DATA_TRUNCATED

MySQL SQL模式

MySQL服务器可以以不同的SQL模式来进行操作,并且依赖于sql_mode系统变量的值对不同的客户端可以应用这些不同的SQL模式。DBA可以设置全局SQL模式来匹配服务器操作要求,并且每种应用程序可以设置它的会话SQL模式来满足它的要求。

SQL模式会影响MySQL支持的SQL语法和数据验证检查。这可以在不同环境中让MySQL与其它数据库一起使用变得更容易。

当使用InnoDB表时,可以考虑使用innodb_strict_mode系统变量,它可以对InnoDB表启用额外的错误检查。

设置SQL模式
在MySQL 5.7中缺省的SQL模式包含:ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES, NO_ZERO_IN_DATE,NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,和 NO_ENGINE_SUBSTITUTION。ONLY_FULL_GROUP_BY和STRICT_TRANS_TABLES是在MySQL 5.7.5中加入的。NO_AUTO_CREATE_USER是在MySQL 5.7.7中加入的。ERROR_FOR_DIVISION_BY_ZERO,NO_ZERO_IN_DATE和NO_ZERO_DATE是在MySQL 5.7.8中加入的。

为了在服务器启动时设置SQL模式,可以在命令行中使用–sql-mode=”modes”选项或在选项文件比如Unix中的my.cnf或Windows上的my.ini文件中使用sql-mode=”modes”选项。modes是用逗号分的不同模式列表。为了显式的清除SQL模式,可以在命令行中使用–sql-mode=””选项将SQL模式设置为空字符串,或者在选项文件中使用sql-mode=””。

MySQL安装程序可以在安装过程中会配置SQL模式。例如,mysql_install_db将在基本的安装目录中创建一个命名为my.cnf的缺省选项文件。这个文件包含设置SQL模式的记录。

如果SQL模式不同于缺省SQL模式或你所期待的SQL模式,可以检查服务器在启动时所读取的选项文件。

为了在运行时改变SQL模式,可以使用set语句来设置全局或会话级的sql_mode系统变量:
set global sql_mode=’modes’;
set session sql_mode=’modes’;

设置global变量需要有super权限并且影响所有连接的客户端操作。设置session变量只影响当前客户端。每个客户端可以在任何时间改变它会话的sql_mode值来达到改变SQL模式的目的。

为了判断当前全局或session级的sql_mode值,执行以下命令:

mysql> SELECT @@GLOBAL.sql_mode;
+-------------------------------------------------------------------------------------------------------------------------------------------+
| @@GLOBAL.sql_mode                                                                                                                         |
+-------------------------------------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+-------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT @@SESSION.sql_mode;
+-------------------------------------------------------------------------------------------------------------------------------------------+
| @@SESSION.sql_mode                                                                                                                        |
+-------------------------------------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+-------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

注意在创建分区表并插入数据之后改变服务器的SQL模式会对表的行为造成重大改变并且可能导致丢失或损坏数据。所以强烈建议在创建用户定义的分区表之后不要修改SQL模式。

当复制分区表时,在主从服务器之间不同的SQL模式也会导致一些问题。所以最好的结果就是在主从服务器上使用相同的SQL模式。

最重要的SQL模式
MySQL中最重要的sql_mode值可能是这些:
.ANSI
这种SQL模式改变语法和行为使其更接近标准SQL。它是一种特定的组合模式列表。

.STRICT_TRANS_TABLES
如果一个值不能以指定的方式插入到一个事务表,终止这个语句。对于非事务表,如果这个值在一个单行记录语句或在多行记录语句第一个出现时终止语句。从MySQL 5.7.5开始缺省的SQL模式包括STRICT_TRANS_TABLES。

.TRADITIONAL
使用MySQL行为像一个传统的SQL数据库系统。简单来说这种模式对于将一个不正确值插入到一个列中时抛出一个错误来代替一个警告。它是特定组合模式中的一种。insert或update一旦发现错误会立即终止。如果您正在使用非事务性存储引擎,这可能不是您想要的,因为在错误之前所做的数据更改可能无法回滚,从而导致“部分完成”的更新。

当提到”strict mode”严格模式时,它意味着是STRICT_TRANS_TABLES或STRICT_ALL_TABLES中的一种或两种都被启用。

完整的SQL模式列表
下面是所有支持的SQL模式列表:
.ALLOW_INVALID_DATES
不对日期执行完全检查。只对月份的范围从1到12和日期的范围从1到31执行检查。这对于Web应用程序非常方便,您可以在三个不同的字段中获得年、月和日,并且希望准确地存储用户插入的内容(不执行日期校验)。这种SQL模式应用于date和datetime列。它不应用于timestamp列,因为它总是请求一个合法的日期值。

这种SQL模式要求月分与每天的取值是合法值,并且范围分别不能超过1到12和1到31。当严格模式被禁用时,一个无效的日期值比如’2014-04-31’将被转换成’0000-00-00’并且生成一个警告。当使用严格模式时,一个无效的日期值会生成一个错误。为了允许这样的日期值,启用ALLOW_INVALID_DATES模式。

.ANSI_QUOTES
将”作为标识引用字符(像`引用字符)而不是字符串引用字符。当使用这种SQL模式时仍然可以使用`作为引用标识符,当ANSI_QUOTES被使用时,不能使用双引号来引用文本字符串,因为它被解析作标识符了。

.ERROR_FOR_DIVISION_BY_ZERO
ERROR_FOR_DIVISION_BY_ZERO模式影响对除以零的处理,这包含MOD(N,0)。对于数据修改操作(insert,update),它的影响也依赖于是否启用了严格SQL模式。
-如果这种模式没有被启用,除以零会插入NULL值并且没有警告。
-如果这种模式被启用,除以零会插入NULL值并生成警告。
-如果这种模式与严格SQL模式被启用,除以堆生成一个错误,除非IGNORE也被指定。对于insert ignore和update ignore,除以零会插入NULL值并生成警告。

对于select语句,除以零会返回NULL值。启用ERROR_FOR_DIVISION_BY_ZERO不管是否启用严格SQL模式会导致生成一个警告。

在MySQL 5.7.4中,ERROR_FOR_DIVISION_BY_ZERO被丢弃,在MySQL 5.7.4到5.7.7中当显式命名时ERROR_FOR_DIVISION_BY_ZERO不会做任何事。代替的是,它的影响被包含在严格SQL模式中。在MySQL 5.7.8和以后的版本中,当显式命名时ERROR_FOR_DIVISION_BY_ZERO会有影响并且没有包含在严格SQL模式中,就像MySQL5.7.4之前的版本一样。然而缺省情况下当严格模式启用下应该与它联合使用。如果ERROR_FOR_DIVISION_BY_ZERO被启用而没有启用严格模式或者当启用严格模式而没有启用ERROR_FOR_DIVISION_BY_ZERO时会出现这个警告。

因为ERROR_FOR_DIVISION_BY_ZERO被丢弃,它将在将来的版本中被删除并作为一个单独的模式名并且它的影响被包含在严格SQL模式。

.HIGH_NOT_PRECEDENCE
NOT操作的优先级就像NOT a between b and c会被解析成NOT (a between b and c)一样。在一些旧版本的MySQL中,表达式会被解析成(NOT a) between b and c。旧版本更高优先级行为可以通过启用HIGH_NOT_PRECEDENCE模式来获得。

mysql> SET sql_mode = '';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> SELECT NOT 1 BETWEEN -5 AND 5;
+------------------------+
| NOT 1 BETWEEN -5 AND 5 |
+------------------------+
|                      0 |
+------------------------+
1 row in set (0.00 sec)

mysql> SET sql_mode = 'HIGH_NOT_PRECEDENCE';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT NOT 1 BETWEEN -5 AND 5;
+------------------------+
| NOT 1 BETWEEN -5 AND 5 |
+------------------------+
|                      1 |
+------------------------+
1 row in set (0.00 sec)

.IGNORE_SPACE
允许在函数名与(符号之间存在空格。这会造成内置函数名被作为保留关键字对待。因此标识符与函数名相同必须有引号。例如,因为有一个count()函数,因此在下面的语句中使用count作为表名就会出错:

mysql> CREATE TABLE count (i INT);
Query OK, 0 rows affected (0.13 sec)

mysql> drop table count cascade;
Query OK, 0 rows affected (0.09 sec)

mysql> SET sql_mode = 'IGNORE_SPACE';
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE count (i INT);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'count (i INT)' at line 1

mysql> create table `count` (i INT);
Query OK, 0 rows affected (0.16 sec)

IGNORE_SPACE模式应用到内置函数,而不是用户定义的函数或存储过程。它总是允许在用户定义的函数或存储过程名后有空格而不管是否启用了IGNORE_SAPCE模式。

.NO_AUTO_CREATE_USER
除非指定了身份验证信息,否则将阻止GRANT语句自动创建新用户帐户。这个语句必须使用identified by来指定非空密码或使用identified with来使用一种验证插件。

最好使用create user来创建MySQL账号,然后使用Grant语句。NO_AUTO_CREATE_USER已经被丢弃并且缺省的SQL模式包含了NO_AUTO_CREATE_USER模式。将sql_mode修改为NO_AUTO_CREATE_USER模式会生成一个警告,除了指定sql_mode为DEFAULT.NO_AUTO_CREATE_USER将会在将来的版本中被删除,到时它的影响将会一直被启用。

之前,在NO_AUTO_CREATE_USER被丢弃之前,一个不启用它的原因是它是不安全的复制。现在它可以被启用并且使用create user if not exists,drop user if exists和alter user if exists而不是grant语句来执行安全复制管理。这些语句当从服务器相对于主服务器有不同的授权时可以启用安全复制。

.NO_AUTO_VALUE_ON_ZERO
NO_AUTO_VALUE_ON_ZERO影响对AUTO_INCREMENT列的处理。正常来说通过向访列插入NULL或0来为列生成下一个序列值。NO_AUTO_VALUE_ON_ZERO会抑制0的行为因此只有插入NULL时会生成下一个序列值。如果0已经被存储到表的AUTO_INCREMENT列中这个SQL模式可能是有用的。(存储0是不建议的)例如,如果使用mysqldump来dump表并且然后再加载它,当遇到值0时MySQL正常来说会生成一个新的序列值,因此表的内容不同于被dump的内容。在加载dump文件之前启用NO_AUTO_VALUE_ON_ZERO来解决这个问题。mysqldump现在在它的输出中自动包含一个语句来启用NO_AUTO_VALUE_ON_ZERO来避免这个问题。

.NO_BACKSLASH_ESCAPES
禁用在字符串中使用反斜杠字符(\)作为转义字符。启用此模式后,反斜杠将成为与其他字符一样的普通字符。

.NO_DIR_IN_CREATE
创建表时,忽略所有索引目录和数据目录指令。此选项在从复制服务器上非常有用。

.NO_ENGINE_SUBSTITUTION
当一个语句比如create table或alter table指定一个存储引擎已经被禁用或没有被编译时控制缺省存储引擎的自动替换。缺省的SQL模式中包含了NO_ENGINE_SUBSTITUTION。因为存储引擎可以在运行时被附加进来,不可以存储引擎也以相同方式被对待:
当NO_ENGINE_SUBSTITUTION被禁用,对于create table的缺省存储引擎被使用并且如果期待的存储引擎不可用会出现一个警告。对于alter table,会出现一个警告并且表不能被修改。
当NO_ENGINE_SUBSTITUTION被启用时,如果期待的存储引擎不可用会出现一个警告并且表不会被创建或被修改。

.NO_FIELD_OPTIONS
在show create table输出中不打印特定MySQL列选项。这种SQL模式被mysqldump以可移植模式来使用。

.NO_KEY_OPTIONS
在show create table输出中不打印特定MySQL索引选项。这种SQL模式被mysqldump以可移植模式来使用。

.NO_TABLE_OPTIONS
在show create table输出中不打印特定MySQL表选项(比如ENGINE)。这种SQL模式被mysqldump以可移植模式来使用。

.NO_UNSIGNED_SUBTRACTION
两个整数相减,这里一种类型UNSIGNED,缺省情况下生成一个没有符号的结果。如果结果出现负数将会出现错误:

mysql> SET sql_mode = '';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> SELECT CAST(0 AS UNSIGNED) - 1;
ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '(cast(0 as unsigned) - 1)'

如果NO_UNSIGNED_SUBTRACTION模式被启用,结果将是负数:

mysql> SET sql_mode = 'NO_UNSIGNED_SUBTRACTION';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT CAST(0 AS UNSIGNED) - 1;
+-------------------------+
| CAST(0 AS UNSIGNED) - 1 |
+-------------------------+
|                      -1 |
+-------------------------+
1 row in set (0.00 sec)

如果使用此类操作的结果更新无符号整数列,则将该结果裁剪为该列类型的最大值,如果启用no_unsigned_subtract,则将其裁剪为0。如果严格SQL模式被启用,则会出现错误并且列会保持不变。

当no_unsigned_subtraction被启用时,就算任何操作数据是无符号的相减的结果是有符号的。例如比较表t1中的c2列与表t2中的c2列:

mysql> SET sql_mode='';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE TABLE test (c1 BIGINT UNSIGNED NOT NULL);
Query OK, 0 rows affected (0.20 sec)


mysql> CREATE TABLE t1 SELECT c1 - 1 AS c2 FROM test;
Query OK, 0 rows affected (0.11 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc t1;
+-------+---------------------+------+-----+---------+-------+
| Field | Type                | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+-------+
| c2    | bigint(21) unsigned | NO   |     | 0       |       |
+-------+---------------------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> SET sql_mode='NO_UNSIGNED_SUBTRACTION';
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE t2 SELECT c1 - 1 AS c2 FROM test;
Query OK, 0 rows affected (0.25 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc t2;
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| c2    | bigint(21) | NO   |     | 0       |       |
+-------+------------+------+-----+---------+-------+
1 row in set (0.00 sec)

这意味着bigint unsigned在所有上下文中不是100%可以使用。

.NO_ZERO_DATE
NO_ZERO_DATE模式影响服务器是否允许’0000-00-00’作为一种有效的日期。它的影响也依赖于是否启用了严格SQL模式。
-如果模式没有被启用,’0000-00-00’被允许并且插入不会产生警告。
-如果模式被启用,’0000-00-00’被允许并且插入会产生警告。
-如果模式和严格SQL模式被启用,’0000-00-00’不被允许并且插入会出现错误,除非指定IGNORE选项。对于insert ignore和update ignore来说,’0000-00-00’被允许并且插入会产生警告。

在MySQL 5.7.4中,NO_ZERO_DATE被丢弃。在MySQL 5.7.4到5.7.7中,NO_ZERO_DATE当显式指定时不会工作。代替地是它的影响已经被包含在严格SQL模式中。在MySQL 5.7.8和以后的版本中,当显式地指定NO_ZERO_DATE模式时它会工作并且它不是严格SQL模式的一部分就像MySQL5.7.4版本之前作用一样。然而,缺省情况下它应该与严格SQL模式联合使用。如果启用NO_ZERO_DATE而没有启用严格SQL模式就是出现警告或者反之亦然。

因为NO_ZERO_DATE已经被丢弃,因此它会在将来的版本中作为单独的模式名被删除并且它的影响会包含在严格SQL模式中。

.NO_ZERO_IN_DATE
NO_ZERO_IN_DATE模式影响服务器是否允许日期中的年部分为非0但月或日部分为0。(这种模式影响日期比如’2010-00-01’或’2010-01-00’,而不是’0000-00-00’。为了控制服务器是否允许’0000-00-00’,使用NO_ZERO_DATE模式)。NO_ZERO_IN_DATE模式的影响也依赖于是否启用了严格SQL模式。
-如果这种模式没有启用,有为0部分折日期被允许并且插入不产生警告。
-如果这种模式被启用,有为0部分的日期值将以’0000-00-00’格式被插入并且生成一个警告。
-如果这种模式与严格SQL模式被启用,有为0部分的日期不被允许并且插入会产生一个错误,除非你指定ignore。对于insert ignore和update ignore来说,有为0部分的日期将以’0000-00-00’格式被插入并且生成一个警告。

在MySQL 5.7.4中,NO_ZERO_IN_DATE被丢弃。在MySQL 5.7.4到5.7.7中,NO_ZERO_DATE当显式指定时不会工作。代替地是它的影响已经被包含在严格SQL模式中。在MySQL 5.7.8和以后的版本中,当显式地指定NO_ZERO_IN_DATE模式时它会工作并且它不是严格SQL模式的一部分就像MySQL5.7.4版本之前作用一样。然而,缺省情况下它应该与严格SQL模式联合使用。如果启用NO_ZERO_IN_DATE而没有启用严格SQL模式就是出现警告或者反之亦然。

因为NO_ZERO_IN_DATE已经被丢弃,因此它会在将来的版本中作为单独的模式名被删除并且它的影响会包含在严格SQL模式中。

.ONLY_FULL_GROUP_BY
拒绝那些select list、HAVING condition或ORDER BY list引用非聚合列的查询,这些列既不在GROUP BY子句中命名,也不依赖于GROUP BY列(由GROUP BY列唯一确定)。

从MySQL 5.7.5开始,缺省的SQL模式包含ONLY_FULL_GROUP_BY模式。(在5.7.5之前,MySQL没有检测到功能依赖并且缺省情况下ONLY_FULL_GROUP_BY模式没有启用。

MySQL扩展了标准SQL来允许在having子句中引用select列表中的别名表达式。在MySQL 5.7.5之前,启用ONLY_FULL_GROUP_BY模式会禁用这种扩展,因此要求having子句以非别名表达式来书写。从MySQL5.7.5开始,这种限制被取消了因此having子句可以引用别名而不用管ONLY_FULL_GROUP_BY模式是否启用了。

.PAD_CAHR_TO_FULL_LENGTH
默认情况下,在检索时从CHAR列值中裁剪尾随空格。如果PAD_CHAR_TO_FULL_LENGTH被启用,裁剪不会发生并且在检索CHAR列值时填充到它的完整长度。这种模式不应用于varchar列。检索时为其保留尾随空格。

mysql> CREATE TABLE t1 (c1 CHAR(10));
Query OK, 0 rows affected (0.12 sec)

mysql> INSERT INTO t1 (c1) VALUES('xy');
Query OK, 1 row affected (0.03 sec)

mysql> SET sql_mode = '';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> SELECT c1, CHAR_LENGTH(c1) FROM t1;
+------+-----------------+
| c1   | CHAR_LENGTH(c1) |
+------+-----------------+
| xy   |               2 |
+------+-----------------+
1 row in set (0.00 sec)

mysql> SET sql_mode = 'PAD_CHAR_TO_FULL_LENGTH';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT c1, CHAR_LENGTH(c1) FROM t1;
+------------+-----------------+
| c1         | CHAR_LENGTH(c1) |
+------------+-----------------+
| xy         |              10 |
+------------+-----------------+
1 row in set (0.00 sec)

.PIPES_AS_CONCAT
将||作为字符串连接操作符(与concat()一样)而不是作为OR的同义词。

.REAL_AS_FLOAT
将REAL作为FLOAT的同义词。缺省情况下,MySQL将REAL作为DOUBLE的同义词。

.STRICT_ALL_TABLES
对所有的存储引擎启用严格SQL模式。无效的数据会被拒绝。从MySQL 5.7.4到5.7.7,STRICT_ALL_TABLES模式包含了ERROR_FOR_DIVISION_BY_ZERO,NO_ZERO_DATE和NO_ZERO_IN_DATE模式的影响。

.STRICT_TRANS_TABLES
对事务型存储引擎启用严格SQL模式并且在可能的情况下使用非事务型存储引擎。从MySQL 5.7.4到5.7.7,STRICT_TRANS_TABLES模式包含了ERROR_FOR_DIVISION_BY_ZERO,NO_ZERO_DATE和NO_ZERO_IN_DATE模式的影响。

mysqld –skip-grant-tables

mysqld的–skip-grant-tables选项
这个选项会导致不使用权限系统来启动服务器,它将让任何用户可以访问服务器并且不受限制的访问所有数据库。在不使用授权表启动服务器后可以通过shell来执行mysqladmin flush-privileges或mysqladmin reload命令或者在连接到服务器后执行flush privileges语句来让正在运行的服务器再次使用授权表。

使用–skip-grant-tables选项启动服务器

[root@localhost mysql]# service mysqld stop
Shutting down MySQL. SUCCESS! 


[root@localhost mysql]# service mysqld start --skip-grant-tables
Starting MySQL.. SUCCESS! 

现在就可以不使用用户和密码就可以登录服务器

[mysql@localhost ~]$ mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.26-log Source distribution

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>

现在可以执行mysqladin flush-privileges命令让正在运行的服务器再次使用授权表

[mysql@localhost ~]$ mysqladmin  flush-privileges

现在不使用用户和密码就不能登录服务器了,必须使用用户和密码才能登录了

[mysql@localhost ~]$ mysql
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)


[mysql@localhost ~]$ mysql -uroot -pabcd mysql
mysql: [Warning] Using a password on the command line interface can be insecure.
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.7.26-log Source distribution

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.

再次使用–skip-grant-tables选项启动服务器

[root@localhost mysql]# service mysqld stop
Shutting down MySQL.. SUCCESS! 

[root@localhost mysql]# service mysqld start --skip-grant-tables
Starting MySQL.. SUCCESS! 

现在就可以不使用用户和密码就可以登录服务器

[mysql@localhost ~]$ mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.26-log Source distribution

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>

现在可以执行mysqladin reload命令让正在运行的服务器再次使用授权表

mysql@localhost ~]$ mysqladmin reload

现在不使用用户和密码就不能登录服务器了,必须使用用户和密码才能登录了

[mysql@localhost ~]$ mysql
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
[mysql@localhost ~]$ mysql -uroot -pabcd mysql
mysql: [Warning] Using a password on the command line interface can be insecure.
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.7.26-log Source distribution

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.

再次使用–skip-grant-tables选项启动服务器

[root@localhost mysql]# service mysqld stop
Shutting down MySQL. SUCCESS! 


[root@localhost mysql]# service mysqld start --skip-grant-tables
Starting MySQL.. SUCCESS! 

现在就可以不使用用户和密码就可以登录服务器

[mysql@localhost ~]$ mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.26-log Source distribution

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>

现在可以执行flush privileges语句让正在运行的服务器再次使用授权表

mysql> flush privileges;
Query OK, 0 rows affected (0.12 sec)

现在不使用用户和密码就不能登录服务器了,必须使用用户和密码才能登录了

[mysql@localhost ~]$ mysql
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
[mysql@localhost ~]$ mysql -uroot -pabcd mysql
mysql: [Warning] Using a password on the command line interface can be insecure.
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.7.26-log Source distribution

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.

–skip-grant-tables选项也可以在选项文件my.cnf中进行设置。这个选项还会导致服务器在启动过程中禁止加载用户定义函数(udf),调度事件和安装插件语句中安装的插件。为了以任何方式来加载插件,使用–plugin-load选项。–skip-grant-tables选项也会导致disabled_storage_engines系统变量失效。

flush privileges语句可以在服务器启动后通过执行其它操作来隐式执行。例如在升级过程中mysql_upgrade程序就会刷新权限。

mysqldump+mysqlbinlog执行备份与还原

服务器的二进制日志文件由用来描述修改数据库内容的事件组成。服务器以二进制方式来写这些文件。为了以文本格式来显示这些内容,可以使用mysqlbinlog工具。也可以使用mysqlbinlog来显示在复制环境中由从从slave服务器所写入中relay日志文件中的内容,因为其格式与二进制日志文件格式一样。
mysqlbinlog的使用语法如下:
Usage: mysqlbinlog [options] log-files

下面的命令用来显示名为binlog.000001的二进制日志文件的内容:

[mysql@localhost ~]$ mysqlbinlog  /mysqldata/mysql/binlog.000001
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#191115 15:39:01 server id 1  end_log_pos 123 CRC32 0x2d9d7b4f  Start: binlog v 4, server v 5.7.26-log created 191115 15:39:01 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
FVbOXQ8BAAAAdwAAAHsAAAABAAQANS43LjI2LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAVVs5dEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
AU97nS0=
'/*!*/;
# at 123
#191115 15:39:01 server id 1  end_log_pos 154 CRC32 0x42dcd61c  Previous-GTIDs
# [empty]
# at 154
#191115 15:51:15 server id 1  end_log_pos 219 CRC32 0x5bc0b021  Anonymous_GTID  last_committed=0        sequence_number=1       rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 219
#191115 15:51:15 server id 1  end_log_pos 308 CRC32 0x7261eacb  Query   thread_id=2     exec_time=0     error_code=0
use `mysql`/*!*/;
SET TIMESTAMP=1573804275/*!*/;
SET @@session.pseudo_thread_id=2/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1436549152/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C gb2312 *//*!*/;
SET @@session.character_set_client=24,@@session.collation_connection=24,@@session.collation_server=45/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
truncate table person
/*!*/;
# at 308
#191115 15:51:38 server id 1  end_log_pos 373 CRC32 0x6d2e39aa  Anonymous_GTID  last_committed=1        sequence_number=2       rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 373
#191115 15:51:38 server id 1  end_log_pos 454 CRC32 0x7871c2ea  Query   thread_id=2     exec_time=0     error_code=0
SET TIMESTAMP=1573804298/*!*/;
BEGIN
/*!*/;
# at 454
# at 486
#191115 15:51:38 server id 1  end_log_pos 486 CRC32 0xb746cd30  Intvar
SET INSERT_ID=1/*!*/;
#191115 15:51:38 server id 1  end_log_pos 654 CRC32 0x0e926042  Query   thread_id=2     exec_time=0     error_code=0
SET TIMESTAMP=1573804298/*!*/;
insert into person(last_name,first_name,birth,death) values('yong','jing','1985-02-28',null)
/*!*/;
# at 654
#191115 15:51:38 server id 1  end_log_pos 736 CRC32 0xc5450308  Query   thread_id=2     exec_time=0     error_code=0
SET TIMESTAMP=1573804298/*!*/;
COMMIT
/*!*/;
# at 736
#191115 15:51:45 server id 1  end_log_pos 801 CRC32 0xc2c892b8  Anonymous_GTID  last_committed=2        sequence_number=3       rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 801
#191115 15:51:45 server id 1  end_log_pos 882 CRC32 0x51a9cd5c  Query   thread_id=2     exec_time=0     error_code=0
SET TIMESTAMP=1573804305/*!*/;
BEGIN
/*!*/;
# at 882
# at 914
#191115 15:51:45 server id 1  end_log_pos 914 CRC32 0x40a98fae  Intvar
SET INSERT_ID=2/*!*/;
#191115 15:51:45 server id 1  end_log_pos 1082 CRC32 0x3396c40d         Query   thread_id=2     exec_time=0     error_code=0
SET TIMESTAMP=1573804305/*!*/;
insert into person(last_name,first_name,birth,death) values('yan','huang','1990-08-25',null)
/*!*/;
# at 1082
#191115 15:51:45 server id 1  end_log_pos 1164 CRC32 0xf6f6efad         Query   thread_id=2     exec_time=0     error_code=0
SET TIMESTAMP=1573804305/*!*/;
COMMIT
/*!*/;
# at 1164
#191115 15:51:53 server id 1  end_log_pos 1229 CRC32 0x55b50dbe         Anonymous_GTID  last_committed=3        sequence_number=4       rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 1229
#191115 15:51:53 server id 1  end_log_pos 1310 CRC32 0xd0f6a335         Query   thread_id=2     exec_time=0     error_code=0
SET TIMESTAMP=1573804313/*!*/;
BEGIN
/*!*/;
# at 1310
# at 1342
#191115 15:51:53 server id 1  end_log_pos 1342 CRC32 0xfad94baf         Intvar
SET INSERT_ID=3/*!*/;
#191115 15:51:53 server id 1  end_log_pos 1508 CRC32 0x26c5b3bb         Query   thread_id=2     exec_time=0     error_code=0
SET TIMESTAMP=1573804313/*!*/;
insert into person(last_name,first_name,birth,death) values('yali','ye','1994-12-23',null)
/*!*/;
# at 1508
#191115 15:51:53 server id 1  end_log_pos 1590 CRC32 0xbb6a2b4c         Query   thread_id=2     exec_time=0     error_code=0
SET TIMESTAMP=1573804313/*!*/;
COMMIT
/*!*/;
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*/;

上面输出了binlog.000001二进制日志文件中所包含的内容。对于基于语句的日志,事件信息包括SQL语句,执行语句的服务器ID,语句被执行的时间戳,执行时间等。对于基于行记录的日志,事件信息指示行的改变而不是SQL语句。

# at 486
#191115 15:51:38 server id 1  end_log_pos 486 CRC32 0xb746cd30  Intvar SET INSERT_ID=1/*!*/;
#191115 15:51:38 server id 1  end_log_pos 654 CRC32 0x0e926042  Query   thread_id=2
exec_time=0     error_code=0 SET TIMESTAMP=1573804298/*!*/;

第一行,at后面的数字指示事件在二进制日志文件中的偏移量或开始位置。

第二行是以日期和时间开始指示语句开始执行的时间。对于复制来说,这个时间戳将传播到从属服务器。server id是事件起源服务器的server_id值。end_log_pos指示下一个事件开始的位置(它是当前事件的终止位置+1)。thread_id那个线程来执行这个事件。exec_time是在主服务器上执行事件所花费的时间。在从属服务器上,它是从属服务器上执行结束时间减去主服务器上的执行开始时间的差值。这种差值可以作为一种指示来表示复制进程落后于主服务器多长时间。error_code指示执行事件的结果。零意味着没有出现错误。

mysqlbinglog的输出可以用来重新执行日志文件中的语句(例如,通过使用mysql工具)。这在服务器崩溃时用来恢复是很有用的。

正常来说,使用mysqlbinlog直接读取二进制日志文件并应用它们到本地MySQL服务器。它也可以通过使用–read-from-remote-server选项来从远程服务器上读取二进制日志文件。为了读取远程二进制日志文件,连接参数选项可以被指定用来指示如何连接服务器。这些选项有–host,–password,–port,–protocol,–socket和–user,除非使用了–read-from-remote-server选项否则它们会被忽略。

当对一个大的二进制日志文件执行mysqlbinlog时,要小心注意文件系统是否有足够的空间来存储结果文件。为了配置目录给mysqlbinlog临时使用存储文件,使用TMPDIR环境变量。

使用mysqlbinlog备份二进制日志文件
缺省情况下,mysqlbinlog读取二进制日志文件并以文本格式来显示它的内容。这能让你更容易使用文件来检查发生的事件和重新执行它们(例如,通过使用输出作为mysql的输入)。mysqlbinlog可以直接从本地文件系统中读取日志文件或者使用–read-from-remote-server选项来连接远程服务器并从远程服务器上读取二进制日志文件。mysqlbinlog以文本格式将内容输出到标准输出,或者如果指定了–result-file=file_name选项会将内容写入文件。

mysqlbinlog可以读了二进制日志文件并将其包含的内容以二进制格式而不是文本格式写入新文件。这种能力可以让你以原来的格式来备份二进制日志文件。mysqlbinlog可以生成静态备份,在备份一组日志文件时当备份完最后的文件时而停止。它也可以生成一种连续(live)备份,当备份到最后的日志文件时仍然保持对服务器的连接并当生成新的事件时继续复制新的事件。在连续备份操作时,mysqlbinlog会运行到连接中断为止(比如,服务器退出)或mysqlbinlog被强制中断为止。当连接中断,mysqlbinlog不会进行等待并重新进行连接,不像从属复制服务器那样。为了在服务器重启之后继续一个live备份,必须重新启动mysqlbinlog。

二进制日志文件备份要求在调用mysqlbinlog时最少要使用两个选项:
.–read-from-remote-server(或-R)选项来告诉mysqlbinlog连接到一个服务器并读取它的二进制日志文件(这类似于一个从属复制服务器连接到它的主服务器).

.–raw选项告诉mysqlbinlog以原始(二进制)格式输出,而不是文本格式。

与–read-from-remote-server一起通常还指定其它选项:–host指示服务器运行在哪里,并且可能需要指定连接选项–user和password。

与–raw联合使用的几个其它选项:.–stop-never:在读取到最后日志文件后保持对服务器的连接并继续读取新的事件。

.–stop-never-slave-server-id=id:当–stop-never被使用时mysqlbinlog报告的服务器ID,缺省值65535。这可以避免与从属服务器或其它的mysqlbinlog进程的ID冲突。

.–result-file:输出文件名的前缀

为了使用mysqlbinlog来备份服务器的二进制日志文件,你必须指定在服务器上真实存在的文件名。如果你不知道文件名,连接到服务器并使用show binary logs语句来查看当前的日志文件名。

mysql> show binary logs;
+---------------+-----------+
| Log_name      | File_size |
+---------------+-----------+
| binlog.000001 |      2530 |
+---------------+-----------+
1 row in set (0.00 sec)

使用这些信息可以使用mysqlbinlog来备份二进制日志文件到当前目录:
为了对binlog.000130到binlog.000132的日志文件进行静态备份,使用以下命令:
mysqlbinlog –read-from-remote-server –host=host_name –raw
binlog.000130 binlog.000131 binlog.000132

mysqlbinlog –read-from-remote-server –host=host_name –raw
–to-last-log binlog.000130

第一个命令显式指定每个文件名。第二个只指定了第一个日志文件并使用了–to-last-log来读取到最后一个日志文件。在这些命令之间的差异是在mysqlbinlog到达binlog.000132的末尾之前如果服务器打开了binlog.000133文件,第一个命令将不会读取,但第二个命令会读取。

为了进行live备份mysqlbinlog从binlog.000130开始备份现有的日志文件,然后保持对服务器的连接来复制生成的新事件:
mysqlbinlog –read-from-remote-server –host=host_name –raw
–stop-never binlog.000130

使用–stop-never选项,不需要指定–to-last-log来读取最后的日志文件因为这个选项是隐含的

输出文件名
在没有使用–raw选项时,mysqlbinlog会生成文本格式的输出,如果指定–result-file选项,指定将所有输出写入一个文件中。使用–raw选项时,mysqlbinlog会将服务器的每个日志文件转换成一个二进制输出文件。缺省情况下,mysqlbinlog会在当前目录中生成与源日志文件同名的文件。为了修改输出文件名,使用–result-file选项。与–raw联合使用,–result-file选项值将作为前缀来命名输出文件名。

现在对远程服务器中的binlog.000001日志文件进行备份

[mysql@localhost ~]$ mysqlbinlog --read-from-remote-server --host=192.168.1.250 --raw binlog.000001 --result-file=jy_
[mysql@localhost ~]$ ls -lrt
-rw-r-----. 1 mysql mysql    2530 11月 22 10:24 jy_binlog.000001

可以看到备份的日志文件为以jy_为前缀,其文件名为jy_binlog.000001

使用mysqldump与mysqlbinlog执行备份与还原操作
下面将介绍一个简单的例子显示如何使用mysqldump与mysqlbinlog一起来备份MySQL服务器的数据和二进制日志文件以及在数据丢失时如何使用备份来还原数据。

现在主机上的MySQL服务器的第一个二进制日志文件为binlog.000001

mysql> show binary logs;
+---------------+-----------+
| Log_name      | File_size |
+---------------+-----------+
| binlog.000001 |      2530 |
+---------------+-----------+
1 row in set (0.01 sec)

使用mysqlbinlog来对二进制日志文件执行连续备份:

[mysql@localhost ~]$ mysqlbinlog --read-from-remote-server --host=192.168.1.250 --raw  --stop-never binlog.000001

[mysql@localhost ~]$ ls -lrt

-rw-r-----. 1 mysql mysql    2530 11月 22 10:38 binlog.000001

创建了一个名为t的测试表并插入了三行记录

mysql> select * from t;
+----+----------+------+
| id | name     | date |
+----+----------+------+
|  1 | jingyong | NULL |
|  2 | yeyali   | NULL |
|  3 | huangyan | NULL |
+----+----------+------+
3 rows in set (0.00 sec)

使用mysqldump来创建一个dump文件作为对MySQL服务器的数据快照。使用–all-databases,–events和–routines来备份所有的数据,–master-data=2用来指示在dump文件中包括当前的二进制日志文件。

[mysql@localhost ~]$ mysqldump --host=192.168.1.250 --port=3306 -uroot -pabcd --all-databases --events --routines --master-data=2> dump_mysql.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[mysql@localhost ~]$ ls -lrt
-rw-r--r--. 1 mysql mysql 3290497 11月 22 10:51 dump_mysql.sql

现在删除mysql库中的表t

mysql> drop table t;
Query OK, 0 rows affected (0.18 sec)

mysql> desc t;
ERROR 1146 (42S02): Table 'mysql.t' doesn't exist

现在假设mysql库中的表t丢失了,使用最近的dump文件来还原数据:

[mysql@localhost ~]$ mysql --host=192.168.1.250 --port=3306 -uroot -pabcd  mysql

还原数据后mysql.t表就恢复了

mysql> desc t;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   |     | NULL    |       |
| name  | varchar(20) | NO   |     | NULL    |       |
| date  | date        | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

mysql> select * from t;
+----+----------+------+
| id | name     | date |
+----+----------+------+
|  1 | jingyong | NULL |
|  2 | yeyali   | NULL |
|  3 | huangyan | NULL |
+----+----------+------+
3 rows in set (0.00 sec)

现在向mysql.t表中插入一条记录并删除这条记录,然后使用备份的二进制日志文件来重新执行事件来恢复这条记录

mysql> insert into t value(4,'wenyao',NULL);
Query OK, 1 row affected (0.03 sec)

mysql> select * from t;
+----+----------+------+
| id | name     | date |
+----+----------+------+
|  1 | jingyong | NULL |
|  2 | yeyali   | NULL |
|  3 | huangyan | NULL |
|  4 | wenyao   | NULL |
+----+----------+------+
4 rows in set (0.00 sec)

mysql> delete from t where id=4;
Query OK, 1 row affected (0.13 sec)

mysql> select * from t;
+----+----------+------+
| id | name     | date |
+----+----------+------+
|  1 | jingyong | NULL |
|  2 | yeyali   | NULL |
|  3 | huangyan | NULL |
+----+----------+------+
3 rows in set (0.01 sec)

我们需要找到插入这条记录在日志文件中的开始与结束的位置

# at 3306211
#191122 11:04:34 server id 1  end_log_pos 3306323 CRC32 0x88f89864      Query   thread_id=11    exec_time=0     error_code=0
SET TIMESTAMP=1574391874/*!*/;
insert into t value(4,'wenyao',NULL)
/*!*/;
# at 3306323
#191122 11:04:34 server id 1  end_log_pos 3306354 CRC32 0x966500de      Xid = 1041
COMMIT/*!*/;
# at 3306354
#191122 11:07:26 server id 1  end_log_pos 3306419 CRC32 0x1f3e6e28      Anonymous_GTID  last_committed=160      sequence_number=161     rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 3306419
#191122 11:07:26 server id 1  end_log_pos 3306500 CRC32 0x883ecef4      Query   thread_id=11    exec_time=0     error_code=0
SET TIMESTAMP=1574392046/*!*/;
BEGIN
/*!*/;
# at 3306500
#191122 11:07:26 server id 1  end_log_pos 3306600 CRC32 0xecae0a57      Query   thread_id=11    exec_time=0     error_code=0
SET TIMESTAMP=1574392046/*!*/;
delete from t where id=4

从上面的日志文件内容可以看到插入的开始位置为3306211,结束位置为3306323

现在当前备份的二进制日志文件名为binlog.000001,重新执行事件的命令如下:

[mysql@localhost ~]$ mysqlbinlog --start-position=3306211 --stop-position=3306323 binlog.000001 | mysql --host=192.168.1.250 --port=3306 -uroot -pabcd  mysql
mysql: [Warning] Using a password on the command line interface can be insecure.

现在检查mysql.t表中的记录,可以看到被删除的这条记录恢复了。

mysql> select * from t;
+----+----------+------+
| id | name     | date |
+----+----------+------+
|  1 | jingyong | NULL |
|  2 | yeyali   | NULL |
|  3 | huangyan | NULL |
|  4 | wenyao   | NULL |
+----+----------+------+
4 rows in set (0.00 sec)

设置msyqlbinlog 服务器ID
在使用–read-from-remote-server选项来调用mysqlbinlog时,mysqlbinlog会连接到一个MySQL服务器,指定了一个服务器ID来标识它并且从该服务器获取所需要的二进制日志文件。可以使用mysqlbinlog以以下几种方式来从服务器中获取日志文件:
.对文件集指定显式的名字。对每个文件,mysqlbinlog会执行连接操作并执行binlog dump命令。服务器会发送文件并断开连接。每个文件都有一个连接。

.指定开始文件与–to-last-log选项,mysqlbinlog会执行连接并对所有的日志文件执行binlog dump命令。服务器会发送所有日志文件并断开连接

.指定开始文件与–stop-never选项(隐式实现–to-last-log选项的功能),mysqlbinlog会执行连接并对所有日志文件执行binlog dump命令。服务器会发送所有日志文件,但在发送最后一个日志文件后不会断开与服务器的连接。

只有使用–read-from-remote-server选项时,mysqlbinlog使用一个为0的server ID进行连接,它将告诉服务器在发送所请求的日志文件后断开连接。

使用–read-from-remote-server与–stop-never选项时,mysqlbinlog将使用一个非0的server ID进行连接,因此在最后的日志文件发送之后服务器不会断开连接。缺省的server ID为65535,但这个可以通过使用–stop-never-slave-server-id选项来修改。

因此,对于使用前两种方式来获取日志文件人,因为mysqlbinlog指定的server ID为0,所有服务器会断开连接,如果–stop-never选项被指定因为mysqlbinlog指定一个非0的server ID,所以服务器将不会断开连接。

 

Proudly powered by WordPress | Indrajeet by Sus Hill.