使用mysqlreplicate快速搭建MySQL主从

简介mysql-utilities工具集是一个集中了多种工具的合集，可以理解为是DBA的工具箱，本文介绍利用其中的mysqlreplicate工具来快速搭建MySQL主从环境。HE1:192.168.1.248 slaveHE3: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生产库自动化安装部署 http://www.linuxidc.com/Linux/2016-09/135422.htmLinux下MySQL5.7安装部署详细教程 http://www.linuxidc.com/Linux/2016-09/135423.htmPart2:基本使用方式[root@HE1 ~]# mysqlreplicate --helpMySQL Utilities mysqlreplicate version 1.5.4License type: GPLv2Usage: mysqlreplicate --master=root@localhost:3306 --slave=root@localhost:3310 --rpl-user=rpl:passwdmysqlreplicate - establish replication with a masterOptions: --version show program"s version number and exit --help display a help message and exit --license display program"s license and exit --master=MASTER connection information for master server in the form: <user>[:<password>]@<host>[:<port>][:<socket>] or <login-path>[:<port>][:<socket>] or <config- path>[<[group]>]. --slave=SLAVE connection information for slave server in the form: <user>[:<password>]@<host>[:<port>][:<socket>] or <login-path>[:<port>][:<socket>] or <config- path>[<[group]>]. --rpl-user=RPL_USER the user and password for the replication user requirement, in the form: <user>[:<password>] or <login-path>. E.g. rpl:passwd -p, --pedantic fail if storage engines differ among master and slave. --test-db=TEST_DB database name to use in testing replication setup （optional） --master-log-file=MASTER_LOG_FILE use this master log file to initiate the slave. --master-log-pos=MASTER_LOG_POS use this position in the master log file to initiate the slave. -b, --start-from-beginning start replication from the first event recorded in the binary logging of the master. Not valid with --master- log-file or --master-log-pos. --ssl-ca=SSL_CA The path to a file that contains a list of trusted SSL CAs. --ssl-cert=SSL_CERT The name of the SSL certificate file to use for establishing a secure connection. --ssl-key=SSL_KEY The name of the SSL key file to use for establishing a secure connection. --ssl=SSL Specifies if the server connection requires use of SSL. If an encrypted connection cannot be established, the connection attempt fails. By default 0 （SSL not required）. -v, --verbose control how much information is displayed. e.g., -v = verbose, -vv = more verbose, -vvv = debug -q, --quiet turn off all messages for quiet execution.Part3:主库准备主库创建复制用户 [root@HE3 ~]# mysql -uroot -pEnter password:Welcome to the MySQL monitor. Commands end with ; or g.Your MySQL connection id is 23329Server version: 5.7.16-log MySQL Community Server （GPL）Copyright （c） 2000, 2016, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type "help;" or "h" for help. Type "c" to clear the current input statement.mysql> grant replication client,replication slave on *.* to "mysync"@"%" identified by "MANAGER";Query OK, 0 rows affected, 1 warning （0.01 sec）mysql> flush privileges;Query OK, 0 rows affected （0.01 sec）Part4:一键配置从库进行配置主从执行如下命令[root@HE1 ~]# mysqlreplicate --master=sys_admin:MANAGER@192.168.1.250:3306 --slave=sys_admin:MANAGER@192.168.1.248:3306 --rpl-user=mysync:MANAGER -bWARNING: Using a password on the command line interface can be insecure.# master on 192.168.1.250: ... connected.# slave on 192.168.1.248: ... connected.# Checking for binary logging on master...# Setting up replication...# ...done.检查Part1:mysqlrplcheck检查[root@HE1 ~]# mysqlrplcheck --master=sys_admin:MANAGER@192.168.1.250:3306 --slave=sys_admin:MANAGER@192.168.1.248:3306 -sWARNING: Using a password on the command line interface can be insecure.# master on 192.168.1.250: ... connected.# slave on 192.168.1.248: ... 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 : 192.168.1.250 Master_User : mysync Master_Port : 3306 Connect_Retry : 60 Master_Log_File : mysql-bin.000003 Read_Master_Log_Pos : 384741 Relay_Log_File : HE1-relay-bin.000004 Relay_Log_Pos : 384954 Relay_Master_Log_File : mysql-bin.000003 Slave_IO_Running : Yes Slave_SQL_Running : Yes Replicate_Do_DB : Replicate_Ignore_DB : Replicate_Do_Table : Replicate_Ignore_Table : Replicate_Wild_Do_Table : Replicate_Wild_Ignore_Table : Last_Errno : 0 Last_Error : Skip_Counter : 0 Exec_Master_Log_Pos : 384741 Relay_Log_Space : 1743112 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 : 0 Master_SSL_Verify_Server_Cert : No Last_IO_Errno : 0 Last_IO_Error : Last_SQL_Errno : 0 Last_SQL_Error : Replicate_Ignore_Server_Ids : Master_Server_Id : 1250 Master_UUID : 1b1daad8-b501-11e6-aa21-000c29c6361d Master_Info_File : /data/mysql/master.info SQL_Delay : 0 SQL_Remaining_Delay : None Slave_SQL_Running_State : Slave has read all relay log; waiting for more updates Master_Retry_Count : 86400 Master_Bind : Last_IO_Error_Timestamp : Last_SQL_Error_Timestamp : Master_SSL_Crl : Master_SSL_Crlpath : Retrieved_Gtid_Set : Executed_Gtid_Set : Auto_Position : 0 Replicate_Rewrite_DB : Channel_Name : Master_TLS_Version :# ...done.其他常用工具Part1:mysqldiskusage检查数据库空间大小[root@HE1 ~]# mysqldiskusage --server=sys_admin:MANAGER@localhostWARNING: Using a password on the command line interface can be insecure.# Source on localhost: ... connected.# Database totals:+---------------------+--------------+| db_name | total |+---------------------+--------------+| maxscale_schema | 14,906 || mysql | 14,250,013 || performance_schema | 818,071 || sys | 500,802 || wms | 925,929,868 |+---------------------+--------------+Total database disk usage = 941,513,660 bytes or 897.90 MB#...done.Part2:mysqlindexcheck检查冗余索引[root@HE1 ~]# mysqlindexcheck --server=sys_admin:MANAGER@localhost wmsWARNING: Using a password on the command line interface can be insecure.# Source on localhost: ... connected.# The following index is a duplicate or redundant for table wms.auth_user:#CREATE UNIQUE INDEX `index_user_name` ON `wms`.`auth_user` （`user_name`） USING BTREE# may be redundant or duplicate of:CREATE INDEX `user_name` ON `wms`.`auth_user` （`user_name`, `state`） USING BTREE# The following index is a duplicate or redundant for table wms.basic_storeage_sapce:#CREATE INDEX `idx_store_district_space_no` ON `wms`.`basic_storeage_sapce` （`store_id`, `district_id`, `store_space_no`） USING BTREE# may be redundant or duplicate of:CREATE UNIQUE INDEX `idx_store_district_space_no_un` ON `wms`.`basic_storeage_sapce` （`store_id`, `district_id`, `store_space_no`） USING BTREE——总结——可以看到利用mysql-utilities工具集中的mysqlreplicate来配置MySQL主从非常简单，mysqlreplicate也提供了各类参数，本文中的-b是指使复制从主二进制日志中的第一个事件开始。mysqlrplcheck 中的-s是指输出show slave statusG的内容。由于笔者的水平有限，编写时间也很仓促，文中难免会出现一些错误或者不准确的地方，不妥之处恳请读者批评指正。本文永久更新链接地址