MySQL 主从模式设置

<p>MySQL 主从模式设置</p><p>/etc/mysql/my.cnf<br/></p><p>主服务器Master</p><p>IP:61.164.186.242</p><p>1<span class="Apple-tab-span" > </span>#[必须]启用二进制日志</p><p>2<span class="Apple-tab-span" > </span>log_bin = mysql-bin</p><p>3<span class="Apple-tab-span" > </span>#[必须]服务器唯一id,一般取ip最后两位</p><p>4<span class="Apple-tab-span" > </span>server_id = 242</p><p>5<span class="Apple-tab-span" > </span>#[可选]每次提交事务时,同步二进制日志内容到磁盘上,即使服务器崩溃也会把事件写入日志</p><p>6<span class="Apple-tab-span" > </span>sync_binlog = 1</p><p>重启生效</p><p>1<span class="Apple-tab-span" > </span>/etc/init.d/mysql restart</p><p>从服务器Slave</p><p>IP:61.164.186.241/246</p><p>1<span class="Apple-tab-span" > </span>#[必须]服务器唯一id,一般取ip最后两位</p><p>2<span class="Apple-tab-span" > </span>server_id = 246</p><p>3<span class="Apple-tab-span" > </span>#[可选]启用二进制日志</p><p>4<span class="Apple-tab-span" > </span>log_bin = mysql-bin</p><p>5<span class="Apple-tab-span" > </span>#[可选]定义中继日志的位置和文件名</p><p>6<span class="Apple-tab-span" > </span>relay_log = mysql-relay-bin</p><p>7<span class="Apple-tab-span" > </span>#[可选]使从服务器把复制的事件记录到自己的二进制日志中</p><p>8<span class="Apple-tab-span" > </span>log_slave_updates = 1</p><p>9<span class="Apple-tab-span" > </span>#[可选]只读</p><p>10<span class="Apple-tab-span" > </span>read_only = 1</p><p>11<span class="Apple-tab-span" > </span>#[可选]跳过1062主键冲突错误 &lt;span&gt;Duplicate entry &#39;1234&#39; for key &#39;PRIMARY&#39;&lt;/span&gt;</p><p>12<span class="Apple-tab-span" > </span>slave-skip-errors = 1062</p><p>同步数据</p><p>将master数据库导出为master.sql</p><p>1<span class="Apple-tab-span" > </span>mysqldump --single-transaction --all-databases --master-data=1 -hlocalhost -uroot -poP05_1ep &gt; master.sql</p><p>复制master.sql到slave服务器</p><p>1<span class="Apple-tab-span" > </span>gzip -c master.sql | ssh root@61.164.186.241 &quot;gunzip -c - &gt;master.sql&quot;</p><p>分别到slave服务器上执行导入</p><p>1<span class="Apple-tab-span" > </span>mysql -uroot -poP05_1ep &lt; master.sql</p><p>由于备份导入的是整个数据库,所以主从数据库的数据完全一致,包括mysql用户名密码都是一致的。</p><p>同步/etc/mysql/debian.cnf 中的管理密码;否则会导致/etc/init.d/mysql restart失败!系统找不到启动后的mysql进程。</p><p>1<span class="Apple-tab-span" > </span>scp /etc/mysql/debian.cnf root@61.164.186.241:/etc/mysql/debian.cnf</p><p>重启生效</p><p>1<span class="Apple-tab-span" > </span>/etc/init.d/mysql restart</p><p>把上面的所有命令用管道一次执行,哈哈^_^,直接将本服务器数据同步到另外一台服务器!!!</p><p>1<span class="Apple-tab-span" > </span>mysqldump --single-transaction --all-databases --master-data=1 -hlocalhost -uroot -poP05_1ep | gzip -c - | ssh root@61.164.186.241 &quot;gunzip -c - | mysql -uroot -poP05_1ep&quot;</p><p>启动复制</p><p>登录slave</p><p>1<span class="Apple-tab-span" > </span>mysql -uroot -poP05_1ep</p><p>设置master</p><p>1<span class="Apple-tab-span" > </span>change master to master_host=&#39;61.164.186.242&#39;,master_user=&#39;root&#39;,master_password=&#39;oP05_1ep&#39;;</p><p>开始复制</p><p>1<span class="Apple-tab-span" > </span>start slave;</p><p>查看状态</p><p>1<span class="Apple-tab-span" > </span>show slave status\G</p><p>1<span class="Apple-tab-span" > </span>Slave_IO_State: Waiting for master to send event</p><p>2<span class="Apple-tab-span" > </span> Master_Host: 61.164.186.242 #设置的master服务器</p><p>3<span class="Apple-tab-span" > </span> Master_User: root</p><p>4<span class="Apple-tab-span" > </span> Master_Port: 3306</p><p>5<span class="Apple-tab-span" > </span> Connect_Retry: 60</p><p>6<span class="Apple-tab-span" > </span> Master_Log_File: mysql-bin.000004</p><p>7<span class="Apple-tab-span" > </span> Read_Master_Log_Pos: 100764106 #同步读取二进制日志的位置,大于等于&gt;=Exec_Master_Log_Pos</p><p>8<span class="Apple-tab-span" > </span> Relay_Log_File: mysqld-relay-bin.000010</p><p>9<span class="Apple-tab-span" > </span> Relay_Log_Pos: 100764251</p><p>10<span class="Apple-tab-span" > </span> Relay_Master_Log_File: mysql-bin.000004</p><p>11<span class="Apple-tab-span" > </span> Slave_IO_Running: Yes</p><p>12<span class="Apple-tab-span" > </span> Slave_SQL_Running: Yes</p><p>13<span class="Apple-tab-span" > </span> Replicate_Do_DB:</p><p>14<span class="Apple-tab-span" > </span> Replicate_Ignore_DB:</p><p>15<span class="Apple-tab-span" > </span> Replicate_Do_Table:</p><p>16<span class="Apple-tab-span" > </span> Replicate_Ignore_Table:</p><p>17<span class="Apple-tab-span" > </span> Replicate_Wild_Do_Table:</p><p>18<span class="Apple-tab-span" > </span> Replicate_Wild_Ignore_Table:</p><p>19<span class="Apple-tab-span" > </span> Last_Errno: 0</p><p>20<span class="Apple-tab-span" > </span> Last_Error:</p><p>21<span class="Apple-tab-span" > </span> Skip_Counter: 0</p><p>22<span class="Apple-tab-span" > </span> Exec_Master_Log_Pos: 100764106 #同步执行二进制日志的位置,小于等于&lt;=Read_Master_Log_Pos</p><p>23<span class="Apple-tab-span" > </span> Relay_Log_Space: 100764450</p><p>24<span class="Apple-tab-span" > </span> Until_Condition: None</p><p>25<span class="Apple-tab-span" > </span> Until_Log_File:</p><p>26<span class="Apple-tab-span" > </span> Until_Log_Pos: 0</p><p>27<span class="Apple-tab-span" > </span> Master_SSL_Allowed: No</p><p>28<span class="Apple-tab-span" > </span> Master_SSL_CA_File:</p><p>29<span class="Apple-tab-span" > </span> Master_SSL_CA_Path:</p><p>30<span class="Apple-tab-span" > </span> Master_SSL_Cert:</p><p>31<span class="Apple-tab-span" > </span> Master_SSL_Cipher:</p><p>32<span class="Apple-tab-span" > </span> Master_SSL_Key:</p><p>33<span class="Apple-tab-span" > </span> Seconds_Behind_Master: 0 #从服务器数据比主服务器延迟多少秒?一般是0</p><p>34<span class="Apple-tab-span" > </span>Master_SSL_Verify_Server_Cert: No</p><p>35<span class="Apple-tab-span" > </span> Last_IO_Errno: 0</p><p>36<span class="Apple-tab-span" > </span> Last_IO_Error:</p><p>37<span class="Apple-tab-span" > </span> Last_SQL_Errno: 0</p><p>38<span class="Apple-tab-span" > </span> Last_SQL_Error:</p><p>问题</p><p>修改mysql配置,导致mysql无法启动?</p><p>答:请启用mysql错误日志,log_error = &quot;/var/log/mysql/mysql.err&quot;,检查错误日志内容。</p><p>[ERROR] /usr/sbin/mysqld: Table &#39;./web_td/trick_rates&#39; is marked as crashed and should be repaired?</p><p>答:编辑配置,在[mysqld]段内加入,myisam-recover = BACKUP,FORCE,强制修复表</p><p>start slave失败,提示relay_log错误?</p><p>答:直接注释掉:relay_log = mysql-relay-bin</p><p>gzip -c -,gunzip -c -是什么意思?</p><p>答:一般来说命令是 gzip -c /file, – 是标准输入stdin;&quot;gzip -c -&quot;的意思是:读取标准输入(-),gzip后,输出到标准输出(-c)</p>
RangeTime:0.009136s
RangeMem:211.59 KB
返回顶部 留言