mysql/mha+的lnmp+负载均衡高可用

1.说明

mysql高可用+lnp集群+负载均衡高可用

2.1环境准备：

两台虚拟机 master slave
master：3306
slave：3307 3308

2.2 删除历史环境：

pkill mysqld
rm -rf /data/330* 
mv /etc/my.cnf /etc/my.cnf.bak

2.3master部署

[ ! -d /software/ ] && mkdir /software
rpm -qa | grep mariadb
yum remove mariadb-libs -y
useradd mysql -s /sbin/nologin
mkdir -p /usr/local/mysql
mkdir -p /data/mysqldata
mkdir -p /var/log/mysql
echo " PATH=$PATH:/usr/local/mysql/bin
export  PATH" >> /etc/profile
source /etc/profile
cat > /etc/my.cnf <<EOF
[mysqld]
user=mysql #指定用户
basedir=/usr/local/mysql
datadir=/data/mysqldata
server_id=1
log-error=/var/log/mysql/error.log 
pid-file=/data/mysqldata/mysql.pid 
port=3306
log_bin=/data/mysqldata/mysql-bin
binlog_format=row
socket=/tmp/mysql.sock
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
EOF
cd /software
wget http://192.168.210.5/package/gz/mysql-5.7.30-linux-glibc2.12-x86_64.tar.gz
tar -xf mysql-5.7.30-linux-glibc2.12-x86_64.tar.gz
mv /software/mysql-5.7.30-linux-glibc2.12-x86_64/* /usr/local/mysql
cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
chkconfig --add mysqld
chown mysql:mysql -R /var/log/mysql
mysqld --initialize-insecure  --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysqldata
/etc/init.d/mysqld restart
mysql -e "alter user root@'localhost' identified by '123456';"

2.4slave部署

[mysqld]
basedir=/usr/local/mysql
datadir=/data/3307/mysqldata
socket=/data/3307/mysql.sock
port=3307
log-error=/data/3307/mysql.log
log_bin=/data/3307/mysql-bin
binlog_format=row
skip-name-resolve
server-id=37
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
EOF
cat >/data/3308/my.cnf<<EOF
[mysqld]
basedir=/usr/local/mysql
datadir=/data/3308/mysqldata
port=3308
socket=/data/3308/mysql.sock
log-error=/data/3308/mysql.log
log_bin=/data/3308/mysql-bin
binlog_format=row
skip-name-resolve
server-id=38
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
EOF

cat >/etc/systemd/system/mysqld3307.service<<EOF
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
ExecStart=/usr/local/mysql/bin/mysqld --defaults-file=/data/3307/my.cnf
LimitNOFILE = 5000
EOF

cat >/etc/systemd/system/mysqld3308.service<<EOF
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
ExecStart=/usr/local/mysql/bin/mysqld --defaults-file=/data/3308/my.cnf
LimitNOFILE = 5000
EOF
chown -R mysql.mysql /data/*
systemctl start mysqld3307
systemctl start mysqld3308

2.5配置主从环境：

	master：
mysql -e "grant replication slave on *.* to repl@'%' identified by '123456';"
slave：
mysql  -S /data/3307/mysql.sock -e "CHANGE MASTER TO MASTER_HOST='192.168.204.22', MASTER_PORT=3306, MASTER_AUTO_POSITION=1, MASTER_USER='repl', MASTER_PASSWORD='123456';"
mysql  -S /data/3308/mysql.sock -e "CHANGE MASTER TO MASTER_HOST='192.168.204.22', MASTER_PORT=3306, MASTER_AUTO_POSITION=1, MASTER_USER='repl', MASTER_PASSWORD='123456';"

2.5.1检查组从部署情况

[root@slave ~]# mysql -S /data/3308/mysql.sock -e "show slave status\G"|grep "Yes"
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
[root@slave ~]# mysql -S /data/3307/mysql.sock -e "show slave status\G"|grep "Yes"
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
[root@slave ~]# 

3. MHA环境搭建

3.1 规划:

master: 22          node 
slave: 23           node
mha:24              manager

3.2准备环境：

master/slave/mha：
ln -s /usr/local/mysql/bin/mysqlbinlog /usr/bin/mysqlbinlog
ln -s /usr/local/mysql/bin/mysql /usr/bin/mysql

3.3生成密钥对

/master/slave/mha
[root@master ~]#ssh-keygen -t rsa
[root@master ~]# ls ~/.ssh/
id_rsa  id_rsa.pub
#弹窗回车默认就行

3.4.将公钥发给其他服务器保持互通

/master/slave/mha
ssh-copy-id -i ~/.ssh/id_rsa.pub root@192.168.204.22
ssh-copy-id -i ~/.ssh/id_rsa.pub