mysql主从复制及其读写分离配置

---

1.理解MySQL主从复制原理。

主从复制原理

主要基于MySQL二进制日志

主要包括三个线程（2个I/O线程，1个SQL线程）

1、MySQL将数据变化记录到二进制日志中；
2、Slave将MySQL的二进制日志拷贝到Slave的中继日志中；
3、Slave将中继日志中的事件在做一次，将数据变化，反应到自身（Slave）的数据库
主库将所有的写操作记录在binlog日志中，并生成log dump线程，将binlog日志传给从库的I/O线程
从库生成两个线程，一个是I/O线程，另一个是SQL线程I/O线程去请求主库的binlog日志，并将binlog日志中的文件写入relay log（中继日志）中SQL线程会读取relay loy中的内容，并解析成具体的操作，来实现主从的操作一致，达到最终数据一致的目的。

详细步骤：
1、从库通过手工执行change master to 语句连接主库，提供了连接的用户一切条件（user 、password、port、ip），并且让从库知道，二进制日志的起点位置（file名 position 号）； startslave
2、从库的IO线程和主库的dump线程建立连接。
3、从库根据change master to 语句提供的file名和position号，IO线程向主库发起binlog的请求。
4、主库dump线程根据从库的请求，将本地binlog以events的方式发给从库IO线程。
5、从库IO线程接收binlog events，并存放到本地relay-log中，传送过来的信息，会记录到master.info中
6、从库SQL线程应用relay-log，并且把应用过的记录到relay-log.info中，默认情况下，已经应用过的relay 会自动被清理purge

2.完成MySQL主从复制（一主两从）。

• 配置步骤

1）确保从数据库与主数据库里的数据一致

2）在主数据库里创建一个同步账户授权给从数据库使用

3）配置主数据库（修改配置文件）

4）配置从数据库（修改配置文件）

5）需求

6）搭建两台MySQL服务器，一台作为主服务器，一台作为从服务器，主服务器进行写操作，从服务器进行读操作

• 物理机

  环境准备
两台机器一主二从。
主库（MySQL Master）：[ip为192.168.95.120 port为3306]
从库1（MySQL Slave ）：[ip为192.168.95.130 port为3306]
从库2（MySQL Slave ）：[ip为192.168.95.131 port为3306]

  主库配置
1）设置server-id值并开启binlog参数
[mysqld]
log_bin = mysql-bin
server_id = 120

重启数据库

2） 建立同步账号
mysql> grant replication slave on *.* to 'rep'@'192.168.95.%' identified by '123456';
mysql> show grants for 'rep'@'192.168.95.%';

3）锁表设置只读
为后面备份准备，注意生产环境要提前申请停机时间；
mysql> flush tables with read lock;
提示：如果超过设置时间不操作会自动解锁。
mysql> show variables like '%timeout%';
测试锁表后是否可以创建数据库：

4）查看主库状态
查看主库状态，即当前日志文件名和二进制日志偏移量
mysql> show master status;

5）备份数据库数据
# mysqldump -uroot -p -A -B |gzip > /server/backup/mysql_bak.$(date +%F).sql.gz


6）解锁
mysql> unlock tables;

7）主库备份数据上传到从库
# scp /server/backup/mysql_bak.2015-11-18.sql.gz 192.168.95.130:/server/backup/
# scp /server/backup/mysql_bak.2015-11-18.sql.gz 192.168.95.131:/server/backup/

  从库1上设置
1）设置server-id值并关闭binlog参数
#log_bin = /data/mysql/data/mysql-bin(可关可开)
server_id = 130

重启数据库：

2）还原从主库备份数据
# cd /server/backup/
# gzip -d mysql_bak.2015-11-18.sql.gz
# mysql -uroot -p < mysql_bak.2015-11-18.sql
检查还原：
# mysql -uroot -p -e 'show databases;'

3）设定从主库同步
mysql> change master to
MASTER_HOST='192.168.95.120',
MASTER_PORT=3306,
MASTER_USER='rep',
MASTER_PASSWORD='123456',
MASTER_LOG_FILE='mysql-bin.000003',
MASTER_LOG_POS=329;

4）启动从库同步开关
mysql> start slave;
检查状态：
mysql> show slave status \G

  从库2上设置
1）设置server-id值并关闭binlog参数
#log_bin = /data/mysql/data/mysql-bin(可关可开)
server_id = 131

重启数据库：

