搭建一个MySQL从库

在阿里云上购买机器

Master MySQL 配置文件修改

# 开启 log-bin
log-bin=mysql-bin

# 配置 server-id, 不能和从库的重复，所有的从库也都不能重复
server-id=1

登录主库，在主库上创建用于同步的账户

#创建用户
CREATE USER 'myslave'@'192.168.0.103' IDENTIFIED BY '123456';

#分配权限, % 可以是 具体的IP地址，仅允许指定IP访问
GRANT REPLICATION SLAVE ON *.* TO 'myslave'@'%';

#刷新权限
flush privileges;


#查看bin-log位置
show master status;

Slave MySQL 配置文件修改

port=6033
default_time_zone='+08:00'
replicate-wild-do-table=m%.%

server-id=31
sync-binlog = 1
sync-relay-log = 1
sync-relay-log-info = 1
sync-master-info = 1

max_allowed_packet=32M

bind-address= 0.0.0.0
log-bin
log_bin_trust_function_creators
log-slave-updates
master_info_repository = 'TABLE'

从库

stop slave;

CHANGE MASTER TO MASTER_HOST='X-MASTER', MASTER_PORT=3306,MASTER_USER='myslave', MASTER_PASSWORD='123456', MASTER_LOG_FILE='x-master-bin.000001', MASTER_LOG_POS=154;

start slave;

# Slave_IO_Running: Yes   Slave_SQL_Running: Yes  说明成功
show slave status;

清除从库记录

reset slave