构建一个高可用的MySQL主从复制集群

目录

一、使用一键安装脚本，在两台机器上分别部署好二进制的MySQL，并设置好f的内容（一键安装脚本中包含了）

one_key_install.sh

二、在master服务器上开启bin_log并配置server_id,在从服务器上配置server_id

1.开启bing_log

2.配置binlog参数并配置server_id

3.重启mysql

三、在master上创建给slave过来复制二进制日志的用户并授权

四、在master上做一个全备，导入数据到slave上，保持master和slave上的数据一致

1.先导出master所有数据库数据导出为alldb.sql

2.将导出的数据拷贝到slave，这里使用的scp命令

3.将alldb.sql导入slave使用命令

五、在slave上配置去master上拉取二进制日志文件

1.master原有databases

2.slave上的databases（以下四个数据库是自带的）

3.从机上配置需要复制的主机

六、在master和slave上都关闭防火墙和selinux

关闭防火墙

关闭selinux

七、在slave服务器上启动slave服务，查看IO线程和SQL线程是否成功启动

开启slave

八、验证主从复制效果

九、为主从复制集群做高可用，进行Keepalived的双VIP配置

1.在router3(master)和router(backup)都安装 keepalived；

2.修改配置文件；

十、添加GTID，配置mysql-router、读写分离

添加GTID配置

slave连接到master

配置mysql-router

一、使用一键安装脚本，在两台机器上分别部署好二进制的MySQL，并设置好f的内容（一键安装脚本中包含了）

one_key_install.sh

#!/bin/bash#解决软件的依赖关系yum install cmake ncurses-devel gcc gcc-c++ vim lsof bzip2 openssl-devel ncurses-compat-libs -y#解压mysql二进制安装包tar xf mysql-5.7.37-linux-glibc2.12-x86_64.tar.gz#移动mysql解压后的文件到/usr/local下改名叫mysqlmv mysql-5.7.37-linux-glibc2.12-x86_64 /usr/local/mysql#新建组和用户 mysqlgroupadd mysql#mysql这个用户的shell 是/bin/false 属于mysql组 useradd -r -g mysql -s /bin/false mysql#关闭firewalld防火墙服务，并且设置开机不要启动service firewalld stopsystemctl disable firewalld#临时关闭selinuxsetenforce 0#永久关闭selinuxsed -i '/^SELINUX=/ s/enforcing/disabled/' /etc/selinux/config#新建存放数据的目录mkdir /data/mysql -p#修改/data/mysql目录的权限归mysql用户和mysql组所有，这样mysql用户可以对这个文件夹进行读写了chown mysql:mysql /data/mysql/#只是允许mysql这个用户和mysql组可以访问，其他人都不能访问chmod 750 /data/mysql/#进入/usr/local/mysql/bin目录cd /usr/local/mysql/bin/#初始化mysql./mysqld --initialize --user=mysql --basedir=/usr/local/mysql/ --datadir=/data/mysql &>passwd.txt#让mysql支持ssl方式登录的设置./mysql_ssl_rsa_setup --datadir=/data/mysql/#获得临时密码tem_passwd=$(cat passwd.txt |grep "temporary"|awk '{print $NF}')#$NF表示最后一个字段# abc=$(命令) 优先执行命令，然后将结果赋值给abc # 修改PATH变量，加入mysql bin目录的路径#临时修改PATH变量的值export PATH=/usr/local/mysql/bin/:$PATH#重新启动linux系统后也生效，永久修改echo 'PATH=/usr/local/mysql/bin:$PATH' >>/root/.bashrc#复制support-files里的mysql.server文件到/etc/init.d/目录下叫mysqldcp ../support-files/mysql.server /etc/init.d/mysqld#修改/etc/init.d/mysqld脚本文件里的datadir目录的值sed -i '70c datadir=/data/mysql' /etc/init.d/mysqld#生成/etc/f配置文件cat >/etc/f <<EOF[mysqld_safe][client]socket=/data/mysql/mysql.sock[mysqld]socket=/data/mysql/mysql.sockport = 3306open_files_limit = 8192innodb_buffer_pool_size = 512Mcharacter-set-server=utf8[mysql]auto-rehashprompt=\\u@\\d \\R:\\m mysql>EOF#修改内核的open file的数量ulimit -n 1000000#设置开机启动的时候也配置生效echo "ulimit -n 1000000" >>/etc/rc.localchmod +x /etc/rc.d/rc.local#启动mysqld进程service mysqld start#将mysqld添加到linux系统里服务管理名单里/sbin/chkconfig --add mysqld#设置mysqld服务开机启动/sbin/chkconfig mysqld on#初次修改密码需要使用--connect-expired-password 选项#-e 后面接的表示是在mysql里需要执行命令 execute 执行#set password='123456'; 修改root用户的密码为123456mysql -uroot -p$tem_passwd --connect-expired-password -e "set password='123456';"#检验上一步修改密码是否成功，如果有输出能看到mysql里的数据库，说明成功。mysql -uroot -p'123456' -e "show databases;"

