Mysql Binlog 主从模式配置 与 验证

<p>1)准备两台Mysql服务,并启动服务</p><p> 127.0.0.1:3306 主机</p><p> 127.0.0.1:3307 从机</p><p> 主从配置前需要确认 两机实例间 库、表、数据一致,不然会导致无法同步。</p><p>2)主机 my.ini / my.cnf 文件配置</p><p>   #二进制文件,主机环境必开</p><p>   log-bin=mysql-bin </p><p>   #主机服务ID ,必须唯一</p><p>   server-id=111</p><p>3)从机 my.ini / my.cnf 文件配置</p><p>   #二进制文件,从机可以不开,建议开启</p><p>   log-bin=mysql-bin </p><p>   #主机服务ID ,必须唯一</p><p>   server-id=111</p><p>4)主机环境开通数据同步用户</p><pre class="brush:bash;toolbar:false">  GRANTREPLICATIONSLAVEON*.*to&#39;mycat_sync&#39;@&#39;%&#39;identifiedby&#39;mycat_sync&#39;;</pre><p>5)主机查年binLog状态</p><pre class="brush:bash;toolbar:false">showmasterstatus;</pre><p>6)从机配置同步 并 启动从机状态</p><pre class="brush:bash;toolbar:false">changemastertomaster_host=&#39;192.168.1.247&#39; ,master_port=3306 ,master_user=&#39;mycat_sync&#39; ,master_password=&#39;mycat_sync&#39; ,master_log_file=&#39;mysql-bin.000001&#39; ,master_log_pos=832; startslave;</pre><p>7)关注Slave_IO_State,Slave_IO_Running,Slave_SQL_Running状态</p><p> 若都为yes状态时确认同步配置完成</p><pre class="brush:bash;toolbar:false">showslavestatus;</pre><p>8)验证主从同步有效性,在主机中 Drop Database mycat</p><p>8.1)主机环境查询库状态</p><p> 这里有个坑, 两机同步时需要确保两边的内容完全一致,后面验证时做了数据清理以完成所有验证步骤。</p><pre class="brush:bash;toolbar:false">mysql&gt;showdatabases; +--------------------+ |Database| +--------------------+ |information_schema| |ecshopdb| |mycat| |mysql| |performance_schema| |shopnc| |shopnc2| |test| |testdb| |xjh| +--------------------+ 10rowsinset(0.00sec)</pre><p>8.2)从机查询库状态</p><pre class="brush:bash;toolbar:false">mysql&gt;showdatabases; +--------------------+ |Database| +--------------------+ |information_schema| |mycat| |mysql| |performance_schema| +--------------------+ 4rowsinset(0.00sec)</pre><p></p><p>8.3)主机 drop database mycat ,并再次确认</p><pre class="brush:bash;toolbar:false">mysql&gt;dropdatabasemycat; QueryOK,0rowsaffected(0.01sec) mysql&gt;showdatabases; +--------------------+ |Database| +--------------------+ |information_schema| |ecshopdb| |mysql| |performance_schema| |shopnc| |shopnc2| |test| |testdb| |xjh| +--------------------+ 9rowsinset(0.00sec)</pre><p>8.4)从机状态查询确认</p><pre class="brush:bash;toolbar:false">mysql&gt;showdatabases; +--------------------+ |Database| +--------------------+ |information_schema| |mysql| |performance_schema| +--------------------+ 3rowsinset(0.00sec)</pre><p></p><p>9)Create Database、Create Table 验证</p><p>9.1)主机操作</p><pre class="brush:bash;toolbar:false">mysql&gt;createdatabasemycat_sync_test; QueryOK,1rowaffected(0.00sec) mysql&gt;showdatabases; +--------------------+ |Database| +--------------------+ |information_schema| |ecshopdb| |mycat_sync_test| |mysql| |performance_schema| |shopnc| |shopnc2| |test| |testdb| |xjh| +--------------------+ 10rowsinset(0.00sec) mysql&gt;usemycat_sync_test; Databasechanged mysql&gt;CREATETABLE`aaa`( -&gt;`id`INTNOTNULL, -&gt;`context`VARCHAR(45)NULL, -&gt;PRIMARYKEY(`id`)); QueryOK,0rowsaffected(0.02sec) mysql&gt;showtables; +---------------------------+ |Tables_in_mycat_sync_test| +---------------------------+ |aaa| +---------------------------+ 1rowinset(0.00sec)</pre><p><br/></p><p>9.2)从机验证</p><pre class="brush:bash;toolbar:false">mysql&gt;showdatabases; +--------------------+ |Database| +--------------------+ |information_schema| |mycat_sync_test| |mysql| |performance_schema| +--------------------+</pre><p>4 rows in set (0.00 sec)</p><pre class="brush:bash;toolbar:false">mysql&gt;usemycat_sync_test; Databasechanged mysql&gt;showtables; +---------------------------+ |Tables_in_mycat_sync_test| +---------------------------+ |aaa| +---------------------------+ 1rowinset(0.00sec)</pre><p><br/></p><p>10)Insert、Update、Delete 验证</p><p>10.1)主机操作 INSERT</p><pre class="brush:bash;toolbar:false">mysql&gt;insertintoaaavalues(111,&#39;testcontext&#39;); QueryOK,1rowaffected(0.00sec) mysql&gt;select*fromaaa; +-----+--------------+ |id|context| +-----+--------------+ |111|testcontext| +-----+--------------+ 1rowinset(0.00sec)</pre><p><br/></p><p>10.2)从机验证 INSERT</p><pre class="brush:bash;toolbar:false">mysql&gt;select*fromaaa; +-----+--------------+ |id|context| +-----+--------------+ |111|testcontext| +-----+--------------+ 1rowinset(0.00sec)</pre><p><br/></p><p>10.3)主机操作 UPDATE</p><pre class="brush:bash;toolbar:false">mysql&gt;updateaaasetcontext=&#39;helloworld&#39;whereid=111; QueryOK,1rowaffected(0.01sec) Rowsmatched:1Changed:1Warnings:0 mysql&gt;select*fromaaa; +-----+-------------+ |id|context| +-----+-------------+ |111|helloworld| +-----+-------------+ 1rowinset(0.00sec)</pre><p><br/></p><p>10.4)从机验证 UPDATE</p><pre class="brush:bash;toolbar:false">mysql&gt;select*fromaaa; +-----+-------------+ |id|context| +-----+-------------+ |111|helloworld| +-----+-------------+ 1rowinset(0.00sec)</pre><p><br/></p><p>10.5)主机操作 DELETE</p><pre class="brush:bash;toolbar:false">mysql&gt;truncatetableaaa; QueryOK,0rowsaffected(0.00sec) mysql&gt;select*fromaaa; Emptyset(0.00sec)</pre><p>10.6)从机验证 DELETE</p><pre class="brush:bash;toolbar:false">mysql&gt;select*fromaaa; Emptyset(0.00sec)</pre>
RangeTime:0.006853s
RangeMem:211.63 KB
返回顶部 留言