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可指定的故障移轉方式有：
- auto：依mysqlfailover所指定的故障移轉侯選資料庫順序自動做故障移轉，如果在故障移轉序列中沒有找到可用的資料庫，則到所有的從服務器中找故障移轉的資料庫；當故障移轉到從資料庫，且將之升級為主資料庫，其他的從資料庫會將此新的(剛升級的)主資料庫設為主資料庫。如果完全找不到可以作故障移轉的從資料庫，則會產生一個錯誤訊息再結束該命令。
- elect：和auto類似，只是當主資資料庫當掉時，自故障移轉侯選資料庫序列中找不到適合的資料庫，則不會找其他的從資料庫，直接丟錯誤訊息，然後結束該命令。
- 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=
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 – 列示資料庫伺服器的資訊，範例如下：
# 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.
沒有留言:
張貼留言