二、在master服务器上开启bin_log并配置server_id,在从服务器上配置server_id

1.开启bing_log

查看是否开启

root@(none) 10:11 mysql>show variables like '%log_bin%';+---------------------------------+-------+| Variable_name | Value |+---------------------------------+-------+| log_bin| OFF || log_bin_basename| || log_bin_index | || log_bin_trust_function_creators | OFF || log_bin_use_v1_row_events | OFF || sql_log_bin | ON |+---------------------------------+-------+6 rows in set (0.00 sec)

其中log_bin 显示OFF，表示未开启，需要手动开启

查找linux系统上的mysql可执行程序所在目录

[root@localhost mysql]# which mysql/usr/local/mysql/bin/mysql

我这里显示是在/usr/local/mysql/bin/mysql目录下

[root@localhost mysql]# /usr/local/mysql/bin/mysql --verbose --help | grep -A 1 'Default options'Default options are read from the following files in the given order:/etc/f /etc/mysql/f /usr/local/mysql/etc/f ~/.f

根据刚刚查找到的mysql可执行程序目录，查找mysql配置文件所在目录，查找后会输出多个目录，mysql按照顺序中这些文件中读取配置，如果上一个配置文件不存在则会去读取下一个，依次类推。

尝试查看第一个配置文件/etc/f的内容，刚刚使用一键安装脚本安装的时候创建了，因此就在这个文件中设置log_bin，设置之前要先查看MySQL版本（我这里是5.7.37）

查看数据库版本

root@(none) 10:20 mysql>select version();+-----------+| version() |+-----------+| 5.7.37 |+-----------+1 row in set (0.00 sec)

2.配置binlog参数并配置server_id

vim /etc/f在[mysqld]下添加

#开启并指定二进制日志前缀log_bin=mysql-bin#唯一idserver_id=11234#二进制日志保存时间expire_logs_days=7binlog_format=ROW

修改之后的文件

[mysqld_safe][client]socket=/data/mysql/mysql.sock[mysqld]socket=/data/mysql/mysql.sockport = 3306open_files_limit = 8192innodb_buffer_pool_size = 512Mcharacter-set-server=utf8#开启并指定二进制日志前缀log_bin=mysql-bin#唯一idserver_id=11234#二进制日志保存时间expire_logs_days=7binlog_format=ROW[mysql]auto-rehashprompt=\u@\d \R:\m mysql>

mysql8.0开启binlog的配置与5.7略有区别，这里不做详细讲解。

3.重启mysql

service mysqld restart

执行后进入mysql查看配置是否生效

root@(none) 10:25 mysql> show variables like '%log_bin%';+---------------------------------+-----------------------------+| Variable_name | Value |+---------------------------------+-----------------------------+| log_bin| ON|| log_bin_basename| /data/mysql/mysql-bin || log_bin_index | /data/mysql/mysql-bin.index || log_bin_trust_function_creators | OFF|| log_bin_use_v1_row_events | OFF|| sql_log_bin | ON|+---------------------------------+-----------------------------+6 rows in set (0.00 sec)

log_bin 显示为ON，表示开启成功

master配置完毕后，接下来就要配置slave的server_id了

这里修改一下f即可，slave的f配置如下

[root@localhost mysql]# cat /etc/f[mysqld_safe][client]socket=/data/mysql/mysql.sock[mysqld]socket=/data/mysql/mysql.sockport = 3306open_files_limit = 8192innodb_buffer_pool_size = 512Mcharacter-set-server=utf8server_id = 11235[mysql]auto-rehashprompt=\u@\d \R:\m mysql>

