MySQL 高可用解决方案(双主双从)

最新推荐文章于 2024-05-06 11:34:09 发布

十二同学啊

最新推荐文章于 2024-05-06 11:34:09 发布

阅读量1.1k

点赞数 19

分类专栏：关系型数据库文章标签： mysql 数据库

本文链接：https://blog.csdn.net/I_Am_Your_God52/article/details/136218036

版权

关系型数据库专栏收录该内容

5 篇文章 0 订阅

订阅专栏

1.环境说明

操作系统：centos7.7
主服务器：node2(192.168.1.102)
从服务器：node3(192.168.1.103)
keepalived中虚拟ip(VIP):192.168.1.100

2.准备事项

主库和从库数据库的版本一致
把主库的数据同步给从库一份

#对主库进行全局读锁定
FLUSH TABLES WITH READ LOCK ;

#在主服务器上把数据导出
mysqldump -udolphinscheduler -p dolphinscheduler_new > dolphinscheduler_new.sql

#在从服务器上进行数据导入
mysql -u root -p dolphinscheduler_new < dolphinscheduler_new.sql

3.主库配置

3.1 编辑MySQL配置文件,开启二进制日志

Mysql：

[root@node2 /]# vim /etc/my.cnf

Mariadb：

[root@node2 /]# vim /etc/my.cnf.d/server.cnf

在下面添加如下配置

binlog-do-db=testdb
binlog-ignore-db=mysql
#启用二进制日志
log-bin=mysql-bin
#服务器唯一ID，与从数据库不重复即可
server-id=2

说明：binlog-do-db 需要同步的数据库 binlog-ignore-db 忽略同步的数据库

如果需要同步两个库的话,需要在添加一行

binlog-do-db=testdb2

3.2 重启mysql服务

Mysql：

[root@node2 /]# systemctl restart mysqld

Mariadb：

[root@node2 /]# systemctl restart mariadb

3.3 建立帐户并授权slave

[root@node2 /]# mysql -u root -proot

查看mysql的binlog是否开启

mysql> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON    |
+---------------+-------+
1 row in set (0.00 sec)

赋予用户FILE 权限，FILE 权限允许用户执行文件操作，如导入和导出数据。

mysql> GRANT FILE ON *.* TO 'root'@'%' IDENTIFIED BY 'root';

REPLICATION SLAVE 权限允许用户作为复制从服务器连接到主服务器并读取二进制日志，REPLICATION CLIENT 权限允许用户监控和管理复制过程

mysql> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* to 'root'@'%' identified by 'root';

刷新权限

mysql> FLUSH PRIVILEGES;

3.4 查询master的状态

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

注意：File 与Position参数需要记录下来,从数据库配置需要使用

4.从数据库配置

4.1 添加mysql配置

[root@node3 mysql]# vim /etc/my.cnf

在下面添加如下配置

server-id=3

说明：从数据库只需要配置server-id 即可，从库的server-id和主库的server-id必须不一致

4.2 重启mysql服务

[root@node3 mysql]# systemctl restart mysqld

4.3 配置从库

[root@node3 mysql]# mysql -u root -proot

mysql>change master to master_host='192.168.1.102',master_port=3306,master_user='root',master_password='root',master_log_file='mysql-bin.000001',master_log_pos=863;

说明：
master_port: mysql服务器端口号
master_user: 执行同步操作的数据库账户
master_password: 同步账号的密码
master_log_pos: 863 就是上面3.4中主数据库中 show master status 中的position对应的值
master_log_file: mysql-bin.000001 就是上面3.4中主数据库中 show master status中的file对应的值

4.4 启动从库复制功能

mysql> start slave;

4.5 检查从库复制功能状态

mysql> show slave status \G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.1.102
                  Master_User: root
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 863
               Relay_Log_File: node3-relay-bin.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           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: 863
              Relay_Log_Space: 527
              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: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 2
                  Master_UUID: 393a9867-cc77-11ed-b111-000c29991e40
             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 more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

注意：Slave_IO_Running和Slave_SQL_Running这两个服务必须保证正常
至此，Mysql主从复制的配置已完成，可进行测试

5.双主双从配置

把从库当作主库，在从库(node3)上面执行主库(node2)的操作配置，以上第3节
把主库当作从库，在主库(node2)上面执行从库(node3)的操作配置，以上第4节

6.高可用配置

引入了keepalived，keepalived通过VIP虚拟IP的漂移实现高可用，在相同集群内发送组播包，master主通过VRRP协议发送组播包，告诉从主的状态。
当我们通过keepalived虚拟出一个ip服务192.168.1.100，应用服务只需要将mysql配置信息更改为192.168.1.100的配置信息，192.168.1.100实际代理的实体服务器为192.168.1.102或192.168.1.103。
当192.168.1.100实际代理192.168.1.102时，192.168.1.102服务出现故障后，keepalived通过自身机制，自动将192.168.1.103作为实际代理主服务器，不需要人工干预去修改mysql配置信息，从而实现了高可用

6.1 keepalived部署安装（主从操作一样）

[root@node2 software]$ wget -q http://www.keepalived.org/software/keepalived-1.2.13.tar.gz
[root@node2 software]$ tar xf keepalived-1.2.13.tar.gz
[root@node2 software]$ yum install -y gcc openssl-devel popt-devel
[root@node2 software]$ cd keepalived-1.2.13
[root@node2 keepalived-1.2.13]# ./configure && make && make install
[root@node2 keepalived-1.2.13]# cp /usr/local/etc/rc.d/init.d/keepalived /etc/rc.d/init.d/
[root@node2 keepalived-1.2.13]# cp /usr/local/etc/sysconfig/keepalived /etc/sysconfig/
[root@node2 keepalived-1.2.13]# mkdir /etc/keepalived
[root@node2 keepalived-1.2.13]# cp /usr/local/etc/keepalived/keepalived.conf /etc/keepalived/
[root@node2 keepalived-1.2.13]# cp /usr/local/sbin/keepalived /usr/sbin/
[root@node2 keepalived-1.2.13]# chkconfig --add keepalived
[root@node2 keepalived-1.2.13]# chkconfig --level 345 keepalived on

