码字不易，转载请附原链，搬砖繁忙回复不及时见谅，技术交流请加QQ群：909211071
主服务器上面的任何修改都会通过自己的 I/O tread(I/O 线程)保存在二进制日志 Binary log 里面。
从服务器上面也启动一个 I/O thread，通过配置好的用户名和密码, 连接到主服务器上面请求读取二进制日志，然后把读取到的二进制日志写到本地的一个Relay log（中继日志）里面。
从服务器上面同时开启一个 SQL thread 定时检查 Relay log(这个文件也是二进制的)，如果发现有更新立即把更新的内容在本机的数据库上面执行一遍。
主库将自己的更改根据同步格式记录到binlog中，由从库的 IO 线程连接到主库读取binlog。
将从库接收到的 binlog 写到本地的relay log中，从库通过SQL线程定时检查relay log，发现更改后则进行重放。
在服务器上开启二进制日志时，同时会生成一个和二进制日志同名的但以.index结尾的文件，这个文件的每一行包含了二进制文件的文件名。MySQL依赖于这个文件来识别二进制日志文件。
中继日志索引文件，和mysql-bin.index作用类似。
保存从库连接到主库所需要的信息，以文本的方式记录复制用户的密码，所以要注意此文件的权限控制。
保存了当前从库复制的二进制日志和中继日志坐标，在从库重启后用于获知从哪个位置开始复制，删除后可能会导致重放执行过的语句。
在基于语句的复制模式下，主库会记录造成数据修改的SQL，当从库读取并重放这些SQL时，就是把这些SQL再执行一遍，这种方式既有优点也有缺点。
优点：
基于SQL执行复制，定位问题简单。大部分情况下节约IO成本，比如update全表时仅需要传递一条SQL缺点：
从库更新一个不存在的记录时不会失败，容易错过同步问题。如果使用触发器或存储过程，容易遇到未知问题。优点：
从库更新一个不存在的记录时会报错并停止复制。能够清晰地知道服务器上发生了哪些更改，更有利于某些数据的恢复。缺点：
由于要记录每一行的更改，所以IO大部分情况下相比基于语句的复制要大。无法判断执行了哪些SQL。结论：有大范围的更新（一般线上很少），人为的去执行，在执行前，把当前session设置成STATEMENT，其余的都用ROW。
分析过程参考：https://www.cnblogs.com/zhoujinyi/archive/2013/01/15/2836131.html
1、下载linux通用源码：wget https://downloads.mysql.com/archives/get/p/23/file/mysql-8.0.18.tar.gz 2、解压压缩包：tar -zxvf mysql-8.0.18.tar.gz 3、安装依赖工具：yum install cmake gcc-c++ ncurses-devel perl-Data-Dumper boost boost-doc boost-devel git 4、删除CMakeCache.txt 5、使用cmake配置：
sudo cmake \ -DWITH_DEBUG=1 \ -DMYSQL_DATADIR=/usr/local/mysql/data \ -DCMAKE_INSTALL_PREFIX=/usr/local/mysql \ -DMYSQL_TCP_PORT=3306 \ -DFORCE_INSOURCE_BUILD=1 \ -DDOWNLOAD_BOOST=1 \ -DWITH_BOOST=/usr/local/boost \ -DSYSCONFDIR=/etc \ -DMYSQL_UNIX_ADDR=/usr/local/mysql/run/mysql.sock \在makefile的开头定义CFLAGS 变量：CFLAGS = -g ，否则调试过程中无法跟踪代码
6、编译&&安装：make && make install
7、cd /usr/local/mysql/bin/ 8、./mysqld --initialize --datadir=/usr/local/mysql/data --basedir=/usr/local/mysql --user=mysql 9、创建etc目录并移动support-files中的配置文件（hu），更名为my.cnf 10、mysqld --skip-grant-tables
11、直接 mysql 跳过验证登陆
12、将root用户验证设置为空
use mysql update user set authentication_string="" where user="root"; exit13、/usr/local/mysql/support-files/mysql.server start 重新开启 mysql 服务
14、无需输入密码，直接回车登陆
/usr/local/mysql_slave/bin/mysql -uroot -p --socket=/usr/local/mysql/run/mysql.sock14、更改本地root用户登陆密
alter user 'root'@'localhost' IDENTIFIED with mysql_native_password by '123456'; flush privileges;
配置文件：
[mysqld] port=3306 mysqlx_port=33060 socket=/usr/local/mysql/run/mysql.sock mysqlx_socket=/usr/local/mysql/run/mysqlx.sock basedir=/usr/local/mysql datadir=/usr/local/mysql/data character-set-server=utf8 lower-case-table-names=2 default_authentication_plugin=mysql_native_password # 主从复制-主机配置 server-id=1 gtid_mode=on enforce_gtid_consistency=on # 启用二进制日志 log-bin=mysql-bin # 设置不要复制的数据库(可设置多个) binlog-ignore-db=sys binlog-ignore-db=mysql binlog-ignore-db=information_schema binlog-ignore-db=performance_schema # 设置需要复制的数据库(可设置多个) binlog-do-db=test # 设置logbin格式 binlog_format=ROW启动服务：
nohup /usr/local/mysql/bin/mysqld --defaults-file=/usr/local/mysql/etc/3306.cnf &登陆主库，创建slave用户并授权：
/usr/local/bin/mysql -uroot -p --socket=/usr/local/mysql/run/mysql.sock Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 8 Server version: 8.0.18 Source distribution Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> show global variables like 'port'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | port | 3306 | +---------------+-------+ 1 row in set (0.01 sec) mysql> show variables like 'server_id'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | server_id | 1 | +---------------+-------+ 1 row in set (0.00 sec) mysql> create user 'slave'@'%' identified with mysql_native_password by '123456'; Query OK, 0 rows affected (0.01 sec) mysql> grant replication slave on *.* to 'slave'@'%'; Query OK, 0 rows affected (0.00 sec) mysql> flush privileges; Query OK, 0 rows affected (0.00 sec) mysql> show grants for 'slave'@'%'; +-----------------------------------------------+ | Grants for slave@% | +-----------------------------------------------+ | GRANT REPLICATION SLAVE ON *.* TO `slave`@`%` | +-----------------------------------------------+ 1 row in set (0.00 sec) mysql> show master status\G; *************************** 1. row *************************** File: mysql-bin.000012 Position: 155 Binlog_Do_DB: test Binlog_Ignore_DB: sys,mysql,information_schema,performance_schema Executed_Gtid_Set: 1 row in set (0.00 sec) ERROR: No query specified mysql>
配置：
[mysqld] port=3307 mysqlx_port=33070 socket=/usr/local/mysql_slave/run/mysql.sock mysqlx_socket=/usr/local/mysql_slave/run/mysqlx.sock basedir=/usr/local/mysql_slave datadir=/usr/local/mysql_slave/data character-set-server=utf8 lower-case-table-names=2 default_authentication_plugin=mysql_native_password # 主从复制-从机配置 server-id=2 gtid_mode=on enforce_gtid_consistency=on # 启用中继日志 relay-log=mysql-relay启动从库：
nohup /usr/local/mysql_slave/bin/mysqld --defaults-file=/usr/local/mysql_slave/etc/3307.cnf &登陆并设置主库：
usr/local/mysql_slave/bin/mysql -uroot -p --socket=/usr/local/mysql_slave/run/mysql.sock Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 9 Server version: 8.0.18 Source distribution Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> show global variables like 'port'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | port | 3307 | +---------------+-------+ 1 row in set (0.02 sec) mysql> show variables like 'server_id'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | server_id | 2 | +---------------+-------+ 1 row in set (0.01 sec) mysql> change master to master_host='127.0.0.1',master_port=3306,master_user='slave',master_password='123456',master_log_file='mysql-bin.000012',master_log_pos=155; mysql> start slave; Query OK, 0 rows affected (0.00 sec) mysql> show slave status\G; *************************** 1. row *************************** Slave_IO_State: Connecting to master Master_Host: 137.0.0.1 Master_User: slave Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000012 Read_Master_Log_Pos: 155 Relay_Log_File: mysql-relay.000001 Relay_Log_Pos: 4 Relay_Master_Log_File: mysql-bin.000012 Slave_IO_Running: Connecting Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 155 Relay_Log_Space: 155 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: NULL Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 0 Master_UUID: Master_Info_File: mysql.slave_master_info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for moooooooomoore updates Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: Master_public_key_path: Get_master_public_key: 0 Network_Namespace: 1 row in set (0.00 sec) ERROR: No query specified mysql>
主要观察从库两个id对比，用于发现从库执行主库事务是否被阻塞
主库：Executed_Gtid_Set 表示当前主库的最新的事务id从库：Retrieved_Gtid_Set 表示接受到的最新事务id，Executed_Gtid_Set 表示从库当前执行完成的最新事务id执行sql：
mysql> insert into test values(1, 1, 1);再次查看事务id：
mysql> show master status\G; *************************** 1. row *************************** File: mysql-bin.000013 Position: 3634 Binlog_Do_DB: test Binlog_Ignore_DB: sys,mysql,information_schema,performance_schema Executed_Gtid_Set: 9513a1b0-f5b4-11ea-930d-a5c900a3310f:1-11 1 row in set (0.00 sec) ERROR: No query specified mysql> show slave status\G; ...省略... Retrieved_Gtid_Set: 9513a1b0-f5b4-11ea-930d-a5c900a3310f:10-11 Executed_Gtid_Set: 266954d6-061b-11eb-9358-4150d020308f:1-2, 9513a1b0-f5b4-11ea-930d-a5c900a3310f:2:10-11 ...省略...写完主库，手动切换到主库进行select
默认在mysql配置的data目录下的lib/plugin目录里，作为*.so动态库存储，所以可在服务运行时加载
[why@whydeMacBook-Pro] /usr/local/mysql/lib/plugin$pwd /usr/local/mysql/lib/plugin [why@whydeMacBook-Pro] /usr/local/mysql/lib/plugin$ll | grep semi -rwxr-xr-x 1 why staff 114056 9 13 19:09 semisync_master.so -rwxr-xr-x 1 why staff 34880 9 13 19:09 semisync_slave.so [why@whydeMacBook-Pro] /usr/local/mysql/lib/plugin$ mysql> show variables like '%plugin%'; +-------------------------------+------------------------------+ | Variable_name | Value | +-------------------------------+------------------------------+ | default_authentication_plugin | mysql_native_password | | plugin_dir | /usr/local/mysql/lib/plugin/ | +-------------------------------+------------------------------+ 2 rows in set (0.00 sec)主服务安装：
mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so'; Query OK, 0 rows affected (0.02 sec)从服务安装：
mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so'; Query OK, 0 rows affected (0.02 sec)主服务半同步配置：
mysql> show variables like '%semi%'; +-------------------------------------------+------------+ | Variable_name | Value | +-------------------------------------------+------------+ | rpl_semi_sync_master_enabled | OFF | | rpl_semi_sync_master_timeout | 10000 | | rpl_semi_sync_master_trace_level | 32 | | rpl_semi_sync_master_wait_for_slave_count | 1 | | rpl_semi_sync_master_wait_no_slave | ON | | rpl_semi_sync_master_wait_point | AFTER_SYNC | +-------------------------------------------+------------+ 6 rows in set (0.00 sec)从服务半同步配置：
mysql> show variables like '%semi%'; +---------------------------------+-------+ | Variable_name | Value | +---------------------------------+-------+ | rpl_semi_sync_slave_enabled | OFF | | rpl_semi_sync_slave_trace_level | 32 | +---------------------------------+-------+ 2 rows in set (0.02 sec)主服务开启半同步：
mysql> set global rpl_semi_sync_master_enabled = ON; Query OK, 0 rows affected (0.01 sec)从服务开启半同步：
mysql> set global rpl_semi_sync_slave_enabled = ON; Query OK, 0 rows affected (0.01 sec)事务更新完再获取事务的gtid需要再对主库执行一次查询，通过更改客户端源码和会话级别变量 setsession_track_gtids，可以在更新成功之后直接返回gtid：
1、修改源码client/mysql.cc，make编译，替换mysql相关bin文件或做软链
mysql_session_track_get_first 方法定义在源码的 sql-common/client.cc 文件，将当前 gtid 存储在 data变量中。
2、数据库层开启每个会话时，设置 setsession_track_gtids=OWN_GTID
详细参考这篇博客：https://mp.weixin.qq.com/s/oj-DzpR-hZRMMziq2_0rYg?spm=a2c6h.12873639.0.0.c491552bPwO056