三、在master上创建给slave过来复制二进制日志的用户并授权

这里直接使用grant授权同时创建，grant授权如果没有这个用户的话会进行创建用户。

root@(none) 10:44 mysql>grant replication slave on *.* to rep1 identified by 'rep123456';Query OK, 0 rows affected, 1 warning (0.09 sec)

四、在master上做一个全备，导入数据到slave上，保持master和slave上的数据一致

1.先导出master所有数据库数据导出为alldb.sql

mysqldump -u root -p123456 --all-databases > alldb.sql

[root@localhost mysql]# mysqldump -u root -p123456 --all-databases > alldb.sqlmysqldump: [Warning] Using a password on the command line interface can be insecure.[root@localhost mysql]# lsalldb.sql include mansupport-filesbin libREADMEdocs LICENSE share

这里有报警，不影响，只是系统提醒我们直接把密码输入到命令行不安全，我这里只是做实验就直接输入了，在工作中就不要把密码直接输入到命令行中了。

2.将导出的数据拷贝到slave，这里使用的scp命令

scp alldb.sql root@192.168.174.139:/usr/local/mysql

命令解释：将当前目录下面的alldb.sql 拷贝到192.168.174.139服务器的/usr/local/mysql目录下使用root用户登陆（要知道对应密码才可以拷贝）

[root@localhost mysql]# scp alldb.sql root@192.168.174.139:/usr/local/mysqlThe authenticity of host '192.168.174.139 (192.168.174.139)' can't be established.ECDSA key fingerprint is SHA256:uEg1xjL7rZlunaIWRaVYq7C5VoKsjcbVueZpIx0uGQ8.ECDSA key fingerprint is MD5:1f:fe:8a:80:90:eb:22:64:ea:76:a8:fe:a8:73:1f:88.Are you sure you want to continue connecting (yes/no)? yesWarning: Permanently added '192.168.174.139' (ECDSA) to the list of known hosts.root@192.168.174.139's password: alldb.sql 100% 862KB 102.3MB/s 00:00

slave 的/usr/local/mysql目录下

[root@localhost mysql]# pwd/usr/local/mysql[root@localhost mysql]# lsalldb.sql docslibmansharebin include LICENSE README support-files

3.将alldb.sql导入slave使用命令

mysql -u root -p < alldb.sql

[root@localhost mysql]# mysql -u root -p < alldb.sqlEnter password: [root@localhost mysql]# mysql -uroot -p123456mysql: [Warning] Using a password on the command line interface can be insecure.Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 6Server version: 5.7.37 MySQL Community Server (GPL)Copyright (c) 2000, , Oracle and/or its affiliates.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.root@(none) 11:52 mysql>show databases;+--------------------+| Database |+--------------------+| information_schema || log|| mysql || performance_schema || sys|| test|+--------------------+6 rows in set (0.00 sec)

这里全备就做好了，然后再接着执行上面未全备的操作就可以了（注意因为我这里还做了一些操作，所以位置号发生了改变，slave到master拉取二进制日志一定要先show master status;看一下位置号）

五、在slave上配置去master上拉取二进制日志文件

为什么要先做主备，因为如果我们的master不是全新的机器，不先做全备那么slave就只会同步你做主从复制之后所做的操作

如果已经做了全备，那就只需要做第3步

不先做主备

1.master原有databases

root@(none) 10:55 mysql>show databases;+--------------------+| Database |+--------------------+| information_schema || log|| mysql || performance_schema || sys|| test|+--------------------+

2.slave上的databases（以下四个数据库是自带的）

root@(none) 11:14 mysql>show databases;+--------------------+| Database |+--------------------+| information_schema || mysql || performance_schema || sys|+--------------------+4 rows in set (0.00 sec)

3.从机上配置需要复制的主机

change master to master_host='192.168.174.137',master_port=3306,master_user='rep1',master_password='rep123456',master_log_file='mysql-bin.000001',master_log_pos=437;Query OK, 0 rows affected, 2 warnings (0.06 sec)

六、在master和slave上都关闭防火墙和selinux

在master和slave都要执行

关闭防火墙

#临时关闭防火墙[root@localhost mysql]# systemctl stop firewalld#设置开机不启动防火前[root@localhost mysql]# systemctl disable firewalld

关闭selinux

临时关闭

setenforce 0

