yxrj 发表于 2020-3-26 10:50:37

MySQL配置主主同步(master-master)

<br><div><br></div><div>MySQL主主同步跟MySQL主从同步类似,只是再另一台上反向再做一次主从同步。</div><div><div><div><p>
只不过MySQL的配置文件有些地方需要注意一下。</p>
<h1>实验环境</h1>
<table>
<thead>
<tr>
<th>名称</th>
<th>IP地址</th>
<th>系统版本</th>
<th>MySQL版本</th>
</tr>
</thead>
<tbody>
<tr>
<td>testA</td>
<td>192.168.1.235</td>
<td>CentOS 6.5</td>
<td>mysql-5.7.13</td>
</tr>
<tr>
<td>testB</td>
<td>192.168.1.237</td>
<td>CentOS 6.5</td>
<td>mysql-5.7.13</td>
</tr>
</tbody>
</table>
<h1>1. 创建用于同步数据的用户</h1>
<h2>a. testA服务器</h2></div><br><pre class="line-numberslanguage-xml" style="box-sizing: border-box; font-size: 12px; font-family: Consolas, Monaco, &quot;Andale Mono&quot;, &quot;Ubuntu Mono&quot;, monospace; margin-bottom: 20px; overflow: scroll auto; overflow-wrap: normal; word-break: break-all; overscroll-behavior-x: contain; border-radius: 4px; z-index: 0; padding: 1em 1em 1em 3.8em; line-height: 1.5; color: rgb(204, 204, 204); background: rgb(45, 45, 45); tab-size: 4; hyphens: none; position: relative; counter-reset: linenumber 0;"><code class="xmllanguage-xml" style="box-sizing: border-box; font-size: 1em; font-family: Consolas, Monaco, &quot;Andale Mono&quot;, &quot;Ubuntu Mono&quot;, monospace; border: none; vertical-align: unset; white-space: inherit; color: inherit; background: none transparent; word-spacing: normal; word-break: normal; overflow-wrap: normal; line-height: 1.5; tab-size: 4; hyphens: none; position: relative;">grant replication slave on *.* to 'sync'@'192.168.1.237' identified by '<span class="token tag" style="box-sizing: border-box; color: rgb(226, 119, 122);"><span class="token tag" style="box-sizing: border-box;"><span class="token punctuation" style="box-sizing: border-box; color: rgb(204, 204, 204);">&lt;</span>YourPassword</span><span class="token punctuation" style="box-sizing: border-box; color: rgb(204, 204, 204);">&gt;</span></span>';    #IP地址可以指定固定的IP,提高安全性。若用'%'则表示所有的IP均可
flush privileges;</code></pre></div></div><div><h2 style="box-sizing: border-box; margin-bottom: 16px; color: rgb(64, 64, 64); text-rendering: optimizelegibility; font-size: 24px; font-family: -apple-system, BlinkMacSystemFont, &quot;Apple Color Emoji&quot;, &quot;Segoe UI Emoji&quot;, &quot;Segoe UI Symbol&quot;, &quot;Segoe UI&quot;, &quot;PingFang SC&quot;, &quot;Hiragino Sans GB&quot;, &quot;Microsoft YaHei&quot;, &quot;Helvetica Neue&quot;, Helvetica, Arial, sans-serif;">b. testB服务器</h2></div><div><pre class="line-numberslanguage-xml" style="box-sizing: border-box; font-size: 12px; font-family: Consolas, Monaco, &quot;Andale Mono&quot;, &quot;Ubuntu Mono&quot;, monospace; margin-bottom: 20px; overflow: scroll auto; overflow-wrap: normal; word-break: break-all; overscroll-behavior-x: contain; border-radius: 4px; z-index: 0; padding: 1em 1em 1em 3.8em; line-height: 1.5; color: rgb(204, 204, 204); background: rgb(45, 45, 45); tab-size: 4; hyphens: none; position: relative; counter-reset: linenumber 0;"><code class="xmllanguage-xml" style="box-sizing: border-box; font-size: 1em; font-family: Consolas, Monaco, &quot;Andale Mono&quot;, &quot;Ubuntu Mono&quot;, monospace; border: none; vertical-align: unset; white-space: inherit; color: inherit; background: none transparent; word-spacing: normal; word-break: normal; overflow-wrap: normal; line-height: 1.5; tab-size: 4; hyphens: none; position: relative;">grant replication slave on *.* to 'sync'@'192.168.1.235' identified by '<span class="token tag" style="box-sizing: border-box; color: rgb(226, 119, 122);"><span class="token tag" style="box-sizing: border-box;"><span class="token punctuation" style="box-sizing: border-box; color: rgb(204, 204, 204);">&lt;</span>YourPassword</span><span class="token punctuation" style="box-sizing: border-box; color: rgb(204, 204, 204);">&gt;</span></span>';    #IP地址可以指定固定的IP,提高安全性。若用'%'则表示所有的IP均可
flush privileges;</code></pre></div><div><h1 style="box-sizing: border-box; margin-bottom: 16px; color: rgb(64, 64, 64); text-rendering: optimizelegibility; font-size: 26px; font-family: -apple-system, BlinkMacSystemFont, &quot;Apple Color Emoji&quot;, &quot;Segoe UI Emoji&quot;, &quot;Segoe UI Symbol&quot;, &quot;Segoe UI&quot;, &quot;PingFang SC&quot;, &quot;Hiragino Sans GB&quot;, &quot;Microsoft YaHei&quot;, &quot;Helvetica Neue&quot;, Helvetica, Arial, sans-serif;">2. MySQL数据库配置文件的注意点</h1></div><div><pre class="line-numberslanguage-xml" style="box-sizing: border-box; font-size: 12px; font-family: Consolas, Monaco, &quot;Andale Mono&quot;, &quot;Ubuntu Mono&quot;, monospace; margin-bottom: 20px; overflow: scroll auto; overflow-wrap: normal; word-break: break-all; overscroll-behavior-x: contain; border-radius: 4px; z-index: 0; padding: 1em 1em 1em 3.8em; line-height: 1.5; color: rgb(204, 204, 204); background: rgb(45, 45, 45); tab-size: 4; hyphens: none; position: relative; counter-reset: linenumber 0;"><code class="xmllanguage-xml" style="box-sizing: border-box; font-size: 1em; font-family: Consolas, Monaco, &quot;Andale Mono&quot;, &quot;Ubuntu Mono&quot;, monospace; border: none; vertical-align: unset; white-space: inherit; color: inherit; background: none transparent; word-spacing: normal; word-break: normal; overflow-wrap: normal; line-height: 1.5; tab-size: 4; hyphens: none; position: relative;">
server-id                      = 1                     #[必须]服务器唯一ID,每台服务器需不同
log-bin                        = /home/mysql/mysql-bin #[必须]启用二进制文件
binlog_format                  = mixed               #[不是必须]二进制文件启用混合模式
expire-logs-days               = 14                  #[不是必须]二进制文件过期时间,单位是天
sync-binlog                  = 1                     #[不是必须]当每进行1次事务提交之后,MySQL将进行一次磁盘同步指令来将binlog_cache中的数据强制写入磁盘

