mysql配置主从复制环境构建至少需要两台服务器,本次为(一主一从)
环境准备
Master: 192.168.124.128
Slave: 192.168.124.129
配置主从复制最好关闭防火墙:
systemctl stop firewalld
#关闭selinux(临时)
setenforce 0
#永久关闭selinux
vi /etc/selinux/config
# This file controls the state of SELinux on the system.
# SELINUX= can take one of these three values:
# enforcing - SELinux security policy is enforced.
# permissive - SELinux prints warnings instead of enforcing.
# disabled - No SELinux policy is loaded.
SELINUX=enforcing #将 enforcing --> disabled
# SELINUXTYPE= can take one of three values:
# targeted - Targeted processes are protected,
# minimum - Modification of targeted policy. Only selected processes are protected.
# mls - Multi Level Security protection.
SELINUXTYPE=targeted
Master端安装及配置
yum下载mysql:
yum install mariadb mariadb-server epel-release net-tools -y
#配置阿里镜像源:
wget -O /etc/yum.repos.d/CentOS-Base.repo https://mirrors.aliyun.com/repo/Centos-7.repo
启动mysql并添加到开机自启:
#启动并设置开机自启
systemctl start mariadb && systemctl enable mariadb
查看mysql是否正常启动:
[root@master ~]# netstat -nltp;ps -ef|grep mysql
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name
tcp 0 0 0.0.0.0:22 0.0.0.0:* LISTEN 1143/sshd
tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 1533/mysqld
tcp6 0 0 :::22 :::* LISTEN 1143/sshd
mysql 1215 1 0 21:19 ? 00:00:00 /bin/sh /usr/bin/mysqld_safe --basedir=/usr
mysql 1533 1215 0 21:19 ? 00:00:01 /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --log-error=/var/log/mariadb/mariadb.log --pid-file=/var/run/mariadb/mariadb.pid --socket=/var/lib/mysql/mysql.sock
root 2131 1466 0
编辑/etc/my.cnf文件
在[mysqld]下添加配置:
server-id=1 #数据库唯一ID,主从的标识号绝对不能重复,master端server-id与slave端不一样即可,相当于身份id
log-bin=mysql-bin #开启bin-log,并指定文件目录和文件名前缀,记录执行的sql语句,相当于日志功能
binlog-ignore-db=mysql #不同步的系统数据库。如果是多个不同步库,就以此格式另写几行;也可以在一行,中间逗号隔开
sync_binlog = 1 #确保binlog日志写入后与硬盘同步
binlog_checksum = none #跳过现有的采用checksum的事件,mysql5.6.5以后的版本中binlog_checksum=crc32,而低版本都是binlog_checksum=none
binlog_format = mixed #bin-log日志文件格式,设置为MIXED可以防止主键重复
配置文件修改完成后重启mysql
systemctl restart mariadb
在master上设置同步权限:
#先进入数据库:
@master ~]# mysql
#设置数据库密码
set password for root@localhost = password('new passwd');
# 只允许192.168.124.129使用root用户,且密码为"123"连接主库做数据同步
grant replication slave,replication client on *.* to root@'192.168.124.129' identified by "123";
#允许所有使用root用户,密码为"123"连接主库做数据同步
grant replication slave,replication client on *.* to root@'%' identified by "123";
# 刷新权限
flush privileges;
# 权限查看
show grants;
show grants for root@'192.168.128.129';
锁库(防止导出过程中有数据写入)
#锁定数据库
flush tables with read lock;
#查看数据库状态:
show master status;
#创建目录
mkdir /dump
#导出需要同步的库
mysqldump -uroot -p'' -S /var/lib/mysql/mysql.sock --events -A -B |gzip >/dump/mysql_back.$(date +%F).sql.gz
Enter password: 输入数据库密码
# 解除锁定
unlock tables;
slave端安装及配置:
安装跟master一样(建议不要克隆)
编辑/etc/my.cnf
在[mysqld]下面添加:
server-id=2 #设置从服务器id,必须于主服务器不同
replicate-ignore-db=mysql #不同步的数据库
slave-skip-errors = all #跳过所有的错误,继续执行复制操作
配置完重启mysql
systemctl restart mariadb
把从主库导出的数据恢复到从库
@master ~]# cd /dump
@master dump]# scp mysql_back.2022-06-06.sql.gz root@192.168.124.129:/dump
配置主从同步指令
#进入Slave数据库
mysql
#执行同步前,要先关闭slave
stop slave;
#在master库查看
MariaDB [(none)]> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000002 | 245 | | mysql |
+------------------+----------+--------------+------------------+
#需要和主数据库的show master status命令结果保持一致
change master to master_host='192.168.124.128',master_user='root',master_password='123',master_log_file='mysql-bin.000002',master_log_pos=245;
#启动Slave端
start slave;
#查询数据库状态:
show slave status \G;
#有以下三个就说明配置成功了
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Seconds_Behind_Master: 0
在Master写入数据查看是否同步
主库创建数据库