mysql gtid能做mha吗_Mysql GTID Mha配置方法

Gtid + Mha +Binlog server配置：

1：测试环境

OS：CentOS 6.5

Mysql：5.6.28

Mha：0.56

192.168.1.21 mysql1 M1

192.168.1.22 mysql2 S1

192.168.1.23 mysql3 S2 Mha manage、Binlog server

2：配置/etc/my.cnf相关参数,在3各节点中分别配置

binlog-format=ROW

log-slave-updates=true

gtid-mode=on

enforce-gtid-consistency=true

master-info-repository=TABLE

relay-log-info-repository=TABLE

sync-master-info=1

slave-parallel-workers=2

binlog-checksum=CRC32

master-verify-checksum=1

slave-sql-verify-checksum=1

binlog-rows-query-log_events=1

设置root密码，创建复制用户：

mysql> use mysql;

mysql> GRANT ALL PRIVILEGES ON *.* TO root@"%" IDENTIFIED BY "oracle123";

mysql> update user set Password = password('oracle123') where User='root';

mysql> flush privileges;

mysql> GRANT replication slave ON *.* TO 'repl'@'%' identified by 'oracle';

mysql> flush privileges;

3：在mysql2、mysql3配置Gtid复制

CHANGE MASTER TO

MASTER_HOST = '192.168.1.21',

MASTER_PORT = 3306,

MASTER_USER = 'repl',

MASTER_PASSWORD = 'oracle',

MASTER_AUTO_POSITION = 1;

start slave;

mysql> show slave statusG

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.1.21

Master_User: repl

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000003

Read_Master_Log_Pos: 524

Relay_Log_File: mysql-relay-bin.000002

Relay_Log_Pos: 734

Relay_Master_Log_File: mysql-bin.000003

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB:

......

Master_SSL_Crlpath:

Retrieved_Gtid_Set: 9ee7c7af-cbf3-11e5-bf75-000c2923e459:1-2

Executed_Gtid_Set: 9ee7c7af-cbf3-11e5-bf75-000c2923e459:1-2

Auto_Position: 1

1 row in set (0.00 sec)

4：安装Mha

rpm -Uvh epel-release-6-8.noarch.rpm

配置SSH等效：

在所有节点都执行

ssh-keygen -t rsa

ssh-copy-id -i /root/.ssh/id_rsa.pub root@mysql1

ssh-copy-id -i /root/.ssh/id_rsa.pub root@mysql2

ssh-copy-id -i /root/.ssh/id_rsa.pub root@mysql3

测试ssh登录，在3各节点分别测试：

ssh myqsl1

ssh myqsl2

ssh myqsl3

binlog server配置：在mysql3

mkdir -p /mysql/backup/binlog

/usr/local/mysql/bin/mysqlbinlog -R --raw --host=192.168.1.20 --user='root' --password='oracle123' --stop-never mysql-

bin.000003 &

最后那个binlog文件时给定从那个binlog文件开始。另外需要注意，当mysql1上的mysql进程退出后，binlog server也会退出。

需要安装一些包做支持，使用yum网络源；如安装遇到问题可以尝试yum update更新yum源或yum clean all清除缓存