MySQL InnoDB的索引扩展

索引扩展,InnoDB通过将主键列附加到每个辅助索引中来自动扩展该索引。创建如下表结构:

mysql> CREATE TABLE t1 (
    -> i1 INT NOT NULL DEFAULT 0,
    -> i2 INT NOT NULL DEFAULT 0,
    -> d DATE DEFAULT NULL,
    -> PRIMARY KEY (i1, i2),
    -> INDEX k_d (d)
    -> ) ENGINE = InnoDB;

Query OK, 0 rows affected (0.14 sec)

表t1在列(i1,i2)上定义了主键。同时也在列(d)上定义了一个辅助索引,但InnoDB扩展了这个索引并且将它视为(d,i1,i2)来处理。

在决定如何使用以及是否使用该索引时,优化器会考虑扩展辅助索引的主键列。这可以产生更高效的查询执行计划和更好的性能。

优化器可以使用扩展的二级索引来进行ref、range和index_merge索引访问,进行松散索引扫描,进行连接和排序优化,以及进行MIN()/MAX()优化。

下面的示例将显示优化器是否使用扩展辅助索引来影响执行计划 向表t1插入以下数据:


mysql> INSERT INTO t1 VALUES (1, 1, '1998-01-01'), (1, 2, '1999-01-01'), (1, 3, '2000-01-01'), (1, 4, '2001-01-01'),
    ->(1, 5, '2002-01-01'), (2, 1, '1998-01-01'), (2, 2, '1999-01-01'), (2, 3, '2000-01-01'),  (2, 4, '2001-01-01'),
    ->(2, 5, '2002-01-01'), (3, 1, '1998-01-01'), (3, 2, '1999-01-01'), (3, 3, '2000-01-01'), (3, 4, '2001-01-01'),
    ->(3, 5, '2002-01-01'), (4, 1, '1998-01-01'), (4, 2, '1999-01-01'), (4, 3, '2000-01-01'), (4, 4, '2001-01-01'),
    ->(4, 5, '2002-01-01'), (5, 1, '1998-01-01'), (5, 2, '1999-01-01'),  (5, 3, '2000-01-01'), (5, 4, '2001-01-01'),
    ->(5, 5, '2002-01-01');
Query OK, 25 rows affected (0.05 sec)
Records: 25  Duplicates: 0  Warnings: 0

假设执行下面的查询:

SET optimizer_switch = 'use_index_extensions=off';
explain select count(*) from t1 where i1=3 and d= '2000-01-01' ;

在这种情况下,优化器不能使用主键,因为主键包含列(i1、i2),并且查询没有引用i2。相反,优化器可以使用列(d)上的辅助索引k_d,执行计划取决于是否使用扩展索引。

当优化器不考虑索引扩展时,它将索引k_d仅视为(d)

mysql> SET optimizer_switch = 'use_index_extensions=off';
Query OK, 0 rows affected (0.00 sec)

mysql> explain select count(*) from t1 where i1=3 and d= '2000-01-01' \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
   partitions: NULL
         type: ref
possible_keys: PRIMARY,k_d
          key: PRIMARY
      key_len: 4
          ref: const
         rows: 5
     filtered: 20.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

当优化器考虑到索引扩展时,它将k_d视为(d, i1, i2)。在这种情况下,它可以使用最左边的索引前缀(d, i1)来生成更好的执行计划

mysql> SET optimizer_switch = 'use_index_extensions=on';
Query OK, 0 rows affected (0.00 sec)

mysql> explain select count(*) from t1 where i1=3 and d= '2000-01-01' \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
   partitions: NULL
         type: ref
possible_keys: PRIMARY,k_d
          key: k_d
      key_len: 8
          ref: const,const
         rows: 1
     filtered: 100.00
        Extra: Using index
1 row in set, 1 warning (0.00 sec)

在这两种情况下,key表示优化器将使用辅助索引k_d,但是EXPLAIN输出显示了使用扩展索引所带来的这些改进:
.key_len从4字节变成了8字节,指示键查找使用了列d和i1,不仅仅是d。
.ref的值从const变成了const,const,因为键查找使用两个键的列而不是一个。
.rows:从5减到1,指示InnoDB将会检查更少的行来生成查询结果。
.Extra值从Using where;Using index变成了Using index。这意味着查询记录只需要使用索引而不用查询数据行记录。

可以使用show status来查看优化器在使用与不使用扩展索引时的差异:

mysql> flush table t1;
Query OK, 0 rows affected (0.01 sec)

mysql> flush status;
Query OK, 0 rows affected (0.03 sec)

上面的flush table和flush status语句用来清除表的缓存和清除状数据统计数据。

不使用索引扩展时show status产生的结果如下:

mysql> SET optimizer_switch = 'use_index_extensions=off';
Query OK, 0 rows affected (0.01 sec)

mysql> select count(*) from t1 where i1=3 and d= '2000-01-01';
+----------+
| count(*) |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec)

mysql> show status like 'handler_read%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Handler_read_first    | 0     |
| Handler_read_key      | 1     |
| Handler_read_last     | 0     |
| Handler_read_next     | 5     |
| Handler_read_prev     | 0     |
| Handler_read_rnd      | 0     |
| Handler_read_rnd_next | 0     |
+-----------------------+-------+
7 rows in set (0.00 sec)

使用索引扩展时,show status产生的结果如下,其中handler_read_next的值从5减到1,指示使用这个索引更有效率:

mysql> flush table t1;
Query OK, 0 rows affected (0.01 sec)

mysql> flush status
    -> ;
Query OK, 0 rows affected (0.02 sec)

mysql> SET optimizer_switch = 'use_index_extensions=on';
Query OK, 0 rows affected (0.00 sec)

mysql> select count(*) from t1 where i1=3 and d= '2000-01-01';
+----------+
| count(*) |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec)

mysql> show status like 'handler_read%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Handler_read_first    | 0     |
| Handler_read_key      | 1     |
| Handler_read_last     | 0     |
| Handler_read_next     | 1     |
| Handler_read_prev     | 0     |
| Handler_read_rnd      | 0     |
| Handler_read_rnd_next | 0     |
+-----------------------+-------+
7 rows in set (0.01 sec)

系统变量optimizer_switch的use_index_extensions标志允许优化器在决定如何使用InnoDB表的辅助索引时使不使用主键列。默认情况下,use_index_extensions是启用的。为了检查禁用索引扩展是否可以提高性能可以执行以下语句:

mysql> SET optimizer_switch = 'use_index_extensions=off';
Query OK, 0 rows affected (0.01 sec)

使用mysqldump以文本分隔格式来dump数据

这里将介绍如何使用mysqldump来创建文本分隔格式的dump文件。在调用mysqldump时使用–tab=dir_name选项时,使用dir_name作为输出目录并且在输出目录是为每个表生成两个文件。表名是这些文件的基础名称。对表t,文件名为t.sql和t.txt。其中.sql文件包含的是表的 create table语句。.txt文件包含的是表数据,每一条记录一行。下面的命令将数据库mysql的内容dump到/mysqldata/tmp目录中

在用mysqldump备份时候遇到1290的错误

从提示看到是因为mysql服务启用了--secure-file-priv,所以才无法执行。这个选项或系统变量用被来限制通过load data和select ... into outfile语句和load_file()函数所执行导入和导出操作的数据量。

