目录

搭建流程

1、安装mysql5726

实现 功能	10.0.0.65	10.0.0.66	10.0.0.67
name	65	66	67
host	10.0.0.65 65 10.0.0.66 66 10.0.0.67 67	10.0.0.65 65 10.0.0.66 66 10.0.0.67 67	10.0.0.65 65 10.0.0.66 66 10.0.0.67 67
gtid复制	主库	从库	从库
高可用	vip	-	mha
邮箱报警	读写分离	-

65\66\67

curl -o /etc/yum.repos.d/CentOS-Base.repo http://mirrors.aliyun.com/repo/Centos-7.repocurl -o /etc/yum.repos.d/epel.repo http://mirrors.aliyun.com/repo/epel-7.repoyum install -y libaio-develmkdir -p /server/toolscd /server/tools/mkdir /application# wget http://mirrors.163.com/mysql/Downloads/MySQL-5.7/mysql-5.7.26-linux-glibc2.12-x86_64.tar.gztar xf mysql-5.7.26-linux-glibc2.12-x86_64.tar.gz mv mysql-5.7.26-linux-glibc2.12-x86_64  /application/mysqlyum remove mariadb-libs-5.5.60-1.el7_5.x86_64 -yrpm -qa |grep mariadbuseradd -s /sbin/nologin mysql#设置环境变量echo "export PATH=/application/mysql/bin:$PATH" >>/etc/profilesource /etc/profilemysql -V#创建数据路径并授权#1. 添加一块新磁盘模拟数据盘#2. 格式化并挂载磁盘# mkfs.xfs /dev/sdb# mkdir /data# blkid# vim /etc/fstab # UUID="b21ec3e0-e251-4ded-bc12-2d940f938dd5" /data xfs defaults 0 0# mount -a# df -hchown -R mysql.mysql /application/*#初始化数据（创建系统数据）# 5.6 版本 初始化命令  /application/mysql/scripts/mysql_install_db # 5.7 版本mkdir /data/mysql/data -p chown -R mysql.mysql /datamysqld --initialize --user=mysql --basedir=/application/mysql --datadir=/data/mysql/data  #自动创建密码mysqld --initialize-insecure --user=mysql --basedir=/application/mysql --datadir=/data/mysql/data   #无密码cat >/etc/systemd/system/mysqld.service <

65

cat > /etc/my.cnf <
    
     EOFsystemctl restart mysqldsystemctl status mysqld
    

66

cat > /etc/my.cnf <
    
     EOFsystemctl restart mysqldsystemctl status mysqld
    

67

cat > /etc/my.cnf <
    
     EOFsystemctl restart mysqldsystemctl status mysqld
    

gtid主从复制

65

mysql -e "grant replication slave on *.* to repl@'10.0.0.%' identified by '123';"

66\67

mysql -e "change master to master_host='10.0.0.65',master_user='repl',master_password='123' ,MASTER_AUTO_POSITION=1;start slave; "mysql -e "show slave status \G"|grep Yes

高可用

65\66\67

ln -s /application/mysql/bin/mysqlbinlog    /usr/bin/mysqlbinlogln -s /application/mysql/bin/mysql          /usr/bin/mysql

65

rm -rf /root/.ssh /bin/ssh-keygen -t rsa -f /root/.ssh/id_rsa  -P ""sshpass -p123456 ssh-copy-id -i ~/.ssh/id_rsa.pub 10.0.0.66 -o StrictHostKeyChecking=nosshpass -p123456 ssh-copy-id -i ~/.ssh/id_rsa.pub 10.0.0.67 -o StrictHostKeyChecking=nomysql -e "grant all privileges on *.* to mha@'10.0.0.%' identified by 'mha';"

65\66\67

ssh 10.0.0.65 datessh 10.0.0.66 datessh 10.0.0.67 dateyum install perl-DBD-MySQL -yrpm -ivh mha4mysql-node-0.56-0.el6.noarch.rpm

67

yum install -y perl-Config-Tiny epel-release perl-Log-Dispatch perl-Parallel-ForkManager perl-Time-HiResrpm -ivh mha4mysql-manager-0.56-0.el6.noarch.rpm#创建配置文件目录 mkdir -p /etc/mha#创建日志目录 mkdir -p /var/log/mha/app1#编辑mha配置文件cat > /etc/mha/app1.cnf<
    
      /var/log/mha/app1/manager.log 2>&1 &#查看MHA状态masterha_check_status --conf=/etc/mha/app1.cnf
    

mha的VIP功能

67

cd /usr/local/bin/rz master_ip_failoverdos2unix /usr/local/bin/master_ip_failover chmod +x /usr/local/bin/master_ip_failover vim  /usr/local/bin/master_ip_failovermy $vip = '10.0.0.55/24';my $key = '1';my $ssh_start_vip = "/sbin/ifconfig eth0:$key $vip";my $ssh_stop_vip = "/sbin/ifconfig eth0:$key down";# vim /etc/mha/app1.cnf添加：master_ip_failover_script=/usr/local/bin/master_ip_failover

65

ifconfig eth0:1 10.0.0.55/24

