MySQL主从同步-binlog

服务器准备

IP	节点	配置	系统版本
191.168.117.143	master	2c2g40g	centos 7.9
192.168.117.142	slave	2c2g40g	centos 7.9

环境准备

下面操作需要在两台机器都操作

关闭防火墙

1	`systemctl stop firewalld && systemctl disable firewalld`

1	`setenforce 0 && sed -i 's/SELINUX=.*/SELINUX=disabled/g' /etc/selinux/config`

安装常用软件

1	`yum install -y wget vim net-tools bash-completion`

更新软件

1	`yum update`

下载mysql安装源

1	`https://dev.mysql.com/downloads/repo/yum/`

根据系统版本点击Download

点击下载rpm包或者右键复制链接

1	`wget https://dev.mysql.com/get/mysql80-community-release-el7-10.noarch.rpm`

1	`rpm -ivh mysql80-community-release-el7-10.noarch.rpm`

配置安装源

1 2	`cd /etc/yum.repos.d/ vim mysql-community.repo`

根据需求版本号配置

我这里直接装8.0 所以不需要修改

安装mysql

1	`yum -y install mysql-community-server`

Master节点操作

修改my.cnf配置

1	`vim /etc/my.cnf`

1
2
3

[mysqld]
log-bin=mysql-bin
server-id=1

启动数据库并配置开机自启

1	`systemctl start mysqld && systemctl enable mysqld`

获取MySQL初始化密码

1	`grep password /var/log/mysqld.log`

登录数据库

1	`mysql -p'密码' ##注意带特殊字符的必须要加单引号`

修改用户权限及slave复制用户

alter user 'root'@'localhost' identified  with mysql_native_password by 'Jianren@123';
grant all privileges on *.* to 'root'@'localhost';
create user 'slave'@'%' identified with mysql_native_password by 'Jianren@123';
grant replication  slave  on *.* to 'slave'@'%';
flush privileges;

1 2	`参数解释 with mysql_native_password 修改用户的身份验证插件，防止slave节点登录同步用户无法连接`

查看binlog开始日志

1	`show master status \G`

Slave从节点操作

配置本地域名解析

1	`vim /etc/hosts`

1	`192.168.117.143 mysql-master`

ping一下测试是否联通

1	`ping mysql-master`

修改my.cnf配置

1	`vim /etc/my.cnf`

1	`server-id=2`

启动mysql并配置开机自启

1	`systemctl start mysqld && systemctl enable mysqld`

修改mysql初始化密码及权限

1	`grep password /var/log/mysqld.log`

登录数据库

1	`mysql -p'密码' ##注意带特殊字符的必须要加单引号`

配置密码及权限

alter user 'root'@'localhost' identified by 'Jianren@123';
grant all  privileges  on *.* to 'root'@'localhost' ;
flush privileges;
exit ;

测试是否可以登录master节点的用户

1
2
3

mysql -uslave -pJianren@123 -h mysql-master
show databases ;
exit;

配置主从复制

登录root用户

1	`mysql -p'Jianren@123'`

编辑

edit

CHANGE MASTER TO
MASTER_HOST='mysql-master',
MASTER_PORT=3306,
MASTER_USER='slave',
MASTER_PASSWORD='Jianren@123',
MASTER_LOG_FILE='mysql-bin.000003',
MASTER_LOG_POS=157;

参数解释
MASTER_HOST='mysql-master'  	master的主机IP 也可以本地域名
MASTER_PORT=3306				master端口
MASTER_USER='slave' 			master创建的同步用户
MASTER_PASSWORD='Jianren@123'	master创建同步用户密码
MASTER_LOG_FILE='mysql-bin.000003'  master查看的binlog文件名
MASTER_LOG_POS=157;				masetr查看binlog的日志位置