• 欢迎访问VPS岛网站,国外VPS,国内VPS,国外服务器,国内服务器,服务器主机,测评及优惠码,推荐使用最新版火狐浏览器和Chrome浏览器访问本网站 QQ群

mysql5.7主从搭建–基于GTID

mysql技术 kesungang 59次浏览 已收录 0个评论

*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)

img

*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;

img

查看是否开启

mysql> start slave;

mysql> show slave status\G

应该能看到有这两项开启

img

*验证操作*

img


VPS岛 的文章和资源来自互联网,仅作为参考资料,如果有侵犯版权的资源请尽快联系站长,我们会在24h内删除有争议的资源。丨 转载请注明mysql5.7主从搭建–基于GTID
喜欢 (0)
发表我的评论
取消评论
表情 贴图 加粗 删除线 居中 斜体 签到

Hi,您需要填写昵称和邮箱!

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址