永久关闭

vim /etc/selinux/config 修改SELINUX=disabled

七、在slave服务器上启动slave服务，查看IO线程和SQL线程是否成功启动

首先进入mysql：mysql-uroot -p123456

开启slave

root@(none) 12:01 mysql>start slave;Query OK, 0 rows affected (0.01 sec)

使用命令show slave \G查看状态,【\G是为了以键值的形式显示，好看一些】

看到Slave_IO_Running和Slave_SQL_Running是YES就表示主从复制成功了

八、验证主从复制效果

master上创建数据库（只是演示一下修改操作，其它修改操作也是可以的，这里为了直观一点就直接创建数据库了）

root@(none) 11:14 mysql>create database log1;root@(none) 11:14 mysql>show databases;+--------------------+| Database |+--------------------+| information_schema || log|| log1|| mysql || performance_schema || sys|| test|+--------------------+7 rows in set (0.00 sec)

slave

root@(none) 11:14 mysql>show databases;+--------------------+| Database |+--------------------+| information_schema || log1|| mysql || performance_schema || sys|+--------------------+5 rows in set (0.00 sec)

可以看到，slave上只同步了新建的log1数据库，但是master上的test并没有出现在slave上

至此，我们的主从复制就做好了

九、为主从复制集群做高可用，进行Keepalived的双VIP配置

1.在router3(master)和router(backup)都安装 keepalived；

yum install keepalived -y

2.修改配置文件；

vim /etc/keepalived/keepalived.conf

master

vrrp_instance VI_1 {state SLAVEinterface ens33virtual_router_id 52priority 100advert_int 1authentication {auth_type PASSauth_pass 1111}virtual_ipaddress {192.168.174.112}}#双VIP的第二组，和上一组不同，代表这个服务器在52这个组里面是SLAVEvrrp_instance VI_2 {state MASTER #指定主服务器为主节点interface ens33 #指定虚拟IP的接口virtual_router_id 51 #VRRP组名，两个节点的组名要一样，表示在同一VRRP组priority 200 #优先级，1-255，数字越大优先级越高advert_int 1 #组播信息发送间隔，两个节点必须一样

slave