67

masterha_stop --conf=/etc/mha/app1.cnfnohup masterha_manager --conf=/etc/mha/app1.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null > /var/log/mha/app1/manager.log 2>&1 &

邮件报警

67

cd /usr/local/bin/rz send  sendEmail  testplvim testpl  #修改报警邮箱vim /etc/mha/app1.cnf# 添加一行report_script=/usr/local/bin/send#重启mhamasterha_stop --conf=/etc/mha/app1.cnfnohup masterha_manager --conf=/etc/mha/app1.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null > /var/log/mha/app1/manager.log 2>&1 &

2.读写分离

主库

yum install -y Atlas*cd /usr/local/mysql-proxy/confmv test.cnf test.cnf.bak vi test.cnf[mysql-proxy]admin-username = useradmin-password = pwdproxy-backend-addresses = 10.0.0.55:3306proxy-read-only-backend-addresses = 10.0.0.66:3306,10.0.0.67:3306pwds = repl:3yb5jEku5h4=,mha:O2jBXONX098=daemon = truekeepalive = trueevent-threads = 8log-level = messagelog-path = /usr/local/mysql-proxy/logsql-log=ONproxy-address = 0.0.0.0:33060admin-address = 0.0.0.0:2345charset=utf8# 启动atlas    /usr/local/mysql-proxy/bin/mysql-proxyd test start    ps -ef |grep proxy# 3. Atlas功能测试# 测试读操作：mysql -umha -pmha  -h 10.0.0.55 -P 3306066 [(none)]>select @@server_id;+-------------+| @@server_id |+-------------+|          67 |+-------------+1 row in set (0.00 sec)66 [(none)]>select @@server_id;+-------------+| @@server_id |+-------------+|          65 |+-------------+1 row in set (0.00 sec)# 测试写操作：66 [(none)]>begin;select @@server_id;commit;Query OK, 0 rows affected (0.00 sec)+-------------+| @@server_id |+-------------+|          66 |+-------------+1 row in set (0.00 sec)Query OK, 0 rows affected (0.00 sec)66 [(none)]>begin;select @@server_id;commit;Query OK, 0 rows affected (0.00 sec)+-------------+| @@server_id |+-------------+|          66 |+-------------+1 row in set (0.00 sec)Query OK, 0 rows affected (0.00 sec)

测试ip漂移

查看vip[root@65 /server/tools]# ip a |grep eth02: eth0: 
    
      mtu 1500 qdisc pfifo_fast state UP group default qlen 1000    inet 10.0.0.65/24 brd 10.0.0.255 scope global noprefixroute eth0    inet 10.0.0.55/24 brd 10.0.0.255 scope global secondary eth0:1#登录66mysql -e "show slave status\G"*************************** 1. row ***************************               Slave_IO_State: Waiting for master to send event                  Master_Host: 10.0.0.65                    //主库是51                  Master_User: rep                  Master_Port: 3306                Connect_Retry: 60              Master_Log_File: mysql-bin.000007          Read_Master_Log_Pos: 191               Relay_Log_File: mysql-db02-relay-bin.000002                Relay_Log_Pos: 361        Relay_Master_Log_File: mysql-bin.000007             Slave_IO_Running: Yes            Slave_SQL_Running: Yes            #停掉主库 65 systemctl stop mysqld                 //停掉主库Mysql测试# 切换到从库67上查看mysql -e "show slave status\G"*************************** 1. row ***************************               Slave_IO_State: Waiting for master to send event                  Master_Host: 10.0.0.66                   //主库切换到52                  Master_User: rep                  Master_Port: 3306                Connect_Retry: 60              Master_Log_File: mysql-bin.000006          Read_Master_Log_Pos: 191               Relay_Log_File: mysql-db03-relay-bin.000002                Relay_Log_Pos: 361        Relay_Master_Log_File: mysql-bin.000006             Slave_IO_Running: Yes            Slave_SQL_Running: Yes            #在65上查看vip信息 ip a |grep eth0     //vip没有了2: eth0: 
     
       mtu 1500 qdisc pfifo_fast state UP group default qlen 1000    inet 10.0.0.65/24 brd 10.0.0.255 scope global noprefixroute eth0#在66上查看vip信息[root@mysql-db02 ~]# ip a |grep eth0    //db02出现vip552: eth0: 
      
        mtu 1500 qdisc pfifo_fast state UP qlen 1000    inet 10.0.0.52/24 brd 10.0.0.255 scope global eth0    inet 10.0.0.55/24 brd 10.0.0.255 scope global secondary eth0:0# VIP漂移测试成功nohup masterha_manager --conf=/etc/mha/app1.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null > /var/log/mha/app1/manager.log 2>&1 & //后台运行mhamasterha_check_repl --conf=/etc/mha/app1.cnf                         //测试mha复制MySQL Replication Health is OK[root@db03 mha]# masterha_check_status --conf=/etc/mha/app1.cnf       //检测mha状态，db03查看主库是否切换66app1 (pid:9849) is running(0:PING_OK), master:10.0.0.66# mha工作正常