MySQL欄位加密工具：MyDiamo

這是一套主要針對MySQL Fork(MySQL, Percona Server, MariaDB)的欄位進行加密管理的第三方工具
它可以對欄位設定不同的加密方式，且加密、解密的權限可獨立分開設定
另外，它還支援連線管控及稽核的功能
詳細的功能就上官網查詢

  • MyDiamo運作原理
    它是屬於MySQL管理層和Storage Engine層之間的一個Plugin，透過自行設定的原則來對InnoDB或MyISAM表的欄位進行加密。(如圖一)
    軟體安裝好後，它有自已的管理介面，要在這個介面中先設定好【原則】，接著在此原則之下設定有哪些【欄位】要使用這個原則，再來就是設定有哪些【使用者】被授予這些欄位的權限為何，最後再執行plugin所屬的migration funcation對資料表欄位進行加密。


圖一

  • 在安裝前要先註冊帳號，它才會把軟體下載的連結寄給你…

  • MyDiamo安裝
    在下載軟體的時候要注意，它不是全部的版本都支援的，它只支援部份小版本，例如：MySQL 5.7.12不支援…等，所以要先確認目前資料庫的版本再來下載，更多的版本內容請看官網啦：Support Platform

    如果你的MySQL是用RPM方式安裝的話，遇到的問題相對會少一些
    我的測試環境是以tar安裝，很多路徑是要另外做設定的(我懶沒設啦…)所以會多一些步驟

    ◆安裝
    1. [root@mysql-course D`Amo_MyDiamo_v3.0.9.4]# ./install-linux-mydiamo-3.0.9.4.run -p /opt/mysql -h 127.0.0.1 -P 3307
    2.  
    3. =====================================================
    4.              MyDiamo Install Script
    5. =====================================================
    6.  
    7.  
    8. Input MySQL/MariaDB root Password :  
    9.  
    10. =====================================================
    11. NDB Cluster : No
    12. DBMS : MySQL
    13. VERSION : 5.7.16
    14. COMPILE MACHINE : 64 bit
    15. MySQL/MariaDB CONFIG : normal
    16. MySQL/MariaDB PLUGIN DIR : /opt/mysql/lib/plugin/
    17. DAMO_INST_HOME : /mydiamo
    18. CIS_CC_HMAC_PATH : /mydiamo/lib64
    19. MANUAL INSTALLATION : No
    20.  
    21. =======================================================
    22.  Program Copy Success
    23. =======================================================
    24. 3.0.9.4/64/ha_damo-mysql-5.7.16-normal.so.md5
    25. 3.0.9.4/64/ha_damo-mysql-5.7.16-normal.so
    26. ha_damo-mysql-5.7.16-normal.so file was decompressed successfully.
    27. ha_damo-mysql-5.7.16-normal.so.md5 file was decompressed successfully.
    28.  
    29. =======================================================
    30. Must restart the MySQL/MariaDB service.
    31. 1) Yes
    32. 2) No
    33. Would you like to MySQL/MariaDB restart now? 1
    34.  
    35. =======================================================
    36.  Stop MySQL/MariaDB service
    37. =======================================================
    38. Shutting down MySQL.. SUCCESS! 
    39.  
    40. =======================================================
    41.  Start MySQL/MariaDB service
    42. =======================================================
    43. Starting MySQL... SUCCESS! 
    44. =======================================================
    45.  Program Install Success
    46. =======================================================
    47.  Check your environment...
    48.  Check DAMO_INST_HOME in the /etc/damo.conf file.
    49.  Check CIS_CC_HMAC_PATH in the /etc/damo.conf file. 
    50. =======================================================
    51. [root@mysql-course mydiamo]# ls /mydiamo/
    52. bin        damo_kms.conf  damo_lms.conf.in  lib    log         sql      uninstall_program.sh
    53. copyright  damo_lms.conf  key               lib64  readme.txt  support  version.txt
    54.  
    55. [root@mysql-course mydiamo]# mysql --login-path=mysql
    56. root@127.0.0.1 [(none)]>show plugins;
    57. +----------------------------+----------+--------------------+-----------------+---------+
    58. | Name                       | Status   | Type               | Library         | License |
    59. +----------------------------+----------+--------------------+-----------------+---------+
    60. | DAMO_MYISAM                | ACTIVE   | STORAGE ENGINE     | ha_damo.so      | GPL     |
    61. | DAMO_INNODB                | ACTIVE   | STORAGE ENGINE     | ha_damo.so      | GPL     |
    62. +----------------------------+----------+--------------------+-----------------+---------+

  • 產生新的key
    1. [root@mysql-course mydiamo]# ./bin/keygen -a -N /mydiamo/key/ -L
    2. =====================================================================
    3.  Warning:
    4.  Incorrect handling of keygen may lead to a malfunction of MyDiamo.
    5.  Please check the manual before attempting to make any changes.
    6. =====================================================================
    7.  
    8. Enter the Site-Key Password: Abcd!234
    9. Enter the Site-Key Confirm Password: Abcd!234
    10. Enter the Console-Key Password: Abcd!234
    11. Enter the Console-Key Confirm Password: Abcd!234
    12. Enter the DB-Key Password: Abcd!234
    13. Enter the DB-Key Confirm Password: Abcd!234
    14. Generate Site Key Pair...
    15. Generate Console Key Pair...
    16. Generate DB Key Pair...
    17. [root@mysql-course mydiamo]# ls key/
    18. damo_console.cer  damo_db.cer  damo_site.cer
    19. damo_console.key  damo_db.key  damo_site.key

  • 啟動keymanager

    1. [root@mysql-course mydiamo]# ./bin/keymanager_cis-3.2 -start
    2.  
    3. Log in to start software KeyManager.
    4. Enter the DB-key Password : Abcd!220
    5. parent end
    6. server port [0]
    7. cert file [/mydiamo/key/damo_db.cer]
    8. pri  file [/mydiamo/key/damo_db.key]
    9. encrypt cert data
    10. encrypt pri data
    11. lnAddress [4924930]
    12. shmget [720900]

  • 進入Console
    1. [root@mysql-course mydiamo]# ./bin/damo_CLI -start
    2.  
    3.  
    4.  =====================================================
    5.  |                                                   |
    6.  |  Copyright (C) 2014 PENTA SECURITY SYSTEMS, INC.  |
    7.  |  All rights reserved                              |
    8.  |                                                   |
    9.  |  MyDiamo command line interface(CLI)              |
    10.  |                                                   |
    11.  =====================================================
    12.  
    13.  
    14.  
    15. Login to start CLI.
    16. Enter the Console-key Password :
    17. [DAMO-2001] 'license.damo' does not exist or correct.
    18.  
    19. There is something wrong with the license file.
    20. Please reissue the new AuthID and AuthCode at MyDiamo website, and active MyDiamo again.
    21.  
    22. Input ID : MDFL-xxxxxxxx-2017-03-22_16-55-36    <= Email裡會提供
    23. Input Auth Code : 78c78d22ddjdosw802js5beab0356670a1d43b   <= Email裡會提供
    24. Succeeded to receive data from server.

  • 設定原則Policy
    1. MyDiamo > create policy nameFull"AES"VIV"0"0"CBC"RAW  ###設定整個欄位進行加密
    2. CREATE POLICY policy[nameFull] alg[AES] iv[VIV] partial-enc-pos[0] partial-enc-off[0], block mode[CBC], encode mode[RAW], enc flag[]
    3. MyDiamo > create policy namePart"AES"VIV"3"6"CBC"RAW    ###設定只對中間部份字串加密
    4. CREATE POLICY policy[namePart] alg[AES] iv[VIV] partial-enc-pos[3] partial-enc-off[6], block mode[CBC], encode mode[RAW], enc flag[]
    5.  
    6. MyDiamo > create policy salary"AES"FIV"0"0"CFB"RAW  ###對數字進行加密只能使用FIV、CFB、RAW模式
    7. CREATE POLICY policy[salary] alg[AES] iv[FIV] partial-enc-pos[0] partial-enc-off[0], block mode[CFB], encode mode[RAW], enc flag[]
    8.  
    9. MyDiamo > show policy                                                                    
    10.  
    11. ------------<< All Policy ID Info. >>-------------
    12. +---------+------------+-----------+---------------+------------------+----------+-----------+--------+
    13. |Policy ID|Enc. Alg.   |IV mode    |Partial Enc Pos|Partial Enc Offset|Block mode|Encode mode|Enc Flag|
    14. +---------+------------+-----------+---------------+------------------+----------+-----------+--------+
    15. |nameFull |AES 128 bits|Variable IV|Non-Partial Enc|Non-Partial Enc   |CBC       |RAW        |        |
    16. |namePart |AES 128 bits|Variable IV|3              |6                 |CBC       |RAW        |        |
    17. |salary   |AES 128 bits|Fixed IV   |Non-Partial Enc|Non-Partial Enc   |CFB       |RAW        |        |
    18. +---------+------------+-----------+---------------+------------------+----------+-----------+--------+
    19. There are 3 policies.
    20. ----------------------------------------
    21.  SG-KMS Mode : NO KMS
    22. ----------------------------------------

  • 設定要加密的欄位
    語法說明：SET COLUMN <資料庫>"<資料表>"<欄位>"<原則名稱> 
    1. MyDiamo > set column world"City"Name"nameFull
    2. SET COLUMN O[world] T[City] C[Name] P[nameFull]
    3. MyDiamo > set column world"Country"Name"namePart
    4. SET COLUMN O[world] T[Country] C[Name] P[namePart]
    5. MyDiamo > set column world"Salary"salary"salary
    6. SET COLUMN O[world] T[Salary] C[salary] P[salary]
    7. MyDiamo > show column
    8.  
    9. --------<< All column info >>---------------------
    10. +-----+-------+------+---------+
    11. |Owner|Table  |Column|Policy ID|
    12. +-----+-------+------+---------+
    13. |world|City   |Name  |nameFull |
    14. |world|Country|Name  |namePart |
    15. |world|Salary |salary|salary   |
    16. +-----+-------+------+---------+
    17. There are 3 encrypted columns.

  • 使用者授權
    使用者要先在資料庫中建立並且給予適當的權限，在此僅設定針對某特定欄位的加、解密權限
    1. 語法說明：SET PRIV ENC <使用者帳號>"<資料庫>"<資料表>"<欄位>"<加密0,1>"<解密0,1>
    2. MyDiamo > set priv enc app"world"City"Name"1"0
    3. SET PRIV ENC U[app] O[world] T[City] C[Name] EP[1] DP[0]
    4. MyDiamo > set priv enc app"world"Country"Name"1"1
    5. SET PRIV ENC U[app] O[world] T[Country] C[Name] EP[1] DP[1]
    6. MyDiamo > set priv enc app"world"Salary"salary"1"0
    7. SET PRIV ENC U[app] O[world] T[Salary] C[salary] EP[1] DP[0]
    8. MyDiamo > set priv enc accountant"world"Salary"salary"1"1
    9. SET PRIV ENC U[accountant] O[world] T[Salary] C[salary] EP[1] DP[1]
    10. MyDiamo > SET ENV DECRYPT DENY MASKING   ###這個設定是讓沒有權限解密的使用者看到欄位上出現**符號，沒有設定的話會直接reject查詢
    11. SET ENV DECRYPT DENY MASKING  
    12.  
    13. MyDiamo > show priv enc
    14.  
    15. ------<< All User's enc/dec privilege info >>-----
    16. +----------+-----+-------+------+--------+--------+
    17. |User      |Owner|Table  |Column|Enc priv|Dec priv|
    18. +----------+-----+-------+------+--------+--------+
    19. |app       |world|City   |Name  |Yes     |No      |
    20. |app       |world|Country|Name  |Yes     |Yes     |
    21. |app       |world|Salary |salary|Yes     |No      |
    22. |accountant|world|Salary |salary|Yes     |Yes     |
    23. +----------+-----+-------+------+--------+--------+
    24. There are 4 enc/dec privilege settings.
    25.  Decrypt deny mode      : Masking
    26. MyDiamo > save all
    27. SAVE ALL

  • 進資料庫將剛剛的設定生效
    1. 指令說明：call mysql.damo_table_migration('資料庫','資料表');
    2. "root@127.0.0.1 [(none)]>call mysql.damo_table_migration('world','City');
    3. +-----------+
    4. | PARTITION |
    5. +-----------+
    6. |           |
    7. +-----------+
    8. 1 row in set (0.51 sec)
    9.  
    10. +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    11. | ENC MIGRATION COMMAND                                                                                                                                                                                                                                                                                                                                                                                                                                                                             |
    12. +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    13. | CREATE TABLE world.City ( ID int(11) NOT NULL  auto_increment, Name char(52) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL  DEFAULT '', CountryCode char(3) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL  DEFAULT '' , District char(20) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL  DEFAULT '' , Population int(11) NOT NULL  DEFAULT '0' , KEY CountryCode (CountryCode), PRIMARY KEY  (ID) ) ENGINE = DAMO_INNODB DEFAULT CHARSET=latin1  COMMENT ' (Enc.)'; |
    14. +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    15. 1 row in set (0.51 sec)
    16.  
    17. +---------------------------------------------+
    18. | ORG TABLE RENAME COMMAND                    |
    19. +---------------------------------------------+
    20. | RENAME TABLE world.City TO world.City_damo; |
    21. +---------------------------------------------+
    22. 1 row in set (0.51 sec)
    23.  
    24. +-------------------------------------------------------+
    25. | DATA INSERT COMMAND                                   |
    26. +-------------------------------------------------------+
    27. | INSERT INTO world.City SELECT * FROM world.City_damo; |
    28. +-------------------------------------------------------+
    29. 1 row in set (1.00 sec)
    30.  
    31. +-----------------------------+
    32. | TEMP TABLE DROP COMMAND     |
    33. +-----------------------------+
    34. | DROP TABLE world.City_damo; |
    35. +-----------------------------+
    36. 1 row in set (2.23 sec)
    37.  
    38. +-------------------------------------------------------------------------------------------------------------------------+
    39. | DEC MIGRATION COMMAND                                                                                                   |
    40. +-------------------------------------------------------------------------------------------------------------------------+
    41. | ALTER TABLE world.City MODIFY Name char(35) COLLATE latin1_swedish_ci NOT NULL  DEFAULT '', ENGINE = InnoDB COMMENT ''; |
    42. +-------------------------------------------------------------------------------------------------------------------------+
    43. 1 row in set (2.55 sec)
    44.  
    45. Query OK, 0 rows affected (2.55 sec)
  • 驗證欄位是否有加密
    1. 用app使用者登入，他world.City.Name、world.Salary.salary有加密的權限，沒有解密的權限
    2. [root@mysql-course mydiamo]# mysql -h127.0.0.1 -P3307 -uapp -papp world
    3. "app@127.0.0.1 [world]>select * from City limit 2;
    4. +----+-------------------------------------------------+-------------+----------+------------+
    5. | ID | Name                                            | CountryCode | District | Population |
    6. +----+-------------------------------------------------+-------------+----------+------------+
    7. |  1 | *********************************************** | AFG         | Kabol    |    1780000 |
    8. |  2 | *********************************************** | AFG         | Qandahar |     237500 |
    9. +----+-------------------------------------------------+-------------+----------+------------+
    10. 2 rows in set (0.01 sec)
    11.  
    12. "app@127.0.0.1 [world]>select * from Salary limit 2;
    13. +-----+------------+--------+-------+
    14. | eid | name       | salary | bonus |
    15. +-----+------------+--------+-------+
    16. |   1 | Peter Pan  |      0 |  2000 |  ###因型態限制關係，加密後只會把正確的值變成0，而不會變成*
    17. |   2 | Judy Wendy |      0 | 20000 |
    18. +-----+------------+--------+-------+
    19. 2 rows in set (0.01 sec)
    20.  
    21. 用accountant使用者登入，他只有對world.Salary.salary加解密權限，對world.City.Name、world.Country.Name沒有解密權限
    22. [root@mysql-course mydiamo]# mysql -h127.0.0.1 -P3307 -uaccountant -paccountant world
    23. "accountant@127.0.0.1 [world]>select * from City limit 2;
    24. +----+-------------------------------------------------+-------------+----------+------------+
    25. | ID | Name                                            | CountryCode | District | Population |
    26. +----+-------------------------------------------------+-------------+----------+------------+
    27. |  1 | *********************************************** | AFG         | Kabol    |    1780000 |
    28. |  2 | *********************************************** | AFG         | Qandahar |     237500 |
    29. +----+-------------------------------------------------+-------------+----------+------------+
    30. 2 rows in set (0.01 sec)
    31.  
    32. "accountant@127.0.0.1 [world]>select * from Country limit 2;
    33. +------+-------------+---------------+---------------------------+-------------+-----------+------------+----------------+---------+--------+-----------------------+----------------------------------------------+---------------+---------+-------+
    34. | Code | Name        | Continent     | Region                    | SurfaceArea | IndepYear | Population | LifeExpectancy | GNP     | GNPOld | LocalName             | GovernmentForm                               | HeadOfState   | Capital | Code2 |
    35. +------+-------------+---------------+---------------------------+-------------+-----------+------------+----------------+---------+--------+-----------------------+----------------------------------------------+---------------+---------+-------+
    36. | ABW  | Ar******    | North America | Caribbean                 |      193.00 |      NULL |     103000 |           78.4 |  828.00 | 793.00 | Aruba                 | Nonmetropolitan Territory of The Netherlands | Beatrix       |     129 | AW    |
    37. | AFG  | Af******tan | Asia          | Southern and Central Asia |   652090.00 |      1919 |   22720000 |           45.9 | 5976.00 |   NULL | Afganistan/Afqanestan | Islamic Emirate                              | Mohammad Omar |       1 | AF    |
    38. +------+-------------+---------------+---------------------------+-------------+-----------+------------+----------------+---------+--------+-----------------------+----------------------------------------------+---------------+---------+-------+
    39. 2 rows in set (0.00 sec)
    40.  
    41. "accountant@127.0.0.1 [world]>select * from Salary;
    42. +-----+------------+--------+-------+
    43. | eid | name       | salary | bonus |
    44. +-----+------------+--------+-------+
    45. |   1 | Peter Pan  | 300000 |  2000 |
    46. |   2 | Judy Wendy |  70000 | 20000 |
    47. +-----+------------+--------+-------+
    48. 2 rows in set (0.01 sec)


  • Debug集

    1. 找不到mysqlbug
      1. [root@mysql-course mydiamo]#./install-linux-mydiamo-3.0.9.4.run -p /opt/mysql/bin -h 127.0.0.1 -P 3307
      2.  
      3. =====================================================
      4.              MyDiamo Install Script
      5. =====================================================
      6.  
      7. [root@mysql-course D`Amo_MyDiamo_v3.0.9.4]# mysqlbug file does not exist(/opt/mysql/bin//mysql/mysqlbug).

      解法一：把mysqlbug設軟連結
      1. [root@mysql-course bin]# ln -s mysqld-debug mysqlbug
      2. [root@mysql-course bin]# ls -l mysqlbug 
      3. lrwxrwxrwx 1 root root 12 Mar 22 14:01 mysqlbug -> mysqld-debug
      4.  
      5. # ./install-linux-mydiamo-3.0.9.4.run -p /opt/mysql/bin -h 127.0.0.1 -P 3307
      6.  
      7. =====================================================
      8.              MyDiamo Install Script
      9. =====================================================
      10.  
      11. [root@mysql-course D`Amo_MyDiamo_v3.0.9.4]# mysqlbug file does not exist(/opt/mysql/bin//mysql/mysqlbug).
      結果還是找不到檔案阿ⓞ﹏ⓞ
      解法二： 搞了半天，是它在編碼裡面寫死了不管你前面帶的目錄是什麼，它就是依你給的目錄下找~./mysql/mysqlbug
      所以最後我只好…把整個bin目錄做了軟連結(有夠蠢)
      1. [root@mysql-course mysql]# ln -s /opt/mysql/bin /opt/mysql/mysql
      2. [root@mysql-course mysql]# ls -l /opt/mysql/mysql
      3. lrwxrwxrwx 1 root root 14 Mar 22 14:06 /opt/mysql/mysql -> /opt/mysql/bin
    2. The table has no privileges. Please check enc/dec or access privileges.
      我用root權限在做migration的時候遇到這個狀況，是因為我的root沒有設定任何加解密的權，所以沒辬法執行這個指令，只需要把root帳號也設定權限即可
      1. "root@127.0.0.1 [(none)]>call mysql.damo_table_migration('world','Salary');
      2. +----------------------------------------------------------------+
      3. | error                                                          |
      4. +----------------------------------------------------------------+
      5. | Selected policy is not supported or has incorrect information. |
      6. +----------------------------------------------------------------+
      7. 1 row in set (0.41 sec)
      8.  
      9. MyDiamo > set priv enc root"world"City"Name"1"1
      10. SET PRIV ENC U[root] O[world] T[City] C[Name] EP[1] DP[1]
      11. MyDiamo > set priv enc root"world"Country"Name"1"1
      12. SET PRIV ENC U[root] O[world] T[Country] C[Name] EP[1] DP[1]
      13. MyDiamo > set priv enc root"world"Salary"salary"1"1
      14. SET PRIV ENC U[root] O[world] T[Salary] C[salary] EP[1] DP[1]
      15. MyDiamo > save all
      16. SAVE ALL 
      17.  
      18. "root@127.0.0.1 [(none)]>call mysql.damo_table_migration('world','Salary');
      19. +-----------+
      20. | PARTITION |
      21. +-----------+
      22. |           |
      23. +-----------+
      24. 1 row in set (0.23 sec)
      25. .......
      26. Query OK, 0 rows affected (1.06 sec)

    指令參考官方手冊：https://mydiamo.com/guide/operation/encryption/

    留言

    張貼留言

    這個網誌中的熱門文章

    MySQL監控軟體MEM及PMM介紹

    圖片
    MySQL監控軟體： PMP(Percona Monitoring Plugin) ：Percona之前的監控是以Plugin或Template的方式加載到其他的監控軟體上，他有支援Zabbix、Nagios和Cacti等3個常用的監控軟體，詳細的介紹可以參考官網： Percona Monitoring Plugins MEM(MySQL Enterprise Monitor) ：官方提供的監控軟體，需購企業版才能使用此功能，可以監控DB內的狀況和InnoDB、NDB、Replication等的內容，另外還可以監控作業系統上的部份內容，例如硬碟、CPU、RAM、網路…等使用狀況，除了監控之外，還有Query Analyzer及發送告警功能，詳細的介紹參考官網： MySQL Enterprise Monitor PMM(Percona Monitoring and Management) ：它是一個用於管理及監控MySQL及MongoDB效能的開源平台，它提供了對MyISAM、InnoDB、TokuDB和PXC/Glarera的監控，另外也提供了與MEM類似的Query Analytics的功能，可以檢視執行了哪些SQL指令，並對執行效能較差的語法進行優化；針對作業系統的部份也提供了硬碟、網路、CPU和RAM的監控，特別的是它提供了Context switches、Processes和interrupts的監控，Context Switches可以看到CPU上下切換文的狀況，Processes可以方便了解系統有多少程序在等待I/O，這3個是MEM沒有提供的。詳細內容參考官方： Percona Monitoring and Management 架構說明： MEM架構 MEM是由Java+Apache Tomcat+MySQL組成的監控軟體 主要安裝Service Manager(也可稱Monitor Server)它有內建一個Agent可以讓你遠端監控 或者是另外在MySQL Server上安裝Agent軟體，其差別在於能否搜集作業系統上的資訊，裝了Agent才能在作業系統上搜集資訊 而DB端的資訊會透過本機或Service Manager的Agent，連到DB裡的Performance Schema中搜集資訊，再儲存到Servi...
    閱讀完整內容

    MySQL Router 設定檔說明

    設定檔範例： 　 　 　 [DEFAULT] logging_folder  = /opt/mysql-router/log plugin_folder   = /opt/mysql-router/lib/mysqlrouter runtime_folder  = /opt/mysql-router/run config_folder   = /opt/mysql-router [logger] level = INFO ## these section are only for standalone [routing:read_write] bind_address    = 192.168.56.107 bind_port       = 7001 destinations    = 192.168.56.102:3306,192.168.56.102:3307 mode            = read-write connect_timeout    = 5 max_connections   = 100 [routing:read_only] bind_address    = 192.168.56.107 bind_port       = 7002 destinations    = 192.168.56.102:3308,192.168.56.102:3309 mode            = read-only connect_timeout    = 1 max_connections   = 100 ## these section are used for Fabric [fabric_cache:my_fabric] address    = 192.168.56.101 user   ...
    閱讀完整內容

    【工作筆記】SQL Timeout追蹤

    圖片
    幾個禮拜前去客戶那處理『SQL效能問題』，到場後，問題延伸為『SQL不定期發生Timeout的問題』 【客戶問題描述】 AP會建立100多個連線，且同時會將程式用幾十個連線來並行處理事務，會在不固定的時間會發生SQL Timeout的問題，由程式端無法定位是哪句SQL以及哪支程式導致的問題，在DB端也不會看到任何的錯誤訊息及警告訊息出現在Error Log中  【架構背景】 OS: CentOS 6.8, CPU 20 Cores, RAM 64G, HDD RAID 10 DB: MariaDB 10.0.1 Galera Cluster 【問題追踨】 在執行壓測過程中監控作業系統的狀態，包括CPU、RAM、IO的負載狀況 =>監控的結果CPU的load average大約在1，RAM有使用到少量的SWAP，cached的部份還有滿多可以使用，IO的負載不算太高，整體看來應該不是硬體方面造成的問題 在執行壓測過程中監控資料庫的錯誤日誌、慢查詢記錄、show processlist的狀況 =>錯誤日誌並未記錄到任何的錯誤及警告訊息，慢查詢只有其他資料庫中全表掃描的記錄，show processlist看到多數的連線均為sleep狀態，且SQL執行的時間都很短 在執行壓測過程中監控InnoDB的狀態 ------------------------ LATEST DETECTED DEADLOCK ------------------------ 2017-01-17 16:02:22 7fd7a00e6700 *** (1) TRANSACTION: TRANSACTION 755310052, ACTIVE 2 sec fetching rows mysql tables in use 1, locked 1 LOCK WAIT 6 lock struct(s), heap size 1184, 154 row lock(s) MySQL thread id 2916595, OS thread handle 0x7fdd2d9fe700, query id 213093063 192.168.3.2 tsap Sending data SELECT id, cid, time, retry FROM ss WHE...
    閱讀完整內容