Centos7上mysql双主集群+keepalived部署

一、前期准备

1.1环境及提要

系统架构

192.168.100.101：centos7.9：4CPU、8G、200G 原单节点mysql数据库

192.168.100.102：centos7.9：4CPU、8G、200G 新建mysql数据库集群节点+keepalived节点

192.168.100.103：centos7.9：4CPU、8G、200G 新建mysql数据库集群节点+keepalived节点

192.168.100.110：是102和103keepalived配置的虚拟VIP

提要

由于centos7使用yum安装keepalived时会依赖自带的mariadb，而部署mysql会与mariadb冲突，所以需要使用源码安装keepalived解决冲突问题

1.2软件清单

mysql8.30下载地址：https://downloads.mysql.com/archives/community/

keepalived2.0.20下载地址：https://www.keepalived.org/download.html

二、mysql双主集群部署

2.1mysql数据库部署

需要在192.168.100.102/103都进行部署，以102为例

卸载自带的mariadb

[root@localhost data]# rpm -qa | grep mariadb
mariadb-libs-5.5.68-1.el7.x86_64
[root@localhost data]# yum -y remove mariadb-libs-5.5.68-1.el7.x86_64

安装mysql

[root@localhost data]# tar -xvf mysql-8.0.30-linux-glibc2.12-x86_64.tar.xz
[root@localhost data]# mv mysql-8.0.30-linux-glibc2.12-x86_64 /usr/local/mysql
[root@localhost data]# groupadd mysql
[root@localhost data]# useradd -r -g mysql -s /sbin/nologin mysql
[root@localhost data]# mkdir /data/mysql -p
[root@localhost data]# mkdir /usr/local/mysql/etc -p
[root@localhost data]# mkdir /var/run/mysql/
[root@localhost data]# chown -R mysql:mysql /usr/local/mysql/
[root@localhost data]# chown -R mysql:mysql /data/mysql
[root@localhost data]# chown -R mysql:mysql /var/run/mysql/
[root@localhost data]# echo "d /var/run/mysql 0755 mysql mysql -" > /usr/lib/tmpfiles.d/mysql.conf
[root@localhost data]# vim /usr/local/mysql/etc/my.cnf
[client]
port=3306
socket=/var/run/mysql/mysql.sock

[mysqld]
user=mysql
port=3306
bind-address=0.0.0.0
datadir=/data/mysql
basedir=/usr/local/mysql
socket=/var/run/mysql/mysql.sock
pid-file=/var/run/mysql/mysql.pid

default-storage-engine=INNODB
character_set_server=utf8mb4
lower_case_table_names=1
table_open_cache=128
max_connections=1200
max_connect_errors=1000
innodb_file_per_table=1
innodb_buffer_pool_size=1G
max_allowed_packet=64M
transaction_isolation=READ-COMMITTED
innodb_flush_method=O_DIRECT
innodb_lock_wait_timeout=1800
innodb_flush_log_at_trx_commit=0
sync_binlog=0
symbolic-links=0
explicit_defaults_for_timestamp=true

log-error=/data/mysql/mysql.err
expire_logs_days = 7
binlog_format=mixed
slow_query_log=1
long_query_time=10
slow_query_log_file=/data/mysql/mysql-slow.log

sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

skip-name-resolve

# 配置server-id 每个MySQL实例的server-id都不能相同，设置102为1,103为2
server-id=1
# MySQL的日志文件的名字，102为mysql_master，103为mysql_slave
log-bin=mysql_master
# 作为从库时 更新操作是否写入日志 on：写入  其他数据库以此数据库做主库时才能进行同步
log-slave-updates=on
# MySQL系统库的数据不需要同步 我们这里写了3个  更加保险
# 同步数据时忽略一下数据库 但是必须在使用use db的情况下才会忽略；如果没有使用use db 比如create user  数据还是会同步的
replicate-ignore-db=information_schema
replicate-ignore-db=mysql
replicate-ignore-db=performance_schema
replicate-ignore-db=sys
# 使用通配符忽略MySQL系统库的表  这样在create user时也不会进行同步了
replicate_wild_ignore_table=information_schema.%
replicate_wild_ignore_table=mysql.%
replicate_wild_ignore_table=performance_schema.%
replicate_wild_ignore_table=sys.%
# MySQL系统库的日志不计入binlog 这样更加保险了
binlog-ignore-db=information_schema
binlog-ignore-db=mysql
binlog-ignore-db=performance_schema
binlog-ignore-db=sys

