14-主从复制

基本原理

MySQL 的主从复制就是基于二进制日志完成的。

复制是指将主数据库的 DDL 和 DML 操作通过 [二进制日志] 传到从库服务器中，然后在从库上对这些日志重新执行（也叫重做），从而使得从库和主库的数据保持同步。

MySQL 支持一台主库同时向多台从库进行复制， 从库同时也可以作为其他从服务器的主库，实现链状复制。

MySQL 复制过程分成 3 步（异步且串行化）：

1. Master 主库在事务提交时，会把数据变更作为时间 Events 记录在二进制日志文件 Binlog 中。这些记录过程叫做 "二进制日志事件(Binary log events)"。
2. Slave 将 Master 的 Binary log events 拷贝到它的中继日志(Relay Log)；也可以理解为是主库推送二进制日志文件 Binlog 中的日志事件到从库的中继日志 Relay Log。
3. Slave 重做中继日志中的事件，将改变应用到自己的数据库中。

复制优势

MySQL 复制的有点主要包含以下 3 个方面：

• 主库出现问题，可以快速切换到从库提供服务。
• 可以在从库上执行查询操作，从主库中更新，实现读写分离，降低主库的访问压力。
• 可以在从库中执行备份，以避免备份期间影响主库的服务。

复制的最大问题：延时。

基本原则

1. 每个 Slave 只有一个 Master
2. 每个 Slave 只能有一个唯一的服务器 ID
3. 每个 Master 可以有多个 Slave

一主一从常见配置

• MySQL 版本一致且后台以服务运行。
• 主从都配置在 [mysqld] 结点下，都是小写。
• 修改前，记得将配置文件备份。
• 防火墙都关闭掉。

主机操作

修改 my.ini

# 服务ID：保证整个集群环境中唯一
server-id=1
# 启用二进制日志，配置binlog日志存储路径和文件名
log-bin=U:/mysql/mysqlbin
# 错误日志,默认已经开启
# log-err
# 是否只读：1代表只读,0代表读写
read-only=0
# 忽略的数据, 指不需要同步(复制)的数据库
binlog-ignore-db=mysql
# 指定同步(复制)的数据库，写一个不存在的数据库名称 → 从机从接入点开始复制
binlog-do-db=mydb_1101
# 设置 logbin 格式，就用默认的 MIXED
# binlog_format=STATEMENT

MySQL 复制主要有 3 种方式：基于 SQL 语句的复制(statement-based replication，SBR)、基于行的复制(row-based replication，RBR)、混合模式复制(mixed-based replication，MBR)。对应的，binlog 的格式也有 3 种：STATEMENT，ROW，MIXED。

• STATEMENT 模式 (SBR)：每一条会修改数据的 SQL 语句会记录到 binlog 中。优点是并不需要记录每一条 SQL 语句和每一行的数据变化，减少了 binlog 日志量，节约 IO，提高性能。缺点是在某些情况下会导致 master-slave 中的数据不一致(如：sleep()、last_insert_id() 以及 user-defined functions(udf) 等会出现问题)
• ROW 模式(RBR)：不记录每条 SQL 语句的上下文信息，仅需记录哪条数据被修改了，修改成什么样了。而且不会出现某些特定情况下的存储过程、function、trigger 的调用和触发无法被正确复制的问题。缺点是会产生大量的日志，尤其是 ALTER TABLE 的时候会让日志暴涨。
• MIXED 模式(MBR)：以上两种模式的混合使用，一般的复制使用 STATEMENT 模式保存 binlog，对于 STATEMENT 模式无法复制的操作使用 ROW 模式保存 binlog，MySQL 会根据执行的 SQL 语句选择日志保存方式。

建立帐户并授权 slave

# 授权给Slave
GRANT REPLICATION SLAVE ON *.* TO 'salve1101'@'192.168.206.129' IDENTIFIED BY '1101';

# 刷新权限列表
FLUSH PRIVILEGES;

# 查看Master状态
SHOW MASTER STATUS;

• File：从哪个日志文件开始推送日志文件
• Position：从哪个位置开始推送日志
• Binlog_Ignore_DB：指定不需要同步的数据库

执行完此步骤后不要再操作主服务器 MySQL，防止主服务器状态值变化。

关闭防火墙+重启服务

从机操作

修改 my.cnf

vim /etc/my.cnf

启动从机复制功能

# 在从机上配置需要复制的主机：指定当前从库对应的主库的IP地址
# 用户名，密码，从哪个日志文件开始的那个位置开始同步推送日志
CHANGE MASTER TO
    MASTER_HOST='192.168.206.1',
    MASTER_USER='salve1101',
    MASTER_PASSWORD='1101',
    MASTER_LOG_FILE='mysqlbin.000001',
    MASTER_LOG_POS=107;

START SLAVE; # 启用从服务器复制功能

SHOW SLAVE STATUSG; # 查看从机状态

关闭防火墙+重启服务

systemctl stop firewalld.service    # 停止firewall
systemctl restart mysqld.service    # 重启mysql

其他操作

• 停止从服务器复制功能：STOP SLAVE;
• 重新配置主从

  STOP SLAVE;
  RESET MASTER;
  

验证同步操作

主从关系配置完毕且都 START 之后，再建库建表插数据。