MySQL两主（多主）多从架构配置

yxrj 发表于 2020-3-26 10:22:04

<h3 id="%E4%B8%80%E3%80%81%E8%A7%92%E8%89%B2%E5%88%92%E5%88%86" name="%E4%B8%80%E3%80%81%E8%A7%92%E8%89%B2%E5%88%92%E5%88%86" style="margin-top: 16px; margin-bottom: 8px; font-size: 14px; box-sizing: border-box; list-style: inherit; color: rgb(0, 0, 0); line-height: 24px; font-family: "pingfang SC", "helvetica neue", arial, "hiragino sans gb", "microsoft yahei ui", "microsoft yahei", simsun, sans-serif; white-space: pre-wrap;">一、角色划分</h3><p style="margin-bottom: 8px; box-sizing: border-box; list-style: inherit; min-height: 24px; color: rgb(51, 51, 51); font-family: "pingfang SC", "helvetica neue", arial, "hiragino sans gb", "microsoft yahei ui", "microsoft yahei", simsun, sans-serif; white-space: pre-wrap;">1、MySQL数据库规划<blockquote style="box-sizing: border-box; list-style: inherit; margin-bottom: 24px; padding-right: 15px; padding-left: 15px; color: rgb(119, 119, 119); border-left: 4px solid rgb(221, 221, 221); font-family: "pingfang SC", "helvetica neue", arial, "hiragino sans gb", "microsoft yahei ui", "microsoft yahei", simsun, sans-serif; white-space: pre-wrap;">我现在的环境是：zhdy04和zhdy05已经做好了主主架构配置，现在需要的是把两台或者多台从服务器与主一一同步。</blockquote><p style="margin-bottom: 8px; box-sizing: border-box; list-style: inherit; min-height: 24px; color: rgb(51, 51, 51); font-family: "pingfang SC", "helvetica neue", arial, "hiragino sans gb", "microsoft yahei ui", "microsoft yahei", simsun, sans-serif; white-space: pre-wrap;">如果搭建主主环境，参照此链接！<div class="table-wrapper" style="margin-top: 12px; margin-bottom: 12px; box-sizing: border-box; list-style: inherit; outline: none; position: relative; overflow-x: auto; color: rgb(51, 51, 51); font-family: "pingfang SC", "helvetica neue", arial, "hiragino sans gb", "microsoft yahei ui", "microsoft yahei", simsun, sans-serif; white-space: pre-wrap;"><table style="border-spacing: 0px; box-sizing: border-box; list-style: inherit; width: 734px;"><thead style="box-sizing: border-box; list-style: inherit;"><tr style="box-sizing: border-box; list-style: inherit;"><th style="padding: 12px 16px; box-sizing: border-box; list-style: inherit; border: 1px solid rgb(225, 230, 240); background-color: rgb(245, 247, 250); color: rgb(103, 116, 137); text-align: left; word-break: keep-all;"><div class="table-header" style="box-sizing: border-box; list-style: inherit;">主机名</div></th><th style="padding: 12px 16px; box-sizing: border-box; list-style: inherit; border: 1px solid rgb(225, 230, 240); background-color: rgb(245, 247, 250); color: rgb(103, 116, 137); text-align: left; word-break: keep-all;"><div class="table-header" style="box-sizing: border-box; list-style: inherit;">IP 地址</div></th><th style="padding: 12px 16px; box-sizing: border-box; list-style: inherit; border: 1px solid rgb(225, 230, 240); background-color: rgb(245, 247, 250); color: rgb(103, 116, 137); text-align: left; word-break: keep-all;"><div class="table-header" style="box-sizing: border-box; list-style: inherit;">角色</div></th><th style="padding: 12px 16px; box-sizing: border-box; list-style: inherit; border: 1px solid rgb(225, 230, 240); background-color: rgb(245, 247, 250); color: rgb(103, 116, 137); text-align: left; word-break: keep-all;"><div class="table-header" style="box-sizing: border-box; list-style: inherit;">Mysql_server_id</div></th></tr></thead><tbody style="box-sizing: border-box; list-style: inherit;"><tr style="box-sizing: border-box; list-style: inherit;"><td style="padding: 12px 16px; box-sizing: border-box; list-style: inherit; border-color: rgb(225, 230, 240);"><div class="table-cell" style="box-sizing: border-box; list-style: inherit;">zhdy04</div></td><td style="padding: 12px 16px; box-sizing: border-box; list-style: inherit; border-color: rgb(225, 230, 240);"><div class="table-cell" style="box-sizing: border-box; list-style: inherit;">192.168.230.145</div></td><td style="padding: 12px 16px; box-sizing: border-box; list-style: inherit; border-color: rgb(225, 230, 240);"><div class="table-cell" style="box-sizing: border-box; list-style: inherit;">masterA</div></td><td style="padding: 12px 16px; box-sizing: border-box; list-style: inherit; border-color: rgb(225, 230, 240);"><div class="table-cell" style="box-sizing: border-box; list-style: inherit;">145</div></td></tr><tr style="box-sizing: border-box; list-style: inherit;"><td style="padding: 12px 16px; box-sizing: border-box; list-style: inherit; border-color: rgb(225, 230, 240);"><div class="table-cell" style="box-sizing: border-box; list-style: inherit;">zhdy05</div></td><td style="padding: 12px 16px; box-sizing: border-box; list-style: inherit; border-color: rgb(225, 230, 240);"><div class="table-cell" style="box-sizing: border-box; list-style: inherit;">192.168.230.146</div></td><td style="padding: 12px 16px; box-sizing: border-box; list-style: inherit; border-color: rgb(225, 230, 240);"><div class="table-cell" style="box-sizing: border-box; list-style: inherit;">masterB</div></td><td style="padding: 12px 16px; box-sizing: border-box; list-style: inherit; border-color: rgb(225, 230, 240);"><div class="table-cell" style="box-sizing: border-box; list-style: inherit;">146</div></td></tr><tr style="box-sizing: border-box; list-style: inherit;"><td style="padding: 12px 16px; box-sizing: border-box; list-style: inherit; border-color: rgb(225, 230, 240);"><div class="table-cell" style="box-sizing: border-box; list-style: inherit;">zhdy06</div></td><td style="padding: 12px 16px; box-sizing: border-box; list-style: inherit; border-color: rgb(225, 230, 240);"><div class="table-cell" style="box-sizing: border-box; list-style: inherit;">192.168.230.147</div></td><td style="padding: 12px 16px; box-sizing: border-box; list-style: inherit; border-color: rgb(225, 230, 240);"><div class="table-cell" style="box-sizing: border-box; list-style: inherit;">slaveA</div></td><td style="padding: 12px 16px; box-sizing: border-box; list-style: inherit; border-color: rgb(225, 230, 240);"><div class="table-cell" style="box-sizing: border-box; list-style: inherit;">147</div></td></tr><tr style="box-sizing: border-box; list-style: inherit;"><td style="padding: 12px 16px; box-sizing: border-box; list-style: inherit; border-color: rgb(225, 230, 240);"><div class="table-cell" style="box-sizing: border-box; list-style: inherit;">zhdy07</div></td><td style="padding: 12px 16px; box-sizing: border-box; list-style: inherit; border-color: rgb(225, 230, 240);"><div class="table-cell" style="box-sizing: border-box; list-style: inherit;">192.168.230.148</div></td><td style="padding: 12px 16px; box-sizing: border-box; list-style: inherit; border-color: rgb(225, 230, 240);"><div class="table-cell" style="box-sizing: border-box; list-style: inherit;">slaveB</div></td><td style="padding: 12px 16px; box-sizing: border-box; list-style: inherit; border-color: rgb(225, 230, 240);"><div class="table-cell" style="box-sizing: border-box; list-style: inherit;">148</div></td></tr></tbody></table></div><figure style="box-sizing: border-box; list-style: inherit; margin-top: 16px; margin-bottom: 16px; color: rgb(51, 51, 51); font-family: "pingfang SC", "helvetica neue", arial, "hiragino sans gb", "microsoft yahei ui", "microsoft yahei", simsun, sans-serif; white-space: pre-wrap;"><div class="image-block" style="box-sizing: border-box; list-style: inherit;"></div></figure><h3 id="%E4%BA%8C%E3%80%81MySQL%E6%95%B0%E6%8D%AE%E5%90%8C%E6%AD%A5%E8%AE%BE%E5%AE%9A" name="%E4%BA%8C%E3%80%81MySQL%E6%95%B0%E6%8D%AE%E5%90%8C%E6%AD%A5%E8%AE%BE%E5%AE%9A" style="margin-top: 16px; margin-bottom: 8px; font-size: 14px; box-sizing: border-box; list-style: inherit; color: rgb(0, 0, 0); line-height: 24px; font-family: "pingfang SC", "helvetica neue", arial, "hiragino sans gb", "microsoft yahei ui", "microsoft yahei", simsun, sans-serif; white-space: pre-wrap;">二、MySQL数据同步设定</h3><p style="margin-bottom: 8px; box-sizing: border-box; list-style: inherit; min-height: 24px; color: rgb(51, 51, 51); font-family: "pingfang SC", "helvetica neue", arial, "hiragino sans gb", "microsoft yahei ui", "microsoft yahei", simsun, sans-serif; white-space: pre-wrap;">1、因为咱们测试的机器数据库全部都是初始值，所以不需要导入任何数据库。（写的原因是假如，配置的环境和现在不一致。我们仍然需要做数据备份和导入）<p style="margin-bottom: 8px; box-sizing: border-box; list-style: inherit; min-height: 24px; color: rgb(51, 51, 51); font-family: "pingfang SC", "helvetica neue", arial, "hiragino sans gb", "microsoft yahei ui", "microsoft yahei", simsun, sans-serif; white-space: pre-wrap;">2、数据同步帐号添加<p style="margin-bottom: 8px; box-sizing: border-box; list-style: inherit; min-height: 24px; color: rgb(51, 51, 51); font-family: "pingfang SC", "helvetica neue", arial, "hiragino sans gb", "microsoft yahei ui", "microsoft yahei", simsun, sans-serif; white-space: pre-wrap;">按理来说，之前我们已经配置了主主，也就是zhdy04和zhdy05已经互相授权了。接下来我们需要设置的是：<ol class="ol-level-0" style="margin-bottom: 16px; padding-left: 18px; box-sizing: border-box; list-style-position: inherit; list-style-image: inherit; color: rgb(51, 51, 51); font-family: "pingfang SC", "helvetica neue", arial, "hiragino sans gb", "microsoft yahei ui", "microsoft yahei", simsun, sans-serif; white-space: pre-wrap;"><li style="margin-bottom: 4px; margin-left: 0px; list-style: inherit; box-sizing: border-box;">在zhdy04上面授权zhdy06可以同步。</li><li style="margin-bottom: 4px; margin-left: 0px; list-style: inherit; box-sizing: border-box;">在zhdy05上面授权zhdy07可以同步。</li></ol><h4 id="zhdy04-masterA%E6%9C%8D%E5%8A%A1%E5%99%A8%EF%BC%9A" name="zhdy04-masterA%E6%9C%8D%E5%8A%A1%E5%99%A8%EF%BC%9A" style="box-sizing: border-box; list-style: inherit; color: rgb(0, 0, 0); font-family: "pingfang SC", "helvetica neue", arial, "hiragino sans gb", "microsoft yahei ui", "microsoft yahei", simsun, sans-serif; white-space: pre-wrap;">zhdy04 masterA服务器：</h4><pre class="prism-token tokenlanguage-javascript" style="box-sizing: border-box; list-style: inherit; margin-top: 0.5em; margin-bottom: 0.5em; padding: 1em; color: rgb(204, 204, 204); background: rgb(80, 85, 107); border-radius: 3px; overflow: auto; font-family: Consolas, Monaco, "Andale Mono", "Ubuntu Mono", monospace; overflow-wrap: normal; word-break: normal; line-height: 1.5; tab-size: 4; hyphens: none;">mysql> grant replication slave on *.* to 'repl'@'192.168.230.147' identified by 'zhangduanya';
Query OK, 0 rows affected (0.03 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)</pre><h4 id="zhdy05-masterB%E6%9C%8D%E5%8A%A1%E5%99%A8%EF%BC%9A" name="zhdy05-masterB%E6%9C%8D%E5%8A%A1%E5%99%A8%EF%BC%9A" style="box-sizing: border-box; list-style: inherit; color: rgb(0, 0, 0); font-family: "pingfang SC", "helvetica neue", arial, "hiragino sans gb", "microsoft yahei ui", "microsoft yahei", simsun, sans-serif; white-space: pre-wrap;">zhdy05 masterB服务器：</h4><pre class="prism-token tokenlanguage-javascript" style="box-sizing: border-box; list-style: inherit; margin-top: 0.5em; margin-bottom: 0.5em; padding: 1em; color: rgb(204, 204, 204); background: rgb(80, 85, 107); border-radius: 3px; overflow: auto; font-family: Consolas, Monaco, "Andale Mono", "Ubuntu Mono", monospace; overflow-wrap: normal; word-break: normal; line-height: 1.5; tab-size: 4; hyphens: none;">mysql> grant replication slave on *.* to 'repl'@'192.168.230.148' identified by 'zhangduanya';
Query OK, 0 rows affected (0.03 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)</pre><p style="margin-bottom: 8px; box-sizing: border-box; list-style: inherit; min-height: 24px; color: rgb(51, 51, 51); font-family: "pingfang SC", "helvetica neue", arial, "hiragino sans gb", "microsoft yahei ui", "microsoft yahei", simsun, sans-serif; white-space: pre-wrap;">3、数据库配置文件修改<ol class="ol-level-0" style="margin-bottom: 16px; padding-left: 18px; box-sizing: border-box; list-style-position: inherit; list-style-image: inherit; color: rgb(51, 51, 51); font-family: "pingfang SC", "helvetica neue", arial, "hiragino sans gb", "microsoft yahei ui", "microsoft yahei", simsun, sans-serif; white-space: pre-wrap;"><li style="margin-bottom: 4px; margin-left: 0px; list-style: inherit; box-sizing: border-box;">zhdy04 masterA服务器：</li></ol><pre class="prism-token tokenlanguage-javascript" style="box-sizing: border-box; list-style: inherit; margin-top: 0.5em; margin-bottom: 0.5em; padding: 1em; color: rgb(204, 204, 204); background: rgb(80, 85, 107); border-radius: 3px; overflow: auto; font-family: Consolas, Monaco, "Andale Mono", "Ubuntu Mono", monospace; overflow-wrap: normal; word-break: normal; line-height: 1.5; tab-size: 4; hyphens: none;">server-id=145
log-bin=mysql-bin
auto-increment-increment=2
auto-increment-offset=1
log-slave-updates</pre><ol class="ol-level-0" style="margin-bottom: 16px; padding-left: 18px; box-sizing: border-box; list-style-position: inherit; list-style-image: inherit; color: rgb(51, 51, 51); font-family: "pingfang SC", "helvetica neue", arial, "hiragino sans gb", "microsoft yahei ui", "microsoft yahei", simsun, sans-serif; white-space: pre-wrap;"><li style="margin-bottom: 4px; margin-left: 0px; list-style: inherit; box-sizing: border-box;">zhdy05 masterB服务器：</li></ol><pre class="prism-token tokenlanguage-javascript" style="box-sizing: border-box; list-style: inherit; margin-top: 0.5em; margin-bottom: 0.5em; padding: 1em; color: rgb(204, 204, 204); background: rgb(80, 85, 107); border-radius: 3px; overflow: auto; font-family: Consolas, Monaco, "Andale Mono", "Ubuntu Mono", monospace; overflow-wrap: normal; word-break: normal; line-height: 1.5; tab-size: 4; hyphens: none;">server-id=146
log-bin=mysql-bin
auto-increment-increment=2
auto-increment-offset=2
log-slave-updates</pre><ol class="ol-level-0" style="margin-bottom: 16px; padding-left: 18px; box-sizing: border-box; list-style-position: inherit; list-style-image: inherit; color: rgb(51, 51, 51); font-family: "pingfang SC", "helvetica neue", arial, "hiragino sans gb", "microsoft yahei ui", "microsoft yahei", simsun, sans-serif; white-space: pre-wrap;"><li style="margin-bottom: 4px; margin-left: 0px; list-style: inherit; box-sizing: border-box;">zhdy06 slaveA服务器：</li></ol><pre class="prism-token tokenlanguage-javascript" style="box-sizing: border-box; list-style: inherit; margin-top: 0.5em; margin-bottom: 0.5em; padding: 1em; color: rgb(204, 204, 204); background: rgb(80, 85, 107); border-radius: 3px; overflow: auto; font-family: Consolas, Monaco, "Andale Mono", "Ubuntu Mono", monospace; overflow-wrap: normal; word-break: normal; line-height: 1.5; tab-size: 4; hyphens: none;">server-id=147</pre><ol class="ol-level-0" style="margin-bottom: 16px; padding-left: 18px; box-sizing: border-box; list-style-position: inherit; list-style-image: inherit; color: rgb(51, 51, 51); font-family: "pingfang SC", "helvetica neue", arial, "hiragino sans gb", "microsoft yahei ui", "microsoft yahei", simsun, sans-serif; white-space: pre-wrap;"><li style="margin-bottom: 4px; margin-left: 0px; list-style: inherit; box-sizing: border-box;">zhdy07 slaveB服务器：</li></ol><pre class="prism-token tokenlanguage-javascript" style="box-sizing: border-box; list-style: inherit; margin-top: 0.5em; margin-bottom: 0.5em; padding: 1em; color: rgb(204, 204, 204); background: rgb(80, 85, 107); border-radius: 3px; overflow: auto; font-family: Consolas, Monaco, "Andale Mono", "Ubuntu Mono", monospace; overflow-wrap: normal; word-break: normal; line-height: 1.5; tab-size: 4; hyphens: none;">server-id=148</pre><p style="margin-bottom: 8px; box-sizing: border-box; list-style: inherit; min-height: 24px; color: rgb(51, 51, 51); font-family: "pingfang SC", "helvetica neue", arial, "hiragino sans gb", "microsoft yahei ui", "microsoft yahei", simsun, sans-serif; white-space: pre-wrap;">在此我为了让大家更加容易理解我所做的配置，所以没有添加任何附加的服务，例如如下：<pre class="prism-token tokenlanguage-javascript" style="box-sizing: border-box; list-style: inherit; margin-top: 0.5em; margin-bottom: 0.5em; padding: 1em; color: rgb(204, 204, 204); background: rgb(80, 85, 107); border-radius: 3px; overflow: auto; font-family: Consolas, Monaco, "Andale Mono", "Ubuntu Mono", monospace; overflow-wrap: normal; word-break: normal; line-height: 1.5; tab-size: 4; hyphens: none;">log-bin = mysql-bin #打开二进制功能,MASTER主服务器必须打开此项
binlog-format=ROW
binlog-row-p_w_picpath=minimal
log-slave-updates=true
gtid-mode=on
enforce-gtid-consistency=true
master-info-repository=TABLE
relay-log-info-repository=TABLE
sync-master-info=1
slave-parallel-workers=0
sync_binlog=0
binlog-checksum=CRC32
master-verify-checksum=1
slave-sql-verify-checksum=1
binlog-rows-query-log_events=1
#expire_logs_days=5
max_binlog_size=1024M #binlog单文件最大值

