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 &

因为数据量较大(导出大概30G)，使用nohup [commend] & 使用后台导出，防止导出时间过长连接断开，可以使用jobs -l命令查看

数据导出也可以直接在主库服务器上导出，然后通过scp backup.sql 192.168.111.66:/home命令传到从库服务器

从库数据导入

连接mysql导入

• 登陆mysql

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

• 执行导入sql命令

source /home/backup.sql

命令直接导入

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

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

注意:

• 可能需要删除backup.sql文件第一行sed -i '1d' backup.sql
• 在nohup后台运行的情况下，需要保持ssh连接，如果关闭请执行exit退出ssh，不然任务在ssh断开自动停止

在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