# MASTER DB #
binlog-do-db                   = test,androidpnserver                        #[不是必须]只将对应的数据库变动写入二进制文件。如果有多个数据库可用逗号分隔,或者使用多个binlog-do-db选项
binlog-ignore-db               = mysql,information_schema,performance_schema #[必须]不需要记录二进制日志的数据库。如果有多个数据库可用逗号分隔,或者使用多个binlog-do-db选项。一般为了保证主主同步不冲突,会忽略mysql数据库。
auto-increment-increment       = 10                                          #[必须]
auto-increment-offset          = 1                                           #[必须]
#做主主备份的时候,因为每台数据库服务器都可能在同一个表中插入数据,如果表有一个自动增长的主键,那么就会在多服务器上出现主键冲突。
#解决这个问题的办法就是让每个数据库的自增主键不连续。上面两项说的是,假设需要将来可能需要10台服务器做备份,将auto-increment-increment设为10。而auto-increment-offset=1表示这台服务器的序号。从1开始,不超过auto-increment-increment。

# SLAVE DB #
replicate-do-db                = test,androidpnserver                        #[不是必须]只同步对应的数据库。如果有多个数据库可用逗号分隔,或者使用多个replicate-do-db选项
replicate-ignore-db            = mysql,information_schema,performance_schema #[必须]不需要同步的数据库。如果有多个数据库可用逗号分隔,或者使用多个replicate-ignore-db选项。一般为了保证主主同步不冲突,会不同步mysql数据库。
relay_log                      = /home/mysql/relay-bin                     #[不是必须]开启中继日志,复制线程先把远程的变化复制到中继日志中,再执行。
log-slave-updates            = ON                                          #[必须]中继日志执行之后将变化写入自己的二进制文件

