MySQL高可用-MHA

MySQL

MySQL配置文件

vim /etc/my.cnf

[mysqld]
basedir=/usr/local/mysql
datadir=/data/mysql/data
user=mysql
server_id=1
log-error=/data/mysql/error.log
log_bin=/data/mysql/binlog/mysql-bin
skip_name_resolve
port=3306
gtid_mode=ON
enforce-gtid-consistency=true
log_slave_updates
default-authentication-plugin=mysql_native_password

MySQL管理文件

vim /etc/systemd/system/mysqld.service

[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=/etc/my.cnf
LimitNOFILE = 5000

MySQL用户相关

create user 'rep'@'192.168.0.%' identified with mysql_native_password by 'rep';
grant replication slave on *.* to 'rep'@'192.168.0.%';

create user 'mha'@'192.168.0.%' identified by 'mha';
grant all privileges on *.* to 'mha'@'192.168.0.%';

MySQL备份

mysqldump -uroot -p -A -B --single-transaction --master-data=2 > /tmp/full.sql

MySQL主从复制

CHANGE MASTER TO
MASTER_HOST='192.168.0.141',
MASTER_USER='rep',
MASTER_PASSWORD='rep',
MASTER_PORT=3306,
MASTER_AUTO_POSITION=1;

从库设置read_only

mysql> set global read_only=1;

MHA

MySQL所有节点ssh免密

MHA node(所有MySQL节点)

yum install perl-DBD-MySQL -y
yum localinstall mha4mysql-node-0.58-0.el7.centos.noarch.rpm

MHA manager(非MySQL主库)

yum install perl-Config-Tiny perl-Log-Dispatch perl-Parallel-ForkManager perl-Time-HiRes
yum localinstall mha4mysql-manager-0.58-0.el7.centos.noarch.rpm

MHA manager创建目录

mkdir -p /etc/mha
mkdir -p /var/log/mha/app1/

MHA配置文件

vim /etc/mha/app1.cnf

[server default]
manager_log=/var/log/mha/app1/manager
manager_workdir=/var/log/mha/app1
master_binlog_dir=/data/mysql/binlog
password=mha
ping_interval=2
repl_password=rep
repl_user=rep
ssh_user=root
user=mha

[server1]
candidate_master=1
check_repl_delay=0
hostname=192.168.0.141
port=3306

[server2]
candidate_master=1
check_repl_delay=0
hostname=192.168.0.137
port=3306

[server3]
hostname=192.168.0.138
no_master=1
port=3306

[binlog1]
no_master=1
hostname=192.168.0.138
master_binlog_dir=/data/mysql/binlog_server

MHA binlog server

mkdir /data/mysql/binlog_server
cd  /data/mysql/binlog_server
mysqlbinlog  -R --host=192.168.0.141 --user=mha --password=mha --raw  --stop-never mysql-bin.000004 &

MHA 检测ssh

masterha_check_ssh --conf=/etc/mha/app1.cnf

MHA检测主从复制

masterha_check_repl --conf=/etc/mha/app1.cnf

MHA状态查看

masterha_check_status --conf=/etc/mha/app1.cnf

MHA启动

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 &

MHA停止

masterha_stop --conf=/etc/mha/app1.cnf