AntDB/PostgreSQL內部原理：表Page結構解析

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數據源怎么配置

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。

PostgreSQL 源碼解讀（28）- 查詢語句#13（查詢優化-上拉子鏈接#3）

本節簡單介紹了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

PostgreSQL 源碼解讀（112）- WAL#8（XLogCtrl數據結構）

本節簡單介紹了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

如何有效的跟蹤線上 MySQL 實例表和權限的變

有很多種方式可以實現, 比如通過?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]?中.

分享標題：包含postgresql跟蹤的詞條
網址分享：http://www.yijiale78.com/article2/dsccgic.html

成都網站建設公司_創新互聯，為您提供網站建設、ChatGPT、網頁設計公司、網站排名、網站收錄、微信小程序

廣告

聲明：本網站發布的內容（圖片、視頻和文字）以用戶投稿、用戶轉載內容為主，如果涉及侵權請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網站立場，如需處理請聯系客服。電話：028-86922220；郵箱：631063699@qq.com。內容未經允許不得轉載，或轉載時需注明來源： 創新互聯