mysql主从复制集群

机器学习小弟

已于 2023-06-09 11:52:01 修改

阅读量348

点赞数

文章标签： mysql 服务器数据库

于 2023-06-01 20:09:51 首次发布

本文链接：https://blog.csdn.net/weixin_65643437/article/details/130995122

版权

mysql主从复制集群

前言
mysql主从复制
gtid模式
- - master：
  - slave：
半同步模式
- - master：
  - slave:
延迟复制
慢查询
并行复制

前言

MySQL主从复制是一种将数据库的更改从一台服务器复制到另一台服务器的过程。这有助于减轻主服务器的负载并提高可用性。在主服务器上进行更改时，这些更改将被记录并传输到从服务器上，从服务器上也会应用这些更改，以便两个服务器保持同步。

以下是MySQL主从分离的步骤：

1.在主服务器上启用二进制日志（binlog）记录。可以在MySQL配置文件中将“log-bin”选项设置为二进制日志的名称和位置。

2.创建一个用于从服务器连接的用户，并将其授予“REPLICATION SLAVE”权限。

3.选择一台从服务器，并在其MySQL配置文件中将“server-id”选项的值设置为与主服务器不同的唯一值。

4.在从服务器上运行“CHANGE MASTER TO”命令，以告诉MySQL从哪个主服务器复制数据。需要指定主服务器的IP地址、用户名、密码等信息。

5.在从服务器上启用从服务器线程，这样从服务器就可以开始复制主服务器上的数据了。

完成上述步骤后，主服务器上的所有更改都会在复制到从服务器上，并且从服务器上的数据与主服务器保持同步。需要注意的是，在主从复制中，从服务器的数据是只读的，不能在从服务器上进行写操作。

mysql主从复制

再添加一台mysql

直接拷贝server1上编译好的mysql

[root@server1 local]# scp -r /usr/local/mysql/ server2:/usr/local/

在server2上启动mysql
[root@server2 ~]# cd /usr/local/mysql/
[root@server2 mysql]# cp support-files/mysql.server  /etc/init.d/mysqld

[root@server2 support-files]# mkdir -p /data/mysql

创建mysql用户，和server1上保持一致

[root@server2 support-files]# groupadd -g 1001 mysql
[root@server2 support-files]# useradd -M -d /usr/local/mysql/ -s /sbin/nologin -g mysql -u 1001 mysql
[root@server2 support-files]# id mysql
uid=1001(mysql) gid=1001(mysql) groups=1001(mysql)

[root@server2 ~]# vim .bash_profile
PATH=$PATH:$HOME/bin:/usr/local/mysql/bin

[root@server2 ~]# source .bash_profile

