mysql 高可靠搭建 (keepalived+主主同步)

工具及软件

  1.  centos 7.x

  2. mysql -5.6.35

  3. keepalived 做 HA

  4. 工作目录 /mytest

在进行本章节前 请先知晓 mysql 安装《centos7 中mysql 数据库安装和配置》

网络拓扑

        

MYSQL 主-主 同步备份

keepalived 做HA

MASTER   finder220  192.168.1.220

BACKUP  finder221  192.168.1.221

主-主同步配置

     配置 mysql 主主同步配置文件

    finder220配置

# vi /etc/my.cnf

 

log-bin=mysql-bin  #启动二进制日志系统
server-id=1  #本机数据库ID标示为主，另一台配置为2
binlog-do-db=test  #二进制需要同步的数据库名称
binlog-ignore-db=mysql  #避免同步mysql用户配置,以免不必要的麻烦
replicate-do-db=test  #同步数据库名称
replicate-ignore-db=mysql #屏蔽对mysql库同步
log-slave-updates
slave-skip-errors=all
sync_binlog=1
auto_increment_increment=2
auto_increment_offset=1  #另一配置为2

finder221配置

 

#vi /etc/my.cnf

log-bin=mysql-bin  #启动二进制日志系统
server-id=2  #本机数据库ID标示为主，另一台配置为2
binlog-do-db=test  #二进制需要同步的数据库名称
binlog-ignore-db=mysql  #避免同步mysql用户配置,以免不必要的麻烦
replicate-do-db=test  #同步数据库名称
replicate-ignore-db=mysql #屏蔽对mysql库同步
log-slave-updates
slave-skip-errors=all
sync_binlog=1
auto_increment_increment=2
auto_increment_offset=2  #另一配置为2

 分别重启 mysql服务

finder220

mysql> flush tables with read lock;
Query OK, 0 rows affected (0.00 sec)

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      120 | test         | mysql            |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

  

finder221

为了 File名称和 finder220不一样  我们先重启  

#  service mysqld restart

  

[root@finder221 ~]# mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.6.35-log MySQL Community Server (GPL)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> flush tables with read lock;
Query OK, 0 rows affected (0.00 sec)

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 |      120 | test         | mysql            |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

mysql> 

分别在2台服务器上用 change master语句指定同步位置

 at finder220

 刚刚我们锁定了表， 我们在执行语句先解锁表

mysql> unlock tables; 
Query OK, 0 rows affected (0.00 sec)

授权同步用

mysql> grant replication slave,file on *.* to 'repl1'@'192.168.1.221' identified by 'password$1';  
Query OK, 0 rows affected (0.00 sec)

执行change master

指定 从 192.168.1.221上 进行同步 ，同时指定 221 上的mysql-bin.000002 日志进行同步

mysql> change master to master_host='192.168.1.221',master_user='repl2',master_password='password$1',master_log_file='mysql-bin.000002',master_log_pos=120;
Query OK, 0 rows affected, 2 warnings (0.01 sec)

mysql> start slave;  启动从服务器线程
Query OK, 0 rows affected (0.00 sec)

查看 从服务状态

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Connecting to master
                  Master_Host: 192.168.1.221
                  Master_User: repl2
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 120
               Relay_Log_File: mysqld-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: test
          Replicate_Ignore_DB: mysql
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 120
              Relay_Log_Space: 120
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 2003
                Last_IO_Error: error connecting to master 'repl2@192.168.1.221:3306' - retry-time: 60  retries: 6
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 0
                  Master_UUID: 
             Master_Info_File: /var/lib/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 170403 20:46:19
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
1 row in set (0.00 sec)

 Slave_IO_Running : YES,Slave_SQL_Running :YES     说明启动成功  如果 状态为其他 ，那么可以先检查 防火墙是不是不允许 3306 端口通过

 列出防火墙允许的端口

firewall-cmd --zone=public --list-ports

[root@finder220 ~]# firewall-cmd --zone=public --add-port=3306/tcp --permanent
success
[root@finder220 ~]# firewall-cmd  --reload
success

BACKUP服务器

 

mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)

mysql> grant replication slave,file on *.* to 'repl2'@'192.168.1.220' identified by 'password$1';
Query OK, 0 rows affected (0.00 sec)

mysql> change master to master_host='192.168.1.220',master_user='repl1',master_password='password$1',master_log_file='mysql-bin.000001',master_log_pos=120;
Query OK, 0 rows affected, 2 warnings (0.00 sec)

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)


mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Connecting to master
                  Master_Host: 192.168.1.220
                  Master_User: repl1
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 120
               Relay_Log_File: mysqld-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: test
          Replicate_Ignore_DB: mysql
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 120
              Relay_Log_Space: 120
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 2003
                Last_IO_Error: error connecting to master 'repl1@192.168.1.220:3306' - retry-time: 60  retries: 1
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 0
                  Master_UUID: 
             Master_Info_File: /var/lib/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 170403 20:56:41
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
1 row in set (0.00 sec)

 Slave_IO_Running : YES,Slave_SQL_Running :YES 

至此 MYSQL 主-主 同步搭建完成。

 下面我们就可以 在 test 数据库中进行测试，看看是否可以同步了

。。。。。。。。。。。。。。。

 

keepalived 实现mysql HA