replicate-ignore-db = mysql #忽略不同步主从的数据库
replicate-ignore-db = information_schema
replicate-ignore-db = performance_schema
replicate-ignore-db = test
replicate-ignore-db = zabbix

max_connections = 3000
max_connect_errors = 30

skip-character-set-client-handshake #忽略应用程序想要设置的其他字符集
init-connect='SET NAMES utf8' #连接时执行的SQL
character-set-server=utf8 #服务端默认字符集
wait_timeout=1800 #请求的最大连接时间
interactive_timeout=1800 #和上一参数同时修改才会生效
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES #sql模式

skip-name-resolve
slow_query_log=1
long_query_time = 6
slow_query_log_file=slow-query.log
innodb_flush_log_at_trx_commit = 2
innodb_log_buffer_size = 16M

binlog-do-db=openfire
binlog-ignore-db=mysql,information_schema
replicate-do-db=openfire
replicate-ignore-db=mysql,information_schema

read_only = 1 #开始只读模式
read_only=0; #关闭只读,可以读写</pre><p style="margin-bottom: 8px; box-sizing: border-box; list-style: inherit; min-height: 24px; color: rgb(51, 51, 51); font-family: "pingfang SC", "helvetica neue", arial, "hiragino sans gb", "microsoft yahei ui", "microsoft yahei", simsun, sans-serif; white-space: pre-wrap;">4、重启4台DB:<pre class="prism-token tokenlanguage-javascript" style="box-sizing: border-box; list-style: inherit; margin-top: 0.5em; margin-bottom: 0.5em; padding: 1em; color: rgb(204, 204, 204); background: rgb(80, 85, 107); border-radius: 3px; overflow: auto; font-family: Consolas, Monaco, "Andale Mono", "Ubuntu Mono", monospace; overflow-wrap: normal; word-break: normal; line-height: 1.5; tab-size: 4; hyphens: none;">/etc/init.d/mysqld restart</pre><p style="margin-bottom: 8px; box-sizing: border-box; list-style: inherit; min-height: 24px; color: rgb(51, 51, 51); font-family: "pingfang SC", "helvetica neue", arial, "hiragino sans gb", "microsoft yahei ui", "microsoft yahei", simsun, sans-serif; white-space: pre-wrap;">5、同步设置：<ol class="ol-level-0" style="margin-bottom: 16px; padding-left: 18px; box-sizing: border-box; list-style-position: inherit; list-style-image: inherit; color: rgb(51, 51, 51); font-family: "pingfang SC", "helvetica neue", arial, "hiragino sans gb", "microsoft yahei ui", "microsoft yahei", simsun, sans-serif; white-space: pre-wrap;"><li style="margin-bottom: 4px; margin-left: 0px; list-style: inherit; box-sizing: border-box;">zhdy04 masterA服务器：</li></ol><pre class="prism-token tokenlanguage-javascript" style="box-sizing: border-box; list-style: inherit; margin-top: 0.5em; margin-bottom: 0.5em; padding: 1em; color: rgb(204, 204, 204); background: rgb(80, 85, 107); border-radius: 3px; overflow: auto; font-family: Consolas, Monaco, "Andale Mono", "Ubuntu Mono", monospace; overflow-wrap: normal; word-break: normal; line-height: 1.5; tab-size: 4; hyphens: none;">mysql> show master status;
记录下各自的 master_log_file、master_log_pos值

