对于使用中的已有了一些数据库的 MySQL, 如何改造成双主呢? 这里写一篇文章, 详细记录操作步骤. 听取各位的意见. 这里假设有两台服务器: A 和 B. 要让他们互为主, 但实际使用时, 不同的服务器只服务不同的 db, 避免出现数据冲突. 1. 修改 nginx 配置, 挂维护
这里假设有两台服务器: A 和 B. 要让他们互为主, 但实际使用时, 不同的服务器只服务不同的 db, 避免出现数据冲突.
这一步很重要. 你需要告诉你的用户, 你的网站在维护, 同时要确保不会再出现数据库写操作.
另外, 还要停止 crontab 任务.
Server A:
<span>mysqldump </span><span>-</span><span>uroot </span><span>-</span><span>p </span><span>--</span><span>all</span><span>-</span><span>databases </span><span>></span><span> a_dbdump_all</span><span>.</span><span>sql gzip a_dbdump_all</span><span>.</span><span>sql</span>
Server B:
<span>mysqldump </span><span>-</span><span>uroot </span><span>-</span><span>p </span><span>--</span><span>all</span><span>-</span><span>databases </span><span>></span><span> b_dbdump_all</span><span>.</span><span>sql gzip b_dbdump_all</span><span>.</span><span>sql</span>
把线上数据库备份到本地
<span>scp test@server_a</span><span>:~/</span><span>a_dbdump_all</span><span>.</span><span>sql</span><span>.</span><span>gz </span><span>.</span><span> scp test@server_b</span><span>:~/</span><span>b_dbdump_all</span><span>.</span><span>sql</span><span>.</span><span>gz </span><span>.</span>
Server A:
<span>GRANT REPLICATION SLAVE ON </span><span>*.*</span><span> TO </span><span>'userx'</span><span>@</span><span>'server_b'</span><span> IDENTIFIED BY </span><span>'xxxxx'</span><span>;</span>
Server B:
<span>GRANT REPLICATION SLAVE ON </span><span>*.*</span><span> TO </span><span>'userx'</span><span>@</span><span>'server_a'</span><span> IDENTIFIED BY </span><span>'xxxxx'</span><span>;</span>
这一步是手工将两台服务器数据库(基准数据)同步.
Server A:
<span>mysqldump </span><span>-</span><span>uroot </span><span>-</span><span>p </span><span>--</span><span>databases db1 </span><span>></span><span> a_dbdump</span><span>.</span><span>sql scp a_dbdump</span><span>.</span><span>sql test@server_b</span><span>:~</span>
Server B:
<span>mysqldump </span><span>-</span><span>uroot </span><span>-</span><span>p </span><span>--</span><span>databases db2 </span><span>></span><span> b_dbdump</span><span>.</span><span>sql scp b_dbdump</span><span>.</span><span>sql test@server_a</span><span>:~</span>
Server A:
<span>source b_dbdump</span><span>.</span><span>sql</span>
Server B:
<span>source a_dbdump</span><span>.</span><span>sql</span>
Server A:
<span>[</span><span>mysqld</span><span>]</span><span> server</span><span>-</span><span>id</span><span>=</span><span>1</span><span> log</span><span>-</span><span>bin</span><span>=</span><span>mysql</span><span>-</span><span>bin log</span><span>-</span><span>slave</span><span>-</span><span>updates binlog</span><span>-</span><span>ignore</span><span>-</span><span>db</span><span>=</span><span>mysql binlog</span><span>-</span><span>ignore</span><span>-</span><span>db</span><span>=</span><span>test binlog</span><span>-</span><span>ignore</span><span>-</span><span>db</span><span>=</span><span>information_schema binlog</span><span>-</span><span>ignore</span><span>-</span><span>db</span><span>=</span><span>performance_schema replicate</span><span>-</span><span>ignore</span><span>-</span><span>db</span><span>=</span><span>mysql replicate</span><span>-</span><span>ignore</span><span>-</span><span>db</span><span>=</span><span>test replicate</span><span>-</span><span>ignore</span><span>-</span><span>db</span><span>=</span><span>information_schema replicate</span><span>-</span><span>ignore</span><span>-</span><span>db</span><span>=</span><span>performance_schema master</span><span>-</span><span>connect</span><span>-</span><span>retry</span><span>=</span><span>10</span><span>Server</span><span> B</span><span>:</span><span>[</span><span>mysqld</span><span>]</span><span> server</span><span>-</span><span>id</span><span>=</span><span>2</span><span> log</span><span>-</span><span>bin</span><span>=</span><span>mysql</span><span>-</span><span>bin log</span><span>-</span><span>slave</span><span>-</span><span>updates binlog</span><span>-</span><span>ignore</span><span>-</span><span>db</span><span>=</span><span>mysql binlog</span><span>-</span><span>ignore</span><span>-</span><span>db</span><span>=</span><span>test binlog</span><span>-</span><span>ignore</span><span>-</span><span>db</span><span>=</span><span>information_schema binlog</span><span>-</span><span>ignore</span><span>-</span><span>db</span><span>=</span><span>performance_schema replicate</span><span>-</span><span>ignore</span><span>-</span><span>db</span><span>=</span><span>mysql replicate</span><span>-</span><span>ignore</span><span>-</span><span>db</span><span>=</span><span>test replicate</span><span>-</span><span>ignore</span><span>-</span><span>db</span><span>=</span><span>information_schema replicate</span><span>-</span><span>ignore</span><span>-</span><span>db</span><span>=</span><span>performance_schema master</span><span>-</span><span>connect</span><span>-</span><span>retry</span><span>=</span><span>10</span>
注意, 他们的 server-id 不相同.
重启 MySQL.
Server A:
<span>mysql </span><span>-</span><span>hserver_b </span><span>-</span><span>uuserx </span><span>-</span><span>pxxxxx</span>
Server B:
<span>mysql </span><span>-</span><span>hserver_a </span><span>-</span><span>uuserx </span><span>-</span><span>pxxxxx</span>
Server A:
<span>FLUSH TABLES</span><span>;</span><span> show master status</span><span>;</span>
会显示这样的信息.
<span>***************************</span><span>1.</span><span> row </span><span>***************************</span><span>File</span><span>:</span><span> mysql</span><span>-</span><span>bin</span><span>.</span><span>000001</span><span>Position</span><span>:</span><span>106</span><span>Binlog_Do_DB</span><span>:</span><span>Binlog_Ignore_DB</span><span>:</span><span> mysql</span><span>,</span><span>test</span><span>,</span><span>information_schema</span><span>,</span><span>performance_schema </span><span>1</span><span> row </span><span>in</span><span>set</span><span>(</span><span>0.00</span><span> sec</span><span>)</span>
记录 File 和 Position, 这时 binlog 的当前位置, 因为 Slave 要从这个位置开始同步数据.
Server B:
<span>CHANGE MASTER TO MASTER_HOST</span><span>=</span><span>'server_a'</span><span>;</span><span> CHANGE MASTER TO MASTER_USER</span><span>=</span><span>'userx'</span><span>;</span><span> CHANGE MASTER TO MASTER_PASSWORD</span><span>=</span><span>'xxxxx'</span><span>;</span><span> CHANGE MASTER TO MASTER_LOG_FILE</span><span>=</span><span>'mysql-bin.000001'</span><span>,</span><span> MASTER_LOG_POS</span><span>=</span><span>106</span><span>;</span><span> start slave</span><span>;</span><span> show slave status\G</span>
同样的, 在 Server A 上也启动同步 Slave 进程.
因为我们确保了没有数据库写操作, 所以不需要 FLUSH TABLES WITH READ LOCK;.
在不同的数据库上执行一些更新数据的操作, 看看数据是否同步过去了.
恢复 crontab 任务
修改 nginx, 撤下维护网页, 恢复服务
如果你有什么问题, 或者发现里这些步骤的不足, 欢迎评论!
