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;