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,所以服务器将不会断开连接。

 

mysql_config_editor 配置工具

mysql_config_editor工具能让你在一种加密的登录路径文件.mylogin.cnf中存储审核身份信息。在Windows中这个文件存储在%APPDATA%\MySQL目录中,在非Windows平台上存储在当前用户的home目录中。这种文件可以被MySQL客户端程序读取来获得连接MySQL服务器的审核身份信息。

没有加密的.mylogin.cnf登录路径文件由选项组组成。类似于其它的选项文件。在.mylogin.cnf文件中的每个选项组叫作”login path”登录路径,它是只允许特定选项的组:host,user,password,port和socket。可以把一个登录路径选项组认为是一组选项来指定使用那个用户来连接那个MySQL服务器的信息。下面是没有加密的登录路径信息的
例子:

[client]
user = mydefaultname
password = mydefaultpass
host = 127.0.0.1
[mypath]
user = myothername
password = myotherpass
host = localhost

当调用一个客户端程序连接MySQL服务器时,客户端使用.mylogin.cnf并结合其它的选项文件。它的优先级比其它的选项文件要高,但比在客户端命令行中显式指定的要低。

为了指定一个替代的登录路径文件名,设置MYSQL_TEST_LOGIN_FILE环境变量。这种变量通过mysql_config_editor,通过标准的MySQL客户端(mysql,mysqladmin等)工具和mysql-test-run.pl测试工具所识别。

程序以以下方式使用登录路径文件中的选项组:
.mysql_config_editor在你没有通过–login-path=name选项来显式指定登录路径时缺省情况下会使用client选项组。

.在没有使用–login-path选项的情况下,客户端程序将像从其它选项文件中读取信息一样从登录路径文件中读取选项组。比如:
shell>mysql
缺省情况下,mysql客户端程序将从其它的选项文件中读取[client]和[mysql]选项组,因此也会从登录路径文件中读取这些信息。

.使用–login-path选项,客户端程序额外从登录路径文件中讯取命名的登录路径。仍然与读取其它选项文件中的选项组一样。比如:
shell>mysql –login-path=mypath

mysql客户端程序将从其它选项文件中读取[client]和[mysql]选项组信息和从登录路径文件中读取[client]和[mysql]选项组信息。

.即使当–no-defaults选项被使用,客户端程序也会读取登录路径文件。这允许使用一种安全的方式来指定密码而不而在命令行中指定。

mysql_config_editor会对.mylogin.cnf文件进行加密因此它不能以明文方式被读取,并且当客户端程序解密时,它的内容只在内存中使用。通过这种方式,密码可以以非明文格式存储在文件中并且在以后的命令行或环境变量需要使用时不需要提供输入密码。mysql_config_editor提供了一个print命令来显示登录路径文件的内容,但即使在这种情况下,密码值也会被隐藏,这样就不会以其他用户可以看到的方式出现

通过mysql_config_editor加密阻止密码以明文方式出现在.mylogin.cnf文件中并通过阻止无意暴露密码提供了一种安全措施。例如,如果你在屏幕上以非加密方式来显示my.cnf选项文件中的信息时,它包含的任何密码对于任何人都是可见的。使用.mylogin.cnf文件不是这种情况。但是使用的加密不会阻止一个有决心的攻击者,你不应该认为它是不可攻破的。如果用户能够获得您机器上的系统管理权限来访问您的文件,那么他可以轻松地解密.mylogin.cnf文件

登录路径文件必须对当前用户可读和可写并且对其它用户来说不可以访问。否则,mysql_config_editor会忽略它,并且客户端程序不会使用它。

mysql_config_editor语法:

shell>mysql_config_editor [program options] [command [command options]]

如果登录路径文件不存在,mysql_config_editor会创建它。

mysql_config_editor命令有以下参数选项:
.program_options由通用的mysql_config_editor选项组成。

.command指示对.mylogin.cnf登录路径文件执行的操作。例如,set将写一个登录路径到文件中,remove将删除一个登录路径,print显示登录路径内容。

.command_options指示任何指定给命令的额外选项,比如登录路径名和登录路径所使用的值。

命令名在程序参数集中的位置很重要。例如,这些命令行具有相同的参数,但产生不同的结果:

