MySQL管理工具MySQL Utilities — mysqlrplms(42)
亿网mysqlrplms 工具允许用户设置多主单从的复制，即从多个主复制。需要提供每个主和从的登录信息。
该工具报告条件是当主和从的存储引擎不一样时。如果主和从的存储引擎不同将产生告警信息。对于Innodb存储引擎而言，必需完全一样，Innodb的类型（built-in 或 InnoDB Plugin）需要一样，同时主次版本号也要一样，并启用状态。
默认情况下，该工具的警告问题在于下面的信息不匹配，存储引擎设置、默认存储引擎和Innodb存储引擎。
为了查看存储引擎和innodb值之间的差异，可以使用-vv选项。
round-robin 调度用于设置主从之间的复制。
mysqlrplms 适用于下面的条件：
- 所有的服务器都启用 GTIDs 。
- 来自不同的主没有事务冲突。如，没有来自对多个主的同一对象更新。
- 复制是异步的。
选项
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 | MySQL Utilities mysqlrplms version 1.5.3 License type : GPLv2 Usage : mysqlrplms — slave = root @ trustauth.cn : 3306 — masters = root @ trustauth.cn : 3310 , root @ trustauth.cn : 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 权限。
主和从的server ID必须非零和唯一的。如果为0或相同产生错误报告。
IP地址和主机名混合使用不推荐。涉及到反向解析的问题。
MySQL客户端工具的路径需要包含在PATH环境变量中，以便使用login-paths验证机制。允许使用my_print_defaults 来从登陆配置文件（.mylogin.cnf）读取login-path值。
对于多主复制，使用临时表有一些限制。为了避免出现问题，建议执行所有的语句的临时表在单个事务中。
实例
在同一台服务器上使用默认设置，不同端口，配置2个主1个从的多主复制，命令如下：
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 | shell > mysqlrplms — slave = root : root @ trustauth.cn : 3306 \ — masters = root : root @ trustauth.cn : 3307 , root : root @ trustauth.cn : 3308 # Starting multi-source replication… # Press CTRL+C to quit. # Switching to master ‘trustauth.cn:3307’. # master on trustauth.cn: … connected. # slave on trustauth.cn: … 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 | + — — — — — — + — — — – + — — — — – + — — — — + — — — — — — + — — — — – + | trustauth.cn | 3307 | MASTER | UP | ON | OK | | trustauth.cn | 3306 | SLAVE | UP | ON | OK | | trustauth.cn | 3308 | MASTER | UP | ON | OK | + — — — — — — + — — — – + — — — — – + — — — — + — — — — — — + — — — — – + # ( . . . ) |
使用 –report-values 选项来报告health, GTID 和 UUID 状态，命令如下：
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 | shell > mysqlrplms — slave = root : root @ trustauth.cn : 3306 \ — masters = root : root @ trustauth.cn : 3307 , root : root @ trustauth.cn : 3308 \ n — report – values = health , gtid , uuid # Starting multi-source replication… # Press CTRL+C to quit. # Switching to master ‘trustauth.cn:3307’. # master on trustauth.cn: … connected. # slave on trustauth.cn: … 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 | + — — — — — — + — — — – + — — — — – + — — — — + — — — — — — + — — — — – + | trustauth.cn | 3307 | MASTER | UP | ON | OK | | trustauth.cn | 3306 | SLAVE | UP | ON | OK | | trustauth.cn | 3308 | MASTER | UP | ON | OK | + — — — — — — + — — — – + — — — — – + — — — — + — — — — — — + — — — — – + # # GTID Status – Transactions executed on the servers: + — — — — — — + — — — – + — — — — – + — — — — — — — — — — — — — — — — — — — — — – + | host | port | role | gtid | + — — — — — — + — — — – + — — — — – + — — — — — — — — — — — — — — — — — — — — — – + | trustauth.cn | 3307 | MASTER | 00a4e027 – a83a – 11e3 – 8bd6 – 28d244017f26 : 1 – 2 | | trustauth.cn | 3306 | SLAVE | 00a4e027 – a83a – 11e3 – 8bd6 – 28d244017f26 : 1 – 2 | | trustauth.cn | 3306 | SLAVE | faf0874f – a839 – 11e3 – 8bd6 – 28d244017f26 : 1 | + — — — — — — + — — — – + — — — — – + — — — — — — — — — — — — — — — — — — — — — – + # # UUID Status: + — — — — — — + — — — – + — — — — – + — — — — — — — — — — — — — — — — — — — – + | host | port | role | uuid | + — — — — — — + — — — – + — — — — – + — — — — — — — — — — — — — — — — — — — – + | trustauth.cn | 3307 | MASTER | 00a4e027 – a83a – 11e3 – 8bd6 – 28d244017f26 | | trustauth.cn | 3306 | SLAVE | faf0874f – a839 – 11e3 – 8bd6 – 28d244017f26 | + — — — — — — + — — — – + — — — — – + — — — — — — — — — — — — — — — — — — — – + # ( . . . ) |
以守护进程方式运行多主复制，命令如下：
| 1 2 3 | shell > mysqlrplms — slave = root : root @ trustauth.cn : 3306 \ — masters = root : root @ trustauth.cn : 3307 , root : root @ trustauth.cn : 3308 \ — log = rplms_daemon . log — pidfile = rplms_daemon . pid — daemon = start |
以守护进程方式重新启动多主复制，命令如下：
| 1 2 3 | shell > mysqlrplms — slave = root : root @ trustauth.cn : 3306 \ — masters = root : root @ trustauth.cn : 3307 , root : root @ trustauth.cn : 3308 \ — log = rplms_daemon . log — pidfile = rplms_daemon . pid — daemon = restart |
停止多主复制，命令如下：
| 1 2 3 | shell > mysqlrplms — slave = root : root @ trustauth.cn : 3306 \ — masters = root : root @ trustauth.cn : 3307 , root : root @ trustauth.cn : 3308 \ — log = rplms_daemon . log — pidfile = rplms_daemon . pid — daemon = stop |
建议
在从的my.cnf文件中配置read_only=1来确保数据不被意外修改，只允许从主读取事件。
权限
在主上需要对mysql数据库具有SELECT 和 INSERT权限，同时还要有REPLICATION SLAVE, REPLICATION CLIENT 和 GRANT OPTION权限。
在从上需要有SUPER 权限。
对于复制用户， –rpl-user 选项使用的，要么自动创建要么指定已经存在的，需要具有 REPLICATION SLAVE 权限。
文章转载来自：trustauth.cn