slave-skip-errors            = all                                       #[不是必须]跳过所有的sql语句失败</code></pre></div><div>这里需要注意点的是<span style="background-color: transparent; color: inherit; font-family: Consolas, Monaco, &quot;Andale Mono&quot;, &quot;Ubuntu Mono&quot;, monospace; font-size: 1em; white-space: inherit; word-spacing: normal;">replicate-do-db&nbsp;和&nbsp;&nbsp;</span><span style="background-color: transparent; color: inherit; font-family: Consolas, Monaco, &quot;Andale Mono&quot;, &quot;Ubuntu Mono&quot;, monospace; font-size: 1em; white-space: inherit; word-spacing: normal;">replicate-ignore-db&nbsp;</span><span style="font-family: Consolas, Monaco, &quot;Andale Mono&quot;, &quot;Ubuntu Mono&quot;, monospace;">这2个尽量不要用,是个大坑。</span></div><div><span style="color: rgb(51, 51, 51); font-family: -apple-system, &quot;SF UI Text&quot;, Arial, &quot;PingFang SC&quot;, &quot;Hiragino Sans GB&quot;, &quot;Microsoft YaHei&quot;, &quot;WenQuanYi Micro Hei&quot;, sans-serif, SimHei, SimSun;"><br></span></div><div><span style="color: rgb(51, 51, 51); font-family: -apple-system, &quot;SF UI Text&quot;, Arial, &quot;PingFang SC&quot;, &quot;Hiragino Sans GB&quot;, &quot;Microsoft YaHei&quot;, &quot;WenQuanYi Micro Hei&quot;, sans-serif, SimHei, SimSun;">可以使用replicate_wild_do_table和replicate_wild_ignore_table来代替</span></div><span style="color: rgb(51, 51, 51); font-family: -apple-system, &quot;SF UI Text&quot;, Arial, &quot;PingFang SC&quot;, &quot;Hiragino Sans GB&quot;, &quot;Microsoft YaHei&quot;, &quot;WenQuanYi Micro Hei&quot;, sans-serif, SimHei, SimSun;">如</span><br style="box-sizing: border-box; outline: 0px; color: rgb(51, 51, 51); font-family: -apple-system, &quot;SF UI Text&quot;, Arial, &quot;PingFang SC&quot;, &quot;Hiragino Sans GB&quot;, &quot;Microsoft YaHei&quot;, &quot;WenQuanYi Micro Hei&quot;, sans-serif, SimHei, SimSun;"><span style="color: rgb(51, 51, 51); font-family: -apple-system, &quot;SF UI Text&quot;, Arial, &quot;PingFang SC&quot;, &quot;Hiragino Sans GB&quot;, &quot;Microsoft YaHei&quot;, &quot;WenQuanYi Micro Hei&quot;, sans-serif, SimHei, SimSun;">replicate_wild_do_table=test.%</span><br style="box-sizing: border-box; outline: 0px; color: rgb(51, 51, 51); font-family: -apple-system, &quot;SF UI Text&quot;, Arial, &quot;PingFang SC&quot;, &quot;Hiragino Sans GB&quot;, &quot;Microsoft YaHei&quot;, &quot;WenQuanYi Micro Hei&quot;, sans-serif, SimHei, SimSun;"><span style="color: rgb(51, 51, 51); font-family: -apple-system, &quot;SF UI Text&quot;, Arial, &quot;PingFang SC&quot;, &quot;Hiragino Sans GB&quot;, &quot;Microsoft YaHei&quot;, &quot;WenQuanYi Micro Hei&quot;, sans-serif, SimHei, SimSun;">或</span><br style="box-sizing: border-box; outline: 0px; color: rgb(51, 51, 51); font-family: -apple-system, &quot;SF UI Text&quot;, Arial, &quot;PingFang SC&quot;, &quot;Hiragino Sans GB&quot;, &quot;Microsoft YaHei&quot;, &quot;WenQuanYi Micro Hei&quot;, sans-serif, SimHei, SimSun;"><span style="color: rgb(51, 51, 51); font-family: -apple-system, &quot;SF UI Text&quot;, Arial, &quot;PingFang SC&quot;, &quot;Hiragino Sans GB&quot;, &quot;Microsoft YaHei&quot;, &quot;WenQuanYi Micro Hei&quot;, sans-serif, SimHei, SimSun;">replicate_wild_ignore_table=mysql.%</span><div><br></div><div><br></div><div><br></div><div><h2 style="box-sizing: border-box; margin-bottom: 16px; color: rgb(64, 64, 64); text-rendering: optimizelegibility; font-size: 24px; font-family: -apple-system, BlinkMacSystemFont, &quot;Apple Color Emoji&quot;, &quot;Segoe UI Emoji&quot;, &quot;Segoe UI Symbol&quot;, &quot;Segoe UI&quot;, &quot;PingFang SC&quot;, &quot;Hiragino Sans GB&quot;, &quot;Microsoft YaHei&quot;, &quot;Helvetica Neue&quot;, Helvetica, Arial, sans-serif;">a. testA的数据库配置文件</h2></div><div><br></div><div><pre class="line-numberslanguage-xml" style="box-sizing: border-box; font-size: 12px; font-family: Consolas, Monaco, &quot;Andale Mono&quot;, &quot;Ubuntu Mono&quot;, monospace; margin-bottom: 20px; overflow: scroll auto; overflow-wrap: normal; word-break: break-all; overscroll-behavior-x: contain; border-radius: 4px; z-index: 0; padding: 1em 1em 1em 3.8em; line-height: 1.5; color: rgb(204, 204, 204); background: rgb(45, 45, 45); tab-size: 4; hyphens: none; position: relative; counter-reset: linenumber 0;"><code class="xmllanguage-xml" style="box-sizing: border-box; font-size: 1em; font-family: Consolas, Monaco, &quot;Andale Mono&quot;, &quot;Ubuntu Mono&quot;, monospace; border: none; vertical-align: unset; white-space: inherit; color: inherit; background: none transparent; word-spacing: normal; word-break: normal; overflow-wrap: normal; line-height: 1.5; tab-size: 4; hyphens: none; position: relative;"># For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html

# Configuration name server



# CLIENT #
port                           = 3306
default-character-set          = utf8


socket                         = /home/mysql/mysql.sock


#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M