+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000004 | 120 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.01 sec)</pre><ol class="ol-level-0" style="margin-bottom: 16px; padding-left: 18px; box-sizing: border-box; list-style-position: inherit; list-style-image: inherit; color: rgb(51, 51, 51); font-family: "pingfang SC", "helvetica neue", arial, "hiragino sans gb", "microsoft yahei ui", "microsoft yahei", simsun, sans-serif; white-space: pre-wrap;"><li style="margin-bottom: 4px; margin-left: 0px; list-style: inherit; box-sizing: border-box;">zhdy05 masterB服务器：</li></ol><pre class="prism-token tokenlanguage-javascript" style="box-sizing: border-box; list-style: inherit; margin-top: 0.5em; margin-bottom: 0.5em; padding: 1em; color: rgb(204, 204, 204); background: rgb(80, 85, 107); border-radius: 3px; overflow: auto; font-family: Consolas, Monaco, "Andale Mono", "Ubuntu Mono", monospace; overflow-wrap: normal; word-break: normal; line-height: 1.5; tab-size: 4; hyphens: none;">mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000004 | 120 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)</pre><ol class="ol-level-0" style="margin-bottom: 16px; padding-left: 18px; box-sizing: border-box; list-style-position: inherit; list-style-image: inherit; color: rgb(51, 51, 51); font-family: "pingfang SC", "helvetica neue", arial, "hiragino sans gb", "microsoft yahei ui", "microsoft yahei", simsun, sans-serif; white-space: pre-wrap;"><li style="margin-bottom: 4px; margin-left: 0px; list-style: inherit; box-sizing: border-box;">zhdy06 slaveA服务器：</li></ol><pre class="prism-token tokenlanguage-javascript" style="box-sizing: border-box; list-style: inherit; margin-top: 0.5em; margin-bottom: 0.5em; padding: 1em; color: rgb(204, 204, 204); background: rgb(80, 85, 107); border-radius: 3px; overflow: auto; font-family: Consolas, Monaco, "Andale Mono", "Ubuntu Mono", monospace; overflow-wrap: normal; word-break: normal; line-height: 1.5; tab-size: 4; hyphens: none;">mysql> change master to master_host='192.168.230.145', master_user='repl', master_password='zhangduanya', master_log_file='mysql-bin.000004', master_log_pos=120;
Query OK, 0 rows affected, 2 warnings (0.04 sec)</pre><ol class="ol-level-0" style="margin-bottom: 16px; padding-left: 18px; box-sizing: border-box; list-style-position: inherit; list-style-image: inherit; color: rgb(51, 51, 51); font-family: "pingfang SC", "helvetica neue", arial, "hiragino sans gb", "microsoft yahei ui", "microsoft yahei", simsun, sans-serif; white-space: pre-wrap;"><li style="margin-bottom: 4px; margin-left: 0px; list-style: inherit; box-sizing: border-box;">zhdy07 slaveB服务器：</li></ol><pre class="prism-token tokenlanguage-javascript" style="box-sizing: border-box; list-style: inherit; margin-top: 0.5em; margin-bottom: 0.5em; padding: 1em; color: rgb(204, 204, 204); background: rgb(80, 85, 107); border-radius: 3px; overflow: auto; font-family: Consolas, Monaco, "Andale Mono", "Ubuntu Mono", monospace; overflow-wrap: normal; word-break: normal; line-height: 1.5; tab-size: 4; hyphens: none;">mysql> change master to master_host='192.168.230.146', master_user='repl', master_password='zhangduanya', master_log_file='mysql-bin.000004', master_log_pos=120;
Query OK, 0 rows affected, 2 warnings (0.08 sec)</pre><p style="margin-bottom: 8px; box-sizing: border-box; list-style: inherit; min-height: 24px; color: rgb(51, 51, 51); font-family: "pingfang SC", "helvetica neue", arial, "hiragino sans gb", "microsoft yahei ui", "microsoft yahei", simsun, sans-serif; white-space: pre-wrap;">6、检查配置<p style="margin-bottom: 8px; box-sizing: border-box; list-style: inherit; min-height: 24px; color: rgb(51, 51, 51); font-family: "pingfang SC", "helvetica neue", arial, "hiragino sans gb", "microsoft yahei ui", "microsoft yahei", simsun, sans-serif; white-space: pre-wrap;">首先一定要把四台的firewalld全部关闭！<pre class="prism-token tokenlanguage-javascript" style="box-sizing: border-box; list-style: inherit; margin-top: 0.5em; margin-bottom: 0.5em; padding: 1em; color: rgb(204, 204, 204); background: rgb(80, 85, 107); border-radius: 3px; overflow: auto; font-family: Consolas, Monaco, "Andale Mono", "Ubuntu Mono", monospace; overflow-wrap: normal; word-break: normal; line-height: 1.5; tab-size: 4; hyphens: none;"># systemctl stop firewalld
# systemctl disable firewalld</pre><p style="margin-bottom: 8px; box-sizing: border-box; list-style: inherit; min-height: 24px; color: rgb(51, 51, 51); font-family: "pingfang SC", "helvetica neue", arial, "hiragino sans gb", "microsoft yahei ui", "microsoft yahei", simsun, sans-serif; white-space: pre-wrap;">分别检查四台服务器的<p style="margin-bottom: 8px; box-sizing: border-box; list-style: inherit; min-height: 24px; color: rgb(51, 51, 51); font-family: "pingfang SC", "helvetica neue", arial, "hiragino sans gb", "microsoft yahei ui", "microsoft yahei", simsun, sans-serif; white-space: pre-wrap;">Slave_IO_Running: Yes Slave_SQL_Running: Yes<p style="margin-bottom: 8px; box-sizing: border-box; list-style: inherit; min-height: 24px; color: rgb(51, 51, 51); font-family: "pingfang SC", "helvetica neue", arial, "hiragino sans gb", "microsoft yahei ui", "microsoft yahei", simsun, sans-serif; white-space: pre-wrap;">开启情况！<ol class="ol-level-0" style="margin-bottom: 16px; padding-left: 18px; box-sizing: border-box; list-style-position: inherit; list-style-image: inherit; color: rgb(51, 51, 51); font-family: "pingfang SC", "helvetica neue", arial, "hiragino sans gb", "microsoft yahei ui", "microsoft yahei", simsun, sans-serif; white-space: pre-wrap;"><li style="margin-bottom: 4px; margin-left: 0px; list-style: inherit; box-sizing: border-box;">zhdy06 slaveA服务器：</li></ol><pre class="prism-token tokenlanguage-javascript" style="box-sizing: border-box; list-style: inherit; margin-top: 0.5em; margin-bottom: 0.5em; padding: 1em; color: rgb(204, 204, 204); background: rgb(80, 85, 107); border-radius: 3px; overflow: auto; font-family: Consolas, Monaco, "Andale Mono", "Ubuntu Mono", monospace; overflow-wrap: normal; word-break: normal; line-height: 1.5; tab-size: 4; hyphens: none;">mysql> start slave;
Query OK, 0 rows affected (0.02 sec)

