MySQL 5.7 的多源复制

你的名字 2022-08-21 04:49 125阅读 0赞

<table style="word-wrap:break-word; empty-cells:show; border-collapse:collapse; table-layout:fixed; width:1119px"> 
 <tbody style="word-wrap:break-word"> 
 <tr style="word-wrap:break-word"> 
 <td style="word-wrap:break-word">什么是多源复制? &nbsp; &nbsp;&nbsp; &nbsp;首先，我们需要清楚&nbsp;multi-master&nbsp;与multi-source&nbsp;复制不是一样的. Multi-Master 复制通常是环形复制， 你可以在任意主机上将数据复制给其他主机。 <img src="https://img-blog.csdn.net/20160316140447551?watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQv/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/Center" alt=""><img width="285" alt="" style="word-wrap:break-word">&nbsp; &nbsp; &nbsp;&nbsp;&nbsp; &nbsp; &nbsp;&nbsp; &nbsp; Multi-source 是不同的. MySQL在这个新版本中修复了一个复制限制 , 这限制是一个从站只能有一个主站.&nbsp; 这是一个在我们设计复制环境中的限制因素，也有一些极客使它正常工作了。但是现在有一个官方的解决办 法了。所以。简单的说， Multi-Source 意味着一个从站能有一个以上主站. 现在,&nbsp; 像下图一样的复制环境是可能的: <img src="https://img-blog.csdn.net/20160316140510520?watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQv/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/Center" alt=""> <img width="300" alt="" style="word-wrap:break-word">&nbsp; &nbsp; &nbsp;&nbsp;&nbsp;这将帮助我们创建一些复制的层次结构，这在过去是不可能的。 举个例子,你可以 将一个从站放在你的办公室 里。在办公室里从所有主站中复制数据传播到世界各地。 它是怎么工作的呢? &nbsp; &nbsp;&nbsp;现在我们有了通信渠道的概念，每一个通信渠道都是一个从服务器从主服务器获得2进制日志的链接。 这意味着每个通信渠道都得有一个IO_THREAD .我们需要运行不同的 “CHANGE MASTER” 命令, 对于 每一个主服务器。我们需要用到 “FOR CHANNEL”这个参数来提供通信链接的名字。 CHANGE MASTER MASTER_HOST='something', MASTER_USER=... FOR&nbsp;CHANNEL="name_of_channel"; &nbsp; &nbsp; 很容易. 有一个单一的先决条件。从服务器需要在mysql5.6上配置安全功能，这意味着信息通常包含在主服 务器上。信息或日志应该在一个表中，让我们开始配置吧 举个例子: 
 <div style="font-size:14px; word-wrap:break-word"> 
 &nbsp; &nbsp;&nbsp;&nbsp;我们需要有一个从服务器和两个主服务器的沙箱环境. 我不会细节的说明怎样配置server_id, binary logs 和 
 </div> 
 <div style="font-size:14px; word-wrap:break-word"> 
 &nbsp;replication users.我假设已经配置好了。 如果你不知道怎么做。你可以看这个<a href="https://www.digitalocean.com/community/articles/how-to-set-up-mysql-master-master-replication" style="word-wrap:break-word; color:rgb(51,102,153)" rel="nofollow">链接</a> 
 </div> 
 <div style="font-size:14px; word-wrap:break-word"> 
 首先,我们在从服务器上实现事故安全功能。 
 </div> 
 <div style="font-size:14px; word-wrap:break-word"> 
 
 </div> master_info_repository=TABLE; relay_log_info_repository=TABLE; 让后重启从服务器。我们可以开始创建通信渠道，用 “master1″ 和 “master2″这两个名字: change master to[size=1em]&nbsp;master_host="127.0.0.1", master_port=12047, master_user="msandbox",master_password="msandbox"[size=1em]&nbsp;for[size=1em]channel="master1"; slave &gt; change master to master_host="127.0.0.1", master_port=12048, master_user="msandbox",master_password="msandbox" for channel="master2"; 按照所设定的通信名字启动从服务器。slave &gt; start slave for channel="master1"; slave &gt; start slave for channel="master2"; 现在我们检查一下从服务器状态： slave &gt; show slave status\G Empty set (0.00 sec) 啊。是空的。我们需要写上通信名字。重新检查下从服务器状态： slave &gt; SHOW SLAVE STATUS FOR CHANNEL="master1"\G *************************** 1. row *************************** &nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;Slave_IO_State: Waiting for master to send event &nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;Master_Host: 127.0.0.1 &nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;Master_User: msandbox &nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;Master_Port: 12047 &nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp; Connect_Retry: 60 &nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp;&nbsp;Master_Log_File: mysql-bin.000002 &nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp; Read_Master_Log_Pos: 232 &nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;Relay_Log_File: squeeze-relay-bin-master1.000003 &nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp; Relay_Log_Pos: 395 &nbsp; &nbsp;&nbsp; &nbsp;&nbsp;&nbsp;Relay_Master_Log_File: mysql-bin.000002 &nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp; Slave_IO_Running: Yes &nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;Slave_SQL_Running: Yes [...] 我们也可以检查正在运行的&nbsp;&nbsp;IO_THREAD&nbsp;和&nbsp;SQL_THREADS&nbsp;: slave &gt; SHOW PROCESSLIST; +----+-------------+-----------------------------------------------------------------------------+ | Id | User&nbsp; &nbsp;&nbsp; &nbsp;&nbsp;&nbsp;| State&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp;&nbsp;| +----+-------------+-----------------------------------------------------------------------------+ |&nbsp;&nbsp;2 | system user | Waiting for master to send event&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp;&nbsp;| |&nbsp;&nbsp;3 | system user | Slave has read all relay log; waiting for the slave I/O thread to update it | |&nbsp;&nbsp;4 | system user | Waiting for master to send event&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp;&nbsp;| |&nbsp;&nbsp;5 | system user | Slave has read all relay log; waiting for the slave I/O thread to update it | +----+-------------+-----------------------------------------------------------------------------+ 测试看一下: master1 &gt; create database master1; master2 &gt; create database master2; slave &gt; show databases like 'master%'; +--------------------+ | Database (master%) | +--------------------+ | master1&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;| | master2&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;| +--------------------+ 正常工作了。看起来很简单! 
 <div style="font-size:14px; word-wrap:break-word"> 
 &nbsp; &nbsp;&nbsp; &nbsp; 
 </div> 
 <div style="font-size:14px; word-wrap:break-word">
 结论: 
 </div> 
 <div style="font-size:14px; word-wrap:break-word"> 
 &nbsp; &nbsp;&nbsp; &nbsp;这个新的multi-source功能使在过去需要一些复杂操作创建的复制环境变得简单了。当然，你应用程序可以考虑这个新 
 </div> 
 <div style="font-size:14px; word-wrap:break-word"> 
 的特性设计和开发，使用&nbsp;&nbsp;multi-master, multi-source都需要特别注意，不要把你的数据都弄乱了。 
 </div> 
 <div style="font-size:14px; word-wrap:break-word"> 
 在每一个新的版本中，mysql的复制功能给我们更多的配置，性能和设计的可能性。所有这些新功能都可以结合起来。在你 
 </div> 
 <div style="font-size:14px; word-wrap:break-word"> 
 新（老）的功能中增加复制功能，你的复制环境会更加出色。举个例子：你可以配置&nbsp;<a href="http://www.mysqlperformanceblog.com/2013/02/08/how-to-createrestore-a-slave-using-gtid-replication-in-mysql-5-6/" style="word-wrap:break-word; color:rgb(51,102,153)" rel="nofollow">GTID</a>&nbsp;可以用&nbsp;<a href="http://scriptingmysql.wordpress.com/2013/01/07/mysql-replication-multi-threaded-slaves-parallel-event-execution/" style="word-wrap:break-word; color:rgb(51,102,153)" rel="nofollow">multi-threaded slave per schema</a> 
 </div> 
 <div style="font-size:14px; word-wrap:break-word"> 
 或&nbsp;<a href="http://geek.rohitkalhans.com/2013/09/enhancedMTS-deepdive.html" style="word-wrap:break-word; color:rgb(51,102,153)" rel="nofollow">intra-database</a>. 
 </div> </td> 
 </tr> 
 </tbody> 
</table>