# GENERAL #
user                           = mysql
default-storage-engine         = InnoDB
socket                         = /home/mysql/mysql.sock
pid-file                     = /home/mysql/mysql.pid
character-set-server         = utf8
lower-case-table-names         = 1
performance_schema             = ON
sql_mode                     = ''
server-id                      = 1

# MyISAM #
key-buffer-size                = 32M
myisam_recover_options         = FORCE,BACKUP

# SAFETY #
max-allowed-packet             = 16M
max-connect-errors             = 1000000

# DATA STORAGE #
datadir                        = /home/mysql/

# BINARY LOGGING #
log-bin                        = /home/mysql/mysql-bin
binlog_format                  = mixed
expire-logs-days               = 14
sync-binlog                  = 1
log-bin-trust-function-creators= 1

# MASTER DB #
#binlog-do-db                   = mdm,androidpnserver
binlog-ignore-db               = mysql,information_schema,performance_schema
auto-increment-increment       = 2
auto-increment-offset          = 1

# SLAVE DB #
#replicate-do-db                = mdm,androidpnserver
replicate-ignore-db            = mysql,information_schema,performance_schema
relay_log                      = /home/mysql/relay-bin
log-slave-updates            = ON

# CACHES AND LIMITS #
tmp-table-size               = 32M
max-heap-table-size            = 32M
query-cache-type               = 0
query-cache-size               = 0
max-connections                = 500
thread-cache-size            = 50
open-files-limit               = 65535
table-definition-cache         = 1024
table-open-cache               = 2048

# INNODB #
innodb-flush-method            = O_DIRECT
innodb-log-files-in-group      = 2
innodb-log-file-size         = 256M
innodb-flush-log-at-trx-commit = 1
innodb-file-per-table          = 1
innodb-buffer-pool-size      = 4G

# LOGGING #
log-error                      = /home/mysql/mysql-error.log
log-queries-not-using-indexes= 1
#slow-query-log               = 1
#slow-query-log-file            = /home/mysql/mysql-slow.log

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0</code></pre></div><div><br></div><div><br></div><div><h2 style="box-sizing: border-box; margin-bottom: 16px; color: rgb(64, 64, 64); text-rendering: optimizelegibility; font-size: 24px; font-family: -apple-system, BlinkMacSystemFont, &quot;Apple Color Emoji&quot;, &quot;Segoe UI Emoji&quot;, &quot;Segoe UI Symbol&quot;, &quot;Segoe UI&quot;, &quot;PingFang SC&quot;, &quot;Hiragino Sans GB&quot;, &quot;Microsoft YaHei&quot;, &quot;Helvetica Neue&quot;, Helvetica, Arial, sans-serif;">b. testB的数据库配置文件</h2></div><div><br></div><div><pre class="line-numberslanguage-xml" style="box-sizing: border-box; font-size: 12px; font-family: Consolas, Monaco, &quot;Andale Mono&quot;, &quot;Ubuntu Mono&quot;, monospace; margin-bottom: 20px; overflow: scroll auto; overflow-wrap: normal; word-break: break-all; overscroll-behavior-x: contain; border-radius: 4px; z-index: 0; padding: 1em 1em 1em 3.8em; line-height: 1.5; color: rgb(204, 204, 204); background: rgb(45, 45, 45); tab-size: 4; hyphens: none; position: relative; counter-reset: linenumber 0;"><code class="xmllanguage-xml" style="box-sizing: border-box; font-size: 1em; font-family: Consolas, Monaco, &quot;Andale Mono&quot;, &quot;Ubuntu Mono&quot;, monospace; border: none; vertical-align: unset; white-space: inherit; color: inherit; background: none transparent; word-spacing: normal; word-break: normal; overflow-wrap: normal; line-height: 1.5; tab-size: 4; hyphens: none; position: relative;"># For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html

# Configuration name server



# CLIENT #
port                           = 3306
default-character-set          = utf8


socket                         = /home/mysql/mysql.sock


#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M

# GENERAL #
user                           = mysql
default-storage-engine         = InnoDB
socket                         = /home/mysql/mysql.sock
pid-file                     = /home/mysql/mysql.pid
character-set-server         = utf8
lower-case-table-names         = 1
performance_schema             = ON
sql_mode                     = ''
server-id                      = 2

# MyISAM #
key-buffer-size                = 32M
myisam_recover_options         = FORCE,BACKUP

# SAFETY #
max-allowed-packet             = 16M
max-connect-errors             = 1000000

# DATA STORAGE #
datadir                        = /home/mysql/

# BINARY LOGGING #
log-bin                        = /home/mysql/mysql-bin
binlog_format                  = mixed
expire-logs-days               = 14
sync-binlog                  = 1
log-bin-trust-function-creators= 1

# MASTER DB #
#binlog-do-db                   = mdm,androidpnserver
binlog-ignore-db               = mysql,information_schema,performance_schema
auto-increment-increment       = 2
auto-increment-offset          = 2

# SLAVE DB #
#replicate-do-db                = mdm,androidpnserver
replicate-ignore-db            = mysql,information_schema,performance_schema
relay_log                      = /home/mysql/relay-bin
log-slave-updates            = ON