mysql> show slave status\G
*************************** 1. row ***************************
 Slave_IO_State: Waiting for master to send event
 Master_Host: 192.168.230.145
 Master_User: repl
 Master_Port: 3306
 Connect_Retry: 60
 Master_Log_File: mysql-bin.000004
 Read_Master_Log_Pos: 120
 Relay_Log_File: zhdy06-relay-bin.000002
 Relay_Log_Pos: 283
 Relay_Master_Log_File: mysql-bin.000004
 Slave_IO_Running: Yes
 Slave_SQL_Running: Yes</pre><ol class="ol-level-0" style="margin-bottom: 16px; padding-left: 18px; box-sizing: border-box; list-style-position: inherit; list-style-image: inherit; color: rgb(51, 51, 51); font-family: "pingfang SC", "helvetica neue", arial, "hiragino sans gb", "microsoft yahei ui", "microsoft yahei", simsun, sans-serif; white-space: pre-wrap;"><li style="margin-bottom: 4px; margin-left: 0px; list-style: inherit; box-sizing: border-box;">zhdy04 masterA服务器：</li></ol><pre class="prism-token tokenlanguage-javascript" style="box-sizing: border-box; list-style: inherit; margin-top: 0.5em; margin-bottom: 0.5em; padding: 1em; color: rgb(204, 204, 204); background: rgb(80, 85, 107); border-radius: 3px; overflow: auto; font-family: Consolas, Monaco, "Andale Mono", "Ubuntu Mono", monospace; overflow-wrap: normal; word-break: normal; line-height: 1.5; tab-size: 4; hyphens: none;">mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show slave status\G
