• MySQL的复制

    一、MySQL复制相关概念

    • 主从复制：主节点将数据同步到多个从节点
    • 级联复制：主节点将数据同步到一个从节点，其他的从节点在向从节点复制数据
    • 同步复制：将数据从主节点全部同步到从节点时才返回给用户的复制策略叫同步复制
    • 异步复制：只要数据写入到主节点就立即返回给用户同步完成
    • 读写分离：在前端加一个调度器，负责将改变数据的语句和查询数据的语句分开调度，把写操作调度到主节点，读操作调度到从节点

    主节点：

    • dump Thread：为每个Slave的I/O Thread启动一个dump线程，用于向其发送binary log events

    从节点：

    • I/O Thread：向Master请求二进制日志事件，并保存于中继日志中
    • SQL Thread：从中继日志中读取日志事件，在本地完成重放

    跟复制功能相关的文件：

    • master.info：用于保存slave连接至master时的相关信息，例如账号、密码、服务器地址等
    • relay-log.info：保存在当前slave节点上已经复制的当前二进制日志和本地replay log日志的对应关系

    复制架构：

    • 一主一从
    • 一主多从
    • 主主复制
    • 环状复制
    • 级联复制
    • 多主一从

    常见的架构有主从架构或者级联架构

    二、简单的一主一从架构实现

    1、新数据库搭建主从架构

    ​ 1）主服务器配置

    1. ~]# vim /etc/my.cnf
    2.     [mysqld]
    3.     log_bin
    4.     binlog_format=ROW
    5.     log-basename=master1
    6.     server_id=1
    7. ~]# systemctl restart mariadb
    8. ~]# mysql
    9. MariaDB [(none)]> GRANT REPLICATION SLAVE ON *.* TO testuser@'192.168.0.8' IDENTIFIED BY 'testpass';  #授权同步账户
    10. MariaDB [(none)]> SHOW MASTER LOGS;
    11. +--------------------+-----------+
    12. | Log_name           | File_size |
    13. +--------------------+-----------+
    14. | master1-bin.000001 |     26756 |
    15. | master1-bin.000002 |    921736 |
    16. | master1-bin.000003 |       401 |  #记录此位置，从服务器从这里开始同步
    17. +--------------------+-----------+

    ​ 2）从服务器配置

    1. ~]# vim /etc/my.cnf
    2.     [mysqld]
    3.     server_id=2  #服务器ID唯一
    4.     relay_log=relay-log
    5.     relay_log_index=relay-log.index
    6.     read_only=ON
    7. ~]# systemctl restart mariadb
    8. ~]# mysql
    9. MariaDB [(none)]> CHANGE MASTER TO
    10.     -> MASTER_HOST='192.168.0.7',  #指定主节点IP
    11.     -> MASTER_USER='testuser',  #同步用户的用户名
    12.     -> MASTER_PASSWORD='testpass',  #密码
    13.     -> MASTER_PORT=3306,
    14.     -> MASTER_LOG_FILE='master1-bin.000003',  #以上记录的文件
    15.     -> MASTER_LOG_POS=401,  #位置
    16.     -> MASTER_CONNECT_RETRY=10;  #重试时间10秒
    17. MariaDB [(none)]> START SLAVE;  #开始主从复制

    ​ 3）测试

    1. 在主节点上生成一些数据：
    2. MariaDB [(none)]> CREATE DATABASE testdb;
    3. MariaDB [(none)]> use testdb
    4. MariaDB [testdb]> create table testlog (id int auto_increment primary key,name char(30),age int default 20);
    5. MariaDB [testdb]> delimiter $$
    6. MariaDB [testdb]> create procedure pro_testlog()
    7.     -> begin
    8.     -> declare i int;
    9.     -> set i = 1;
    10.     -> while i < 100000
    11.     -> do insert into testlog(name,age) values (concat('testuser',i),i);
    12.     -> set i = i +1; 
    13.     -> end while;
    14.     -> end$$
    15. MariaDB [testdb]> delimiter ;
    16. MariaDB [testdb]> START TRANSACTION;
    17. MariaDB [testdb]> CALL pro_testlog;
    18. MariaDB [testdb]> COMMIT;
    1. 在从节点上查看同步情况：
    2. MariaDB [(none)]> SELECT COUNT(*) FROM testdb.testlog;
    3. +----------+
    4. | COUNT(*) |
    5. +----------+
    6. |    99999 |  #同步成功
    7. +----------+
    8. MariaDB [(none)]> SHOW SLAVE STATUS\G
    9. *************************** 1. row ****************************
    10.                Slave_IO_State: Waiting for master to send event
    11.                   Master_Host: 192.168.0.7
    12.                   Master_User: testuser
    13.                   Master_Port: 3306
    14.                 Connect_Retry: 10
    15.               Master_Log_File: master1-bin.000003
    16.           Read_Master_Log_Pos: 10389814
    17.                Relay_Log_File: relay-log.000002
    18.                 Relay_Log_Pos: 10389944
    19.         Relay_Master_Log_File: master1-bin.000003
    20.              Slave_IO_Running: Yes  #IO线程已启动
    21.             Slave_SQL_Running: Yes  #SQL线程已启动
    22.         Seconds_Behind_Master: 0    #主从复制的时间差
    23.              Master_Server_Id: 1

    2、旧数据库新加从服务器

    ​ 1）主服务器配置

    1. ~]# vim /etc/my.cnf
    2.     [mysqld]
    3.     log_bin
    4.     binlog_format=ROW
    5.     log-basename=master1
    6.     server_id=1
    7. ~]# systemctl restart mariadb
    8. ~]# mysqldump -A -F --single-transaction --master-data=1 > full.sql
    9. ~]# scp full.sql root@192.168.0.8:/root/
    10. ~]# mysql -e 'GRANT REPLICATION SLAVE ON *.* TO testuser@'192.168.0.8' IDENTIFIED BY 'testpass';'

    ​ 2）从服务器配置

    1. ~]# vim /etc/my.cnf
    2.     [mysqld]
    3.     server_id=2
    4.     relay_log=relay-log   
    5.     relay_log_index=relay-log.index
    6.     read_only=ON
    7. ~]# systemctl restart mariadb
    8. ~]# vim full.sql  #在备份的SQL文件中加入以下信息
    9.     CHANGE MASTER TO
    10.     MASTER_HOST='192.168.0.7',
    11.     MASTER_USER='testuser',
    12.     MASTER_PASSWORD='testpass',
    13.     MASTER_PORT=3306,
    14.     MASTER_LOG_FILE='master1-bin.000005',
    15.     MASTER_LOG_POS=245,
    16.     MASTER_CONNECT_RETRY=10;
    17. ~]# mysql < full.sql  #导入SQL的同时配置已经完成
    18. MariaDB [(none)]> SELECT COUNT(*) FROM testdb.testlog;
    19. +----------+
    20. | COUNT(*) |
    21. +----------+
    22. |    99999 |
    23. +----------+
    24. MariaDB [(none)]> START SLAVE;  #启动复制

    三、级联复制架构实现

    正在上传…重新上传取消

    1）主节点

    1. [root@master ~]# vim /etc/my.cnf
    2. [mysqld]
    3. log_bin
    4. binlog_format=ROW
    5. log-basename=master
    6. server_id=1
    7. [root@master ~]# systemctl restart mariadb
    8. MariaDB [(none)]> GRANT REPLICATION SLAVE ON *.* TO repluser@'192.168.0.%' IDENTIFIED BY 'replpass';
    9. MariaDB [(none)]> SHOW MASTER LOGS;
    10. +-------------------+-----------+
    11. | Log_name          | File_size |
    12. +-------------------+-----------+
    13. | master-bin.000001 |     26753 |
    14. | master-bin.000002 |    921736 |
    15. | master-bin.000003 |       401 |
    16. +-------------------+-----------+

    2）从节点

    1. [root@slave1 ~]# vim /etc/my.cnf
    2. [mysqld]
    3. log_bin  #注意，级联架构中中继从节点一定得开二进制日志功能
    4. binlog_format=ROW
    5. read_only=ON
    6. server_id=2
    7. log_slave_updates  #这项为关键，作用是将从服务的数据改变记录到二进制日志文件中
    8. relay_log=relay-log
    9. relay_log_index=relay-log.index
    10. [root@slave1 ~]# systemctl start mariadb
    11. MariaDB [(none)]> CHANGE MASTER TO
    12.     -> MASTER_HOST='192.168.0.7',
    13.     -> MASTER_USER='repluser',
    14.     -> MASTER_PASSWORD='replpass',
    15.     -> MASTER_PORT=3306,
    16.     -> MASTER_LOG_FILE='master-bin.000003',
    17.     -> MASTER_LOG_POS=401,
    18.     -> MASTER_CONNECT_RETRY=10;
    19. MariaDB [(none)]> START SLAVE;
    20. MariaDB [(none)]> SHOW MASTER LOGS;
    21. +--------------------+-----------+
    22. | Log_name           | File_size |
    23. +--------------------+-----------+
    24. | mariadb-bin.000001 |       245 |
    25. +--------------------+-----------+
    26. MariaDB [(none)]> GRANT REPLICATION SLAVE ON *.* TO repluser@'192.168.0.%' IDENTIFIED BY 'replpass';

    3）从节点的从节点

    1. [root@slave2 ~]# vim /etc/my.cnf
    2. [mysqld]
    3. read_only=ON
    4. server_id=3
    5. relay_log=relay-log
    6. relay_log_index=relay-log.index
    7. [root@slave2 ~]# systemctl start mariadb
    8. MariaDB [(none)]> CHANGE MASTER TO
    9.     -> MASTER_HOST='192.168.0.8',
    10.     -> MASTER_USER='repluser',
    11.     -> MASTER_PASSWORD='replpass',
    12.     -> MASTER_PORT=3306,
    13.     -> MASTER_LOG_FILE='mariadb-bin.000001',
    14.     -> MASTER_LOG_POS=245,
    15.     -> MASTER_CONNECT_RETRY=10;
    16. MariaDB [(none)]> START SLAVE;

    4）从节点的从节点2

    1. [root@slave3 ~]# vim /etc/my.cnf
    2. [mysqld]
    3. read_only=ON
    4. server_id=4
    5. relay_log=relay-log
    6. relay_log_index=relay-log.index
    7. [root@slave3 ~]# systemctl start mariadb
    8. MariaDB [(none)]> CHANGE MASTER TO
    9.     -> MASTER_HOST='192.168.0.8',
    10.     -> MASTER_USER='repluser',
    11.     -> MASTER_PASSWORD='replpass',
    12.     -> MASTER_PORT=3306,
    13.     -> MASTER_LOG_FILE='mariadb-bin.000001',
    14.     -> MASTER_LOG_POS=245,
    15.     -> MASTER_CONNECT_RETRY=10;
    16. MariaDB [(none)]> START SLAVE;
    17. 到此已经搭建好了级联复制，接下来测试一下把~

    四、主主复制架构

    容易产生的问题：数据不一致，因此慎用；考虑要点：自动增长id
    配置一个节点使用奇数id
    auto_increment_offset=1 开始点
    auto_increment_increment=2 增长幅度
    另一个节点使用偶数id
    auto_increment_offset=2
    auto_increment_increment=2

    1）主1

    1. [mysqld]
    2. log_bin
    3. binlog_format=ROW
    4. log-basename=master1
    5. server_id=1
    6. relay_log=relay-log
    7. relay_log_index=relay-log.index
    8. auto_increment_offset=1  #自增长字段从1开始
    9. auto_increment_increment=2  #每次增长2，也就是说master1节点写入的数据的id字段全部是奇数
    10. [root@master ~]# systemctl start mariadb
    11. MariaDB [(none)]> SHOW MASTER LOGS;
    12. +--------------------+-----------+
    13. | Log_name           | File_size |
    14. +--------------------+-----------+
    15. | master1-bin.000001 |     27033 |
    16. | master1-bin.000002 |    942126 |
    17. | master1-bin.000003 |       245 |
    18. +--------------------+-----------+
    19. MariaDB [(none)]> GRANT REPLICATION SLAVE ON *.* TO repluser@'192.168.0.%' IDENTIFIED BY 'replpass';
    20. MariaDB [(none)]> CHANGE MASTER TO
    21.     -> MASTER_HOST='192.168.0.8',
    22.     -> MASTER_USER='repluser',
    23.     -> MASTER_PASSWORD='replpass',
    24.     -> MASTER_PORT=3306,
    25.     -> MASTER_LOG_FILE='master2-bin.000003',
    26.     -> MASTER_LOG_POS=245,
    27.     -> MASTER_CONNECT_RETRY=10;
    28. MariaDB [(none)]> START SLAVE;

    2）主2

    1. [mysqld]
    2. log_bin
    3. binlog_format=ROW
    4. log-basename=master2
    5. server_id=2
    6. relay_log=relay-log
    7. relay_log_index=relay-log.index
    8. auto_increment_offset=2  #自增长字段从1开始
    9. auto_increment_increment=2  #每次增长2，也就是说master1节点写入的数据的id字段全部是偶数
    10. [root@master2 ~]# systemctl start mariadb
    11. MariaDB [(none)]> SHOW MASTER LOGS;
    12. +--------------------+-----------+
    13. | Log_name           | File_size |
    14. +--------------------+-----------+
    15. | master2-bin.000001 |     27036 |
    16. | master2-bin.000002 |    942126 |
    17. | master2-bin.000003 |       245 |
    18. +--------------------+-----------+
    19. MariaDB [(none)]> GRANT REPLICATION SLAVE ON *.* TO repluser@'192.168.0.%' IDENTIFIED BY 'replpass';
    20. MariaDB [(none)]> CHANGE MASTER TO
    21.     -> MASTER_HOST='192.168.0.7',
    22.     -> MASTER_USER='repluser',
    23.     -> MASTER_PASSWORD='replpass',
    24.     -> MASTER_PORT=3306,
    25.     -> MASTER_LOG_FILE='master1-bin.000003',
    26.     -> MASTER_LOG_POS=245,
    27.     -> MASTER_CONNECT_RETRY=10;
    28. MariaDB [(none)]> START SLAVE;

    3）测试

    1. 在master1上创建表，增加数据
    2. MariaDB [(none)]> CREATE DATABASE db1;
    3. MariaDB [(none)]> use db1
    4. MariaDB [db1]> CREATE TABLE t1(id INT(2) AUTO_INCREMENT PRIMARY KEY,name CHAR(30));
    5. MariaDB [db1]> INSERT t1(name) VALUES ('tom');
    6. MariaDB [db1]> INSERT t1(name) VALUES ('maria'); 
    7. MariaDB [db1]> SELECT * FROM t1;
    8. +----+-------+
    9. | id | name  |
    10. +----+-------+
    11. |  1 | tom   |
    12. |  3 | maria |
    13. +----+-------+
    14. 在master2上增加数据
    15. MariaDB [db1]> INSERT t1(name) VALUES ('jerry');
    16. MariaDB [db1]> INSERT t1(name) VALUES ('tony'); 
    17. MariaDB [db1]> SELECT * FROM t1;
    18. +----+-------+
    19. | id | name  |
    20. +----+-------+
    21. |  1 | tom   |
    22. |  3 | maria |
    23. |  4 | jerry |
    24. |  6 | tony  |

    五、半同步复制的实现

    ​ 默认情况下，MySQL的复制功能是异步的，异步复制可以提供最佳的性能，主库把binlog日志发送给从库即结束，并不验证从库是否接收完毕。这意味着当主服务器或从服务器端发生故障时，有可能从服务器没有接收到主服务器发送过来的binlog日志，这就会造成主服务器和从服务器的数据不一致，甚至在恢复时造成数据的丢失；半同步复制的机制是只有当主节点和从节点同步完成，仅有一台同步完成即可，返回写入完成，这样的机制保证了数据的安全性。

    1）主节点

    1. [root@master ~]# vim /etc/my.cnf
    2. [mysqld]
    3. log_bin
    4. binlog_format=ROW
    5. log-basename=master
    6. server_id=1
    7. relay_log=relay-log
    8. relay_log_index=relay-log.index
    9. [root@master ~]# systemctl restart mariadb
    10. MariaDB [(none)]> GRANT REPLICATION SLAVE ON *.* TO repluser@'192.168.0.%' IDENTIFIED BY 'replpass';
    11. MariaDB [(none)]> SHOW MASTER LOGS;
    12. +-------------------+-----------+
    13. | Log_name          | File_size |
    14. +-------------------+-----------+
    15. | master-bin.000001 |     26753 |
    16. | master-bin.000002 |    921736 |
    17. | master-bin.000003 |       401 |
    18. +-------------------+-----------+
    19. MariaDB [(none)]> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';  #安装模块
    20. MariaDB [(none)]> SET GLOBAL rpl_semi_sync_master_enabled=1;  #开启半同步功能
    21. MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE '%semi%';
    22. +------------------------------------+-------+
    23. | Variable_name                      | Value |
    24. +------------------------------------+-------+
    25. | rpl_semi_sync_master_enabled       | ON    |  #已开启
    26. | rpl_semi_sync_master_timeout       | 10000 |
    27. | rpl_semi_sync_master_trace_level   | 32    |
    28. | rpl_semi_sync_master_wait_no_slave | ON    |
    29. +------------------------------------+-------+
    30. MariaDB [(none)]> SHOW GLOBAL STATUS LIKE '%semi%';
    31. +--------------------------------------------+-------+
    32. | Variable_name                              | Value |
    33. +--------------------------------------------+-------+
    34. | Rpl_semi_sync_master_clients               | 0     |
    35. | Rpl_semi_sync_master_net_avg_wait_time     | 0     |
    36. | Rpl_semi_sync_master_net_wait_time         | 0     |
    37. | Rpl_semi_sync_master_net_waits             | 0     |
    38. | Rpl_semi_sync_master_no_times              | 0     |
    39. | Rpl_semi_sync_master_no_tx                 | 0     |
    40. | Rpl_semi_sync_master_status                | ON    |
    41. | Rpl_semi_sync_master_timefunc_failures     | 0     |
    42. | Rpl_semi_sync_master_tx_avg_wait_time      | 0     |
    43. | Rpl_semi_sync_master_tx_wait_time          | 0     |
    44. | Rpl_semi_sync_master_tx_waits              | 0     |
    45. | Rpl_semi_sync_master_wait_pos_backtraverse | 0     |
    46. | Rpl_semi_sync_master_wait_sessions         | 0     |
    47. | Rpl_semi_sync_master_yes_tx                | 0     |
    48. +--------------------------------------------+-------+

    2）从节点1

    1. [root@slave1 ~]# vim /etc/my.cnf
    2. [mysqld]
    3. read_only=ON
    4. log_bin
    5. binlog_format=ROW
    6. log-basename=slave
    7. server_id=2
    8. relay_log=relay-log
    9. relay_log_index=relay-log.index
    10. [root@slave1 ~]# systemctl restart mariadb
    11. MariaDB [(none)]> CHANGE MASTER TO
    12.     -> MASTER_HOST='192.168.0.7',
    13.     -> MASTER_USER='repluser',
    14.     -> MASTER_PASSWORD='replpass',
    15.     -> MASTER_PORT=3306,
    16.     -> MASTER_LOG_FILE='master-bin.000003',
    17.     -> MASTER_LOG_POS=401,
    18.     -> MASTER_CONNECT_RETRY=10;
    19. MariaDB [(none)]> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
    20. MariaDB [(none)]> SET GLOBAL rpl_semi_sync_slave_enabled=1;
    21. MariaDB [(none)]> START SLAVE;
    22. MariaDB [(none)]> SHOW MASTER LOGS;
    23. +------------------+-----------+
    24. | Log_name         | File_size |
    25. +------------------+-----------+
    26. | slave-bin.000001 |     26753 |
    27. | slave-bin.000002 |    921736 |
    28. | slave-bin.000003 |       245 |
    29. +------------------+-----------+
    30. MariaDB [(none)]> GRANT REPLICATION SLAVE ON *.* TO repluser@'192.168.0.%' IDENTIFIED BY 'replpass';

    3）从节点2

    1. [root@slave2 ~]# vim /etc/my.cnf
    2. [mysqld]
    3. read_only=ON
    4. server_id=3
    5. relay_log=relay-log
    6. relay_log_index=relay-log.index
    7. [root@slave2 ~]# systemctl restart mariadb
    8. MariaDB [(none)]> CHANGE MASTER TO
    9.     -> MASTER_HOST='192.168.0.8',
    10.     -> MASTER_USER='repluser',
    11.     -> MASTER_PASSWORD='replpass',
    12.     -> MASTER_PORT=3306,
    13.     -> MASTER_LOG_FILE='slave-bin.000003',
    14.     -> MASTER_LOG_POS=245,
    15.     -> MASTER_CONNECT_RETRY=10;
    16. MariaDB [(none)]> START SLAVE;

    4）从节点3

    1. [root@slave3 ~]# vim /etc/my.cnf
    2. [mysqld]
    3. read_only=ON
    4. server_id=4
    5. relay_log=relay-log
    6. relay_log_index=relay-log.index
    7. [root@slave3 ~]# systemctl restart mariadb
    8. MariaDB [(none)]> CHANGE MASTER TO
    9.     -> MASTER_HOST='192.168.0.8',
    10.     -> MASTER_USER='repluser',
    11.     -> MASTER_PASSWORD='replpass',
    12.     -> MASTER_PORT=3306,
    13.     -> MASTER_LOG_FILE='slave-bin.000003',
    14.     -> MASTER_LOG_POS=245,
    15.     -> MASTER_CONNECT_RETRY=10;
    16. MariaDB [(none)]> START SLAVE;

    六、加密传输复制的实现

    在默认的主从复制过程或远程连接到MySQL/MariaDB所有的链接通信中的数据都是明文的，外网里访问数据或则复制，存在安全隐患。通过SSL/TLS加密的方式进行复制的方法，来进一步提高数据的安全性

    主服务器开启SSL：[mysqld] 加一行ssl
    主服务器配置证书和私钥；并且创建一个要求必须使用SSL连接的复制账号
    从服务器使用CHANGER MASTER TO 命令时指明ssl相关选项

    正在上传…重新上传取消

    1. MariaDB [(none)]> SHOW VARIABLES LIKE '%ssl%';
    2. +---------------+----------+
    3. | Variable_name | Value    |
    4. +---------------+----------+
    5. | have_openssl  | DISABLED |
    6. | have_ssl      | DISABLED |
    7. | ssl_ca        |          |
    8. | ssl_capath    |          |
    9. | ssl_cert      |          |
    10. | ssl_cipher    |          |
    11. | ssl_key       |          |
    12. +---------------+----------+

    特别提示：在配置之前先检查mysql服务是否支持ssl功能，如果have_ssl的值为'DISABLED'则支持；如果为'NO'则不支持，需要再重新编译安装或者安装具有ssl功能的版本

    1）CA

    1. [root@CA ~]# mkdir /etc/my.cnf.d/ssl/
    2. [root@CA ~]# cd /etc/my.cnf.d/ssl/
    3. [root@CA ssl]# openssl genrsa 2048 > cakey.pem
    4. [root@CA ssl]# openssl req -new -x509 -key cakey.pem -out cacert.pem -days 3650 #自签证书
    5.     Country Name (2 letter code) [XX]:CN
    6.     State or Province Name (full name) []:beijing
    7.     Locality Name (eg, city) [Default City]:beijing
    8.     Organization Name (eg, company) [Default Company Ltd]:testmysqlca 
    9.     Organizational Unit Name (eg, section) []:opt
    10.     Common Name (eg, your name or your server's hostname) []:ca.testmysqlca.com
    12. [root@CA ssl]# openssl req -newkey rsa:2048 -days 365 -nodes -keyout master.key > master.csr
    13.     Country Name (2 letter code) [XX]:CN
    14.     State or Province Name (full name) []:beijing
    15.     Locality Name (eg, city) [Default City]:beijing
    16.     Organization Name (eg, company) [Default Company Ltd]:testmysqlca
    17.     Organizational Unit Name (eg, section) []:opt
    18.     Common Name (eg, your name or your server's hostname) []:master.testmysqlca.com
    19. [root@CA ssl]# openssl x509 -req -in master.csr -CA cacert.pem -CAkey cakey.pem -set_serial 01 > master.crt #签署master证书
    20.  
    21. [root@CA ssl]# openssl req -newkey rsa:2048 -days 365 -nodes -keyout slave.key > slave.csr
    22.     Country Name (2 letter code) [XX]:CN
    23.     State or Province Name (full name) []:beijing
    24.     Locality Name (eg, city) [Default City]:beijing
    25.     Organization Name (eg, company) [Default Company Ltd]:testmysqlca 
    26.     Organizational Unit Name (eg, section) []:opt
    27.     Common Name (eg, your name or your server's hostname) []:slave.testmysqlca.com
    28. [root@CA ssl]# openssl x509 -req -in slave.csr -CA cacert.pem -CAkey cakey.pem -set_serial 02 > slave.crt #签署slave证书
    30. [root@CA ssl]# openssl req -newkey rsa:2048 -days 365 -nodes -keyout slave2.key > slave2.csr
    31. Country Name (2 letter code) [XX]:CN
    32. State or Province Name (full name) []:beijing
    33. Locality Name (eg, city) [Default City]:beijing
    34. Organization Name (eg, company) [Default Company Ltd]:testmysqlca
    35. Organizational Unit Name (eg, section) []:opt
    36. Common Name (eg, your name or your server's hostname) []:slave2.testmysqlca.com
    37. [root@CA ssl]# openssl x509 -req -in slave2.csr -CA cacert.pem -CAkey cakey.pem -set_serial 03 > slave2.crt #签署slave2证书
    38.  
    39. [root@CA ssl]# openssl verify -CAfile cacert.pem master.crt slave.crt slave2.crt #检查证书是否可用
    40. master.crt: OK
    41. slave.crt: OK
    42. slave2.crt: OK
    43. 先在各个节点上创建/etc/my.cnf.d/ssl/文件夹，将各自的证书，CA的证书和各自的秘钥文件复制过去
    44. [root@CA ssl]# scp cacert.pem master.crt master.key root@192.168.0.7:/etc/my.cnf.d/ssl/
    45. [root@CA ssl]# scp cacert.pem slave.crt slave.key root@192.168.0.8:/etc/my.cnf.d/ssl/ 
    46. [root@CA ssl]# scp cacert.pem slave2.crt slave2.key root@192.168.0.9:/etc/my.cnf.d/ssl/

    2）master

    1. [root@master ~]# mkdir /etc/my.cnf.d/ssl/
    2. [root@master ~]# vim /etc/my.cnf
    3. [mysqld]
    4. log_bin
    5. binlog_format=ROW
    6. log-basename=master
    7. server_id=1
    8. ssl #开启ssl功能
    9. ssl-ca=/etc/my.cnf.d/ssl/cacert.pem #指定CA证书的路径
    10. ssl-cert=/etc/my.cnf.d/ssl/master.crt #指定自己的证书的路径
    11. ssl-key=/etc/my.cnf.d/ssl/master.key #指定自己的秘钥文件路径
    12. [root@master ~]# systemctl restart mariadb
    13. MariaDB [(none)]> GRANT REPLICATION SLAVE ON *.* TO repluser@'192.168.0.%' IDENTIFIED BY 'replpass' REQUIRE SSL; #授权用户并且强制迫使用户开启ssl登录
    14. MariaDB [(none)]> SHOW MASTER LOGS;
    15. +-------------------+-----------+
    16. | Log_name          | File_size |
    17. +-------------------+-----------+
    18. | master-bin.000001 |     26753 |
    19. | master-bin.000002 |    921736 |
    20. | master-bin.000003 |       413 |
    21. +-------------------+-----------+

    3）slave1

    1. [root@slave1 ~]# mkdir /etc/my.cnf.d/ssl/
    2. [root@slave1 ~]# mysql -urepluser -preplpass -h192.168.0.7 --ssl-ca=/etc/my.cnf.d/ssl/cacert.pem --ssl-cert=/etc/my.cnf.d/ssl/slave.crt --ssl-key=/etc/my.cnf.d/ssl/slave.key
    3. [root@slave1 ~]# vim /etc/my.cnf
    4. [mysqld]
    5. read_only=ON
    6. server_id=2
    7. relay_log=relay-log
    8. relay_log_index=relay-log.index
    9. ssl
    10. ssl-ca=/etc/my.cnf.d/ssl/cacert.pem
    11. ssl-cert=/etc/my.cnf.d/ssl/slave.crt
    12. ssl-key=/etc/my.cnf.d/ssl/slave.key
    13. [root@slave1 ~]# systemctl restart mariadb
    14. MariaDB [(none)]> CHANGE MASTER TO
    15.     -> MASTER_HOST='192.168.0.7',
    16.     -> MASTER_USER='repluser',
    17.     -> MASTER_PASSWORD='replpass',
    18.     -> MASTER_PORT=3306,
    19.     -> MASTER_LOG_FILE='master-bin.000003',
    20.     -> MASTER_LOG_POS=413,
    21.     -> MASTER_CONNECT_RETRY=10,
    22.     -> MASTER_SSL=1;  #注意，需要指明开启ssl链接
    23. MariaDB [(none)]> START SLAVE;

    4）slave2

    1. [root@slave2 ~]# mkdir /etc/my.cnf.d/ssl/
    2. [root@slave2 ~]# mysql -urepluser -preplpass -h192.168.0.7 --ssl-ca=/etc/my.cnf.d/ssl/cacert.pem --ssl-cert=/etc/my.cnf.d/ssl/slave2.crt --ssl-key=/etc/my.cnf.d/ssl/slave2.key
    3. [root@slave2 ~]# vim /etc/my.cnf
    4. [mysqld]
    5. read_only=ON
    6. server_id=3
    7. relay_log=relay-log
    8. relay_log_index=relay-log.index
    9. ssl
    10. ssl-ca=/etc/my.cnf.d/ssl/cacert.pem
    11. ssl-cert=/etc/my.cnf.d/ssl/slave2.crt
    12. ssl-key=/etc/my.cnf.d/ssl/slave2.key
    13. [root@slave2 ~]# systemctl restart mariadb
    14. MariaDB [(none)]> CHANGE MASTER TO
    15.     -> MASTER_HOST='192.168.0.7',
    16.     -> MASTER_USER='repluser',
    17.     -> MASTER_PASSWORD='replpass',
    18.     -> MASTER_PORT=3306,
    19.     -> MASTER_LOG_FILE='master-bin.000003',
    20.     -> MASTER_LOG_POS=413,
    21.     -> MASTER_CONNECT_RETRY=10,
    22.     -> MASTER_SSL=1;
    23. MariaDB [(none)]> START SLAVE;

    七、MySQL复制的相关指令和变量总结

    选项：

    • log_bin 启用二进制日志，在主节点或级联复制中间的从节点必须要开启
    • binlog_format=ROW 二进制日志记录方式为基于行的方式记录，强烈建议开启
    • log-basename=master | slave ... 二进制日志的前缀名，不是必须向，但建议标识
    • server_id = # 服务器ID，各个节点的ID必须唯一
    • relay_log = relay-log 开启中继日志，并以relay-log为文件名开头，从节点开启
    • relay_log_index = relay-log.index 中继日志索引文件
    • log_slave_updates 作用是SQL线程重读中继日志时将改变数据的操作记录为二进制日志，在级联复制中使用
    • ssl 开启ssl功能
      • ssl-ca=/etc/my.cnf.d/ssl/cacert.pem
      • ssl-cert=/etc/my.cnf.d/ssl/master.crt
      • ssl-key=/etc/my.cnf.d/ssl/master.key
    • sync_binlog=1 每次写后立即同步二进制日志到磁盘
      • innodb_flush_log_at_trx_commit=1 每次事务提交立即同步日志写磁盘
      • sync_master_info=# #次事件后master.info同步到磁盘
    • skip_slave_start=ON 不自动启动slave
    • sync_relay_log=# #次写后同步relay log到磁盘
    • sync_relay_log_info=# #次事务后同步relay-log.info到磁盘
    • auto_increment_offset=1 自动增长开始点，在主主复制中使用
    • auto_increment_increment=2 增长幅度

    变量：

    • replicate_do_db= 指定复制库的白名单
    • replicate_ignore_db= 指定复制库黑名单
    • replicate_do_table= 指定复制表的白名单
    • replicate_ignore_table= 指定复制表的黑名单
    • replicate_wild_do_table= foo%.bar% 支持通配符

    • replicate_wild_ignore_table= 指定复制的表，黑名单

    • rpl_semi_sync_slave_enabled=1 开启半同步复制，需要安装模块

    指令：

    • START SLAVE; 启动主从复制
    • STOP SLAVE; 停止复制
    • SHOW SLAVE STATUS; 查看复制状态
      • Seconds_Behind_Master: 0 从服务器是否落后于主服务
    • RESET SLAVE ALL; 重置从服务器的配置
    • MASTER_SSL=1, 配合 CHANGE MASTER TO 使用，开启ssl加密复制
      • MASTER_SSL_CA = '/etc/my.cnf.d/ssl/cacert.pem',
      • MASTER_SSL_CERT = '/etc/my.cnf.d/ssl/slave.crt',
      • MASTER_SSL_KEY = '/etc/my.cnf.d/ssl/slave.key';
    • PURGE { BINARY | MASTER } LOGS { TO 'log_name' | BEFORE datetime_expr } 删除二进制日志，谨慎操作
    • SHOW MASTER STATUS 查看二进制日志状态
    • SHOW BINLOG EVENTS 查看二进制日志
    • SHOW BINARY LOGS 查看二进制日志
    • SHOW PROCESSLIST 查看进程
  • 相关阅读:
    JAVA如何处理各种批量数据入库(BlockingQueue)
    Springboot数据库访问JPA
    python打开浏览器并模拟搜索
    【深度学习 | 计算机视觉】Focal Loss原理及其实践（含源代码）
    基于智能优化算法的机器人路径优化（Matlab代码实现）
    C语言变量与常量
    行走的offer收割机，这份十万字Java面试总结已经帮助上百人拿到大厂offer，还不快冲？
    CSS Grid Layout(网格布局)
    语法基础(函数)
    Python数据容器——列表、元组、字符串、集合、字典
  • 原文地址：https://blog.csdn.net/m0_62089210/article/details/126714483