mysql主从分离读写_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