vrrp_instance VI_1 {state MASTERinterface ens33virtual_router_id 52priority 200advert_int 1authentication {auth_type PASSauth_pass 1111}virtual_ipaddress {192.168.174.112}}vrrp_instance VI_2 {state SLAVEinterface ens33virtual_router_id 51priority 100advert_int 1authentication {auth_type PASSauth_pass 1111}

检测是否配置成功

输入ip a查看

master

slave

当其中有一台机器宕机之后

如果有以上效果就代表成功了

十、添加GTID，配置mysql-router、读写分离

添加GTID配置

master和slave都要添加如下图的两行配置

vim /etc/f

检查GTID是否已经开启

这里显示gtid_mode为ON就代表配置好了

slave连接到master

这里要修改成GTID

CHANGE MASTER TO MASTER_HOST='master的IP', MASTER_USER='用户名', MASTER_PASSWORD='密码', MASTER_PORT=端口号, # 1 代表采用GTID协议复制# 0 代表采用老的binlog复制MASTER_AUTO_POSITION = 1;

配置mysql-router

下载

[root@Hardy ~]# cd /data/[root@Hardy data]# wget /get/Downloads/MySQL-Router/mysql-router-8.0.18-linux-glibc2.12-x86_64.tar.xz[root@Hardy data]# tar -Jxvf mysql-router-8.0.18-linux-glibc2.12-x86_64.tar.xz [root@Hardy data]# ln -s /data/mysql-router-8.0.18-linux-glibc2.12-x86_64 /usr/local/mysql-router

配置

[root@Hardy data]# mkdir /etc/mysql-route/[root@Hardy data]# cp /usr/local/mysql-router/share/doc/mysqlrouter/sample_mysqlrouter.conf /etc/mysql-route/mysqlrouter.conf

修改后完整的内容，配置主可读可写，配置从只读

[DEFAULT]# 日志存放目录logging_folder = /data/log/mysql-route# 插件存放目录plugin_folder = /usr/local/mysql-router/lib/mysqlrouter# 配置文件存放目录config_folder = /etc/mysql-route# 运行目录runtime_folder = /var/run# [logger]# 日志运行级别level = debug# #主节点故障转移配置[routing:basic_failover]# 写节点地址bind_address=192.168.174.160# 写节点端口bind_port = 7001# 模式，读写mode = read-write# 主节点地址：默认情况下第一台主数据库为写主库，当第一台主数据库DOWN机后，第二台数据库被提升为主库destinations = 192.168.174.111:3306,192.168.174.112:3306# 从节点负载均衡配置[routing:balancing]# 绑定的IP地址bind_address=192.168.174.160# 监听的端口bind_port = 7002# 连接超时时间connect_timeout = 3# 最大连接数max_connections = 1024# 后端服务器地址destinations = 192.168.174.112:3306# 模式:读还是写mode = read-only[keepalive]interval = 60

创建目录

mkdir -p /data/log/mysql-routechown root:root /data/log/mysql-route/

启动

/usr/local/mysql-router/bin/mysqlrouter -c /etc/mysql-route/mysqlrouter.conf &

查看日志

tail -f /data/log/mysql-route/mysqlrouter.log -08-14 16:13:37 main DEBUG [7f58512e5780] Starting all plugins.-08-14 16:13:37 main DEBUG [7f584dda7700] plugin 'keepalive:' starting-08-14 16:13:37 keepalive INFO [7f584dda7700] keepalive started with interval 60-08-14 16:13:37 keepalive INFO [7f584dda7700] keepalive-08-14 16:13:37 main DEBUG [7f58512e5780] plugin 'logger:' doesn't implement start()-08-14 16:13:37 main DEBUG [7f584d5a6700] plugin 'routing:balancing' starting-08-14 16:13:37 routing INFO [7f584d5a6700] [routing:balancing] started: listening on 192.168.174.160:7002, routing strategy = round-robin

登录验证

这里要注意，不要用root登陆，因为root默认是不允许远程登陆的。

[root@localhost bin]# mysql -h 192.168.174.160 -uroot -p'123456' -P7002mysql: [Warning] Using a password on the command line interface can be insecure.ERROR 1045 (28000): Access denied for user 'root'@'192.168.174.160' (using password: YES)[root@localhost bin]# mysql -h 192.168.174.160 -ua -p'123456' -P7001mysql: [Warning] Using a password on the command line interface can be insecure.Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 8Server version: 5.7.37-log MySQL Community Server (GPL)Copyright (c) 2000, , Oracle and/or its affiliates.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.a@(none) 17:12 mysql>show databases;+--------------------+| Database |+--------------------+| information_schema || log|| mysql || performance_schema || sys|| test|+--------------------+6 rows in set (0.00 sec)a@(none) 17:22 mysql>show slave status\G;*************************** 1. row ***************************Slave_IO_State: Waiting for master to send eventMaster_Host: 192.168.174.111Master_User: rep1Master_Port: 3306Connect_Retry: 60Master_Log_File: mysql-bin.000002Read_Master_Log_Pos: 586Relay_Log_File: localhost-relay-bin.000002Relay_Log_Pos: 799Relay_Master_Log_File: mysql-bin.000002Slave_IO_Running: YesSlave_SQL_Running: YesReplicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0Last_Error: Skip_Counter: 0Exec_Master_Log_Pos: 586Relay_Log_Space: 1010Until_Condition: NoneUntil_Log_File: Until_Log_Pos: 0Master_SSL_Allowed: NoMaster_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0Master_SSL_Verify_Server_Cert: NoLast_IO_Errno: 0Last_IO_Error: Last_SQL_Errno: 0Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 11234Master_UUID: 7d81324d-fe5f-11ec-b5b0-000c29993ee7Master_Info_File: /data/mysql/master.infoSQL_Delay: 0SQL_Remaining_Delay: NULLSlave_SQL_Running_State: Slave has read all relay log; waiting for more updatesMaster_Retry_Count: 86400Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: 7d81324d-fe5f-11ec-b5b0-000c29993ee7:1-2Executed_Gtid_Set: 7d81324d-fe5f-11ec-b5b0-000c29993ee7:1-2Auto_Position: 1Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: 1 row in set (0.00 sec)ERROR: No query specified

参考：

/p/09f68090a0f8

mysql router使用配置 - davie - 博客园

如果觉得《构建一个高可用的MySQL主从复制集群》对你有帮助，请点赞、收藏，并留下你的观点哦！