##############mysql主从模式,高可用
db01: 10.0.0.50
db02: 10.0.0.51
db03: 10.0.0.52
[root@db01 ~]# hostnamectl set-hostname db01 [root@db02 ~]# hostnamectl set-hostname db02 [root@db03 ~]# hostnamectl set-hostname db03
#修改主机名
[root@db01 ~]# vi /etc/sysconfig/network-scripts/ifcfg-eth0 TYPE="Ethernet" PROXY_METHOD="none" BROWSER_ONLY="no" BOOTPROTO="none" DEFROUTE="yes" NAME="eth0" DEVICE="eth0" ONBOOT="yes" IPADDR="10.0.0.51" PREFIX="24" GATEWAY="10.0.0.2" DNS1="223.5.5.5" #修改网卡配置
###修改网关
###########mysql的tar包安装
59 tar xf mysql-5.6.40-linux-glibc2.12-x86_64.tar.gz
60 mv mysql-5.6.40-linux-glibc2.12-x86_64 /usr/local/mysql-5.6.40
61 cd /usr/local/mysql-5.6.40/
62 useradd mysql -s /sbin/nologin -M
63 cd support-files/
64 cp my-default.cnf /etc/my.cnf
65 cp mysql.server /etc/init.d/mysqld
66 cd /usr/local/mysql-5.6.40/scripts/
67 ./mysql_install_db --user=mysql --basedir=/usr/local/mysql-5.6.40 --datadir=/usr/local/mysql-5.6.40/data
68 ll /usr/local/mysql-5.6.40/data/
69 ln -s /usr/local/mysql-5.6.40 /usr/local/mysql
70 vim /etc/profile
71 source /etc/profile
72 /etc/init.d/mysqld start
73 ps axu |grep mysqld
74 history
三.部署MHA
安装: db01(主) db02(从) db03(从)
db01,02,03:yum install perl-DBD-MySQL -y #01,02,03都安装下 db03: #(03单独安装主要用控制使用) wget -O /etc/yum.repos.d/epel.repo https://mirrors.aliyun.com/repo/epel-7.repo yum install -y perl-Config-Tiny epel-release perl-Log-Dispatch perl-Parallel-ForkManager perl-Time-HiRes
1.做主从复制的先决条件:
主库:
1)开启binlog
2)开启server_id
3)创建主从复制用户
从库:
1)必须开启binlog
2)从库开启server_id(与主库不相同)
3)从库必须要创建主从复制用户
4)开IO,SQl线程 start slave;
db01: [mysqld] log_bin=mysql-bin binlog_format=row server_id=1 skip-name-resolve skip-name-resolv db02: [mysqld] log_bin=mysql-bin binlog_format=row server_id=2 skip-name-resolve skip-name-resolv db03: [mysqld] log_bin=mysql-bin binlog_format=row server_id=3 skip-name-resolve skip-name-resolv
2.MHA工作原理
当Master出现故障时，它可以自动将最新数据的Slave提升为新的Master,然后将所有其他的Slave重新指向新的Master。
db01
1.开启binlog
2.主从复制用户
3.server_id 不同
db02
1.开启binlog
2.主从复制用户
3.server_id 不同
db03
1.开启binlog
2.主从复制用户
3.server_id 不同
=============================================================
3.MHA的工具
Manager工具包主要包括以下几个工具：
masterha_check_ssh #检查MHA的ssh-key
masterha_check_repl #检查主从复制情况
masterha_manger #启动MHA
masterha_check_status #检测MHA的运行状态
masterha_master_monitor #检测master是否宕机
masterha_master_switch #手动故障转移
masterha_conf_host #手动添加server信息
masterha_secondary_check #建立TCP连接从远程服务器
masterha_stop #停止MHA
Node工具包主要包括以下几个工具：
save_binary_logs #保存宕机的master的binlog
apply_diff_relay_logs #识别relay log的差异
filter_mysqlbinlog #防止回滚事件
purge_relay_logs #清除中继日志
MHA 是 C/S结构的服务
manager
node
4.主从配置
查看主的信息:
mysql> show master status; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000004 | 120 | | | | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec)
给主的添加用户
grant replication slave on *.* to rep@'%' identified by '123';
从的mysql 添加如下操作:
change master to master_host='10.0.0.50', master_user='rep', master_password='123', master_log_file='mysql-bin.000004', master_log_pos=120; #############以上是同步主的信息 mysql> start slave; ########然后开启slave的服务
查看状态:
5.只读 和 禁用删除relaylog功能
#禁用自动删除relay log 功能(3个库都执行)
mysql> set global relay_log_purge = 0;
#设置只读(只能在从库执行)
mysql> set global read_only=1;
#编辑配置文件
[root@mysql-db02 ~]# vim /etc/my.cnf
#在mysqld标签下添加
[mysqld]
#禁用自动删除relay log 永久生效
relay_log_purge = 0
6.安装node包
[root@db01 ~]# rpm -ivh mha4mysql-node-0.56-0.el6.noarch.rpm
[root@db02 ~]# rpm -ivh mha4mysql-node-0.56-0.el6.noarch.rpm
[root@db03 ~]# rpm -ivh mha4mysql-node-0.56-0.el6.noarch.rpm
7.安装manager包(避免装在主库上) 别在主库上安装
[root@db03 ~]# rpm -ivh mha4mysql-manager-0.56-0.el6.noarch.rpm
8.创建命令软连接
[root@db01 ~]# ln -s /usr/local/mysql/bin/mysqlbinlog /usr/bin/
[root@db01 ~]# ln -s /usr/local/mysql/bin/mysql /usr/bin/
[root@db02 ~]# ln -s /usr/local/mysql/bin/mysqlbinlog /usr/bin/
[root@db02 ~]# ln -s /usr/local/mysql/bin/mysql /usr/bin/
[root@db03 ~]# ln -s /usr/local/mysql/bin/mysqlbinlog /usr/bin/
[root@db03 ~]# ln -s /usr/local/mysql/bin/mysql /usr/bin/
9.创建mha工作目录
[root@db03 ~]# mkdir /etc/mha
10.编辑mha配置文件
[root@db03 ~]# vim /etc/mha/app1.cnf
[server default]
manager_log=/etc/mha/manager.log
manager_workdir=/etc/mha/app1
master_binlog_dir=/usr/local/mysql/data
user=mha
password=mha
ping_interval=2
repl_password=123
repl_user=rep
ssh_user=root
[server1]
hostname=10.0.0.50
port=3306
[server2]
hostname=10.0.0.51
port=3306
[server3]
hostname=10.0.0.52
port=3306
11.在mysql中创建一个mha管理用户(三台),只需要在主库上创建
mysql> grant all on *.* to mha@'%' identified by 'mha';
#然后在从的数据库查看状态
mysql> select user,host from mysql.user; +------+-----------+ | user | host | +------+-----------+ | mha | % | | rep | % | | root | 127.0.0.1 | | root | ::1 | | | localhost | | root | localhost | | | node2 | | root | node2 | +------+-----------+ 8 rows in set (0.00 sec)
12.创建密钥对,做免密登录
[root@db01 ~]# ssh-keygen -t dsa -P '' -f ~/.ssh/id_dsa >/dev/null 2>&1
[root@db01 ~]# ssh-copy-id -i /root/.ssh/id_dsa.pub root@10.0.0.50
[root@db01 ~]# ssh-copy-id -i /root/.ssh/id_dsa.pub root@10.0.0.51
[root@db01 ~]# ssh-copy-id -i /root/.ssh/id_dsa.pub root@10.0.0.52
[root@db02 ~]# ssh-keygen -t dsa -P '' -f ~/.ssh/id_dsa >/dev/null 2>&1
[root@db02 ~]# ssh-copy-id -i /root/.ssh/id_dsa.pub root@10.0.0.50
[root@db02 ~]# ssh-copy-id -i /root/.ssh/id_dsa.pub root@10.0.0.51
[root@db02 ~]# ssh-copy-id -i /root/.ssh/id_dsa.pub root@10.0.0.52
[root@db03 ~]# ssh-keygen -t dsa -P '' -f ~/.ssh/id_dsa >/dev/null 2>&1
[root@db03 ~]# ssh-copy-id -i /root/.ssh/id_dsa.pub root@10.0.0.50
[root@db03 ~]# ssh-copy-id -i /root/.ssh/id_dsa.pub root@10.0.0.52
[root@db03 ~]# ssh-copy-id -i /root/.ssh/id_dsa.pub root@10.0.0.51
13.测试免密登录
[root@db01 ~]# ssh root@10.0.0.51
[root@db01 ~]# ssh root@10.0.0.52
[root@db01 ~]# ssh root@10.0.0.50
[root@db02 ~]# ssh root@10.0.0.51
[root@db02 ~]# ssh root@10.0.0.52
[root@db02 ~]# ssh root@10.0.0.50
[root@db03 ~]# ssh root@10.0.0.51
[root@db03 ~]# ssh root@10.0.0.52
[root@db03 ~]# ssh root@10.0.0.50
14.使用mha工具检测ssh (在第三台从的机器上)
[root@db03 ~]# masterha_check_ssh --conf=/etc/mha/app1.cnf
#切记问题所有首先 排错解析问题, 然后私钥公钥问题,没创建好,建议删除全部在创建一次
[root@node2 mha]# masterha_check_ssh --conf=/etc/mha/app1.cnf Fri May 10 20:34:57 2019 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping. Fri May 10 20:34:57 2019 - [info] Reading application default configuration from /etc/mha/app1.cnf.. Fri May 10 20:34:57 2019 - [info] Reading server configuration from /etc/mha/app1.cnf.. Fri May 10 20:34:57 2019 - [info] Starting SSH connection tests.. Fri May 10 20:34:58 2019 - [debug] Fri May 10 20:34:57 2019 - [debug] Connecting via SSH from root@10.0.0.50(10.0.0.50:22) to root@10.0.0.51(10.0.0.51:22).. Fri May 10 20:34:58 2019 - [debug] ok. Fri May 10 20:34:58 2019 - [debug] Connecting via SSH from root@10.0.0.50(10.0.0.50:22) to root@10.0.0.52(10.0.0.52:22).. Fri May 10 20:34:58 2019 - [debug] ok. Fri May 10 20:34:59 2019 - [debug] Fri May 10 20:34:58 2019 - [debug] Connecting via SSH from root@10.0.0.51(10.0.0.51:22) to root@10.0.0.50(10.0.0.50:22).. Fri May 10 20:34:58 2019 - [debug] ok. Fri May 10 20:34:58 2019 - [debug] Connecting via SSH from root@10.0.0.51(10.0.0.51:22) to root@10.0.0.52(10.0.0.52:22).. Fri May 10 20:34:59 2019 - [debug] ok. Fri May 10 20:35:00 2019 - [debug] Fri May 10 20:34:58 2019 - [debug] Connecting via SSH from root@10.0.0.52(10.0.0.52:22) to root@10.0.0.50(10.0.0.50:22).. Fri May 10 20:34:59 2019 - [debug] ok. Fri May 10 20:34:59 2019 - [debug] Connecting via SSH from root@10.0.0.52(10.0.0.52:22) to root@10.0.0.51(10.0.0.51:22).. Fri May 10 20:34:59 2019 - [debug] ok. Fri May 10 20:35:00 2019 - [info] All SSH connection tests passed successfully.
15.使用mha工具检测主从复制
[root@db03 ~]# masterha_check_repl --conf=/etc/mha/app1.cnf
#注意如果报错, 需要在主节点 mysql内添加: 如下命令
grant replication slave on *.* to rep@'%' identified by '123';
#如果碰到 db01设置为db02为主模式 db02设置为db01为主的模式 这样就不行了,需要关闭一个节点的slave模式
mysql>stop slave;
mysql>reset slave;
Fri May 10 20:40:44 2019 - [info] Checking replication health on 10.0.0.51.. Fri May 10 20:40:44 2019 - [info] ok. Fri May 10 20:40:44 2019 - [info] Checking replication health on 10.0.0.52.. Fri May 10 20:40:44 2019 - [info] ok. Fri May 10 20:40:44 2019 - [info] Checking master_ip_failover_script status: Fri May 10 20:40:44 2019 - [info] /etc/mha/app1/master_ip_failover --command=status --ssh_user=root --orig_master_host=10.0.0.50 --orig_master_ip=10.0.0.50 --orig_master_port=3306 IN SCRIPT TEST====/sbin/ifconfig eth0:0 down==/sbin/ifconfig eth0:0 10.0.0.55/24=== Checking the Status of the script.. OK Fri May 10 20:40:44 2019 - [info] OK. Fri May 10 20:40:44 2019 - [warning] shutdown_script is not defined. Fri May 10 20:40:44 2019 - [info] Got exit code 0 (Not master dead). MySQL Replication Health is OK.
16.启动mha
[root@db03 ~]# nohup masterha_manager --conf=/etc/mha/app1.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null > /etc/mha/manager.log 2>&1 &
17.检查MHA启动状态
[root@db03 ~]# masterha_check_status --conf=/etc/mha/app1.cnf
app1 (pid:25635) is running(0:PING_OK), master:10.0.0.51
###############################以上这些说明 主从模式都创建完成了 如果发生检测错误,需要查看另外的保存信息文档
18.主库绑定vip
/sbin/ifconfig eth0:0 10.0.0.55/24 #注意这个是在主的服务器安装
19.给脚本执行权限
[root@db03 app1]# chmod +x master_ip_failover
20.添加配置文件
master_ip_failover_script=/etc/mha/app1/master_ip_failover #注意吧脚本存放在/etc/mha/app1目录下别放错了
1.脚本语法问题 因为上传上去的脚本是在windows写的,所以需要转换下
2.脚本的格式问题 安装格式转换命令:
[root@db03 app1]# yum install -y dos2unix
[root@db03 app1]# dos2unix master_ip_failover
dos2unix: converting file master_ip_failover to Unix format ...
3.脚本的权限问题 chmod +x master_ip_failover
4.关闭: 关闭MHA的方法
[root@node2 mha]# masterha_stop --conf=/etc/mha/app1.cnf
5.启动:
nohup masterha_manager --conf=/etc/mha/app1.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null > /etc/mha/manager.log 2>&1 &
[root@db03 app1]# masterha_check_status --conf=/etc/mha/app1.cnf
app1 (pid:26448) is running(0:PING_OK), master:10.0.0.52
#注意,每次主机停掉, 从机顶上去之后, 03那个管理机的MHA的进程就会自动关闭!!!!!
