MySQL管理工具MySQL Utilities — mysqlrplms(42)

mysqlrplms 工具允许用户设置多主单从的复制，即从多个主复制。需要提供每个主和从的登录信息。

该工具报告条件是当主和从的存储引擎不一样时。如果主和从的存储引擎不同将产生告警信息。对于Innodb存储引擎而言，必需完全一样，Innodb的类型（built-in 或 InnoDB Plugin）需要一样，同时主次版本号也要一样，并启用状态。文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-utilities-mysqlrplms/

默认情况下，该工具的警告问题在于下面的信息不匹配，存储引擎设置、默认存储引擎和Innodb存储引擎。文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-utilities-mysqlrplms/

为了查看存储引擎和innodb值之间的差异，可以使用-vv选项。文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-utilities-mysqlrplms/

round-robin 调度用于设置主从之间的复制。文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-utilities-mysqlrplms/

mysqlrplms 适用于下面的条件：文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-utilities-mysqlrplms/

• 所有的服务器都启用 GTIDs 。
• 来自不同的主没有事务冲突。如，没有来自对多个主的同一对象更新。
• 复制是异步的。

选项

MySQL Utilities mysqlrplms version 1.5.3 
License type: GPLv2
Usage: mysqlrplms --slave=root@localhost:3306 --masters=root@localhost:3310,root@localhost:3311 --rpl-user=rpl:passwd

mysqlrplms - establish multi-source replication

Options:
  --version             show program's version number and exit
  --help                display a help message and exit
  --license             display program's license and exit
  -i INTERVAL, --interval=INTERVAL
                        interval in seconds for reporting health. Default = 15
                        seconds. Lowest value is 5 seconds.
  --switchover-interval=SWITCHOVER_INTERVAL
                        interval in seconds for switching masters. Default =
                        60 seconds. Lowest value is 30 seconds.
  --slave=SLAVE         connection information for slave server in the form:
                        <user>[:<password>]@<host>[:<port>][:<socket>] or
                        <login-path>[:<port>][:<socket>] or <config-
                        path>[<[group]>]
  --masters=MASTERS     connection information for master servers in the form:
                        <user>[:<password>]@<host>[:<port>][:<socket>] or
                        <login-path>[:<port>][:<socket>] or <config-
                        path>[<[group]>]. List multiple master in comma-
                        separated list.
  --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
  -b, --start-from-beginning
                        start replication from the first event recorded in the
                        binary logging of the masters.
  --report-values=REPORT_VALUES
                        report values used in multi-source replication. It can
                        be health, gtid or uuid. Multiple values can be used
                        separated by commas. The default is health.
  -f FORMAT, --format=FORMAT
                        display the output in either grid (default), tab, csv,
                        or vertical format
  --daemon=DAEMON       run on daemon mode. It can be start, stop, restart or
                        nodetach.
  --pidfile=PIDFILE     pidfile for running mysqlrplms as a daemon.
  --log=LOG_FILE        specify a log file to use for logging messages
  --log-age=LOG_AGE     specify maximum age of log entries in days. Entries
                        older than this will be purged on startup. Default = 7
                        days.
  --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.
  -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.

Introduction
------------
The mysqlrplms utility is used to setup round robin multi-source replcation.
This technique can be a solution for aggregating streams of data from multiple
masters for a single slave.

The mysqlrplms utility follows these assumptions:

  - All servers have GTIDs enabled.
  - There are no conflicts between transactions from different sources/masters.
    For example, there are no updates to the same object from multiple masters.
  - Replication is asynchronous.

A round-robin scheduling is used to setup replication among the masters and
slave.