2）还原从主库备份数据
# cd /server/backup/
# gzip -d mysql_bak.2015-11-18.sql.gz
# mysql -uroot -p < mysql_bak.2015-11-18.sql
检查还原：
# mysql -uroot -p -e 'show databases;'

3）设定从主库同步
mysql> change master to
MASTER_HOST='192.168.95.120',
MASTER_PORT=3306,
MASTER_USER='rep',
MASTER_PASSWORD='123456',
MASTER_LOG_FILE='mysql-bin.000003',
MASTER_LOG_POS=329;

4）启动从库同步开关
mysql> start slave;
检查状态：
mysql> show slave status \G

3.基于MySQL一主两从配置，完成MySQL读写分离配置

1、创建数据源

# 添加读写的数据源
/*+ mycat:createDataSource{
"dbType":"mysql",
"idleTimeout":60000,
"initSqls":[],
"initSqlsGetConnection":true,
"instanceType":"READ_WRITE",
"maxCon":1000,
"maxConnectTimeout":3000,
"maxRetryCount":5,
"minCon":1,
"name":"m1",
"password":"123456",
"type":"JDBC",
"url":"jdbc:mysql://127.0.0.1:3307/db1?
useUnicode=true&serverTimezone=UTC&characterEncoding=UTF-8",
"user":"root",
"weight":0
} */;
# 添加读的数据源
/*+ mycat:createDataSource{
"dbType":"mysql",
"idleTimeout":60000,
"initSqls":[],
"initSqlsGetConnection":true,
"instanceType":"READ",
"maxCon":1000,
"maxConnectTimeout":3000,
"maxRetryCount":5,
"minCon":1,
"name":"m1s1",
"password":"123456",
"type":"JDBC",
"url":"jdbc:mysql://127.0.0.1:3308/db1?
useUnicode=true&serverTimezone=UTC&characterEncoding=UTF-8",
"user":"root",
"weight":0
} */;
# 添加读的数据源
/*+ mycat:createDataSource{
"dbType":"mysql",
"idleTimeout":60000,
"initSqls":[],
"initSqlsGetConnection":true,
"instanceType":"READ",
"maxCon":1000,
"maxConnectTimeout":3000,
"maxRetryCount":5,
"minCon":1,
"name":"m1s2",
"password":"123456",
"type":"JDBC",
"url":"jdbc:mysql://127.0.0.1:3309/db1?
useUnicode=true&serverTimezone=UTC&characterEncoding=UTF-8",
"user":"root",
"weight":0
} */;

2、创建集群

/*! mycat:createCluster{
"clusterType":"MASTER_SLAVE",
"heartbeat":{
"heartbeatTimeout":1000,
"maxRetry":3,
"minSwitchTimeInterval":300,
"slaveThreshold":0
},
"masters":[
"m1"
],
"maxCon":2000,
"name":"prototype",
"readBalanceType":"BALANCE_ALL",
"replicas":[
"m1s1"，
“m1s2”
],
"switchType":"SWITCH"
} */;

3、创建逻辑库

CREATE DATABASE db1 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

4、修改逻辑库的数据源

修改conf/schemas/db1.schema.json
vim /data/mycat/conf/schemas/db1.schema.json
在里面添加 “targetName”:“prototype”,

[root@node4 mysqlms]# cat /data/mycat/conf/schemas/db1.schema.json
{
"customTables":{},
"globalTables":{},
"normalProcedures":{},
"normalTables":{},
"schemaName":"db1",
"shardingTables":{},
"targetName":"prototype",
"views":{}
}

5、测试读写分离是否成功(在MyCAT里面测试)
重启MyCAT：

[root@node4 mysqlms]# cd /data/mycat/bin/
[root@node4 bin]# ./mycat restart

1）在MyCAT里面创建一个sys_user表：

CREATE TABLE SYS_USER( ID BIGINT PRIMARY KEY, USERNAME VARCHAR(200) NOT NULL,
ADDRESS VARCHAR(500));

2）通过注释生成物理库和物理表：
如果物理表不存在，在 MyCAT2 能正常启动的情况下，根据当前配置自动创建分片表，全局表和物理
表：
3）查看后端物理库：发现物理库和物理表都生成了。
4）在MyCAT里面向sys_user表添加一条数据：

INSERT INTO SYS_USER(ID,USERNAME,ADDRESS) VALUES(1,"XIAOMING","WUHAN");

5）修改MySQL里面的让数据不一样：(仅用于测试验证)

6）在MyCAT里面查询数据，会发现每次查询的结果不一样：

到此，我们使用MyCAT2主从搭建就完成了。