*************************** 1. row ***************************
 Slave_IO_State: Waiting for master to send event
 Master_Host: 192.168.230.146
 Master_User: repl
 Master_Port: 3306
 Connect_Retry: 60
 Master_Log_File: mysql-bin.000004
 Read_Master_Log_Pos: 120
 Relay_Log_File: zhdy04-relay-bin.000012
 Relay_Log_Pos: 283
 Relay_Master_Log_File: mysql-bin.000004
 Slave_IO_Running: Yes
 Slave_SQL_Running: Yes</pre><ol class="ol-level-0" style="margin-bottom: 16px; padding-left: 18px; box-sizing: border-box; list-style-position: inherit; list-style-image: inherit; color: rgb(51, 51, 51); font-family: "pingfang SC", "helvetica neue", arial, "hiragino sans gb", "microsoft yahei ui", "microsoft yahei", simsun, sans-serif; white-space: pre-wrap;"><li style="margin-bottom: 4px; margin-left: 0px; list-style: inherit; box-sizing: border-box;">zhdy07 slaveB服务器：</li></ol><pre class="prism-token tokenlanguage-javascript" style="box-sizing: border-box; list-style: inherit; margin-top: 0.5em; margin-bottom: 0.5em; padding: 1em; color: rgb(204, 204, 204); background: rgb(80, 85, 107); border-radius: 3px; overflow: auto; font-family: Consolas, Monaco, "Andale Mono", "Ubuntu Mono", monospace; overflow-wrap: normal; word-break: normal; line-height: 1.5; tab-size: 4; hyphens: none;">mysql> start slave;