# CACHES AND LIMITS #
tmp-table-size               = 32M
max-heap-table-size            = 32M
query-cache-type               = 0
query-cache-size               = 0
max-connections                = 500
thread-cache-size            = 50
open-files-limit               = 65535
table-definition-cache         = 1024
table-open-cache               = 2048

# INNODB #
innodb-flush-method            = O_DIRECT
innodb-log-files-in-group      = 2
innodb-log-file-size         = 256M
innodb-flush-log-at-trx-commit = 1
innodb-file-per-table          = 1
innodb-buffer-pool-size      = 4G

# LOGGING #
log-error                      = /home/mysql/mysql-error.log
log-queries-not-using-indexes= 1
#slow-query-log               = 1
#slow-query-log-file            = /home/mysql/mysql-slow.log

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0</code></pre></div><div><br></div><div><h1 style="box-sizing: border-box; margin-bottom: 16px; color: rgb(64, 64, 64); text-rendering: optimizelegibility; font-size: 26px; font-family: -apple-system, BlinkMacSystemFont, &quot;Apple Color Emoji&quot;, &quot;Segoe UI Emoji&quot;, &quot;Segoe UI Symbol&quot;, &quot;Segoe UI&quot;, &quot;PingFang SC&quot;, &quot;Hiragino Sans GB&quot;, &quot;Microsoft YaHei&quot;, &quot;Helvetica Neue&quot;, Helvetica, Arial, sans-serif;">3. 分别重启MySQL服务</h1></div><div><pre class="line-numberslanguage-xml" style="box-sizing: border-box; font-size: 12px; font-family: Consolas, Monaco, &quot;Andale Mono&quot;, &quot;Ubuntu Mono&quot;, monospace; margin-bottom: 20px; overflow: scroll auto; overflow-wrap: normal; word-break: break-all; overscroll-behavior-x: contain; border-radius: 4px; z-index: 0; padding: 1em 1em 1em 3.8em; line-height: 1.5; color: rgb(204, 204, 204); background: rgb(45, 45, 45); tab-size: 4; hyphens: none; position: relative; counter-reset: linenumber 0;"><code class="xmllanguage-xml" style="box-sizing: border-box; font-size: 1em; font-family: Consolas, Monaco, &quot;Andale Mono&quot;, &quot;Ubuntu Mono&quot;, monospace; border: none; vertical-align: unset; white-space: inherit; color: inherit; background: none transparent; word-spacing: normal; word-break: normal; overflow-wrap: normal; line-height: 1.5; tab-size: 4; hyphens: none; position: relative;">service mysqld restart</code></pre></div><div><h1 style="box-sizing: border-box; margin-bottom: 16px; color: rgb(64, 64, 64); text-rendering: optimizelegibility; font-size: 26px; font-family: -apple-system, BlinkMacSystemFont, &quot;Apple Color Emoji&quot;, &quot;Segoe UI Emoji&quot;, &quot;Segoe UI Symbol&quot;, &quot;Segoe UI&quot;, &quot;PingFang SC&quot;, &quot;Hiragino Sans GB&quot;, &quot;Microsoft YaHei&quot;, &quot;Helvetica Neue&quot;, Helvetica, Arial, sans-serif;">4. 查看主服务器状态</h1><h2 style="box-sizing: border-box; margin-bottom: 16px; color: rgb(64, 64, 64); text-rendering: optimizelegibility; font-size: 24px; font-family: -apple-system, BlinkMacSystemFont, &quot;Apple Color Emoji&quot;, &quot;Segoe UI Emoji&quot;, &quot;Segoe UI Symbol&quot;, &quot;Segoe UI&quot;, &quot;PingFang SC&quot;, &quot;Hiragino Sans GB&quot;, &quot;Microsoft YaHei&quot;, &quot;Helvetica Neue&quot;, Helvetica, Arial, sans-serif;">a. testA服务器</h2></div><div><pre class="line-numberslanguage-xml" style="box-sizing: border-box; font-size: 12px; font-family: Consolas, Monaco, &quot;Andale Mono&quot;, &quot;Ubuntu Mono&quot;, monospace; margin-bottom: 20px; overflow: scroll auto; overflow-wrap: normal; word-break: break-all; overscroll-behavior-x: contain; border-radius: 4px; z-index: 0; padding: 1em 1em 1em 3.8em; line-height: 1.5; color: rgb(204, 204, 204); background: rgb(45, 45, 45); tab-size: 4; hyphens: none; position: relative; counter-reset: linenumber 0;"><code class="xmllanguage-xml" style="box-sizing: border-box; font-size: 1em; font-family: Consolas, Monaco, &quot;Andale Mono&quot;, &quot;Ubuntu Mono&quot;, monospace; border: none; vertical-align: unset; white-space: inherit; color: inherit; background: none transparent; word-spacing: normal; word-break: normal; overflow-wrap: normal; line-height: 1.5; tab-size: 4; hyphens: none; position: relative;">mysql&gt; flush tables with read lock; #防止进入新的数据
Query OK, 0 rows affected (0.00 sec)
mysql&gt; show master status\G;
*************************** 1. row ***************************
             File: mysql-bin.000010
         Position: 64353552
   Binlog_Do_DB:
