提供了编程的基础技术教程

zl程序教程

您现在的位置是：首页 >  数据库

当前栏目

mysql 主主复制（双主复制）+ 配置KEEPALIVED实现热备

mysql配置 实现 复制 Keepalived 热备 双主 主主
2023-09-14 09:00:10 时间

 

 

binlog-do-db和replicate-do-db表示需要同步的数据库
binlog-ignore-db和replicate-ignore-db表示不需要同步的数据库

 云端服务器为master配置如下:

[root@DB data]# egrep "server-id|log-bin|binlog-ignore-db|auto-increment-increment|auto-increment-offset|slave-skip-errors" /etc/my.cnf
log-bin=mysql-bin
binlog-ignore-db = mysql,information_schema
auto-increment-increment = 2
auto-increment-offset = 1
slave-skip-errors = all
server-id	= 1
#server-id       = 2
#log-bin=mysql-bin

 先查看下log bin日志和pos值位置:

[root@DB data]# mysql -uroot -p -e "show master status;"
Enter password: 
+------------------+----------+--------------+--------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB         |
+------------------+----------+--------------+--------------------------+
| mysql-bin.000016 |      107 |              | mysql,information_schema |
+------------------+----------+--------------+--------------------------+

 

[root@DB data]# service mysqld restart
Shutting down MySQL. SUCCESS! 
Starting MySQL... SUCCESS!

 

mysql> select user,password,host from user;
+--------+-------------------------------------------+-----------+
| user   | password                                  | host      |
+--------+-------------------------------------------+-----------+
| root   | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 | localhost |
| zabbix | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | %         |
+--------+-------------------------------------------+-----------+
2 rows in set (0.05 sec)

