mySql主从配置出错:The server is not configured as slave;

<p>数据库已搭建完成,各种配置均已完成,但是在start slave ;的时候,报错:</p><p>ERROR 1200 (HY000): The server is not configured as slave; fix in config file or with CHANGE MASTER TO</p><p>这里说,我没有配置为从机,但我明明配置过的啊。</p><p>MySQL版本:5.6.19</p><p>主:master IP : 172.17.210.199</p><p>从:slave IP :172.17.206.138</p><p>先看看从机172.17.206.138的my.cnf</p><pre class="brush:as3;toolbar:false">[root@liuyazhuang~]#vi/etc/my.cnf [mysqld] datadir=/usr/local/mysql/data socket=/tmp/mysql.sock skip-grant-tables user=mysql #Disablingsymbolic-linksisrecommendedtopreventassortedsecurityrisks symbolic-links=0 tmpdir=/tmp [mysqld_safe] log-error=/usr/local/data/mysqld.log pid-file=/usr/local/mysql/data/mysqld.pid ###############以下是添加主从的配置 server_id=2 log-bin=/usr/local/mysql/log/solve-bin.log master-host=172.17.210.199 master-user=test master-pass=123456 master-port=3306 master-connect-retry=60</pre><p>这是MySQL主机172.17.210.199的my.cnf</p><pre class="brush:bash;toolbar:false">[root@liuyazhuang~]$cat/etc/my.cnf [mysqld] log-bin=/u01/mysql/log/masters-bin.log read-only=0 basedir=/u01/mysql datadir=/u01/mysql/data port=3306 server_id=1 socket=/tmp/mysql.sock join_buffer_size=128M sort_buffer_size=2M read_rnd_buffer_size=2M sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES</pre><p>尝试过多次 重启主从还是报错;</p><p>接着手动chang to 后还是一样报错</p><pre class="brush:bash;toolbar:false">mysql&gt;CHANGEMASTERTO MASTER_HOST=&#39;172.17.210.199&#39;, MASTER_USER=&#39;test&#39;, MASTER_PASSWORD=&#39;123456&#39;, MASTER_LOG_FILE=&#39;masters-bin.000003&#39;, MASTER_LOG_POS=120; QueryOK,0rowsaffected,2warnings(0.04sec)</pre><p>在从库服务器执行START slave;命令启动slave</p><pre class="brush:bash;toolbar:false">mysql&gt;STARTslave;</pre><p><br/></p><p>报错:</p><p>ERROR 1200 (HY000): The server is not configured as slave; fix in config file or with CHANGE MASTER TO</p><p>于是查看日志</p><p>1.查看SLAVE172.17.206.138上的Mysql报错日志,有这么一句:</p><p>141009 6:06:29 [ERROR] Server id not set, will not start slave</p><p>意思是,slave的server-id没有设置。</p><p>那就奇怪了,我明明在配置文件里面指定了server-id的了,并且有重启mysql服务,难道不起效?</p><p>分别在主从上执行命令&quot;show variables like &#39;server_id&#39;;&quot;。</p><p>-------从机上面查看端口</p><pre class="brush:bash;toolbar:false">mysql&gt;mysql&gt;SHOWVARIABLESLIKE&#39;server_id&#39;; +---------------+-------+ |Variable_name|Value| +---------------+-------+ |server_id|0| +---------------+-------+ 1rowinset(0.00sec)</pre><p>命名设置的是2,怎么会变成0</p><p>-------主机上面查看</p><pre class="brush:bash;toolbar:false">mysql&gt;SHOWVARIABLESLIKE&#39;server_id&#39;; +---------------+-------+ |Variable_name|Value| +---------------+-------+ |server_id|1| +---------------+-------+ 1rowinset(0.00sec)</pre><p>跟设置的一样。</p><p>既然参数文件不生效,就试试在数据库命令里面设置:在从机 172.17.206.138上执行命令</p><pre class="brush:bash;toolbar:false">mysql&gt;SETGLOBALserver_id=2;</pre><p>再次在从机 172.17.206.138上执行slave start和show slave status,成功了。</p><p> 注意!!!由于&quot;SET GLOBAL server_id=;&quot;命令会在mysql服务重启后丢失,所以一定要写到配置文件里面。</p><p>但为什么我之前修改了my.cnf文件不起效?</p><p>仔细排查,发现配置里面有[mysqld]和[mysqld_safe],之前将修改的配置内容基本都放在了[mysqld_safe]下面,新增的配置文件放的位置不一样也有关系?于是我尝试把配置文件里修改的部分放在[mysqld]下面,于是改成这样:</p><pre class="brush:bash;toolbar:false">[root@liuyazhuang]#cat/etc/my.cnf [mysqld] datadir=/usr/local/mysql/data socket=/tmp/mysql.sock skip-grant-tables user=mysql symbolic-links=0 tmpdir=/tmp server_id=2 log-bin=/usr/local/mysql/log/solve-bin.log master-host=172.17.210.199 master-user=test master-pass=123456 master-port=3306 master-connect-retry=60 [mysqld_safe] log-error=/usr/local/data/mysqld.log pid-file=/usr/local/mysql/data/mysqld.pid</pre><p><br/></p><p>如图,就是将图中标注的内容从[mysqld_safe]下修改到[mysqld]下</p><p><img src="/up_pic/201805/180931578191.jpg" title="180931578191.jpg" alt="1.jpg"/></p><p><img src="/up_pic/201805/180932029908.jpg" title="180932029908.jpg" alt="2.jpg"/></p><p>修改之后,再次同步,成功了!</p><pre class="brush:bash;toolbar:false">mysql&gt; mysql&gt;STOPslave; QueryOK,0rowsaffected(0.05sec) mysql&gt;STARTslave; QueryOK,0rowsaffected(0.00sec)</pre>
RangeTime:0.003806s
RangeMem:203.23 KB
返回顶部 留言