mha mysql 5.7_MHA-mysql5.7.20

1.服务器：

192.168.9.24 Mha-master

192.168.9.25 Mha-slave

192.168.9.26 Mha-bak

2.布置开启Gtid功能mysql5.7.20并建立主从复制

2.1三台安装mysql5.7.20

#!/bin/bash

#author:pan

#切换到相应文件夹

cd

#二进制安装mysql5.7.20

tar xvf mysql-5.7.20-linux-glibc2.12-x86_64.tar.gz -C /usr/local

cd /usr/local/

mv mysql-5.7.20-linux-glibc2.12-x86_64/ mysql

cd /usr/local/mysql/

mkdir -p data log binlogs run

cd /usr/local/

#添加用户

useradd -M -s /sbin/nologin mysql

#更改属主

chown -R mysql.mysql mysql

#添加路径

ln -s /usr/local/mysql/bin/* /usr/local/bin/

#删除原my.cnf

rm -f /etc/my.cnf

#创建my.cnf

cat > /etc/my.cnf << EOF

[client]

port = 3306

socket = /usr/local/mysql/run/mysql.sock

[mysqld]

port = 3306

socket = /usr/local/mysql/run/mysql.sock

pid_file = /usr/local/mysql/run/mysql.pid

datadir = /usr/local/mysql/data

default_storage_engine = InnoDB

max_allowed_packet = 512M

max_connections = 2048

open_files_limit = 65535

skip-name-resolve

lower_case_table_names=1

character-set-server = utf8mb4

collation-server = utf8mb4_unicode_ci

init_connect=‘SET NAMES utf8mb4‘

innodb_buffer_pool_size = 1024M

innodb_log_file_size = 2048M

innodb_file_per_table = 1

innodb_flush_log_at_trx_commit = 0

key_buffer_size = 64M

log-error = /usr/local/mysql/log/mysql_error.log

log-bin = /usr/local/mysql/binlogs/mysql-bin

slow_query_log = 1

slow_query_log_file = /usr/local/mysql/log/mysql_slow_query.log

long_query_time = 5

tmp_table_size = 32M

max_heap_table_size = 32M

query_cache_type = 0

query_cache_size = 0

server-id=1

EOF

#初始化数据库

mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data

#设置启动项

cat > /usr/lib/systemd/system/mysqld.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

Type=forking

PIDFile=/usr/local/mysql/run/mysqld.pid

#Disable service start and stop timeout logic of systemd for mysqld service.

TimeoutSec=0

#Execute pre and post scripts as root

PermissionsStartOnly=true

#Needed to create system tables

#ExecStartPre=/usr/bin/mysqld_pre_systemd

#Start main service

ExecStart=/usr/local/mysql/bin/mysqld --daemonize --pid-file=/usr/local/mysql/run/mysqld.pid $MYSQLD_OPTS

#Use this to switch malloc implementation

EnvironmentFile=-/etc/sysconfig/mysql

#Sets open_files_limit

LimitNOFILE = 65535

Restart=on-failure

RestartPreventExitStatus=1

PrivateTmp=false

EOF

#重新加载daemon

systemctl daemon-reload

systemctl enable mysqld.service

systemctl start mysqld

#启动后第一次登陆密码

#grep ‘temporary password‘ /usr/local/mysql/log/mysql_error.log

#修改初始密码

#mysql> ALTER USER ‘root‘@‘localhost‘ IDENTIFIED BY ‘000000‘;

#安装完成

2.2开启gtid功能,修改配置文件

Mha-master主机：

[mysqld]

gtid_mode = ON

log_slave_updates 增加此三行调整server-id的值

enforce_gtid_consistency

server-id=1

Mha-slave主机：

[mysqld]

read_only = 1 设置只读

gtid_mode = ON

log_slave_updates 增加此三行调整server-id的值

enforce_gtid_consistency

server-id=2

relay_log_purge = 0 禁用自动删除relay log功能

重启mysql，并查看

[[email protected] ~]# systemctl restart mysqld

[[email protected] ~]# mysql -uroot -p000000

mysql> show global variables like ‘%gtid%‘;

+----------------------------------+-------+

| Variable_name | Value |

+----------------------------------+-------+

| binlog_gtid_simple_recovery | ON |

| enforce_gtid_consistency | ON |

| gtid_executed | |

| gtid_executed_compression_period | 1000 |

| gtid_mode | ON |

| gtid_owned | |

| gtid_purged | |

| session_track_gtids | OFF |

+----------------------------------+-------+

8 rows in set (0.00 sec)

2.3配置主从

2.3.1在Mha-master上创建主从复制账号

mysql> grant replication slave on . to [email protected]‘192.168.9.%‘ identified by ‘000000‘;

Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show grants for [email protected]‘192.168.9.%‘ ;

+-------------------------------------------------------+

| Grants for [email protected]% |

+-------------------------------------------------------+

| GRANT REPLICATION SLAVE ON . TO ‘rep‘@‘192.168.9.%‘ |

+-------------------------------------------------------+

1 row in set (0.00 sec)

2.3.2在两个从上进行主从同步

[[email protected] ~]# mysql -uroot -p000000

mysql> change master to master_host=‘192.168.9.24‘,master_user=‘rep‘,master_password=‘000000‘,master_auto_position=1;

Query OK, 0 rows affected, 2 warnings (0.01 sec)

mysql> start slave;

Query OK, 0 rows affected (0.00 sec)

mysql> show slave status\G;

查看是否成功，如果不成功查看是否是防火墙的问题

3.三台都做服务器ssh

[[email protected] ~]# ssh-keygen -t rsa -P "" -f ~/.ssh/id_rsa

[[email protected] ~]# sshpass -p 102110504 ssh-copy-id -i ~/.ssh/id_rsa.pub -o StrictHostKeyChecking=no [email protected]

4.部署MHA