mysql> grant replication slave on *.* to 'rep'@'%' identified by 'replication';
Query OK, 0 rows affected (0.05 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> change master to
    -> master_host='192.168.1.99',
    -> master_user='rep',
    -> master_password='replication',
    -> master_log_file='mysql-bin.000016',
    -> master_log_pos=107;
Query OK, 0 rows affected (0.41 sec)

mysql> select user,password,host from user;
+--------+-------------------------------------------+-----------+
| user   | password                                  | host      |
+--------+-------------------------------------------+-----------+
| root   | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 | localhost |
| rep    | *D36660B5249B066D7AC5A1A14CECB71D36944CBC | %         |
| zabbix | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | %         |
+--------+-------------------------------------------+-----------+
3 rows in set (0.00 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

 

 backup配置如下:

mysql中有自增长字段，在做数据库的主主同步时需要设置自增长的两个相关配置：auto_increment_offset和auto_increment_increment。
    auto_increment_offset表示自增长字段从那个数开始，他的取值范围是1 .. 65535
    auto_increment_increment表示自增长字段每次递增的量，其默认值是1，取值范围是1 .. 65535
在主主同步配置时，需要将两台服务器的auto_increment_increment增长量都配置为2，而要把auto_increment_offset分别配置为1和2.

 

mysql> use mysql;
Database changed
mysql> select user,host,password from user;
+--------+-----------+-------------------------------------------+
| user   | host      | password                                  |
+--------+-----------+-------------------------------------------+
| root   | localhost | *5E9021B1C46435835981A94A7087707B3465144B |
| master | %         | *5E9021B1C46435835981A94A7087707B3465144B |
| rep    | %         | *5E9021B1C46435835981A94A7087707B3465144B |
+--------+-----------+-------------------------------------------+
3 rows in set (0.00 sec)

mysql> grant replication slave on *.* to 'rep'@'%' identified by "3306";

 

mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000023 |      184 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
mysql> change master to
    -> master_host='192.168.1.66',
    -> master_user='rep',
    -> master_password='3306',
    -> master_log_file='mysql-bin.000023',
    -> master_log_pos=184;
Query OK, 0 rows affected (0.29 sec)

mysql> start slave;
Query OK, 0 rows affected (0.01 sec)
mysql> show slave status;
  Slave_IO_Running: Yes
  Slave_SQL_Running: Yes

如果有如下报错及解决方案：

mysql> slave stop
    -> ;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> reset slave;
Query OK, 0 rows affected (0.18 sec)

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

mysql> show slave status;

 

  Slave_IO_Running: Yes
  Slave_SQL_Running: Yes

 

 

[root@DB-S data]# egrep "server-id|log-bin|binlog-ignore-db|auto-increment-increment|auto-increment-offset|slave-skip-errors" /etc/my.cnf
log-bin=mysql-bin
server-id = 2

 MASTER中新建张表;

mysql> use test;
Database changed
mysql> show tables;
Empty set (0.00 sec)

mysql> create table user (number INT(10),name VARCHAR(255));
Query OK, 0 rows affected (0.17 sec)

mysql> SHOW TABLES;
+----------------+
| Tables_in_test |
+----------------+
| user           |
+----------------+
1 row in set (0.01 sec)

 

 查看BACKUP是否同步：

mysql> use test;
Database changed
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| user           |
+----------------+
1 row in set (0.00 sec)

 在backup中插入一条数据：

mysql> insert into user values(01,'zhangsan');
Query OK, 1 row affected (0.11 sec)

mysql> select * from user;
+--------+----------+
| number | name     |
+--------+----------+
|      1 | zhangsan |
+--------+----------+
1 row in set (0.00 sec)

 查看MASTER是否同步成功：

mysql> select * from user;
+--------+----------+
| number | name     |
+--------+----------+
|      1 | zhangsan |
+--------+----------+
1 row in set (0.00 sec)
同步成功！

 在BACKUP端：

[root@DB  ~]# yum install -y pcre-devel openssl-devel popt-devel #安装依赖包

 

[root@DB ~]# which wget
/usr/bin/wget
[root@DB ~]# wget http://www.keepalived.org/software/keepalived-1.2.7.tar.gz
[root@DB ~]# ls
   keepalived-1.2.7.tar.gz
[root@DB ~]# cd keepalived-1.2.7
[root@DB keepalived-1.2.7]# pwd
/root/keepalived-1.2.7
[root@DB keepalived-1.2.7]# ./configure --prefix=/usr/local/keepalived
[root@DB keepalived-1.2.7]# make && make install

 

 

[root@DB keepalived-1.2.7]# cp /usr/local/keepalived/etc/rc.d/init.d/keepalived /etc/init.d/
[root@DB keepalived-1.2.7]# cp /usr/local/keepalived/etc/sysconfig/keepalived /etc/sysconfig/
[root@DB keepalived-1.2.7]# mkdir /etc/keepalived/
[root@DB keepalived-1.2.7]# cp /usr/local/keepalived/etc/keepalived/keepalived.conf /etc/keepalived/
[root@DB keepalived-1.2.7]# cp /usr/local/keepalived/sbin/keepalived /usr/sbin/

 

[root@DB keepalived]# cat /etc/keepalived/keepalived.conf
! Configuration File for keepalived

global_defs {
   router_id MYSQL_HA#标识，双主相同
}
vrrp_script check_run{
script '/usr/local/mysql/mysql_check.sh'
interval 300
}
vrrp_instance VI_1 {
    state BACKUP#两台都设置BACKUP
    interface eth0
    virtual_router_id 51#主备相同
    priority 100#优先级，backup设置90(最关建点之一，同为backup时优先级别越高，就会主动去抢VIP，所以如果不想让这种情况出现加参数nopreempt)
    advert_int 1#主备心跳检查间隔
    nopreempt #不主动抢占资源，只在master这台优先级高的设置，backup不设置(当master恢复时，不会把VIP抢过来)
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    virtual_ipaddress {
        192.168.1.74 #vip
    }
}

virtual_server 192.168.1.74 3306 {
    delay_loop 2
    #lb_algo rr#LVS算法，用不到，我们就关闭了
    #lb_kind NAT
    #nat_mask 255.255.255.0
    persistence_timeout 50#同一IP的连接60秒内被分配到同一台真实服务器
    protocol TCP

    real_server 192.168.1.66 3306 { #检测本地mysql，backup也要写检测本地mysql
        weight 3
	notify_down /usr/local/keepalived/mysql.sh #当mysq服down时，执行此脚本，杀死keepalived实现切换
	TCP_CHECK {
	connect_timeout 3#连接超时
	nb_get_retry 3#重试次数
	delay_before_retry 3#重试间隔时间
	}
}

 

[root@DB keepalived]# cat /usr/local/keepalived/mysql.sh 
#!/bin/bash
 
/usr/bin/pkill  keepalived

 backup端：

[root@DB-S ~]# cat /etc/keepalived/keepalived.conf
! Configuration File for keepalived

global_defs {
   notification_email {
     acassen@firewall.loc
     failover@firewall.loc
     sysadmin@firewall.loc
   }
   notification_email_from Alexandre.Cassen@firewall.loc
   smtp_server 192.168.200.1
   smtp_connect_timeout 30
   router_id MYSQL_HA
   #router_id LVS_DEVEL
}

vrrp_instance VI_1 {
    state BACKUP
    interface eth0
    virtual_router_id 51
    priority 90
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    virtual_ipaddress {
        192.168.1.74
    }
}

virtual_server 192.168.1.74 3306 {
    delay_loop 2
    #lb_algo rr
    #lb_kind NAT
    #nat_mask 255.255.255.0
    persistence_timeout 50
    protocol TCP

    real_server 192.168.1.99 3306 {
        weight 3
	notify_down /usr/local/keepalived/mysql.sh
	TCP_CHECK {
	connect_timeout 3
	nb_get_retry 3
	delay_before_retry 3
	}
}

 

[root@DB ~]# chmod +x /usr/local/keepalived/mysql.sh

 

[root@DB ~]# /etc/init.d/keepalived start

 #  授权两台DB服务器允许管理员登录，用于其它服务器登录测试！

mysql> grant all on *.* to 'admin'@'%' identified by '3306';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> select user,host,password from user;
+--------+-----------+-------------------------------------------+
| user   | host      | password                                  |
+--------+-----------+-------------------------------------------+
| U_R    | %         | *5E9021B1C46435835981A94A7087707B3465144B |
| root   | localhost | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
| admin  | %         | *5E9021B1C46435835981A94A7087707B3465144B |
| rep    | %         | *D36660B5249B066D7AC5A1A14CECB71D36944CBC |
| zabbix | %         | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+--------+-----------+-------------------------------------------+
5 rows in set (0.00 sec)

 通过MYSQL客户端通过VIP连接，看是否成功！

[root@bass ~]# mysql -uadmin -h192.168.1.74 -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 175
Server version: 5.5.40-log MySQL Community Server (GPL)

Copyright (c) 2000, 2013, 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>

 成功连接！！！！

可通MASTER或BACKUP的/var/log/messges日志，看出主备切换过程

 

[root@DB ~]# ip addr
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN 
    link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
    inet 127.0.0.1/8 scope host lo
    inet6 ::1/128 scope host 
       valid_lft forever preferred_lft forever
2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000
    link/ether 52:54:00:94:a0:9f brd ff:ff:ff:ff:ff:ff
    inet 192.168.1.66/24 brd 192.168.1.255 scope global eth0
    inet 192.168.1.74/32 scope global eth0
    inet6 fe80::5054:ff:fe94:a09f/64 scope link 
       valid_lft forever preferred_lft forever
[root@DB-S ~]# ip addr
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN 
    link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
    inet 127.0.0.1/8 scope host lo
    inet6 ::1/128 scope host 
       valid_lft forever preferred_lft forever
2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000
    link/ether 52:54:00:e4:6a:a4 brd ff:ff:ff:ff:ff:ff
    inet 192.168.1.99/24 brd 192.168.1.255 scope global eth0
    inet6 fe80::5054:ff:fee4:6aa4/64 scope link 
       valid_lft forever preferred_lft forever

 

 

[root@DB ~]# service mysqld stop
Shutting down MySQL.... SUCCESS! 
[root@DB ~]# ip addr
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN 
    link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
    inet 127.0.0.1/8 scope host lo
    inet6 ::1/128 scope host 
       valid_lft forever preferred_lft forever
2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000
    link/ether 52:54:00:94:a0:9f brd ff:ff:ff:ff:ff:ff
    inet 192.168.1.66/24 brd 192.168.1.255 scope global eth0
    inet 192.168.1.74/32 scope global eth0
    inet6 fe80::5054:ff:fe94:a09f/64 scope link 
       valid_lft forever preferred_lft forever
[root@DB-S ~]# ip addr
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN 
    link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
    inet 127.0.0.1/8 scope host lo
    inet6 ::1/128 scope host 
       valid_lft forever preferred_lft forever
2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000
    link/ether 52:54:00:e4:6a:a4 brd ff:ff:ff:ff:ff:ff
    inet 192.168.1.99/24 brd 192.168.1.255 scope global eth0
    inet 192.168.1.74/32 scope global eth0
    inet6 fe80::5054:ff:fee4:6aa4/64 scope link 
       valid_lft forever preferred_lft forever
#vip 在BACKUP上出现

 特别注意：mysql客户端不能在和双主服务器在一台上，否则VIP飘逸时，客户端连接会失败！

相关文章