<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主键冲突错误 <span>Duplicate entry '1234' for key 'PRIMARY'</span></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 > 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 "gunzip -c - >master.sql"</p><p>分别到slave服务器上执行导入</p><p>1<span class="Apple-tab-span" > </span>mysql -uroot -poP05_1ep < 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 "gunzip -c - | mysql -uroot -poP05_1ep"</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='61.164.186.242',master_user='root',master_password='oP05_1ep';</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 #同步读取二进制日志的位置，大于等于>=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 #同步执行二进制日志的位置，小于等于<=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 = "/var/log/mysql/mysql.err"，检查错误日志内容。</p><p>[ERROR] /usr/sbin/mysqld: Table './web_td/trick_rates' 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；"gzip -c -"的意思是：读取标准输入(-)，gzip后，输出到标准输出(-c)</p>