mysql主从读写分离实战

环境

• mysql-5.7.19
• Centos7
• master: 192.168.111.64
• slave: 192.168.111.66

主从配置

master配置

## GTID
server_id = 100  # 服务器id，从库最好大于主库,注意要唯一
gtid_mode = on    #开启gtid模式
enforce_gtid_consistency = on    #强制gtid一致性，开启后对特定的create table不被支持
log-bin = mysql-bin    #开启二进制日志
binlog_format = row    #默认为mixed混合模式，更改成row复制，为了数据一致性
log-slave-updates = 1    #从库binlog才会记录主库同步的操作日志
skip_slave_start=1    #跳过slave复制线程

slave配置

## 设置server_id，注意要唯一
server_id=101
log-bin = mysql-bin
binlog_format = row
log-slave-updates = 1
gtid_mode = on
enforce_gtid_consistency = on
skip_slave_start=1
# 从库设置为只读
read_only=on

重启数据库

service mysqld restart

创建用户

# 用户从库同步
GRANT SELECT,RELOAD,SHOW DATABASES,LOCK TABLES,EVENT,REPLICATION CLIENT  ON *.* TO 'repl_user'@'192.168.111.64' IDENTIFIED BY '123456';

# 创建只读用户，用户从库读取数据
GRANT Select ON *.* TO 'reader'@'192.168.111.%'  IDENTIFIED BY "123456";

数据导出

# 在192.169.111.66上操作
nohup mysqldump --single-transaction --master-data=2 --triggers --routines --all-databases --events -h192.168.111.64 -u'root' -p'password' > /home/backup.sql &

从库数据导入

连接 mysql 导入

• 登陆mysql

# 连接到数据库
mysql -uroot -p'123456';

• 执行导入 sql 命令

source /home/backup.sql

命令直接导入

nohup mysql -uroot -p’123456’ < /home/backup.sql &

推荐第二种方式，尤其是数据量较大的情况下

在192.168.111.66上设置主从连接

• 连接mysql

mysql -uroot -p'123456'

• 设置

# 设置
CHANGE MASTER TO MASTER_HOST='192.168.111.64', MASTER_PORT=3306,MASTER_USER='repl_user',MASTER_PASSWORD='123456',MASTER_AUTO_POSITION=1;

# 开启主从
start slave;

# 查看主从状态
show slave status \G;

stop slave
reset slave
reset master