[mysql@localhost ~]$ mysql_config_editor --help set
mysql_config_editor Ver 1.0 Distrib 5.7.26, for Linux on x86_64
Copyright (c) 2012, 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.

MySQL Configuration Utility.
Usage: mysql_config_editor [program options] [command [command options]]
  -#, --debug[=#]     This is a non-debug version. Catch this and exit.
  -?, --help          Display this help and exit.
  -v, --verbose       Write more information.
  -V, --version       Output version information and exit.

Variables (--variable-name=value)
and boolean options {FALSE|TRUE}  Value (after reading options)
--------------------------------- ----------------------------------------
verbose                           FALSE

Where command can be any one of the following :
       set [command options]     Sets user name/password/host name/socket/port
                                 for a given login path (section).
       remove [command options]  Remove a login path from the login file.
       print [command options]   Print all the options for a specified
                                 login path.
       reset [command options]   Deletes the contents of the login file.
       help                      Display this usage/help information.

[mysql@localhost ~]$ mysql_config_editor set --help
mysql_config_editor Ver 1.0 Distrib 5.7.26, for Linux on x86_64
Copyright (c) 2012, 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.

MySQL Configuration Utility.

Description: Write a login path to the login file.
Usage: mysql_config_editor [program options] [set [command options]]
  -?, --help          Display this help and exit.
  -h, --host=name     Host name to be entered into the login file.
  -G, --login-path=name 
                      Name of the login path to use in the login file. (Default
                      : client)
  -p, --password      Prompt for password to be entered into the login file.
  -u, --user=name     User name to be entered into the login file.
  -S, --socket=name   Socket path to be entered into login file.
  -P, --port=name     Port number to be entered into login file.
  -w, --warn          Warn and ask for confirmation if set command attempts to
                      overwrite an existing login path (enabled by default).
                      (Defaults to on; use --skip-warn to disable.)

Variables (--variable-name=value)
and boolean options {FALSE|TRUE}  Value (after reading options)
--------------------------------- ----------------------------------------
host                              (No default value)
login-path                        client
user                              (No default value)
socket                            (No default value)
port                              (No default value)
warn                              TRUE

第一个命令行显示了通用的mysql_config_editor的帮助信息并且忽略了set命令。第二个命令行是显示了set命令的特定帮助信息。

假设想要建立一个client登录路径来定义你的缺省连接参数和额外的remote登录路径来连接远程服务器。想要记录以下信息:
.缺省情况,连接到本地服务器的用户名与密码为root和xxzx7817600
.连接到远程服务器的用户名与密码为root和123456

为了在.mylogin.cnf文件中设置登录路径,使用下面的set命令。每个命令单独一行执行并且根据提示输入相关的密码:

[mysql@localhost ~]$ mysql_config_editor set --login-path=client --host=192.168.1.250 --user=root --password
Enter password: 
[mysql@localhost ~]$ mysql_config_editor set --login-path=remote --host=192.168.1.251 --user=root --password --port=33306
Enter password: 
[mysql@localhost ~]$ 

mysql_config_editor缺省情况下使用client登录路径,因此–login-path=client选项从第一个命令中可以被忽略而不会产生影响。

为了查询mysql_config_editor写入.mylogin.cnf文件的内容,执行print命令:

[mysql@localhost ~]$ mysql_config_editor print --all
[client]
user = root
password = *****
host = 192.168.1.250
[remote]
user = root
password = *****
host = 192.168.1.251
port = 33306

print命令以一组行集合来显示每个登录路径,在方括号中的选项组头指示了登录路径名,接着是登录路径的选项值。密码值以星号出现不是以明文来显示。

如果你在执行print命令时不指定–all选项来显示所有的登录路径或不使用–login-path=name来显示指定的登录路径,如果存会client登录路径,那么缺省情况下print命令只会显示client登录路径。

[mysql@localhost ~]$ mysql_config_editor print
[client]
user = root
password = *****
host = 192.168.1.250

通过上面的例子可以看到一个登录路径文件可以包含多个登录路径。使用这种方式,mysql_config_editor可以简单地多个个性化的登录路径来连接到不同的MySQL服务器或者使用不同的账号连接到指定的服务器。这些登录路径都可以在调用客户端程序时通过使用–login-path选项来使用。例如,为了连接到远程服务器,执行以下命令:

