MySQL主从同步的几种实现方式

配置文件：
[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> 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

[root@localhost ~]# tail -1 /etc/my.cnf
server_id=3
[root@localhost ~]# systemctl restart mysqld

还原主库备份：
# scp db.sql 172.16.100.22:/root/
# scp db.sql 172.16.100.23:/root/

mysql -uroot -pmysql@123 < db.sql

mysql> show slave status \g
*************************** 1. row ***************************
               slave_io_state: connecting to source
                  master_host: 192.168.150.21
                  master_user: rep
                  master_port: 3306
                connect_retry: 60
              master_log_file: binlog.000003
          read_master_log_pos: 157
               relay_log_file: localhost-relay-bin.000001
                relay_log_pos: 4
        relay_master_log_file: binlog.000003
             slave_io_running: connecting
            slave_sql_running: yes
...
              last_io_error: error connecting to master 'rep@192.168.150.21:3306' - retry-time: 60 retries: 1 message: can't connect to mysql server on '192.168.150.21:3306' (111)


[root@localhost ~]# mysqladmin -urep -p123456 -h192.168.150.21 ping
mysqladmin: [warning] using a password on the command line interface can be insecure.
mysqld is alive


# 查询日志
[root@localhost ~]# tail /var/log/mysql/mysqld.log
...
2023-12-02t03:17:53.631813z 5 [error] [my-010584] [repl] slave i/o for channel '': error connecting to master 'rep@192.168.150.21:3306' - retry-time: 60 retries: 3 message: authentication plugin 'caching_sha2_password' reported error: authentication requires secure connection. error_code: my-002061

mysql> alter user 'rep'@'192.168.150.%' identified with mysql_native_password by '123456';
query ok, 0 rows affected (0.01 sec)

mysql> stop slave;
query ok, 0 rows affected, 1 warning (0.00 sec)

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,
    -> 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

开启gtid
gtid_mode=on
enforce-gtid-consistency=on

mysql> change master to
     >     master_host = host,
     >     master_port = port,
     >     master_user = user,
     >     master_password = password,
     >     master_auto_position = 1;

or from mysql 8.0.23:

mysql> change replication source to
     >     source_host = host,
     >     source_port = port,
     >     source_user = user,
     >     source_password = password,
     >     source_auto_position = 1;
     
mysql> start slave;
or from mysql 8.0.22:
mysql> start replica;

查看监控信息:
last_sql_error: error 'can't create database 'db4'; database exists' on query. default database: 'db4'. query: 'create database db4'

retrieved_gtid_set: 71bfa52e-4aae-11e9-ab8c-000c293b577e:1-3
executed_gtid_set:  71bfa52e-4aae-11e9-ab8c-000c293b577e:1-2,
7ca4a2b7-4aae-11e9-859d-000c298720f6:1

注入空事务的方法：

stop slave;
set gtid_next='99279e1e-61b7-11e9-a9fc-000c2928f5dd:3';
begin;commit;
set gtid_next='automatic';
    
这里的xxxxx:n 也就是你的slave sql thread报错的gtid，或者说是你想要跳过的gtid。
最好的解决方案：重新构建主从环境

# mysql -uadmin -padmin -h127.0.0.1 -p6032
mysql: [warning] using a password on the command line interface can be insecure.
welcome to the mysql monitor.  commands end with ; or \g.
your mysql connection id is 2
server version: 5.5.30 (proxysql admin module)
mysql> show databases;
+-----+---------------+-------------------------------------+
| seq | name          | file                                |
+-----+---------------+-------------------------------------+
| 0   | main          |                                     |
| 2   | disk          | /var/lib/proxysql/proxysql.db       |
| 3   | stats         |                                     |
| 4   | monitor       |                                     |
| 5   | stats_history | /var/lib/proxysql/proxysql_stats.db |
+-----+---------------+-------------------------------------+
5 rows in set (0.00 sec)

# 配置yum源
cat > /etc/yum.repos.d/proxysql.repo << eof
[proxysql]
name=proxysql yum repository
baseurl=https://repo.proxysql.com/proxysql/proxysql-2.5.x/centos/\$releasever
gpgcheck=1
gpgkey=https://repo.proxysql.com/proxysql/proxysql-2.5.x/repo_pub_key
eof

# 安装proxysql
yum install -y proxysql

[root@proxy ~]# systemctl enable --now proxysql

# 管理员登录
[root@proxy ~]# mysql -uadmin -padmin -h 127.0.0.1 -p 6032
welcome to the mariadb monitor.  commands end with ; or \g.
your mysql connection id is 1
server version: 5.5.30 (proxysql admin module)

copyright (c) 2000, 2018, oracle, mariadb corporation ab and others.

type 'help;' or '\h' for help. type '\c' to clear the current input statement.

mysql [(none)]> show databases;
+-----+---------------+-------------------------------------+
| seq | name          | file                                |
+-----+---------------+-------------------------------------+
| 0   | main          |                                     |
| 2   | disk          | /var/lib/proxysql/proxysql.db       |
| 3   | stats         |                                     |
| 4   | monitor       |                                     |
| 5   | stats_history | /var/lib/proxysql/proxysql_stats.db |
+-----+---------------+-------------------------------------+
5 rows in set (0.00 sec)

可见有五个库： main、disk、stats 、monitor 和 stats_history
main: 内存配置数据库，即 memory，表里存放后端 db 实例、用户验证、路由规则等信息。main 库中有如下信息：

mysql [(none)]> show tables from main;
+----------------------------------------------------+
| tables                                             |
+----------------------------------------------------+
| coredump_filters                                   |
| global_variables                                   |
| mysql_aws_aurora_hostgroups                        |
| mysql_collations                                   |
| mysql_firewall_whitelist_rules                     |
| mysql_firewall_whitelist_sqli_fingerprints         |
| mysql_firewall_whitelist_users                     |
| mysql_galera_hostgroups                            |
| mysql_group_replication_hostgroups                 |
| mysql_hostgroup_attributes                         |
| mysql_query_rules                                  |
| mysql_query_rules_fast_routing                     |
| mysql_replication_hostgroups                       |
| mysql_servers                                      |
| mysql_users                                        |
| proxysql_servers                                   |
| restapi_routes                                     |
| runtime_checksums_values                           |
| runtime_coredump_filters                           |
| runtime_global_variables                           |
| runtime_mysql_aws_aurora_hostgroups                |
| runtime_mysql_firewall_whitelist_rules             |
| runtime_mysql_firewall_whitelist_sqli_fingerprints |
| runtime_mysql_firewall_whitelist_users             |
| runtime_mysql_galera_hostgroups                    |
| runtime_mysql_group_replication_hostgroups         |
| runtime_mysql_hostgroup_attributes                 |
| runtime_mysql_query_rules                          |
| runtime_mysql_query_rules_fast_routing             |
| runtime_mysql_replication_hostgroups               |
| runtime_mysql_servers                              |
| runtime_mysql_users                                |
| runtime_proxysql_servers                           |
| runtime_restapi_routes                             |
| runtime_scheduler                                  |
| scheduler                                          |
+----------------------------------------------------+
36 rows in set (0.00 sec)

库下的主要表：
mysql_servers: 后端可以连接 mysql 服务器的列表
mysql_users: 配置后端数据库的账号和监控的账号。
mysql_query_rules: 指定 query 路由到后端不同服务器的规则列表。

注： 表名以 runtime_开头的表示 proxysql 当前运行的配置内容，不能通过 dml 语句修改。

只能修改对应的不以 runtime 开头的表，然后 “load” 使其生效，“save” 使其存到硬盘以供下次重启加载。
disk ：持久化的磁盘的配置
stats： 统计信息的汇总
monitor：一些监控的收集信息，比如数据库的健康状态等
stats_history: 这个库是 proxysql 收集的有关其内部功能的历史指标

create user 'monitor'@'172.16.%.%' identified with mysql_native_password by 'monitor@123.com';
grant all privileges on *.* to 'monitor'@'172.16.%.%' with grant option;

#proxysql 的对外访问账户
create user 'proxysql'@'172.16.%.%' identified with mysql_native_password by '123456';
grant all privileges on *.* to 'proxysql'@'172.16.%.%' with grant option;

mysql [(none)]> show create table mysql_replication_hostgroups \g
*************************** 1. row ***************************
       table: mysql_replication_hostgroups
create table: create table mysql_replication_hostgroups (
    writer_hostgroup int check (writer_hostgroup>=0) not null primary key,
    reader_hostgroup int not null check (reader_hostgroup<>writer_hostgroup and reader_hostgroup>=0),
    check_type varchar check (lower(check_type) in ('read_only','innodb_read_only','super_read_only','read_only|innodb_read_only','read_only&innodb_read_only')) not null default 'read_only',
    comment varchar not null default '', unique (reader_hostgroup))
1 row in set (0.00 sec)

注：writer_hostgroup 和reader_hostgroup 写组和读组都要大于等于0且不能相同

mysql [(none)]> insert into mysql_replication_hostgroups (writer_hostgroup,reader_hostgroup,comment) values (1,0,'proxy');
query ok, 1 row affected (0.00 sec)

mysql [(none)]> load mysql servers to runtime;
query ok, 0 rows affected (0.01 sec)

mysql [(none)]> save mysql servers to disk;
query ok, 0 rows affected (0.02 sec)

注意：proxysql会根据server的read_only的取值将服务器进行分组。read_only=0的server，master被分到编号为1的写组，read_only=1的server，slave则分到编号为0的读组

mysql [(none)]> select * from mysql_replication_hostgroups;
+------------------+------------------+------------+---------+
| writer_hostgroup | reader_hostgroup | check_type | comment |
+------------------+------------------+------------+---------+
| 1                | 0                | read_only  | proxy   |
+------------------+------------------+------------+---------+
1 row in set (0.00 sec)

mysql [(none)]> insert into mysql_servers(hostgroup_id,hostname,port) values (1,'172.16.100.21',3306);
query ok, 1 row affected (0.00 sec)

mysql [(none)]> insert into mysql_servers(hostgroup_id,hostname,port) values (0,'172.16.100.22',3306);
query ok, 1 row affected (0.00 sec)

mysql [(none)]> insert into mysql_servers(hostgroup_id,hostname,port) values (0,'172.16.100.23',3306);
query ok, 1 row affected (0.00 sec)

mysql [(none)]> load mysql servers to runtime;
query ok, 0 rows affected (0.01 sec)

mysql [(none)]> save mysql servers to disk;
query ok, 0 rows affected (0.00 sec)

mysql [(none)]> select * from mysql_servers;
+--------------+----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname       | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 1            | 192.168.150.21 | 3306 | 0         | online | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
| 0            | 192.168.150.22 | 3306 | 0         | online | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
| 0            | 192.168.150.23 | 3306 | 0         | online | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
+--------------+----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
3 rows in set (0.00 sec)

mysql [(none)]> use monitor
reading table information for completion of table and column names
you can turn off this feature to get a quicker startup with -a

database changed
mysql [monitor]> set mysql-monitor_username='monitor';
query ok, 1 row affected (0.00 sec)

mysql [monitor]> set mysql-monitor_password='monitor@123.com';
query ok, 1 row affected (0.00 sec)

上面这两句是修改变量的方式还可以在main库下面用sql语句方式修改
在main下修改：
mysql [monitor]> use main
reading table information for completion of table and column names
you can turn off this feature to get a quicker startup with -a

database changed
mysql [main]> update global_variables set variable_value='monitor' where variable_name='mysql-monitor_username';
query ok, 1 row affected (0.00 sec)

mysql [main]> update global_variables set variable_value='monitor@123.com' where variable_name='mysql-monitor_password';
query ok, 1 row affected (0.00 sec)

修改后，保存到runtime和disk
mysql [monitor]> load mysql variables to runtime;
mysql [monitor]> save mysql variables to disk;

查看监控账号【proxysql】
select * from global_variables where variable_name like 'mysql-monitor_%';
//也可以这样快速定位
mysql [(none)]> select @@mysql-monitor_username;
+--------------------------+
| @@mysql-monitor_username |
+--------------------------+
| monitor                  |
+--------------------------+
1 row in set (0.00 sec)
mysql [(none)]> select @@mysql-monitor_password;
+--------------------------+
| @@mysql-monitor_password |
+--------------------------+
| monitor@123.com          |
+--------------------------+
1 row in set (0.00 sec)


mysql [(none)]> select * from monitor.mysql_server_connect_log;
+----------------+------+------------------+-------------------------+---------------+
| hostname       | port | time_start_us    | connect_success_time_us | connect_error |
+----------------+------+------------------+-------------------------+---------------+
| 192.168.150.21 | 3306 | 1701659547601785 | 2126                    | null          |
| 192.168.150.23 | 3306 | 1701659548266990 | 1883                    | null          |
| 192.168.150.22 | 3306 | 1701659548932828 | 1626                    | null          |
| 192.168.150.21 | 3306 | 1701659607601353 | 1672                    | null          |
| 192.168.150.22 | 3306 | 1701659608066386 | 1649                    | null          |
| 192.168.150.23 | 3306 | 1701659608531392 | 1962                    | null          |
| 192.168.150.21 | 3306 | 1701659667601720 | 1283                    | null          |
| 192.168.150.23 | 3306 | 1701659668394216 | 2033                    | null          |
| 192.168.150.22 | 3306 | 1701659669186458 | 1350                    | null          |
+----------------+------+------------------+-------------------------+---------------+
9 rows in set (0.00 sec)

验证监控信息，proxysql 监控模块的指标都保存在monitor库的log表中 
以下是连接是否正常的监控，对connect指标的监控 ，在前面可能会有很多connect_error，这是因为没有配置监控信息时的错误，配置后如果connect_error的结果为null则表示正常

mysql [(none)]> select * from mysql_server_ping_log limit 10;
+----------------+------+------------------+----------------------+------------+
| hostname       | port | time_start_us    | ping_success_time_us | ping_error |
+----------------+------+------------------+----------------------+------------+
| 192.168.150.21 | 3306 | 1701659547602344 | 460                  | null       |
| 192.168.150.23 | 3306 | 1701659547604095 | 245                  | null       |
| 192.168.150.22 | 3306 | 1701659547604690 | 258                  | null       |
| 192.168.150.23 | 3306 | 1701659557601548 | 471                  | null       |
| 192.168.150.21 | 3306 | 1701659557601738 | 313                  | null       |
| 192.168.150.22 | 3306 | 1701659557601713 | 349                  | null       |
| 192.168.150.22 | 3306 | 1701659567602161 | 572                  | null       |
| 192.168.150.23 | 3306 | 1701659567602361 | 405                  | null       |
| 192.168.150.21 | 3306 | 1701659567602318 | 460                  | null       |
| 192.168.150.22 | 3306 | 1701659577602909 | 434                  | null       |
+----------------+------+------------------+----------------------+------------+
10 rows in set (0.00 sec)

mysql [(none)]> select * from mysql_server_read_only_log limit 5;
+----------------+------+------------------+-----------------+-----------+-------+
| hostname       | port | time_start_us    | success_time_us | read_only | error |
+----------------+------+------------------+-----------------+-----------+-------+
| 192.168.150.23 | 3306 | 1701659547606174 | 373             | 1         | null  |
| 192.168.150.22 | 3306 | 1701659547606768 | 634             | 1         | null  |
| 192.168.150.21 | 3306 | 1701659547606639 | 858             | 0         | null  |
| 192.168.150.21 | 3306 | 1701659549101574 | 657             | 0         | null  |
| 192.168.150.22 | 3306 | 1701659549101863 | 422             | 1         | null  |
+----------------+------+------------------+-----------------+-----------+-------+
5 rows in set (0.00 sec)

monitor 模块就会开始监控后端的read_only值，当监控到read_only值，就会按照read_only的值将某些节点自动移到读写组 
一些监控的状态斗志在log相关，都在monitor库下面的 global_variables 变量。

mysql [(none)]> show create table mysql_users\g
*************************** 1. row ***************************
       table: mysql_users
create table: create table mysql_users (
    username varchar not null,
    password varchar,
    active int check (active in (0,1)) not null default 1,
    use_ssl int check (use_ssl in (0,1)) not null default 0,
    default_hostgroup int not null default 0,
    default_schema varchar,
    schema_locked int check (schema_locked in (0,1)) not null default 0,
    transaction_persistent int check (transaction_persistent in (0,1)) not null default 1,
    fast_forward int check (fast_forward in (0,1)) not null default 0,
    backend int check (backend in (0,1)) not null default 1,
    frontend int check (frontend in (0,1)) not null default 1,
    max_connections int check (max_connections >=0) not null default 10000,
    attributes varchar check (json_valid(attributes) or attributes = '') not null default '',
    comment varchar not null default '',
    primary key (username, backend),
    unique (username, frontend))
1 row in set (0.00 sec)

insert into mysql_users (username,password,default_hostgroup,transaction_persistent) values ('proxysql','123456',1,1);
 
load mysql users to runtime;
save mysql users to disk;
mysql [(none)]> select * from mysql_users\g
*************************** 1. row ***************************
              username: proxysql
              password: 123456
                active: 1
               use_ssl: 0
     default_hostgroup: 1
        default_schema: null
         schema_locked: 0
transaction_persistent: 1
          fast_forward: 0
               backend: 1
              frontend: 1
       max_connections: 10000
            attributes:
               comment:
1 row in set (0.00 sec)

注：transaction_persistent 如果为1，则一个完整的sql只可能路由到一个节点；这点非常重要，主要解决这种情况：一个事务有混合的读操作和写操作组成，事务未提交前，如果事务中的读操作和写操作路由到不同节点，那么读取到的结果必然是脏数据。所以一般情况下，该值应该设置为1，尤其是业务中使用到事务机制的情况（默认为0）
mysql_users 表有不少字段，最主要的三个字段username,password,default_hostgroup 
a.username: 前端链接proxysql ，以及proxysql 将sql 语句路由给mysql所使用的的用户名
b.password：用户名对应的密码，。可以是明文密码，也可以是hash密码。如果想使用hash密码，可以先在某个mysql节点上执行select password(password)，然后将加密结果复制到该字段。 
c.default_hostgroup：该用户名默认的路由目标。例如，指定root用户的该字段值为1时，则使用 proxysql 用户发送的sql语句默认情况下将路由到hostgroup_id=1 组中的某个

[root@slave1 ~]# mysql -h172.16.100.24 -uproxysql -p'123456' -p 6033
mysql: [warning] using a password on the command line interface can be insecure.
welcome to the mysql monitor.  commands end with ; or \g.
your mysql connection id is 3
server version: 5.5.30 (proxysql)

copyright (c) 2000, 2022, oracle and/or its affiliates.

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 databases;
+--------------------+
| database           |
+--------------------+
| db1                |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.01 sec)