Query OK, 0 rows affected (0.02 sec)

mysql> show slave status\G
*************************** 1. row ***************************
 Slave_IO_State: Waiting for master to send event
 Master_Host: 192.168.230.146
 Master_User: repl
 Master_Port: 3306
 Connect_Retry: 60
 Master_Log_File: mysql-bin.000004
 Read_Master_Log_Pos: 120
 Relay_Log_File: zhdy07-relay-bin.000002
 Relay_Log_Pos: 283
 Relay_Master_Log_File: mysql-bin.000004
 Slave_IO_Running: Yes
 Slave_SQL_Running: Yes</pre><ol class="ol-level-0" style="margin-bottom: 16px; padding-left: 18px; box-sizing: border-box; list-style-position: inherit; list-style-image: inherit; color: rgb(51, 51, 51); font-family: "pingfang SC", "helvetica neue", arial, "hiragino sans gb", "microsoft yahei ui", "microsoft yahei", simsun, sans-serif; white-space: pre-wrap;"><li style="margin-bottom: 4px; margin-left: 0px; list-style: inherit; box-sizing: border-box;">zhdy05 masterB服务器：</li></ol><pre class="prism-token tokenlanguage-javascript" style="box-sizing: border-box; list-style: inherit; margin-top: 0.5em; margin-bottom: 0.5em; padding: 1em; color: rgb(204, 204, 204); background: rgb(80, 85, 107); border-radius: 3px; overflow: auto; font-family: Consolas, Monaco, "Andale Mono", "Ubuntu Mono", monospace; overflow-wrap: normal; word-break: normal; line-height: 1.5; tab-size: 4; hyphens: none;">mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show slave status\G
