mysql5.7主从复制、只读模式_mysql从库只读

1、主库操作

主库的my.cnf配置
[client]
default-character-set=utf8mb4
[mysql]
default-character-set=utf8mb4
[mysqld]
character-set-server=utf8mb4 
collation-server=utf8mb4_general_ci
sql_mode=''
 
server-id=100
log-bin=mysql-bin
# 只同步哪些数据库，除此之外，其他不同步
binlog-do-db = db
#同步的类型（推荐MIXED 经过验证这是目前最优选择）
binlog_format=MIXED
 
 
 
 
#创建同步数据用户
CREATE USER 'slave'@'%' IDENTIFIED BY '123456';
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'slave'@'%';
#修改密码语句（备用）
ALTER USER 'slave'@'%' IDENTIFIED WITH mysql_native_password BY '123456';
 
 
#主库设置为只读状态
show global variables like "%read_only%";
#给所有的表加读锁的命令
flush tables with read lock;
#设置为只读状态
set global read_only=1;
show global variables like "%read_only%";
 
 
#查看主库状态
show master status;
 
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.001005 |   012    | db           |                  |                   |
+------------------+----------+--------------+------------------+-------------------+

2、从库操作

#从库my.cnf
[client]
default-character-set=utf8mb4
[mysql]
default-character-set=utf8mb4
[mysqld]
character-set-server=utf8mb4 
collation-server=utf8mb4_general_ci
sql_mode=''
 
server-id=102
 
 
#导出主库数据
docker exec mysql mysqldump -h 172.17.xx.xx -P 3306 -uroot -p123456 --quick --events --flush-logs  --max_allowed_packet=4194300 --net_buffer_length=16384 --single-transaction -R --triggers --databases db > db.sql
 
 
#导入从库 mysql -u root -p之后 直接source导入(存放在docker logs映射文件中)
source /logs/db.sql
 
 
#配置主库关联 其中MASTER_LOG_FILE 和MASTER_LOG_POS 对应主库参数
stop slave;
reset slave;
#复制主机的命令
CHANGE MASTER TO MASTER_HOST='172.17.xx.xx',
MASTER_USER='slave',
MASTER_PASSWORD='123456',
master_port=3306,
MASTER_LOG_FILE='mysql-bin.000105',MASTER_LOG_POS=012;
 
 
#给所有的表加读锁的命令
flush tables with read lock;
#设置为只读状态
set global read_only=1;
 
#启动从库复制功能
start slave;
 
 
 
show slave status \G;

3、主库解除锁定

#主库解除只读状态
unlock tables;
set global read_only=0;

到这里就结束了，应用服务可以正常启动了 

命令解析

对于数据库读写状态，主要靠 “read_only”全局参数来设定；但设置 read_only=1 状态有两个需要注意的地方：
      1.read_only=1只读模式，不会影响slave同步复制的功能，所以在MySQL slave库中设定了read_only=1后，通过 show slave status\G 命令查看salve状态，可以看到salve仍然会读取master上的日志，并且在slave库中应用日志，保证主从数据库同步一致；
      2.read_only=1只读模式，可以限定普通用户进行数据修改的操作，但不会限定具有super权限的用户的数据修改操作；在MySQL中设置read_only=1后，普通用户不能进行insert、update、delete等会产生数据变化的DML操作，但具有super权限的用户，例如root登录到数据库，还是可以进行数据变化的DML操作；为了确保所有用户，包括具有super权限的用户也不能进行读写操作，就需要执行给所有的表加读锁的命令 “flush tables with read lock;”