Mysql-双主复制和keepalived

数据库 , 评论

Mysql-双主复制和keepalived

环境

1
2
3
4
test1  192.168.8.201 centos7 mysql5.7
test2  192.168.8.202  centos7 mysql5.7
虚拟vip  192.168.8.203  keepalived 1.2.13
xtrabackup 2.4

mysql安装配置

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
## 安装xtrabackup
rpm -ivh libev-4.15-7.el7.x86_64.rpm
yum install -y percona-xtrabackup-80-8.0.10-1.el7.x86_64.rpm
``
## 安装mysql
rpm -qa |grep maria
rpm -e mariadb-libs-5.5.60-1.el7_5.x86_64 --nodeps
yum install -y net-tools perl

rpm -ivh mysql-community-common-5.7.28-1.el7.x86_64.rpm
rpm -ivh mysql-community-libs-5.7.28-1.el7.x86_64.rpm
rpm -ivh mysql-community-libs-compat-5.7.28-1.el7.x86_64.rpm
rpm -ivh mysql-community-client-5.7.28-1.el7.x86_64.rpm
rpm -ivh mysql-community-server-5.7.28-1.el7.x86_64.rpm

systemctl enable mysqld
systemctl restart mysqld
cat /var/log/mysqld.log |grep password
mysql_secure_installation
mysql>use mysql;
mysql>GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '2014Picc!!!';

## mysql8
create user 'root'@'%' IDENTIFIED BY '2014Picc!!!';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%';

mysql>flush privileges;
mysql>quit

1号机开启binlog

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
vi /etc/my.cnf

log-bin=mysql-bin
##开启binlog
binlog_format=mixed
##binlog模式
server-id=1
##serverid,两台机不能相同
auto_increment_increment=2
##自增量步进，一般为服务器数量
auto_increment_offset=1
##自增量偏移量
replicate-do-db=monitor
##需复制数据库

## 重启
service mysql restart

1号机开启脚本，不停插入数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
#创建测试表
create database monitor;
use monitor;
create table moni(monidata datetime,host varchar(20));


[root@mysql1 mysqlbackup]# cat /aa.sh
export MYSQL_PWD="2014Picc!!!"
while true
do
mysql -u root -D monitor <<! 2>> aa.log 1>&2
insert into moni(monidata,host)
select now(),'A';
!
sleep 1
done

1号机全量备份，设置备份用户

1
2
GRANT RELOAD, LOCK TABLES, REPLICATION CLIENT,PROCESS,SUPER ON *.* TO 'bkpuser'@'localhost' IDENTIFIED BY '2014Picc!!!';
FLUSH PRIVILEGES;

全量备份

1
xtrabackup --defaults-file=/etc/my.cnf --host=localhost --port=3306 --user=bkpuser --password=2014Picc!!! --target-dir=/mysqlbackup/ --backup

从1号机拷贝数据导2号机

1
scp -r 2019-08-26_15-43-26 192.168.8.202:/backup

2号机停止mysql

1
service mysql stop

2号机清空mysqldata文件件