[mysql@localhost ~]$ mysql --login-path=remote  
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 56674
Server version: 5.7.26 MySQL Community Server (GPL)

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

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

mysql> 

上面的命令mysql从其它选项文件中读取[client]和[mysql]选项组并且从登录路径文件中读取[mysql]和[remote]选项组信息。

为了连接到本地服务器,执行以下命令

[mysql@localhost ~]$ mysql --login-path=client
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.7.26 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>

因为缺省情况下mysql读取登录路径文件中的client和mysql登录路径,在这种情况下–login-path选项不会增加其它登录路径。因此上面的命令等价下面的命令:

[mysql@localhost ~]$ mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.7.26 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> 

从登录路径文件读取的选项优先于从其他选项文件读取的选项。从登录路径文件中稍后出现的登录路径组读取的选项优先于从文件中较早出现的登录路径组读取的选项。

mysql_config_editor向登录路径文件增加登录路径的顺序就是创建它们的顺序,因此应该先创建更多的通用登录路径,后创建特定登录路径。如果想要在登录路径文件中移动一个登录路径,可以先删除它,然后再重新创建它。例如一个client登录路径很通用,因为所有的客户端程序将会读取它,而mysqldump登录路径只能由mysqldump程序来读取。后指定的选项会覆盖先指定的选项,因此以client,mysqldump顺序来创建登录路径,mysqldump程序能让mysqldump的特定选项覆盖client的选项。

在使用mysql_config_editor的set命令来创建一个登录路径时不需要指定所有可能选项值(主机名,用户名,密码,端口号,socket)。只有指定的值会被写入登录路径。任何丢失而在调用客户端程序连接服务器时所需要的选项可以在其它选项文件或命令行中批定。任何在命令行中指定的选项值会覆盖在登录路径文件或其它选项文件中所指定的选项值。例如,如果在remote登录路径中指定了端口号33306,现在假设远程服务器端口变为3306了,那么连接服务器命令如下:

[mysql@localhost ~]$ mysql --login-path=remote -P3306
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 56674
Server version: 5.7.26 MySQL Community Server (GPL)

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

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

mysql> 

命令行指定的端口号覆盖登录路径中指定的端口号

可以使用remove命令来删除登录路径或登录路径中的某些选项删除remote登录路径中的–port选项

[mysql@localhost ~]$ mysql_config_editor print --login-path=remote
[remote]
user = root
password = *****
host = 192.168.1.250
port = 33306
[mysql@localhost ~]$ mysql_config_editor remove --login-path=remote --port
[mysql@localhost ~]$ mysql_config_editor print --login-path=remote
[remote]
user = root
password = *****
host = 192.168.1.250

删除删除remote登录路径

[mysql@localhost ~]$ mysql_config_editor remove --login-path=remote
[mysql@localhost ~]$ mysql_config_editor print --login-path=remote

达梦列存储表(HUGE Table)

达梦数据库中,表的数据存储方式分为行存储和列存储。行存储是以记录为单位进行存储的,数据页面中存储的是完整的若干条记录;列存储是以列为单位进行存储的,每一个列的所有行数据都存储在一起,而且一个指定的页面中存储的都是某一个列的连续数据。

Huge File System(检查HFS)是达梦数据库实现的,针对海量数据进行分析的一种高效、简单的列存储机制。列存储表(也称为HUGE表)就是建立在HFS存储机制上的一种表。

HUGE表是建立在自己特有的表空间HTS(HUGE TABLESPACE,即HUGE表空间)上的。最多可创建32767个HUGE表空间,其相关信息存储在动态视图V$HUGE_TABLESPACE中。

这个表空间与普通的表空间不同。普通的表空间,数据是通过段、簇、页来管理的,并且以固定大小(4K、8K、16K、32K)的页面为管理单位;而HUGE表空间是通过HFS存储机制来管理的,它相当于一个文件系统。创建一个HTS,其实就是创建一个空的文件目录(系统中有一个默认HTS,目录名为HMAIN)。在创建一个HUGE表并插入数据时,数据库会在指定的HTS表空间目录下创建一系列的目录及文件。

