<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'mycat_sync'@'%'identifiedby'mycat_sync';</pre><p>5）主机查年binLog状态</p><pre class="brush:bash;toolbar:false">showmasterstatus;</pre><p>6）从机配置同步 并 启动从机状态</p><pre class="brush:bash;toolbar:false">changemastertomaster_host='192.168.1.247'
,master_port=3306
,master_user='mycat_sync'
,master_password='mycat_sync'
,master_log_file='mysql-bin.000001'
,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>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>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>dropdatabasemycat;
QueryOK,0rowsaffected(0.01sec)
mysql>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>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>createdatabasemycat_sync_test;
QueryOK,1rowaffected(0.00sec)
mysql>showdatabases;
+--------------------+
|Database|
+--------------------+
|information_schema|
|ecshopdb|
|mycat_sync_test|
|mysql|
|performance_schema|
|shopnc|
|shopnc2|
|test|
|testdb|
|xjh|
+--------------------+
10rowsinset(0.00sec)
mysql>usemycat_sync_test;
Databasechanged
mysql>CREATETABLE`aaa`(
->`id`INTNOTNULL,
->`context`VARCHAR(45)NULL,
->PRIMARYKEY(`id`));
QueryOK,0rowsaffected(0.02sec)
mysql>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>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>usemycat_sync_test;
Databasechanged
mysql>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>insertintoaaavalues(111,'testcontext');
QueryOK,1rowaffected(0.00sec)
mysql>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>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>updateaaasetcontext='helloworld'whereid=111;
QueryOK,1rowaffected(0.01sec)
Rowsmatched:1Changed:1Warnings:0
mysql>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>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>truncatetableaaa;
QueryOK,0rowsaffected(0.00sec)
mysql>select*fromaaa;
Emptyset(0.00sec)</pre><p>10.6）从机验证 DELETE</p><pre class="brush:bash;toolbar:false">mysql>select*fromaaa;
Emptyset(0.00sec)</pre>