[mysql]
default-character-set=utf8mb4

[mysql.server]
default-character-set=utf8mb4

初始化数据库

[root@localhost data]# cd /usr/local/mysql/bin
[root@localhost bin]# ./mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql --lower-case-table-names=1

查看临时密码，配置环境变量

[root@localhost bin]# tailf /data/mysql/mysql.err
[root@localhost bin]# cp /usr/local/mysql/support-files/mysql.server  /etc/init.d/mysqld
[root@localhost bin]# /etc/init.d/mysqld start
Starting MySQL... SUCCESS! 
[root@localhost bin]# vim /etc/profile
PATH=$PATH:$HOME/bin:/usr/local/mysql/bin
[root@localhost bin]# source /etc/profile
[root@localhost bin]# chkconfig --add mysqld
[root@localhost bin]# vim /etc/rc.d/rc.local
末尾添加/etc/init.d/mysqld start

[root@localhost bin]# mysql -uroot -p
mysql: error while loading shared libraries: libtinfo.so.5: cannot open shared object file: No such file or directory

ln -s /usr/lib64/libtinfo.so.6.1 /usr/lib64/libtinfo.so.5

修改密码并赋权

[root@localhost bin]# mysql -uroot -p
alter user 'root'@'localhost' identified by 'MYPASSWORD';
use mysql;
create user 'root'@'%' identified by 'MYPASSWORD';
grant all privileges on *.* to 'root'@'%' with grant option;

2.2配置主从模式

先配置102为主，103为从

登录102mysql操作：

# 创建备份的账号repl_user
CREATE USER 'repl_user'@'%' IDENTIFIED WITH MYSQL_NATIVE_PASSWORD BY 'MYPASSWORD';
#赋权
GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'%';
#刷新权限
FLUSH PRIVILEGES;
#查看主节点状态
SHOW MASTER STATUS;
+---------------------+----------+--------------+-------------------------------------------------+
| File                | Position | Binlog_Do_DB | Binlog_Ignore_DB                                | 
+---------------------+----------+--------------+-------------------------------------------------+
| mysql_master.000002 |      476 |              | information_schema,mysql,performance_schema,sys |         
+---------------------+----------+--------------+-------------------------------------------------+
1 row in set (0.00 sec)
#记住binlog文件的名字也就是File：mysql_master.000002 ，和位置Position也就是476。

登录103mysql操作

#设置102为主库
CHANGE MASTER TO MASTER_HOST='10.10.3.202',MASTER_PORT=3306,MASTER_USER='repl_user',MASTER_PASSWORD='MYPASSWORD',MASTER_LOG_FILE='mysql_master.000002',MASTER_LOG_POS=476;
#开启从库
START SLAVE;
#查看从库状态Slave_IO_Running: Yes和Slave_SQL_Running: Yes表示主从正常通信
SHOW SLAVE STATUS\G;

再配置103为主，102为从

登录103mysql操作

# 创建备份的账号repl_user
CREATE USER 'repl_user'@'%' IDENTIFIED WITH MYSQL_NATIVE_PASSWORD BY 'MYPASSWORD';
#赋权
GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'%';
#刷新权限
FLUSH PRIVILEGES;
#查看主节点状态
SHOW MASTER STATUS;
+--------------------+----------+--------------+-------------------------------------------------+
| File               | Position | Binlog_Do_DB | Binlog_Ignore_DB                                | 
+--------------------+----------+--------------+-------------------------------------------------+
| mysql_slave.000002 |      478 |              | information_schema,mysql,performance_schema,sys |           
+--------------------+----------+--------------+-------------------------------------------------+
1 row in set (0.00 sec)
#我们要记住binlog文件的名字也就是File：mysql_slave.000002  ，和位置Position也就是478
exit;

登录102mysql