HUGE表的存储方式有以下几个优点:
1. 同一个列的数据都是连续存储的,可以加快某一个列的数据查询速度;
2. 连续存储的列数据,具有更大的压缩单元和数据相似性,可以获得远优于行存储的压缩效率,压缩的单位是区
3. 条件扫描借助数据区的统计信息进行精确过滤,可以进一步减少IO,提高扫描效率;
4. 允许建立二级索引;
5. 支持以ALTER TABLE的方式添加或者删除PK和UNIQUE约束。

DM支持两种类型的HUGE表:非事务型HUGE表和事务型HUGE表,下面分别进行介绍。
非事务型HUGE表
对非事务型HUGE表的增、删、改是直接对HUGE表进行写操作的,不写UNDO日志,不通过BUFFER缓存,直接操纵文件,速度快,但也因此导致不支持事务。另外,非事务型HUGE表中的ROWID是不固定的。

当非事务型HUGE表在操作过程中出现系统崩溃或者断电等问题时,因为修改时采取的是直接写的策略,所以有可能会出现数据不一致的问题。为了保证数据的一致性,在操作时可以适当地做一些日志来保证数据的完整性,完整性保证策略主要是通过数据的镜像来实现的,镜像的不同程度可以实现不同程度的完整性恢复。镜像文件是放在表目录中的以.mir为扩展名的文件。DM提供三种方案:
1. LOG NONE:不做镜像。相当于不做数据一致性的保证,如果出错只能手动通过系统函数来修复表数据,当然速度是最快的,不需要额外的IO,这种选项如果用户明确知道自己的环境不会出现问题可以采用,效率最高。

2. LOG LAST:做部分镜像。但是在任何时候都只对当前操作的区做镜像,如果当前区的操作完成了,那么这个镜像也就失效了,可能会被下一个被操作区覆盖,这样做的好处是镜像文件不会太大,同时也可以保证数据是完整的。但有可能遇到的问题是:一次操作很多的情况下,有可能一部分数据已经完成,另一部分数据还没有来得及做的问题。如果用户能接受这个问题的话这个选择不失为最佳选择,这也是系统默认的选择

3. LOG ALL:全部做镜像。在操作过程中,所有被修改的区都会被记录下来,当一次操作修改的数据过多时,镜像文件有可能会很大,但好处是,能够保证操作完整性。比如,在操作过程中失败了,那么这个操作会完整的撤消,不存在上面一部分修改部分还没修改的问题。

AUX辅助表
对于每个HUGE表,相应地配备一个AUX辅助表来管理其数据。因为在HUGE表文件中只存储了数据,辅助表用来管理以及辅助系统用户操作这些数据,AUX辅助表是在创建HUGE表时系统自动创建的,表名为“表名$AUX”,如果该HUGE表为分区表,则辅助表名为“子表名$AUX”。辅助表的表名长度不能大于128个字节。AUX辅助表中每一条记录对应文件中的一个数据区,包括下面15列:
1.COLID:表示当前这条记录对应的区所在的列的列ID号;
2.SEC_ID:表示当前这个记录对应的区的区ID号,每一个区都有一个ID号,并且唯一;
3.FILE_ID:表示这个区的数据所在的文件号;
4.OFFSET:表示这个区的数据在文件中的偏移位置,4K对齐;
5.COUNT:表示这个区中存储的数据总数(有可能包括被删除的数据);
6.ACOUNT:表示这个区中存储的实际数据行数;
7.N_LEN:表示这个区中存储的数据在文件中的长度,4K对齐的;
8.N_NULL:表示这个区中的数据中包括的NULL值的行数;
9.N_DIST:表示这个区中所有数据互不相同的行数;
10.CPR_FLAG:表示这个区是否压缩;
11.ENC_FLAG:表示这个区是否加密;
12.CHKSUM:用来较验的,该功能暂未启用;
13.MAX_VAL:表示这个区中的最大值,精确值;
14.MIN_VAL:表示这个区中的最小值,精确值;
15.SUM_VAL:表示这个区中所有值的和,精确值。
前面7列是用来控制数据存取的,根据这些信息就可以知道这个区的具体存储位置、长度及基本信息。后面8列都是用来对这个区进行统计分析的。其中,COLID和SEC_ID的组合键为辅助表的聚集关键字。

