window环境下mysql实现主从同步

3311从库：

<span style="color: #008080">--</span><span style="color: #008080"> my.ini文件中的[mysqld]增加</span>
server<span style="color: #808080">-</span>id <span style="color: #808080">=</span> <span style="color: #800000; font-weight: bold">2</span>
<span style="color: #ff00ff">replicate</span><span style="color: #808080">-</span>do<span style="color: #808080">-</span>db<span style="color: #808080">=</span><span style="color: #000000">test1
relay</span><span style="color: #808080">-</span><span style="color: #ff00ff">log</span><span style="color: #808080">=</span>relay<span style="color: #808080">-</span><span style="color: #000000">bin
relay</span><span style="color: #808080">-</span><span style="color: #ff00ff">log</span><span style="color: #808080">-</span><span style="color: #0000ff">index</span><span style="color: #808080">=</span>relay<span style="color: #808080">-</span>bin<span style="color: #808080">-</span><span style="color: #0000ff">index</span>

<span style="color: #000000">stop slave;<br><br>-- 【方法一】<br>change master </span><span style="color: #0000ff">to</span> master_host<span style="color: #808080">=</span><span style="color: #ff0000">‘</span><span style="color: #ff0000">192.168.1.110</span><span style="color: #ff0000">‘</span>,master_port<span style="color: #808080">=</span><span style="color: #800000; font-weight: bold">3310</span>,master_user<span style="color: #808080">=</span><span style="color: #ff0000">‘</span><span style="color: #ff0000">slave</span><span style="color: #ff0000">‘</span>,master_password<span style="color: #808080">=</span><span style="color: #ff0000">‘</span><span style="color: #ff0000">123456</span><span style="color: #ff0000">‘</span><span style="color: #000000">;
</span><span style="color: #008080">--</span><span style="color: #008080"> 【方法二】若指定position同步，请使用（后面两个参数通过主库命令“show master status;</span><span style="color: #008080">”查看）：</span> <br>change master <span style="color: #0000ff">to</span> master_host<span style="color: #808080">=</span><span style="color: #ff0000">‘</span><span style="color: #ff0000">192.168.1.110</span><span style="color: #ff0000">‘</span>,master_port<span style="color: #808080">=</span><span style="color: #800000; font-weight: bold">3310</span>,master_user<span style="color: #808080">=</span><span style="color: #ff0000">‘</span><span style="color: #ff0000">slave</span><span style="color: #ff0000">‘</span>,master_password<span style="color: #808080">=</span><span style="color: #ff0000">‘</span><span style="color: #ff0000">123456</span><span style="color: #ff0000">‘</span>,<br>master_log_file<span style="color: #808080">=</span><span style="color: #ff0000">‘</span><span style="color: #ff0000">mysql-bin.000001</span><span style="color: #ff0000">‘</span>,master_log_pos<span style="color: #808080">=</span><span style="color: #800000; font-weight: bold">107</span><span>; <br><br>start slave;</span>

至此，配置完毕，通过命令：

show slave status\G;

查看同步是否连接成功，关键指标为：

<span style="color: #000000">Slave_IO_Running: Yes
Slave_SQL_Running: Yes</span>

备注：

<span style="color: #008080">--</span><span style="color: #008080"> 查看server_id：</span>
show VARIABLES <span style="color: #808080">like</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000">server_id</span><span style="color: #ff0000">‘</span>;

<span style="color: #008080">--</span><span style="color: #008080"> 主服务器上查看主机状态，File和Position</span>
show master status;

默认配置的mysql中root账户无密码，通过如下方式修改：

mysql <span style="color: #808080">-</span>u root <span style="color: #808080">-</span><span style="color: #000000">p
enter password:（此处直接回车）<br>
</span><span style="color: #008080">--</span><span style="color: #008080"> 修改密码为123456</span>
<span style="color: #0000ff">set</span> password<span style="color: #808080">=</span>password(<span style="color: #ff0000">‘</span><span style="color: #ff0000">123456</span><span style="color: #ff0000">‘</span><span style="color: #000000">);
</span><span style="color: #008080">--</span><span style="color: #008080"> 刷新权限</span>
flush <span style="color: #0000ff">privileges</span>;

附my.ini配置文件下载：

 my.ini下载

参考：

https://www.cnblogs.com/puchengjie/p/5220676.html

主从同步故障解决方案：

Slave_SQL_Running: No

http://blog.51cto.com/kerry/277414

http://blog.chinaunix.net/uid-21505614-id-289477.html

linux下配置主从参考：

http://blog.chinaunix.net/uid-21505614-id-289477.html

window环境下mysql实现主从同步

标签：.com   root   replica   star   lin   status   class   window   index