[root@server2 ~]# vim /etc/my.cnf
[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock

[root@server2 ~]# mysqld --initialize --user=mysql
2022-12-21T07:13:52.852561Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2022-12-21T07:13:53.087152Z 0 [Warning] InnoDB: New log files created, LSN=45790
2022-12-21T07:13:53.121834Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2022-12-21T07:13:53.179222Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 06766605-80ff-11ed-bb21-000c29eccb66.
2022-12-21T07:13:53.180533Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2022-12-21T07:13:53.447522Z 0 [Warning] A deprecated TLS version TLSv1 is enabled. Please use TLSv1.2 or higher.
2022-12-21T07:13:53.447538Z 0 [Warning] A deprecated TLS version TLSv1.1 is enabled. Please use TLSv1.2 or higher.
2022-12-21T07:13:53.448083Z 0 [Warning] CA certificate ca.pem is self signed.
2022-12-21T07:13:53.476330Z 1 [Note] A temporary password is generated for root@localhost: tddM5k<rWgue

[root@server2 ~]# mysql_secure_installation

[root@server2 ~]# mysql -pwestos
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 5
Server version: 5.7.36 Source distribution

Copyright (c) 2000, 2021, 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           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

master配置

[root@server1 ~]# vim /etc/my.cnf
[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
log-bin=mysql-bin
server-id=1

[root@server1 ~]# /etc/init.d/mysqld restart

[root@server1 ~]# mysql -pwestos

mysql>  CREATE USER 'repl'@'%' IDENTIFIED BY 'westos';
Query OK, 0 rows affected (0.01 sec)

mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      595 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| westos             |
+--------------------+
5 rows in set (0.01 sec)

备份westos数据库

[root@server1 ~]# mysqldump -pwestos westos > dump.sql

拷贝到server2

[root@server1 ~]# scp dump.sql  server2:

slave配置

在server2上还原dump.sql

[root@server2 ~]# mysqladmin create westos -pwestos
[root@server2 ~]# mysql -pwestos westos < dump.sql

[root@server2 ~]# vim /etc/my.cnf
[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
server-id=2

[root@server2 ~]# /etc/init.d/mysqld restart

[root@server2 ~]# mysql -pwestos

mysql>  CHANGE MASTER TO MASTER_HOST='192.168.56.170', MASTER_USER='repl', MASTER_PASSWORD='westos', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=595;
Query OK, 0 rows affected, 2 warnings (0.03 sec)
#这两个值需要和show master status;输出保持一致


mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.56.170
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 595
               Relay_Log_File: server2-relay-bin.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes			#两个yes表示成功
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:

测试
在master上插入 数据
[root@server1 ~]# mysql -pwestos
mysql> use westos

mysql> insert into user_tb values ('user3','3333');
Query OK, 1 row affected (0.00 sec)

mysql> select * from user_tb;
+----------+----------+
| username | password |
+----------+----------+
| user1    | 123      |
| user1    | 123      |
| user2    | 456      |
| user3    | 3333     |
+----------+----------+
4 rows in set (0.00 sec)

在slave上查看数据是否自动同步
mysql> use westos
mysql> select * from user_tb;
+----------+----------+
| username | password |
+----------+----------+
| user1    | 123      |
| user1    | 123      |
| user2    | 456      |
| user3    | 3333     |
+----------+----------+
4 rows in set (0.00 sec)

gtid模式

GTID是MySQL全局事务标识符（Global Transaction ID）的简称，是MySQL 5.6版本引入的概念。它是用来替代传统的 binlog file 和 position 的复制方式的，是一种更为可靠的复制方式。

GTID模式可以实现基于事件的复制（statement-based replication）和基于行的复制（row-based replication）。与传统的 binlog file 和 position 的复制方式相比，GTID具有以下优点：

更加简单和安全。不再需要对Binlog文件名和Position进行管理和维护，降低了管理员的工作难度和出错率。
支持多源复制。不管是单主或多主，只要使用相同的 GTID 模式服务，就可以在不使用任何第三方工具的情况下实现数据的跨服务器同步。
更高效。通过 GTID 模式的下发可以减少在解析 MySQL binlog 的时候出现的死循环的产生，从而提高了性能。

在 GTID 模式中，每个事务都有一个全局唯一的 GTID 值标识，这个值是由一个服务器生成，然后被所有的服务器在其复制过程中进行识别的。每个 GTID 分为两个部分：server_uuid 和 transaction_id，其中 server_uuid 表示当前主机的标识，transaction_id 表示当前事务的顺序编号。

由于 GTID 是全局唯一的标识符，所以即使在传递中发生主从切换或者增加节点也不会影响执行事务的标识符，也就是说无论服务器的变化如何，GTID 都不会因为复制丢失而失效，可以更加有效防范出现数据不一致的问题。

master：

[root@server1 mysql]# vim /etc/my.cnf
[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
log-bin=mysql-bin
server-id=1
gtid_mode=ON
enforce-gtid-consistency=ON

[root@server1 mysql]# /etc/init.d/mysqld restart

slave：

[root@server2 mysql]# vim /etc/my.cnf
[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
server-id=2
gtid_mode=ON
enforce-gtid-consistency=ON

[root@server2 mysql]# /etc/init.d/mysqld restart

[root@server2 mysql]# mysql -pwestos

mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)

mysql> CHANGE MASTER TO MASTER_HOST='192.168.56.170', MASTER_USER='repl', MASTER_PASSWORD='westos', MASTER_AUTO_POSITION = 1;
Query OK, 0 rows affected, 2 warnings (0.01 sec)

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.56.170
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 154
               Relay_Log_File: server2-relay-bin.000002
                Relay_Log_Pos: 367
        Relay_Master_Log_File: mysql-bin.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

半同步模式

半同步模式（Semi-Synchronous Replication）是MySQL 5.5版本引入的复制模式，它是基于传统的主从复制的基础上进行扩展改进，可以提高复制的可靠性。在半同步复制模式下，当主服务器接收到事务的提交信号（COMMIT）时，它会等待其中至少一个从服务器确认接收到了此事务的 binlog 数据再向客户端返回 OK 响应，从而保证复制的一致性和可靠性。

半同步模式相较于传统的异步复制模式（Asynchronous Replication），有如下的优点：

提高可靠性。半同步复制模式可以有效地避免数据丢失的情况，提高了数据的可靠性和安全性。
增强一致性。半同步复制模式可以保证从服务器数据的一致性，避免了因为不同步的情况导致数据的不一致性。
改进性能。相较于 GTID 复制模式，半同步模式不需要额外的线程用于复制，可以提升主服务器的性能。

但是，半同步复制模式也有一些不足之处：

需要较高的网络带宽。等待从服务器的应答需要一定的时间，在网络质量较差的情况下，可能会导致主服务器的性能下降。
任一从服务器发生故障将导致主服务器阻塞。如果从服务器在向主服务器应答期间发生故障，主服务器可能会一直等待从服务器的响应。
对于写密集型的应用会存在数据同步的延迟问题，增量同步的速度受到主从网络的影响。

半同步模式是一种适用于需要高可靠、数据真实一致性场景的复制。我们需要根据实际情况去权衡它所带来的利弊。

master：

[root@server1 mysql]# mysql -pwestos

mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
Query OK, 0 rows affected (0.00 sec)

mysql> show plugins;

mysql> SET GLOBAL rpl_semi_sync_master_enabled =1;		#写入my.cnf才能永久生效
Query OK, 0 rows affected (0.00 sec)

mysql>  SHOW VARIABLES LIKE 'rpl_semi_sync%';
+-------------------------------------------+------------+
| Variable_name                             | Value      |
+-------------------------------------------+------------+
| rpl_semi_sync_master_enabled              | ON         |
| 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 STATUS LIKE 'Rpl_semi_sync%';
+--------------------------------------------+-------+
| Variable_name                              | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients               | 0     |
| Rpl_semi_sync_master_net_avg_wait_time     | 0     |
| Rpl_semi_sync_master_net_wait_time         | 0     |
| Rpl_semi_sync_master_net_waits             | 0     |
| Rpl_semi_sync_master_no_times              | 0     |
| Rpl_semi_sync_master_no_tx                 | 0     |
| Rpl_semi_sync_master_status                | ON    |
| Rpl_semi_sync_master_timefunc_failures     | 0     |
| Rpl_semi_sync_master_tx_avg_wait_time      | 0     |
| Rpl_semi_sync_master_tx_wait_time          | 0     |
| Rpl_semi_sync_master_tx_waits              | 0     |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0     |
| Rpl_semi_sync_master_wait_sessions         | 0     |
| Rpl_semi_sync_master_yes_tx                | 0     |
+--------------------------------------------+-------+
14 rows in set (0.00 sec)

slave:

[root@server2 mysql]# mysql -pwestos
mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
Query OK, 0 rows affected (0.01 sec)

mysql> SET GLOBAL rpl_semi_sync_slave_enabled=1;
Query OK, 0 rows affected (0.00 sec)

mysql>  SHOW VARIABLES LIKE 'rpl_semi_sync%';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| rpl_semi_sync_slave_enabled     | ON    |
| rpl_semi_sync_slave_trace_level | 32    |
+---------------------------------+-------+
2 rows in set (0.01 sec)

mysql> SHOW STATUS LIKE 'Rpl_semi_sync%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Rpl_semi_sync_slave_status | OFF   |
+----------------------------+-------+
1 row in set (0.00 sec)

mysql> STOP SLAVE IO_THREAD;
Query OK, 0 rows affected (0.00 sec)

mysql> START SLAVE IO_THREAD;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW STATUS LIKE 'Rpl_semi_sync%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Rpl_semi_sync_slave_status | ON    |
+----------------------------+-------+
1 row in set (0.00 sec)

mysql> SHOW STATUS LIKE 'Rpl_semi_sync%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Rpl_semi_sync_slave_status | ON    |
+----------------------------+-------+
1 row in set (0.00 sec)

延迟复制

[root@server2 mysql]# mysql -pwestos

mysql> STOP SLAVE SQL_THREAD;
Query OK, 0 rows affected (0.00 sec)

mysql> CHANGE MASTER TO MASTER_DELAY=60;
Query OK, 0 rows affected (0.00 sec)

mysql> START SLAVE SQL_THREAD;
Query OK, 0 rows affected (0.00 sec)

mysql> show slave status\G;

慢查询

mysql> set  long_query_time=5;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like "long%";
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 5.000000 |
+-----------------+----------+
1 row in set (0.00 sec)

mysql> set global slow_query_log=ON;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like "slow%";
+---------------------+------------------------------+
| Variable_name       | Value                        |
+---------------------+------------------------------+
| slow_launch_time    | 2                            |
| slow_query_log      | ON                           |
| slow_query_log_file | /data/mysql/server1-slow.log |
+---------------------+------------------------------+
3 rows in set (0.01 sec)

mysql> select sleep(6);
+----------+
| sleep(6) |
+----------+
|        0 |
+----------+
1 row in set (6.00 sec)

[root@server1 mysql]# cat server1-slow.log
/usr/local/mysql/bin/mysqld, Version: 5.7.36-log (Source distribution). started with:
Tcp port: 3306  Unix socket: /data/mysql/mysql.sock
Time                 Id Command    Argument
# Time: 2022-12-28T07:41:57.345717Z
# User@Host: root[root] @ localhost []  Id:     6
# Query_time: 6.000533  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0
use westos;
SET timestamp=1672213317;
select sleep(6);

并行复制

salve：
vim /etc/my.cnf
...
slave-parallel-type=LOGICAL_CLOCK
slave-parallel-workers=16
master_info_repository=TABLE
relay_log_info_repository=TABLE
relay_log_recovery=ON


[root@server2 mysql]# /etc/init.d/mysqld restart