事务型HUGE表
非事务型HUGE表在进行增、删、改时直接对HUGE表进行写操作,每次写操作需要至少对一个区进行IO,导致IO量较大,且并发性能不高。为此,DM推出了事务型HUGE表,通过增加RAUX、DAUX和UAUX行辅助表,减少了事务型HUGE表增、删、改操作的IO,
提高效率,同时提高并行性能。事务型HUGE表支持UNDO日志,实现了事务特性。

RAUX行辅助表
RAUX行辅助表存放最后一个数据区(不够存满一个数据区)的数据,表名为“HUGE表名$RAUX”。如果该HUGE表为分区表,则辅助表名为“子表名$RAUX”。辅助表的表名长度不能大于128个字节。

RAUX行辅助表中内容对应于HUGE表中的最后一部分记录(不够存满一个数据区的)。RAUX表与HUGE表结构相同,不论数据在那个表中,每一行数据的ROWID固定不变。

DAUX行辅助表
DAUX行辅助表记录HUGE表数据文件中被删除的数据,表名为“HUGE表名$DAUX”。如果该HUGE表为分区表,则辅助表名为“子表名$DAUX”。辅助表的表名长度不能大于128个字节。

UAUX行辅助表
UAUX行辅助表记录HUGE表被更新的数据的新值,表名为“HUGE表名$UAUX”。如果该HUGE表为分区表,则辅助表名为“子表名$UAUX”。辅助表的表名长度不能大于128个字节。

创建HUGE表
当用户确定自己要使用HUGE表的时候,首先需要在模式中创建新表,创建一个HUGE表需要有CREATE TABLE数据库权限,要想在其他用户的模式中创建新表需要有CREATE ANY TABLE 数据库权限。

但是创建一个HUGE表时,如果不使用默认的表空间,则必须要先创建一个HUGE TABLESPACE(HTS),创建HTS语法如下:
CREATE HUGE TABLESPACE < 表空间名> PATH < 表空间路径>;
参数说明:
1. < 表空间名> 表空间的名称,表空间名称最大长度128字节;
2. < 表空间路径> 指明新生成的表空间在操作系统下的路径。
示例如下:

SQL> CREATE HUGE TABLESPACE HTS_NAME PATH '/dm_home/dmdba/dmdbms/data/jydm/htsspace';
executed successfully
used time: 183.066(ms). Execute id is 8.
SQL> select * from v$huge_tablespace;

LINEID     ID          NAME     PATHNAME                                
---------- ----------- -------- ----------------------------------------
1          0           HMAIN    /dm_home/dmdba/dmdbms/data/jydm/HMAIN
2          1           HTS_NAME /dm_home/dmdba/dmdbms/data/jydm/htsspace

used time: 0.723(ms). Execute id is 9.

在创建HUGE表时,根据WITH|WITHOUT DELTA区分创建非事务型HUGE表还是事务型HUGE表。指定WITH DELTA,创建事务型HUGE表;指定WITHOUT DELTA,则创建非事务型HUGE表,缺省为WITHOUT DELTA。

例如,创建非事务型HUGE表T1

SQL> CREATE HUGE TABLE T1 (A INT, B INT) STORAGE(WITHOUT DELTA) tablespace HTS_NAME;
executed successfully
used time: 49.058(ms). Execute id is 13.
SQL> desc t1;

LINEID     NAME TYPE$   NULLABLE
---------- ---- ------- --------
1          A    INTEGER Y
2          B    INTEGER Y

used time: 14.603(ms). Execute id is 14.



SQL> select owner,table_name,tablespace_name from dba_tables where table_name='T1';

LINEID     OWNER  TABLE_NAME TABLESPACE_NAME
---------- ------ ---------- ---------------
1          SYSDBA T1         HTS_NAME

used time: 57.166(ms). Execute id is 17.

创建事务型HUGE表T2。

SQL> CREATE HUGE TABLE T2 (A INT, B INT) STORAGE(WITH DELTA) tablespace hts_name;
executed successfully
used time: 37.888(ms). Execute id is 18.
SQL> select owner,table_name,tablespace_name from dba_tables where table_name='T2';

