MySQL主从复制及遇到问题的解决

266次阅读

共计 7430 个字符，预计需要花费 19 分钟才能阅读完成。

超简单的 MySQL 主从复制，怎么安装 MySQL 数据库，这里不说了，只说它的主从复制，步骤如下：

1、主从服务器分别作以下操作：
1.1、版本一致
1.2、初始化表，并在后台启动 mysql
1.3、修改 root 的密码

2、修改主服务器 master:
#vi /etc/my.cnf
[mysqld]
log-bin=mysql-bin //[必须]启用二进制日志
server-id=222 //[必须] 服务器唯一 ID，默认是 1，一般取 IP 最后一段

3、修改从服务器 slave:
#vi /etc/my.cnf
[mysqld]
log-bin=mysql-bin //[不是必须]启用二进制日志
server-id=226 //[必须] 服务器唯一 ID，默认是 1，一般取 IP 最后一段

4、重启两台服务器的 mysql
/etc/init.d/mysql restart

5、在主服务器上建立帐户并授权 slave:
#/usr/local/mysql/bin/mysql -uroot -pmttang
mysql>GRANT REPLICATION SLAVE ON *.* to ‘mysync’@’%’ identified by ‘q123456’; // 一般不用 root 帐号，“%” 表示所有客户端都可能连，只要帐号，密码正确，此处可用具体客户端 IP 代替，如 192.168.145.226，加强安全。

7、配置从服务器 Slave：
mysql>change master to master_host=’192.168.145.222′,master_user=’mysync‘,master_password=’q123456’,
master_log_file=’mysql-bin.000004′,master_log_pos=308; // 注意不要断开，308 数字前后无单引号。

Mysql>start slave; // 启动从服务器复制功能

8、检查从服务器复制功能状态：

mysql> show slave status\G

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.2.222 // 主服务器地址
Master_User: mysync // 授权帐户名，尽量避免使用 root
Master_Port: 3306 // 数据库端口，部分版本没有此行
Connect_Retry: 60
Master_Log_File: mysql-bin.000004
Read_Master_Log_Pos: 600 //# 同步读取二进制日志的位置，大于等于 Exec_Master_Log_Pos
Relay_Log_File: ddte-relay-bin.000003
Relay_Log_Pos: 251
Relay_Master_Log_File: mysql-bin.000004
Slave_IO_Running: Yes // 此状态必须 YES
Slave_SQL_Running: Yes // 此状态必须 YES
……

注：Slave_IO 及 Slave_SQL 进程必须正常运行，即 YES 状态，否则都是错误的状态(如：其中一个 NO 均属错误)。

以上操作过程，主从服务器配置完成。

9、主从服务器测试：

主服务器 Mysql，建立数据库，并在这个库中建表插入一条数据：

mysql> create database hi_db;
Query OK, 1 row affected (0.00 sec)

mysql> use hi_db;
Database changed

mysql> create table hi_tb(id int(3),name char(10));
Query OK, 0 rows affected (0.00 sec)

mysql> insert into hi_tb values(001,’bobu’);
Query OK, 1 row affected (0.00 sec)

从服务器 Mysql 查询：

mysql> show databases;

+——————–+
4 rows in set (0.00 sec)

mysql> use hi_db
Database changed
mysql> select * from hi_tb; // 查看主服务器上新增的具体数据
+——+——+
| id | name |
+——+——+
| 1 | bobu |
+——+——+
1 row in set (0.00 sec)

10、完成：
编写一 shell 脚本，用 nagios 监控 slave 的两个 yes（Slave_IO 及 Slave_SQL 进程），如发现只有一个或零个 yes，就表明主从有问题了，发短信警报吧。

主从复制配置比较简单，下面主要讲遇到的问题

1、主从服务器分别作以下操作 ：
1.1、版本一致
1.2、初始化表，并在后台启动 mysql
1.3、修改 root 的密码

2、修改主服务器 master:
#vi /etc/my.cnf
[mysqld]
log-bin=mysql-bin //[必须] 启用二进制日志
server-id=222 //[必须] 服务器唯一 ID，默认是 1，一般取 IP 最后一段

3、修改从服务器 slave:
#vi /etc/my.cnf
[mysqld]
log-bin=mysql-bin //[不是必须] 启用二进制日志
server-id=226 //[必须] 服务器唯一 ID，默认是 1，一般取 IP 最后一段

4、重启两台服务器的 mysql
/etc/init.d/mysql restart

