MySQL基于binlog实现主从同步

MySQL内建的复制功能是构建大型，高性能应用程序的基础 通过将MySQL的某一台主机（master）的数据复制到其他主机（slaves）上，并重新执行一遍来执行 复制过程中一台服务器充当主服务器，而其他一个或多个其他服务器充当从服务器

1、MySQL支持的复制类型

基于语句（statement）的复制 在主服务器上执行SQL语句，在从服务器上执行同样的语句。MySQL默认采用基于语句的复制，效率比较高。 基于行（row）的复制 把改变的内容复制过去，而不是把命令在从服务器上执行一遍。从MySQL 5.0开始支持。 混合型（mixed）的复制 默认采用基于语句的复制，一旦发现基于语句的无法精确复制时，就会采用基于行的复制。

2、为什么要做主从复制

灾备 数据分布 负载平衡 读写分离 提高并发能力

3、主从复制原理

主要基于MySQL二进制日志 主要包括三个线程（2个I/O线程，1个SQL线程）

 1、MySQL将数据变化记录到二进制日志中；
 2、Slave将MySQL的二进制日志拷贝到Slave的中继日志中；
 3、Slave将中继日志中的事件在做一次，将数据变化，反应到自身（Slave）的数据库
 ​
 详细步骤：
 1、从库通过手工执行change master to 语句连接主库，提供了连接的用户一切条件（user 、password、port、ip），并且让从库知道，二进制日志的起点位置（file名 position 号）； start slave
 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

4、MySQL复制常用的拓扑结构

主从类型（Master-Slave） 主主类型（Master-Master） 级联类型（Master-Slave-Slave）

基于binlog的主从同步

主库配置

 配置文件：
 [root@localhost ~]# tail -1 /etc/my.cnf
 server_id=1
 [root@localhost ~]# systemctl restart mysqld
 ​
 备份：
 [root@localhost ~]# mysqldump --opt -B db1 it school > db.sql
 ​
 授权用户：
 mysql> create user rep@'172.16.%.%' identified with mysql_native_password by '123456';
 mysql> grant replication slave on *.* to rep@'172.16.%.%';
 mysql>flush privileges;
 ​
 mysql> show master status;
 +---------------+----------+--------------+------------------+--------------------------------------------------------------------------------------+
 | File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                                                                    |
 +---------------+----------+--------------+------------------+--------------------------------------------------------------------------------------+
 | binlog.000015 |      756 |              |                  | 2db179b1-cf96-11ee-8b00-2e6ff2d90c84:1-14,
 d09e219c-cec9-11ee-baf1-2e6ff2d90c84:1-46 |
 +---------------+----------+--------------+------------------+--------------------------------------------------------------------------------------+
 1 row in set (0.00 sec)

从库配置：

 配置文件：
 [root@localhost ~]# tail -1 /etc/my.cnf
 server_id=2
 [root@localhost ~]# systemctl restart mysqld
 ​
 还原主库备份：
 # scp db.sql 172.16.100.22:/root/
 ​
 mysql -uroot -pMySQL@123 < db.sql

 mysql> change master to
     -> master_host='172.16.100.21',
     -> master_user='rep',
     -> master_password='123456',
     -> master_log_file='binlog.000015',
     -> master_log_pos=756, #主的Position
     -> get_master_public_key=1;
 Query OK, 0 rows affected, 9 warnings (0.00 sec)
 ​
 mysql> start slave;
 Query OK, 0 rows affected, 1 warning (0.01 sec)

可选配置

 #[可选] 0（默认）表示读写（主机），1表示只读（从机）
 read-only=0
 #设置日志文件保留的时长，单位是秒
 binlog_expire_logs_seconds=6000
 #控制单个二进制日志大小。此参数的最大和默认值是1GB
 max_binlog_size=200M
 #[可选]设置不要复制的数据库
 binlog-ignore-db=test
 #[可选]设置需要复制的数据库,默认全部记录。
 binlog-do-db=需要复制的主数据库名字
 #[可选]设置binlog格式
 binlog_format=STATEMENT

报错解决：

 mysql> create user rep@'172.16.%.%' identified with mysql_native_password by '123456';
 Query OK, 0 rows affected (0.04 sec)
 ​
 mysql>  grant replication slave on *.* to rep@'172.16.%.%';
 ERROR 1410 (42000): You are not allowed to create a user with GRANT

原因：mysql.user表中的host字段为localhost，需要修改为%

 mysql>update user set host = '%' where user = 'root';