LINEID     OWNER  TABLE_NAME TABLESPACE_NAME
---------- ------ ---------- ---------------
1          SYSDBA T2         HTS_NAME

used time: 27.566(ms). Execute id is 19.


[root@shard1 htsspace]# ls -lrt
总用量 0
drwxr-xr-x 4 dmdba dinstall 34 11月 13 01:07 SCH150994945
[root@shard1 htsspace]# cd SCH150994945
[root@shard1 SCH150994945]# ls
TAB1425  TAB1427
[root@shard1 SCH150994945]# ls -lrt
总用量 0
drwxr-xr-x 2 dmdba dinstall 6 11月 13 01:01 TAB1425
drwxr-xr-x 2 dmdba dinstall 6 11月 13 01:07 TAB1427

需要注意的是,当指定创建事务型HUGE表时,指定HUGE表镜像文件方案的选项LOG NONE|LOG LAST|LOG ALL失效。另外,在创建表HUGE表时,可以指定表的存储属性,存储属性包括如下几个方面:

另外,在创建表HUGE表时,可以指定表的存储属性,存储属性包括如下几个方面:
1. 区大小(一个区的数据行数)
区大小可以通过设置表的存储属性来指定,区的大小必须是2的多少次方,如果不是则向上对齐。取值范围:1024行~1024*1024行。默认值为65536行。例如,创建HUGE表test,指定区的大小为2048,其它默认。
CREATE HUGE TABLE test(name VARCHAR, sno INT) STORAGE(SECTION (2048));

例如,创建HUGE表test,所有列都采用默认值。
CREATE HUGE TABLE test(name VARCHAR, sno INT);

2. 是否记录区统计信息,即在插入时是否记下其最大值最小值
关于这一点有一个原则,如果这个列经常用作查询条件,并且数据不是很均匀,或者基本是有序的,那么做统计信息是非常有用的,反之则可以不做统计。缺省情况下,为记录区统计信息。如果想不记录,可通过设置STAT NONE实现。例如,创建HUGE表test,通过列存储属性指定统计信息属性(不记录区统计信息)。
CREATE HUGE TABLE test(name VARCHAR STORAGE (STAT NONE), sno INT);

又如,创建HUGE表test,通过表存储属性指定统计信息属性(不记录区统计信息)。
CREATE HUGE TABLE test(name VARCHAR, sno INT) STORAGE (STAT NONE);

3. 所属的表空间
创建HUGE表,需要通过存储属性指定其所在的表空间,不指定则存储于默认表空间HMAIN中。HUGE表指定的表空间只能是HTS表空间,例如HTS_NAME为已指定HTS表空间。

SQL> CREATE HUGE TABLE t3 (name VARCHAR, sno INT) STORAGE (ON HTS_NAME);
executed successfully
used time: 31.424(ms). Execute id is 25.

SQL> select owner,table_name,tablespace_name from dba_tables where table_name='T3';

LINEID     OWNER  TABLE_NAME TABLESPACE_NAME
---------- ------ ---------- ---------------
1          SYSDBA T3         HTS_NAME

used time: 14.911(ms). Execute id is 27.

还可以在创建语句中通过tablespace选项来指定表空间

SQL> CREATE HUGE TABLE T2 (A INT, B INT) STORAGE(WITH DELTA) tablespace hts_name;
executed successfully
used time: 37.888(ms). Execute id is 18.
SQL> select owner,table_name,tablespace_name from dba_tables where table_name='T2';

LINEID     OWNER  TABLE_NAME TABLESPACE_NAME
---------- ------ ---------- ---------------
1          SYSDBA T2         HTS_NAME

used time: 27.566(ms). Execute id is 19.

4. 文件大小
创建HUGE表时还可以指定单个文件的大小,通过表的存储属性来指定,取值范围为16M~1024*1024M。不指定则默认为64M。文件大小必须是2的多少次方,如果不是则向上对齐。
CREATE HUGE TABLE test(name VARCHAR, sno INT) STORAGE (filesize(64));

5. 指定压缩级别
为特定列指定压缩级别,取值范围0~10,分别代表不同的算法和级别。有两种压缩算法:SNAPPY和ZIP。10采用SNAPPY算法轻量级方式压缩。2~9采用ZIP算法压缩,2~9代表压缩级别,值越小表示压缩比越低、压缩速率越快;值越大表示压缩比越高、压缩速度越慢。0和1为快捷使用,默认值为0。0等价于LEVEL 2;1等价于LEVEL 9。