*************************** 1. row ***************************
 Slave_IO_State: Waiting for master to send event
 Master_Host: 192.168.230.145
 Master_User: repl
 Master_Port: 3306
 Connect_Retry: 60
 Master_Log_File: mysql-bin.000004
 Read_Master_Log_Pos: 120
 Relay_Log_File: zhdy05-relay-bin.000011
 Relay_Log_Pos: 283
 Relay_Master_Log_File: mysql-bin.000004
 Slave_IO_Running: Yes
 Slave_SQL_Running: Yes</pre><p style="margin-bottom: 8px; box-sizing: border-box; list-style: inherit; min-height: 24px; color: rgb(51, 51, 51); font-family: "pingfang SC", "helvetica neue", arial, "hiragino sans gb", "microsoft yahei ui", "microsoft yahei", simsun, sans-serif; white-space: pre-wrap;">由此看来全部同步成功！<p style="margin-bottom: 8px; box-sizing: border-box; list-style: inherit; min-height: 24px; color: rgb(51, 51, 51); font-family: "pingfang SC", "helvetica neue", arial, "hiragino sans gb", "microsoft yahei ui", "microsoft yahei", simsun, sans-serif; white-space: pre-wrap;">7、最后一步，在主服务器zhdy04 masterA创建一个数据库，也就是说这四台服务器都会有这个数据库！<pre class="prism-token tokenlanguage-javascript" style="box-sizing: border-box; list-style: inherit; margin-top: 0.5em; margin-bottom: 0.5em; padding: 1em; color: rgb(204, 204, 204); background: rgb(80, 85, 107); border-radius: 3px; overflow: auto; font-family: Consolas, Monaco, "Andale Mono", "Ubuntu Mono", monospace; overflow-wrap: normal; word-break: normal; line-height: 1.5; tab-size: 4; hyphens: none;">mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| qqq |