The utility can be run as a daemon on POSIX systems.

  # Basic multi-source replication setup.

  $ mysqlrplms --slave=root:pass@host1:3306 \
               --masters=root:pass@host2:3306,root:pass@host3:3306

  # Multi-source replication setup using a different report values.

  $ mysqlrplms --slave=root:pass@host1:3306 \
               --masters=root:pass@host2:3306,root:pass@host3:3306 \
               --report-values=health,gtid,uuid

  # Start multi-source replication running as a daemon. (POSIX only)

  $ mysqlrplms --slave=root:pass@host1:3306 \
               --masters=root:pass@host2:3306,root:pass@host3:3306 \
               --log=rplms_daemon.log --pidfile=rplms_daemon.pid \
               --daemon=start

  # Restart a multi-source replication running as a daemon.

  $ mysqlrplms --slave=root:pass@host1:3306 \
               --masters=root:pass@host2:3306,root:pass@host3:3306 \
               --log=rplms_daemon.log --pidfile=rplms_daemon.pid \
               --daemon=restart

  # Stop a multi-source replication running as a daemon.

  $ mysqlrplms --slave=root:pass@host1:3306 \
               --masters=root:pass@host2:3306,root:pass@host3:3306 \
               --log=rplms_daemon.log --pidfile=rplms_daemon.pid \
               --daemon=stop


Helpful Hints
-------------
  - The default report value is 'health'.
    This value can be changed with the --report-values option. It can be
    'health', 'gtid' or 'uuid'. Multiple values can be used separated by
    commas.

  - The default output for reporting health is 'grid'.
    This value can be changed with the --format option. It can be 'grid',
    'tab', 'csv' or 'vertical' format.

  - The default interval for reporting health is 15 seconds.
    This value can be changed with the --interval option.

  - The default interval for switching masters is 60 seconds.
    This value can be changed with the --switchover-interval option.

注意事项

登录主服务器的用户必须具有对访问数据库的授权权限和创建账号的权限。也就是WITH GRANT OPTION 权限。文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-utilities-mysqlrplms/

主和从的server ID必须非零和唯一的。如果为0或相同产生错误报告。文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-utilities-mysqlrplms/

IP地址和主机名混合使用不推荐。涉及到反向解析的问题。文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-utilities-mysqlrplms/

MySQL客户端工具的路径需要包含在PATH环境变量中，以便使用login-paths验证机制。允许使用my_print_defaults 来从登陆配置文件（.mylogin.cnf）读取login-path值。文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-utilities-mysqlrplms/

对于多主复制，使用临时表有一些限制。为了避免出现问题，建议执行所有的语句的临时表在单个事务中。文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-utilities-mysqlrplms/

实例

在同一台服务器上使用默认设置，不同端口，配置2个主1个从的多主复制，命令如下：文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-utilities-mysqlrplms/

shell> mysqlrplms --slave=root:root@localhost:3306 \
       --masters=root:root@localhost:3307,root:root@localhost:3308
# Starting multi-source replication...
# Press CTRL+C to quit.
# Switching to master 'localhost:3307'.
# master on localhost: ... connected.
# slave on localhost: ... connected.
#
# Current Master Information:
+-------------------+-----------+---------------+-------------------+
| Binary Log File   | Position  | Binlog_Do_DB  | Binlog_Ignore_DB  |
+-------------------+-----------+---------------+-------------------+
| clone-bin.000001  | 594       | N/A           | N/A               |
+-------------------+-----------+---------------+-------------------+
# GTID Executed Set: 00a4e027-a83a-11e3-8bd6-28d244017f26:1-2
#
# Health Status:
+------------+-------+---------+--------+------------+---------+
| host       | port  | role    | state  | gtid_mode  | health  |
+------------+-------+---------+--------+------------+---------+
| localhost  | 3307  | MASTER  | UP     | ON         | OK      |
| localhost  | 3306  | SLAVE   | UP     | ON         | OK      |
| localhost  | 3308  | MASTER  | UP     | ON         | OK      |
+------------+-------+---------+--------+------------+---------+
#
(...)

使用 --report-values 选项来报告health, GTID 和 UUID 状态，命令如下：文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-utilities-mysqlrplms/

