mysql5.7主从复制、读写分离的配置/启动/关闭

环境

主master : 192.168.0.10
从slave : 192.168.0.11
master、slave mysql版本必须一致

1.授权给从数据库服务器

-- #创建从库同步账号
GRANT REPLICATION SLAVE ON *.* to 'test'@'192.168.0.11' IDENTIFIED BY 'test123456';
#刷新权限
FLUSH PRIVILEGES;
#取消授权
#revoke REPLICATION SLAVE ON *.* from 'test'@'192.168.0.11'

2.修改主库配置文件，开启binlog，并设置server-id，每次修改配置文件后都要重启mysql服务才会生效

linux下查看mysql的配置文件，可以通过以下方法

首先先看看你的mysql在哪，通过which命令

which mysql

显示出目录比如我的是这个 /usr/bin/mysql

针对这个目录通过命令查看配置文件

/usr/bin/mysql --verbose --help | grep -A 1 'Default options'

然后在下面会出现一些信息

这个信息的意思是：
服务器首先读取的是/etc/mysql/my.cnf文件，如果前一个文件不存在则继续读/etc/my.cnf文件，还不存在便会去读~/.my.cnf文件

修改my.cnf配置文件，在[mysqld]下面添加下面内容

[mysqld]
#指定了安装 MySQL 的安装路径，填写全路径可以解决相对路径所造成的问题。
basedir=/www/server/mysql/　
#指定了 MySQL 的数据库文件放在什么路径下。数据库文件即我们常说的 MySQL data 文件　　
datadir=/www/server/mysql/data
#主机读写都可以
read-only=0
#同步的日志路径及文件名，一定注意这个目录要是mysql有权限写入的
log-bin=/www/server/mysql/binlog
#错误日志
log-err=/www/server/mysql/binerr
#数据库服务器的唯一标识,设置为当前ip的最后一个段的数字,这样不会乱,在同一个复制组下的所有实例的server_id都必须是唯一的，而且取值必须是正整数，取值范围是1~(232)−1
server-id=10 
# 不同步哪些数据库  
binlog-ignore-db = mysql  
binlog-ignore-db = information_schema  
  
# 只同步哪些数据库，除此之外，其他不同步  
binlog-do-db = test

可以使用MySQL命令"show variables like ‘%log_bin%’;"查看配置

show variables like '%log_bin%'

修改配置文件后，重启服务：

service mysqld restart

查看主服务器当前二进制日志名和偏移量，这个操作的目的是为了在从数据库启动后，从这个点开始进行数据的恢复

主数据库进行锁表操作，不让数据再进行写入动作

FLUSH TABLES WITH READ LOCK;

查看master数据库状态

show master status;

记录下 File 及 Position 的值。

将主服务器的数据文件（整个/www/server/mysql/data目录）复制到从服务器，建议通过tar归档压缩后再传到从服务器解压。

配置从库

配置文件my.cnf 在[mysqld]添加下面配置：

[mysqld]
#数据库服务器的唯一标识,设置为当前ip的最后一个段的数字,这样不会乱,在同一个复制组下的所有实例的server_id都必须是唯一的，而且取值必须是正整数，取值范围是1~(232)−1
server-id=11 
#二进制日志 可选
log-bin=/www/server/mysql/binlog

#只读模式 实现读写分离
read_only=1

从库创建只读用户

给test数据库创建只读用户

GRANT SELECT ON test.* TO 'test_read_only'@'%' IDENTIFIED BY "test123456";
FLUSH PRIVILEGES;

重启从库

service mysqld restart

执行如下sql

CHANGE MASTER TO MASTER_HOST='192.168.0.10', 
MASTER_USER='test', 
MASTER_PASSWORD='test123456', 
-- #上面记录的master的File值
MASTER_LOG_FILE='binlog.000001', 
-- #上面记录的master的Position值
MASTER_LOG_POS=154;

开启从库模式

如果启动失败，一定要重新查看master show master status 修改对应的MASTER_LOG_POS值然后再start slave

start slave;
-- #关闭从库模式
-- stop slave;

查看从库状态

show slave status\G;

当Slave_IO_Running和Slave_SQL_Running都为YES的时候就表示主从同步设置成功了。

取消master数据库锁定

UNLOCK TABLES;

读写分离账号

test_read_write 主库增删改查账号
test_read_only 从库只读账号

MySQL复制的原理大致总结如下：

1. 在Slave 服务器上执行sart slave命令开启主从复制开关，开始进行主从复制。
2. 此时，Slave服务器的IO线程会通过在master上已经授权的复制用户权限请求连接master服务器，并请求从执行binlog日志文件的指定位置（日志文件名和位置就是在配置主从复制服务时执行change
   master命令指定的）之后开始发送binlog日志内容
3. Master服务器接收到来自Slave服务器的IO线程的请求后，二进制转储IO线程会根据Slave服务器的IO线程请求的信息分批读取指定binlog日志文件指定位置之后的binlog日志信息，然后返回给Slave端的IO线程。返回的信息中除了binlog日志内容外，还有在master服务器端记录的新的binlog文件名称，以及在新的binlog中的下一个指定更新位置。
4. 当Slave服务器的IO线程获取到Master服务器上IO线程发送的日志内容、日志文件及位置点后，会将binlog日志内容依次写到Slave端自身的Relay
   Log（即中继日志）文件（MySQL-relay-bin.xxx）的最末端，并将新的binlog文件名和位置记录到master-info文件中，以便下一次读取master端新binlog日志时能告诉Master服务器从新binlog日志的指定文件及位置开始读取新的binlog日志内容
5. Slave服务器端的SQL线程会实时检测本地Relay Log 中IO线程新增的日志内容，然后及时把Relay LOG
   文件中的内容解析成sql语句，并在自身Slave服务器上按解析SQL语句的位置顺序执行应用这样sql语句，并在relay-log.info中记录当前应用中继日志的文件名和位置点