MySql 主库/从库原理及实战

主/从原理

原理图

从库生成两个线程，一个I/O线程，一个SQL线程；
I/O线程去请求主库 的binlog，并将得到的binlog日志写到relay log（中继日志） 文件中；
主库会生成一个 log dump 线程，用来给从库 I/O线程传binlog；
SQL 线程，会读取relay log文件中的日志，并解析成具体操作，来实现主从的操作一致，而最终数据一致；

用途及必要条件

• mysql主从复制用途

  1. 实时灾备，用于故障切换
  2. 读写分离，提供查询服务
  3. 备份，避免影响业务

• 主从部署必要条件：

  1. 主库开启binlog日志（设置log-bin参数）
  2. 主从server-id不同
  3. 从库服务器能连通主库

实践操作

1、设置主/从服务器配置

#主库
#此配置文件可能mysql不同版本,名称不尽相同
vim /etc/mysql/conf.d/mysql.cnf 
#写入以下内容
[mysqld]
log-bin=mysql-bin  #启用二进制日志
server-id=1        #服务器唯一ID 

#从库
#此配置文件可能mysql不同版本,名称不尽相同
vim /etc/mysql/conf.d/mysql.cnf 
#写入以下内容
[mysqld]
log-bin=mysql-bin  #启用二进制日志
server-id=2        #服务器唯一ID   

#重启主、从两个MySql，因操作系统不尽相同，重启命令视具体版本
systemctl restart mysql

2、创建主/从服务器容器
此处为尽量排除版本不一致导致各种未知问题，所以采用docker构建部署MySql

#主库
docker run --name=mysql -p 3307:3306  -v /home/mysql/mysql:/var/lib/mysql -e REPLICATION_MASTER=true  -e MYSQL_ROOT_PASSWORD=123456 -d mysql:5.6

#从库
docker run --name=mysql-backup  --link mysql:mysql -p 3308:3306 -v /home/mysql_backup/mysql:/var/lib/mysql -e REPLICATION_SLAVE=true -e MYSQL_ROOT_PASSWORD=123456  -d mysql:5.6

3、登录主服务器的mysql，查询master的状态

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |     2781 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+

#master
#主库创建用户
SET sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));
GRANT REPLICATION SLAVE ON *.* to 'slaveuser'@'%' identified by '123456';

#slave
#登录从服务器的mysql，设置与主服务器相关的配置参数
SET sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));
change master to master_host='172.17.0.13',master_port=3306,master_user='slaveuser',master_password='123456',master_log_file='mysql-bin.000001',master_log_pos=2781;
#master_host为主服务器的地址
#master_user是在主库创建的用户
#master_log_pos是主库show master status，查询出的Position

#启动服务
start slave;

#查看服务状态
show slave status;

Slave_IO_State 提示：Waiting for master to send event 就是成功了，可在主库添加或修改数据，在到从库查看是否同步
Connecting to master 多半是网络连接不通，可查看日志文件确认
到此你已经可以实现主从同步了
温馨提示： 以上弱密码需自行修改

拓展话题

• 对于需要保证master-slave主从同步的salve库不被修改，可设置salve库为只读状态，进入slave库执行的命令为

mysql> set global read_only=1;

• 将salve库从只读状态变为读写状态，需要执行的命令是：

mysql> set global read_only=0;

• 数据库读写状态设置原理
  对于数据库读写状态，主要靠 “read_only”全局参数来设定；默认情况下，数据库是用于读写操作的，所以read_only参数也是0或false状态，这时候不论是本地用户还是远程访问数据库的用户，都可以进行读写操作；如需设置为只读状态，将该read_only参数设置为1或TRUE状态，但设置 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权限的root等用户只能在本地登录，不会发生数据变化，其他远程连接的应用用户只按需分配为select,insert,update,delete等权限，保证没有super权限，则只需要将salve设定“read_only=1”模式，即可保证主从同步，又可以实现从库只读。