MySQL 高可用架构：主从备份及读写分离
原文出处：https://blog.51cto.com/u_16213577/7402163
https://mp.weixin.qq.com/s/zWRDlY5E2y8EDLZFpcwbFQ
主从复制原理
一共由三个线程完成
-
主服务将数据的更新记录保存到二进制日志--主服务器线程
-
从服务将主服务的二进制日志复制到本地中继日志--从服务IO线程
-
从服务读取中继日志，更新本地数据--从服务SQL线程
1、环境准备
5台服务器
192.168.2.34 master01 部署mysql 192.168.2.35 master02 部署mysql 192.168.2.36 slave01 部署mysql 192.168.2.37 slave02 部署mysql
192.168.2.40 mycat 部署jdk1.8，部署mycat，部署mysql
2、安装mysql8.0数据库
5台服务器均需要执行此脚本
#编写mysql安装脚本，使用如下脚本继续安装 vi /opt/mysql_install.sh #!/bin/bash #关闭防火墙 systemctl stop firewalld #设置防火墙开机不启动 systemctl disable firewalld #临时关闭selinux setenfore 0 #环境清理 rpm -e mariadb-libs-5.5.68-1.el7.x86_64 --nodeps echo $? rm -rf /etc/selinux/targeted/active/modules/100/mysql echo $? rm -rf /usr/lib64/mysql echo $? #安装wget命令，下载mysql8.0的安装包 yum -y install wget &> null echo $? cd /opt #下载mysql8.0安装包 wget http://repo.mysql.com/mysql80-community-release-el7.rpm &> null echo $? #安装mysql8.0 rpm -ivh mysql80-community-release-el7.rpm &> null echo $? yum -y install mysql-community-server &> null echo $? #启动数据库 systemctl start mysqld echo $? #查询mysql初始密码 p=`grep -i 'password' /var/log/mysqld.log | awk '{print $313}'` echo $? #脚本执行最后，会打印mysql初始root密码
3、修改root密码（5台均需要修改密码）
mysql -uroot -p'初始密码' #初始执行上述脚本后，会进行打印 Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 8 Server version: 8.0.32 Copyright (c) 2000, 2023, Oracle and/or its affiliates. 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> alter user 'root'@'localhost' identified by '1qaz!QAZ'; Query OK, 0 rows affected (0.00 sec)
4、mysql双主、双备配置
master01和master02互为主备
master01和slave01为主从复制
master02和slave02为主从复制
192.168.2.34-master01配置
#修改主机名 hostname master01 bash #修改mysql配置文件 vi /etc/my.cnf [mysql] server-id=34 #mysql服务唯一id log-bin=mysql-bin #存放日志文件位置 auto_increment_increment=2 #控制主键自增长的步长，几台服务器就设置几 auto_increment_offset=1 #设置自增起始值，此处是第一台 replication_do_db=test #设置需要同步的数据库，不配置默认同步全部
systemctl restart mysqld #重启数据库
#在数据库中创建用户：master01 mysql -uroot -p'1qaz!QAZ' create user 'master01'@'%' identified with mysql_native_password by '1qaz!QAZ';
#赋予master01用户权限 grant replication slave on *.* to ;master01'@'%'; flush privileges; #刷新数据库，提交之前的操作到数据库 change master to master_host='192.168.2.35',master_user='master02',master_password='1qaz!QAZ',master_log_file='mysql-bin.000001',master_log_pos=157,GET_MASTER_PUBLIC_KEY=1;
mysql > start slave; mysql > show slave status; mysql> show master status; #此处查询的信息后面配置需要用到 +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000001 | 157 | | | | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec
192.168.2.35-master02配置
#修改主机名 hostname master02 bash #修改mysql配置文件 vi /etc/my.cnf [mysql] server-id=35 #mysql服务唯一id log-bin=mysql-bin #存放日志文件位置 auto_increment_increment=2 #控制主键自增长的步长，几台服务器就设置几 auto_increment_offset=2 #设置自增起始值，此处是第一台 replication_do_db=test #设置需要同步的数据库，不配置默认同步全部
systemctl restart mysqld #重启数据库
#在数据库中创建用户：master01 mysql -uroot -p'1qaz!QAZ' create user 'master02'@'%' identified with mysql_native_password by '1qaz!QAZ';
#赋予master01用户权限 grant replication slave on *.* to ;master02'@'%'; flush privileges; #刷新数据库，提交之前的操作到数据库 mysql > change master to master_host='192.168.2.34',master_user='master01',master_password='1qaz!QAZ',master_log_file='mysql-bin.000001',master_log_pos=157,GET_MASTER_PUBLIC_KEY=1;
mysql > start slave; mysql > show slave status; mysql> show master status; #此处查询的信息后面配置需要用到 +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000001 | 157 | | | | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec
192.168.2.36-slave01配置
#修改主机名 hostname master01 bash #修改mysql配置文件 vi /etc/my.cnf [mysql] server-id=36 #mysql服务唯一id
systemctl restart mysqld #重启数据库
mysql -uroot -p mysql > change master to master_host='192.168.2.34',master_user='master01',master_password='1qaz!QAZ',master_log_file='mysql-bin.000001',master_log_pos=157,GET_MASTER_PUBLIC_KEY=1; mysql > start slave; mysql > show slave status;
192.168.2.36-slave02配置
#修改主机名 hostname master01 bash #修改mysql配置文件 vi /etc/my.cnf [mysql] server-id=37 #mysql服务唯一id
systemctl restart mysqld #重启数据库
mysql -uroot -p mysql > change master to master_host='192.168.2.35',master_user='master02',master_password='1qaz!QAZ',master_log_file='mysql-bin.000001',master_log_pos=157,GET_MASTER_PUBLIC_KEY=1; mysql > start slave; mysql > show slave status;
至此，mysql双主双备已经部署完成
5、安装数据库中间件Mycat
192.168.2.40 mycat
5.1、安装mycat需要安装java环境，此处安装jdk1.8版本
#jdk1.8安装包获取 # https://pan.baidu.com/s/1Z-ZwMHFk6325vD910gickw?pwd=gjf6 #此处将获取到的jdk安装包放在/opt目录下 mkdir -p /usr/local/jdk1.8 cd /opt tar -zxvf jdk-8u191-linux-x64.tar.gz -C /usr/local/jdk1.8 #将以下内容写入 /etc/profile export JAVA_HOME=/usr/local/jdk1.8 export JRE_HOME=/usr/local/jdk1.8/jre export CLASSPATH=.:$JAVA_HOME/lib/de.jar:$JAVA_HOME/lib/tools.jar$JRE_HOME/lib:$CLASSPATH export PATH=$JAVA_HOME/bin:$PATH source /etc/profile #安装mycat #mycat安装包地址 #https://pan.baidu.com/s/1Rlw7h5kfoWgyxE6MO366nw?pwd=6if5 mkdir /usr/local/mycat cd /opt tar -zxvf Mycat-server-1.6.7.1-release-20190627191042-linux.tar.gz -C /usr/local/mycat #然后将如下内容追加到/etc/profile export MYCAT_HOME=/usr/local/mycat export PATH=$MYCAT_HOME/bin:$PATH:$JAVA_HOME/bin source /etc/profile
5.2、mycat配置文件详解
#mycat已经安装完成，此时需修改配置文件
#mycat有三个主要的配置文件 /usr/local/mycat/conf/schema.xml #涵盖了mycat的逻辑库，逻辑表，分片规则，分片节点和数据源的配置
/usr/local/mycat/conf/server.xml #主要配置连接时的权限过滤
/usr/local/mycat/conf/rule.xml #定义分片规则，分片规则决定了逻辑表中的数据以何种方式存储到不同的数据库
## schema.xml 配置文件详解
#如下是我的schema.xml配置文件 <?xml version="1.0"?> <!DOCTYPE mycat:schema SYSTEM "schema.dtd"> <mycat:schema xmlns:mycat="http://io.mycat/"> <schema name="TESTDB" checkSQLschema="true" sqlMaxLimit="100" dataNode="mycat"> </schema> <dataNode name="mycat" dataHost="master01" database="testdb" /> <dataHost name="master01" maxCon="1000" minCon="10" balance="3" writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100"> <heartbeat>select user()</heartbeat> <writeHost host="master01" url="192.168.2.34:3306" user="root" password="1qaz!QAZ"> <readHost host="master02" url="192.168.2.35:3306" user="root" password="1qaz!QAZ" /> <readHost host="slave01" url="192.168.2.36:3306" user="root" password="1qaz!QAZ" /> <readHost host="slave02" url="192.168.2.37:3306" user="root" password="1qaz!QAZ" /> </writeHost> </dataHost> </mycat:schema <!-- <schema name="TESTDB" #逻辑数据库名称（必须大写） checkSQLschema="true" sqlMaxLimit="100" dataNode="mycat"> #定义数据节点名称 </schema> <dataNode name="mycat" #mycat节点名称 dataHost="master01" #数据库实例主机名称 database="testdb" /> #主机master01上mysql数据库中存在的数据库库名称 <dataHost name="master01" #节点名称，与dataNode name一致 maxCon="1000" #最大连接数 minCon="10" #最小连接数 balance="1" #负载均衡方式（0：不开启读写分离，1：双主双备模式下，master02、slave01和slave02都参与select语句负载均衡，2：所有操作随机在writehost和readhost分发，3：所有读请求都随机分发到writehost对应的radwrite，writehost不参与读请求） writeType="0" #写操作分发方式（0：写操作转发到第一个writehost，第一个宕机后转发到第二个，1：随机转发到writehost） dbType="mysql" #数据库类型 dbDriver="native" #数据库驱动，支持native和jdbc switchType="1" #（1：主从自动切换，2：从机延时超过slave threshold值时切换） slaveThreshold="100"> <heartbeat>select user()</heartbeat> #心跳检测 <writeHost host="master01" #写操作数据库实例主机形成 url="192.168.2.34:3306" #写操作数据库ip地址 user="root" #写操作数据库用户名 password="1qaz!QAZ"> #写操作数据库用户名密码 #以下配置了3台读操作数据库 <readHost host="master02" url="192.168.2.35:3306" user="root" password="1qaz!QAZ" /> <readHost host="slave01" url="192.168.2.36:3306" user="root" password="1qaz!QAZ" /> <readHost host="slave02" url="192.168.2.37:3306" user="root" password="1qaz!QAZ" /> </writeHost> </dataHost> -->
## server.xml配置文件详解 <user name="mycat" defaultAccount="true"> #设置为mycat，便于和root区分 <property name="password">123456</property> <property name="schemas">TESTDB</property> <!-- 表级 DML 权限设置 --> <!-- <privileges check="false"> <schema name="TESTDB" dml="0110" > <table name="ilovyou" dml="0000"></table> </schema> </privileges> --> </user> <user name="user"> <property name="password">123456</property> <property name="schemas">TESTDB</property> <property name="readOnly">true</property> </user> <!-- <user name="mycat" defaultAccount="true"> #配置远程登录mycat的用户名 <property name="password">123456</property> #密码 <property name="schemas">TESTDB</property> #可以访问的逻辑数据库 </user> <user name="user"> <property name="password">123456</property> <property name="schemas">TESTDB</property> <property name="readOnly">true</property> </user> -->
rule.xml 默认配置即可
#此时配置文件已经修改完毕，启动mycat cd /usr/local/mycat/bin ./mycat start consol Starting Mycat-server... ./mycat status Mycat-server is running (14149). #查看mycat进程 ps -ef | grep mycat root 14149 1 0 15:45 ? 00:00:00 /usr/local/mycat/bin/./wrapper-linux-x86-64 /usr/local/mycat/conf/wrapper.conf wrapper.syslog.ident=mycat wrapper.pidfile=/usr/local/mycat/logs/mycat.pid wrapper.daemonize=TRUE wrapper.lockfile=/var/lock/subsys/mycat root 14151 14149 8 15:45 ? 00:00:04 java -DMYCAT_HOME=. -server -Djava.library.path=lib -classpath lib/wrapper.jar:conf:lib/asm-4.0.jar:lib/commons-collections-3.2.1.jar:lib/commons-lang-2.6.jar:lib/curator-client-2.11.0.jar:lib/curator-framework-2.11.0.jar:lib/curator-recipes-2.11.0.jar:lib/disruptor-3.3.4.jar:lib/dom4j-1.6.1.jar:lib/druid-1.0.26.jar:lib/ehcache-core-2.6.11.jar:lib/fastjson-1.2.12.jar:lib/guava-19.0.jar:lib/hamcrest-core-1.3.jar:lib/hamcrest-library-1.3.jar:lib/jline-0.9.94.jar:lib/joda-time-2.9.3.jar:lib/jsr305-2.0.3.jar:lib/kryo-2.10.jar:lib/leveldb-0.7.jar:lib/leveldb-api-0.7.jar:lib/libwrapper-linux-ppc-64.so:lib/libwrapper-linux-x86-32.so:lib/libwrapper-linux-x86-64.so:lib/log4j-1.2-api-2.5.jar:lib/log4j-1.2.17.jar:lib/log4j-api-2.5.jar:lib/log4j-core-2.5.jar:lib/log4j-slf4j-impl-2.5.jar:lib/mapdb-1.0.7.jar:lib/minlog-1.2.jar:lib/mongo-java-driver-2.11.4.jar:lib/Mycat-server-1.6.7.1-release.jar:lib/mysql-binlog-connector-java-0.16.1.jar:lib/mysql-connector-java-5.1.35.jar:lib/netty-3.7.0.Final.jar:lib/netty-buffer-4.1.9.Final.jar:lib/netty-common-4.1.9.Final.jar:lib/objenesis-1.2.jar:lib/reflectasm-1.03.jar:lib/sequoiadb-driver-1.12.jar:lib/slf4j-api-1.6.1.jar:lib/univocity-parsers-2.2.1.jar:lib/velocity-1.7.jar:lib/wrapper.jar:lib/zookeeper-3.4.6.jar -Dwrapper.key=WnzqYVt4xpSGqMKn -Dwrapper.port=32000 -Dwrapper.jvm.port.min=31000 -Dwrapper.jvm.port.max=31999 -Dwrapper.pid=14149 -Dwrapper.version=3.2.3 -Dwrapper.native_library=wrapper -Dwrapper.service=TRUE -Dwrapper.cpu.timeout=10 -Dwrapper.jvmid=1 org.tanukisoftware.wrapper.WrapperSimpleApp io.mycat.MycatStartup start root 14536 1777 0 15:46 pts/0 00:00:00 grep --color=auto mycat #查看mycat端口 netstat -ntlp Active Internet connections (only servers) Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name tcp 0 0 127.0.0.1:32000 0.0.0.0:* LISTEN 14151/java tcp 0 0 0.0.0.0:22 0.0.0.0:* LISTEN 1123/sshd tcp 0 0 127.0.0.1:25 0.0.0.0:* LISTEN 1290/master #远程登录端口 tcp6 0 0 :::8066 :::* LISTEN 14151/java tcp6 0 0 :::9066 :::* LISTEN 14151/java tcp6 0 0 :::22 :::* LISTEN 1123/sshd tcp6 0 0 ::1:25 :::* LISTEN 1290/master
#管理端口 9066
#数据源端口 8066
5.3、master01，master02，slave01，slave02赋予root用户可远程登录权限
#因为在schma.xml文件中配置了root用户和密码。mycat连接数据库要用到，所以此处需要配置可以使用root用户远程d登录mysql数据库
mysql > grant all privileges on *.* to 'root'@'%' with grant option;
mysql> select Host,user from mysql.user;
+-----------+------------------+ | Host | user | +-----------+------------------+ | % | root | # %表示可以远程d登录 | localhost | mysql.infoschema | | localhost | mysql.session | | localhost | mysql.sys | | localhost | root | +-----------+------------------+ 7 rows in set (0.00 sec)
5.4、测试mycat是否可以正常登录
#在mycat（192.168.2.40）这台服务器进行测试
[root@xuguangjia bin]# mysql -h 192.168.2.40 -P 8066 -umycat -p123456 Welcome to the MariaDB monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server version: 5.6.29-mycat-1.6.7.1-release-20190627191042 MyCat Server (OpenCloudDB) Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MySQL [(none)]> # 可以正常登录 MySQL [(none)]> show databases; #看到在schma.xml文件中配置的逻辑库 +----------+ | DATABASE | +----------+ | TESTDB | +----------+ 1 row in set (0.00 sec) MySQL [(none)]> use TESTDB; #进入逻辑库 Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed MySQL [TESTDB]> show tables; +------------------+ | Tables_in_testdb | #表'ilovyou' 在库testdb中 +------------------+ | ilovyou | #此时插到的TESTDB逻辑库下有一张表“ilovyou” +------------------+ 1 row in set (0.12 sec) 这是因为在schma.xml文件中配置了如下语句 <dataNode name="mycat" dataHost="master01" database="testdb" /> 此时的逻辑库TESTDB 对应mysql数据库中的testdb数据库 #此时我们在master01的数据库查看所有的数据库 mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | | testdb | | xuguangjia | +--------------------+ 6 rows in set (0.01 sec) mysql> use testdb; #进入testdb库 Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> show tables; #查看表 +------------------+ | Tables_in_testdb | +------------------+ | ilovyou | +------------------+ 1 row in set (0.00 sec) #至此，mycat实现mysq双主双备读写分离实验配置完成。 #修改日志mycat日志级别，这样j就可以查看写操作和读操作分别在那台服务器上 vi /usr/local/mycat/conf/log4j2.xml ... <asyncRoot level="info" includeLocation="true"> ... ... ## info 修改为dubug <asyncRoot level="debug" includeLocation="true"> ... #然后重启mycat cd /usr/local/mycat/bin ./mycat restart
Keepalived+Mysql双活实现Mysql高可用
1、Mysql-8.0双主部署
1.1、环境准备
iptables -F systemctl stop firewalld #关闭防火墙 setenfore 0 #关闭selinux
rpm -qa | grep mysql rpm -qa | grep mariadb mariadb-libs-5.5.68-1.el7.x86_64
rpm -e mariadb-libs-5.5.68-1.el7.x86_64 --nodeps
find / -name mariadb find / -name mysql
/etc/selinux/targeted/active/modules/100/mysql /usr/lib64/mysql
rm -rf /etc/selinux/targeted/active/modules/100/mysql rm -rf /usr/lib64/mysql
1.2、安装mysql（两台服务器均需要安装）
yum -y install wget #安装wgt命令。用于下载mysql安装包 wget http://repo.mysql.com/mysql80-community-release-el7.rpm rpm -ivh mysql80-community-release-el7.rpm yum -y install mysql-community-server
systemctl start mysqld #启动mysql
grep -i 'password' /var/log/mysql.log #查找mysql初始密
2023-03-23T06:39:43.463553Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: q_gkjqeR1iGA #冒号后面的一串字符就是mysql初始密码
1.3、修改mysql初始密码
mysql -uroot -p'q_gkjqeR1iGA' Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 8 Server version: 8.0.32 Copyright (c) 2000, 2023, Oracle and/or its affiliates. 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> alter user 'root'@'localhost' identified by '1qaz!QAZ'; Query OK, 0 rows affected (0.00 sec)
1.4、配置主主同步
Master01配置
#修改主机名 hostname master01 bash #修改mysql配置文件 vi /etc/my.cnf [mysql] server-id=11 #mysql服务唯一id log-bin=mysql-bin #存放日志文件位置 auto_increment_increment=2 #控制主键自增长的步长，几台服务器就设置几 、auto_increment_offset=1 #设置自增起始值，此处是第一台 replication_do_db=test #设置需要同步的数据库，不配置默认同步全部
systemctl restart mysqld #重启数据库
#在数据库中创建用户：master01 mysql -uroot -p'1qaz!QAZ' create user 'master01'@'%' identified with mysql_native_password by '1qaz!QAZ'; #赋予master01用户权限 grant replication slave on *.* to ;master01'@'%';
flush privileges; #刷新数据库，提交之前的操纵到数据库
mysql> show master status; #此处查询的信息后面配置需要用到 +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000005 | 2709 | | | | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 se
master02配置
#修改主机名 hostname master02 bash
#修改mysql配置文件 vi /etc/my.cnf mysql-server=22 log-bin=mysql-bin auto_increment_increment=2 auto_increment_offset=2 replicate-do-db=test systemctl restart mysqld mysql -uroot -p'1qaz!QAZ'
#绑定master01数据库 change master to master_host='master01-ip',master_user='master01',master_password='1qaz!QAZ',master_log_file='mysql-bin.000005',master_log_pos=2709,GET_MASTER_PUBLIC_KEY=1;
start slave; #启动备份
mysql> show slave status \G #查看配置状态 *************************** 1. row *************************** Slave_IO_State: Waiting for source to send event Master_Host: 192.168.2.31 Master_User: master31 Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000005 Read_Master_Log_Pos: 2709 Relay_Log_File: master2-relay-bin.000002 Relay_Log_Pos: 1102 Relay_Master_Log_File: mysql-bin.000005 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: demo_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: 2709 Relay_Log_Space: 1314 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: 31 Master_UUID: 839c6949-98f6-11ee-beb4-000c299a17cf Master_Info_File: mysql.slave_master_info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Replica 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: Master_public_key_path: Get_master_public_key: 0 Network_Namespace: 1 row in set, 1 warning (0.00 sec) #至此，master02为master01的从数据库已经配置完成。
master02配置（此时需要配置master01为master02的从数据库）
#master02上进行配置 mysql -uroot -p'1qaz!QAZ'
create user 'master02'@'%' identified with mysql_native_password by '1qaz!QAz'; grant replication slave on *.* to 'master02'@'%';
mysql> show master status; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000002 | 1965 | | | | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec)
master01进行配置（配置master01为master02的从数据库）
mysql -uroot -p'1qaz!QAZ'
change master to master_host='master02-ip',master_user='master02',master_password='1qaz!QAZ',master_log_file='mysql-bin.000005',master_log_pos=1965,GET_MASTER_PUBLIC_KEY=1; start slave;
mysql> show slave status \G *************************** 1. row *************************** Slave_IO_State: Waiting for source to send event Master_Host: 192.168.2.32 Master_User: master32 Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000002 Read_Master_Log_Pos: 1965 Relay_Log_File: master1-relay-bin.000002 Relay_Log_Pos: 646 Relay_Master_Log_File: mysql-bin.000002 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: demo_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: 1965 Relay_Log_Space: 858 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: 32 Master_UUID: d75926e6-98f6-11ee-80c7-000c2952c327 Master_Info_File: mysql.slave_master_info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Replica 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: Master_public_key_path: Get_master_public_key: 1 Network_Namespace: 1 row in set, 1 warning (0.00 sec) #至此，mysql主主同步已经配置w完成
2、keepalived源码安装部署（master01和master02分别安装keepalived，安装步骤相同，配置文件不同）
2.1安装依赖包，下载keepalived2.2.8版本至/opt目录
#安装依赖包 yum -y install gcc openssl-devel popt-devel psmisc
#安装wget命令，用于下载keepalived安装包 yum -y install wget
#下载keepalived安装包到/opt目录 wget -p /opt --no-check-certificate https://www.keepalived.org/software/keepalived-2.2.8.tar.gz
#进入opt目录 cd /opt
#在/opt目录下进行解压
tar -zxvf keepalived-2.2.8.tar.gz ls -l keepalived-2.2.8
总用量 1768 -rw-rw-r--. 1 keepalived keepalived 44361 5月 31 2023 aclocal.m4 -rw-rw-r--. 1 keepalived keepalived 41 9月 28 2009 AUTHOR -rwxrwxr-x. 1 keepalived keepalived 100 12月 21 2020 autogen.sh drwxr-xr-x. 2 root root 39 12月 14 18:31 bin drwxrwxr-x. 2 keepalived keepalived 60 12月 14 18:30 bin_install drwxrwxr-x. 2 keepalived keepalived 83 5月 31 2023 build-aux -rwxrwxr-x. 1 keepalived keepalived 100 5月 5 2020 build_setup -rw-rw-r--. 1 keepalived keepalived 507110 1月 27 2021 ChangeLog -rw-r--r--. 1 root root 461338 12月 14 18:30 config.log -rwxr-xr-x. 1 root root 46595 12月 14 18:30 config.status -rwxrwxr-x. 1 keepalived keepalived 448153 5月 31 2023 configure -rw-rw-r--. 1 keepalived keepalived 116637 5月 31 2023 configure.ac -rw-rw-r--. 1 keepalived keepalived 823 12月 25 2017 CONTRIBUTORS -rw-rw-r--. 1 keepalived keepalived 18092 11月 20 2012 COPYING drwxrwxr-x. 5 keepalived keepalived 226 12月 14 18:30 doc -rw-r--r--. 1 root root 2279 12月 14 18:30 Dockerfile -rw-rw-r--. 1 keepalived keepalived 2720 11月 21 2021 Dockerfile.in -rw-rw-r--. 1 keepalived keepalived 9940 8月 30 2022 INSTALL drwxrwxr-x. 11 keepalived keepalived 4096 12月 14 18:31 keepalived -rw-r--r--. 1 root root 9363 12月 14 18:30 keepalived.spec -rw-rw-r--. 1 keepalived keepalived 10701 1月 25 2022 keepalived.spec.in drwxrwxr-x. 3 keepalived keepalived 4096 12月 14 18:31 lib drwxrwxr-x. 2 keepalived keepalived 43 5月 31 2023 m4 -rw-r--r--. 1 root root 32582 12月 14 18:30 Makefile -rw-rw-r--. 1 keepalived keepalived 3212 11月 21 2021 Makefile.am -rw-rw-r--. 1 keepalived keepalived 31923 5月 31 2023 Makefile.in -rw-r--r--. 1 root root 1963 12月 14 18:31 README -rw-rw-r--. 1 keepalived keepalived 2812 2月 26 2021 README.md drwxrwxr-x. 3 keepalived keepalived 41 7月 7 2022 snap -rw-rw-r--. 1 keepalived keepalived 6898 3月 15 2021 TODO drwxrwxr-x. 2 keepalived keepalived 26 5月 31 2023 tools
2.2配置keepalived，并且进行编译安装
#进入解压目录 cd keepalived-2.2.8 #进行编译安装 ./configure --prefix=/opt/keepalived-2.2.8 make && make install
2.3将keepalived文件复制到对应目录下
#创建keepalived配置文件存放目录
mkdir /etc/keepalived cp keepalived/etc/keepalived/keepalived.conf.sample /etc/keepalived/keepalived.conf
cp keepalived/etc/init.d/keepalived /etc/init.d/
cp keepalived/etc/sysconfig/keepalived /etc/sysconfig/
cp bin/keepalived /usr/sbin/
2.4创建kill_keepalived.sh脚本
#创建停止keepaliecd服务的脚本，后续会用到
cd /etc/keepalived vi kill_keepalived.sh
#!/bin/bash /etc/init.d/keepalived stop
#给予脚本执行权限 chmod +x /etc/keepalived/kill_keepalived.sh
2.5ifconfig查看网卡名称
#查看网名称 ifconfig #本机网卡名称为ens33 ens33: flags=4163<UP,BROADCAST,RUNNING,MULTICAST> mtu 1500 inet 192.168.2.31 netmask 255.255.255.0 broadcast 192.168.2.255 inet6 fe80::8683:d772:2e3:ed49 prefixlen 64 scopeid 0x20<link> ether 00:0c:29:9a:17:cf txqueuelen 1000 (Ethernet) RX packets 120145 bytes 73062159 (69.6 MiB) RX errors 0 dropped 0 overruns 0 frame 0 TX packets 123773 bytes 39409818 (37.5 MiB) TX errors 0 dropped 0 overruns 0 carrier 0 collisions 0 lo: flags=73<UP,LOOPBACK,RUNNING> mtu 65536 inet 127.0.0.1 netmask 255.0.0.0 inet6 ::1 prefixlen 128 scopeid 0x10<host> loop txqueuelen 1000 (Local Loopback) RX packets 22235 bytes 1536984 (1.4 MiB) RX errors 0 dropped 0 overruns 0 frame 0 TX packets 22235 bytes 1536984 (1.4 MiB) TX errors 0 dropped 0 overruns 0 carrier 0 collisions 0
2.6修改master01服务器的keepalived配置文件
#先对原配置文件进行备份 mv /etc/keepalived/keepalived.conf /etc/keepalived/keepalived_back.conf
#配置keepalived文件 vi /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 127.0.0.1 smtp_connect_timeout 30 router_id MYSQL -1 #主机标识符，唯一即可 # router_id LVS_DEVEL vrrp_skip_check_adv_addr vrrp_strict vrrp_garp_interval 0 vrrp_gna_interval 0 } vrrp_instance VI_1 { state BACKUP #标识keepalived角色，建议都设置为buckup，然后以优先级为主 interface ens33 #网卡名称 virtual_router_id 151 #虚拟路由标识，两者保持一致 priority 120 #优先级，用来选举master advert_int 1 authentication { auth_type PASS auth_pass 1111 } virtual_ipaddress { #keepalived虚拟出来的ip地址 192.168.2.50 192.168.2.51 192.168.2.52 } }
virtual_server 192.168.2.50 3306 { #虚拟地址和端口 delay_loop 2 #设置运行情况检查时间，单位秒 lb_algo rr #设置后端调度器算法，rr为轮询算法 lb_kind DR #设置lvs实现负载均衡的机制，DR，NAT，TUN三种模式 persistence_timeout 50 #会话保持时间，单位秒 protocol TCP #指定转发协议，tcp和udp real_server 192.168.2.31 3306 { #本机真实ip地址和+3306端口 weight 5 #服务器的权重值，权重越高，服务器在负载均衡中被选中的概率就越高 notify_down /etc/keepalived/kill_keepalived.sh #检测mysql进程，一旦mysql进程停止，就执行kill_keepalived.sh脚本 TCP_CHECK { connect_ip 192.168.2.31 #实际物理机ip地址 connect_port 3306 #实际物理机端口 connect_timeout 3 retry 3 delay_before_retry 3 } } }
2.7修改master02服务器keepalived配置文件
#先对原配置文件进行备份 mv /etc/keepalived/keepalived.conf /etc/keepalived/keepalived_back.conf #配置keepalived文件 vi /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 127.0.0.1 smtp_connect_timeout 30 router_id MYSQL -2 #主机标识符，唯一即可 # router_id LVS_DEVEL vrrp_skip_check_adv_addr vrrp_strict vrrp_garp_interval 0 vrrp_gna_interval 0 } vrrp_instance VI_1 { state BACKUP #标识keepalived角色，建议都设置为buckup，然后以优先级为主 interface ens33 #网卡名称 virtual_router_id 151 #虚拟路由标识，两者保持一致 priority 100 #优先级，用来选举master advert_int 1 authentication { auth_type PASS auth_pass 1111 } virtual_ipaddress { #keepalived虚拟出来的ip地址 192.168.2.50 192.168.2.51 192.168.2.52 } } virtual_server 192.168.2.50 3306 { #虚拟地址和端口 delay_loop 2 #设置运行情况检查时间，单位秒 lb_algo rr #设置后端调度器算法，rr为轮询算法 lb_kind DR #设置lvs实现负载均衡的机制，DR，NAT，TUN三种模式 persistence_timeout 50 #会话保持时间，单位秒 protocol TCP #指定转发协议，tcp和udp real_server 192.168.2.31 3306 { #本机真实ip地址和+3306端口 weight 5 #服务器的权重值，权重越高，服务器在负载均衡中被选中的概率就越高 notify_down /etc/keepalived/kill_keepalived.sh #检测mysql进程，一旦mysql进程停止，就执行kill_keepalived.sh脚本 TCP_CHECK { connect_ip 192.168.2.32 #实际物理机ip地址 connect_port 3306 #实际物理机端口 connect_timeout 3 retry 3 delay_before_retry 3 } } }
2.8启动keepalived（两台服务器均需要启动）
/etc/init.d/keepalived start Starting keepalived (via systemctl): [ 确定 ] /etc/init.d/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 四 2023-12-14 21:18:36 CST; 55min ago Docs: man:systemd-sysv-generator(8) Process: 61589 ExecStop=/etc/rc.d/init.d/keepalived stop (code=exited, status=0/SUCCESS) Process: 61940 ExecStart=/etc/rc.d/init.d/keepalived start (code=exited, status=0/SUCCESS) Main PID: 61959 (keepalived) CGroup: /system.slice/keepalived.service ├─61959 keepalived -D ├─61960 keepalived -D └─61961 keepalived -D 12月 14 21:18:44 xu******** Keepalived_vrrp[61961]: Sending gratuitous ARP on ens33 for 192.168.2.50 12月 14 21:18:44 xu******** Keepalived_vrrp[61961]: Sending gratuitous ARP on ens33 for 192.168.2.51 12月 14 21:18:44 xu******** Keepalived_vrrp[61961]: Sending gratuitous ARP on ens33 for 192.168.2.52 12月 14 21:18:44 xu******** Keepalived_vrrp[61961]: Sending gratuitous ARP on ens33 for 192.168.2.50 12月 14 21:18:44 xu******** Keepalived_vrrp[61961]: Sending gratuitous ARP on ens33 for 192.168.2.51 12月 14 21:18:44 xu******** Keepalived_vrrp[61961]: Sending gratuitous ARP on ens33 for 192.168.2.52 12月 14 21:18:44 xu******** Keepalived_vrrp[61961]: Sending gratuitous ARP on ens33 for 192.168.2.50 12月 14 21:18:44 xu******** Keepalived_vrrp[61961]: Sending gratuitous ARP on ens33 for 192.168.2.51 12月 14 21:18:44 xu******** Keepalived_vrrp[61961]: Sending gratuitous ARP on ens33 for 192.168.2.52 12月 14 21:55:46 xu******** Keepalived_healthcheckers[61960]: A thread timer expired 1.459160 seconds ago
2.9配置虚拟ip登录用户
#创建用户keepalived useradd -m keepalived #修改密码 passwd keepalived 1qaz!QAZ
使用192.168.2.50虚拟地址ssh远程登录服务器，实际地址为192.168.2.31
此时停止master01上的keepalived进程，再次进行登录查看
#停止kddpalived进程 /etc/init.d/keepalived stop Stopping keepalived (via systemctl): [ 确定 ]
此时的虚拟ip地址依旧是192.168.2.50，但是物理机ip地址已经自动切换到了192.168.2.32
综上测试可知keepalived部署成功