Binlog_Ignore_DB: mysql,information_schema,performance_schema
Executed_Gtid_Set:
1 row in set (0.00 sec)</code></pre></div><div><h2 style="box-sizing: border-box; margin-bottom: 16px; color: rgb(64, 64, 64); text-rendering: optimizelegibility; font-size: 24px; font-family: -apple-system, BlinkMacSystemFont, &quot;Apple Color Emoji&quot;, &quot;Segoe UI Emoji&quot;, &quot;Segoe UI Symbol&quot;, &quot;Segoe UI&quot;, &quot;PingFang SC&quot;, &quot;Hiragino Sans GB&quot;, &quot;Microsoft YaHei&quot;, &quot;Helvetica Neue&quot;, Helvetica, Arial, sans-serif;">b. testB服务器</h2></div><div><pre class="line-numberslanguage-xml" style="box-sizing: border-box; font-size: 12px; font-family: Consolas, Monaco, &quot;Andale Mono&quot;, &quot;Ubuntu Mono&quot;, monospace; margin-bottom: 20px; overflow: scroll auto; overflow-wrap: normal; word-break: break-all; overscroll-behavior-x: contain; border-radius: 4px; z-index: 0; padding: 1em 1em 1em 3.8em; line-height: 1.5; color: rgb(204, 204, 204); background: rgb(45, 45, 45); tab-size: 4; hyphens: none; position: relative; counter-reset: linenumber 0;"><code class="xmllanguage-xml" style="box-sizing: border-box; font-size: 1em; font-family: Consolas, Monaco, &quot;Andale Mono&quot;, &quot;Ubuntu Mono&quot;, monospace; border: none; vertical-align: unset; white-space: inherit; color: inherit; background: none transparent; word-spacing: normal; word-break: normal; overflow-wrap: normal; line-height: 1.5; tab-size: 4; hyphens: none; position: relative;">mysql&gt; flush tables with read lock; #防止进入新的数据
Query OK, 0 rows affected (0.00 sec)
mysql&gt; show master status\G;
*************************** 1. row ***************************
             File: mysql-bin.000008
         Position: 64355767
   Binlog_Do_DB:
