189 8069 5689
PostgreSQL 中 Page 是一个磁盘 Block 上的一个抽象结构，用于描述 Block 内部的数据结构与组织形式。
在金牛等地区，都构建了全面的区域性战略布局，加强发展的系统性、市场前瞻性、产品创新能力，以专注、极致的服务理念，为客户提供成都网站制作、成都网站建设 网站设计制作按需网站开发,公司网站建设,企业网站建设,成都品牌网站建设,成都营销网站建设,外贸网站制作,金牛网站建设费用合理。
所有数据块在读写时，必须按 Page 格式进行访问操作。
PostgreSQL 11 的 Page 格式（包含 3 行数据）如下：
行指针之前的 Page Header 总空间消耗为： (64 + 16 * 6 + 32) bit / 8 = 24 Byte
以下分别对这些结构以及对应的标志位的值进行说明：
Tuple 类型和行中各列数据的头部信息共享相同的数据结构，所以可以用相同的方法来构建和检查。但需求略有不同，数据不需要事务可见性信息，它需要一个长度字段和一些嵌入式类型信息。我们可以通过覆盖 Heap Tuple 上的 xmin/cmin/xmax/cmax/xvac 字段来实现数据上的需求。
Heap tuple 的头部信息，为了避免空间浪费，应该将字段以一种避免结构扩充的方式来布局。
通常，内存中所有的 tuples 都会使用数据字段进行初始化，当一个 tuple 需要写入表中时，事务相关的字段将会被写入，并覆盖数据字段。
Heap tuple 的整体结构包括：
通过 pageinspect 扩展模块，可以在低层次观察 page 中的实际数据，而不用考虑事务及相关可见性限制，这通常用于 DEBUG 目的的数据研究。
其常用函数说明如下：
创建模块
创建测试表
查看 Page Header
数据含义解析：
查看 Page 中的记录（Tuple）
数据含义解析：
解析 Tuple 数据
尝试多次更新同一条一条数据
再次查看页面数据
数据含义解析：
删除一条数据
再次查看页面数据
数据含义解析：
通过跟踪 t_xmin, t_xmax, t_ctid 三个字段的变化，可以得到 Tuple 数据的多版本变化历史，这也是 PostgreSQL 的 MVCC 实现原理
PostgreSQL 的多版本（MVCC）与 Oracle 有很大的不同，在于其将多版本信息与表数据存储在一起，这种多版本实现方式有其优势与局限性。
优势
劣势
PostgresSQL提供了许多数据库配置参数，本章将介绍每个参数的作用和如何配置每一个参数。
10.1 如何设置数据库参数
所有的参数的名称都是不区分大小写的。每个参数的取值是布尔型、整型、浮点型和字符串型这四种类型中的一个，分别用boolean
、integer、 floating point和string表示。布尔型的值可以写成ON、OFF、 TRUE、 FALSE、 YES、 NO、 1和 0，而且不区分大小
写。
有些参数用来配置内存大小和时间值。内存大小的单位可以是KB、MB和GB。时间的单位可以是毫秒、秒、分钟、小时和天。用ms表示
毫秒，用s表示秒，用 min表示分钟，用h表示小时，用d表示天。表示内存大小和时间值的参数参数都有一个默认的单位，如果用户
在设置参数的值时没有指定单位，则以参数默认的 单位为准。例如，参数shared_buffers表示数据缓冲区的大小，它的默认单位是
数据块的个数，如果把它的值设成8，因为每个数据块的大小是 8KB，则数据缓冲区的大小是8*8=64KB，如果将它的值设成128MB，
则数据缓冲区的大小是128MB。参数vacuum_cost_delay 的默认单位是毫秒，如果把它的值设成10，则它的值是10毫秒，如果把它的
值设成100s，则它的值是100秒。
所有的参数都放在文件 postgresql.conf中，下面是一个文件实例:
#这是注释
log_connections = yes
log_destination = 'syslog'
search_path = '"$user", public'
每一行只能指定一个参数，空格和空白行都会被忽略。“ #”表示注释，注释信息不用单独占一行，可以出现在配置文件的任何地方
。如果参数的值不是简单的标识符和数字，应该用单引号引起来。如果参数的值中有单引号，应该写两个单引号，或者在单引号前面
加一个反斜杠。
一个配置文件也可以包含其它配置文件，使用include指令能够达到这个目的，例如，假设postgresql.conf文件中有下面一行：
include ‘my.confg’
文件my.config中的配置信息也会被数据库读入。include指令指定的配置文件也可以用include指令再包含其它配置文件。如果
include指令中指定的文件名不是绝对路径，数据库会在postgresql.conf文件所在的目录下查找这个文件。
用户也可以在数据库启动以后修改postgresql.conf配置文件，使用命令pg_ctl reload来通知数据库重新读取配置文件。注意，有些
参数在数据库启动以后，不能被修改，只有重新启动数据库以后，新的参数值才能生效。另外一些参数可 以在数据库运行过程中被
修改而且新的值可以立即生效。所以数据库在运行过程中重新读取参数配置文件以后，不是所有的参数都会被赋给新的值。
用户可以在自己建立的会话中执行命令SET修改某些配置参数的值（注意不是全部参数），例如：
SET ENABLE_SEQSCAN TO OFF;
另外，有些参数只有数据库超级用户才能使用SET命令修改它们。用户可以在psql中执行命令show来查看所有的数据库参数的当前值
。例如：
（1）show all; --查看所有数据库参数的值
（2）show search_path; --查看参数search_path的值
10.2 连接与认证
10.2.1 连接设置
listen_addresses (string)
这个参数只有在启动数据库时，才能被设置。它指定数据库用来监听客户端连接的TCP/IP地址。默认是值是* ，表示数据库在启动以
后将在运行数据的机器上的所有的IP地址上监听用户请求（如果机器只有一个网卡，只有一个IP地址，有多个网卡的机器有多个 IP
地址）。可以写成机器的名字，也可以写成IP地址，不同的值用逗号分开，例如，’server01’, ’140.87.171.49, 140.87.171.21
’。如果被设成localhost，表示数据库只能接受本地的客户端连接请求，不能接受远程的客户端连接请求。
port (integer)
这个参数只有在启动数据库时，才能被设置。它指定数据库监听户端连接的TCP端口。默认值是5432。
max_connections (integer)
这个参数只有在启动数据库时，才能被设置。它决定数据库可以同时建立的最大的客户端连接的数目。默认值是100。
superuser_reserved_connections (integer)
这个参数只有在启动数据库时，才能被设置。它表示预留给超级用户的数据库连接数目。它的值必须小于max_connections。 普通用
户可以在数据库中建立的最大的并发连接的数目是max_connections- superuser_reserved_connections， 默认值是3。
unix_socket_group (string)
这个参数只有在启动数据库时，才能被设置。设置Unix-domain socket所在的操作系统用户组。默认值是空串，用启动数据库的操作
系统用户所在的组作为Unix-domain socket的用户组。
unix_socket_permissions (integer)
这个参数只有在启动数据库时，才能被设置。它设置Unix-domain socket的访问权限，格式与操作系统的文件访问权限是一样的。默
认值是0770，表示任何操作系统用户都能访问Unix-domain socket。可以设为0770（所有Unix-domain socket文件的所有者所在的组
包含的用户都能访问）和0700(只有Unix-domain socket文件的所有者才能访问)。对于Unix-domain socket，只有写权限才有意义，
读和执行权限是没有意义的。
tcp_keepalives_idle (integer)
这个参数可以在任何时候被设置。默认值是0，意思是使用操作系统的默认值。它设置TCP套接字的TCP_KEEPIDLE属性。这个参数对于
通过Unix-domain socket建立的数据库连接没有任何影响。
tcp_keepalives_interval (integer)
这个参数可以在任何时候被设置。默认值是0，意思是使用操作系统的默认值。它设置TCP套接字的TCP_KEEPINTVL属性。这个参数对
于通过Unix-domain socket建立的数据库连接没有任何影响。
tcp_keepalives_count (integer)
这个参数可以在任何时候被设置。默认值是0，意思是使用操作系统的默认值。它设置TCP套接字的TCP_KEEPCNT属性。这个参数对于
通过Unix-domain socket建立的数据库连接没有任何影响。
10.2.2. 安全与认证
authentication_timeout (integer)
这个参数只能在postgresql.conf文件中被设置，它指定一个时间长度，在这个时间长度内，必须完成客户端认证操作，否则客户端
连接请求将被拒绝。它可以阻止某些客户端进行认证时长时间占用数据库连接。单位是秒，默认值是60。
ssl (boolean)
这个参数只有在启动数据库时，才能被设置。决定数据库是否接受SSL连接。默认值是off。
ssl_ciphers (string)
指定可以使用的SSL加密算法。查看操作系统关于openssl的用户手册可以得到完整的加密算法列表（执行命令openssl ciphers –v
也可以得到）。
10.3 资源消耗
10.3.1 内存
shared_buffers (integer)
这个参数只有在启动数据库时，才能被设置。它表示数据缓冲区中的数据块的个数，每个数据块的大小是8KB。数据缓冲区位于数据
库的共享内存中，它越大越好，不能小于128KB。默认值是1024。
temp_buffers (integer)
这个参数可以在任何时候被设置。默认值是8MB。它决定存放临时表的数据缓冲区中的数据块的个数，每个数据块的大小是8KB。临时
表缓冲区存放在每个数据库进程的私有内存中，而不是存放在数据库的共享内存中。默认值是1024。
max_prepared_transactions (integer)
这个参数只有在启动数据库时，才能被设置。它决定能够同时处于prepared状态的事务的最大数目（参考PREPARE TRANSACTION命令
）。如果它的值被设为0。则将数据库将关闭prepared事务的特性。它的值通常应该和max_connections的值 一样大。默认值是5。
work_mem (integer)
这个参数可以在任何时候被设置。它决定数据库的排序操作和哈希表使用的内存缓冲区的大小。如何work_mem指定的内存被耗尽，数
据库将使用磁盘文件进 行完成操作，速度会慢很多。ORDER BY、DISTINCT和merge连接会使用排序操作。哈希表在Hash连接、hash聚
集函数和用哈希表来处理IN谓词中的子查询中被使用。单位是 KB，默认值是1024。
maintenance_work_mem (integer)
这个参数可以在任何时候被设置。它决定数据库的维护操作使用的内存空间的大小。数据库的维护操作包括VACUUM、CREATE INDEX和
ALTER TABLE ADD FOREIGN KEY等操作。 maintenance_work_mem的值如果比较大，通常可以缩短VACUUM数据库和从dump文件中恢复数
据库需要的时间。 maintenance_work_mem存放在每个数据库进程的私有内存中，而不是存放在数据库的共享内存中。单位是KB，默
认值是16384。
max_stack_depth (integer)
这个参数可以在任何时候被设置，但只有数据库超级用户才能修改它。它决定一个数据库进程在运行时的STACK所占的空间的最大值
。数据库进程在运行时，会 自动检查自己的STACK大小是否超过max_stack_depth，如果超过，会自动终止当前事务。这个值应该比
操作系统设置的进程STACK的大小 的上限小1MB。使用操作系统命令“ulimit –s“可以得到操作系统设置的进程STACK的最大值。单
位是KB，默认值是100。
10.3.2 Free Space Map
数据库的所有可用空间信息都存放在一个叫free space map (FSM)的结构中，它记载数据文件中每个数据块的可用空间的大小。FSM
中没有记录的数据块，即使有可用空间，也不会系统使用。系统如果需要新的物理存 储空间，会首先在FSM中查找，如果FSM中没有
一个数据页有足够的可用空间，系统就会自动扩展数据文件。所以，FSM如果太小，会导致系统频繁地扩展数 据文件，浪费物理存储
空间。命令VACUUM VERBOSE在执行结束以后，会提示当前的FSM设置是否满足需要，如果FSM的参数值太小，它会提示增大参数。
FSM存放在数据库的共享内存中，由于物理内存的限制，FSM不可能跟踪数据库的所有的数据文件的所有数据块的可用空间信息，只能
跟踪一部分数据块的可用空间信息。
max_fsm_relations (integer)
这个参数只有在启动数据库时，才能被设置。默认值是1000。它决定FSM跟踪的表和索引的个数的上限。每个表和索引在FSM中占7个
字节的存储空间。
max_fsm_pages (integer)
这个参数只有在启动数据库时，才能被设置。它决定FSM中跟踪的数据块的个数的上限。initdb在创建数据库集群时会根据物理内存
的大小决定它的值。每 个数据块在fsm中占6个字节的存储空间。它的大小不能小于16 * max_fsm_relations。默认值是20000。
10.3.3 内核资源
max_files_per_process (integer)
这个参数只有在启动数据库时，才能被设置。他设定每个数据库进程能够打开的文件的数目。默认值是1000。
shared_preload_libraries (string)
这个参数只有在启动数据库时，才能被设置。它设置数据库在启动时要加载的操作系统共享库文件。如果有多个库文件，名字用逗号
分开。如果数据库在启动时未找到shared_preload_libraries指定的某个库文件，数据库将无法启动。默认值为空串。
10.3.4 垃圾收集
执行VACUUM 和ANALYZE命令时，因为它们会消耗大量的CPU与IO资源，而且执行一次要花很长时间，这样会干扰系统执行应用程序发
出的SQL命令。为了解决这个 问题，VACUUM 和ANALYZE命令执行一段时间后，系统会暂时终止它们的运行，过一段时间后再继续执行
这两个命令。这个特性在默认的情况下是关闭的。将参数 vacuum_cost_delay设为一个非零的正整数就可以打开这个特性。
用户通常只需要设置参数vacuum_cost_delay和vacuum_cost_limit，其它的参数使用默认值即可。VACUUM 和ANALYZE命令在执行过程
中，系统会计算它们执行消耗的资源，资源的数量用一个正整数表示，如果资源的数量超过 vacuum_cost_limit，则执行命令的进程
会进入睡眠状态，睡眠的时间长度是是vacuum_cost_delay。 vacuum_cost_limit的值越大，VACUUM 和ANALYZE命令在执行的过程中
，睡眠的次数就越少，反之，vacuum_cost_limit的值越小，VACUUM 和ANALYZE命令在执行的过程中，睡眠的次数就越多。
vacuum_cost_delay (integer)
这个参数可以在任何时候被设置。默认值是0。它决定执行VACUUM 和ANALYZE命令的进程的睡眠时间。单位是微秒。它的值最好是10
的整数，如果不是10的整数，系统会自动将它设为比该值大的并且最接近该值的是10 的倍数的整数。如果值是0，VACUUM 和ANALYZE
命令在执行过程中不会主动进入睡眠状态，会一直执行下去直到结束。
vacuum_cost_page_hit (integer)
这个参数可以在任何时候被设置。默认值是1。
vacuum_cost_page_miss (integer)
这个参数可以在任何时候被设置。默认值是10。
vacuum_cost_page_dirty (integer)
这个参数可以在任何时候被设置。默认值是20。
vacuum_cost_limit (integer)
这个参数可以在任何时候被设置。默认值是200。
10.3.5 后台写数据库进程
后台写数据库进程负责将数据缓冲区中的被修改的数据块（又叫脏数据块）写回到数据库物理文件中。
bgwriter_delay (integer)
这个参数只能在文件postgresql.conf中设置。它决定后台写数据库进程的睡眠时间。后台写数据库进程每次完成写数据到物理文件
中的任务以后， 就会睡眠bgwriter_delay指定的时间。 bgwriter_delay的值应该是10的倍数，如果用户设定的值不是10的倍数，数
据库会自动将参数的值设为比用户指定的值大的最接近用户指定的值 的同时是10的倍数的值。单位是毫秒，默认值是200。
bgwriter_lru_maxpages (integer)
这个参数只能在文件postgresql.conf中设置。默认值是100。后台写数据库进程每次写脏数据块时，写到外部文件中的脏数据块的个
数不能超过 bgwriter_lru_maxpages指定的值。例如，如果它的值是500，则后台写数据库进程每次写到物理文件的数据页的个数不
能超过500，若 超过，进程将进入睡眠状态，等下次醒来再执行写物理文件的任务。如果它的值被设为0, 后台写数据库进程将不会
写任何物理文件（但还会执行检查点操作）。
bgwriter_lru_multiplier (floating point)
这个参数只能在文件postgresql.conf中设置。默认值是2.0。它决定后台写数据库进程每次写物理文件时，写到外部文件中的脏数据
块的个数 （不能超过bgwriter_lru_maxpages指定的值）。一般使用默认值即可，不需要修改这个参数。这个参数的值越大，后台写
数据库进程每次写 的脏数据块的个数就越多。
10.4 事务日志
full_page_writes (boolean)
这个参数只能在postgresql.conf文件中被设置。默认值是on。打开这个参数，可以提高数据库的可靠性，减少数据丢失的概率，但
是会产生过多的事务日志，降低数据库的性能。
wal_buffers (integer)
这个参数只有在启动数据库时，才能被设置。默认值是8。它指定事务日志缓冲区中包含的数据块的个数，每个数据块的大小是8KB，
所以默认的事务日志缓冲区的大小是8*8=64KB。事务日志缓冲区位于数据库的共享内存中。
wal_writer_delay (integer)
这个参数只能在postgresql.conf文件中被设置。它决定写事务日志进程的睡眠时间。WAL进程每次在完成写事务日志的任务后，就会
睡眠 wal_writer_delay指定的时间，然后醒来，继续将新产生的事务日志从缓冲区写到WAL文件中。单位是毫秒（millisecond），
默认 值是200。
commit_delay (integer)
这个参数可以在任何时候被设置。它设定事务在发出提交命令以后的睡眠时间，只有在睡眠了commit_delay指定的时间以后，事务产
生的事务日志才会 被写到事务日志文件中，事务才能真正地提交。增大这个参数会增加用户的等待时间，但是可以让多个事务被同
时提交，提高系统的性能。如果数据库中的负载比较 高，而且大部分事务都是更新类型的事务，可以考虑增大这个参数的值。下面
的参数commit_siblings会影响commit_delay是否生效。 默认值是0，单位是微秒（microsecond）。
commit_siblings (integer)
这个参数可以在任何时候被设置。这个参数的值决定参数commit_delay是否生效。假设commit_siblings的值是5，如果一个事务发出
一个提交请求，此时，如果数据库中正在执行的事务的个数大于或等于5，那么该事务将睡眠commit_delay指定的时间。如果数据库
中正在执行的事务 的个数小于5，这个事务将直接提交。默认值是5。
10.5 检查点
checkpoint_segments (integer)
这个参数只能在postgresql.conf文件中被设置。默认值是3。它影响系统何时启动一个检查点操作。如果上次检查点操作结束以后，
系统产生的事 务日志文件的个数超过checkpoint_segments的值，系统就会自动启动一个检查点操作。增大这个参数会增加数据库崩
溃以后恢复操作需要的时 间。
checkpoint_timeout (integer)
这个参数只能在postgresql.conf文件中被设置。单位是秒，默认值是300。它影响系统何时启动一个检查点操作。如果现在的时间减
去上次检查 点操作结束的时间超过了checkpoint_timeout的值，系统就会自动启动一个检查点操作。增大这个参数会增加数据库崩
溃以后恢复操作需要的时 间。
checkpoint_completion_target (floating point)
这个参数控制检查点操作的执行时间。合法的取值在0到1之间，默认值是0.5。不要轻易地改变这个参数的值，使用默认值即可。 这
个参数只能在postgresql.conf文件中被设置。
10.6 归档模式
archive_mode (boolean)
这个参数只有在启动数据库时，才能被设置。默认值是off。它决定数据库是否打开归档模式。
archive_dir (string)
这个参数只有在启动数据库时，才能被设置。默认值是空串。它设定存放归档事务日志文件的目录。
archive_timeout (integer)
这个参数只能在postgresql.conf文件中被设置。默认值是0。单位是秒。如果archive_timeout的值不是0，而且当前时间减去数 据
库上次进行事务日志文件切换的时间大于archive_timeout的值，数据库将进行一次事务日志文件切换。一般情况下，数据库只有在
一个事务日志 文件写满以后，才会切换到下一个事务日志文件，设定这个参数可以让数据库在一个事务日志文件尚未写满的情况下
切换到下一个事务日志文件。
10.7 优化器参数
10.7.1 存取方法参数
下列参数控制查询优化器是否使用特定的存取方法。除非对优化器特别了解，一般情况下，使用它们默认值即可。
enable_bitmapscan (boolean)
打开或者关闭bitmap-scan 。默认值是 on。
enable_hashagg (boolean)
打开或者关闭hashed aggregation。默认值是 on。
enable_hashjoin (boolean)
打开或者关闭hash-join。默认值是 on。
enable_indexscan (boolean)
打开或者关闭index-scan。默认值是 on。
enable_mergejoin (boolean)
打开或者关闭merge-join。默认值是 on。
enable_nestloop (boolean)
打开或者关闭nested-loop join。默认值是 on。不可能完全不使用nested-loop join，关闭这个参数会让系统在有其它存取方法可
用的情况下，不使用nested-loop join。
enable_seqscan (boolean)
打开或者关闭sequential scan。默认值是 on。不可能完全不使用sequential scan，关闭这个参数会让系统在有其它存取方法可用
的情况下，不使用sequential scan。
本节简单介绍了PG查询逻辑优化中的子查询链接(subLink),以EXISTS子链接为例介绍了子查询链接上拉主函数处理逻辑以及使用gdb跟踪分析。
上一节介绍了ANY子链接,本节介绍了EXISTS子链接.
为便于方便解析,根据日志分析,得出查询树如下图所示:
convert_EXISTS_sublink_to_join函数源码:
相关数据结构
1、Var
XX_one_pos
依赖的函数
simplify_EXISTS_query
OffsetVarNodes
IncrementVarSublevelsUp
IncrementVarSublevelsUp
pull_varnos
contain_vars_of_level
query_tree_walker
OffsetVarNodes_walker
query_or_expression_tree_walker
pull_varnos_walker
contain_vars_of_level_walker
expression_tree_walker
本节简单介绍了XLOG全局(所有进程之间)共享的数据结构:XLogCtlData和XLogCtlInsert。在这两个结构体中,存储了REDO point/Lock等相关重要的信息.
XLogCtlInsert
WAL插入记录时使用的共享数据结构
XLogCtl
XLOG的所有共享内存状态信息
跟踪任意一个后台进程,打印全局变量XLogCtl.
其中:
1.XLogCtl-Insert是XLogCtlInsert结构体变量.
2.RedoRecPtr为5510830896 - 1/48789B30,该值与pg_control文件中的REDO location相对应.
3.ThisTimeLineIDPrevTimeLineID,时间线ID,值为1.
其他相关信息可对照结构体定义阅读.
PostgreSQL 源码解读（4）- 插入数据#3（heap_insert）
PG Source Code
有很多种方式可以实现, 比如通过 migrate 等工具强制所有的操作都以统一的方式执行, 这需要开发人员做更多的配合, 所以这类工具在非规模话的业务场景中较难实现; 另外管理员或 DBA 也可以通过知识库比如 redmine 等类似的方式记录变更, 不过不可控因素很多, 特别依赖上线的流程, 也容易出现纰漏. 这就引申出本文要介绍的如何跟踪线上库表的变更, 下文以 MySQL 数据库介绍说明.
跟踪的方式
在 Postgresql 中, 由于触发器对各种操作都有很好的支持, 我们完全可以通过触发器的形式来记录所有 DDL 语句的变更. 与此相比, MySQL 则显得较为弱小, 我们只能以其它方式实现类似的目标. 下面以中间件, log, binlog, 注册 slave, mysqldiff 五种方式进行介绍.
1. 中间件
现有的中间件 atlas, kingshard, mycat 等, 都以 proxy 的角色部署于程序和 MySQL 之间, 所有发往 MySQL 的 sql 都通过 proxy 进行转发. 如下图所示, 我们可以在 proxy 层面增加一些 DDL, DML 相关语句的记录, 达到跟踪变更的目的.
+------+ +-------+ +-------+
| app | --- | proxy | --- | MySQL |
+------+ +-------+ +-------+
这种方式自由度较高, 大家都可以随意定制. 不过需要一些开发能力, 另外 sql 的过滤也会影响到查询的性能, 通过中间件来直接修改表结构等操作也是有风险较大的方式.
2. log
这种方式很简单, 打开 MySQL 的 general log 或 audit log 即可记录所有的 sql 语句. 这种方式比较适合开发环境, 线上环境如果开启会产生很多日志, 弊远远大于利, 也不利于维护;
3. binlog
管理员或 DBA 同样可以解析 MySQL 的 binlog 来过滤表或权限的变更. 这种方式本质上等同第二种方式, 线上数据库需要开启 binlog 选项, 解析 binlog 也是很耗资源的操作. 线上如果实例较多, 这种方式特别不可取.
4. 注册 slave
注册 slave 的意思即通过 MySQL 的主从协议伪造一个假的 slave, 这样 master 会把所有的更新都发送过来, 再进行一些过滤的操作. 这种方式在同步数据或增量消费的场景特别适合, 这里只用于记录表或权限的变更确实是大材小用, 线上实例较多的话也不可取. 典型的工具有 myreplication, tungsten-replicator 以及阿里的 canal 等.
5. mysqldiff
实际上权限和表变更本身是低频率的操作事件, 上述的四种方式虽然都可以达到目标, 但本质上都是很耗费资源的操作. 考虑到这点, 我们可以通过对比的方式来实现权限及表结构变更的跟踪, 详见 sys-mysql-diff 工具. 考虑到通用性, sys-mysql-diff 工具每次都需要获取指定库的所有表的定义语句, 通过对比来生成对应的 DDL 语句. mysqldiff 则是对 sys-mysql-diff 工具的封装, 可以批量跟踪多个实例.
如何使用 mysqldiff
mysqldiff 工具是在 sys-mysql-diff 工具的基础上进行了一层封装, 所以本质上是通过 sys-mysql-diff 工具跟踪线上库的变化. 在实际的运用中, 需要注意以下几点:
1. 配置文件
mysqldiff 所需要的配置参考以下:
[backend]
dsn = user_mysqlmon:xxxxxxxx@tcp(10.0.21.17:3306)/mysqldiff?charset=utf8
[test3301]
host = 10.0.21.5port = 3301db = test
user = user_mysqldiff
pass = xxxxxxxx
tag = host_location
[test3306]
host = 10.0.21.7port = 3306db = percona
user = user_mysqldiff
pass = xxxxxxxx
tag = host_location
2. 权限
所有的变更结果都会保存到指定的 MySQL 库中的 mysql_diff 表, 即上述的 [backend] 部分, 对于该表需要 select, insert, update 相关的权限. 被跟踪的实例则是 [testXXXX] 部分, 由于需要查看表结构和用户权限所以需要 select 和 grant option 权限. 我们以 user_mysqlmon 用户为 [backend] 的用户, 以 user_mysqldiff 为 [testXXXX] 部分的用户为例, 需要赋予他们以下权限:
?
12
grant select,insert,update on mysqldiff.* to user_mysqlmon@`10.0.21.%`;grant select on *.* to user_mysqldiff@`10.0.21.%` with grant option;
配置中的 db = information_schema 则表示跟踪所有的数据库;
3. 运行
运行 mysqldiff 命令进行跟踪:
# ./mysqldiff -conf conf.cnf -verbose2017/03/20 16:31:27 ---------------------------
changes from 10.0.21.5:3301 changes from 10.0.21.7:3306 DROP TABLE `emp`;
SET GLOBAL wait_timeout = 1000;2017/03/20 16:31:27 insert 10.0.21.17:3306/percona ok2017/03/20 16:31:27 ---------------------------
insert ... ok 一行表示将结果插入到了 [backend] 中.