Mysql GTID Mha配置方法

2024-07-24 12:52:14

字体：大中小

来源：转载

供稿：网友

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 status/G*************************** 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: 11 row in set (0.00 sec)

4：安装Mha

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

配置SSH等效：

在所有节点都执行

ssh-keygen -t rsassh-copy-id -i /root/.ssh/id_rsa.pub root@mysql1ssh-copy-id -i /root/.ssh/id_rsa.pub root@mysql2ssh-copy-id -i /root/.ssh/id_rsa.pub root@mysql3

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

ssh myqsl1ssh myqsl2ssh 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清除缓存

上一篇：在win10系统下安装Mysql 5.7.17图文教程

下一篇：MySQL PXC构建一个新节点只需IST传输的方法(推荐)