6.2 keepalived.conf配置

[root@node2 keepalived-1.2.13]# vim /etc/keepalived/keepalived.conf

6.2.1 主库配置

global_defs {
   router_id Nginx_HA_1
}

vrrp_script check_run {
    script "/home/mysql/mysql_check.sh"
    interval 60
    fall 3
    rise 1
}

vrrp_sync_group VG1 {
    group {
        VI_1
    }
}

vrrp_instance VI_1 {
    state MASTER
    interface ens32
    virtual_router_id 12
    priority 100
    advert_int 1
    nopreempt
    #本机ip
unicast_src_ip 192.168.1.102
unicast_peer {
    #对象IP  发送vrrp包给备服务器
    192.168.1.103
}

    authentication {
        auth_type PASS
        auth_pass nginxha
     }
    track_script {
         check_run
    }
    virtual_ipaddress {
        192.168.1.100
    }
}

6.2.2 从库配置

global_defs {
   router_id Nginx_HA_1
}

vrrp_script check_run {
    script "/home/mysql/mysql_check.sh"
    interval 30
    rise 1
    fall 3
}

vrrp_sync_group VG1 {
    group {
        VI_1
    }
}

vrrp_instance VI_1 {
    state BACKUP
    interface ens32
    virtual_router_id 12
    priority 50
    advert_int 1
    nopreempt
    #本机ip
unicast_src_ip 192.168.1.103
unicast_peer {
    #对象IP  发送vrrp包给备服务器
    192.168.1.102
}

    authentication {
        auth_type PASS
        auth_pass nginxha
     }
    track_script {
         check_run
    }
    virtual_ipaddress {
        192.168.1.100
    }
}

说明：
1.interface ens32 ->ens32是linux网卡名称;
2.主服务 priority 参数必须大于从服务;
3.script “/home/mysql/mysql_check.sh” mysql_check.sh是检测mysql是否正常的脚本如果不正常就关闭keepalived;
4.interval 60：定义了检查脚本执行的时间间隔，此处为60秒。意味着每隔60秒执行一次脚本检查;
5.fall 3：指定在多少次检查失败后认定服务故障切换到备用服务器;
6.rise 1：指定在多少次检查成功后切回到主服务器,表示在连续1次检查成功后才会将服务恢复到主服务器。

6.3 编写检查Mysql脚本(mysql_check.sh)

[root@node2 keepalived-1.2.13]# vim /home/mysql/mysql_check.sh

#!/bin/bash
counter=$(netstat -na|grep "LISTEN"|grep "3306"|wc -l)
if [ "${counter}" -eq 0 ]; then
    service keepalived stop
fi

主服务器和从服务器节点都要一份

#授权与格式转换
[root@node2 ~]# chmod -R 777  /home/mysql/mysql_check.sh    
[root@node2 ~]# sed -i "s/\r//" /home/mysql/mysql_check.sh

6.4 启动并测试

主从都启动

[root@node2 ~]# systemctl start keepalived

启动后可以进行状态查看

[root@node2 ~]# service keepalived status
● keepalived.service - SYSV: Start and stop Keepalived
   Loaded: loaded (/etc/rc.d/init.d/keepalived; bad; vendor preset: disabled)
   Active: active (running) since 一 2024-02-26 22:18:06 CST; 14s ago
     Docs: man:systemd-sysv-generator(8)
  Process: 2901 ExecStart=/etc/rc.d/init.d/keepalived start (code=exited, status=0/SUCCESS)
 Main PID: 2908 (keepalived)
   CGroup: /system.slice/keepalived.service
           ├─2908 keepalived -D
           ├─2910 keepalived -D
           └─2911 keepalived -D

查看vip绑定到哪台机器上

[root@node2 /]# ip addr
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1000
    link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
    inet 127.0.0.1/8 scope host lo
       valid_lft forever preferred_lft forever
    inet6 ::1/128 scope host 
       valid_lft forever preferred_lft forever
2: ens32: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000
    link/ether 00:0c:29:99:1e:40 brd ff:ff:ff:ff:ff:ff
    inet 192.168.1.102/24 brd 192.168.1.255 scope global noprefixroute ens32
       valid_lft forever preferred_lft forever
    inet 192.168.1.100/32 scope global ens32
       valid_lft forever preferred_lft forever
    inet6 fe80::348f:8ac3:b2c:809d/64 scope link noprefixroute 
       valid_lft forever preferred_lft forever

[root@node3 ~]# ip addr
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1000
    link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
    inet 127.0.0.1/8 scope host lo
       valid_lft forever preferred_lft forever
    inet6 ::1/128 scope host 
       valid_lft forever preferred_lft forever
2: ens32: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000
    link/ether 00:0c:29:8e:59:08 brd ff:ff:ff:ff:ff:ff
    inet 192.168.1.103/24 brd 192.168.1.255 scope global noprefixroute ens32
       valid_lft forever preferred_lft forever
    inet6 fe80::d26d:7d7a:6daa:f99b/64 scope link noprefixroute 
       valid_lft forever preferred_lft forever