2013年1月21日 星期一

MySQL Utility的新功能


除了MySQL數据庫,自dev.mysql.com/downloads還能找到不少的好東西,包括了各種connector,proxy等...。其中MySQL Workbench是較少為人注意到的一個軟體,然而對DBA或資料庫開發者,MySQL Workbench是一個能為我們增加生產力的一個利器。用過MySQL Workbench的人大概都知道它有三大功能-SQL Development、Data Modeling、Server Administrator,分別支援以圖形介面自資料庫查詢/修改資料,以正向工程或反向工程,以圖形化介面做資料庫設計,並以此建立資庫的Data Schema和將Data Dictionary轉為圖形化的資料庫設計模型,以及以圖形化介面協助DBA管理MySQL資料庫,包括開、關、調整、授權、調校資料庫等工作。但是我在這裡想要和各位分享的却是MySQL Workbench自5.2版以後的新功能 - MySQL Utility。

MySQL Utility是內嵌於MySQL Workbench的一套以Python開發的script,使我們能不用花工夫寫script,以一道指令執行一連串的操作,例如在不資料庫間抄資料、比對資料、故障移轉、設定主從複製等。除了透過MySQL Workbench,它也可以自launch pad(https://launchpad.net/mysql-utilities)下載。

如果透過MySQL Workbench使用MySQL Utility可自Workbench上列之工具列最右邊的一個工具(如下圖所示)帶出來,或在Workbench的選單中選 'Plugins',再選'Start Shell for MySQL Utilities'即可帶出一個command mode 的terminal。



MySQL Utility提供的script有以下種類:
1. 資料庫操作,包括-
  • mysqldbcompare – 比對兩個database間的差異,包括table definition和table其中的資料 ,在比對table中的資料時會lock該table;另外還可以加上  '--changes-for' 參數,以一個資料庫為主去改另一個資料庫,使兩個資料庫一致化。這個工具在主從複製時,查是否有資料還有資料沒有自主資料庫複製到從資料庫相當有用。

範例如下:

$ mysqldbcompare --server1=root:welcome1@localhost:3308 --server2=root:welcome1@localhost:3306 world:world --run-all-test --show-reverse
# server1 on localhost: ... connected.
# server2 on localhost: ... connected.
# Checking databases world on server1 and world on server2
#
# WARNING: Objects in server1.world but not in server2.world:
#        TABLE: tt
#
#                                                   Defn    Row     Data  
# Type      Object Name                             Diff    Count   Check 
# -------------------------------------------------------------------------
# TABLE     City                                    pass    pass    pass   
# TABLE     Country                                 pass    pass    pass   
# TABLE     CountryLanguage                         pass    FAIL    FAIL   
#
# Row counts are not the same among world.CountryLanguage and world.CountryLanguage.
#

# Rows in world.CountryLanguage not in world.CountryLanguage
+--------------+-----------+-------------+-------------+
| CountryCode  | Language  | IsOfficial  | Percentage  |
+--------------+-----------+-------------+-------------+
| TWN          | Taiya     | F           | 0.2         |
| TWN          | Lukai     | F           | 0.2         |
+--------------+-----------+-------------+-------------+

#
# Rows in world.CountryLanguage not in world.CountryLanguage
# +--------------+-----------+-------------+-------------+
# | CountryCode  | Language  | IsOfficial  | Percentage  |
# +--------------+-----------+-------------+-------------+
# | TWN          | Taiya     | F           | 0.2         |
# | TWN          | Lukai     | F           | 0.2         |
# +--------------+-----------+-------------+-------------+
#
# Database consistency check failed.
#
# ...done

上例顯示比對 在同一個server上,用不同port(3306,3308)的兩個database instance的內容,用3308的資料庫多了一個table – tt,另外用3308的資料庫在CountryCode還多了兩個rows.

  • mysqldbcopy – 指定database object,將database自來源資料庫instance抄到目的資料庫instance,是一個𨗴輯層的抄寫,以登入來源資料庫,下SELECT指令,再INSERT到目的資料庫的方式進行抄寫。但是現在的問題(MySQL 5.6.9 搭配MySQL Workbench 5.2.45)是執行mysqldbcopy時會有 ‘ERROR: Query failed. 1840: GTID_PURGED can only be set when GTID_EXECUTED is empty.’ 的錯誤訊息' ,bug report上說會在5.6.11解決此問題,參考http://bugs.mysql.com/bug.php?id=68038。

  • mysqldbexport – 倒出資料及資料詞典資料,和mysqldump類似,但能有更多的選項,可導出更多種的格式,以 '--format' 指定產出的格式,可產出sql﹑grid﹑csv﹑tab﹑vertical (各column垂直列示) 。如果沒指定 '--export=data' 則只會傳出建table的SQL DDL

範例如下:
$ mysqldbexport --server=root:welcome1@127.0.0.1:3306 --export=data -v world

  • mysqldbimport – 倒入資料及資料詞典資料

  • mysqldiff – 比對不同資料庫物件的定義的差異,和mysqldbcompare不同,它不會比對table內各行的值,只會看table况stored procedure是否有差異。

範例如下:
$mysqldiff --server1=root:welcome1@127.0.0.1:3306 --server2=root:welcome1@127.0.0.1:3308 --difftype=context  world.CountryLanguage:world.CountryLanguage
2. 一般性的作業,包括 -
  • mysqldiskusage – 顯示資料庫對磁碟空間的使用,大致上等同在OS下 ‘du' 的指令。

  • mysqlindexcheck – 檢查是否有多餘的索引。

範例如下:
$ mysqlindexcheck --server=root:welcome1@127.0.0.1:3306 world
# Source on 127.0.0.1: ... connected.
# The following indexes are duplicates or redundant for table world.CountryLanguage:
#
CREATE INDEX CountryCode ON world.CountryLanguage (CountryCode) USING BTREE
#     may be redundant or duplicate of:
ALTER TABLE world.CountryLanguage ADD PRIMARY KEY (CountryCode, Language)


  • mysqlmetagrep – 找尋指定(可加wild card到搜尋字串)的資料物件的定義,包括stored program。

範例如下:

$ mysqlmetagrep --pattern="t%" --server=root:welcome1@127.0.0.1 --format=vertical world
*************************      81. row *************************
  Connection: root:*@127.0.0.1:3306
 Object Type: TABLE
 Object Name: table_io_waits_summary_by_table
    Database: performance_schema
  Field Type: TABLE
     Matches: table_io_waits_summary_by_table
*************************      82. row *************************
  Connection: root:*@127.0.0.1:3306
 Object Type: TABLE
 Object Name: table_lock_waits_summary_by_table
    Database: performance_schema
  Field Type: TABLE
     Matches: table_lock_waits_summary_by_table


  • mysqlprocgrep – 指定類型找show processlist的資訊。


3. 高可用

  • mysqlfailover – 為MySQL 5.6.5以後的MySQL資料庫做自動故障移轉,我個人認為這是MySQL Utilities最有趣的一部份,它能監測主從複製架構中各資料庫的健康狀況(特別是主資料庫),在主資料庫不能使用時自動故障移。要使該程式發生作用,必需將GTID(Global Transaction Identifier)開啟,也就是在my.cnf檔串加上GTID_MODE=ON的參數配置,用戶還以設定—interval參數以秒為單位指定多久去測一次主數据庫的狀況,再以—ping參數設定多少次測試沒回應,就以—failover-mode參數所指定的故障移轉方式進行故障移轉。--failover-mode可指定的故障移轉方式有:
  1. auto:依mysqlfailover所指定的故障移轉侯選資料庫順序自動做故障移轉,如果在故障移轉序列中沒有找到可用的資料庫,則到所有的從服務器中找故障移轉的資料庫;當故障移轉到從資料庫,且將之升級為主資料庫,其他的從資料庫會將此新的(剛升級的)主資料庫設為主資料庫。如果完全找不到可以作故障移轉的從資料庫,則會產生一個錯誤訊息再結束該命令。
  2. elect:和auto類似,只是當主資資料庫當掉時,自故障移轉侯選資料庫序列中找不到適合的資料庫,則不會找其他的從資料庫,直接丟錯誤訊息,然後結束該命令。
  3.  fail:主資料庫當掉時,不做故障移轉,直接丟錯誤訊息,然後結束該命令。

mysqlfailover的使用範例如下:

$ mysqlfailover --master=root:welcome1@127.0.0.1:3306 --discover-slaves-login=root:welcome1
# Discovering slaves for master at 127.0.0.1:3306
# Checking privileges.
# WARNING: You may be mixing host names and IP addresses. This may result in negative status reporting if your DNS services do not support reverse name lookup.
#
# Failover console will start in 10 seconds.


MySQL Replication Failover Utility
Failover Mode = auto     Next Interval = Mon Jan 21 11:34:06 2013

Master Information
------------------
Binary Log File       Position  Binlog_Do_DB  Binlog_Ignore_DB  
ivan-ubuntu-bin.0000  231                                       


當mysqlfailover成功開啟後,在console上可看到以下訊息,它顯示mysqlfailover定期(此處為10秒)ping主資料庫和從資料庫。

GTID Executed Set
CE495FF3-5965-11E2-8479-0023184A0390:1-10 [...]

Replication Health Status
+------------+-------+---------+--------+------------+---------+
| host       | port  | role    | state  | gtid_mode  | health  |
+------------+-------+---------+--------+------------+---------+
| 127.0.0.1  | 3306  | MASTER  | UP     | ON         | OK      |
| localhost  | 3308  | SLAVE   | UP     | ON         | OK      |
| localhost  | 3310  | SLAVE   | UP     | ON         | OK      |
+------------+-------+---------+--------+------------+---------+


上列指令會自動找連上該指定的主資料庫之從資料庫,當成故障移轉序列資料庫;在mysqlfailover的console上可觀查到有一個主資料庫在IP為127.0.0.1,佔用端口為3306,兩個從資料庫分別在localhost,佔用端口為3308及3310。但是要注意的是,在該指令生效前必需在my.cnf中加上以下參數:

report-host=name>
report-port=#>

report-user=>


report-password=
relay-log-info-repository=table
master-info-repository=table

對上列參數的說明請參考http://dev.mysql.com/doc/refman/5.6/en/slave-logs.html

當故障移轉發生時-例如關掉主資料庫,可在mysqlfailover console上看到下列訊息,它顯示mysqlfailover開始做故障移轉,且挑在localhost用3308的從資料庫升級成主資料庫。
Failover starting in 'auto' mode...
# Candidate slave localhost:3308 will become the new master.
# Preparing candidate for failover.

MySQL Replication Failover Utility
Failover Mode = auto     Next Interval = Mon Jan 21 11:47:40 2013

Master Information
------------------
Binary Log File       Position  Binlog_Do_DB  Binlog_Ignore_DB  
ivan-ubuntu-bin.0000  271              
                         

當故障移轉完成時, console上可看到以下訊息,它顯示新的主資料庫在localhost,所用的端口為3308;

GTID Executed Set
68E65F0B-5A40-11E2-8A0B-0023184A0390:1-4 [...]

Replication Health Status
+------------+-------+---------+--------+-----------+---------------------------------------------------------------------+
| host       | port  | role    | state  | gtid_mode |health                                                              |
+------------+-------+---------+--------+-----------+---------------------------------------------------------------------+
| localhost  | 3308  | MASTER  | UP     | ON   |OK                                                                  |
| localhost  | 3310  | SLAVE   | UP     | ON         | SQL thread is not running., Slave has 1 transactions behindmaster. |
+------------+-------+---------+--------+-----------+---------------------------------------------------------------------+


mysqlreplicate – 配置主從複製,該指令可使我們不需登入MySQL客戶端直接設定主從複製架構,範例如下:

$ mysqlreplicate --master=root:welcome1@127.0.0.1:3306 --slave=root:welcome1@127.0.0.1:3308 --master-log-file=ivan-ubuntu-bin.000014 --master-log-pos=231 –rpl-user=root

# master on 127.0.0.1: ... connected.
# slave on 127.0.0.1: ... connected.
# Checking for binary logging on master...
# Setting up replication...

# ...done.

  • mysqlrpladmin – MySQL 5.6.5 為MySQL 5.6.5做主從複製管理、切換、故障移轉;這是除了mysqlfailover之外另一個很用的工具。它使我們能簡化主從複製資料庫的管理工作,能應用系統的交易自主資料庫切換到指定的從資料,同時在切換時會鎖定資料庫,使它不會丟失任何交易;在把交 易切回原主資料庫時,會等原主資料庫的資料都補齊時才會切回來。
    本工具的指令如下:
    • elect:根据GTID自一序列從資料庫中選一個資料庫當成switchover或failover的對象(找GTID跟得最近的)
    •  failover:依elect或指定的從資料庫做故障移轉
範例如下:
$ mysqlrpladmin --master=root:welcome1@127.0.0.1:3306 --discover-slaves-login=root:welcome1 failover

# Discovering slaves for master at 127.0.0.1:3306
# Checking privileges.
# Performing failover.
# Candidate slave localhost:3308 will become the new master.
# Preparing candidate for failover.
# Creating replication user if it does not exist.
# Stopping slaves.
# Performing STOP on all slaves.
# Switching slaves to new master.
# Starting slaves.
# Performing START on all slaves.
# Checking slaves for errors.
# Failover complete.
#
# Replication Topology Health:
+------------+-------+---------+--------+------------+---------+
| host       | port  | role    | state  | gtid_mode  | health  |
+------------+-------+---------+--------+------------+---------+
| localhost  | 3308  | MASTER  | UP     | ON         | OK      |
+------------+-------+---------+--------+------------+---------+

    • gtid:找出指定主資料庫及其從資料庫目前的GTID
範例如下:
$ mysqlrpladmin --master=root:welcome1@127.0.0.1:3306 --discover-slaves-login=root:welcome1 gtid
# Discovering slaves for master at 127.0.0.1:3306
# Checking privileges.
#
# UUIDS for all servers:
+------------+-------+---------+---------------------------------------+
| host       | port  | role    | uuid                                  |
+------------+-------+---------+---------------------------------------+
| 127.0.0.1  | 3306  | MASTER  | ce495ff3-5965-11e2-8479-0023184a0390  |
| localhost  | 3308  | SLAVE   | df6e1330-5965-11e2-847a-0023184a0390  |
| localhost  | 3310  | SLAVE   | 68e65f0b-5a40-11e2-8a0b-0023184a0390  |
+------------+-------+---------+---------------------------------------+
#
# Transactions executed on the server:
+------------+-------+---------+----------------------------------------------+
| host       | port  | role    | gtid                                         |
+------------+-------+---------+----------------------------------------------+
| 127.0.0.1  | 3306  | MASTER  | CE495FF3-5965-11E2-8479-0023184A0390:1-11    |
| 127.0.0.1  | 3306  | MASTER  | DF6E1330-5965-11E2-847A-0023184A0390:1-2     |
| localhost  | 3308  | SLAVE   | 68E65F0B-5A40-11E2-8A0B-0023184A0390:1-4     |
| localhost  | 3308  | SLAVE   | CE495FF3-5965-11E2-8479-0023184A0390:1-11    |
| localhost  | 3308  | SLAVE   | DF6E1330-5965-11E2-847A-0023184A0390:1-17    |
| localhost  | 3310  | SLAVE   | 68E65F0B-5A40-11E2-8A0B-0023184A0390:1-5313  |
| localhost  | 3310  | SLAVE   | CE495FF3-5965-11E2-8479-0023184A0390:1-11    |
| localhost  | 3310  | SLAVE   | DF6E1330-5965-11E2-847A-0023184A0390:1-4     |
+------------+-------+---------+----------------------------------------------+

    • health:列示指定的主資料庫和其從資料庫的狀況
範例如下:
$ mysqlrpladmin --master=root:welcome1@127.0.0.1:3306 --discover-slaves-login=root:welcome1 health
# Discovering slaves for master at 127.0.0.1:3306
# Checking privileges.
#
# Replication Topology Health:
+------------+-------+---------+--------+------------+---------+
| host       | port  | role    | state  | gtid_mode  | health  |
+------------+-------+---------+--------+------------+---------+
| 127.0.0.1  | 3306  | MASTER  | UP     | ON         | OK      |
| localhost  | 3308  | SLAVE   | UP     | ON         | OK      |
| localhost  | 3310  | SLAVE   | UP     | ON         | OK      |
+------------+-------+---------+--------+------------+---------+

    • reset:清掉所有的主從複製的設定,相當於在所有的從資料庫上下'stop slave; reset slave all;'的指令
範例如下:
$ mysqlrpladmin --master=root:welcome1@127.0.0.1:3306 --discover-slaves-login=root:welcome1 reset
# Discovering slaves for master at 127.0.0.1:3306
# Checking privileges.
# Performing STOP on all slaves.
#   Executing stop on slave localhost:3308 Ok
#   Executing stop on slave localhost:3310 Ok
# Performing RESET on all slaves.
#   Executing reset on slave localhost:3308 Ok
#   Executing reset on slave localhost:3310 Ok
# ...done.
    • start:開始主從複製,相當於在所有的從資料庫下'start slave;'
    • stop:關掉主從複製,相當於在所有的從資料庫下'stop slave;'
    • switchover:主從複製架構中,主從角色對掉

範例如下:

$ mysqlrpladmin --master=root:welcome1@127.0.0.1:3306 --new-master=root:welcome1@127.0.0.1:3308 switchover
# Checking privileges.
# Performing switchover from master at 127.0.0.1:3306 to slave at 127.0.0.1:3308.
# Checking candidate slave prerequisites.
# Waiting for slaves to catch up to old master.
# Stopping slaves.
# Performing STOP on all slaves.
# Switching slaves to new master.
# Starting all slaves.
# Performing START on all slaves.
# Checking slaves for errors.
# Switchover complete.
#
# Replication Topology Health:
+------------+-------+---------+--------+------------+---------+
| host       | port  | role    | state  | gtid_mode  | health  |
+------------+-------+---------+--------+------------+---------+
| 127.0.0.1  | 3308  | MASTER  | UP     | ON         | OK      |
+------------+-------+---------+--------+------------+---------+

在執行前要確認複製的設定都正,例如,配置成兩個主資料庫,要清乾淨原來的從資料庫設定要用'reset slave all;'指令

  • mysqlrplcheck – 檢查主從複製的配置,範例如下:

$ mysqlrplcheck --master=root:welcome1@127.0.0.1:3308 --slave=root:welcome1@127.0.0.1:3310 –show-slave-status

# master on 127.0.0.1: ... connected.
# slave on 127.0.0.1: ... connected.
Test Description                                                     Status
---------------------------------------------------------------------------
Checking for binary logging on master                                [pass]
Are there binlog exceptions?                                         [pass]
Replication user exists?                                             [pass]
Checking server_id values                                            [pass]
Checking server_uuid values                                          [pass]
Is slave connected to master?                                        [pass]
Check master information file                                        [pass]
Checking InnoDB compatibility                                        [pass]
Checking storage engines compatibility                               [pass]
Checking lower_case_table_names settings                             [pass]
Checking slave delay (seconds behind master)                         [pass]

#
# Slave status: 
#
                Slave_IO_State : Waiting for master to send event
                   Master_Host : localhost
                   Master_User : root
                   Master_Port : 3308
                 Connect_Retry : 60
               Master_Log_File : ivan-ubuntu-bin.000010
           Read_Master_Log_Pos : 271
                Relay_Log_File : ivan-ubuntu-relay-bin.000001
                 Relay_Log_Pos : 4
         Relay_Master_Log_File : 
              Slave_IO_Running : Yes
             Slave_SQL_Running : No
               Replicate_Do_DB : 
           Replicate_Ignore_DB : 
            Replicate_Do_Table : 
        Replicate_Ignore_Table : 
       Replicate_Wild_Do_Table : 
   Replicate_Wild_Ignore_Table : 
                    Last_Errno : 1593
                    Last_Error : Failed during slave workers initialization
                  Skip_Counter : 0
           Exec_Master_Log_Pos : 0
               Relay_Log_Space : 7705
               Until_Condition : None
                Until_Log_File : 
                 Until_Log_Pos : 0
            Master_SSL_Allowed : No
            Master_SSL_CA_File : 
            Master_SSL_CA_Path : 
               Master_SSL_Cert : 
             Master_SSL_Cipher : 
                Master_SSL_Key : 
         Seconds_Behind_Master : None
 Master_SSL_Verify_Server_Cert : No
                 Last_IO_Errno : 0
                 Last_IO_Error : 
                Last_SQL_Errno : 1593
                Last_SQL_Error : Failed during slave workers initialization
   Replicate_Ignore_Server_Ids : 
              Master_Server_Id : 2
                   Master_UUID : df6e1330-5965-11e2-847a-0023184a0390
              Master_Info_File : mysql.slave_master_info
                     SQL_Delay : 0
           SQL_Remaining_Delay : None
       Slave_SQL_Running_State : 
            Master_Retry_Count : 86400
                   Master_Bind : 
       Last_IO_Error_Timestamp : 
      Last_SQL_Error_Timestamp : 130121 11:42:31
                Master_SSL_Crl : 
            Master_SSL_Crlpath : 
            Retrieved_Gtid_Set : DF6E1330-5965-11E2-847A-0023184A0390:5-17
             Executed_Gtid_Set : 68E65F0B-5A40-11E2-8A0B-0023184A0390:1-5313,
CE495FF3-5965-11E2-8479-0023184A0390:1-10,
DF6E1330-5965-11E2-847A-0023184A0390:1-4
# ...done.

  • mysqlrplshow – 列示複製結構的拓樸圖,範例如下:

$ mysqlrplshow --master=root:welcome1@127.0.0.1:3308 --discover-slaves-login=root:welcome1
# master on 127.0.0.1: ... connected.
# Finding slaves for master: 127.0.0.1:3308

# Replication Topology Graph
127.0.0.1:3308 (MASTER)
   |
   +--- 127.0.0.1:3310 - (SLAVE)

  • mysqlserverclone – 在空的資料庫中以現有的資料庫為範本建一個新的資料庫,範例如下:

$ mysqlserverclone --server=root:welcome1@localhost:3306 --new-data=./data4 --new-port=3312 --new-id=4 --root-password=welcome1
# Cloning the MySQL server running on localhost.
# Creating new data directory...
# Configuring new instance...
# Locating mysql tools...
# Setting up empty database and mysql tables...
# Starting new instance of the server...
# Testing connection to new instance...
# Success!
# Setting the root password...
# Connection Information:
#  -uroot -pwelcome1 --socket=/home/ivan/data4/mysql.sock
#...done.

我原本以為會以指定的資料庫instance為範本-在localhost上用3306端口,完全複製同樣內容的資料庫instance到./data4,同時用3312為端口;但是出乎我意料之外的是,我只看到一個空的資料庫instance,只有預訊的資料庫 -如mysql,information_schema,先前我在第一個資料庫instance以手動方式建的資料庫在新的資料庫完全看不到。

  • mysqlserverinfo – 列示資料庫伺服器的資訊,範例如下:
$ mysqlserverinfo --server=root:welcome1@127.0.0.1:3308 --format=vertical
# Source on 127.0.0.1: ... connected.

*************************       1. row *************************
         server: 127.0.0.1:3308
        version: 5.6.9-rc-log
        datadir: /mysql56/data2/
        basedir: /home/ivan/mysql-5.6.9-rc-linux-glibc2.5-i686
     plugin_dir: /home/ivan/mysql-5.6.9-rc-linux-glibc2.5-i686/lib/plugin/
    config_file: /etc/mysql/my.cnf, /home/ivan/mysql-5.6.9-rc-linux-glibc2.5-i686/my.cnf
     binary_log: ivan-ubuntu-bin.000010
 binary_log_pos: 271
      relay_log: ivan-ubuntu-relay-bin.000001
  relay_log_pos: 4
1 rows.
#...done.