secure_file_priv有以下设置:
.如果为空,变量不生效。没有安全设置。
.如果设置为一个目录名,服务器对导入和导出操作只对这个目录中的文件生效,但目录必须存在,服务器不会创建目录。
.如果设置NULL,服务器禁止导入和导出操作。

查看数据库当前设置:

mysql> show global variables like '%secur%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| require_secure_transport | OFF   |
| secure_auth              | ON    |
| secure_file_priv         | NULL  |
+--------------------------+-------+
3 rows in set (0.01 sec)

看到secure_file_priv当前设置为NULL,说明限制导入和导出操作。

查看配置文件my.cnf发现没有设置secure_file_priv参数,那么这个参数默认应该是为NULL。

[mysql@localhost mysql]$ cat my.cnf


[mysqld]
basedir=/mysqlsoft/mysql
datadir=/mysqldata/mysql
bind-address=*
user=mysql
port=3306
log-error=/mysqldata/mysql/mysql.err
pid-file=/mysqldata/mysql/mysqld.pid
socket = /mysqlsoft/mysql/mysql.sock
character-set-server=utf8mb4
default-storage-engine=INNODB
explicit_defaults_for_timestamp = true
innodb_flush_method=O_DIRECT
binlog_format = mixed
log-bin=/mysqldata/mysql/binlog
max_binlog_size = 100m
binlog_cache_size = 4m
server-id=1

修改配置文件my.cnf参加secure_file_priv=

[mysql@localhost mysql]$ vi my.cnf
.....
secure-file-priv=

重启mysql数据库

[root@localhost ~]# service mysqld restart
Shutting down MySQL.... SUCCESS!
Starting MySQL.. SUCCESS!

再次检查secure_file_priv参数设置

mysql> show global variables like '%secur%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| require_secure_transport | OFF   |
| secure_auth              | ON    |
| secure_file_priv         |       |
+--------------------------+-------+
3 rows in set (0.00 sec)

再执行导出操作成功

[mysql@localhost ~]$ mysqldump -uroot -pxxzx7817600 --tab=/mysqldata/tmp  mysql
mysqldump: [Warning] Using a password on the command line interface can be insecure.

数据库服务器会生成包含数据内容的.txt文件,因此它是属于运行数据库服务的系统账号。服务器使用select … into outfile来写这个文件,因此你必须有file权限才能执行这个操作,如果一个特定的.txt文件已经存在的话会出错。

数据库服务器为被dump的表发送create定义语句来mysqldump,它将被写入到.sql文件中。因此这些文件属于执行mysqldump命令的用户。

–tab选项最好只在本地服务器上执行dump。如果将它用于远程服务器,–tab选项所指定的目录必须在本地服务器和远程服务器上都要存在,燕且.txt文件会被服务器写入远程目录(数据库服务器所在主机),而.sql文件将被mysqldump写入本地目录(客户端所在主机)。

对于mysqldump –tab,服务器默认情况下将表数据写入.txt文件,每行表记录一行,列值之间使用制表符,列值周围不使用引号,换行符作为行结束符。

为了能在写入数据文件时使用不同的格式,mysqldump支持以下选项:
.–fields-terminated-by=str
列值分隔符(默认为:tab制表符)

.–fields-enclosed-by=char
将列值括在其中的字符(默认为:没有)

.–fields-optionally-enclosed-by=char
用来括起非数值列值的字符(默认为:没有)

.–fields-escaped-by=char
用于转义特殊字符的字符(默认为:没有转义)
.–lines-terminated-by=str
行终止符(默认为:新行)

根据你所为这些选项指定的值,为了让命令解释器能正确处理可能需要在命令行中为这些值使用引号或转义。另外可以以十六进制格式来指定。假设你想用双引号引用列值。为了达到这个目的应该为–fields-enclosed-by选项指定双引号。但双引号通常对于命令解释器有特定含义并且必须特定对待。例如,在Unix中,可以使用引号来指定双引号:
–fields-enclosed-by='”‘

在任何平台中,可以指定十六进制值:
–fields-enclosed-by=0x22

同时使用几个数据格式化选项是很常见的。例如,为了使用通过回车/换行符对(\r\n)作为行终止符的逗号分隔值格式来dump表,可以执行以下命令:

[mysql@localhost tmp]$ mysqldump -uroot -pxxzx7817600 --tab=/mysqldata/tmp --fields-terminated-by=, --fields-enclosed-by='"'  --lines-terminated-by=0x0d0a mysql
mysqldump: [Warning] Using a password on the command line interface can be insecure.