| test |
+--------------------+
5 rows in set (0.06 sec)</pre><p style="margin-bottom: 8px; box-sizing: border-box; list-style: inherit; min-height: 24px; color: rgb(51, 51, 51); font-family: "pingfang SC", "helvetica neue", arial, "hiragino sans gb", "microsoft yahei ui", "microsoft yahei", simsun, sans-serif; white-space: pre-wrap;">我就不一一截屏了，然后所有的数据库均有了qqq这个数据库！<p style="margin-bottom: 8px; box-sizing: border-box; list-style: inherit; min-height: 24px; color: rgb(51, 51, 51); font-family: "pingfang SC", "helvetica neue", arial, "hiragino sans gb", "microsoft yahei ui", "microsoft yahei", simsun, sans-serif; white-space: pre-wrap;">8、总结<p style="margin-bottom: 8px; box-sizing: border-box; list-style: inherit; min-height: 24px; color: rgb(51, 51, 51); font-family: "pingfang SC", "helvetica neue", arial, "hiragino sans gb", "microsoft yahei ui", "microsoft yahei", simsun, sans-serif; white-space: pre-wrap;">也就是说，未来我们搭建多主多从看完这个配置是不是也有思路了？就像配置路由器一样，互相连接的互相授权下是不是就可以了？<p style="margin-bottom: 8px; box-sizing: border-box; list-style: inherit; min-height: 24px; color: rgb(51, 51, 51); font-family: "pingfang SC", "helvetica neue", arial, "hiragino sans gb", "microsoft yahei ui", "microsoft yahei", simsun, sans-serif; white-space: pre-wrap;">给大家一个设置的方法：<pre class="prism-token tokenlanguage-javascript" style="box-sizing: border-box; list-style: inherit; margin-top: 0.5em; margin-bottom: 0.5em; padding: 1em; color: rgb(204, 204, 204); background: rgb(80, 85, 107); border-radius: 3px; overflow: auto; font-family: Consolas, Monaco, "Andale Mono", "Ubuntu Mono", monospace; overflow-wrap: normal; word-break: normal; line-height: 1.5; tab-size: 4; hyphens: none;">auto-increment-increment = 10
auto-increment-offset = 1</pre><pre class="prism-token tokenlanguage-javascript" style="box-sizing: border-box; list-style: inherit; margin-top: 0.5em; margin-bottom: 0.5em; padding: 1em; color: rgb(204, 204, 204); background: rgb(80, 85, 107); border-radius: 3px; overflow: auto; font-family: Consolas, Monaco, "Andale Mono", "Ubuntu Mono", monospace; overflow-wrap: normal; word-break: normal; line-height: 1.5; tab-size: 4; hyphens: none;">auto-increment-increment = 10
auto-increment-offset = 2</pre><pre class="prism-token tokenlanguage-javascript" style="box-sizing: border-box; list-style: inherit; margin-top: 0.5em; margin-bottom: 0.5em; padding: 1em; color: rgb(204, 204, 204); background: rgb(80, 85, 107); border-radius: 3px; overflow: auto; font-family: Consolas, Monaco, "Andale Mono", "Ubuntu Mono", monospace; overflow-wrap: normal; word-break: normal; line-height: 1.5; tab-size: 4; hyphens: none;">auto-increment-increment = 10
auto-increment-offset = 3</pre><p style="margin-bottom: 8px; box-sizing: border-box; list-style: inherit; min-height: 24px; color: rgb(51, 51, 51); font-family: "pingfang SC", "helvetica neue", arial, "hiragino sans gb", "microsoft yahei ui", "microsoft yahei", simsun, sans-serif; white-space: pre-wrap;">这样是不是就可以设置10台主mysql了呢？<p style="margin-bottom: 8px; box-sizing: border-box; list-style: inherit; min-height: 24px; color: rgb(51, 51, 51); font-family: "pingfang SC", "helvetica neue", arial, "hiragino sans gb", "microsoft yahei ui", "microsoft yahei", simsun, sans-serif; white-space: pre-wrap;">但是一旦超过20台呢？或者是100台？<p style="margin-bottom: 8px; box-sizing: border-box; list-style: inherit; min-height: 24px; color: rgb(51, 51, 51); font-family: "pingfang SC", "helvetica neue", arial, "hiragino sans gb", "microsoft yahei ui", "microsoft yahei", simsun, sans-serif; white-space: pre-wrap;">20台<pre class="prism-token tokenlanguage-javascript" style="box-sizing: border-box; list-style: inherit; margin-top: 0.5em; margin-bottom: 0.5em; padding: 1em; color: rgb(204, 204, 204); background: rgb(80, 85, 107); border-radius: 3px; overflow: auto; font-family: Consolas, Monaco, "Andale Mono", "Ubuntu Mono", monospace; overflow-wrap: normal; word-break: normal; line-height: 1.5; tab-size: 4; hyphens: none;">auto-increment-increment = 20
auto-increment-offset = 1</pre><pre class="prism-token tokenlanguage-javascript" style="box-sizing: border-box; list-style: inherit; margin-top: 0.5em; margin-bottom: 0.5em; padding: 1em; color: rgb(204, 204, 204); background: rgb(80, 85, 107); border-radius: 3px; overflow: auto; font-family: Consolas, Monaco, "Andale Mono", "Ubuntu Mono", monospace; overflow-wrap: normal; word-break: normal; line-height: 1.5; tab-size: 4; hyphens: none;">auto-increment-increment = 20
auto-increment-offset = 2</pre><p style="margin-bottom: 8px; box-sizing: border-box; list-style: inherit; min-height: 24px; color: rgb(51, 51, 51); font-family: "pingfang SC", "helvetica neue", arial, "hiragino sans gb", "microsoft yahei ui", "microsoft yahei", simsun, sans-serif; white-space: pre-wrap;">…..<pre class="prism-token tokenlanguage-javascript" style="box-sizing: border-box; list-style: inherit; margin-top: 0.5em; margin-bottom: 0.5em; padding: 1em; color: rgb(204, 204, 204); background: rgb(80, 85, 107); border-radius: 3px; overflow: auto; font-family: Consolas, Monaco, "Andale Mono", "Ubuntu Mono", monospace; overflow-wrap: normal; word-break: normal; line-height: 1.5; tab-size: 4; hyphens: none;">auto-increment-increment = 20
auto-increment-offset = 20</pre><p style="margin-bottom: 8px; box-sizing: border-box; list-style: inherit; min-height: 24px; color: rgb(51, 51, 51); font-family: "pingfang SC", "helvetica neue", arial, "hiragino sans gb", "microsoft yahei ui", "microsoft yahei", simsun, sans-serif; white-space: pre-wrap;">100台<pre class="prism-token tokenlanguage-javascript" style="box-sizing: border-box; list-style: inherit; margin-top: 0.5em; margin-bottom: 0.5em; padding: 1em; color: rgb(204, 204, 204); background: rgb(80, 85, 107); border-radius: 3px; overflow: auto; font-family: Consolas, Monaco, "Andale Mono", "Ubuntu Mono", monospace; overflow-wrap: normal; word-break: normal; line-height: 1.5; tab-size: 4; hyphens: none;">auto-increment-increment = 100
auto-increment-offset = 1</pre><pre class="prism-token tokenlanguage-javascript" style="box-sizing: border-box; list-style: inherit; margin-top: 0.5em; margin-bottom: 0.5em; padding: 1em; color: rgb(204, 204, 204); background: rgb(80, 85, 107); border-radius: 3px; overflow: auto; font-family: Consolas, Monaco, "Andale Mono", "Ubuntu Mono", monospace; overflow-wrap: normal; word-break: normal; line-height: 1.5; tab-size: 4; hyphens: none;">auto-increment-increment = 100
auto-increment-offset = 2</pre><p style="margin-bottom: 8px; box-sizing: border-box; list-style: inherit; min-height: 24px; color: rgb(51, 51, 51); font-family: "pingfang SC", "helvetica neue", arial, "hiragino sans gb", "microsoft yahei ui", "microsoft yahei", simsun, sans-serif; white-space: pre-wrap;">…..<pre class="prism-token tokenlanguage-javascript" style="box-sizing: border-box; list-style: inherit; margin-top: 0.5em; margin-bottom: 0.5em; padding: 1em; color: rgb(204, 204, 204); background: rgb(80, 85, 107); border-radius: 3px; overflow: auto; font-family: Consolas, Monaco, "Andale Mono", "Ubuntu Mono", monospace; overflow-wrap: normal; word-break: normal; line-height: 1.5; tab-size: 4; hyphens: none;">auto-increment-increment = 100
auto-increment-offset = 100</pre><p style="margin-bottom: 8px; box-sizing: border-box; list-style: inherit; min-height: 24px; color: rgb(51, 51, 51); font-family: "pingfang SC", "helvetica neue", arial, "hiragino sans gb", "microsoft yahei ui", "microsoft yahei", simsun, sans-serif; white-space: pre-wrap;">是不是就不会重复了？不难看出，自增长值可设置为主机的数量值即可，但是一定要想到可扩充性，也就是尽量设置这个值大些。

页: [1]

晓云网络's Archiver

MySQL两主（多主）多从架构配置