环境：
• 202.106.0.6: as client• 202.106.0.17：as firewall • 202.106.0.147: as powerDNS• 192.168.205.27: as NFS server• 192.168.205.37: as NFS Backup server(inotify+rsync)• 192.168.205.47: as proxysql1• 192.168.205.57: as porxysql2• 192.168.205.67: as MHA for manager mysql master/slave• 192. 168.205.77: as mysql primary• 192.168.205.87: as mysql secondary• 192.168.205.97: as secondary 2• 192.168.205.107: as HAproxy1• 192.168.205.117: as HAproxy2• 192.168.205.127: as web1 (nginx +php+wordpress)• 192.168.205.137: as web2 (nginx +php+wordpress)注：所有操作系统默认停用firewalld,iptable为空,关闭selinux
版本：
OS: centos 7 1810 with mini installpoweradmin-2.1.7.tgzmariadb-5.5.60（主要测试主从自动切换和读写分离没有使用高版本）nginx-1.16.1.tar.gzphp-7.3.7.tar.xzwordpress-5.0.4-zh_CN.tar.gzmha4mysql-manager-0.56-0.el6.noarch.rpm mha4mysql-node-0.56-0.el6.noarch.rpm注：其它未注明均为yum 安装
目地：
• 实现nginx编译安装和支持fast-cgi, 编译安装php7.3支持最新的wordpress,• 通过MHA实现主从的自动切换，通过proxysql实现读写的分离，并实瑞proxy的高可用性• web静态页面存在nfs共享文件中，通地inotify 和rsync进行实时的备份网站的数据。• 前端通过haproxy实现负载均衡
步骤：
1. 安装mariadb并配置主从2. 配置半同步复制3. 实现MHA管理4. 实现proxysql5. 实现proxysql的keepalive6. 安装rsync server做为nfs backup服务器7. 安装nginx和php8. 安装haproxy9. 实现firewall DNAT10. 测试
安装mariadb,实现主从
通过脚本yum真接安装mariadb在77，87，97上，安装完成后自动重启[root@master data]#vi maridb_yum.sh #!/bin/bashID=`ip a show dev eth0 | sed -r '3!d;s@(.*inet)(.*)(/.*)@\2@' | cut -d. -f4`rpm -q mariadb-server ||yum install -y mariadb-server[ -d /data/mysql ] || mkdir -p /data/mysql[ -d /data/logs ] || mkdir -p /data/logschown mysql:mysql /data/{mysql,logs}sed -i 's@datadir=/var/lib/mysql@datadir=/data/mysql@' /etc/fgrep "log-bin" /etc/f || sed -i '/\[mysqld\]/a log-bin=/data/logs/bin' /etc/frep "innodb_file_per_table" /etc/f || sed -i '/\[mysqld\]/a innodb_file_per_table = on' /etc/fgrep "skip_name_resolve" /etc/f || sed -i '/\[mysqld\]/a skip_name_resolve = on' /etc/fgrep "server-id" /etc/f || sed -i "/\[mysqld\]/a server-id=$ID" /etc/fservice mariadb restart
修改slave数据库的配置
[root@slave1 ~]#vi /etc/f[mysqld]read_onlyrelay_log_purge=0[root@slave1 ~]#systemctl restart mariadb [root@slave2 ~]#vi /etc/f[mysqld]read_only[root@slave2 ~]#systemctl restart mariadb
在主服务器上记录复制位置
MariaDB [(none)]> show master logs;
在主服务器创建同步帐号
MariaDB [(none)]> grant replication slave on *.* to repluser'192.168.205.%' identified by 'centos';
分别在从服务器上修改change master to
CHANGE MASTER TOMASTER_HOST='192.168.205.77',MASTER_USER='repluser',MASTER_PASSWORD='centos',MASTER_PORT=3306,MASTER_LOG_FILE='bin.000003', #此文件跟据主上的show master logsMASTER_LOG_POS=245;#此位置跟据主上的show master logs
在所有的从节点上启动i/o线和relay线程
MariaDB [(none)]> satar slaveMariaDB [(none)]> show slave status;
配置半同步复制
记录半同步插件的文件名称[root@master ~]#rpm -ql mariadb-server/usr/lib64/mysql/plugin/semisync_master.so/usr/lib64/mysql/plugin/semisync_slave.so
在主服务器和从服务器上安装半同步插件
MariaDB [(none)]> install plugin rpl_semi_sync_master soname 'semisync_master.so';
查看半同步的状态
MariaDB [(none)]> show global variables like '%semi%' ;+------------------------------------+-------+| Variable_name | Value |+------------------------------------+-------+| rpl_semi_sync_master_enabled | OFF | #半同步默认off| rpl_semi_sync_master_timeout | 10000 | #超时毫秒，10秒| rpl_semi_sync_master_trace_level | 32 || rpl_semi_sync_master_wait_no_slave | ON |+------------------------------------+-------+4 rows in set (0.00 sec)
启用半同步
MariaDB [(none)]> set global rpl_semi_sync_master_enabled=on;Query OK, 0 rows affected (0.00 sec)
查看半同步状态信息
MariaDB [(none)]> show global status like '%semi%';+--------------------------------------------+-------+| Variable_name| Value |+--------------------------------------------+-------+| Rpl_semi_sync_master_clients| 0|| Rpl_semi_sync_master_net_avg_wait_time| 0|| Rpl_semi_sync_master_net_wait_time | 0|| Rpl_semi_sync_master_net_waits | 0|| Rpl_semi_sync_master_no_times | 0|| Rpl_semi_sync_master_no_tx | 0|| Rpl_semi_sync_master_status| ON || Rpl_semi_sync_master_timefunc_failures| 0|| Rpl_semi_sync_master_tx_avg_wait_time| 0|| Rpl_semi_sync_master_tx_wait_time| 0|| Rpl_semi_sync_master_tx_waits | 0|| Rpl_semi_sync_master_wait_pos_backtraverse | 0|| Rpl_semi_sync_master_wait_sessions | 0|| Rpl_semi_sync_master_yes_tx| 0|+--------------------------------------------+-------+14 rows in set (0.00 sec)
在所有的从节点安装slave semi插件；
MariaDB [(none)]> install plugin rpl_semi_sync_slave soname 'semisync_slave.so';Query OK, 0 rows affected (0.00 sec)MariaDB [(none)]> show plugins;…| rpl_semi_sync_slave | ACTIVE | REPLICATION | semisync_slave.so | GPL|+--------------------------------+----------+--------------------+-------------------+---------+43 rows in set (0.00 sec)
查看所有从节点的半同步状态，并启用半同步状态，需要重新启动线程，再查看半同步状态是on才可以
MariaDB [(none)]> show global variables like '%semi%';+---------------------------------+-------+| Variable_name | Value |+---------------------------------+-------+| rpl_semi_sync_slave_enabled| OFF || rpl_semi_sync_slave_trace_level | 32 |+---------------------------------+-------+2 rows in set (0.00 sec)MariaDB [(none)]> set global rpl_semi_sync_slave_enabled=on; Query OK, 0 rows affected (0.00 sec)MariaDB [(none)]> show global variables like '%semi%';+---------------------------------+-------+| Variable_name | Value |+---------------------------------+-------+| rpl_semi_sync_slave_enabled| ON || rpl_semi_sync_slave_trace_level | 32 |+---------------------------------+-------+2 rows in set (0.00 sec)MariaDB [(none)]> stop slave;Query OK, 0 rows affected (0.00 sec)MariaDB [(none)]> start slave;Query OK, 0 rows affected (0.00 sec)MariaDB [(none)]> show global status like '%semi%'; +----------------------------+-------+| Variable_name | Value |+----------------------------+-------+| Rpl_semi_sync_slave_status | ON |+----------------------------+-------+1 row in set (0.00 sec)
此时查看主节点的状态
MariaDB [(none)]> show global status like '%semi%';+--------------------------------------------+-------+| Variable_name| Value |+--------------------------------------------+-------+| Rpl_semi_sync_master_clients| 2| #表示已经有两个客户端，说明成功| Rpl_semi_sync_master_net_avg_wait_time| 0|| Rpl_semi_sync_master_net_wait_time | 0|| Rpl_semi_sync_master_net_waits | 0|| Rpl_semi_sync_master_no_times | 0|| Rpl_semi_sync_master_no_tx | 0|| Rpl_semi_sync_master_status| ON | #状态是on表半同步打开| Rpl_semi_sync_master_timefunc_failures| 0|| Rpl_semi_sync_master_tx_avg_wait_time| 0|| Rpl_semi_sync_master_tx_wait_time| 0|| Rpl_semi_sync_master_tx_waits | 0|| Rpl_semi_sync_master_wait_pos_backtraverse | 0|| Rpl_semi_sync_master_wait_sessions | 0|| Rpl_semi_sync_master_yes_tx| 0|+--------------------------------------------+-------+14 rows in set (0.00 sec)
实现MHA管理
安装从mha网站下载的mha rpm软件包，依赖包要去epel下载，所以要启用epel源[root@MHA ~]#yum install mha4mysql-manager-0.56-0.el6.noarch.rpm mha4mysql-node-0.56-0.el6.noarch.rpm
所有节点上安装node包，无论主不是从，mha都会当做一个node来看
[root@master ~]#yum install mha4mysql-node-0.56-0.el6.noarch.rpm [root@slave1 data]#yum install mha4mysql-node-0.56-0.el6.noarch.rpm [root@slave2 data]#yum install mha4mysql-node-0.56-0.el6.noarch.rpm
在主服务器上创建帐号做为mha的监控帐号使用
MariaDB [(none)]> grant all on *.* to mhauser@'192.168.205.%' identified by 'centos';
由于主从在切换时，MHA要修改配置文件等，所以需要ssh key验证，我们采用速的方法，直接在本地产生key和authorized文件一并复制到所有节点
[root@MHA ~]#ssh-keygen [root@MHA ~]#ssh-copy-id 192.168.205.67[root@MHA ~]#cat .ssh/authorized_keys [root@MHA ~]#scp -r .ssh 192.168.205.77:/root/[root@MHA ~]#scp -r .ssh 192.168.205.87:/root/[root@MHA ~]#scp -r .ssh 192.168.205.97:/root/
MHA配置文件没有，我们直接按照如下创建
[root@MHA ~]#mkdir /etc/mha[root@MHA ~]#vim /etc/mha/f[server default]master_binlog_dir=/data/logs/ user=mhauser#这个帐号为mha监控mysql的帐号password=centos #帐号的密码manager_workdir=/data/mastermha/app1/ #配置文件的存放位置manager_log=/data/mastermha/app1/manager.log #日志的存放位置remote_workdir=/data/mastermha/app1/ ssh_user=root #SSH key连接的用户名repl_user=repluser#复制帐号，也就是我们上面创建的复制帐号名repl_password=centos #复制帐号的密码ping_interval=1 #检测间隔为每一秒[server1] #定议节点服务器hostname=192.168.205.77 candidate_master=1[server2]hostname=192.168.205.87 [server3]hostname=192.168.205.97 candidate_master=1 #表示当主不可用时优先提升为主的从服务器
启动之前进行检查ssh，repl复制是否准备好, 如果successful 我们可以进行下一步。
[root@MHA ~]#masterha_check_ssh --conf=/etc/mha/f[root@MHA ~]#masterha_check_repl --conf=/etc/mha/f
起动进程，此进程前台运行，当主节点失败时切换完成后它会终止，所以要想持续要重启进程
[root@MHA ~]#masterha_manager --conf=/etc/mha/fMon Aug 12 23:33:22 - [warning] Global configuration file /etc/f not found. Skipping.Mon Aug 12 23:33:22 - [info] Reading application default configuration from /etc/mha/f..Mon Aug 12 23:33:22 - [info] Reading server configuration from /etc/mha/f..
实现proxysql
我们直接在两个proxysql服务器上创建yum源来安装proxysqlcat <<EOF | tee /etc/yum.repos.d/proxysql.repo[proxysql_repo]name= ProxySQL YUM repository#baseurl=/ProxySQL/proxysql-2.0.x/centos/\$releaseverbaseurl=/ProxySQL/proxysql-1.4.x/centos/\$releasevergpgcheck=1gpgkey=/ProxySQL/repo_pub_keyEOF
因为proxysql是一个基于轻量的数据库配置的，所以我们需要一个sql客户端来连接设置proxysql
[root@proxysql1 ~]#yum install proxysql mariadb[root@proxysql2 ~]#yum install proxysql mariadb
启动服务器查看端口，其中6032为proxysql的数据库端口，6033为proxysql的用户连接端口
[root@proxysql1 ~]#service proxysql start[root@proxysql2 ~]#service proxysql start[root@proxysql1 ~]#ss -ntlState Recv-Q Send-Q Local Address:Port Peer Address:Port LISTEN0128 *:6032 *:* LISTEN0128 *:6033 *:* LISTEN0128 *:6033 *:* LISTEN0128 *:6033 *:* LISTEN0128 *:6033 *:*
proxysql默认的用户名和密码是admin admin， 监听端口为6032,
[root@proxysql1 ~]#mysql -uadmin -padmin -P6032 -h127.0.0.1
在所有的proxysql注册sql server的节点，包括所有的主从节点，使有和insert插入记录到mysql_servers库用
MySQL [(none)]> insert into mysql_servers(hostgroup_id,hostname,port) values(10,'192.168.205.77',3306);MySQL [(none)]> insert into mysql_servers(hostgroup_id,hostname,port) values(10,'192.168.205.87',3306);MySQL [(none)]> insert into mysql_servers(hostgroup_id,hostname,port) values(10,'192.168.205.97',3306);MySQL [(none)]> load mysql servers to runtime;MySQL [(none)]> save mysql servers to disk;
查看一下我们刚才添加的主机记录是否在库中
MySQL [(none)]> select * from mysql_servers;
由于proxysql是查看主和从的数据的read_only来判读谁是主谁是从的，所以建立一账号用来连接到主和从服务器上，我们要在主节点上建立这个帐号，它会复制到从节点上
MariaDB [(none)]> grant replication client on *.* to monitor@'192.168.205.%' identified by 'centos';
在所有的proxysql服务器设置监控账号，保存状态
MySQL [(none)]> set mysql-monitor_username='monitor';MySQL [(none)]> set mysql-monitor_password='centos'; MySQL [(none)]> load mysql variables to runtime; MySQL [(none)]> save mysql variables to disk;
查看一下相关的日志，以前出错的原因是因为默认没使用monitor密码是monitor进行连接(在f中可以看到)，所以会出错，当你添加完帐号就成功了,
MySQL [(none)]> select * from mysql_server_connect_log;MySQL [(none)]> select * from mysql_server_ping_log;
需要修改的是main库中的mysql_replication_hostgroups表，该表有3个字段：writer_hostgroup, reader_hostgroup，comment, 指定写组的id为10，读组的id为20
MySQL [(none)]> insert into mysql_replication_hostgroups values(10,20,"test");MySQL [(none)]> load mysql servers to runtime; MySQL [(none)]> save mysql servers to disk;
proxySQL会跟据刚才连接帐号判断read_only并自动的把三个服务器按读写组加到这个表中了
MySQL [(none)]> select hostgroup_id,hostname,port,status,weight from mysql_servers;MySQL [(none)]> select * from mysql_server_read_only_log;
在主服务器上设置一个帐号用来测试
MySQL [(none)]> grant all on *.* to sqluser@'192.168.205.%' identified by 'centos';
在proxysql服务器上设置这个帐号的缺省组为10
MySQL [(none)]> insert into mysql_users(username,password,default_hostgroup) values('sqluser','centos',10);MySQL [(none)]> load mysql users to runtime;MySQL [(none)]> save mysql users to disk;
此时proxysql还是不知道那些sql语句算读，那些为写，我们要定义好，让它来区别并发送到不同的组服务器上,其中10为写，20组为读
MySQL [(none)]>insert into mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply) VALUES(1,1,'^SELECT.*FOR UPDATE$',10,1),(2,1,'^SELECT',20,1); MySQL [(none)]>load mysql query rules to runtime;MySQL [(none)]>save mysql query rules to disk;
查看一下你添加的规则
MySQL [(none)]>select rule_id,active,match_digest,destination_hostgroup,apply from mysql_query_rules;
测试连接并实现读，可以看到一会调度到87，一会调度到97
mysql -usqluser -pcentos -P6033 -h127.0.0.1 -e 'select @@server_id'
如果使用事务不能发送到读服务器，只会发送到主服务器
mysql -usqluser -pcentos -P6033 -h127.0.0.1 -e 'begin;select @@server_id;commit' mysql -usqluser -pcentos -P6033 -h127.0.0.1 -e 'create database testdb' mysql -usqluser -pcentos testdb -P6033 -h127.0.0.1 -e 'create table t1(id int)'
我们可以用下列查询看调度是否成功，并调度到那台服务器上
select hostgroup hg,sum_time,count_star,digest_text from stats_mysql_query_digest order by sum_time desc;
实现proxysql的keepalive
使用yum安装keepalvie，psmisc，psmisc中有个命令为killall可以检测到进程的状态，keepalive会使用这个做为脚本检测进行是否运行[root@proxysql1 ~]#yum install keepalived ipvsadm psmisc[root@proxysql2 ~]#yum install keepalived ipvsadm psmisc
修改keepalive配置文件
[root@proxysql1 ~]#vi /etc/keepalived/keepalived.conf global_defs {notification_email {root@localhost}notification_email_from keepalived@localhostsmtp_server 127.0.0.1smtp_connect_timeout 30router_id LVS_1vrrp_mcast_group4 224.0.0.100}# Script used to check if Proxy is runningvrrp_script check_proxy {#定义脚本测试proxysql的进程，如果进程down实现主备切换script "killall -0 proxysql"#监控进程interval 2 #每二秒监控一次weight -30 #进程down时priority减30fall 2rise 1}vrrp_instance VI_1 {state MASTERinterface eth0virtual_router_id 45priority 100advert_int 1authentication {auth_type PASSauth_pass 1111}virtual_ipaddress {192.168.205.45/24 dev eth0 label eth0:0}track_script {check_proxy}}[root@proxysql1 ~]#systemctl start keepalived
修改proxysql2的keepalive
[root@proxysql2 ~]#vi /etc/keepalived/keepalived.confglobal_defs {notification_email {root@localhost}notification_email_from keepalived@localhostsmtp_server 127.0.0.1smtp_connect_timeout 30router_id LVS_2vrrp_mcast_group4 224.0.0.100}# Script used to check if Proxy is runningvrrp_script check_proxy {script "killall -0 proxysql"interval 2weight -30fall 2rise 1}vrrp_instance VI_1 {state BACKUP interface eth0virtual_router_id 45priority 80advert_int 1authentication {auth_type PASSauth_pass 1111}virtual_ipaddress {192.168.205.45/24 dev eth0 label eth0:0}track_script {check_proxy}}[root@proxysql2 ~]#systemctl start keepalived
在proxysql1上查看IP，可以看到vip 192.168.205.45, 停掉服务，看一下IP
[root@proxysql1 ~]#ip a2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000link/ether 00:0c:29:37:f9:93 brd ff:ff:ff:ff:ff:ffinet 192.168.205.47/24 brd 192.168.205.255 scope global noprefixroute eth0valid_lft forever preferred_lft foreverinet 192.168.205.45/24 scope global secondary eth0:0valid_lft forever preferred_lft foreverinet6 fe80::20c:29ff:fe37:f993/64 scope link noprefixroute valid_lft forever preferred_lft forever[root@proxysql1 ~]#systemctl stop proxysql
在proxysql2上查看可以看到vip 192.168.205.45已经飘到了proxysql2上
[root@proxysql2 ~]#ip a2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000link/ether 00:0c:29:cf:e5:bb brd ff:ff:ff:ff:ff:ffinet 192.168.205.57/24 brd 192.168.205.255 scope global noprefixroute eth0valid_lft forever preferred_lft foreverinet 192.168.205.45/24 scope global secondary eth0:0valid_lft forever preferred_lft foreverinet6 fe80::20c:29ff:fecf:e5bb/64 scope link noprefixroute valid_lft forever preferred_lft forever
我们测试一下能否通过web server通过连接vip连接到后端的服务器，从而实现读写分离
[root@web1 data]#mysql -uwordpress -pcentos -P6033 -h192.168.205.45
安装rsync server做为nfs backup服务器
安装rsync服务[root@nfs2 data]#yum install rsync
编辑rsyncd.conf文件，让rsyncc以daemon的方式运行
[root@nfs2 data]#vi /etc/rsyncd.confuid = root #以什么身份开启服务gid = rootuse chroot = no max connections = 0 #不限制连接ignore errors#忽略错误exclude = lost+found/log file = /var/log/rsyncd.logpid file = /var/run/rsyncd.pidlock file = /var/run/rsyncd.lockreverse lookup = no 反向解析名称与iphosts allow = 192.168.205.0/24 充许连接的主机列表[backup] 起名子path = /data/www/comment = webserver www backupread only = no 可以写auth users = rsyncsecrets file = /etc/rsync.pass 密码文件
服务器端生成验证文件
[root@nfs2 data]#echo "rsync:centos" > /etc/rsync.pass[root@nfs2 data]#chmod 600 /etc/rsync.pass
服务器端准备目录
[root@nfs2 data]#mkdir /data/www
服务器端启动rsync服务
[root@nfs2 data]#systemctl start rsyncd
启用NFS服务器
启用epel[root@nfs1 data]#yum install inotify-tools nfs-utils rsync
创建目录
[root@nfs1 data]#mkdir /data/www
创建用户nginx用户
[root@nfs1 data]# useradd -s /sbin/nologin nginx -u 2000[root@nfs1 data]# id nginxuid=2000(nginx) gid=2000(nginx) groups=2000(nginx)
修改NFS配置文件，共享www目录
[root@nfs1 data]# vi /etc/exports/data/www 192.168.205.0/24(rw,all_squash,anonuid=2000,anongid=2000) [root@nfs1 data]# systemctl restart nfs-server
将NFS服务器配置为rsync的客户端，先配置密码文件
[root@nfs1 data]#echo "centos" > /etc/rsync.pass[root@nfs1 data]#chmod 600 /etc/rsync.pass
安装inotify实现实时同步数据，客户端创建inotify_rsync.sh脚本
[root@nfs1 data]#cat inotify_rsync.sh #!/bin/bashSRC='/data/www/'DEST='rsync@192.168.205.37::backup'inotifywait -mrq --timefmt '%Y-%m-%d %H:%M' --format '%T %w %f' \-e create,delete,moved_to,close_write,attrib ${SRC} | \while read DATE TIME DIR FILE;doFILEPATH=${DIR}${FILE}rsync -az --delete --password-file=/etc/rsync.pass $SRC $DEST && echo "At ${TIME} \on ${DATE}, file $FILEPATH was backuped up via rsync" >> /var/log/changelist.logdone
将脚本文件存在rc.local中，启动进会自动执行
[root@nfs1 data]#chmod +x inotify_rsync.sh[root@nfs1 data]#vi /etc/rc.d/rc.local/data/inotify_rsync.sh & [root@nfs1 data]#chmod +x /etc/rc.d/rc.local
在web server中实现挂载，安装nfs-utils工具才可以mount NFS文件系统
[root@web1 ~]#yum install nfs-utils [root@web2 ~]#yum install nfs-utils
测试连接NFS服务器的共享文件
[root@web1 ~]#showmount -e 192.168.205.27Export list for 192.168.205.27:/data/www 192.168.205.0/24[root@web1 ~]#mount 192.168.205.27:/data/www /data/www[root@web2 ~]#mount 192.168.205.27:/data/www /data/www[root@web1 ~]#df[root@web2 ~]#df
在两个web server中将mount写入到fstab文件中，实现开机自动mount
[root@web1 ~]##vi /etc/fstab192.168.205.27:/data/www /app/httpd24/htdocs nfs defaults 0 0
安装nginx和php
复制nginx和php源码文件到一个目录中在两个web服务器上nginx-1.16.1.tar.gzphp-7.3.7.tar.xz
执行nginx安装脚本在同一个目录中
[root@web2 ~]#cat nginx_install_bin.sh #!/bin/bash##########################difination variables#########################TMP_DIR=`pwd`NGINX="nginx-1.16.1.tar.gz"NGINX_DIR=`echo "$NGINX" |rev | cut -d. -f3- | rev`INS_DIR="/apps/nginx"###########################Packges check and install##########################pkg(){for i in $PKGS; dorpm -q $i &> /dev/null && echo "Packge `rpm -q $i` is installed" || yum -y install $idone}######################NGINX INSTALLATION#1.unarchive binary #####################install dependency packges,just add packges name to variable PKGS separate by SPACE.#Example PKGS="libaio gcc glibc" nginx_ins(){PKGS="gcc pcre-devel openssl-devel zlib-devel"pkgcd $TMP_DIR[ -e $TMP_DIR/$NGINX ] || wget -c /download/$NGINXtar xf $TMP_DIR/$NGINXcd $NGINX_DIR./configure \--prefix=$INS_DIR \--conf-path=/etc/nginx/nginx.conf \--error-log-path=/var/log/nginx/error.log \--http-log-path=/var/log/nginx/access.log \--pid-path=/var/run/nginx.pid \--lock-path=/var/run/nginx.lock \--user=nginx \--group=nginx \--with-http_ssl_module \--with-http_v2_module \--with-http_dav_module \--with-http_stub_status_module \--with-http_gzip_static_module \--with-http_realip_module \--with-pcre \--with-threads \--with-file-aio \--with-stream \--with-stream_ssl_module \--with-stream_realip_moduleif [ $? -eq 0 ]; thenmake -j 4 && make installcd $TMP_DIRrm -rf $HTTPD_DIRelseecho "please remake and make install"fiid nginx || useradd nginx -s /sbin/nologin -u 2000chown nginx:nginx -R $INS_DIRecho "$INS_DIR/sbin/nginx" >> /etc/rc.d/rc.localchmod +x /etc/rc.d/rc.localln -s $INS_DIR/sbin/nginx /sbin/nginxnginx}################OPTIONS SELECT###############case $1 in install)nginx_ins;;remove)nginx_rm;;*)echo "Useage $0 $1 install|remove";;esac[root@web2 ~]#./nginx_install_bin.sh install
执行如下脚本安装php
[root@web2 ~]#cat apache_php_install.sh #!/bin/bash##########################difination variables#########################TMP_DIR=`pwd`APR="apr-1.7.0.tar.bz2"APR_UTIL="apr-util-1.6.1.tar.bz2"HTTPD="httpd-2.4.39.tar.bz2"PHP="php-7.3.7.tar.xz"HTTPD_DIR=`echo "$HTTPD" |rev | cut -d. -f3- | rev`APR_DIR=`echo "$APR" |rev | cut -d. -f3- | rev`APR_UTIL_DIR=`echo "$APR_UTIL" | rev | cut -d. -f3- | rev`PHP_DIR=`echo "$PHP" | rev | cut -d. -f3- | rev`INS_HTTPD_DIR=/app/httpd24INS_PHP_DIR=/app/php###########################Packges check and install##########################pkg(){for i in $PKGS; dorpm -q $i &> /dev/null && echo "Packge `rpm -q $i` is installed" || yum -y install $idone}######################APACHE INSTALLATION#1.unarchive binary #####################install dependency packges,just add packges name to variable PKGS separate by SPACE.#Example PKGS="libaio gcc glibc" httpd_ins(){PKGS="gcc prce-devel openssl-devel expat-devel lbzip2"pkgcd $TMP_DIRif [ -e $TMP_DIR/$HTTPD ]; thentar xf $HTTPDelseecho "file $HTTPD does not exist, pleases download it" exitfiif [ -e $TMP_DIR/$APR ]; thentar xf $APR mv $APR_DIR $HTTPD_DIR/srclib/aprelseecho "file $APR does not exist, pleases download it"exitfiif [ -e $TMP_DIR/$APR_UTIL ]; thentar xf $APR_UTILmv $APR_UTIL_DIR $HTTPD_DIR/srclib/apr-utilelseecho "file $APR_UTIL does not exist, pleases download it"exitfi#########################2.make and make install########################id apache||useradd -r -s /sbin/nologin apachecd $HTTPD_DIR./configure \--prefix=$INS_HTTPD_DIR \--enable-so \--enable-ssl \--enable-cgi \--enable-rewrite \--with-zlib \--with-pcre \--with-included-apr \--enable-modules=most \--enable-mpms-shared=all \--with-mpm=preforkif [ $? -eq 0 ]; thenmake -j 4 && make installcd $TMP_DIRrm -rf $HTTPD_DIRelseecho "please remake and make install"fi######################3. modify config files######################sed -ri 's@^(.*) daemon$@\1 apache@' $INS_HTTPD_DIR/conf/httpd.confsed -ri 's@DirectoryIndex@DirectoryIndex index.php @' $INS_HTTPD_DIR/conf/httpd.confsed -ri 's@#(LoadModule proxy_module modules/mod_proxy.so)@\1@' $INS_HTTPD_DIR/conf/httpd.confsed -ri 's@#(LoadModule proxy_fcgi_module modules/mod_proxy_fcgi.so)@\1@' $INS_HTTPD_DIR/conf/httpd.confsed -ri 's@#(LoadModule proxy_fdpass_module modules/mod_proxy_fdpass.so)@\1@' $INS_HTTPD_DIR/conf/httpd.confcat >> $INS_HTTPD_DIR/conf/httpd.conf <<-EOFAddType application/x-httpd-php .phpAddType application/x-httpd-php-source .phpsProxyRequests OffProxyPassMatch "^/.*\.php(/.*)?$" "fcgi://localhost:9000/app/httpd24/htdocs/"EOFecho "PATH=$INS_HTTPD_DIR/bin:\$PATH" > /etc/profile.d/httpd.shsource /etc/profile.d/httpd.shecho "$INS_HTTPD_DIR/bin/apachectl start" >> /etc/rc.d/rc.localchmod +x /etc/rc.d/rc.localapachectl start}##########################PHP INSALLATION#1. unarchive install packge#########################php_ins(){PKGS="libxml2-devel bzip2-devel libmcrypt-devel gd-devel"pkgcd $TMP_DIRecho $TMP_DIRif [ -e $TMP_DIR/$PHP ]; thentar xvf $PHPelseecho "files $PHP does not exist, pleases download it"exitfi########################2. make and make install#######################cd $PHP_DIR/./configure \--prefix=$INS_PHP_DIR \--enable-mysqlnd \--with-mysqli=mysqlnd \--with-pdo-mysql=mysqlnd \--with-openssl \--with-freetype-dir \--with-jpeg-dir \--with-png-dir \--with-zlib \--with-libxml-dir=/usr \--with-config-file-path=/etc \--with-config-file-scan-dir=/etc/php.d \--enable-mbstring \--enable-xml \--enable-sockets \--enable-fpm \--enable-maintainer-zts \--disable-fileinfo \--with-gd \--with-imap\--with-ldap\--with-odbcver\--with-iodbc\--with-pear\--with-libxml-dir\--with-xmlrpc\--enable-mbstring\--with-mhash\--with-gettextif [ $? -eq 0 ]; then make && make installelseecho "please re make and make install"fi#######################3. modify config file######################cp php.ini-production /etc/php.inicp sapi/fpm/init.d.php-fpm /etc/init.d/php-fpm chmod +x /etc/init.d/php-fpmcd $INS_PHP_DIR/etccp php-fpm.conf.default php-fpm.confcd php-fpm.d/cp www.conf.default www.confsed -ri 's@(^.*) = nobody@\1 = apache@' $INS_PHP_DIR/etc/php-fpm.d/www.confchkconfig --add php-fpmservice php-fpm startcd $TMP_DIRrm -rf $PHP_DIR echo '<?php phpinfo(); ?>' > /app/httpd24/htdocs/index.php }###################4. remove PHP#################rmphp(){service php-rpm stoprm /app/php -rfrm /etc/php.ini -fchkconfig --del php-fpmrm /etc/init.d/php-fpm -f}#################5. remove HTTPD################rmhttpd(){apachectl stoprm /app/httpd24 -rfsed -i '/\/app\/http24\/bin\/apachectl start/d' /etc/rc.d/rc.localrm /etc/profile.d/httpd.sh}################OPTIONS SELECT###############case $1 in install)case $2 inhttpd)httpd_ins;;php)php_ins;;*)echo "Useage $0 $1 httpd|php";;esac;;remove)case $2 inphp)rmphp;;httpd)rmhttpd;;*)echo "Useage $0 $1 httpd|php";;esac;; *)echo "Useage $0 <install|remove> <php|httpd>";;esac[root@web2 ~]#./apache_php_install.sh install php
修改nginx配置文件
[root@web2 ~]#vi /etc/nginx/nginx.confserver {listen 80;server_name ;location / {root /data/www;index index.php index.html index.htm;}location ~ \.php$ {root /data/www;fastcgi_pass 127.0.0.1:9000;fastcgi_index index.php;fastcgi_param SCRIPT_FILENAME $document_root$fastcgi_script_name;include fastcgi_params;}}
修改php配置文件
[root@web2 ~]#vi /app/php/etc/php-fpm.d/www.confuser = nginxgroup = nginxlisten = 127.0.0.1:9000listen.owner = nginxlisten.group = nginxlisten.mode = 0660
将所有配置文件复制到所有的web server中并启动服务
[root@web2 ~]#nginx -s reload[root@web2 ~]#service php-fpm restart
解压缩文件 wordpress-5.0.4-zh_CN.tar.gz
[root@web1 data]#tar xf wordpress-5.0.4-zh_CN.tar.gz -C www
在主服务器上创建wordpress数据库及用户名和密码
MariaDB [(none)]> CREATE DATABASE wordpress;MariaDB [(none)]> GRANT ALL PRIVILEGES ON wordpress.* TO "wordpress"@"192.168.205.%" IDENTIFIED BY "centos";MariaDB [(none)]> FLUSH PRIVILEGES;
同样我也需要将这个帐号在所有的proxysql服务器授权
[root@proxysql1 ~]#mysql -uadmin -padmin -P6032 -h127.0.0.1[root@proxysql2 ~]#mysql -uadmin -padmin -P6032 -h127.0.0.1所有proxysql中执行如下：MySQL [(none)]> insert into mysql_users(username,password,default_hostgroup) values('wordpress','centos',10);MySQL [(none)]> load mysql users to runtime;MySQL [(none)]> save mysql users to disk;
修改wordpress配置文件
[root@web1 www]#cp wp-config-sample.php wp-config.php[root@web1 www]#vi wp-config.php define('DB_NAME', 'wordpress');define('DB_USER', 'wordpress');define('DB_PASSWORD', 'centos');define('DB_HOST', '192.168.205.45:6033');#注意这里要添加proxysql vip的地址，端口为6033
生成密钥，可以使用网站/secret-key/1.1/salt/ 自动生成，然后直接替换即可
[root@web1 www]#vi wp-config.phpdefine('AUTH_KEY', ']xRUezwud7/sl9n{5Qv-=VM|uoqaFauAuc3|6wy<w7Dg0qUC7{.4%#>o+HfjC!I+');define('SECURE_AUTH_KEY', '=e[P3g1~S|:+J@I)f-(:MTf3~h+;hQCg?wuk50NMP)Dgoj3X kL@BDDk%&;zed^`');define('LOGGED_IN_KEY', 'f,B`O^3qW20-,`k>dHdW8Bt^/]HZ5 -sA1rz$x:|x3R3~!j*}^mw?0|N)YTO<usi');define('NONCE_KEY', 'x/7V-u*8K^d-|3a&L}/V&2b9K}G+r-q&A7NCWin}h3dP1P( /X;fRzqG1U[,;F_C');define('AUTH_SALT', 'U,kjv 5&srgsePiCJOxUxc+>HkX#B3:fWbQ;[n^5FD)-4r9C!/+Swwv:k~~HZ|-l');define('SECURE_AUTH_SALT', ';=3HS/eY&DRN0p1_->e#]%h#x=*Q?Zj]A*tC=@*H$9_T%+SF+!w0?b}f/`#K&[h&');define('LOGGED_IN_SALT', 'iVWA_K4+X&guJiXc90L4UnQ-#E7+q--rH1_`nhdbSzlC2X.}}R11aua{>8 <hQv:');define('NONCE_SALT', 'z4,S7_]&70?7^p[o>$n7tJAq]?12ngpfi(]Cl{zfKs>!.Y?9|4@59{R*Q<k(Hg_.');
此时我们可以直接访问web1或web2来测试
安装haproxy
在所有的proxy server上安装HAproxy和keepalived[root@haproxy1 ~]#yum install haproxy keepalived[root@haproxy2 ~]#yum install haproxy keepalived
修改proxysql1的keepalive配置
[root@haproxy1 ~]#vi /etc/keepalived/keepalived.conf global_defs {notification_email {acassen@firewall.locfailover@firewall.locsysadmin@firewall.loc}notification_email_from Alexandre.Cassen@firewall.locsmtp_server 192.168.200.1smtp_connect_timeout 30router_id LVS_1vrrp_skip_check_adv_addrvrrp_strictvrrp_iptablesvrrp_garp_interval 0vrrp_gna_interval 0}# Script used to check if HAProxy is runningvrrp_script check_haproxy {script "killall -0 haproxy"interval 2weight -30fall 2rise 1}vrrp_instance VI_1 {state MASTERinterface eth0virtual_router_id 111priority 100advert_int 1authentication {auth_type PASSauth_pass 1111}virtual_ipaddress {192.168.205.111/24 dev eth0 label eth0:0}track_script {check_haproxy}}
修改proxysql2的keepalived配置
[root@haproxy2 ~]#vi /etc/keepalived/keepalived.conf global_defs {notification_email {acassen@firewall.locfailover@firewall.locsysadmin@firewall.loc}notification_email_from Alexandre.Cassen@firewall.locsmtp_server 192.168.200.1smtp_connect_timeout 30router_id LVS_2vrrp_skip_check_adv_addrvrrp_strictvrrp_iptablesvrrp_garp_interval 0vrrp_gna_interval 0}# Script used to check if HAProxy is runningvrrp_script check_haproxy {script "killall -0 haproxy"interval 2weight -30fall 2rise 1}vrrp_instance VI_1 {state BACKUPinterface eth0virtual_router_id 111priority 80advert_int 1authentication {auth_type PASSauth_pass 1111}virtual_ipaddress {192.168.205.111/24 dev eth0 label eth0:0}track_script {check_haproxy}}
启动服务
[root@haproxy1 ~]#systemctl enable keepalived[root@haproxy1 ~]#systemctl start keepalived [root@haproxy2 ~]#systemctl enable keepalived[root@haproxy2 ~]#systemctl start keepalived
修改haproxy配置文件在所有的haproxy server上
[root@haproxy1 ~]#vi /etc/haproxy/haproxy.cfg defaultsmodehttplog globaloption httplogoption dontlognulloption http-server-closeoption forwardfor except 127.0.0.0/8option redispatchretries 3timeout http-request 10stimeout queue 1mtimeout connect 10stimeout client1mtimeout server1mtimeout http-keep-alive 10stimeout check 10smaxconn 3000listen web-80bind 192.168.205.111:80server web1 192.168.205.127:80 check inter 3s fall 3 rise 5server web1 192.168.205.137:80 check inter 3s fall 3 rise 5
启动服务
[root@haproxy1 ~]#systemctl enable haproxy[root@haproxy1 ~]#systemctl start haproxy [root@haproxy1 ~]#ss -ntlState Recv-Q Send-Q Local Address:Port Peer Address:Port LISTEN0128192.168.205.111:80 *:*
将haprox配置文件复制到haproxy2上并启动服务
[root@haproxy1 ~]#scp /etc/haproxy/haproxy.cfg 192.168.205.117:/etc/haproxy/[root@haproxy2 ~]#systemctl enable haproxy [root@haproxy2 ~]#systemctl start haproxy
我们发现没有监听192.168.205.111：80端口，因为默认不会监听不存在IP的端口
[root@haproxy2 ~]#ss -ntlState Recv-Q Send-Q Local Address:Port Peer Address:Port LISTEN0128 *:22 *:* LISTEN0100 127.0.0.1:25 *:* LISTEN0128 :::22 :::* LISTEN0100 ::1:25 :::*
修改内核参数可以实现监听，重启haproxy, 可以看到haproxy2也实现了监听, 同样我也需要在haproxy1上加上这个选项否则主失效的情况下，再切回来情况下，haproxy会出错，无法启动
[root@haproxy2 ~]#sysctl -a | grep bindnet.ipv4.ip_nonlocal_bind = 0[root@haproxy2 ~]#vi /etc/sysctl.conf net.ipv4.ip_nonlocal_bind = 1[root@haproxy2 ~]#sysctl -pnet.ipv4.ip_nonlocal_bind = 1[root@haproxy2 ~]#systemctl restart haproxy[root@haproxy2 ~]#ss -ntlState Recv-Q Send-Q Local Address:Port Peer Address:Port LISTEN0128192.168.205.111:80 *:*
通过192.168.205.111访问web服务器，发现没有问题
如果停掉haproxy1的keepalive, 再测试
[root@haproxy1 ~]#systemctl stop keepalived
恢复keepalived，尝试停掉haproxy，再测试，发现vip已经切到haproxy2上
[root@haproxy1 ~]#systemctl stop haproxy[root@haproxy2 ~]#ip a2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000link/ether 00:0c:29:05:be:a7 brd ff:ff:ff:ff:ff:ffinet 192.168.205.117/24 brd 192.168.205.255 scope global noprefixroute eth0valid_lft forever preferred_lft foreverinet 192.168.205.111/24 scope global secondary eth0:0valid_lft forever preferred_lft foreverinet6 fe80::20c:29ff:fe05:bea7/64 scope link noprefixroute valid_lft forever preferred_lft forever
实现firewall
为了简化我们使用一台linux的iptables做为DNAT,确保关闭firewald[root@centos7 ~]#iptables -t nat -A PREROUTING -s 0/0 -d 202.106.0.17 -p tcp --dport 80 -j DNAT --to-destination 192.168.205.111:80
开启ip转发功能
[root@centos7 network-scripts]#cat /proc/sys/net/ipv4/ip_forward0[root@centos7 network-scripts]#sysctl -w net.ipv4.ip_forward=1net.ipv4.ip_forward = 1[root@centos7 ~]#vi /etc/sysctl.conf net.ipv4.ip_forward = 1
安装iptables service实现自动保存
[root@centos7 ~]#yum install iptables-services[root@centos7 ~]#iptables-save >/etc/sysconfig/iptables[root@centos7 ~]#systemctl enable iptables.service
注意，Haproxy的默认网关要指向firewall, 两个webserver也默认网关也要指定firewall, 原因在于webserver回包时直接回到网关，实际是不是这样，有待考证，但我加了网站才能够正常访问
[root@haproxy1 ~]#ip rdefault via 192.168.205.17 dev eth0 proto static metric 102 [root@haproxy2 ~]#ip rdefault via 192.168.205.17 dev eth0 proto static metric 102 [root@web1 ~]#ip r a default via 192.168.205.17 dev eth0[root@web1 ~]#ip rdefault via 192.168.205.17 dev eth0 [root@web2 ~]#ip r a default via 192.168.205.17 dev eth0[root@web2 ~]#ip rdefault via 192.168.205.17 dev eth0
实现powerdns
安装包：基于EPEL源yum install -y pdns pdns-backend-mysql mariadb-server
创建数据库
CREATE DATABASE powerdns;GRANT ALL ON powerdns.* TO 'powerdns'@'127.0.0.1' IDENTIFIED BY 'centos'; USE powerdns;
创建powerdns数据库中的表 (参考/md/authoritative/backend-generic-mysql/)
CREATE TABLE domains (idINT AUTO_INCREMENT,name VARCHAR(255) NOT NULL,masterVARCHAR(128) DEFAULT NULL,last_check INT DEFAULT NULL,type VARCHAR(6) NOT NULL,notified_serial INT DEFAULT NULL,accountVARCHAR(40) DEFAULT NULL,PRIMARY KEY (id)) Engine=InnoDB;CREATE UNIQUE INDEX name_index ON domains(name);CREATE TABLE records (idBIGINT AUTO_INCREMENT,domain_id INT DEFAULT NULL,name VARCHAR(255) DEFAULT NULL,type VARCHAR(10) DEFAULT NULL,contentVARCHAR(64000) DEFAULT NULL,ttl INT DEFAULT NULL,prio INT DEFAULT NULL,change_date INT DEFAULT NULL,disabled TINYINT(1) DEFAULT 0,ordername VARCHAR(255) BINARY DEFAULT NULL,auth TINYINT(1) DEFAULT 1,PRIMARY KEY (id)) Engine=InnoDB;CREATE INDEX nametype_index ON records(name,type);CREATE INDEX domain_id ON records(domain_id);CREATE INDEX recordorder ON records (domain_id, ordername);CREATE TABLE supermasters (ipVARCHAR(64) NOT NULL,nameserver VARCHAR(255) NOT NULL,accountVARCHAR(40) NOT NULL,PRIMARY KEY (ip, nameserver)) Engine=InnoDB;CREATE TABLE comments (idINT AUTO_INCREMENT,domain_id INT NOT NULL,name VARCHAR(255) NOT NULL,type VARCHAR(10) NOT NULL,modified_at INT NOT NULL,accountVARCHAR(40) NOT NULL,commentVARCHAR(64000) NOT NULL,PRIMARY KEY (id)) Engine=InnoDB;CREATE INDEX comments_domain_id_idx ON comments (domain_id);CREATE INDEX comments_name_type_idx ON comments (name, type);CREATE INDEX comments_order_idx ON comments (domain_id, modified_at);CREATE TABLE domainmetadata (idINT AUTO_INCREMENT,domain_id INT NOT NULL,kind VARCHAR(32),contentTEXT,PRIMARY KEY (id)) Engine=InnoDB;CREATE INDEX domainmetadata_idx ON domainmetadata (domain_id, kind);CREATE TABLE cryptokeys (idINT AUTO_INCREMENT,domain_id INT NOT NULL,flags INT NOT NULL,activeBOOL,contentTEXT,PRIMARY KEY(id)) Engine=InnoDB;CREATE INDEX domainidindex ON cryptokeys(domain_id);CREATE TABLE tsigkeys (idINT AUTO_INCREMENT,name VARCHAR(255),algorithm VARCHAR(50),secretVARCHAR(255),PRIMARY KEY (id)) Engine=InnoDB;CREATE UNIQUE INDEX namealgoindex ON tsigkeys(name, algorithm);
配置PowerDNS使用mariadb作为后台数据存储
vim /etc/pdns/pdns.conf，查找到包含launch= 的行，修改并添加下面的内容launch=gmysqlgmysql-host=localhostgmysql-port=3306gmysql-dbname=powerdnsgmysql-user=powerdnsgmysql-password=centos
启动服务
systemctl start pdnssystemctl enable pdns
安装httpd和php相关包
yum -y install httpd php php-devel php-gd php-mcrypt php-imap php-ldap php-mysql php-odbc php-pear php-xml php-xmlrpc php-mbstring php-mcrypt php-mhash gettext wgetsystemctl start httpdsystemctl enable httpd
下载poweradmin程序，并解压缩到相应目录
cd /var/www/htmlwget /project/poweradmin/poweradmin-2.1.7.tgztar xvf poweradmin-2.1.7.tgzmv poweradmin-2.1.7 /var/www/html/poweradmin
访问下面地址，启动PowerAdmin的网页安装向导，选择英文然后 go to step2：
http://192.168.205.147/poweradmin/install/
提供先前配置的数据库详情，同时为Poweradmin设置管理员密码
Username: 为上面91步创建的用户名,此处应该为powerdnspassword:为上面91步创建的密码应该为centosdatabase type :为myqlhostname： 为mysql主服务器的IPDB port: 为默认的3306database: 为上面9步创建的数据库名称powerdnspoweradmin adminstrator pasword: 此处为powerdns会自动创建一个web管理员名为admin的用户，此为admin的密码
为Poweradmin创建一个受限用户，powerDNS会使用这个帐户来更新powerdns数据库，上面的只是用来连接数据库，此处为真正更新时使用的帐户
Username：更新的用户名Password：上述用户的密码Hostmaster：当创建SOA记录指定默认主机管理员Primary nameserver：主域名服务器，此处应该为192.168.205.17Secondary namesever: 辅域名服务器, 没有辅助服务器，可以不添
跟据上面的输入，他会自动产生sql语句，按照下面页面说明，在数据库中192.168.205.147创建用户并授权
MariaDB [powerdns]> GRANT SELECT, INSERT, UPDATE, DELETE ON powerdns.* TO 'poweradmin'@'127.0.0.1' IDENTIFIED BY 'centos1';
按下面页面说明，创建config.in.php文件内容
vim /var/www/html/poweradmin/inc/config.inc.php
删除install目录
rm -rf /var/www/html/poweradmin/install/
登录http://202.106.0.17/poweradmin/
username：adminpassword：admin 参看第96步
添加主机记录在powerdns上
在客户端的windows中测试dns解析
如果觉得《架构测试：HAproxy实现负载均衡 前端为nginx+php 数据库实现读写分离和高可用...》对你有帮助，请点赞、收藏，并留下你的观点哦！