例如,创建HUGE表test,指定sno列按照最大压缩比压缩。
CREATE HUGE TABLE test(name VARCHAR, sno INT) COMPRESS LEVEL 1 (sno);

下面是一个综合的创建HUGE表的例子:

SQL> CREATE HUGE TABLE orders
2   (
3   o_orderkey INT,
4   o_custkey INT,
5   o_orderstatus CHAR(1),
6   o_totalprice FLOAT,
7   o_orderdate DATE,
8   o_orderpriority CHAR(15),
9   o_clerk CHAR(15),
10  o_shippriority INT,
11  o_comment VARCHAR(79) STORAGE(stat none)
12  )
13  STORAGE(section(65536) ,filesize(64), with delta,on HTS_NAME) 
14  COMPRESS LEVEL 9 FOR 'QUERY HIGH' (o_comment);
executed successfully
used time: 32.537(ms). Execute id is 28.

这个例子创建了一个名为ORDERS的事务型HUGE表,ORDERS表的区大小为65536行,文件大小为64M,指定所在的表空间为HTS_NAME,o_comment列指定的区大小为不做统计信息,其它列(默认)都做统计信息,指定列o_comment列压缩类型为查询高压缩率,压缩级别为9。

HUGE表使用说明
HUGE表与普通行表一样,可以进行增、删、改操作,操作方式也是一样的。但HUGE表的删除与更新操作的效率会比行表低一些,并发操作性能也会比行表差一些,因此在HUGE中不宜做频繁的删除及更新操作。总之,HUGE表比较适合做分析型表的存储。另外,使用HUGE表时应注意存在以下一些限制:
1.建HUGE表时仅支持定义NULL、NOT NULL、UNIQUE约束以及PRIMARY KEY,后两种约束也可以通过ALTER TABLE的方式添加,但这两种约束不检查唯一性;
2.HUGE不允许建立聚簇索引,允许建立二级索引,不支持建位图索引,其中UNIQUE索引不检查唯一性;
3.不支持SPACE LIMIT(空间限制);
4.不支持建立全文索引;
5.不支持使用自定义类型;
6.不支持引用约束;
7.不支持IDENTITY自增列;
8.不支持大字段列;
9.不支持建触发器;
10.不允许垂直分区;
11.不支持游标的修改操作;
12.PK和UNIQUE约束不检查唯一性,对应的索引都为虚索引;UNIQUE索引也不检查唯一性,为实索引,索引标记中
不包含唯一性标记,即和普通二级索引相同;
13.不允许对分区子表设置SECTION和WITH/WITHOUT DELTA;
14.当事务型HUGE表进行了较多增删改操作时,应对其进行数据重整操作,以提高性能。

查看有关HUGE表的信息
1.表定义
对一个HUGE表,用户可以通过CALL SP_TABLEDEF(‘SYSDBA’, ‘ORDERS’);得到这个表的定义语句,可以具体了解表的各个列的数据类型信息、存储属性等,还可以查看在这个表上是否有压缩等等。

SQL> CALL SP_TABLEDEF('SYSDBA', 'ORDERS');

LINEID     COLUMN_VALUE                                                                                                                                                                                                                   
---------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1          CREATE HUGE TABLE "SYSDBA"."ORDERS"  (  "O_ORDERKEY" INT,  "O_CUSTKEY" INT,  "O_ORDERSTATUS" CHAR(1),  "O_TOTALPRICE" FLOAT,  "O_ORDERDATE" DATE,  "O_ORDERPRIORITY" CHAR(15),  "O_CLERK" CHAR(15),  "O_SHIPPRIORITY" INT,  "O_COMMENT" VARCHAR(79) STORAGE(STAT NONE)) STORAGE(STAT ASYNCHRONOUS, WITH DELTA, SECTION(65536), FILESIZE(64), ON "HTS_NAME")    COMPRESS (     "O_COMMENT" LEVEL 9 ) LOG LAST ;

used time: 2.205(ms). Execute id is 31.

SQL> CALL SP_TABLEDEF('SYSDBA', 'T1');

