MySQL8.0 mha gtid主从同步搭建步骤

MySQL8.0 mha gtid主从同步搭建步骤

1.二进制安装

0）、修改主机名，配置hosts

hostnamectl set-hostname node1

hostnamectl set-hostname node2

hostnamectl set-hostname node3

127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4

::1 localhost localhost.localdomain localhost6 localhost6.localdomain6

172.17.0.35 node1

172.17.0.29 node2

172.17.0.56 node3

1）、创建操作系统用户：

groupadd mysql

useradd -g mysql mysql

2）、解压二进制包，建立软链接

cd /usr/local

tar xvf mysql-8.0.25-linux-glibc2.12-x86_64.tar.xz

ln -s mysql-8.0.25-linux-glibc2.12-x86_64 mysql

3）、修改配置文件my.cnf

主：172.17.0.35

[client]

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

[mysqld]

basedir=/usr/local/mysql

datadir=/usr/local/mysql/data

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

log-error=/usr/local/mysql/data/mysqld.err

port=3306

user=mysql

log_timestamps=system

default_authentication_plugin = mysql_native_password

log-bin=mysql-bin

server-id=663306

gtid-mode=on

enforce-gtid-consistency=1

从1：172.17.0.29

[client]

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

[mysqld]

basedir=/usr/local/mysql

datadir=/usr/local/mysql/data

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

log-error=/usr/local/mysql/data/mysqld.err

port=3308

user=mysql

log_timestamps=system

default_authentication_plugin = mysql_native_password

log-bin=mysql-bin

server-id=663306

gtid-mode=on

enforce-gtid-consistency=1

从2：172.17.0.56

[client]

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

[mysqld]

basedir=/usr/local/mysql

datadir=/usr/local/mysql/data

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

log-error=/usr/local/mysql/data/mysqld.err

port=3307

user=mysql

log_timestamps=system

default_authentication_plugin = mysql_native_password

log-bin=mysql-bin

server-id=883306

gtid-mode=on

enforce-gtid-consistency=1

4）、创建数据目录，并修改其属主、属组。

mkdir -p /usr/local/mysql/data

chown mysql.mysql /usr/local/mysql/data

4.1）、配置互信，每台都要做

ssh-keygen -t rsa

ssh-copy-id 172.17.0.35

ssh-copy-id 172.17.0.29

ssh-copy-id 172.17.0.56

4.3、关闭防火墙以及selinux

systemctl stop firewalld.service

systemctl disable firewalld.service

systemctl status firewalld.service

vim /etc/selinux/config

SELINUX=disabled

setenforce 0

5）、初始化实例：

/usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf --initialize

6）、启动实例

/usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf &

7）、登录实例（使用初始化过程中生成的随机密码）

grep ‘temporary password’ mysqld.err

8）、配置mysql环境变量

echo “PATH=/usr/local/mysql/bin:/usr/local/mysql/lib:$PATH” >> /etc/profile

echo “export PATH” >> /etc/profile

source /etc/profile

9）、修改root用户密码

mysql -uroot -p’>H0VQ;ZEm:FK’;

ALTER USER ‘root’@‘localhost’ IDENTIFIED WITH mysql_native_password BY ‘123456’;

create user root@’%’ IDENTIFIED WITH mysql_native_password BY ‘123456’;

10）、创建同步用户repl以及赋权

create user ‘repl’@’%’ identified with mysql_native_password by ‘123456’;

GRANT REPLICATION SLAVE ON . TO ‘repl’@’%’;

11）、创建用户mha以及赋权

create user ‘mha’@’%’ identified with mysql_native_password by ‘123456’;

grant all privileges on . to ‘mha’@’%’;

flush privileges;

12）、验证用户远程登录是否成功

mysql -urepl -p123456 -h172.17.0.35 -P 3306

13）、检查同步用户权限

show grants for repl@’%’\G

14）、搭建MySQL8.0gtid主从同步

主：172.17.0.35

mysql> show master status \G;

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

File: mysql-bin.000001

Binlog_Do_DB:


Binlog_Ignore_DB:

Executed_Gtid_Set:

1 row in set (0.00 sec)

ERROR:

No query specified

从1：172.17.0.29

change master to master_host=“172.17.0.35”,master_user=“repl”,master_password=“123456”,master_port=3306,master_auto_position=1;

start slave;

show slave status \G;

mysql> change master to master_host=“172.17.0.35”,master_user=“repl”,master_password=“123456”,master_port=3306,master_auto_position=1;

Query OK, 0 rows affected, 8 warnings (0.06 sec)

mysql>

mysql>

mysql> start slave;

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

mysql> show slave status \G;

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

Slave_IO_State: Waiting for master to send event

Replicate_Wild_Do_Table:


Replicate_Wild_Ignore_Table:

Last_Errno: 0

Seconds_Behind_Master: 0


Master_SSL_Verify_Server_Cert: No

Last_IO_Errno: 0

Last_SQL_Error:


Replicate_Ignore_Server_Ids:

Master_Server_Id: 663306

Network_Namespace:


1 row in set, 1 warning (0.00 sec)

ERROR:

No query specified

从2：172.17.0.56

change master to master_host=“172.17.0.35”,master_user=“repl”,master_password=“123456”,master_port=3306,master_auto_position=1;

start slave;

show slave status \G;

mysql> change master to master_host=“172.17.0.35”,master_user=“repl”,master_password=“123456”,master_port=3306,master_auto_position=1;

Query OK, 0 rows affected, 8 warnings (0.03 sec)

mysql> start slave;

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

mysql> show slave status \G;

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

Slave_IO_State: Waiting for master to send event

Replicate_Wild_Do_Table:


Replicate_Wild_Ignore_Table:

Last_Errno: 0

Seconds_Behind_Master: 0


Master_SSL_Verify_Server_Cert: No

Last_IO_Errno: 0

Last_SQL_Error:


Replicate_Ignore_Server_Ids:

Master_Server_Id: 663306

Network_Namespace:


1 row in set, 1 warning (0.00 sec)

ERROR:

No query specified

15）、如主从环境搭建失败清理步骤

主：

reset master;

show master status \G;

从：

stop slave;

reset slave all;

show slave status \G;

以上为测试环境实验，生产环境谨慎操作。