CentOS下mysql的双机热备（转分享是一种美德）

深藏阁楼爱情的钟 2023-08-17 16:48 6阅读 0赞

[![mysql双机热备][mysql]][mysql_mysql]

heartbeat只是起到了ip漂移的功能，但是实际应用当中必须要做到文件和数据的实时同步，这里我们来动手搭建一下mysql的双机互备，及搭建过程中碰到的常见问题和解决方法。

mysql的双机热备其实是基于二进制日志文件的，从服务器读取主服务器的日志文件然后更新，从而达到同步，具体过程是这样的：mysql支持单向、异步复制，复制过程中要有一个主服务器，一个或者多个充当从服务器，主服务器将更新写入二进制日志文件，并维护日志文件的索引以跟踪日志的循环，服务器在日志中读取最后一次更新的位置，从服务器接收从那时起发起的任何更新，然后封锁并等待主服务器通知的下一次更新。

实际项目中两台服务器互为主备，当其中一台服务器出现故障时，另外一台服务器接管主服务器上的应用（结合上一篇 结合上一篇：[http://www.iamlintao.com/动手搭建centos下用heartbeat搭建双机热备/][http_www.iamlintao.com_centos_heartbeat] 实现），此时便需要mysql的实时双机互备。我的搭建过程如下：

### **服务器环境：** ###

**主服务器：**

操作系统：centos6.4 (Linux 2.6.18-348.12.1.el5)  
ip：10.0.0.211  
web环境：wdcp搭建  
mysql：5.1.69

**备服务器：**

操作系统：centos6.4 (Linux 2.6.18-348.12.1.el5)  
ip：10.0.0.212  
web环境：wdcp搭建  
mysql：5.1.69

### 创建同步账号： ###

数据库能相互访问受限要创建可以远程登陆的账号，通过这个远程账号才能实现数据同步。首先登陆mysql(登陆方法自行google),登陆mysql之后用以下命令创建远程登陆账号：

<table> 
 <tbody> 
  <tr> 
   <td>1</td> 
   <td>&lt;span style="font-family: 'Times New Roman';"&gt;MySQL&gt;GRANT all privileges ON *.* TO zuiw@'10.0.0.212'&nbsp;IDENTIFIED BY &nbsp;'******';&nbsp;&lt;/span&gt;</td> 
  </tr> 
 </tbody> 
</table>

*这条命令是在主服务器上自行的，备服务器自己来写就行。*

**语法解释：**

grant 权限1,权限2,…权限n on 数据库名称.表名称 to 远程登陆用户名@远程登陆地址 identified by ‘远程登陆连接口令’;

·权限1,权限2,…权限n代表select,insert,update,delete,create,drop,index,alter,grant,references,reload,shutdown,process,file等14个权限。  
·当权限1,权限2,…权限n被all privileges或者all代替，表示赋予用户全部权限。  
·当数据库名称.表名称被\*.\*代替，表示赋予用户操作服务器上所有数据库所有表的权限。  
·用户地址可以是localhost，也可以是ip地址、机器名字、域名。也可以用’%’表示从任何地址连接。  
· ‘连接口令’ 不能为空，否则创建失败。

**配置服务器的数据库参数：**

对于wdcp来说，mysql的配置文件在 /www/wdlinux/etc/my.cnf ；对于独立安装的mysql来说在 /etc/my.cnf下，用vi或者vim打开后按照如下配置：

<table> 
 <tbody> 
  <tr> 
   <td>1</td> 
   <td>log-bin=mysql-bin # 二进制文件的开头标示，必写项</td> 
  </tr> 
 </tbody> 
</table>

<table> 
 <tbody> 
  <tr> 
   <td>2</td> 
   <td>server-id= 1 # 主服务器写1，备服务器写2，必写项，主备服务器的一定不能相同</td> 
  </tr> 
 </tbody> 
</table>

<table> 
 <tbody> 
  <tr> 
   <td>3</td> 
   <td>binlog-do-db=test # 主服务器用，需要同步的数据库，如果有多个自行复制多行</td> 
  </tr> 
 </tbody> 
</table>

<table> 
 <tbody> 
  <tr> 
   <td>4</td> 
   <td>#binlog-ignore-db = mysql # 主服务器用，不需要同步的数据库，多个数据库自行复制</td> 
  </tr> 
 </tbody> 
</table>

<table> 
 <tbody> 
  <tr> 
   <td>5</td> 
   <td>master-host=10.0.0.212 # 允许同步服务器的ip，双机互备的情形下主服务器写备服务器ip，备服务器写主服务器ip</td> 
  </tr> 
 </tbody> 
</table>

<table> 
 <tbody> 
  <tr> 
   <td>6</td> 
   <td>master-user=zuiw # 用于同步的账号，在上一步中已经创建的那个远程登陆账号</td> 
  </tr> 
 </tbody> 
</table>

<table> 
 <tbody> 
  <tr> 
   <td>7</td> 
   <td>master-password=****** # 用于同步账号的密码，在上一步创建远程登陆账号时候设置的密码</td> 
  </tr> 
 </tbody> 
</table>

<table> 
 <tbody> 
  <tr> 
   <td>8</td> 
   <td>master-port=3306 # 同步服务器的mysql的端口</td> 
  </tr> 
 </tbody> 
</table>

<table> 
 <tbody> 
  <tr> 
   <td>9</td> 
   <td>master-connect-retry=3 #同步的时间间隔，单位是秒</td> 
  </tr> 
 </tbody> 
</table>

<table> 
 <tbody> 
  <tr> 
   <td>10</td> 
   <td>replicate-do-db=test # 备服务器用，需要同步的数据库，如果有多个自行复制多行</td> 
  </tr> 
 </tbody> 
</table>

<table> 
 <tbody> 
  <tr> 
   <td>11</td> 
   <td>#replicate-ignore-db=test&nbsp;#备服务器用，不需要同步的数据库，如果有多个自行复制多行</td> 
  </tr> 
 </tbody> 
</table>

<table> 
 <tbody> 
  <tr> 
   <td>12</td> 
   <td>slave-skip-errors=all # 忽略所有类型的错误</td> 
  </tr> 
 </tbody> 
</table>

<table> 
 <tbody> 
  <tr> 
   <td>13</td> 
   <td>sync_binlog=1 # 每向二进制日志文件写入n条sql或n个食物后，这把二进制日志问及那的数据刷新到新磁盘上，如果为0则不主动刷新而有操作系统决定，为了保险起见，这里建议写成1</td> 
  </tr> 
 </tbody> 
</table>

<table> 
 <tbody> 
  <tr> 
   <td>14</td> 
   <td>auto_increment_increment=1 #&nbsp;控制列中的值的增量值</td> 
  </tr> 
 </tbody> 
</table>

<table> 
 <tbody> 
  <tr> 
   <td>15</td> 
   <td>auto_increment_offset=1 #&nbsp;控制AUTO_INCREMENT 列值的起点</td> 
  </tr> 
 </tbody> 
</table>

以上配置为主服务器的配置，理解了上面的这几项，从服务器就容易了，只需要修改master-host和server-id即可，注意：这些项有的是存在的，有的是新增加的，有的是需要修改的，请自行核对。

执行同步：

·首先登陆服务器重启mysql

<table> 
 <tbody> 
  <tr> 
   <td>1</td> 
   <td>service MySQLd restart</td> 
  </tr> 
 </tbody> 
</table>

·然后停止同步、锁定表、查看读取的二进制文件和position的值，然后执行 CHANGE MASTER TO 命令，步骤如下：

<table> 
 <tbody> 
  <tr> 
   <td>1</td> 
   <td>MySQL&gt;stop slave; &nbsp; # 停止同步</td> 
  </tr> 
 </tbody> 
</table>

<table> 
 <tbody> 
  <tr> 
   <td>2</td> 
   <td>MySQL&gt;flush&nbsp;tables with read lock; &nbsp;# 锁定表为只读状态</td> 
  </tr> 
 </tbody> 
</table>

<table> 
 <tbody> 
  <tr> 
   <td>3</td> 
   <td>MySQL&gt;SHOW MASTER STATUS; # 查看主服务器的日志名称和position的值</td> 
  </tr> 
 </tbody> 
</table>

注:如果出现“ Can’t execute the given command because you have active locked tables or an active transaction”字样，则按照字面意思解决，执行先锁定再停止，总之命令就这几个，顺序自己搞定。

从返回结果获取二进制的名和position的值：

[![SHOW MASTER STATUS;][SHOW MASTER STATUS]][SHOW MASTER STATUS_SHOW MASTER STATUS]

CHANGE MASTER TO 这条命令把数据保存到master.info文件中去了，在数据库存放的路径下。

在主服务器上的mysql命令下执行如下语句：

<table> 
 <tbody> 
  <tr> 
   <td>1</td> 
   <td>change master to master_host='10.0.0.212',master_user='zuiw',master_password='****',master_log_file='mysql-bin.000001', master_log_pos=1787;</td> 
  </tr> 
 </tbody> 
</table>

可以这样理解，以那个用户/密码读取那个服务器(ip)的那个文件，从那个position开始读取。格式说明：

change master to master\_host=’主服务器上写备服务器ip，备服务器写主服务器ip’,master\_user=’同步用户名’,master\_password=’同步密码’,master\_log\_file=’上面获取到的二进制文件名’, master\_log\_pos=获取到的position的值;

备服务器自行编写。

最后启动同步：

<table> 
 <tbody> 
  <tr> 
   <td>1</td> 
   <td>MySQL&gt;start slave;</td> 
  </tr> 
 </tbody> 
</table>

<table> 
 <tbody> 
  <tr> 
   <td>2</td> 
   <td>MySQL&gt;unlock tables;</td> 
  </tr> 
 </tbody> 
</table>

如果正确，在主、备服务器上分别写入数据就能看到同步。

### 容易出现的几个问题： ###

**1. 同步用户创建不成功**

现象：执行“SHOW SLAVE STATUS;” 命令是返回带有“error connecting to master ‘zuiw@10.0.0.211:3306′ – retry-time: 60 retries: 86400”字样。

解决：提示说明连接到主/备服务器失败。在网上流传有三种分析：网络问题、配置问题、同步用户写错了，除了这三个之外还可能有另外一种情况。用phpmyadmin可以打开mysql数据库中的user表，如果成功创建同步账户则在user表中会 存在此用户，如果创建失败则不会存在；如果创建正确，执行“show processlist;”会得到如下：

[![mysqlzuiw][]][mysqlzuiw 1]

注意这里的同步所对应的host不是ip而是搭建服务器时候设置的服务器名(结合上一篇：[http://www.iamlintao.com/动手搭建centos下用heartbeat搭建双机热备/][http_www.iamlintao.com_centos_heartbeat]),所以连接不上可以考虑是不是host文件有问题，但是我发现我的host上是这样写的“127.0.0.1 localhost localhost.localdomain localhost”，于是改成“127.0.0.1 localhost.localdomain localhost”后再新建同步账户成功。

**2.日志重置**

现象：经过多次的测试或者试验会出现多个日志，需要重新设置。

解决：在mysql下执行

<table> 
 <tbody> 
  <tr> 
   <td>1</td> 
   <td>mysql&gt;reset master; &nbsp; #清除日志</td> 
  </tr> 
 </tbody> 
</table>

<table> 
 <tbody> 
  <tr> 
   <td>2</td> 
   <td>mysql&gt;&lt;code&gt;purge binary logs to&nbsp;&lt;/code&gt;&lt;code&gt;'mysql-bin.000018'&lt;/code&gt;&lt;code&gt;; # 删除mysql-bin.000018之前的日志&lt;/code&gt;</td> 
  </tr> 
 </tbody> 
</table>

或者直接用ssh工具登陆到服务器直接删除 master.info、 mysql-bin.000001、 mysql-bin.index、 relay-log.info 这几个文件，重启mysql后会自己生成。

**3.在没有解锁的情况下停止slave进程：**

<table> 
 <tbody> 
  <tr> 
   <td>1</td> 
   <td>mysql&gt; stop slave;</td> 
  </tr> 
 </tbody> 
</table>

<table> 
 <tbody> 
  <tr> 
   <td>2</td> 
   <td>ERROR 1192 (HY000): Can't execute the given command because you have active locked tables&nbsp;or&nbsp;an active transaction</td> 
  </tr> 
 </tbody> 
</table>

**4.change master语法错误，落下逗号**

<table> 
 <tbody> 
  <tr> 
   <td>1</td> 
   <td>mysql&gt; change master to</td> 
  </tr> 
 </tbody> 
</table>

<table> 
 <tbody> 
  <tr> 
   <td>3</td> 
   <td>-&gt; master_host='IP'</td> 
  </tr> 
 </tbody> 
</table>

<table> 
 <tbody> 
  <tr> 
   <td>4</td> 
   <td>&nbsp;-&gt; master_user='USER',</td> 
  </tr> 
 </tbody> 
</table>

<table> 
 <tbody> 
  <tr> 
   <td>5</td> 
   <td>&nbsp;-&gt; master_password='PASSWD',</td> 
  </tr> 
 </tbody> 
</table>

<table> 
 <tbody> 
  <tr> 
   <td>6</td> 
   <td>&nbsp;-&gt; master_log_file='mysql-bin.000002',</td> 
  </tr> 
 </tbody> 
</table>

<table> 
 <tbody> 
  <tr> 
   <td>7</td> 
   <td>&nbsp;-&gt; master_log_pos=106;</td> 
  </tr> 
 </tbody> 
</table>

<table> 
 <tbody> 
  <tr> 
   <td>8</td> 
   <td>&nbsp;ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version &nbsp; &nbsp;&nbsp;for&nbsp;the right syntax to&nbsp;use&nbsp;near&nbsp;'master_user='USER',</td> 
  </tr> 
 </tbody> 
</table>

<table> 
 <tbody> 
  <tr> 
   <td>9</td> 
   <td>&nbsp;master_password='PASSWD',</td> 
  </tr> 
 </tbody> 
</table>

<table> 
 <tbody> 
  <tr> 
   <td>10</td> 
   <td>&nbsp;master_log_file='mysql-bin.000002'&nbsp;at line 3</td> 
  </tr> 
 </tbody> 
</table>

**5.在没有停止slave进程的情况下change master**

[查看源代码][Link 1][打印][Link 2] [帮助][Link 3]

<table> 
 <tbody> 
  <tr> 
   <td>1</td> 
   <td>mysql&gt; change master to master_host=‘IP', master_user='USER', master_password='PASSWD', master_log_file='mysql- &nbsp; &nbsp;bin.000001',master_log_pos=106;</td> 
  </tr> 
 </tbody> 
</table>

<table> 
 <tbody> 
  <tr> 
   <td>3</td> 
   <td>ERROR 1198 (HY000): This operation cannot be performed with a running slave; run STOP SLAVE first</td> 
  </tr> 
 </tbody> 
</table>

6.A B的server-id相同：

<table> 
 <tbody> 
  <tr> 
   <td>1</td> 
   <td>Last_IO_Error: Fatal error: The slave I/O thread stops because master&nbsp;and&nbsp;slave have equal MySQL server ids;</td> 
  </tr> 
 </tbody> 
</table>

<table> 
 <tbody> 
  <tr> 
   <td>2</td> 
   <td>these ids must be different&nbsp;for&nbsp;replication to work (or&nbsp;the --replicate-same-server-id option must be used on</td> 
  </tr> 
 </tbody> 
</table>

<table> 
 <tbody> 
  <tr> 
   <td>3</td> 
   <td>slave but this does not always make sense; please check the manual before using it).</td> 
  </tr> 
 </tbody> 
</table>

<table> 
 <tbody> 
  <tr> 
   <td>4</td> 
   <td>查看server-id</td> 
  </tr> 
 </tbody> 
</table>

<table> 
 <tbody> 
  <tr> 
   <td>5</td> 
   <td>mysql&gt; show variables like&nbsp;'server_id';</td> 
  </tr> 
 </tbody> 
</table>

<table> 
 <tbody> 
  <tr> 
   <td>6</td> 
   <td>手动修改server-id</td> 
  </tr> 
 </tbody> 
</table>

<table> 
 <tbody> 
  <tr> 
   <td>7</td> 
   <td>mysql&gt; set&nbsp;global&nbsp;server_id=2; #此处的数值和my.cnf里设置的一样就行</td> 
  </tr> 
 </tbody> 
</table>

<table> 
 <tbody> 
  <tr> 
   <td>8</td> 
   <td>mysql&gt; slave start;</td> 
  </tr> 
 </tbody> 
</table>

**最后：**调试期间可以借助phpmyadmin、SSH Secure Shell 等工具，便于解决问题。

转载请注明来自：[http://www.iamlintao.com/亲测centos下实现mysql双机互备/][http_www.iamlintao.com_centos_mysql]

[mysql]: https://imgconvert.csdnimg.cn/aHR0cDovL3N0YXRpYy5vc2NoaW5hLm5ldC91cGxvYWRzL2ltZy8yMDEzMDgvMzAxMDI2MTBfN2JUci5qcGc?x-oss-process=image/format,png
[mysql_mysql]: /images/20230809/b8d77b7e4ad74f0fb03e4452ee48b51b.png
[http_www.iamlintao.com_centos_heartbeat]: http://www.iamlintao.com/%E5%8A%A8%E6%89%8B%E6%90%AD%E5%BB%BAcentos%E4%B8%8B%E7%94%A8heartbeat%E6%90%AD%E5%BB%BA%E5%8F%8C%E6%9C%BA%E7%83%AD%E5%A4%87/
[SHOW MASTER STATUS]: https://imgconvert.csdnimg.cn/aHR0cDovL3N0YXRpYy5vc2NoaW5hLm5ldC91cGxvYWRzL2ltZy8yMDEzMDgvMzAxMDI2MTFfRnRkYy5qcGc?x-oss-process=image/format,png
[SHOW MASTER STATUS_SHOW MASTER STATUS]: /images/20230809/7a63d54e9be541ddbf9ca349d6b83942.png
[mysqlzuiw]: /images/20230809/21e6266484304a80b6a9f980e8ae0615.png
[mysqlzuiw 1]: http://www.iamlintao.com/wp-content/uploads/2013/08/mysqlzuiw1.jpg
[Link 1]: http://www.iamlintao.com/%E4%BA%B2%E6%B5%8Bcentos%E4%B8%8B%E5%AE%9E%E7%8E%B0mysql%E5%8F%8C%E6%9C%BA%E4%BA%92%E5%A4%87/#viewSource
[Link 2]: http://www.iamlintao.com/%E4%BA%B2%E6%B5%8Bcentos%E4%B8%8B%E5%AE%9E%E7%8E%B0mysql%E5%8F%8C%E6%9C%BA%E4%BA%92%E5%A4%87/#printSource
[Link 3]: http://www.iamlintao.com/%E4%BA%B2%E6%B5%8Bcentos%E4%B8%8B%E5%AE%9E%E7%8E%B0mysql%E5%8F%8C%E6%9C%BA%E4%BA%92%E5%A4%87/#about
[http_www.iamlintao.com_centos_mysql]: http://www.iamlintao.com/%E4%BA%B2%E6%B5%8Bcentos%E4%B8%8B%E5%AE%9E%E7%8E%B0mysql%E5%8F%8C%E6%9C%BA%E4%BA%92%E5%A4%87/