欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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

程序员文章站 2023-12-02 09:38:28
简介 mysql-utilities工具集是一个集中了多种工具的合集,可以理解为是dba的工具箱,本文介绍利用其中的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的内容。由于笔者的水平有限,编写时间也很仓促,文中难免会出现一些错误或者不准确的地方,不妥之处恳请读者批评指正。

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