shell> mysqlrplms --slave=root:root@localhost:3306 \
       --masters=root:root@localhost:3307,root:root@localhost:3308\n
       --report-values=health,gtid,uuid
# Starting multi-source replication...
# Press CTRL+C to quit.
# Switching to master 'localhost:3307'.
# master on localhost: ... connected.
# slave on localhost: ... connected.
#
# Current Master Information:
+-------------------+-----------+---------------+-------------------+
| Binary Log File   | Position  | Binlog_Do_DB  | Binlog_Ignore_DB  |
+-------------------+-----------+---------------+-------------------+
| clone-bin.000001  | 594       | N/A           | N/A               |
+-------------------+-----------+---------------+-------------------+
# GTID Executed Set: 00a4e027-a83a-11e3-8bd6-28d244017f26:1-2
#
# Health Status:
+------------+-------+---------+--------+------------+---------+
| host       | port  | role    | state  | gtid_mode  | health  |
+------------+-------+---------+--------+------------+---------+
| localhost  | 3307  | MASTER  | UP     | ON         | OK      |
| localhost  | 3306  | SLAVE   | UP     | ON         | OK      |
| localhost  | 3308  | MASTER  | UP     | ON         | OK      |
+------------+-------+---------+--------+------------+---------+
#
# GTID Status - Transactions executed on the servers:
+------------+-------+---------+-------------------------------------------+
| host       | port  | role    | gtid                                      |
+------------+-------+---------+-------------------------------------------+
| localhost  | 3307  | MASTER  | 00a4e027-a83a-11e3-8bd6-28d244017f26:1-2  |
| localhost  | 3306  | SLAVE   | 00a4e027-a83a-11e3-8bd6-28d244017f26:1-2  |
| localhost  | 3306  | SLAVE   | faf0874f-a839-11e3-8bd6-28d244017f26:1    |
+------------+-------+---------+-------------------------------------------+
#
# UUID Status:
+------------+-------+---------+---------------------------------------+
| host       | port  | role    | uuid                                  |
+------------+-------+---------+---------------------------------------+
| localhost  | 3307  | MASTER  | 00a4e027-a83a-11e3-8bd6-28d244017f26  |
| localhost  | 3306  | SLAVE   | faf0874f-a839-11e3-8bd6-28d244017f26  |
+------------+-------+---------+---------------------------------------+
#
(...)

以守护进程方式运行多主复制，命令如下：文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-utilities-mysqlrplms/

shell> mysqlrplms --slave=root:root@localhost:3306 \
       --masters=root:root@localhost:3307,root:root@localhost:3308 \
       --log=rplms_daemon.log --pidfile=rplms_daemon.pid --daemon=start

以守护进程方式重新启动多主复制，命令如下：文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-utilities-mysqlrplms/

shell> mysqlrplms --slave=root:root@localhost:3306 \
       --masters=root:root@localhost:3307,root:root@localhost:3308 \
       --log=rplms_daemon.log --pidfile=rplms_daemon.pid --daemon=restart

停止多主复制，命令如下：文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-utilities-mysqlrplms/

shell> mysqlrplms --slave=root:root@localhost:3306 \
       --masters=root:root@localhost:3307,root:root@localhost:3308 \
       --log=rplms_daemon.log --pidfile=rplms_daemon.pid --daemon=stop

建议

在从的my.cnf文件中配置read_only=1来确保数据不被意外修改，只允许从主读取事件。文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-utilities-mysqlrplms/

权限

在主上需要对mysql数据库具有SELECT 和 INSERT权限，同时还要有REPLICATION SLAVE, REPLICATION CLIENT 和 GRANT OPTION权限。文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-utilities-mysqlrplms/

在从上需要有SUPER 权限。文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-utilities-mysqlrplms/

对于复制用户， --rpl-user 选项使用的，要么自动创建要么指定已经存在的，需要具有 REPLICATION SLAVE 权限。文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-utilities-mysqlrplms/ 文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-utilities-mysqlrplms/