[mysql@localhost tmp]$ ls -lrt
总用量 3128
-rw-r--r--. 1 mysql mysql    1871 7月   9 17:54 rewrite_rules.sql
-rw-r--r--. 1 mysql mysql    1876 7月  10 15:37 columns_priv.sql
-rw-rw-rw-. 1 mysql mysql       0 7月  10 15:37 columns_priv.txt
-rw-rw-rw-. 1 mysql mysql       0 7月  10 15:37 count.txt
-rw-r--r--. 1 mysql mysql    1298 7月  10 15:37 count.sql
-rw-rw-rw-. 1 mysql mysql       7 7月  10 15:37 cs.txt
-rw-r--r--. 1 mysql mysql    1359 7月  10 15:37 cs.sql
-rw-r--r--. 1 mysql mysql    2917 7月  10 15:37 db.sql
-rw-rw-rw-. 1 mysql mysql     198 7月  10 15:37 db.txt
-rw-rw-rw-. 1 mysql mysql     114 7月  10 15:37 engine_cost.txt
-rw-r--r--. 1 mysql mysql    1630 7月  10 15:37 engine_cost.sql
-rw-rw-rw-. 1 mysql mysql       0 7月  10 15:37 event.txt
-rw-r--r--. 1 mysql mysql    3350 7月  10 15:37 event.sql
-rw-r--r--. 1 mysql mysql    1557 7月  10 15:37 func.sql
-rw-rw-rw-. 1 mysql mysql     398 7月  10 15:37 func.txt
-rw-r--r--. 1 mysql mysql    1578 7月  10 15:37 gtid_executed.sql
-rw-rw-rw-. 1 mysql mysql     964 7月  10 15:37 help_category.txt
-rw-r--r--. 1 mysql mysql    1563 7月  10 15:37 help_category.sql
-rw-r--r--. 1 mysql mysql    1471 7月  10 15:37 help_keyword.sql
-rw-rw-rw-. 1 mysql mysql    9748 7月  10 15:37 help_keyword.txt
-rw-r--r--. 1 mysql mysql    1486 7月  10 15:37 help_relation.sql
-rw-rw-rw-. 1 mysql mysql   10771 7月  10 15:37 help_relation.txt
-rw-r--r--. 1 mysql mysql    1592 7月  10 15:37 help_topic.sql
-rw-rw-rw-. 1 mysql mysql  712538 7月  10 15:37 help_topic.txt
-rw-rw-rw-. 1 mysql mysql      32 7月  10 15:37 imptest.txt
-rw-r--r--. 1 mysql mysql    1337 7月  10 15:37 imptest.sql
-rw-r--r--. 1 mysql mysql    1879 7月  10 15:37 innodb_index_stats.sql
-rw-rw-rw-. 1 mysql mysql    2647 7月  10 15:37 innodb_index_stats.txt
-rw-rw-rw-. 1 mysql mysql     395 7月  10 15:37 innodb_table_stats.txt
-rw-r--r--. 1 mysql mysql    1745 7月  10 15:37 innodb_table_stats.sql
-rw-rw-rw-. 1 mysql mysql       0 7月  10 15:37 ndb_binlog_index.txt
-rw-r--r--. 1 mysql mysql    1842 7月  10 15:37 ndb_binlog_index.sql
-rw-rw-rw-. 1 mysql mysql      76 7月  10 15:37 person.txt
-rw-r--r--. 1 mysql mysql    1571 7月  10 15:37 person.sql
-rw-r--r--. 1 mysql mysql    1420 7月  10 15:37 plugin.sql
-rw-rw-rw-. 1 mysql mysql      53 7月  10 15:37 plugin.txt
-rw-r--r--. 1 mysql mysql    3067 7月  10 15:37 proc.sql
-rw-rw-rw-. 1 mysql mysql     806 7月  10 15:37 proc.txt
-rw-rw-rw-. 1 mysql mysql       0 7月  10 15:37 procs_priv.txt
-rw-r--r--. 1 mysql mysql    1970 7月  10 15:37 procs_priv.sql
-rw-rw-rw-. 1 mysql mysql      60 7月  10 15:37 proxies_priv.txt
-rw-r--r--. 1 mysql mysql    1862 7月  10 15:37 proxies_priv.sql
-rw-rw-rw-. 1 mysql mysql     297 7月  10 15:37 server_cost.txt
-rw-r--r--. 1 mysql mysql    1530 7月  10 15:37 server_cost.sql
-rw-rw-rw-. 1 mysql mysql       0 7月  10 15:37 servers.txt
-rw-r--r--. 1 mysql mysql    1730 7月  10 15:37 servers.sql
-rw-r--r--. 1 mysql mysql    4150 7月  10 15:37 slave_master_info.sql
-rw-r--r--. 1 mysql mysql    2406 7月  10 15:37 slave_relay_log_info.sql
-rw-rw-rw-. 1 mysql mysql       0 7月  10 15:37 slave_worker_info.txt
-rw-r--r--. 1 mysql mysql    2221 7月  10 15:37 slave_worker_info.sql
-rw-r--r--. 1 mysql mysql    1342 7月  10 15:37 t.sql
-rw-rw-rw-. 1 mysql mysql      52 7月  10 15:37 t.txt
-rw-rw-rw-. 1 mysql mysql       3 7月  10 15:37 t1.txt
-rw-r--r--. 1 mysql mysql    1291 7月  10 15:37 t1.sql
-rw-r--r--. 1 mysql mysql    1306 7月  10 15:37 t2.sql
-rw-rw-rw-. 1 mysql mysql       4 7月  10 15:37 t2.txt
-rw-rw-rw-. 1 mysql mysql     162 7月  10 15:37 tables_priv.txt
-rw-r--r--. 1 mysql mysql    2068 7月  10 15:37 tables_priv.sql
-rw-rw-rw-. 1 mysql mysql       0 7月  10 15:37 test.txt
-rw-r--r--. 1 mysql mysql    1304 7月  10 15:37 test.sql
-rw-r--r--. 1 mysql mysql    1487 7月  10 15:37 time_zone.sql
-rw-rw-rw-. 1 mysql mysql   11122 7月  10 15:37 time_zone.txt
-rw-rw-rw-. 1 mysql mysql       0 7月  10 15:37 time_zone_leap_second.txt
-rw-r--r--. 1 mysql mysql    1493 7月  10 15:37 time_zone_leap_second.sql
-rw-r--r--. 1 mysql mysql    1435 7月  10 15:37 time_zone_name.sql
-rw-rw-rw-. 1 mysql mysql   41245 7月  10 15:37 time_zone_name.txt
-rw-r--r--. 1 mysql mysql    1548 7月  10 15:37 time_zone_transition.sql
-rw-rw-rw-. 1 mysql mysql 1998482 7月  10 15:37 time_zone_transition.txt
-rw-r--r--. 1 mysql mysql    1670 7月  10 15:37 time_zone_transition_type.sql
-rw-rw-rw-. 1 mysql mysql  161600 7月  10 15:37 time_zone_transition_type.txt
-rw-rw-rw-. 1 mysql mysql     878 7月  10 15:37 user.txt
-rw-r--r--. 1 mysql mysql    4390 7月  10 15:37 user.sql
-rw-r--r--. 1 mysql mysql    1576 7月  10 15:37 general_log.sql
-rw-r--r--. 1 mysql mysql    1757 7月  10 15:37 slow_log.sql


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@localhost tmp]$ cat t.sql
-- MySQL dump 10.13  Distrib 5.7.26, for Linux (x86_64)
--
-- Host: localhost    Database: mysql
-- ------------------------------------------------------
-- Server version       5.7.26-log

/*!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 */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `t`
--

DROP TABLE IF EXISTS `t`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `name` varchar(20) NOT NULL,
  `date` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
/*!40101 SET character_set_client = @saved_cs_client */;

/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!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 2020-07-10 15:37:05

[mysql@localhost tmp]$ cat t.txt
"1","jingyong",\N
"2","yeyali",\N
"3","huangyan",\N
"4","wenyao",\N

如果使用任何数据格式化选项来转储表数据,则在稍后重新加载数据文件时需要指定相同的格式,以确保正确地解释文件内容。

重新加载文本分隔格式的备份文件
对于使用mysqldump –tab命令所生成的备份,每个表在输出目录中都有一个包含create table语句的.sql文件和一个包含表数据的.txt文件。为了重新加载表,首先进入到输出目录。然后使用msyql命令来处理.sql文件创建一个空表,再执行mysqlimport来处理.txt文件加载数据:

[mysql@localhost tmp]$ mysql -uroot -pxxzx7817600  mysql < t.sql
mysql: [Warning] Using a password on the command line interface can be insecure.

[mysql@localhost tmp]$ mysqlimport -uroot -pxxzx7817600  --fields-terminated-by=, --fields-enclosed-by='"'  --lines-terminated-by=0x0d0a mysql /mysqldata/tmp/t.txt
mysqlimport: [Warning] Using a password on the command line interface can be insecure.
mysql.t: Records: 4  Deleted: 0  Skipped: 0  Warnings: 0


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)

另一种替mysqlimport来加载数据文件的方式是使用load data infile语句:

mysql> load data infile '/mysqldata/tmp/t.txt' into table t fields terminated by ',' enclosed by '"' lines terminated by 0x0d0a;
Query OK, 4 rows affected (0.03 sec)
Records: 4  Deleted: 0  Skipped: 0  Warnings: 0

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)

使用mysqldump以SQL格式来dump数据

Using mysqldump for Backups
使用mysqldump工具可以以以下几种方式来生成dump文件:
.作为备份在数据丢失的情况下来执行数据恢复
.作为源数据用来配置复制从服务器
.作为源数据用来进行实验:
-为了复制数据库而不用改变源数据
-为了测试潜在的升级后不兼容的问题

mysqldump生成两种类型的输出,这依赖于是否使用–tab选项:
.不使用–tab选项,mysqldump将输出SQL语句到标准输出。这类输出由创建dump对象(数据库,表,存储过程等)的create语句组成,并且包含向表加载数据的insert语句。输出可以被保存大文件中并且之后可以使用mysql来重新加载来重新创建被dump的对象。有一些选项可用于修改SQL语句的格式,以及控制转储哪些对象。

.使用–tab选项,mysqldump将为每个被dump的对象生成两个输出文件。服务器以制表符分隔的文本形式写入一个文件用来存储表记录,每条记录一行。这个文件名为tbl_name.txt被保存输出目录中。服务器还会在输出目录中生成一个名为tbl_name.sql的文件用来存储create table语句。