5、在主服务器上建立帐户并授权 slave:
#/usr/local/mysql/bin/mysql -uroot -pmttang
mysql>GRANT REPLICATION SLAVE ON *.* to ‘mysync’@’%’ identified by ‘q123456’; // 一般不用 root 帐号，“%” 表示所有客户端都可能连，只要帐号，密码正确，此处可用具体客户端 IP 代替，如 192.168.145.226，加强安全。

6、登录主服务器的 mysql，查询 master 的状态
mysql>show master status;
+——————+———-+————–+——————+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+——————+———-+————–+——————+
| mysql-bin.000004 | 308 | | |
+——————+———-+————–+——————+
1 row in set (0.00 sec)
注：执行完此步骤后不要再操作主服务器 MYSQL，防止主服务器状态值变化

7、配置从服务器 Slave：
mysql>change master to master_host=’192.168.145.222′,master_user=’mysync‘,master_password=’q123456’,
master_log_file=’mysql-bin.000004′,master_log_pos=308; // 注意不要断开，308 数字前后无单引号。

Mysql>start slave; // 启动从服务器复制功能

8、检查从服务器复制功能状态：

mysql> show slave status\G

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.2.222 // 主服务器地址
Master_User: mysync // 授权帐户名，尽量避免使用 root
Master_Port: 3306 // 数据库端口，部分版本没有此行
Connect_Retry: 60
Master_Log_File: mysql-bin.000004
Read_Master_Log_Pos: 600 //# 同步读取二进制日志的位置，大于等于 Exec_Master_Log_Pos
Relay_Log_File: ddte-relay-bin.000003
Relay_Log_Pos: 251
Relay_Master_Log_File: mysql-bin.000004
Slave_IO_Running: Yes // 此状态必须 YES
Slave_SQL_Running: Yes // 此状态必须 YES
……

注：Slave_IO 及 Slave_SQL 进程必须正常运行，即 YES 状态，否则都是错误的状态 (如：其中一个 NO 均属错误)。

以上是转载，主从服务器配置完成。

下面就遇到问题了。

Relay_Log_Pos: 4

Relay_Master_Log_File: mysql-bin.000009

Slave_IO_Running: Connecting

Slave_SQL_Running: Yes

本人 Ubuntu16.04 系统，mysql5.5，进行到这一步时Slave_IO_Running 状态为 connection，很明显从服务器没有连接上主服务器的数据库。出现这种情况有三种可能性：

1，网络不通

2，密码不对

3，pos 不对，

1、直接排除，本人用的 vm 虚拟机网络都是配置好的，以防万一可以 ping 一下。

PING 192.168.1.50 (192.168.1.50) 56(84) bytes of data.

64 bytes from 192.168.1.50: icmp_seq=1 ttl=64 time=0.177 ms

64 bytes from 192.168.1.50: icmp_seq=2 ttl=64 time=0.257 ms

64 bytes from 192.168.1.50: icmp_seq=3 ttl=64 time=0.202 ms

2、找到原 change master 命令：

change master to master_host=’192.168.1.50′,master_user=’******’,master_password=’123456′,master_log_file=’mysql-bin.000009′,master_log_pos=326;

仔细看了一下相关信息均没有错误，此处若找不到命令可以去查看 mysql error 日志：vim /usr/local/mysql/var/******-server-4.err

翻到最下面日志开始的地方

171012 0:04:46 [Note] ‘CHANGE MASTER TO executed’. Previous state master_host=”, master_port=’3306′, master_log_file=”, master_log_pos=’4′. New state master_host=’192.168.1.50′, master_port=’3306′, master_log_file=’mysql-bin.000009′, master_log_pos=’326′.

171012 0:04:56 [Note] Slave SQL thread initialized, starting replication in log ‘mysql-bin.000009’ at position 326, relay log ‘./******-server-4-relay-bin.000001’ position: 4

171012 0:07:03 [ERROR] Slave I/O: error connecting to master ‘******@192.168.1.50:3306’ – retry-time: 60 retries: 86400, Error_code: 2003

此处显示比较详细，包括 change master 配置信息和连接错误信息（以上 * 号为 mysql 用户名，请自行替换）。

3、找到原主数据库 show master status 命令结果（这个找不到就真找不到了）：

mysql> show master status;

+——————+———-+————–+——————+

+——————+———-+————–+——————+

| mysql-bin.000009 | 326 | | |

最后再试一下，cmd 连接主服务器 mysql，

C:\Users\Administrator>mysql -h 192.168.1.50 -P 3306 -u ****** -p

Enter password: ******

ERROR 2003 (HY000): Can’t connect to MySQL server on ‘192.168.1.50’ (10060 “Unknown error”)

不出所料，基本可以断定问题在哪了，查看防火墙 iptables -L -n