如何快速使用mysqlreplicate搭建MySQL主从
简介
mysql-utilities工具集是一个集中了多种工具的合集，可以理解为是DBA的工具箱，本文介绍利用其中的mysqlreplicate工具来快速搭建MySQL主从环境。
HE1:192.168.1.248slave
HE3:192.168.1.250master
实战
Part1:安装mysql-utilities
[root@HE1~]#tarxvfmysql-utilities-1.5.4.tar.gz [root@HE1~]#cdmysql-utilities-1.5.4 [root@HE1mysql-utilities-1.5.4]#pythonsetup.pybuild [root@HE1mysql-utilities-1.5.4]#pythonsetup.pyinstall
Part2:基本使用方式
[root@HE1~]#mysqlreplicate--help MySQLUtilitiesmysqlreplicateversion1.5.4 Licensetype:GPLv2 Usage:mysqlreplicate--master=root@localhost:3306--slave=root@localhost:3310--rpl-user=rpl:passwd mysqlreplicate-establishreplicationwithamaster Options: --versionshowprogram'sversionnumberandexit --helpdisplayahelpmessageandexit --licensedisplayprogram'slicenseandexit --master=MASTERconnectioninformationformasterserverintheform:[: ]@ [: ][: ]or [: ][: ]or [<[group]>]. --slave=SLAVEconnectioninformationforslaveserverintheform: [: ]@ [: ][: ]or [: ][: ]or [<[group]>]. --rpl-user=RPL_USERtheuserandpasswordforthereplicationuser requirement,intheform: [: ]or .E.g.rpl:passwd -p,--pedanticfailifstorageenginesdifferamongmasterandslave. --test-db=TEST_DBdatabasenametouseintestingreplicationsetup (optional) --master-log-file=MASTER_LOG_FILE usethismasterlogfiletoinitiatetheslave. --master-log-pos=MASTER_LOG_POS usethispositioninthemasterlogfiletoinitiate theslave. -b,--start-from-beginning startreplicationfromthefirsteventrecordedinthe binaryloggingofthemaster.Notvalidwith--master- log-fileor--master-log-pos. --ssl-ca=SSL_CAThepathtoafilethatcontainsalistoftrustedSSL CAs. --ssl-cert=SSL_CERTThenameoftheSSLcertificatefiletousefor establishingasecureconnection. --ssl-key=SSL_KEYThenameoftheSSLkeyfiletouseforestablishinga secureconnection. --ssl=SSLSpecifiesiftheserverconnectionrequiresuseof SSL.Ifanencryptedconnectioncannotbeestablished, theconnectionattemptfails.Bydefault0(SSLnot required). -v,--verbosecontrolhowmuchinformationisdisplayed.e.g.,-v= verbose,-vv=moreverbose,-vvv=debug -q,--quietturnoffallmessagesforquietexecution.
Part3:主库准备
主库创建复制用户
[root@HE3~]#mysql-uroot-p Enterpassword: WelcometotheMySQLmonitor.Commandsendwith;or\g. YourMySQLconnectionidis23329 Serverversion:5.7.16-logMySQLCommunityServer(GPL) Copyright(c)2000,2016,Oracleand/oritsaffiliates.Allrightsreserved. OracleisaregisteredtrademarkofOracleCorporationand/orits affiliates.Othernamesmaybetrademarksoftheirrespective owners. Type'help;'or'\h'forhelp.Type'\c'toclearthecurrentinputstatement. mysql>grantreplicationclient,replicationslaveon*.*to'mysync'@'%'identifiedby'MANAGER'; QueryOK,0rowsaffected,1warning(0.01sec) mysql>flushprivileges; QueryOK,0rowsaffected(0.01sec)
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-b WARNING:Usingapasswordonthecommandlineinterfacecanbeinsecure. #masteron192.168.1.250:...connected. #slaveon192.168.1.248:...connected. #Checkingforbinaryloggingonmaster... #Settingupreplication... #...done.
检查
Part1:mysqlrplcheck检查
[root@HE1~]#mysqlrplcheck--master=sys_admin:MANAGER@192.168.1.250:3306--slave=sys_admin:MANAGER@192.168.1.248:3306-s WARNING:Usingapasswordonthecommandlineinterfacecanbeinsecure. #masteron192.168.1.250:...connected. #slaveon192.168.1.248:...connected. TestDescriptionStatus --------------------------------------------------------------------------- Checkingforbinaryloggingonmaster[pass] Aretherebinlogexceptions?[pass] Replicationuserexists?[pass] Checkingserver_idvalues[pass] Checkingserver_uuidvalues[pass] Isslaveconnectedtomaster?[pass] Checkmasterinformationfile[pass] CheckingInnoDBcompatibility[pass] Checkingstorageenginescompatibility[pass] Checkinglower_case_table_namessettings[pass] Checkingslavedelay(secondsbehindmaster)[pass] # #Slavestatus: # Slave_IO_State:Waitingformastertosendevent 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:Slavehasreadallrelaylog;waitingformoreupdates 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@localhost WARNING:Usingapasswordonthecommandlineinterfacecanbeinsecure. #Sourceonlocalhost:...connected. #Databasetotals: +---------------------+--------------+ |db_name|total| +---------------------+--------------+ |maxscale_schema|14,906| |mysql|14,250,013| |performance_schema|818,071| |sys|500,802| |wms|925,929,868| +---------------------+--------------+ Totaldatabasediskusage=941,513,660bytesor897.90MB #...done.
Part2:mysqlindexcheck检查冗余索引
[root@HE1~]#mysqlindexcheck--server=sys_admin:MANAGER@localhostwms WARNING:Usingapasswordonthecommandlineinterfacecanbeinsecure. #Sourceonlocalhost:...connected. #Thefollowingindexisaduplicateorredundantfortablewms.auth_user: # CREATEUNIQUEINDEX`index_user_name`ON`wms`.`auth_user`(`user_name`)USINGBTREE #mayberedundantorduplicateof: CREATEINDEX`user_name`ON`wms`.`auth_user`(`user_name`,`state`)USINGBTREE #Thefollowingindexisaduplicateorredundantfortablewms.basic_storeage_sapce: # CREATEINDEX`idx_store_district_space_no`ON`wms`.`basic_storeage_sapce`(`store_id`,`district_id`,`store_space_no`)USINGBTREE #mayberedundantorduplicateof: CREATEUNIQUEINDEX`idx_store_district_space_no_un`ON`wms`.`basic_storeage_sapce`(`store_id`,`district_id`,`store_space_no`)USINGBTREE
可以看到利用mysql-utilities工具集中的mysqlreplicate来配置MySQL主从非常简单，mysqlreplicate也提供了各类参数，本文中的-b是指使复制从主二进制日志中的第一个事件开始。mysqlrplcheck中的-s是指输出showslavestatus\G的内容。由于笔者的水平有限，编写时间也很仓促，文中难免会出现一些错误或者不准确的地方，不妥之处恳请读者批评指正。
以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持毛票票。