mysql 5.7 gtid 搭建_mysql5.7主从搭建--基于GTID

*mysql5.7主从搭建--基于GTID*

环境搭建

*硬件环境：*

主M：192.168.56.11

从S：192.168.56.12

*系统环境：*

#系统版本

[root@ceph1 ~]# ***\*cat /etc/redhat-release\****

CentOS Linux release 7.5.1804 (Core)

#防火墙

[root@ceph1 ~]# ***\*systemctl stop firewalld\****

[root@ceph1 ~]# ***\*systemctl status firewalld\****

● firewalld.service - firewalld - dynamic firewall daemon

Loaded: loaded (/usr/lib/systemd/system/firewalld.service; disabled; vendor preset: enabled)

Active: inactive (dead)

Docs: man:firewalld(1)

Jun 04 00:04:10 ceph1 systemd[1]: Starting firewalld - dynamic firewall daemon...

Jun 04 00:04:11 ceph1 systemd[1]: Started firewalld - dynamic firewall daemon.

Jun 04 00:44:31 ceph1 systemd[1]: Stopping firewalld - dynamic firewall daemon...

Jun 04 00:44:31 ceph1 systemd[1]: Stopped firewalld - dynamic firewall daemon.

[root@ceph1 ~]# ***\*getenforce\****

Permissive

#卸载自带的Mariadb

*yum -y remove mariadb-libs*

*软件版本：*

[root@ceph1 ~]# yum install wget -y

wget https://mirrors.cloud.tencent.com/mysql/downloads/MySQL-5.7/mysql-community-common-5.7.28-1.el7.x86_64.rpm

wget https://mirrors.cloud.tencent.com/mysql/downloads/MySQL-5.7/mysql-community-libs-5.7.28-1.el7.x86_64.rpm

wget https://mirrors.cloud.tencent.com/mysql/downloads/MySQL-5.7/mysql-community-client-5.7.28-1.el7.x86_64.rpm

wget https://mirrors.cloud.tencent.com/mysql/downloads/MySQL-5.7/mysql-community-server-5.7.28-1.el7.x86_64.rpm

wget https://mirrors.cloud.tencent.com/mysql/downloads/MySQL-5.7/mysql-community-libs-compat-5.7.28-1.el7.x86_64.rpm

部署服务

*安装服务*

yum install -y libaio net-tools perl

rpm -ivh ./*.rpm #安装上面下载的5个rpm包

安装顺序是：

*启动服务*

systemctl start mysqld && systemctl enable mysqld

*修改密码*

#grep password /var/log/mysqld.log | sed 's/.*(............)$/\1/'

mysql> set password for root@localhost = password('Abcd1234!');

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

mysql> grant all privileges on . to 'root'@'%' identified by 'Abcd1234!';

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

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

*修改主配置文件*

server_id = 1 #服务器id

gtid_mode = on #开启gtid模式

enforce_gtid_consistency = on #强制gtid一致性，开启后对特定的create table不被支持

log-bin = mysql-bin #开启二进制日志

binlog_format = row #默认为mixed混合模式，更改成row复制，为了数据一致性

log-slave-updates = 1 #从库binlog才会记录主库同步的操作日志

skip_slave_start=1 #跳过slave复制线程

*修改从配置文件*

server_id = 2

log-bin = mysql-bin

binlog_format = row

log-slave-updates = 1

gtid_mode = on

enforce_gtid_consistency = on

skip_slave_start=1

*修改数据库设置*

*Master设置*

授权从库的复制权限

mysql> show master status; #刚开始的状态

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

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

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

| mysql-bin.000001 | 154 | | | |

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

1 row in set (0.00 sec)

mysql> grant replication slave on . to 'root'@'%' identified by 'Abcd1234!';#创建同步账号

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

mysql> show master status;#查看状态

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

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

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

| mysql-bin.000001 | 437 | | | 0855ccd8-a5b8-11ea-8378-000c29b073ec:1 |

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

1 row in set (0.00 sec)

*Slave设置*

设置链接主库的信息

change master to master_host='192.168.56.11',master_user='root',master_password='Abcd1234!',master_log_file='mysql-bin.000001', master_log_pos=437;

查看是否开启

mysql> start slave；

mysql> show slave status\G

应该能看到有这两项开启

*验证操作*