mysql> select @@server_id;
+-------------+
| @@server_id |
+-------------+
|          21 |
+-------------+
1 row in set (0.00 sec)

#通过proxysql用户，创建一个keme库
mysql> create database keme;
query ok, 1 row affected (0.00 sec)

mysql> select @@server_id;
+-------------+
| @@server_id |
+-------------+
|          23 |
+-------------+
1 row in set (0.00 sec)

mysql> show databases;
+--------------------+
| database           |
+--------------------+
| db1                |
| information_schema |
| keme               |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
6 rows in set (0.01 sec)

insert into mysql_query_rules(rule_id,active,match_pattern,destination_hostgroup,apply) values(1,1,'^select .* for update$',1,1);

insert into mysql_query_rules(rule_id,active,match_pattern,destination_hostgroup,apply) values(2,1,'^select',0,1);

load mysql query rules to runtime;
save mysql query rules to disk;

[root@slave1 ~]# mysql -uproxysql -p123456 -h 172.16.100.24 -p 6033 -e "select @@server_id"
mysql: [warning] using a password on the command line interface can be insecure.
+-------------+
| @@server_id |
+-------------+
|          3 |
+-------------+
[root@slave1 ~]# mysql -uproxysql -p123456 -h 172.16.100.24 -p 6033 -e "select @@server_id"
mysql: [warning] using a password on the command line interface can be insecure.
+-------------+
| @@server_id |
+-------------+
|          2 |
+-------------+
[root@slave1 ~]# mysql -uproxysql -p123456 -h 172.16.100.24 -p 6033 -e "begin;select @@server_id commit;"
mysql: [warning] using a password on the command line interface can be insecure.
+--------+
| commit |
+--------+
|     1 |
+--------+