MYSQL 主从复制

MYSQL 主从复制

从上图来看，复制分成三步:
1.Master主库在事务提交时，会把数据变更记录在二进制日志文件 Binlog 中
2.从库读取主库的二进制日志文件 Binlog，写入到从库的中继日志 Relay Log
3.slave重做中继日志中的事件，将改变反映它自己的数据。

1.主库配置

1.修改配置文件 my.cnf

log-bin = /usr/local/mysql-5.7.32-3306/logs/mybinlog
#mysql服务ID，保证整个集群环境中唯一，取值范围: 1 - 2的32次方-1，默认为1
server-id=1
#是否只读,1 代表只读,0 代表读写
read-only=0
#忽略的数据,指不需要同步的数据库
#binlog-ignore-db=数据库名
#指定同步的数据库
#binlog-do-db=数据库名
#复制格式
binlog_format=STATEMENT

binlog 有三种格式
Statement（Statement-Based Replication,SBR）：每一条会修改数据的 SQL 都会记录在 binlog 中。
Row（Row-Based Replication,RBR）：不记录 SQL 语句上下文信息，仅保存哪条记录被修改。
Mixed（Mixed-Based Replication,MBR）：Statement 和 Row 的混合体。
Statement
Statement 模式只记录执行的 SQL，不需要记录每一行数据的变化，因此极大的减少了 binlog 的日志量，避免了大量的 IO 操作，提升了系统的性能。（比如update user set name=“张三” where id >1 and id <10000,假设被修改的数据有2000条，那么Row的日志量就是2000条，而Statement只是这条sql语句一条日志而已，所以Statement的日志量相对Row会少很多）
但是，正是由于 Statement 模式只记录 SQL，而如果一些 SQL 中 包含了函数，那么可能会出现执行结果不一致的情况。比如说 uuid() 函数，每次执行的时候都会生成一个随机字符串，在 master 中记录了 uuid，当同步到 slave 之后，再次执行，就得到另外一个结果了。
所以使用 Statement 格式会出现一些数据一致性问题。
Row
从 MySQL5.1.5 版本开始，binlog 引入了 Row 格式，Row 格式不记录 SQL 语句上下文相关信息，仅仅只需要记录某一条记录被修改成什么样子了。
Row 格式的日志内容会非常清楚地记录下每一行数据修改的细节，这样就不会出现 Statement 中存在的那种数据无法被正常复制的情况。
不过 Row 格式也有一个很大的问题，那就是日志量太大了，特别是批量 update、整表 delete、alter 表等操作，由于要记录每一行数据的变化，此时会产生大量的日志，大量的日志也会带来 IO 性能问题。
此外，新版的MySQL中对row级别也做了一些优化，当表结构发生变化的时候，会记录语句而不是逐行记录。
Mixed
从 MySQL5.1.8 版开始，MySQL 又推出了 Mixed 格式，这种格式实际上就是 Statement 与 Row 的结合。
在 Mixed 模式下，系统会自动判断 该 用 Statement 还是 Row：一般的语句修改使用 Statement 格式保存 binlog；对于一些 Statement 无法准确完成主从复制的操作，则采用 Row 格式保存 binlog。
Mixed 模式中，MySQL 会根据执行的每一条具体的 SQL 语句来区别对待记录的日志格式，也就是在 Statement 和 Row 之间选择一种。

2.重启MYSQL
3.登录mysql，创建远程连接的账号，并授予主从复制权限

CREATE USER 'liu'@'%' IDENTIFIED BY '密码'; //root创建外部访问权限
ALTER USER 'liu'@'%' IDENTIFIED WITH mysql_native_password BY '密码'; 
GRANT REPLICATION SLAVE ON *.* TO 'liu'@'%';//授权
FLUSH PRIVILEGES;

4.通过指令，查看二进制日志坐标

show master status;

字段含义说明:
file:从哪个日志文件开始推送日志文件
position:从哪个位置开始推送日志
binlog_ignore_db:指定不需要同步的数据库

2.从库配置

1.修改配置文件 my.cnf

relay_log=/opt/mysql8/log/relay_log
#mysql服务ID，保证整个集群环境中唯一，取值范围: 1 - 2的32次方-1，默认为1
server-id=2
#是否只读,1 代表只读,0 代表读写
read-only=1

2.重启MYSQL
3.登录mysql，设置主库配置

CHANGE REPLCATON SOURCE TO SOURCE_HOST='', SOURCE_USER='', SOURCE_PASSWORD='', SOURCE_LOG_FILE='', SOURCE_LOG_POS=''

上述是8.0.23中的语法。如果mysgl是 8.0.23之前的版本，执行如下SQL:

change master to  master_host='',master_port=3307,
master_user='',master_password='',
master_log_file='',master_log_pos=;

master_log_file：主库配置show master status;查询出file的值
master_log_pos：主库配置show master status;查询出position的值
4.开启同步操作

#8.0.22之后
start replica ;
#8.0.22之前
start slave ;

5.查看主从同步状态

show replica status \G; #8.0.22之后
show slave status \G; #8.0.22之前

6.重置从库

STOP SLAVE;
RESET MASTER;