MySQL 5.7.39 主从复制搭建

一、环境

• 系统：CentOS 7
• master服务器ip：192.168.68.130
• slave服务器ip：192.168.68.131

二、实验操作步骤

2.1 master服务器

MySQL安装

systemctl stop firewalld.service
systemctl disable firewalld.service

#查询并删除系统自带的Mariadb
rpm -qa | grep mariadb
#程序名是上述命令查询到的结果
rpm -e --nodeps 程序名

#创建/opt/mysql目录，并上传mysql安装包至该目录下
cd /opt/mysql
tar -xf mysql-5.7.39-1.el7.x86_64.rpm-bundle.tar -C /opt/mysql/

#按照顺序依次安装rpm包
rpm -ivh mysql-community-common-5.7.39-1.el7.x86_64.rpm
rpm -ivh mysql-community-libs-5.7.39-1.el7.x86_64.rpm
rpm -ivh mysql-community-devel-5.7.39-1.el7.x86_64.rpm
rpm -ivh mysql-community-libs-compat-5.7.39-1.el7.x86_64.rpm
rpm -ivh mysql-community-client-5.7.39-1.el7.x86_64.rpm
rpm -ivh mysql-community-server-5.7.39-1.el7.x86_64.rpm

MySQL配置

vim /etc/my.conf
#末尾添加以下内容
#mysql服务ID,保证唯一性
server-id=1
#开启binlog日志
log-bin=/var/lib/mysql/mysql-bin
#设置数据库是否只读，0是读写，1是只读
read-only=0
#指定同步的数据库
#binlog-do-db=数据库名
#忽略同步的数据库
#binlog-ignore-db=数据库名

systemctl start mysqld
service mysqld status
#查询mysql为root用户生成的临时密码
grep 'temporary password' /var/log/mysqld.log
#使用临时密码登录mysql
mysql -u root -p
#修改root用户密码
mysql> alter user 'root'@'localhost' identified with mysql_native_password by 'Root12!@';
#创建远程连接的账号，并赋予复制权限
mysql> create user 'user01'@'%' identified with mysql_native_password by 'User0112!@';
mysql> grant replication slave on *.* to 'user01'@'%';
#查看二进制日志坐标
mysql> show master status;

2.2 slave服务器

MySQL安装

systemctl stop firewalld.service
systemctl disable firewalld.service

#查询并删除系统自带的Mariadb
rpm -qa | grep mariadb
#程序名是上述命令查询到的结果
rpm -e --nodeps 程序名

#创建/opt/mysql目录，并上传mysql安装包至该目录下
cd /opt/mysql
tar -xf mysql-5.7.39-1.el7.x86_64.rpm-bundle.tar -C /opt/mysql/

#按照顺序依次安装rpm包
rpm -ivh mysql-community-common-5.7.39-1.el7.x86_64.rpm
rpm -ivh mysql-community-libs-5.7.39-1.el7.x86_64.rpm
rpm -ivh mysql-community-devel-5.7.39-1.el7.x86_64.rpm
rpm -ivh mysql-community-libs-compat-5.7.39-1.el7.x86_64.rpm
rpm -ivh mysql-community-client-5.7.39-1.el7.x86_64.rpm
rpm -ivh mysql-community-server-5.7.39-1.el7.x86_64.rpm

MySQL配置

vim /etc/my.conf
#末尾添加以下内容
#mysql服务ID,保证唯一性
server-id=2
#开启binlog日志
log-bin=/var/lib/mysql/mysql-bin
#设置数据库是否只读，0是读写，1是只读
read-only=1
#relay-bin文件损坏，自动放弃未执行的relay-log，重新从master上获取日志
relay_log_recovery=1
#指定同步的数据库
#binlog-do-db=数据库名
#忽略同步的数据库
#binlog-ignore-db=数据库名

systemctl start mysqld
service mysqld status
#查询mysql为root用户生成的临时密码
grep 'temporary password' /var/log/mysqld.log
#使用临时密码登录mysql
mysql -u root -p
#修改root用户密码
mysql> alter user 'root'@'localhost' identified with mysql_native_password by 'Root12!@';
#配置主库配置
mysql> change master to master_host='192.168.68.130', master_port=3306,master_user='user01', master_password='User0112!@', master_log_file='mysql-bin.000002', master_log_pos=843;
#开启同步操作
mysql> start slave;
#查看主从同步状态
mysql> show slave status\G;