Shulou(Shulou.com)06/01 Report--

Brief introduction

The mysql-utilities toolset is a collection of many tools, which can be understood as the toolbox of DBA. This paper introduces the use of mysqlreplicate tools to quickly build a MySQL master-slave environment.

HE1:192.168.1.248 slave

HE3:192.168.1.250 master

Actual combat

Part1: installing 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

Part2: basic usage

[root@HE1] # mysqlreplicate-- helpMySQL Utilities mysqlreplicate version 1.5.4 License type: GPLv2Usage: mysqlreplicate-- master=root@localhost:3306-- slave=root@localhost:3310-- rpl-user=rpl:passwd mysqlreplicate-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: [:] @ [:] or [:] [:] or []. -- slave=SLAVE connection information for slave server in the form: [:] @ [:] or [:] [:] or []. Rpl-user=RPL_USER the user and password for the replication user requirement, in the form: [:] or. 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: main library preparation

The main library creates replication users

[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: one-click configuration

Configure the master and slave from the library and execute the following command [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.

Check

Part1:mysqlrplcheck check

[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.

Other commonly used tools

Part1:mysqldiskusage checks the size of database space

[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 | 14906 | mysql | 14250013 | | performance_schema | 818071 | sys | 500802 | | wms | 925929868 | +-- +-+ Total database disk usage = 941513660 bytes or 897.90 MB#...done.

Part2:mysqlindexcheck checks redundant indexes

[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

-- Summary.

You can see that it is very easy to configure MySQL master and slave with mysqlreplicate in the mysql-utilities toolset, and mysqlreplicate also provides various parameters, and-b in this article instructs replication to start with the first event in the master binary log. The-s in mysqlrplcheck refers to the contents of the output show slave status\ G. As the author's level is limited and the writing time is very short, it is inevitable that there will be some errors or inaccuracies in the article. I urge readers to criticize and correct them.

The above is the whole content of this article, I hope it will be helpful to your study, and I also hope that you will support it.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.