LINEID     COLUMN_VALUE                                                                                                                          
---------- --------------------------------------------------------------------------------------------------------------------------------------
1          CREATE HUGE TABLE "SYSDBA"."T1"  (  "A" INT,  "B" INT) STORAGE(WITHOUT DELTA, SECTION(65536), FILESIZE(64), ON "HTS_NAME")  LOG LAST ;

used time: 1.063(ms). Execute id is 32.

2. 数据存储情况
HUGE表有一个很好的特点就是有AUX辅助表,其中用户可以利用的信息很多,因为每一条记录对应一个区,所以可以查看每一个区的存储情况,每一个列的存储情况及每一个列中具有相同区ID的所有数据的情况等,还包括了很精确的统计信息,用户可以通过观察AUX辅助表中的信息对表进行一些相应的操作。

非事务型huge表

SQL> insert into t1 values(2,2);
affect rows 1

used time: 33.110(ms). Execute id is 35.
SQL> commit;
executed successfully
used time: 16.240(ms). Execute id is 36.

SQL> select * from T1$AUX;

LINEID     COLID       SEC_ID      FILE_ID     OFFSET               COUNT       ACOUNT      N_LEN       N_NULL      N_DIST      CPR_FLAG ENC_FLAG CHKSUM      MAX_VAL    MIN_VAL    SUM_VAL           
---------- ----------- ----------- ----------- -------------------- ----------- ----------- ----------- ----------- ----------- -------- -------- ----------- ---------- ---------- ------------------
1          0           0           0           4096                 2           2           270336      0           0           N        N        0           0x02000000 0x01000000 0x0300000000000000
2          1           0           0           4096                 2           2           270336      0           0           N        N        0           0x02000000 0x01000000 0x0300000000000000

used time: 1.079(ms). Execute id is 44.
SQL> 

事务刑huge表

SQL> insert into orders values(1,1,'F',50.5,sysdate,'1','1',1,'1');
affect rows 1

used time: 33.758(ms). Execute id is 46.
SQL> insert into orders values(2,2,'F',134.2,sysdate,'2','2',2,'2');
affect rows 1

used time: 1.499(ms). Execute id is 47.
SQL> commit;
executed successfully
used time: 21.297(ms). Execute id is 48.

在插入数据后RAUX辅助表中有数据,DAUX与UAUX辅助表中没有数据

SQL> select * from orders$raux;

LINEID     O_ORDERKEY  O_CUSTKEY   O_ORDERSTATUS O_TOTALPRICE              O_ORDERDATE O_ORDERPRIORITY O_CLERK         O_SHIPPRIORITY O_COMMENT
---------- ----------- ----------- ------------- ------------------------- ----------- --------------- --------------- -------------- ---------
1          1           1           F             5.050000000000000E+01     2019-11-14  1               1               1              1
2          2           2           F             1.342000000000000E+02     2019-11-14  2               2               2              2

used time: 1.197(ms). Execute id is 50.


SQL> select * from orders$daux;
no rows

used time: 0.961(ms). Execute id is 51.
SQL> select * from orders$uaux;
no rows

used time: 1.007(ms). Execute id is 52.

当更新orders表中的数据后,UAUX辅助表中会记录相关数据

SQL> update orders set O_ORDERSTATUS='D' where O_ORDERKEY=2;
affect rows 1

used time: 2.416(ms). Execute id is 53.
SQL> commit;
executed successfully
used time: 29.715(ms). Execute id is 54.
SQL> select * from orders$uaux;

LINEID     COLID       DTA_ROWID            VALUE
---------- ----------- -------------------- -----
1          2           2                    0x44

used time: 0.726(ms). Execute id is 55.

当删除数据后,DAUX辅助表中会记录相关数据

SQL> delete from orders where O_ORDERKEY=2;
affect rows 1

used time: 2.128(ms). Execute id is 57.
SQL> commit;
executed successfully
used time: 19.066(ms). Execute id is 58.
SQL> select * from orders$daux;

LINEID     START_ID             COUNT       INFO      
---------- -------------------- ----------- ----------
1          2                    1           NULL

used time: 0.590(ms). Execute id is 59.
Proudly powered by WordPress | Indrajeet by Sus Hill.