mysql-utilities工具集是一個集中了多種工具的合集，可以了解為是DBA的工具箱，本文介紹利用其中的mysqlreplicate工具來快速搭建MySQL主從環境。
HE1:192.168.1.248 slave
HE3:192.168.1.250 master
Part1:安裝mysql-utilities
[root@HE1 ~]# tar xvf mysql-utilities-1.5.4.tar.gz
[root@HE1 ~]# cd mysql-utilities-1.5.4
[root@HE1 mysql-utilities-1.5.4]# python setup.py build
[root@HE1 mysql-utilities-1.5.4]# python setup.py install
如何安裝MySQL可參考
MySQL5.6生産庫自動化安裝部署
<a href="http://suifu.blog.51cto.com/9167728/1846671" target="_blank">http://suifu.blog.51cto.com/9167728/1846671</a>
1分鐘完成MySQL5.7安裝部署
<a href="http://suifu.blog.51cto.com/9167728/1855415" target="_blank">http://suifu.blog.51cto.com/9167728/1855415</a>
Part2:基本使用方式
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
<code>[root@HE1 ~]</code><code># mysqlreplicate --help</code>
<code>MySQL Utilities mysqlreplicate version 1.5.4 </code>
<code>License </code><code>type</code><code>: GPLv2</code>
<code>Usage: mysqlreplicate --master=root@localhost:3306 --slave=root@localhost:3310 --rpl-user=rpl:</code><code>passwd</code>
<code>mysqlreplicate - establish replication with a master</code>
<code>Options:</code>
<code> </code><code>--version show program's version number and </code><code>exit</code>
<code> </code><code>--help display a help message and </code><code>exit</code>
<code> </code><code>--license display program's license and </code><code>exit</code>
<code> </code><code>--master=MASTER connection information </code><code>for</code> <code>master server </code><code>in</code> <code>the form:</code>
<code> </code><code><user>[:<password>]@<host>[:<port>][:<socket>] or</code>
<code> </code><code><login-path>[:<port>][:<socket>] or <config-</code>
<code> </code><code>path>[<[group]>].</code>
<code> </code><code>--slave=SLAVE connection information </code><code>for</code> <code>slave server </code><code>in</code> <code>the form:</code>
<code> </code><code>--rpl-user=RPL_USER the user and password </code><code>for</code> <code>the replication user</code>
<code> </code><code>requirement, </code><code>in</code> <code>the form: <user>[:<password>] or</code>
<code> </code><code><login-path>. E.g. rpl:</code><code>passwd</code>
<code> </code><code>-p, --pedantic fail </code><code>if</code> <code>storage engines differ among master and slave.</code>
<code> </code><code>--</code><code>test</code><code>-db=TEST_DB database name to use </code><code>in</code> <code>testing replication setup</code>
<code> </code><code>(optional)</code>
<code> </code><code>--master-log-</code><code>file</code><code>=MASTER_LOG_FILE</code>
<code> </code><code>use this master log </code><code>file</code> <code>to initiate the slave.</code>
<code> </code><code>--master-log-pos=MASTER_LOG_POS</code>
<code> </code><code>use this position </code><code>in</code> <code>the master log </code><code>file</code> <code>to initiate</code>
<code> </code><code>the slave.</code>
<code> </code><code>-b, --start-from-beginning</code>
<code> </code><code>start replication from the first event recorded </code><code>in</code> <code>the</code>
<code> </code><code>binary logging of the master. Not valid with --master-</code>
<code> </code><code>log-</code><code>file</code> <code>or --master-log-pos.</code>
<code> </code><code>--ssl-ca=SSL_CA The path to a </code><code>file</code> <code>that contains a list of trusted SSL</code>
<code> </code><code>CAs.</code>
<code> </code><code>--ssl-cert=SSL_CERT The name of the SSL certificate </code><code>file</code> <code>to use </code><code>for</code>
<code> </code><code>establishing a secure connection.</code>
<code> </code><code>--ssl-key=SSL_KEY The name of the SSL key </code><code>file</code> <code>to use </code><code>for</code> <code>establishing a</code>
<code> </code><code>secure connection.</code>
<code> </code><code>--ssl=SSL Specifies </code><code>if</code> <code>the server connection requires use of</code>
<code> </code><code>SSL. If an encrypted connection cannot be established,</code>
<code> </code><code>the connection attempt fails. By default 0 (SSL not</code>
<code> </code><code>required).</code>
<code> </code><code>-</code><code>v</code><code>, --verbose control how much information is displayed. e.g., -</code><code>v</code> <code>=</code>
<code> </code><code>verbose, -vv = </code><code>more</code> <code>verbose, -vvv = debug</code>
<code> </code><code>-q, --quiet turn off all messages </code><code>for</code> <code>quiet execution.</code>
Part3:主庫準備
主庫建立複制使用者
<code>[root@HE3 ~]</code><code># mysql -uroot -p</code>
<code>Enter password: </code>
<code>Welcome to the MySQL monitor. Commands end with ; or \g.</code>
<code>Your MySQL connection </code><code>id</code> <code>is 23329</code>
<code>Server version: 5.7.16-log MySQL Community Server (GPL)</code>
<code>Copyright (c) 2000, 2016, Oracle and</code><code>/or</code> <code>its affiliates. All rights reserved.</code>
<code>Oracle is a registered trademark of Oracle Corporation and</code><code>/or</code> <code>its</code>
<code>affiliates. Other names may be trademarks of their respective</code>
<code>owners.</code>
<code>Type </code><code>'help;'</code> <code>or </code><code>'\h'</code> <code>for</code> <code>help. Type </code><code>'\c'</code> <code>to </code><code>clear</code> <code>the current input statement.</code>
<code>mysql> grant replication client,replication slave on *.* to </code><code>'mysync'</code><code>@</code><code>'%'</code> <code>identified by </code><code>'MANAGER'</code><code>;</code>
<code>Query OK, 0 rows affected, 1 warning (0.01 sec)</code>
<code>mysql> flush privileges;</code>
<code>Query OK, 0 rows affected (0.01 sec)</code>
Part4:一鍵配置
<code>從庫進行配置主從執行如下指令</code>
<code>[root@HE1 ~]</code><code># mysqlreplicate --master=sys_admin:[email protected]:3306 --slave=sys_admin:[email protected]:3306 --rpl-user=mysync:MANAGER -b</code>
<code>WARNING: Using a password on the </code><code>command</code> <code>line interface can be insecure.</code>
<code># master on 192.168.1.250: ... connected.</code>
<code># slave on 192.168.1.248: ... connected.</code>
<code># Checking for binary logging on master...</code>
<code># Setting up replication...</code>
<code># ...done.</code>
Part1:mysqlrplcheck檢查
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
<code>[root@HE1 ~]</code><code># mysqlrplcheck --master=sys_admin:[email protected]:3306 --slave=sys_admin:[email protected]:3306 -s</code>
<code>Test Description Status</code>
<code>---------------------------------------------------------------------------</code>
<code>Checking </code><code>for</code> <code>binary logging on master [pass]</code>
<code>Are there binlog exceptions? [pass]</code>
<code>Replication user exists? [pass]</code>
<code>Checking server_id values [pass]</code>
<code>Checking server_uuid values [pass]</code>
<code>Is slave connected to master? [pass]</code>
<code>Check master information </code><code>file</code> <code>[pass]</code>
<code>Checking InnoDB compatibility [pass]</code>
<code>Checking storage engines compatibility [pass]</code>
<code>Checking lower_case_table_names settings [pass]</code>
<code>Checking slave delay (seconds behind master) [pass]</code>
<code>#</code>
<code># Slave status: </code>
<code> </code><code>Slave_IO_State : Waiting </code><code>for</code> <code>master to send event</code>
<code> </code><code>Master_Host : 192.168.1.250</code>
<code> </code><code>Master_User : mysync</code>
<code> </code><code>Master_Port : 3306</code>
<code> </code><code>Connect_Retry : 60</code>
<code> </code><code>Master_Log_File : mysql-bin.000003</code>
<code> </code><code>Read_Master_Log_Pos : 384741</code>
<code> </code><code>Relay_Log_File : HE1-relay-bin.000004</code>
<code> </code><code>Relay_Log_Pos : 384954</code>
<code> </code><code>Relay_Master_Log_File : mysql-bin.000003</code>
<code> </code><code>Slave_IO_Running : Yes</code>
<code> </code><code>Slave_SQL_Running : Yes</code>
<code> </code><code>Replicate_Do_DB : </code>
<code> </code><code>Replicate_Ignore_DB : </code>
<code> </code><code>Replicate_Do_Table : </code>
<code> </code><code>Replicate_Ignore_Table : </code>
<code> </code><code>Replicate_Wild_Do_Table : </code>
<code> </code><code>Replicate_Wild_Ignore_Table : </code>
<code> </code><code>Last_Errno : 0</code>
<code> </code><code>Last_Error : </code>
<code> </code><code>Skip_Counter : 0</code>
<code> </code><code>Exec_Master_Log_Pos : 384741</code>
<code> </code><code>Relay_Log_Space : 1743112</code>
<code> </code><code>Until_Condition : None</code>
<code> </code><code>Until_Log_File : </code>
<code> </code><code>Until_Log_Pos : 0</code>
<code> </code><code>Master_SSL_Allowed : No</code>
<code> </code><code>Master_SSL_CA_File : </code>
<code> </code><code>Master_SSL_CA_Path : </code>
<code> </code><code>Master_SSL_Cert : </code>
<code> </code><code>Master_SSL_Cipher : </code>
<code> </code><code>Master_SSL_Key : </code>
<code> </code><code>Seconds_Behind_Master : 0</code>
<code> </code><code>Master_SSL_Verify_Server_Cert : No</code>
<code> </code><code>Last_IO_Errno : 0</code>
<code> </code><code>Last_IO_Error : </code>
<code> </code><code>Last_SQL_Errno : 0</code>
<code> </code><code>Last_SQL_Error : </code>
<code> </code><code>Replicate_Ignore_Server_Ids : </code>
<code> </code><code>Master_Server_Id : 1250</code>
<code> </code><code>Master_UUID : 1b1daad8-b501-11e6-aa21-000c29c6361d</code>
<code> </code><code>Master_Info_File : </code><code>/data/mysql/master</code><code>.info</code>
<code> </code><code>SQL_Delay : 0</code>
<code> </code><code>SQL_Remaining_Delay : None</code>
<code> </code><code>Slave_SQL_Running_State : Slave has </code><code>read</code> <code>all relay log; waiting </code><code>for</code> <code>more</code> <code>updates</code>
<code> </code><code>Master_Retry_Count : 86400</code>
<code> </code><code>Master_Bind : </code>
<code> </code><code>Last_IO_Error_Timestamp : </code>
<code> </code><code>Last_SQL_Error_Timestamp : </code>
<code> </code><code>Master_SSL_Crl : </code>
<code> </code><code>Master_SSL_Crlpath : </code>
<code> </code><code>Retrieved_Gtid_Set : </code>
<code> </code><code>Executed_Gtid_Set : </code>
<code> </code><code>Auto_Position : 0</code>
<code> </code><code>Replicate_Rewrite_DB : </code>
<code> </code><code>Channel_Name : </code>
<code> </code><code>Master_TLS_Version : </code>
Part1:mysqldiskusage檢查資料庫空間大小
<code>[root@HE1 ~]</code><code># mysqldiskusage --server=sys_admin:MANAGER@localhost</code>
<code># Source on localhost: ... connected.</code>
<code># Database totals:</code>
<code>+---------------------+--------------+</code>
<code>| db_name | total |</code>
<code>| maxscale_schema | 14,906 |</code>
<code>| mysql | 14,250,013 |</code>
<code>| performance_schema | 818,071 |</code>
<code>| sys | 500,802 |</code>
<code>| wms | 925,929,868 |</code>
<code>Total database disk usage = 941,513,660 bytes or 897.90 MB</code>
<code>#...done.</code>
Part2:mysqlindexcheck檢查備援索引
<code>[root@HE1 ~]</code><code># mysqlindexcheck --server=sys_admin:MANAGER@localhost wms</code>
<code># The following index is a duplicate or redundant for table wms.auth_user:</code>
<code>CREATE UNIQUE INDEX `index_user_name` ON `wms`.`auth_user` (`user_name`) USING BTREE</code>
<code># may be redundant or duplicate of:</code>
<code>CREATE INDEX `user_name` ON `wms`.`auth_user` (`user_name`, `state`) USING BTREE</code>
<code># The following index is a duplicate or redundant for table wms.basic_storeage_sapce:</code>
<code>CREATE INDEX `idx_store_district_space_no` ON `wms`.`basic_storeage_sapce` (`store_id`, `district_id`, `store_space_no`) USING BTREE</code>
<code>CREATE UNIQUE INDEX `idx_store_district_space_no_un` ON `wms`.`basic_storeage_sapce` (`store_id`, `district_id`, `store_space_no`) USING BTREE</code>
——總結——
可以看到利用mysql-utilities工具集中的mysqlreplicate來配置MySQL主從非常簡單，mysqlreplicate也提供了各類參數，本文中的-b是指使複制從主二進制日志中的第一個事件開始。mysqlrplcheck 中的-s是指輸出show slave status\G的内容。由于筆者的水準有限，編寫時間也很倉促，文中難免會出現一些錯誤或者不準确的地方，不妥之處懇請讀者批評指正。
<b> 本文轉自 dbapower 51CTO部落格，原文連結：http://blog.51cto.com/suifu/1878443</b><b>，如需轉載請自行聯系原作者</b>
![資料遷移方法資料遷移原則資料遷移之雙寫方案資料遷移之級聯同步方案[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)