主机环境
系统支持centos8,rocky8
[root@mysql-master ~]# cat /etc/os-release
NAME="Rocky Linux"
VERSION="8.6 (Green Obsidian)"
关闭防火墙,selinux,开启时间同步
一、mysql主从搭建
(1)安装数据库,修改配置文件
修改主机名
hostnamectl set-hostname mysql-master
hostnamectl set-hostname mysql-slave
[root@mysql-master ~]# hostname -I
10.0.0.100
[root@mysql-slave ~]# hostname -I
10.0.0.101
[root@mysql-master ~]# yum -y install mysql-server
[root@mysql-slave ~]# yum -y install mysql-server
-------------------------master-------------------------
修改配置文件
[root@mysql-master ~]# vim /etc/my.cnf
[mysqld]
server-id=100
log-bin=/data/mysql/mysql-bin
general_log
#半同步
plugin-load-add="semisync_master.so"
loose-rpl_semi_sync_master_enabled=ON
loose-rpl_semi_sync_master_timeout=3000
#gtid模式
gtid_mode=ON
enforce_gtid_consistency #保证GTID安全的参数
-------------------------slave-------------------------
修改配置文件
[root@mysql-master ~]# vim /etc/my.cnf
[mysqld]
server-id=101
log-bin=/data/mysql/mysql-bin
general_log
#半同步
loose-plugin-load-add="semisync_slave.so"
loose-rpl_semi_sync_slave_enabled=ON
#gtid模式
gtid_mode=on
enforce_gtid_consistency #保证GTID安全的参数
(2)两台主机执行
新增二进制日志路径文件夹
mkdir -p /data/mysql/
chown -R mysql:mysql /data
开机启动数据库
systemctl enable mysqld
systemctl start mysqld
(3)master执行
mysql> show master logs;
+------------------+-----------+-----------+
| Log_name | File_size | Encrypted |
+------------------+-----------+-----------+
| mysql-bin.000001 | 179 | No |
| mysql-bin.000002 | 156 | No |
+------------------+-----------+-----------+
2 rows in set (0.00 sec)
查看插件
mysql> SHOW PLUGINS;
查看变量
mysql> SHOW GLOBAL VARIABLES LIKE '%semi%'
创建用户
mysql> create user repluser@'10.0.0.%' identified by '123456';
mysql> grant replication slave on *.* to repluser@'10.0.0.%';
(4)slave执行
加入master
mysql> CHANGE MASTER TO
MASTER_HOST='10.0.0.100',
MASTER_USER='repluser',
MASTER_PASSWORD='123456',
MASTER_PORT=3306,
MASTER_CONNECT_RETRY=10;
启用
mysql> start slave;
查看状态
mysql> show slave status\G;
二、master创建数据,查看数据是否同步
创建数据库
mysql> create database testdb;
mysql> use testdb;
创建表
mysql> create table student(id int unsigned auto_increment primary key,name varchar(20) not null,age tinyint unsigned,gender enum('M','F')default 'M');
查看表结构
mysql> desc student;
插入数据
mysql> insert student(name,age) values('xiaoming',18);
查询表数据
mysql> select * from student;
+----+----------+------+--------+
| id | name | age | gender |
+----+----------+------+--------+
| 1 | xiaoming | 18 | M |
+----+----------+------+--------+
1 row in set (0.00 sec)
到slave服务器上查询,可以发现数据和master是一模一样的
三、测试半同步
先敲好代码,再停止slave的mysql服务,然后立即在master上插入表
[root@mysql-slave ~]# systemctl stop mysqld
master停留几秒,检测slave是否在线,检测slave掉线了,才插入数据
mysql> insert student(name,age) values('test2',18);
启动slave后,自动加入master
[root@mysql-slave ~]# systemctl start mysqld
查看数据,发现master和slave上的数据都是一样的