Mysql 主从复制搭建

0. 服务器架构

序号	服务器	IP	描述
1	Mysql-master	192.168.31.157	写节点(主)
2	Mysql-slave	192.168.31.167	只读节点(从)

1. 主服务器

1.1修改配置文件

cat /etc/my.cnf.d/mysql-server.cnf
[mysqld]
server_id=157
log-bin=/data/binlog/mysql-bin
binlog_format=row
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysql/mysqld.log
pid-file=/run/mysqld/mysqld.pid

1.2 建立二进制文件存放目录

mkdir -p /data/binlog/
chown -R mysql.mysql /data/binlog/

1.3 启动数据库服务

systemctl start mysqld

1.4 创建账号

create user repluser@'192.168.31.%' identified by '123456';
grant replication slave on *.* to repluser@'192.168.31.%';

1.5 数据库全备

mysqldump -A -F -uroot -p123456 --master-data=1 --single-transaction > /backup/all.sql

–master-data=1 适用于主从复制
–master-data=2 适用于单机

1.6 将备份复制到slave服务器上

scp /backup/all.sql 192.168.31.167:/backup/

2. Slave服务器

2.1 修改配置文件

vi /etc/my.cnf.d/mysql-server.cnf
[mysqld]
server_id=167
log-bin=/data/binlog/mysql-bin
binlog_format=row
read-only
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysql/mysqld.log
pid-file=/run/mysqld/mysqld.pid

2.2 建立二进制文件存放目录

mkdir -p /data/binlog/
chown -R mysql.mysql /data/binlog/

2.3 启动数据库

systemctl start mysqld

2.4 修改备份文件

vim /backup/all.sql
CHANGE MASTER TO
        MASTER_HOST='192.168.31.157',
        MASTER_USER='repluser',
        MASTER_PASSWORD='Pana#123',
        MASTER_PORT=3306,
        MASTER_LOG_FILE='mysql-bin.000015', MASTER_LOG_POS=156;

2.5 暂时关闭二进制日志

mysql -uroot
set sql_log_bin=0;

2.6 恢复备份

source /backup/all.sql;

2.7 查看show slave status\G

2.8 启动slave的2个线程

2.9 开启logbin

set sql_log_bin=1;

2.10 后续数据库会被持续更新

从节点