阿里云-云小站(无限量代金券发放中)
【腾讯云】云服务器、云数据库、COS、CDN、短信等热卖云产品特惠抢购

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

259次阅读
没有评论

共计 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,加强安全。

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 均属错误)。

以上操作过程,主从服务器配置完成。
 
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> show databases;
  +——————–+
  | Database          |
  +——————–+
  | information_schema |
  | hi_db                |
  | mysql                |
  | test                 |
  +——————–+
  4 rows in set (0.00 sec)

从服务器 Mysql 查询:

  mysql> show databases;

  +——————–+
  | Database               |
  +——————–+
  | information_schema |
  | hi_db                 |       //I’M here,大家看到了吧
  | mysql                 |
  | test          |

   +——————–+
  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;

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

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |

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

| 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

DROP       tcp  —  0.0.0.0/0            0.0.0.0/0            tcp dpt:3306

外部连接 3306 端口被直接丢弃 …. 好变态!

查看防火墙规则编号:iptables -L -n –line-number

6    DROP       tcp  —  0.0.0.0/0            0.0.0.0/0            tcp dpt:3306

现在我们删掉这条规则:iptables -D INPUT 6

再重新添加一条规则:iptables -A INPUT -p tcp –dport 3306 -j ACCEPT

查看状态:

ACCEPT     tcp  —  0.0.0.0/0            0.0.0.0/0            tcp dpt:3306

修改完毕,重启从服务器 mysql、查看数据库

之前:

mysql> show databases;

+——————–+

| Database           |

+——————–+

| information_schema |

| mysql              |

| performance_schema |

+——————–+

之后:

+——————–+

| Database           |

+——————–+

| information_schema |

| mysql              |

| performance_schema |

| test               |

+——————–+

同步完成!

MySQL 主从复制部署  http://www.linuxidc.com/Linux/2017-09/146728.htm

Ubuntu 16.04 下实现 MySQL 主从复制  http://www.linuxidc.com/Linux/2017-08/146624.htm

本文永久更新链接地址:http://www.linuxidc.com/Linux/2017-10/147557.htm

正文完
星哥玩云-微信公众号
post-qrcode
 0
星锅
版权声明:本站原创文章,由 星锅 于2022-01-22发表,共计7430字。
转载说明:除特殊说明外本站文章皆由CC-4.0协议发布,转载请注明出处。
【腾讯云】推广者专属福利,新客户无门槛领取总价值高达2860元代金券,每种代金券限量500张,先到先得。
阿里云-最新活动爆款每日限量供应
评论(没有评论)
验证码
【腾讯云】云服务器、云数据库、COS、CDN、短信等云产品特惠热卖中