拓扑图:
资源列表:
操作系统 | 配置 | 主机名/IP地址 | 相关程序 |
---|---|---|---|
CentOS 7.9 | 2C2G | manage/172.16.10.11 | mysql-5.7、mha4mysql-manager、mha4mysql-node、Amoeba |
CentOS 7.9 | 2C2G | master/172.16.10.10 | mysql-5.7、mha4mysql-node |
CentOS 7.9 | 2C2G | slave1/172.16.10.12 | mysql-5.7、mha4mysql-node |
CentOS 7.9 | 2C2G | slave1/172.16.10.13 | mysql-5.7、mha4mysql-node |
一、基础环境
1.1 关闭防火墙
systemctl stop firewalld systemctl enable firewalld
1.2 关闭selinux
sed -i "s/.*SELINUX=.\*/SELINUX=disabled/g" /etc/selinux/config reboot
1.3 修改主机名
hostnamectl set-hostname manager hostnamectl set-hostname master hostnamectl set-hostname slave1 hostnamectl set-hostname slave2
1.4 添加hosts文件
cat >> /etc/hosts << EOF 172.16.10.11 manager 172.16.10.10 master 172.16.10.12 slave1 172.16.10.13 slave2 EOF
1.5 时间同步
yum -y install chrony systemctl start chronyd systemctl enable chronyd chronyc sources -v
二、安装MySQL
-
所有节点都安装
-
这里安装的是mysql-5.7版本,使用的下载好的的rpm包来进行安装
上传mysql57_rpm.tar.gz 包 tar zxf mysql57_rpm.tar.gz cd mysql57_rpm yum -y localinstall *.rpm #启动数据库 systemctl start mysqld systemctl enable mysqld #查看初始密码(一定启动后再查看) cat /var/log/mysqld.log | grep password ##或者 将密码重定向输出到 mysql_passwd.txt 文本中 cat /var/log/mysqld.log | grep password|awk -F ":" '{print $4}'|sed 's/ //g' > mysql_passwd.txt
三、配置MySQL
3.1 修改各数据库密码
msyql -uroot -p密码 #重置root用户密码 set password for root@localhost = password('Yan123456.'); flush privileges;
3.2 部署mysql主从
-
master1 进行slave授权
# 修改 master1 配置文件 vim /etc/my.cnf # 在末尾添加 log-bin=master-bin binlog_format = MIXED server-id=1 # 重启mysqld systemctl restart mysqld # 登录主服务器,给从服务器授权 grant replication slave on *.* to 'myslave'@'172.16.10.%' identified by 'Slave123.'; flush privileges; show master status; +-------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +-------------------+----------+--------------+------------------+-------------------+ | master-bin.000001 | 602 | | | | +-------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec) # 修改slave节点mysql配置文件 ##slave1: vim /etc/my.cnf log-bin=slave-bin binlog_format = MIXED server-id=2 #两个slave的id及主服务器的id必须保持不同 #重启 systemctl restart mysqld ##slave2: vim /etc/my.cnf log-bin=slave-bin binlog_format = MIXED server-id=3 #重启 systemctl restart mysqld #登录从服务器,配置同步(两台slave同样操作) change master to master_host='172.16.10.10',master_user='myslave',master_password='Slave123.',master_log_file='master-bin.000001',master_log_pos=602; #开启slave start slave; #查看slave状态 show slave status\G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 172.16.10.10 Master_User: myslave Master_Port: 3306 Connect_Retry: 60 Master_Log_File: master-bin.000001 Read_Master_Log_Pos: 602 Relay_Log_File: slave1-relay-bin.000002 Relay_Log_Pos: 321 Relay_Master_Log_File: master-bin.000001 Slave_IO_Running: Yes ##值为Yes表示IO线程正常 Slave_SQL_Running: Yes ##值为Yes表示SQL线程正常 Replicate_Do_DB:
四、部署MHA高可用
4.1 安装依赖包
# 所有mysql服务器都要安装 yum -y install epel-release yum -y install perl-DBD-MySQL perl-Config-Tiny perl-Log-Dispatch perl-Parallel-ForkManager perl-ExtUtils-CBuilder perl-ExtUtils-MakeMaker perl-CPAN
4.2 安装MHA
-
在所有服务器上必须先安装 node 组件,最后在 MHA-manager 节点上安装 manager 组件, 因为 manager 依赖 node 组件。
# 安装node组件(所有节点都执行) tar zxf mha4mysql-node-0.57.tar.gz cd mha4mysql-node-0.57/ perl Makefile.PL make && make install # 安装 manager组件,仅在manager节点执行 tar zxf mha4mysql-manager-0.57.tar.gz cd mha4mysql-manager-0.57 perl Makefile.PL make && make install
4.3 免密配置
-
四台需要相互做免密
# manager节点 ssh-keygen ssh-copy-id manager ssh-copy-id master ssh-copy-id slave1 ssh-copy-id slave2 # master节点 ssh-keygen ssh-copy-id manager ssh-copy-id master ssh-copy-id slave1 ssh-copy-id slave2 # slave1节点 ssh-keygen ssh-copy-id manager ssh-copy-id master ssh-copy-id slave1 ssh-copy-id slave2 # slave2节点 ssh-keygen ssh-copy-id manager ssh-copy-id master ssh-copy-id slave1 ssh-copy-id slave2
4.4 配置MHA
# 在 manager 节点上复制相关脚本到/usr/local/bin 目录 cp /root/mha4mysql-manager-0.57/samples/scripts/* /usr/local/bin # 上传master_ip_failover文件并修改 ##修改master_ip_failover文件,定义VIP策略 vim /usr/local/bin/master_ip_failover ================================================= my $vip = '172.16.10.100/24'; '//同网段未被占用的IP地址' my $key = '1'; my $ssh_start_vip = "/sbin/ifconfig ens33:$key $vip up"; '//网卡名称' my $ssh_stop_vip = "/sbin/ifconfig ens33:$key down"; '//网卡名称' GetOptions( # 添加执行权限 chmod +x /usr/local/bin/master_ip_failover # 创建 MHA 软件目录并拷贝配置文件 mkdir /etc/masterha/ cp /root/mha4mha-manager-0.57/samples/conf/app1.cnf /etc/masterha # 修改app1.cnf配置文件,定义转移策略 vim /etc/masterha/app1.cnf ================================================================= [server default] manager_log=/var/log/masterha/app1/manager.log manager_workdir=/var/log/masterha/app1 master_binlog_dir=/var/lib/mysql master_ip_failover_script=/usr/local/bin/master_ip_failover master_ip_online_change_script=/usr/local/bin/master_ip_online_change password=Manager123. ping_interval=1 remote_workdir=/tmp repl_password=Slave123. repl_user=myslave secondary_check_script=/usr/local/bin/masterha_secondary_check -s 172.16.10.11 -s 172.16.10.12 shutdown_script="" ssh_user=root user=mha [server1] hostname=172.16.10.10 port=3306 [server2] candidate_master=1 check_repl_delay=0 hostname=172.16.10.11 port=3306 [server3] hostname=172.16.10.12 port=3306 ================================================================= mkdir -p /var/log/masterha/app1/
4.5 所有数据库节点给manager设置用户并授权;salve节点创建用户myslave(主从复制master创建用户)并授权
# master节点 grant all privileges on *.* to 'mha'@'172.16.10.%' identified by 'Manager123.'; flush privileges; # slave1 节点 grant all privileges on *.* to 'mha'@'172.16.10.%' identified by 'Manager123.'; grant replication slave on *.* to 'myslave'@'172.16.10.%' identified by 'Slave123.'; flush privileges; # slave2 节点 grant all privileges on *.* to 'mha'@'172.16.10.%' identified by 'Manager123.'; grant replication slave on *.* to 'myslave'@'172.16.10.%' identified by 'Slave123.'; flush privileges;
4.6 测试ssh无密码认证和mysql主从复制连接情况
# manager 节点执行 1.检查ssh无密码验证 masterha_check_ssh --conf=/etc/masterha/app1.cnf '//检查ssh是否成功' ##出现All SSH connection tests passed successfully.表示成功 2.检查mysql主从连接情况 masterha_check_repl --conf=/etc/masterha/app1.cnf '//检查MySQL复制运行状况' ##出现MySQL Replication Health is OK表示正常 ================================================================================ '问题报错1:出现MySQL Replication Health is NOT OK' /如果报错检查slave主机是否有主从复制master创建的用户,/ /若没有,两台slave需要创建该用户,并与master创建的一致。/ 'slave1:slave2同配置' mysql> grant replication slave on *.* to myslave@'172.16.10.%' identified by '123456'; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> flush privileges; Query OK, 0 rows affected (0.01 sec) == '问题报错2:如果在执行过程中,有如下报错信息' Can't exec "mysqlbinlog": No such file or directory at /usr/lib64/perl5/vendor_perl/MHA/BinlogManager.pm line 99 # 采用设置软连接的方式解决,三台 MySQL 上都需要执行 ln -s /usr/local/mysql/bin/mysqlbinlog /usr/sbin/ ln -s /usr/local/mysql/bin/mysql /usr/sbin/ '问题报错3:如果有如下报错信息' Bareword "FIXME_xxx" not allowed while "strict subs" in use at /usr/local/bin/master_ip_failover line 100 # 在 manager 机器上面使用如下解决方法 [root@manager ~]# vi /usr/local/bin/master_ip_failover ## Update master ip on the catalog database, etc #FIXME_xxx; '//将此行注释' ==================================================================================
4.7 首次配置 MHA 的 VIP 地址需要手动进行配置
# 在 master 上执行如下命令: ifconfig ens33:1 172.16.10.100
4.8 启动MHA,manager节点执行
# (1)启动MHA nohup masterha_manager \换行 --conf=/etc/masterha/app1.cnf \ '//启动masterha' --remove_dead_master_conf \ '//添加mater宕机就删除maser文件' --ignore_last_failover \ '//忽略宕机不足8小时不进行master转移' < /dev/null > /var/log/masterha/manager.log 2>&1 & '//后台启动manager' # (2)关闭MHA masterha_stop --conf=/etc/masterha/app1.cnf # 或者可以直接采用 kill 进程 ID 的方式关闭。 # (3)查看MHA状态,可以看到当前的master是master节点 masterha_check_status --conf=/etc/masterha/app1.cnf
五、部署mysql读写分离
5.1 安装Amoeba,部署在manager节点
# (1)安装JDK,上传jdk-6u14-linux-x64.bin ## jdk-6u14-linux-x64.bin添加执行权限 chmod +x jdk-6u14-linux-x64.bin ##安装 ./jdk-6u14-linux-x64.bin ## 按“q”快速略过信息,再输入“yes”进行安装,最后根据提示按 ”Enter“ 键完成即可 mv jdk1.6.0_14/ /usr/local/jdk1.6 # 修改环境变量 vim /etc/profile 末行添加: export JAVA_HOME=/usr/local/jdk1.6 export CLASSPATH=$CLASSPATH:$JAVA_HOME/lib:$JAVA_HOME/jre/lib export PATH=$JAVA_HOME/lib:$JAVA_HOME/jre/bin:$PATH:$HOME/bin export AMOEBA_HOME=/usr/local/amoeba/ export PATH=$PATH:$AMOEBA_HOME/bin # 刷新环境变量 source /etc/profile #查看版本 java -version # (2)安装Amoeba mkdir /usr/local/amoeba chmod -R 755 /usr/local/amoeba/ ##上传amoeba-mysql-binary-2.2.0.tar.gz 包 tar zxf amoeba-mysql-binary-2.2.0.tar.gz -C /usr/local/amoeba ##执行下面命令:显示 amoeba start|stop 说明 Amoeba 安装成功 /usr/local/amoeba/bin/amoeba
5.2 配置Amoeba
# 配置Amoeba 读写分离,两个 Slave 读负载均衡 ## Master、Slave1、Slave2 中开放权限给 Amoeba 访问。 grant all on *.* to test@'172.16.10.%' identified by 'Test123.'; flush privileges; ## 编辑 amoeba.xml 配置文件 vim /usr/local/amoeba/conf/amoeba.xml ================================================================= ##…… 以下内容需要修改,并取消注释(-->) <Property name="user">amoeba</property> '//创建通过Amoeba服务登录账户' <property name="password">Amoeba</property> '//创建通过Amoeba服务登录账户密码' .... <property name="defaultPool">master</property> '//默认池为master主服务器' <property name="writePool">master</property> '//写池为master主服务器' <property name="readPool">slaves</property> '//读池为slaves从服务器' .... ================================================================= ## 编辑 dbServers.xml 配置文件 vim /usr/local/amoeba/conf/dbServers.xml ================================================================== ##…… 以下内容需要修改,并取消注释(-->) <!-- mysql user --> <property name="user">test</property> '//Amoeba登录mysql的用户,即主从服务器授权的用户' <property name="password">Test123.</property> '//Amoeba登录mysql用户密码' </factoryConfig> .... <dbServer name="master" parent="abstractServer"> '//主服务器池的定义' <factoryConfig> <!-- mysql ip --> <property name="ipAddress">172.16.10.10</property> '//主服务器池的ip' </dbServer> <dbServer name="slave1" parent="abstractServer"> '//从服务器池的定义' <factoryConfig> <!-- mysql ip --> <property name="ipAddress">172.16.10.11</property> '//从服务器池的ip' </factoryConfig> </dbServer> <dbServer name="slave2" parent="abstractServer"> '//从服务器池的定义,若无slave2 段,手动添加' <factoryConfig> <!-- mysql ip --> <property name="ipAddress">172.16.10.12</property> '//从服务器池的ip' </factoryConfig> </dbServer> <dbServer name="slaves" virtual="true"> '//从服务器读取池的名字为slaves,在前面配置文件定义的' <poolConfig class="com.meidusa.amoeba.server.MultipleServerPool"> <!-- Load balancing strategy: 1=ROUNDROBIN, 2=WEIGHTBASED, 3=HA--> <property name="loadbalance">1</property> <!-- Separated by commas,such as: server1,server2,server1 --> <property name="poolNames">slave1,slave2</property> '//定义从服务器池所包含的从服务器' </poolConfig> </dbServer> ===================================================================== # 配置无误后,可以启动 Amoeba 软件,其默认端口为 tcp 8066 /usr/local/amoeba/bin/amoeba start & ##启动并放在后台运行 netstat -napt |grep java tcp6 0 0 :::8066 :::* LISTEN 48722/java tcp6 0 0 127.0.0.1:62641 :::* LISTEN 48722/java tcp6 0 0 172.16.10.11:58852 172.16.10.10:3306 ESTABLISHED 48311/java tcp6 0 0 172.16.10.11:51664 172.16.10.12:3306 ESTABLISHED 48311/java ###注意:test数据库肯定是要存在的。在 Master、Slave1 和 Slave2 上面创建 test 数据库
5.3 读写分离验证
-
需要安装一台客户机 client 进行验证
# client 节点执行 yum -y install mariadb mysql -uamoeba -pAmoeba123. -h 172.16.10.11 -P8066 create database bk; use bk; create table jifang (id int(10) not null);
-
验证主数据库只写
# 关闭 从数据库slave同步 # slave1 执行 stop slave # slave2 执行 stop slave # client 执行 ##插入数据 insert into jifang values (10); # 分别从主、从数据库查看数据 ## master数据库 select * from bk.jifang; +----+ | id | +----+ | 10 | +----+ 1 row in set (0.00 sec) #有数据 ## slave1数据库 select * from bk.jifang; Empty set (0.00 sec) #没有数据 ## slave2数据库 select * from bk.jifang; Empty set (0.00 sec) #没有数据 说明:数据只在主数据库写入
-
验证从数据库只读
# 关闭 从数据库slave同步 # slave1 执行 stop slave # slave2 执行 stop slave # client 执行 ##再插入数据 insert into jifang values (20); # 分别从主、从数据库查看数据 ## master数据库 select * from bk.jifang; +----+ | id | +----+ | 10 | | 20 | +----+ 2 rows in set (0.00 sec) #有2条数据 ## slave1数据库 select * from bk.jifang; +----+ | id | +----+ | 10 | +----+ 1 row in set (0.00 sec) # 有1条数据 ## slave2数据库 select * from bk.jifang; +----+ | id | +----+ | 10 | +----+ 1 row in set (0.00 sec) # 有1条数据 # 使用client 节点来查数据 select * from bk.jifang; +----+ | id | +----+ | 10 | +----+ 1 row in set (0.00 sec) #仅显示从数据库的1条数据 说明:数据只从从数据库读取