Binlog_Ignore_DB: mysql,information_schema,performance_schema
Executed_Gtid_Set:
1 row in set (0.00 sec)</code></pre></div><div><br></div><div><h1 style="box-sizing: border-box; margin-bottom: 16px; color: rgb(64, 64, 64); text-rendering: optimizelegibility; font-size: 26px; font-family: -apple-system, BlinkMacSystemFont, &quot;Apple Color Emoji&quot;, &quot;Segoe UI Emoji&quot;, &quot;Segoe UI Symbol&quot;, &quot;Segoe UI&quot;, &quot;PingFang SC&quot;, &quot;Hiragino Sans GB&quot;, &quot;Microsoft YaHei&quot;, &quot;Helvetica Neue&quot;, Helvetica, Arial, sans-serif;">5. 使用change master语句指定同步位置</h1><h2 style="box-sizing: border-box; margin-bottom: 16px; color: rgb(64, 64, 64); text-rendering: optimizelegibility; font-size: 24px; font-family: -apple-system, BlinkMacSystemFont, &quot;Apple Color Emoji&quot;, &quot;Segoe UI Emoji&quot;, &quot;Segoe UI Symbol&quot;, &quot;Segoe UI&quot;, &quot;PingFang SC&quot;, &quot;Hiragino Sans GB&quot;, &quot;Microsoft YaHei&quot;, &quot;Helvetica Neue&quot;, Helvetica, Arial, sans-serif;">a. testA服务器</h2></div><div><pre class="line-numberslanguage-xml" style="box-sizing: border-box; font-size: 12px; font-family: Consolas, Monaco, &quot;Andale Mono&quot;, &quot;Ubuntu Mono&quot;, monospace; margin-bottom: 20px; overflow: scroll auto; overflow-wrap: normal; word-break: break-all; overscroll-behavior-x: contain; border-radius: 4px; z-index: 0; padding: 1em 1em 1em 3.8em; line-height: 1.5; color: rgb(204, 204, 204); background: rgb(45, 45, 45); tab-size: 4; hyphens: none; position: relative; counter-reset: linenumber 0;"><code class="xmllanguage-xml" style="box-sizing: border-box; font-size: 1em; font-family: Consolas, Monaco, &quot;Andale Mono&quot;, &quot;Ubuntu Mono&quot;, monospace; border: none; vertical-align: unset; white-space: inherit; color: inherit; background: none transparent; word-spacing: normal; word-break: normal; overflow-wrap: normal; line-height: 1.5; tab-size: 4; hyphens: none; position: relative;">CHANGE MASTER TO MASTER_HOST='192.168.1.237',MASTER_USER='sync',MASTER_PASSWORD='<span class="token tag" style="box-sizing: border-box; color: rgb(226, 119, 122);"><span class="token tag" style="box-sizing: border-box;"><span class="token punctuation" style="box-sizing: border-box; color: rgb(204, 204, 204);">&lt;</span>YourPassword</span><span class="token punctuation" style="box-sizing: border-box; color: rgb(204, 204, 204);">&gt;</span></span>',MASTER_LOG_FILE='mysql-bin.000008',MASTER_LOG_POS=64355767;</code></pre></div><div><h2 style="box-sizing: border-box; margin-bottom: 16px; color: rgb(64, 64, 64); text-rendering: optimizelegibility; font-size: 24px; font-family: -apple-system, BlinkMacSystemFont, &quot;Apple Color Emoji&quot;, &quot;Segoe UI Emoji&quot;, &quot;Segoe UI Symbol&quot;, &quot;Segoe UI&quot;, &quot;PingFang SC&quot;, &quot;Hiragino Sans GB&quot;, &quot;Microsoft YaHei&quot;, &quot;Helvetica Neue&quot;, Helvetica, Arial, sans-serif;">b. testB服务器</h2></div><div><pre class="line-numberslanguage-xml" style="box-sizing: border-box; font-size: 12px; font-family: Consolas, Monaco, &quot;Andale Mono&quot;, &quot;Ubuntu Mono&quot;, monospace; margin-bottom: 20px; overflow: scroll auto; overflow-wrap: normal; word-break: break-all; overscroll-behavior-x: contain; border-radius: 4px; z-index: 0; padding: 1em 1em 1em 3.8em; line-height: 1.5; color: rgb(204, 204, 204); background: rgb(45, 45, 45); tab-size: 4; hyphens: none; position: relative; counter-reset: linenumber 0;"><code class="xmllanguage-xml" style="box-sizing: border-box; font-size: 1em; font-family: Consolas, Monaco, &quot;Andale Mono&quot;, &quot;Ubuntu Mono&quot;, monospace; border: none; vertical-align: unset; white-space: inherit; color: inherit; background: none transparent; word-spacing: normal; word-break: normal; overflow-wrap: normal; line-height: 1.5; tab-size: 4; hyphens: none; position: relative;">CHANGE MASTER TO MASTER_HOST='192.168.1.235',MASTER_USER='sync',MASTER_PASSWORD='<span class="token tag" style="box-sizing: border-box; color: rgb(226, 119, 122);"><span class="token tag" style="box-sizing: border-box;"><span class="token punctuation" style="box-sizing: border-box; color: rgb(204, 204, 204);">&lt;</span>YourPassword</span><span class="token punctuation" style="box-sizing: border-box; color: rgb(204, 204, 204);">&gt;</span></span>',MASTER_LOG_FILE='mysql-bin.000010',MASTER_LOG_POS=64353552;</code></pre></div><div><h1 style="box-sizing: border-box; margin-bottom: 16px; color: rgb(64, 64, 64); text-rendering: optimizelegibility; font-size: 26px; font-family: -apple-system, BlinkMacSystemFont, &quot;Apple Color Emoji&quot;, &quot;Segoe UI Emoji&quot;, &quot;Segoe UI Symbol&quot;, &quot;Segoe UI&quot;, &quot;PingFang SC&quot;, &quot;Hiragino Sans GB&quot;, &quot;Microsoft YaHei&quot;, &quot;Helvetica Neue&quot;, Helvetica, Arial, sans-serif;">6. 分别启动从服务器线程</h1></div><div><pre class="line-numberslanguage-xml" style="box-sizing: border-box; font-size: 12px; font-family: Consolas, Monaco, &quot;Andale Mono&quot;, &quot;Ubuntu Mono&quot;, monospace; margin-bottom: 20px; overflow: scroll auto; overflow-wrap: normal; word-break: break-all; overscroll-behavior-x: contain; border-radius: 4px; z-index: 0; padding: 1em 1em 1em 3.8em; line-height: 1.5; color: rgb(204, 204, 204); background: rgb(45, 45, 45); tab-size: 4; hyphens: none; position: relative; counter-reset: linenumber 0;"><code class="xmllanguage-xml" style="box-sizing: border-box; font-size: 1em; font-family: Consolas, Monaco, &quot;Andale Mono&quot;, &quot;Ubuntu Mono&quot;, monospace; border: none; vertical-align: unset; white-space: inherit; color: inherit; background: none transparent; word-spacing: normal; word-break: normal; overflow-wrap: normal; line-height: 1.5; tab-size: 4; hyphens: none; position: relative;">mysql&gt; start slave;
Query OK, 0 rows affected (0.00 sec)</code></pre></div><div><br></div><div><br></div><div><h1 style="box-sizing: border-box; margin-bottom: 16px; color: rgb(64, 64, 64); text-rendering: optimizelegibility; font-size: 26px; font-family: -apple-system, BlinkMacSystemFont, &quot;Apple Color Emoji&quot;, &quot;Segoe UI Emoji&quot;, &quot;Segoe UI Symbol&quot;, &quot;Segoe UI&quot;, &quot;PingFang SC&quot;, &quot;Hiragino Sans GB&quot;, &quot;Microsoft YaHei&quot;, &quot;Helvetica Neue&quot;, Helvetica, Arial, sans-serif;">7. 查看从服务器状态</h1></div><div><h2 style="box-sizing: border-box; margin-bottom: 16px; color: rgb(64, 64, 64); text-rendering: optimizelegibility; font-size: 24px; font-family: -apple-system, BlinkMacSystemFont, &quot;Apple Color Emoji&quot;, &quot;Segoe UI Emoji&quot;, &quot;Segoe UI Symbol&quot;, &quot;Segoe UI&quot;, &quot;PingFang SC&quot;, &quot;Hiragino Sans GB&quot;, &quot;Microsoft YaHei&quot;, &quot;Helvetica Neue&quot;, Helvetica, Arial, sans-serif;">a. testA服务器</h2></div><div><pre class="line-numberslanguage-xml" style="box-sizing: border-box; font-size: 12px; font-family: Consolas, Monaco, &quot;Andale Mono&quot;, &quot;Ubuntu Mono&quot;, monospace; margin-bottom: 20px; overflow: scroll auto; overflow-wrap: normal; word-break: break-all; overscroll-behavior-x: contain; border-radius: 4px; z-index: 0; padding: 1em 1em 1em 3.8em; line-height: 1.5; color: rgb(204, 204, 204); background: rgb(45, 45, 45); tab-size: 4; hyphens: none; position: relative; counter-reset: linenumber 0;"><code class="xmllanguage-xml" style="box-sizing: border-box; font-size: 1em; font-family: Consolas, Monaco, &quot;Andale Mono&quot;, &quot;Ubuntu Mono&quot;, monospace; border: none; vertical-align: unset; white-space: inherit; color: inherit; background: none transparent; word-spacing: normal; word-break: normal; overflow-wrap: normal; line-height: 1.5; tab-size: 4; hyphens: none; position: relative;">mysql&gt; show slave status\G;
*************************** 1. row ***************************
主要关注以下 2 个参数:
...
...
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
...
... </code></pre></div><div><h2 style="box-sizing: border-box; margin-bottom: 16px; color: rgb(64, 64, 64); text-rendering: optimizelegibility; font-size: 24px; font-family: -apple-system, BlinkMacSystemFont, &quot;Apple Color Emoji&quot;, &quot;Segoe UI Emoji&quot;, &quot;Segoe UI Symbol&quot;, &quot;Segoe UI&quot;, &quot;PingFang SC&quot;, &quot;Hiragino Sans GB&quot;, &quot;Microsoft YaHei&quot;, &quot;Helvetica Neue&quot;, Helvetica, Arial, sans-serif;">b. testB服务器</h2></div><div><br></div><div><pre class="line-numberslanguage-xml" style="box-sizing: border-box; font-size: 12px; font-family: Consolas, Monaco, &quot;Andale Mono&quot;, &quot;Ubuntu Mono&quot;, monospace; margin-bottom: 20px; overflow: scroll auto; overflow-wrap: normal; word-break: break-all; overscroll-behavior-x: contain; border-radius: 4px; z-index: 0; padding: 1em 1em 1em 3.8em; line-height: 1.5; color: rgb(204, 204, 204); background: rgb(45, 45, 45); tab-size: 4; hyphens: none; position: relative; counter-reset: linenumber 0;"><code class="xmllanguage-xml" style="box-sizing: border-box; font-size: 1em; font-family: Consolas, Monaco, &quot;Andale Mono&quot;, &quot;Ubuntu Mono&quot;, monospace; border: none; vertical-align: unset; white-space: inherit; color: inherit; background: none transparent; word-spacing: normal; word-break: normal; overflow-wrap: normal; line-height: 1.5; tab-size: 4; hyphens: none; position: relative;">mysql&gt; show slave status\G;
*************************** 1. row ***************************
主要关注以下 2 个参数:
...
...
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
...
... </code></pre></div><div><br></div><div><h1 style="box-sizing: border-box; margin-bottom: 16px; color: rgb(64, 64, 64); text-rendering: optimizelegibility; font-size: 26px; font-family: -apple-system, BlinkMacSystemFont, &quot;Apple Color Emoji&quot;, &quot;Segoe UI Emoji&quot;, &quot;Segoe UI Symbol&quot;, &quot;Segoe UI&quot;, &quot;PingFang SC&quot;, &quot;Hiragino Sans GB&quot;, &quot;Microsoft YaHei&quot;, &quot;Helvetica Neue&quot;, Helvetica, Arial, sans-serif;">8. 验证测试</h1><p style="box-sizing: border-box; margin-bottom: 20px; word-break: break-word; color: rgb(64, 64, 64); font-family: -apple-system, BlinkMacSystemFont, &quot;Apple Color Emoji&quot;, &quot;Segoe UI Emoji&quot;, &quot;Segoe UI Symbol&quot;, &quot;Segoe UI&quot;, &quot;PingFang SC&quot;, &quot;Hiragino Sans GB&quot;, &quot;Microsoft YaHei&quot;, &quot;Helvetica Neue&quot;, Helvetica, Arial, sans-serif; font-size: 16px;">略</p></div><div><br></div><div><br></div><div><br></div><div><br></div>
页: [1]
查看完整版本: MySQL配置主主同步(master-master)