使用mysqldump以SQL格式来dump数据
默认情况下,mysqldump以SQL语句来作为标准输出。可以将输出保存到文件中:

为了dump所有数据库,可以使用–all-databases选项来调用mysqldump

[mysql@localhost ~]$ mysqldump -uroot -pabcd1234 --all-databases > all_db_dump.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.

为了dump指定的数据库,可以使用–databases选项来指定

[mysql@localhost ~]$ mysqldump -uroot -pabcd1234 --databases mysql query_rewrite > dump.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.

–databases选项会让命令行中的所有名字作为数据库名字来对待。不使用这个选项,mysqldump将第一个名字作为数据库名后面的作为表名对待。

使用–all-databases或–databases选项,mysqldump在dump每个数据库之前会写入create database和use语句。这确保了当dump文件被重新加载时,如果不存在数据库时会创建数据库并使它作为默认数据库使用数据库的内容被加载到相同的数据库中。如果想让dump文件在重建数据之前强制删除每个数据库,可以使用–add-drop-database选项。在这种情况下,mysqldump会在每个create database语句之前写一个drop database语句。

为了dump单个数据库,在命令行中指定数据库名:

[mysql@localhost ~]$ mysqldump -uroot -pabcd1234 --databases mysql > dump_mysql.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.

在dump单个数据库的情况下,可以忽略–databases选项:

[mysql@localhost ~]$ mysqldump -uroot -pabcd1234  mysql > dump_mysql_1.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.



[mysql@localhost ~]$ more dump_mysql.sql
-- MySQL dump 10.13  Distrib 5.7.26, for Linux (x86_64)
--
-- Host: localhost    Database: mysql
-- ------------------------------------------------------
-- Server version       5.7.26-log

/*!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 */;
/*!40101 SET NAMES utf8 */;
/*!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: `mysql`
--

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `mysql` /*!40100 DEFAULT CHARACTER SET utf8mb4 */;

USE `mysql`;

--
-- Table structure for table `columns_priv`
--

[mysql@localhost ~]$ more dump_mysql_1.sql
-- MySQL dump 10.13  Distrib 5.7.26, for Linux (x86_64)
--
-- Host: localhost    Database: mysql
-- ------------------------------------------------------
-- Server version       5.7.26-log

/*!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 */;
/*!40101 SET NAMES utf8 */;
/*!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 */;

--
-- Table structure for table `columns_priv`
--

上面两个命令之间的差异就在于不使用–databases选项时,dump输出不会包含create database或use语句。这有几种含义:
.当重新加载dump文件时,你必须指定一个默认数据库名因此服务器就会知道那个数据库会被加载。
.为了重新加载,可以指定一个不同于原始数据库名的数据库名,这能让你加载数据到不同的数据库中。
.如果被加载的数据库不存在,你必须先创建数据库。
.因为输出将不会包含create database语句,–add-drop-database选项没有影响。如果使用它,将不会生成drop database语句。

为了dump一个数据库中的指定表,在命令行中指定数据库名后指定表名:

[mysql@localhost ~]$ mysqldump -uroot -pabcd1234  mysql t t1 t2 > dump_tables.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[mysql@localhost ~]$ cat dump_tables.sql
-- MySQL dump 10.13  Distrib 5.7.26, for Linux (x86_64)
--
-- Host: localhost    Database: mysql
-- ------------------------------------------------------
-- Server version       5.7.26-log

/*!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 */;
/*!40101 SET NAMES utf8 */;
/*!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 */;

--
-- Table structure for table `t`
--

DROP TABLE IF EXISTS `t`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `name` varchar(20) NOT NULL,
  `date` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `t`
--