1
rm -rf /mysqldata/*

2号机查看备份的日志信息（同步时用到)

1
2
innobackupex --apply-log /backup/2019-08-26_16-37-20
InnoDB: xtrabackup: Last MySQL binlog file position 15398, file name mysql-bin.000003

2号机恢复备份

1
innobackupex --defaults-file=/etc/my.cnf --use-memory=1G --copy-back /backup/2019-08-26_16-37-20

恢复后更改属主

1
chown -R mysql:mysql /mysqldata

2号机开启binlog

1
2
3
4
5
6
7
8
9
10
11
12
log-bin=mysql-bin
##开启binlog
binlog_format=mixed
##binlog模式
server-id=2
##serverid,两台机不能相同
auto_increment_increment=2
##自增量步进，一般为服务器数量
auto_increment_offset=2
##自增量偏移量
replicate-do-db=monitor
##需复制数据库

重启

1
service mysql restart

查看表情况

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
mysql> select max(id) from moni;
+---------+
| max(id) |
+---------+
|    2689 |
+---------+
1 row in set (0.00 sec)


mysql>  select max(id) from moni;
+---------+
| max(id) |
+---------+
|    1935 |
+---------+
1 row in set (0.00 sec)

1号机创建复制用户

1
2
3
GRANT REPLICATION SLAVE ON *.* TO 'repuser'@'192.168.8.%' IDENTIFIED BY '2014Picc!!!';
FLUSH PRIVILEGES;
show master status;

2号机开启slave

1
2
3
change master to master_host='192.168.8.201', master_user='repuser', master_password='2014Picc!!!', master_log_file='mysql-bin.000003', master_log_pos=15398;
start slave;
show slave status\G

停掉插入脚本，对比两边数据状态

1
2
3
4
5
6
7
8
9
10
11
12
13
ps -ef |grep aa.sh |grep -v grep |awk '{print $2}'|xargs kill -9
mysql> select max(id) from moni;
+---------+
| max(id) |
+---------+
|    3369 |
+---------+
mysql> select max(id) from moni;
+---------+
| max(id) |
+---------+
|    3369 |
+---------+

同理，创建2号机到1号机的复制，2号机创建复制用户

1
2
GRANT REPLICATION SLAVE ON *.* TO 'repuser'@'192.168.8.%' IDENTIFIED BY '2014Picc';
FLUSH PRIVILEGES;

查看2号机日志状态

1
show master status;

1号机开启slave，并查看状态

1
2
3
4
change master to master_host='192.168.8.202', master_user='repuser', master_password='2014Picc', master_log_file='mysql-bin.000001', master_log_pos=434;
start slave;

show slave status\G

2号机开启插入脚本

1
2
3
4
5
6
7
8
9
10
[root@mysql1 mysqlbackup]# cat /bb.sh
export MYSQL_PWD="2014Picc"
while true
do
mysql -u root -D monitor <<! 2>> aa.log 1>&2
insert into moni(monidata,host)
select now(),'B';
!
sleep 1
done

停止1、2号机上的脚本

1
2
ps -ef |grep aa.sh |grep -v grep |awk '{print $2}'|xargs kill -9
ps -ef |grep bb.sh |grep -v grep |awk '{print $2}'|xargs kill -9

对比数据状态，数据一致

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
mysql> select host,count(1) from moni group by host;
+------+----------+
| host | count(1) |
+------+----------+
| A    |     1038 |
| B    |       84 |
+------+----------+
2 rows in set (0.00 sec)
mysql>  select host,count(1) from moni group by host;
+------+----------+
| host | count(1) |
+------+----------+
| A    |     1038 |
| B    |       84 |
+------+----------+
2 rows in set (0.01 sec)

keepalived配置

安装（1，2号机）

1
rpm -ivh keepalived-1.2.13-5.el6_6.x86_64.rpm

1号机配置

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
[root@mysql1 haproxy15_keepalived12_el6_rpm]# cat /etc/keepalived/keepalived.conf
! Configuration File for keepalived
global_defs {
    router_id rr1
}
vrrp_script check_mysql {
    script "/root/check_mysql.sh"
    interval 5
}
vrrp_sync_group VG1 {
     group {
       VI_1
     }
}
vrrp_instance VI_1 {
    state MASTER
    interface eth0
    virtual_router_id 51
    priority 100
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 2014Picc
    }
    virtual_ipaddress {
        192.168.8.199
    }
    track_script {
       check_mysql
    }
}

2号机配置

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
[root@mysql2 ~]# cat /etc/keepalived/keepalived.conf
! Configuration File for keepalived
global_defs {
    router_id rr2
}
vrrp_script check_mysql {
    script "/root/check_mysql.sh"
    interval 5
}
vrrp_sync_group VG1 {
     group {
       VI_1
     }
}
vrrp_instance VI_1 {
    state BACKUP
    interface eth1
    virtual_router_id 51
    priority 50
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 2014Picc
    }
    virtual_ipaddress {
        192.168.8.199
    }
    track_script {
    check_mysql
    }
}

1，2号机配置脚本

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
[root@mysql2 ~]# cat /root/check_mysql.sh
#!/bin/bash
MYSQL=/usr/bin/mysql
MYSQL_HOST=localhost
MYSQL_USER=root
export MYSQL_PWD="2014Picc"
CHECK_TIME=3
#mysql  is working MYSQL_OK is 1 , mysql down MYSQL_OK is 0
MYSQL_OK=1
function check_mysql_health (){
$MYSQL -h $MYSQL_HOST -u $MYSQL_USER -e "show status;" >/dev/null 2>&1
if [ $? = 0 ] ;then
     MYSQL_OK=1
else
     MYSQL_OK=0
fi
     return $MYSQL_OK
}
while [ $CHECK_TIME -ne 0 ]
do
     let "CHECK_TIME-=1"
     check_mysql_health
     if [ $MYSQL_OK = 1 ] ; then
          CHECK_TIME=0
          exit 0
     fi
     if [ $MYSQL_OK -eq 0 ] &&  [ $CHECK_TIME -eq 0 ]
     then
          /etc/init.d/keepalived stop
     exit 1
     fi
     sleep 1
done

1，2号机启动keepalived

1
service keepalived start

测试，用vip连接数据库，查看实例id

1
2
3
4
5
6
7
8
9
mysql -h 192.168.8.199 -u root -p
mysql> show variables like '%server_id%';
+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| server_id      | 1     |
| server_id_bits | 32    |
+----------------+-------+
2 rows in set (0.00 sec)

关掉1号机mysql

1
service mysql stop

重新登录查询，已成功切换

1
2
3
4
5
6
7
8
9
mysql -h 192.168.8.199 -u root -p
mysql> show variables like '%server_id%';
+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| server_id      | 2     |
| server_id_bits | 32    |
+----------------+-------+
2 rows in set (0.00 sec)

重启1号机mysql、keepalived

1
2
3
4
[root@mysql1 haproxy15_keepalived12_el6_rpm]# service mysql start
Starting MySQL.                                            [  OK  ]
[root@mysql1 haproxy15_keepalived12_el6_rpm]# service keepalived start
Starting keepalived:                                       [  OK  ]

再次登录查询，已成功回切

1
2
3
4
5
6
7
8
9
mysql -h 192.168.8.199 -u root -p
mysql> show variables like '%server_id%';
+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| server_id      | 1     |
| server_id_bits | 32    |
+----------------+-------+
2 rows in set (0.00 sec)

biglovewheat老年佛系运维

老年佛系运维 | biglovewheat@126.com