官方推荐： mysql主从复制要求版本最好一致，至少前两个版本号相同，最主要还是怕版本不一致带来的不兼容问题。如现有版本不一致，推荐低版本作为master，高版本作为slave。
主库ip:44.44.141.141
从库ip:33.33.131.131
一、主库配置
1.修改配置
windows: C:\ProgramData\MySQL\MySQL Server 8.0\my.ini
# 服务器唯一id
server-id=1
# 启动MySQL二进制日志，即数据同步语句，从数据库会一条一条的执行这些语句
# 开启binlog后，会在数据目录（默认）生产host-bin.n（具体binlog信息）文件及host-bin.index索引文件（记录binlog文件列表）。当binlog日志写满(binlog大小max_binlog_size，默认1G),或者数据库重启才会生产新文件，但是也可通过手工进行切换让其重新生成新的文件（flush logs）；另外，如果正使用大的事务，由于一个事务不能横跨两个文件，因此也可能在binlog文件未满的情况下刷新文件
log_bin=master-bin
log_bin_index=master-bin.index
# 制定记录二进制日志的数据库，即需要同步的数据库名
binlog_do_db=demo
# 指定不记录二进制日志的数据库，即不需要复制的数据库名
# binlog-ignore-db=sys
2.登陆mysql-创建复制账号
mysql-7版本
//创建新用户
create user repl;
// repl用户必须具有REPLICATION SLAVE权限，除此之外没有必要添加不必要的权限，密码为mysql。
// 33.33.131.131为从库的IP地址
grant replication slave on . to 'repl'@'33.33.131.131' identified by '123456';
mysql-8版本
CREATE USER 'repl'@'33.33.131.131' IDENTIFIED WITH mysql_native_password BY '123456';
GRANT REPLICATION SLAVE ON . TO 'repl'@'33.33.131.131';
刷新权限
flush privileges;
查看账号信息
use mysql;
select user,host from user;
3.主库配置结束，查看配置信息
show master status \G; #显示master 状态
show processlist \G; #显示进程列表
查看binlog状态，显示当前二进制日志文件状态信息，显示正在写入的二进制文件及当前position
show master status \G;
查看binlog位置
show variables like '%log_bin%';
//查看binlog文件列表,
show binary logs;
4.查看binlog内容
a. mysqlbinlog: /usr/bin/mysqlbinlog mysql-bin.000007
- mysqlbinlog是mysql官方提供的一个binlog查看工具
- 也可使用–read-from-remote-server从远程服务器读取二进制日志
- 还可使用--start-position --stop-position、--start-time= --stop-time精确解析binlog日志
截取位置1190-1352 binlog如下：
# at 1190 //事件的起点
#171223 21:56:26 server id 123 end_log_pos 1190 CRC32 0xf75c94a7 Intvar
SET INSERT_ID=2/!/;
#171223 21:56:26 server id 123 end_log_pos 1352 CRC32 0xefa42fea Query thread_id=4 exec_time=0 error_code=0
SET TIMESTAMP=1514123786/!/; //开始事务的时间起点 (每个at即为一个event)
insert into tb_person set name="name__2", address="beijing", sex="man", other="nothing" //sql语句
/!/;
# at 1352
#171223 21:56:26 server id 123 end_log_pos 1383 CRC32 0x72c565d3 Xid = 5 //执行时间，及位置戳，Xid:事件指示提交的XA事务
b.直命令行解析
SHOW BINLOG EVENTS
[IN 'log_name'] //要查询的binlog文件名
[FROM pos]
[LIMIT [offset,] row_count]
mysql> show binlog events in 'master-bin.000001' from 1470 limit 2\G
*************************** 1. row ***************************
Log_name: master-bin.000001
Pos: 1470
Event_type: Query #事件类型
Server_id: 1
End_log_pos: 1545 #事件结束点，下个事件的开始点
Info: BEGIN*************************** 2. row ***************************
Log_name: master-bin.000001
Pos: 1545
Event_type: Rows_query
Server_id: 1
End_log_pos: 1663
Info: # INSERT INTOdemo.user(name,password,age,sex) VALUES ('ggg', 'ggg', '33', '55')
2 rows in set (0.00 sec)
直命令行解析截图
5.binlog格式
Mysql binlog日志有ROW,Statement,MiXED三种格式；可通过my.cnf配置文件及 ==set global binlog_format='ROW/STATEMENT/MIXED'== 进行修改，命令行 ==show variables like 'binlog_format'== 命令查看binglog格式；。
- Row level: 仅保存记录被修改细节，不记录sql语句上下文相关信息优点：能非常清晰的记录下每行数据的修改细节，不需要记录上下文相关信息，因此不会发生某些特定情况下的procedure、function、及trigger的调用触发无法被正确复制的问题，任何情况都可以被复制，且能加快从库重放日志的效率，保证从库数据的一致性
缺点:由于所有的执行的语句在日志中都将以每行记录的修改细节来记录，因此，可能会产生大量的日志内容，干扰内容也较多；比如一条update语句，如修改多条记录，则binlog中每一条修改都会有记录，这样造成binlog日志量会很大，特别是当执行alter table之类的语句的时候，由于表结构修改，每条记录都发生改变，那么该表每一条记录都会记录到日志中，实际等于重建了表。
tip: - row模式生成的sql编码需要解码，不能用常规的办法去生成，需要加上相应的参数(--base64-output=decode-rows -v)才能显示出sql语句; - 新版本binlog默认为ROW level，且5.6新增了一个参数：binlog_row_image；把binlog_row_image设置为minimal以后，binlog记录的就只是影响的列，大大减少了日志内容 - Statement level: 每一条会修改数据的sql都会记录在binlog中优点：只需要记录执行语句的细节和上下文环境，避免了记录每一行的变化，在一些修改记录较多的情况下相比ROW level能大大减少binlog日志量，节约IO，提高性能；还可以用于实时的还原；同时主从版本可以不一样，从服务器版本可以比主服务器版本高
缺点：为了保证sql语句能在slave上正确执行，必须记录上下文信息，以保证所有语句能在slave得到和在master端执行时候相同的结果；另外，主从复制时，存在部分函数（如sleep）及存储过程在slave上会出现与master结果不一致的情况，而相比Row level记录每一行的变化细节，绝不会发生这种不一致的情况 - Mixedlevel level: 以上两种level的混合使用经过前面的对比，可以发现ROW level和statement level各有优势，如能根据sql语句取舍可能会有更好地性能和效果；Mixed level便是以上两种leve的结合。不过，新版本的MySQL对row level模式也被做了优化，并不是所有的修改都会以row level来记录，像遇到表结构变更的时候就会以statement模式来记录，如果sql语句确实就是update或者delete等修改数据的语句，那么还是会记录所有行的变更；因此，现在一般使用row level即可。
- 选取规则如果是采用 INSERT，UPDATE，DELETE 直接操作表的情况，则日志格式根据 binlog_format 的设定而记录
如果是采用 GRANT，REVOKE，SET PASSWORD 等管理语句来做的话，那么无论如何都采用statement模式记录
6.binlog显示具体sql
如果binlog不显示具体的sql，仅仅显示看不懂的如上图endposition428,755这种的
需要开启一个变量 针对当binlog_format=row格式 下的设置
show variables like 'binlog_rows_query_log_events';
set binlog_rows_query_log_events=1;#ON
set binlog_rows_query_log_events=1;#OFF
开启后，会显示出多一行内容Rows_query ，显示出具体的sql，如上图endposition1663
二、从库配置
1.修改配置
windows: C:\ProgramData\MySQL\MySQL Server 8.0\my.ini
# 服务器唯一id
server-id=2
# slave无需必要开启二进制日志，双向主从可以配置
# log-bin=slave-bin
# log-bin-index=slave-bin.index
//打开Mysql中继日志，日志格式为二进制
log_slave_updates=1
relay-log=slave-relay-bin
relay-log-index=slave-relay-bin.index
# 设置只读权限
# read_only=1
# 如果salve库名称与master库名相同，使用本配置
replicate-do-db=demo
# 如果master库名[demo]与salve库名[demo01]不同，使用以下配置[需要做映射]
# replicate-rewrite-db = demo[主库名] -> demo01[从库名]
# 如果不是要全部同步[默认全部同步]，则指定需要同步的表
# replicate-wild-do-table=demo01.t_order
# replicate-wild-do-table=demo01.t_order_item
2.登陆mysql-关联主库
# 'master-bin.000001'155 是在主库中输入show master status得到的File和Position（从这个日志文件的第155个位置开始复制备份）
change master to master_host='44.44.141.141',master_port=3306,master_user='repl',master_password='123456', master_log_file='master-bin.000001',master_log_pos=155;
开始复制线程
start slave;
启动可能报错：
ERROR 3021 (HY000): This operation cannot be performed with a running slave io thread;run STOP SLAVE IO_THREAD FOR CHANNEL '' first.
解决办法
在设置同步关系时，没有先停止slaves线程。
解决办法为在设置同步关系之前，先执行下“stop slave;”，以停止slave线程。
然后启动线程"start slave;"
3.从库配置结束，查看配置信息
show slave status \G ; #显示slave 状态
配置解析
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: ---------------------------- slave io状态，表示还未启动
Master_Host: 30.45.148.145
Master_User: replz
Master_Port: 3306
Connect_Retry: 60 ------------------------- master宕机或连接丢失从服务器线程重新尝试连接主服务器之前睡眠时间
Master_Log_File: master-bin.000001 ------------ 当前读取master binlog文件
Read_Master_Log_Pos: 698 ------------------------- slave读取master binlog文件位置
Relay_Log_File: relay-bin.000001 ------------ 回放binlog
Relay_Log_Pos: 4 -------------------------- 回放relay log位置
Relay_Master_Log_File: mysql-bin.000003 ------------ 回放log对应maser binlog文件
Slave_IO_Running: No
Slave_SQL_Running: No
Exec_Master_Log_Pos: 0 --------------------------- 相对于master从库的sql线程执行到的位置
Seconds_Behind_Master: NULL
Slave_IO_State, Slave_IO_Running, 和Slave_SQL_Running为NO说明slave还没有开始复制过
三、配置完成验证
1.登陆主库mysql-查看进程信息
show processlist \G; #显示进程列表
可以看到从库信息
2.主库插入数据，从库查看是否存在
四、配置对应文件结构
主库数据-对应其my.ini配置
从库-对应其my.ini配置
五、主从配置问题汇总
show slave status;
判断同步是否报错
1、查看Slave_IO_Running与Slave_SQL_Running状态，Slave_IO_Running=YES && Slave_SQL_Running=NO说明slave复制出错了
2、查看Last_Error以及Last_SQL_Error，获取到具体的报错信息，获取到具体的binlog以及position
3、通过binlog及posttion到主库上，解析对应的binlog，找到报错的sql。
4、解决报错，参考：https://blog.51cto.com/hujiangtao/1932166
如何正确判断SLAVE的延迟情况，判定slave是否追上master的binlog：
1、首先看 Relay_Master_Log_File 和 Maser_Log_File 是否有差异；
2、如果Relay_Master_Log_File 和 Master_Log_File 是一样的话，再来看Exec_Master_Log_Pos 和 Read_Master_Log_Pos 的差异，对比SQL线程比IO线程慢了多少个binlog事件；
3、如果Relay_Master_Log_File 和 Master_Log_File 不一样，那说明延迟可能较大，需要从MASTER上取得binlog status，判断当前的binlog和MASTER上的差距；
4、如果以上都不能发现问题，可使用pt_heartbeat工具来监控主备复制的延迟。
demo
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event -- 等待master新的event
Master_Host: 30.45.148.145
Master_User: replz
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.000001
Read_Master_Log_Pos: 6985 -------------- 6985 等于Exec_Master_Log_Pos，已完成回放
Relay_Log_File: slave-relay-bin.000002 ||
Relay_Log_Pos: 627 ||
Relay_Master_Log_File: master-bin.000001 ||
Slave_IO_Running: Yes ||
Slave_SQL_Running: No ||代表同步报错，查看Last_Error和Last_SQL_Error查看具体信息
Replicate_Do_DB: demo
Last_Errno: 1032
Last_Error: Could not execute Update_rows event on table demo.user; Can't find record in 'user', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log master-bin.000001, end_log_pos 755
Skip_Counter: 0 ------------解决部分报错，可以通过跳过语句来处理，这里会对应改变
Exec_Master_Log_Pos: 459 ---------------459 等于slave读取master binlog位置，已完成回放，小于slave读取master binlog位置，未完成回放
Relay_Log_Space: 8291
Last_SQL_Errno: 1032
Last_SQL_Error: Could not execute Update_rows event on table demo.user; Can't find record in 'user', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log master-bin.000001, end_log_pos 755
Master_Server_Id: 1 --------------- master信息
Master_UUID: 103c1ca5-8285-11e9-870c-186024b2daec
Master_Info_File: mysql.slave_master_info
Seconds_Behind_Master: NULL
Master_Retry_Count: 86400 --------------- master重试次数
Last_SQL_Error_Timestamp: 200508 18:06:43 ---------- 最后一次同步报错时间
六、主从同步
1.同步方式
mysql数据同步方式：1.GTID（全局事务标示符） 2.bin-log
a.Master将数据改变记录到二进制日志(binary log)中
b.Slave上面的IO进程连接上Master，并请求从指定日志文件的指定位置（或者从最开始的日志）之后的日志内容
c.Master接收到来自Slave的IO进程的请求后，负责复制的IO进程会根据请求信息读取日志指定位置之后的日志信息，返回给Slave的IO进程。
返回信息中除了日志所包含的信息之外，还包括本次返回的信息已经到Master端的bin-log文件的名称以及bin-log的位置
d.Slave的IO进程接收到信息后，将接收到的日志内容依次添加到Slave端的relay-log文件的最末端，并将读取到的Master端的 bin-log的
文件名和位置记录到master-info文件中，以便在下一次读取的时候能够清楚的告诉Master从某个bin-log的哪个位置开始往后的日志内容
e.Slave的Sql进程检测到relay-log中新增加了内容后，会马上解析relay-log的内容成为在Master端真实执行时候的那些可执行的内容，并在自身执行
2.延时问题
主从复制方式：同步复制，异步复制（默认），半同步复制
同步复制：性能问题（金融场景）
master 1.insert(execute) 2.写入bin-log 4.commit
slave 串行3.relay-log 执行完毕落入db
异步复制：性能高，会丢数据
master 1.insert(execute) 2.写入bin-log 并行3.commit
slave 并行3.relay-log 执行完毕落入db
半同步复制
master 1.insert(execute) 2.写入bin-log 5.commit
slave1 3.relay-log 并行4.ack主库 并行4.执行完毕落入db
slave2 3.relay-log 执行完毕落入db
问题：ack不及时(10s)，会降级为异步
市面服务器配置
RDS高可用(一主一备)+主从配置(一主一从)
网友评论