如何快速使用mysqlreplicate搭建MySQL主从

简介

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

part2:基本使用方式

[root@he1 ~]# mysqlreplicate --help
mysql utilities mysqlreplicate version 1.5.4 
license type: gplv2
usage: mysqlreplicate --master=root@localhost:3306 --slave=root@localhost: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>]@<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 -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) 2000, 2016, oracle and/or its affiliates. all rights reserved.
oracle is a registered trademark of oracle corporation and/or its
affiliates. other names may be 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:一键配置

从库进行配置主从执行如下命令
[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: 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 -s
warning: 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@localhost
warning: 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 wms
warning: 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 status\g的内容。由于笔者的水平有限，编写时间也很仓促，文中难免会出现一些错误或者不准确的地方，不妥之处恳请读者批评指正。

以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持。