#设置102为主库
CHANGE MASTER TO MASTER_HOST='10.10.3.204',MASTER_PORT=3306,MASTER_USER='repl_user',MASTER_PASSWORD='MYPASSWORD',MASTER_LOG_FILE='mysql_slave.000002',MASTER_LOG_POS=478;
#开启从库
START SLAVE;
#查看从库状态Slave_IO_Running: Yes和Slave_SQL_Running: Yes表示主从正常通信
SHOW SLAVE STATUS\G;
exit;

完成mysql双主集群的配置

三、keepalived部署

在102和103上都需要进行部署，部署过程相同，以102为例

[root@localhost bin]# cd /data/
安装依赖（libnl libnl-devel是支持ipv6的，可选）
[root@localhost data]#  yum install -y openssl-devel libnfnetlink-devel libnl libnl-devel
[root@localhost data]# tar -zvxf keepalived-2.0.20.tar.gz
[root@localhost data]# cd keepalived-2.0.20
[root@localhost keepalived-2.0.20]# ./configure --prefix=/usr/local/keepalived
[root@localhost keepalived-2.0.20]# make && make install
[root@localhost keepalived-2.0.20]# cp keepalived/etc/init.d/keepalived /etc/rc.d/init.d/
[root@localhost keepalived-2.0.20]# cp /usr/local/keepalived/etc/sysconfig/keepalived /etc/sysconfig/

编辑配置文件（生产环境建议使用非抢占模式，所以都配置成BACKUP，优先级也一样）

[root@localhost keepalived-2.0.20]# mkdir /etc/keepalived/
[root@localhost keepalived-2.0.20]# vim /etc/keepalived/keepalived.conf
# 全局配置 不用动  只需注释掉vrrp_strict
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 LVS_DEVEL
   vrrp_skip_check_adv_addr
   #必须注释掉 否则报错
   #vrrp_strict
   vrrp_garp_interval 0
   vrrp_gna_interval 0
}

# 检查mysql服务是否存活的脚本
vrrp_script chk_mysql {
    script "/usr/bin/killall -0 mysqld"
}
# vrrp配置虚IP
vrrp_instance VI_1 {
    # 状态：均为BACKUP
    state BACKUP
    # 绑定的网卡
    interface ens33
    # 虚拟路由id  两台机器需保持一致
    virtual_router_id 51
    # 优先级 MASTER的值要大于BACKUP
    priority 100
    #开启非抢占模式
    nopreempt
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    # 虚拟IP地址 两台keepalived需要一致
    virtual_ipaddress {
        10.10.3.209
    }
    # 检查脚本 vrrp_script的名字
    track_script {
        chk_mysql
    }
}
###后边的virtual_server全部注释掉 它是和LVS做负载均衡用的  这里用不到
###

[root@localhost keepalived-2.0.20]# cp /usr/local/keepalived/sbin/keepalived /usr/sbin/

更改PIDFile地址，不然启动会报错

[root@localhost keepalived-2.0.20]# vim /lib/systemd/system/keepalived.service
[Unit]
Description=LVS and VRRP High Availability Monitor
After=syslog.target network-online.target
 
[Service]
Type=forking
PIDFile=/var/run/keepalived.pid
KillMode=process
EnvironmentFile=-/usr/local/keepalived/etc/sysconfig/keepalived
ExecStart=/usr/local/keepalived/sbin/keepalived $KEEPALIVED_OPTIONS
ExecReload=/bin/kill -HUP $MAINPID
 
[Install]
WantedBy=multi-user.target

安装psmisc

[root@localhost keepalived-2.0.20]# yum search killall
[root@localhost keepalived-2.0.20]# yum install psmisc -y

启动并加入开机自启

[root@localhost keepalived-2.0.20]# systemctl start keepalived && systemctl enable keepalived
[root@localhost keepalived-2.0.20]# echo "/etc/init.d/keepalived start" >> /etc/rc.local
[root@localhost keepalived-2.0.20]# ip a|grep 110
    inet 192.168.100.110/32 scope global ens33

四、数据迁移

讲101数据库上的数据迁移到VIP110上

步骤如下:

4.1先在110上创建需要的用户并配置权限，创建需要的database

4.2在101上将需要使用迁移的database分别导出成sql文件

4.3在110上进入对应的database，导入对应的sql文件