How to use Mysqlreplicate to quickly build MySQL master and slave?

Use one of the 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

[Email protected] ~]# tar xvf mysql-utilities-1.5.4.tar.gz
[Email protected] ~]# CD mysql-utilities-1.5.4
[[email protected] mysql-utilities-1.5.4]# python setup.py build
[[email protected] mysql-utilities-1.5.4]# python setup.py install

How to install MySQL can be consulted

MySQL5.6 Production Library Automation installation Deployment http://www.linuxidc.com/Linux/2016-09/135422.htm

Linux under MySQL5.7 Installation deployment detailed tutorial Http://www.codesec.net/Linux/2016-09/135423.htm

PART2: Basic Use Mode

[Email protected] ~]# mysqlreplicate--help

MySQL Utilities mysqlreplicate Version 1.5.4

License Type:gplv2

usage:mysqlreplicate [email protected]:3306 [Email protected]:3310--rpl-user=rpl:passwd

Mysqlreplicate-establish replication with a master

Options:

--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>]@

--slave=slave connection information for slave server in the form:

<user>[:<password>]@

--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, contains a list of trusted SSL

Cas.

--ssl-cert=ssl_cert the name of the SSL certificate file to

Establishing a secure connection.

--ssl-key=ssl_key the name of the 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 is 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

Master Library Create replication user

[Email protected] ~]# mysql-uroot-p

Enter Password:

Welcome to the MySQL Monitor. Commands End With; or \g.

Your MySQL Connection ID is 23329

Server Version:5.7.16-log MySQL Community Server (GPL)

Copyright (c), Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of the Oracle Corporation and/or its

Affiliates. Other names trademarks of their respective

Owners.

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 from the library master-Slave Execute the following command

[Email protected] ~]# mysqlreplicate--master=sys_admin:[email protected]:3306--slave=sys_admin:[email protected] : 3306--rpl-user=mysync:manager-b

Warning:using a password on the command line interface can is 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

[Email protected] ~]# Mysqlrplcheck--master=sys_admin:[email protected]:3306--slave=sys_admin:[email protected] : 3306-s

Warning:using a password on the command line interface can is 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]
Is 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 have 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 common tools

Part1:mysqldiskusage Checking database space size

[Email protected] ~]# mysqldiskusage--server=sys_admin:[email protected]

Warning:using a password on the command line interface can is 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 Checking for redundant indexes

[Email protected] ~]# mysqlindexcheck--server=sys_admin:[email protected] wms

Warning:using a password on the command line interface can is 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 ' to ' 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

How to use Mysqlreplicate to quickly build MySQL master and slave?