LOCK TABLES `t` WRITE;
/*!40000 ALTER TABLE `t` DISABLE KEYS */;
INSERT INTO `t` VALUES (1,'jingyong',NULL),(2,'yeyali',NULL),(3,'huangyan',NULL),(4,'wenyao',NULL);
/*!40000 ALTER TABLE `t` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `t1`
--

DROP TABLE IF EXISTS `t1`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `t1` (
  `c1` char(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `t1`
--

LOCK TABLES `t1` WRITE;
/*!40000 ALTER TABLE `t1` DISABLE KEYS */;
INSERT INTO `t1` VALUES ('xy');
/*!40000 ALTER TABLE `t1` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `t2`
--

DROP TABLE IF EXISTS `t2`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `t2` (
  `i` int(10) NOT NULL,
  PRIMARY KEY (`i`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `t2`
--

LOCK TABLES `t2` WRITE;
/*!40000 ALTER TABLE `t2` DISABLE KEYS */;
INSERT INTO `t2` VALUES (0),(1);
/*!40000 ALTER TABLE `t2` 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 2020-07-10 17:19:42

重新加载SQL格式备份
为了重新加载由mysqldump工具生成的由SQL语句组成的dump文件,使用这个dump文件作为mysql客户端的输入。如果dump文件是由使用–all-databases或–databases选项的mysqldump命令所生成,它将包含create database和use语句并且不需要为加载的数据指定默认数据库。

[mysql@localhost ~]$ mysql -uroot -pabcd1234  mysql  < dump_tables.sql
mysql: [Warning] Using a password on the command line interface can be insecure.

一种替代的方式是通过在mysql命令提示符下使用source命令:

mysql> source /var/lib/mysql/dump_tables.sql
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.10 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.12 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 4 rows affected (0.02 sec)
Records: 4  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.08 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.12 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 1 row affected (0.02 sec)

Query OK, 0 rows affected (0.02 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.07 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.12 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 2 rows affected (0.02 sec)
Records: 2  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.04 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 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> select * from t1;
+------+
| c1   |
+------+
| xy   |
+------+
1 row in set (0.00 sec)

mysql> select * from t2;
+---+
| i |
+---+
| 0 |
| 1 |
+---+
2 rows in set (0.00 sec)

如果dump文件是单数据库dump且不包含create database和use语句,那么首先需要创建数据库(必须的):

shell> mysqladmin create db1

然后在加载dump文件时指定数据库名:

shell> mysql db1 < dump.sql

另一种方式,在mysql提示符中,创建数据库,选它作为默认数据库,然后加载dump文件:

mysql> create database if not exists db1;
mysql> use db1;
mysql> source /var/lib/mysql/dump_tables.sql
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.10 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.12 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 4 rows affected (0.02 sec)
Records: 4  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.08 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.12 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 1 row affected (0.02 sec)

Query OK, 0 rows affected (0.02 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.07 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.12 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 2 rows affected (0.02 sec)
Records: 2  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.04 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysqldump Got error 1290

在用mysqldump备份时候遇到1290的错误

[mysql@localhost ~]$ mysqldump -uroot -pxxzx7817600 --tab=/mysqldata/tmp  query_rewrite
mysqldump: [Warning] Using a password on the command line interface can be insecure.
mysqldump: Got error: 1290: The MySQL server is running with the --secure-file-priv option so it cannot execute this statement when executing 'SELECT INTO OUTFILE'

从提示看到是因为mysql服务启用了–secure-file-priv,所以才无法执行。这个选项或系统变量用被来限制通过load data和select … into outfile语句和load_file()函数所执行导入和导出操作的数据量。

secure_file_priv有以下设置:
.如果为空,变量不生效。没有安全设置。
.如果设置为一个目录名,服务器对导入和导出操作只对这个目录中的文件生效,但目录必须存在,服务器不会创建目录。
.如果设置NULL,服务器禁止导入和导出操作。

查看数据库当前设置:

mysql> show global variables like '%secur%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| require_secure_transport | OFF   |
| secure_auth              | ON    |
| secure_file_priv         | NULL  |
+--------------------------+-------+
3 rows in set (0.01 sec)

看到secure_file_priv当前设置为NULL,说明限制导入和导出操作。

查看配置文件my.cnf发现没有设置secure_file_priv参数,那么这个参数默认应该是为NULL。

[mysql@localhost mysql]$ cat my.cnf


[mysqld]
basedir=/mysqlsoft/mysql
datadir=/mysqldata/mysql
bind-address=*
user=mysql
port=3306
log-error=/mysqldata/mysql/mysql.err
pid-file=/mysqldata/mysql/mysqld.pid
socket = /mysqlsoft/mysql/mysql.sock
character-set-server=utf8mb4
default-storage-engine=INNODB
explicit_defaults_for_timestamp = true
innodb_flush_method=O_DIRECT
binlog_format = mixed
log-bin=/mysqldata/mysql/binlog
max_binlog_size = 100m
binlog_cache_size = 4m
server-id=1

修改配置文件my.cnf参加secure_file_priv=

[mysql@localhost mysql]$ vi my.cnf
.....
secure-file-priv=

重启mysql数据库

[root@localhost ~]# service mysqld restart
Shutting down MySQL.... SUCCESS!
Starting MySQL.. SUCCESS!

再次检查secure_file_priv参数设置

mysql> show global variables like '%secur%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| require_secure_transport | OFF   |
| secure_auth              | ON    |
| secure_file_priv         |       |
+--------------------------+-------+
3 rows in set (0.00 sec)

再执行导出操作成功

[mysql@localhost ~]$ mysqldump -uroot -pxxzx7817600 --tab=/mysqldata/tmp  mysql
mysqldump: [Warning] Using a password on the command line interface can be insecure.

MySQL Version Tokens

MySQL 5.7.8或更高版本的发行版包括版本令牌,该特性支持创建和同步服务器令牌,应用程序可以使用这些令牌来防止访问不正确或过时的数据。

版本令牌接口具有这些特征:
.版本令牌是由用作键或标识符的名称和一个值组成的对
.版本令牌可以被锁定。应用程序可以使用令牌锁向其他协作应用程序表明正在使用令牌,不应该修改它们。
.每个服务器都建立版本令牌列表;例如,指定服务器分配或操作状态。此外,与服务器通信的应用程序可以注册自己的令牌列表,这些令牌表示它需要服务器处于的状态。应用程序发送到不处于所需状态的服务器的SQL语句将产生错误。这是给应用程序的一个信号,它应该寻找另一个处于所需状态的服务器来接收SQL语句。

以下部分描述了版本令牌的组件,讨论了如何安装和使用它,并为其组件提供参考信息。

版本标识组件
名为version_token的服务器端插件持有与服务器关联的版本令牌列表,并订阅语句执行事件的通知。version_token插件使用审计插件API来监视来自客户端的传入语句,并将每个客户端特定于会话的版本令牌列表与服务器版本令牌列表进行匹配。如果存在匹配,插件允许语句通过,服务器继续处理它。否则,插件将向客户端返回一个错误,语句将失败。

一组用户定义函数(udf)提供了一个sql级别的API,用于操作和检查插件维护的服务器版本令牌列表。调用任何的令牌udf版本

系统变量允许客户端指定注册所需服务器状态的版本令牌列表。如果客户端发送语句时服务器处于不同的状态,则客户端接收到一个错误

安装或卸载版本令牌

这里介绍如何安装或卸载版本令牌,这些令牌是在包含插件和用户定义函数的插件库文件中实现的。有关安装或卸载插件和udf的一般信息要使服务器可用,插件库文件必须位于MySQL插件目录中(由plugin_dir系统变量命名的目录)。如果需要,在服务器启动时设置plugin_dir的值,告诉服务器插件目录的位置

插件库的基本名是version_token。文件名后缀因平台而异(例如,对于Unix和类Unix系统,.dll为Windows).

要安装版本令牌插件和udf,请使用install plugin并创建函数语句(根据需要调整.so后缀):

mysql> INSTALL PLUGIN version_tokens SONAME 'version_token.so';
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    39
Current database: mysql

Query OK, 0 rows affected (0.07 sec)

mysql> CREATE FUNCTION version_tokens_set RETURNS STRING SONAME 'version_token.so';
Query OK, 0 rows affected (0.02 sec)

mysql> CREATE FUNCTION version_tokens_show RETURNS STRING SONAME 'version_token.so';
Query OK, 0 rows affected (0.02 sec)

mysql> CREATE FUNCTION version_tokens_edit RETURNS STRING SONAME 'version_token.so';
Query OK, 0 rows affected (0.02 sec)

mysql> CREATE FUNCTION version_tokens_delete RETURNS STRING SONAME 'version_token.so';
CREATE FUNCTION version_tokens_lock_shared RETURNS STRING SONAME 'version_token.so';
Query OK, 0 rows affected (0.02 sec)

mysql> CREATE FUNCTION version_tokens_lock_shared RETURNS STRING SONAME 'version_token.so';
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE FUNCTION version_tokens_lock_exclusive RETURNS STRING SONAME 'version_token.so';
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE FUNCTION version_tokens_unlock RETURNS STRING SONAME 'version_token.so';
Query OK, 0 rows affected (0.03 sec)

您必须安装udf来管理服务器的版本令牌列表,但是您还必须安装插件,因为没有它udf将无法正常工作。

如果在主复制服务器上使用插件和udf,也要将它们安装在所有从服务器上,以避免复制问题

如前所述,一旦安装完成,版本令牌插件和udf将一直保持安装状态,直到卸载为止。要删除它们,使用UNINSTALL插件和DROP FUNCTION语句:

mysql> uninstall plugin version_tokens;
Query OK, 0 rows affected, 1 warning (0.02 sec)

mysql> drop function version_tokens_set;
drop function version_tokens_show;
Query OK, 0 rows affected (0.02 sec)

mysql> drop function version_tokens_show;
drop function version_tokens_edit;
Query OK, 0 rows affected (0.00 sec)

mysql> drop function version_tokens_edit;
Query OK, 0 rows affected (0.00 sec)

mysql> drop function version_tokens_delete;
Query OK, 0 rows affected (0.01 sec)

mysql> drop function version_tokens_lock_shared;
Query OK, 0 rows affected (0.00 sec)

mysql> drop function version_tokens_lock_exclusive;
Query OK, 0 rows affected (0.00 sec)

mysql> drop function version_tokens_unlock;
Query OK, 0 rows affected (0.02 sec)

使用版本标记
版本令牌可能有用的一个场景是,系统访问MySQL服务器集合,但是需要通过监视它们并根据负载变化调整服务器分配来管理它们,以实现负载平衡。这样一个系统包括这些组件
.要管理的MySQL服务器集合
.与服务器通信并将它们组织成高可用性组的管理或管理应用程序。组有不同的用途,每个组中的服务器可能有不同的分配。某个组内的服务器的分配可以随时更改
.访问服务器以检索和更新数据的客户机应用程序,根据分配给它们的用途选择服务器。例如,客户端不应该向只读服务器发送更新

版本令牌允许根据分配对服务器访问进行管理,而不需要客户端重复查询服务器的分配
.管理应用程序执行服务器分配并在每个服务器上建立版本令牌以反映其分配。应用程序缓存此信息以提供对其的中央访问点。如果在某个时候管理应用程序需要更改服务器分配(例如,将其从允许写改为仅允许读),则它将更改服务器的版本令牌列表并更新其缓存。

.为了提高性能,客户端应用程序从管理应用程序获取缓存信息,使它们不必为每个语句检索关于服务器分配的信息。基于它将发出的语句类型(例如,读与写),客户端选择适当的服务器并连接到它

.此外,客户端向服务器发送自己的客户端特定版本的令牌来注册它需要的服务器分配。对于客户端发送到服务器的每个语句,服务器将自己的令牌列表与客户端令牌列表进行比较。如果服务器令牌列表包含客户端令牌列表中所有具有相同值的令牌,则存在匹配,服务器执行该语句

另一方面,可能管理应用程序更改了服务器分配及其版本令牌列表。在这种情况下,新的服务器分配现在可能与客户端需求不兼容。服务器和客户端令牌列表之间的令牌不匹配,服务器返回一个错误作为对语句的应答。这指示客户机从管理应用程序缓存中刷新其版本令牌信息,并选择要与之通信的新服务器。

检测版本令牌错误和选择新服务器的客户端逻辑可以通过不同的方式实现:
.客户端可以自己处理所有版本令牌注册、不匹配检测和连接切换
.这些操作的逻辑可以在管理客户端和MySQL服务器之间连接的连接器中实现。这样的连接器可以处理错配错误检测和语句重新发送本身,也可以将错误传递给应用程序,并将其留给应用程序重新发送语句。

下面的例子以更具体的形式说明了前面的讨论。
当版本令牌在给定服务器上初始化时,服务器的版本令牌列表为空。通过调用用户定义函数(udf)来执行令牌列表维护。调用任何版本令牌udf都需要超级特权,因此具有该特权的管理或管理应用程序需要修改令牌列表。

假设一个管理应用程序与一组服务器通信,客户端查询这些服务器以访问雇员和产品数据库(分别名为emp和prod)。所有服务器都被允许处理数据检索语句,但只有一部分服务器被允许进行数据库更新。为了在特定于数据库的基础上处理这个问题,管理应用程序在每个服务器上建立一个版本令牌列表。在给定服务器的令牌列表中,令牌名称表示数据库名称,读写令牌值取决于数据库是否必须以只读方式使用,或者是否可以进行读写。

客户端应用程序通过设置系统变量来注册它们需要服务器匹配的版本令牌列表。变量设置是在客户端特定的基础上进行的,因此不同的客户端可以注册不同的需求。默认情况下,客户端令牌列表是空的,它匹配任何服务器令牌列表。当客户端将其令牌列表设置为非空值时,匹配可能成功也可能失败,这取决于服务器版本令牌列表。

为了定义服务器的版本令牌列表,管理应用程序调用version_token_set() UDF。(稍后将介绍用于修改和显示令牌列表的udf。)例如,应用程序可能将这些语句发送到三个服务器组成的组
服务器1:

mysql> SELECT version_tokens_set('emp=read;prod=read');
+------------------------------------------+
| version_tokens_set('emp=read;prod=read') |
+------------------------------------------+
| 2 version tokens set.                    |
+------------------------------------------+
1 row in set (0.03 sec)

服务器2:

mysql> SELECT version_tokens_set('emp=write;prod=read');
+-------------------------------------------+
| version_tokens_set('emp=write;prod=read') |
+-------------------------------------------+
| 2 version tokens set.                     |
+-------------------------------------------+
1 row in set (0.00 sec)

服务器3:

mysql> SELECT version_tokens_set('emp=read;prod=write');
+-------------------------------------------+
| version_tokens_set('emp=read;prod=write') |
+-------------------------------------------+
| 2 version tokens set.                     |
+-------------------------------------------+
1 row in set (0.00 sec)

在每种情况下,令牌列表都被指定为以分号分隔的名称=值对列表。产生的令牌列表值导致这些服务器连接:
.任何服务器都会接受对两个数据库中的任意一个进行读取
.只有服务器2接受对emp数据库的更新
.只有服务器3接受对prod数据库的更新

除了为每个服务器分配一个版本令牌列表外,管理应用程序还维护一个反映服务器分配的缓存。

在与服务器通信之前,客户机应用程序与管理应用程序进行联系,并检索关于服务器分配的信息。然后客户端根据这些分配选择服务器。假设客户机希望同时执行对emp数据库的读写操作。根据前面的分配,只有服务器2合格。客户机连接到服务器2,并通过设置
version_tokens_session系统变量在服务器2上注册服务器需求:

mysql> SET @@session.version_tokens_session = 'emp=write';
Query OK, 0 rows affected (0.00 sec)

对于客户机发送到服务器2的后续语句,服务器将自己的版本令牌列表与客户机列表进行比较,以检查它们是否匹配。如果是,则语句正常执行:

mysql> UPDATE emp.employee SET salary = salary * 1.1 WHERE id = 4981;
Query OK, 1 row affected (0.07 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> SELECT last_name, first_name FROM emp.employee WHERE id = 4981;
+-----------+------------+
| last_name | first_name |
+-----------+------------+
| Smith | Abe |
+-----------+------------+
1 row in set (0.01 sec)

服务器和客户端版本令牌列表之间的差异可能以两种方式出现:
.version_tokens_session值中的令牌名称在服务器令牌列表中不存在。在这种情况下会发生ER_VTOKEN_PLUGIN_TOKEN_NOT_FOUND错误
.version_tokens_session值中的令牌值与服务器令牌列表中相应令牌的值不同。在这种情况下将出现ER_VTOKEN_PLUGIN_TOKEN_MISMATCH错误

只要服务器2的分配没有改变,客户端就会继续使用它进行读写。但是,假设管理应用程序希望更改服务器分配,以便emp数据库的写操作必须发送到服务器1而不是服务器2。为此,它使用version_tokens_edit()修改两个服务器上的emp令牌值(并更新其服务器分配缓存):
服务器1:

mysql> SELECT version_tokens_edit('emp=write');
+----------------------------------+
| version_tokens_edit('emp=write') |
+----------------------------------+
| 1 version tokens updated.        |
+----------------------------------+
1 row in set (0.00 sec)

服务器2:

mysql> SELECT version_tokens_edit('emp=read');
+---------------------------------+
| version_tokens_edit('emp=read') |
+---------------------------------+
| 1 version tokens updated.       |
+---------------------------------+
1 row in set (0.00 sec)

version_tokens_edit()修改服务器令牌列表中的指定令牌而其它的令牌不会改变。

当客户机下一次向服务器2发送一条语句时,它自己的令牌列表将不再与服务器令牌列表匹配,并出现一个错误

mysql> UPDATE emp.employee SET salary = salary * 1.1 WHERE id = 4982;
ERROR 3136 (42000): Version token mismatch for emp. Correct value read

在这种情况下,客户端应该联系管理应用程序以获得关于服务器分配的更新信息,选择一个新服务器,并将失败的语句发送到新服务器

注意:每个客户端必须与版本令牌进行协作,仅根据它在给定服务器上注册的令牌列表发送语句。例如,如果客户端注册了一个’emp=read’的令牌列表,版本令牌中没有任何内容可以阻止客户端发送emp数据库的更新。客户本身必须避免这样做

对于从客户机接收到的每个语句,服务器隐式地使用锁,如下所示:
.为客户端令牌列表(即version_tokens_session值)中命名的每个令牌获取一个共享锁
.执行服务器和客户端令牌列表之间的比较
.根据比较结果执行语句或产生错误
.释放锁

服务器使用共享锁,以便可以在不阻塞的情况下对多个会话进行比较,同时防止对任何试图在操作服务器令牌列表中具有相同名称的令牌之前获取独占锁的会话的令牌进行更改

前面的例子只使用了版本标记插件库中包含的一些用户定义,但是还有其他的。一组udf允许对服务器的版本令牌列表进行操作和检查。另一组udf允许锁定和解锁版本标记

这些udf允许创建、更改、删除和检查服务器的版本标记列表:
.version_tokens_set()完全替换当前列表并分配一个新列表。参数是一个以分号分隔的名称=值对列表。
.version_tokens_edit()支持对当前列表进行部分修改。它可以添加新的令牌或更改现有令牌的值。参数是一个以分号分隔的名称=值对列表
.version_tokens_delete()从当前列表中删除令牌。参数是一个用分号分隔的令牌名称列表
.version_tokens_show()显示当前令牌列表。不需要任何论证

这些函数中的每一个,如果成功,将返回一个指示操作发生的二进制字符串。下面的示例建立服务器令牌列表,通过添加新令牌对其进行修改,删除一些令牌,并显示生成的令牌列表:

mysql> SELECT version_tokens_set('tok1=a;tok2=b');
+-------------------------------------+
| version_tokens_set('tok1=a;tok2=b') |
+-------------------------------------+
| 2 version tokens set.               |
+-------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT version_tokens_edit('tok3=c');
+-------------------------------+
| version_tokens_edit('tok3=c') |
+-------------------------------+
| 1 version tokens updated.     |
+-------------------------------+
1 row in set (0.00 sec)

mysql> SELECT version_tokens_delete('tok2;tok1');
+------------------------------------+
| version_tokens_delete('tok2;tok1') |
+------------------------------------+
| 2 version tokens deleted.          |
+------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT version_tokens_show();
+-----------------------+
| version_tokens_show() |
+-----------------------+
| tok3=c;               |
+-----------------------+
1 row in set (0.00 sec)

如果令牌列表格式不正确,就会出现警告:

mysql> SELECT version_tokens_set('tok1=a; =c');
+----------------------------------+
| version_tokens_set('tok1=a; =c') |
+----------------------------------+
| 1 version tokens set.            |
+----------------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> show warnings \G
*************************** 1. row ***************************
  Level: Warning
   Code: 42000
Message: Invalid version token pair encountered. The list provided is only partially updated.
1 row in set (0.00 sec)

如前所述,版本标记是使用一个以分号分隔的名称=值对列表来定义的。考虑一下version_tokens_set()的调用:

mysql> SELECT version_tokens_set('tok1=b;;; tok2= a = b ; tok1 = 1\'2 3"4');
+---------------------------------------------------------------+
| version_tokens_set('tok1=b;;; tok2= a = b ; tok1 = 1\'2 3"4') |
+---------------------------------------------------------------+
| 3 version tokens set.                                         |
+---------------------------------------------------------------+
1 row in set (0.00 sec)

版本标记对参数的解释如下:
.名称和值周围的空白将被忽略。允许在名称和值中使用空格。(对于version_tokens_delete(),它接受一个没有值的名称列表,名称周围的空白将被忽略。)
.没有报价机制
.令牌的顺序并不重要,除非令牌列表包含给定令牌名称的多个实例,否则最后一个值优先于前面的值

根据这些规则,前面的version_tokens_set()调用会产生一个令牌列表,其中包含两个令牌:tok1的值是1’2 3’4,tok2的值是a = b。

mysql> SELECT version_tokens_show();
+--------------------------+
| version_tokens_show()    |
+--------------------------+
| tok2=a = b;tok1=1'2 3"4; |
+--------------------------+
1 row in set (0.01 sec)

如果令牌列表包含两个令牌,为什么version_tokens_set()返回设置的值3版本令牌?这是因为原来的令牌列表包含两个tok1定义,而第二个定义替换了第一个定义。

版本标记令牌操作udf将这些约束放在令牌名称和值上:
.令牌名称不能包含=或;字符,最大长度为64个字符
.令牌值不能包含;字符。值的长度受到max_allowed_packet系统变量的值的限制
.版本令牌将令牌名称和值视为二进制字符串,因此比较是区分大小写的

版本令牌还包括一组udf,允许对令牌进行锁定和解锁:
.version_tokens_lock_exclusive()获得独占的版本令牌锁。它接受一个或多个锁名和超时值的列表
.version_tokens_lock_shared()获得共享版本令牌锁。它接受一个或多个锁名和超时值的列表
.version_tokens_unlock()释放版本令牌锁(独占和共享)。不需要任何论证

每个锁定函数都返回非零表示成功。否则,将发生错误:

mysql> SELECT version_tokens_lock_shared('lock1', 'lock2', 0);
+-------------------------------------------------+
| version_tokens_lock_shared('lock1', 'lock2', 0) |
+-------------------------------------------------+
| 1 |
+-------------------------------------------------+
mysql> SELECT version_tokens_lock_shared(NULL, 0);
ERROR 3131 (42000): Incorrect locking service lock name '(null)'.

使用版本标记锁定功能的锁定是建议的;申请必须同意合作。

可以锁定不存在的令牌名称。这不会创建令牌。

对于版本令牌锁定函数,令牌名称参数完全按照指定的方式使用。周围的空白不被忽略,并且=和;字符是允许的。这是因为令牌只是像传递
给锁定服务一样传递要锁定的令牌名称。

版本标记引用

版本标记功能
版本令牌插件库包含几个用户定义的函数。一组udf允许对服务器的版本令牌列表进行操作和检查。另一组udf允许锁定和解锁版本标记。调用任何版本令牌UDF都需要超级特权。

下面的udf允许创建、更改、删除和检查服务器的版本令牌列表:
.version_tokens_delete (name_list)
使用name_list参数从服务器的版本令牌列表中删除令牌,并返回指示操作结果的二进制字符串。name_list是要删除的版本令牌名称的分号分隔列表。

mysql> SELECT version_tokens_delete('tok1;tok3');
+------------------------------------+
| version_tokens_delete('tok1;tok3') |
+------------------------------------+
| 2 version tokens deleted.          |
+------------------------------------+
1 row in set (0.00 sec)

从MySQL 5.7.9开始,NULL参数被视为空字符串,这对令牌列表没有影响。
version_tokens_delete()删除其参数中指定的标记(如果它们存在的话)。(删除不存在的令牌不是错误。)要在不知道列表中有哪些令牌的情况下完全清除令牌列表,请将NULL或不包含令牌的字符串传递给version_tokens_set():

mysql> SELECT version_tokens_set(NULL);
+------------------------------+
| version_tokens_set(NULL)     |
+------------------------------+
| Version tokens list cleared. |
+------------------------------+
1 row in set (0.00 sec)

mysql> SELECT version_tokens_set('');
+------------------------------+
| version_tokens_set('')       |
+------------------------------+
| Version tokens list cleared. |
+------------------------------+
1 row in set (0.00 sec)

.version_tokens_edit (token_list)
使用token_list参数修改服务器的版本令牌列表,并返回指示操作结果的二进制字符串。token_list是一个以分号分隔的名称=值对列表,它指定要定义的每个令牌的名称及其值。如果存在令牌,则使用给定的值更新其值。如果标记不存在,则使用给定的值创建它。如果参数为NULL或字符串不包含令牌,则令牌列表保持不变。

mysql> SELECT version_tokens_set('tok1=value1;tok2=value2');
+-----------------------------------------------+
| version_tokens_set('tok1=value1;tok2=value2') |
+-----------------------------------------------+
| 2 version tokens set.                         |
+-----------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT version_tokens_edit('tok2=new_value2;tok3=new_value3');
+--------------------------------------------------------+
| version_tokens_edit('tok2=new_value2;tok3=new_value3') |
+--------------------------------------------------------+
| 2 version tokens updated.                              |
+--------------------------------------------------------+
1 row in set (0.00 sec)

.version_tokens_set (token_list)
用token_list参数中定义的令牌替换服务器的版本令牌列表,并返回指示操作结果的二进制字符串。token_list是一个用分号分隔的名称=值对列表,指定要定义的每个令牌的名称及其值。如果参数为空或字符串不包含令牌,则清除令牌列表。

mysql> SELECT version_tokens_set('tok1=value1;tok2=value2');
+-----------------------------------------------+
| version_tokens_set('tok1=value1;tok2=value2') |
+-----------------------------------------------+
| 2 version tokens set.                         |
+-----------------------------------------------+
1 row in set (0.00 sec)

.version_tokens_show()
以二进制字符串的形式返回服务器的版本标记列表,其中包含一个以分号分隔的名称=值对列表。

mysql> SELECT version_tokens_show();
+--------------------------+
| version_tokens_show()    |
+--------------------------+
| tok2=value2;tok1=value1; |
+--------------------------+
1 row in set (0.00 sec)

下面的udf允许锁定和解锁版本标记:
.version_tokens_lock_exclusive (token_name [token_name]…超时)
获取一个或多个版本令牌(按名称指定为字符串)上的独占锁,如果未在给定的超时值内获取锁,则超时并报错

mysql> SELECT version_tokens_lock_exclusive('lock1', 'lock2', 10);
+-----------------------------------------------------+
| version_tokens_lock_exclusive('lock1', 'lock2', 10) |
+-----------------------------------------------------+
| 1 |
+-----------------------------------------------------+

这个函数是在MySQL 5.7.8中添加的,名为vtoken_get_write_locks(),在5.7.9中重命名为version_tokens_lock_exclusive()

.version_tokens_lock_shared (token_name [token_name]…超时)
获取一个或多个版本令牌(按名称指定为字符串)上的共享锁,如果未在给定的超时值内获取锁,则超时并报错

mysql> SELECT version_tokens_lock_shared('lock1', 'lock2', 10);
+--------------------------------------------------+
| version_tokens_lock_shared('lock1', 'lock2', 10) |
+--------------------------------------------------+
| 1 |
+--------------------------------------------------+

这个函数是在MySQL 5.7.8中添加的,名为vtoken_get_read_locks(),在5.7.9中重命名为version_tokens_lock_shared()

.version_tokens_unlock ()
使用version_tokens_lock_exclusive()和version_tokens_lock_shared()释放在当前会话中获取的所有锁。

mysql> SELECT version_tokens_unlock();
+-------------------------+
| version_tokens_unlock() |
+-------------------------+
| 1 |
+-------------------------+

这个函数是在MySQL 5.7.8中添加的,名为vtoken_release_locks(),在5.7.9中重命名为version_tokens_unlock()。

锁定功能共享这些特性:
.对于成功,返回值为非零。否则,将发生错误
.令牌名称是字符串
.与操作服务器令牌列表的udf的参数处理不同,令牌名称参数周围的空白不会被忽略,并且=和;字符是允许的
.可以锁定不存在的令牌名称。这不会创建令牌
.超时值是非负整数,表示在出现错误超时之前等待获取锁所需的时间(以秒为单位)。如果超时为0,则不存在等待,如果不能立即获取锁,则该函数将产生一个错误
.版本令牌锁定功能基于所描述的锁定服务在Section 28.3.1 锁服务中描述。

版本令牌系统变量
版本标记支持以下系统变量。这些变量不可用,除非安装版本令牌插件

系统变量:
.version_tokens_session
命令行格式:–version-tokens-session=value
变量范围:Global,Session
动态:Yes
允许的值:类型为字符串,默认值为NULL
此变量的会话值指定客户端版本令牌列表,并指示客户端会话要求服务器版本令牌列表具有的令牌。

如果version_tokens_session变量为NULL(默认值)或值为空,则任何服务器版本令牌列表都是匹配的。(实际上,空值会禁用匹配需求。)

如果version_tokens_session变量有一个非空值,那么它的值与服务器版本令牌列表之间的任何不匹配都会导致会话发送给服务器的任何语句出错。在这种情况下会发生失配:
.version_tokens_session值中的令牌名称在服务器令牌列表中不存在。在本例中,发生了ER_VTOKEN_PLUGIN_TOKEN_NOT_FOUND错误
.version_tokens_session值中的令牌值与服务器令牌列表中相应令牌的值不同。在本例中,将出现ER_VTOKEN_PLUGIN_TOKEN_MISMATCH错误

服务器版本令牌列表中包含没有在version_tokens_session值中命名的令牌不是不匹配的

假设一个管理应用程序将服务器令牌列表设置为如下所示:

mysql> SELECT version_tokens_set('tok1=a;tok2=b;tok3=c');
+--------------------------------------------+
| version_tokens_set('tok1=a;tok2=b;tok3=c') |
+--------------------------------------------+
| 3 version tokens set. |
+--------------------------------------------+

客户端通过设置其version_tokens_session值来注册它需要服务器匹配的令牌。然后,对于客户端发送的每个后续语句,服务器将根据客户
端version_tokens_session值检查其令牌列表,如果存在不匹配,则产生一个错误:

mysql> SET @@session.version_tokens_session = 'tok1=a;tok2=b';
mysql> SELECT 1;
+---+
| 1 |
+---+
| 1 |
+---+
mysql> SET @@session.version_tokens_session = 'tok1=b';
mysql> SELECT 1;
ERROR 3136 (42000): Version token mismatch for tok1. Correct value a

第一个选择成功是因为客户端令牌tok1和tok2出现在服务器令牌列表中,并且每个令牌在服务器列表中具有相同的值。第二个SELECT失败是因为,虽然tok1出现在服务器令牌列表中,但它的值与客户机指定的值不同

此时,客户端发送的任何语句都将失败,除非服务器令牌列表发生更改,使其再次匹配。假设管理应用程序按如下方式更改服务器令牌列表:

mysql> SELECT version_tokens_edit('tok1=b');
+-------------------------------+
| version_tokens_edit('tok1=b') |
+-------------------------------+
| 1 version tokens updated. |
+-------------------------------+
mysql> SELECT version_tokens_show();
+-----------------------+
| version_tokens_show() |
+-----------------------+
| tok3=c;tok1=b;tok2=b; |
+-----------------------+

现在,客户机version_tokens_session值与服务器令牌列表匹配,客户机可以再次成功执行语句:

mysql> SELECT 1;
+---+
| 1 |
+---+
| 1 |
+---+

这个变量是在MySQL 5.7.8中添加的
.version_tokens_session_number
命令行格式:–version-tokens-session-number=N
变量范围:Global,Session
动态:NO
允许的值:类型为整型,默认值为0
此变量供内部使用。
这个变量是在MySQL 5.7.8中添加的。

Proudly powered by WordPress | Indrajeet by Sus Hill.