首页 > 资讯 > 数据库 >MySQL5.7--------基于无损复制搭建主从

837

分享到

MySQL5.7--------基于无损复制搭建主从

2024-04-02 19:04:59 837人浏览独家记忆

摘要

1. 背景 * Mysql Replication默认都是异步（asynchronous），当主库在执行完一些事务后，是不会管备库的进度的。如果备库不幸落后，而更不幸的是主库此时又出

1. 背景

* Mysql Replication默认都是异步（asynchronous），当主库在执行完一些事务后，是不会管备库的进度的。如果备库不幸落后，而更不幸的是主库此时又出现Crash（例如宕机），这时备库中的数据就是不完整的。简而言之，在主库发生故障的时候，我们无法使用备库来继续提供数据一致的服务了。

* Semi sync Replication(半同步复制)是在master上提交完成后，再传送到slave等待ack应答，仅仅在一定情况下事务的已经传递到一个slave上，但是并不确保已经在备库上执行完成，会造成最后一次events的主备不一致。

* lossless replication(无损复制)是在master提前过程中，传送到slave中等待应答。当至少一个slave request bilog后写入到relay-log并flush disk，就返回ack

MySQL5.7--------基于无损复制搭建主从

2. lossless replication传输过程

MySQL5.7--------基于无损复制搭建主从

3. 环境

* master 实例环境

mysql> system cat /etc/redhat-release
Centos release 6.8 (Final)

mysql> system ifconfig eth0  | sed -rn '2s#^.*addr:(.*)  Bca.*$#\1#gp'
172.18.0.1

mysql> show variables like 'version';
+---------------+------------+
| Variable_name | Value      |
+---------------+------------+
| version       | 5.7.18-log |
+---------------+------------+
1 row in set (0.00 sec)

* slave 实例环境

mysql> system cat /etc/redhat-release
CentOS release 6.8 (Final)

mysql> system ifconfig eth0  | sed -rn '2s#^.*addr:(.*)  Bca.*$#\1#gp'
172.18.4.1

mysql> show variables like 'version';
+---------------+------------+
| Variable_name | Value      |
+---------------+------------+
| version       | 5.7.18-log |
+---------------+------------+
1 row in set (0.00 sec)

* master 实例my.cnf文件

[mysqld]
########basic settings########
# 主从server-id一定要设置不同
server-id = 110
port = 3306
user = mysql
bind_address = 0.0.0.0    
character_set_server=utf8mb4
skip_name_resolve = 1
datadir = /data/mysql_data
log_error = error.log
#######replication settings########
master_info_repository = TABLE
relay_log_info_repository = TABLE
# MySQL复制是基于binlog日志的
log_bin = bin.log
sync_binlog = 1
log_slave_updates
# MySQL binlog格式搭建主从时必须设置为row
binlog_fORMat = row
relay_log = relay.log
relay_log_recovery = 1
slave_skip_errors = ddl_exist_errors
######semi sync replication settings########
# 设置插件目录路径
plugin_dir=/usr/local/mysql/lib/plugin
# 加载插件
plugin_load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
# 开启master semi sync replication
rpl_semi_sync_master_enabled = 1
# 开启slave semi sync replication
rpl_semi_sync_slave_enabled = 1
# 等待5秒无ack应答自动切换为异步模式
rpl_semi_sync_master_timeout = 5000
# 开启lossless replication
rpl_semi_sync_master_wait_point= AFTER_SYNC
# 至少有1个slave接收到日志
rpl_semi_sync_master_wait_for_slave_count = 1

* slave 实例my.cnf文件

[mysqld]
########basic settings########
server-id = 210
port = 3306
user = mysql
bind_address = 0.0.0.0
character_set_server=utf8mb4
skip_name_resolve = 1
datadir = /data/mysql_data
log_error = error.log
# slave上开启只读，避免应用误写导致主从数据不一致
read_only = on
super_read_only = on
#######replication settings########
master_info_repository = TABLE
relay_log_info_repository = TABLE
log_bin = bin.log
sync_binlog = 1
log_slave_updates
binlog_format = row
relay_log = relay.log
relay_log_recovery = 1
binlog_gtid_simple_recovery = 1
slave_skip_errors = ddl_exist_errors
######semi sync replication settings########
plugin_dir=/usr/local/mysql/lib/plugin
plugin_load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
loose_rpl_semi_sync_master_enabled = 1
loose_rpl_semi_sync_slave_enabled = 1
loose_rpl_semi_sync_master_timeout = 5000
rpl_semi_sync_master_wait_point = AFTER_SYNC
rpl_semi_sync_master_wait_for_slave_count = 1

4. 搭建无数据基于无损全复制主从 [ master原来无数据 ]

* Master 创建复制所使用的用户 [ 此处ip设置为slave服务IP或者% ]

mysql> grant replication slave on *.* to 'rpl'@'172.18.4.1' identified by '123';
Query OK, 0 rows affected, 1 warning (0.00 sec)

* master服务器上查看binlog文件名和日志位置

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

* slave服务器上设置master信息

未开启slave服务时，Slave_IO_Running与Slave_SQL_Running状态成No

master_log_file 设置开始复制文件, master_log_pos 开始文件复制点

mysql> show slave status;          # 未开启复制功能时，slave状态是空的
Empty set (0.00 sec)

mysql> change master to master_host='172.18.0.1',master_user='rpl',master_passWord='123',master_log_file='bin.000002',master_log_pos=689;
Query OK, 0 rows affected, 2 warnings (0.03 sec)

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: 
                  Master_Host: 172.18.0.1
                  Master_User: rpl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: bin.000002
          Read_Master_Log_Pos: 689
               Relay_Log_File: relay.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: bin.000002
             Slave_IO_Running: No
            Slave_SQL_Running: No
              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: 689
              Relay_Log_Space: 154
              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: 
           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: 
1 row in set (0.00 sec)

* 开启slave服务，并查看状态

正常开启slave服务后，Slave_IO_Running与Slave_SQL_Running状态成Yes

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.18.0.1
                  Master_User: rpl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: bin.000002
          Read_Master_Log_Pos: 689
               Relay_Log_File: relay.000002
                Relay_Log_Pos: 314
        Relay_Master_Log_File: bin.000002
             Slave_IO_Running: 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: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 689
              Relay_Log_Space: 511
              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: 0
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: 110
                  Master_UUID: d7d5a01b-6ea0-11e7-9773-00163e0432c5
             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 more 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: 
1 row in set (0.00 sec)

* Master上查看Slave连接信息

mysql> show slave hosts;
+-----------+------+------+-----------+--------------------------------------+
| Server_id | Host | Port | Master_id | Slave_UUID                           |
+-----------+------+------+-----------+--------------------------------------+
|       210 |      | 3306 |       110 | 499ecfb3-6ea2-11e7-aec1-00163e028c02 |
+-----------+------+------+-----------+--------------------------------------+
1 row in set (0.00 sec)

* Master上操作创建数据库与表，并插入数据

mysql> create database mytest character set utf8mb4;
Query OK, 1 row affected (0.01 sec)

mysql> use mytest;
Database changed
mysql> create table users(
    -> id BIGINT NOT NULL AUTO_INCREMENT,
    -> name VARCHAR(255) NOT NULL,
    -> sex ENUM('M', 'F') NOT NULL DEFAULT 'M',
    -> age INT SIGNED NOT NULL DEFAULT '0',
    -> PRIMARY KEY (id)
    -> )ENGINE=INNODB DEFAULT CHARSET=utf8mb4;
Query OK, 0 rows affected (0.02 sec)

mysql> insert into users values(null, 'tom', 'M', 24), (null, 'jak', 'F', 32), (null, 'sea', 'M', 35), (null, 'lisea', 'M', 29);
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from users;
+----+-------+-----+-----+
| id | name  | sex | age |
+----+-------+-----+-----+
|  1 | tom   | M   |  24 |
|  2 | jak   | F   |  32 |
|  3 | sea   | M   |  35 |
|  4 | lisea | M   |  29 |
+----+-------+-----+-----+
4 rows in set (0.00 sec)

* Slave上查看

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

mysql> use mytest;
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> show tables;
+------------------+
| Tables_in_mytest |
+------------------+
| users            |
+------------------+
1 row in set (0.00 sec)

mysql> select * from users;
+----+-------+-----+-----+
| id | name  | sex | age |
+----+-------+-----+-----+
|  1 | tom   | M   |  24 |
|  2 | jak   | F   |  32 |
|  3 | sea   | M   |  35 |
|  4 | lisea | M   |  29 |
+----+-------+-----+-----+
4 rows in set (0.00 sec)

5. 搭建有数据基于无损全复制主从 [ master原来有数据 ]

* 查看mytest库内容

Database changed
mysql> show tables;
+------------------+
| Tables_in_mytest |
+------------------+
| users            |
+------------------+
1 row in set (0.00 sec)

mysql> select * from users;
+----+-------+-----+-----+
| id | name  | sex | age |
+----+-------+-----+-----+
|  1 | tom   | M   |  24 |
|  2 | jak   | F   |  32 |
|  3 | sea   | M   |  35 |
|  4 | lisea | M   |  29 |
+----+-------+-----+-----+
4 rows in set (0.00 sec)

* 使用mysqldump原子导出master库数据，并记录binlog [ 测试只有mytest库 ]

如果有多个库，-B参数后逗号分隔。

[root@master ~]# mysqldump --single-transaction --master-data -B mytest -uroot -p > mytest.sql
Enter password:

* 将导出的备份文件mytest.sql传输到slave

[root@master ~]# scp ./mytest.sql root@172.18.4.1:/root

* slave创建相同的数据库，并将备份导入

mysql> create database mytest character set utf8mb4;
Query OK, 1 row affected (0.01 sec)

[root@slave ~]# mysql -uroot -p mytest < mytest.sql 
Enter password:

* Master 创建复制所使用的用户 [ 此处ip设置为slave服务IP或者% ]

mysql> grant replication slave on *.* to 'rpl'@'172.18.4.1' identified by '123';
Query OK, 0 rows affected, 1 warning (5.01 sec)

* 查看备份文件mytest.sql查看binlog文件名和日志位置

[root@slave ~]# grep 'CHANGE MASTER TO' mytest.sql 
CHANGE MASTER TO MASTER_LOG_FILE='bin.000002', MASTER_LOG_POS=1575;

* slave服务器上设置master信息

未开启slave服务时，Slave_IO_Running与Slave_SQL_Running状态成No

mysql> show slave status;        # 未开启复制功能时，slave状态是空的
Empty set (0.00 sec)

mysql> change master to master_host='172.18.0.1',master_user='rpl',master_password='123',master_log_file='bin.000002',master_log_pos=1575;
Query OK, 0 rows affected, 2 warnings (0.02 sec)

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: 
                  Master_Host: 172.18.0.1
                  Master_User: rpl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: bin.000002
          Read_Master_Log_Pos: 1575
               Relay_Log_File: relay.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: bin.000002
             Slave_IO_Running: No
            Slave_SQL_Running: No
              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: 1575
              Relay_Log_Space: 154
              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: 
           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: 
1 row in set (0.00 sec)

* 开启slave服务，并查看状态

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

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.18.0.1
                  Master_User: rpl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: bin.000002
          Read_Master_Log_Pos: 1872
               Relay_Log_File: relay.000002
                Relay_Log_Pos: 611
        Relay_Master_Log_File: bin.000002
             Slave_IO_Running: 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: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 1872
              Relay_Log_Space: 808
              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: 0
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: 110
                  Master_UUID: d7d5a01b-6ea0-11e7-9773-00163e0432c5
             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 more 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: 
1 row in set (0.00 sec)

* master上mytest库数据操作

mysql> select * from mytest.users;
+----+-------+-----+-----+
| id | name  | sex | age |
+----+-------+-----+-----+
|  1 | tom   | M   |  24 |
|  2 | jak   | F   |  32 |
|  3 | sea   | M   |  35 |
|  4 | lisea | M   |  29 |
+----+-------+-----+-----+
4 rows in set (0.00 sec)

mysql> insert into mytest.users select null, 'test', 'M', 42;
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> update mytest.users set name='seasea'  where id = 3;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from mytest.users;
+----+--------+-----+-----+
| id | name   | sex | age |
+----+--------+-----+-----+
|  1 | tom    | M   |  24 |
|  2 | jak    | F   |  32 |
|  3 | seasea | M   |  35 |
|  4 | lisea  | M   |  29 |
|  5 | test   | M   |  42 |
+----+--------+-----+-----+
5 rows in set (0.00 sec)

* slave上查看

mysql> select * from mytest.users;
+----+--------+-----+-----+
| id | name   | sex | age |
+----+--------+-----+-----+
|  1 | tom    | M   |  24 |
|  2 | jak    | F   |  32 |
|  3 | seasea | M   |  35 |
|  4 | lisea  | M   |  29 |
|  5 | test   | M   |  42 |
+----+--------+-----+-----+
5 rows in set (0.00 sec)

6. 总结

以需求驱动技术，技术本身没有优略之分，只有业务之分。

您可能感兴趣的文档:

--结束END--

本文标题: MySQL5.7--------基于无损复制搭建主从

本文链接: https://lsjlt.com/news/43964.html(转载时请注明来源链接)

有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341

回答

如何调试操作系统的错误？
操作系统

2023-11-15发布

回答

操作系统中的I/O系统是如何实现的？
操作系统

2023-11-15发布

回答

如何实现操作系统的内存管理？
操作系统

2023-11-15发布

回答

什么是虚拟内存，它对操作系统有什么影响？
操作系统

2023-11-15发布

回答

ASP中的MVC架构和WebForms架构有什么区别和使用场景？
ASP.NET

2023-11-15发布

回答

ASP中的数据验证和数据校验有什么不同？
ASP.NET

2023-11-15发布

回答

ASP中的ADO对象和DAO对象有什么区别和使用方法？
ASP.NET

2023-11-15发布

回答

Node.js中的包管理器NPM是什么？如何使用它进行依赖管理？
node.js

2023-11-15发布

回答

Vue.js中的动态组件是什么？如何使用它来动态渲染组件？
VUE

2023-11-15发布

回答

如何使用Vue.js实现懒加载和预加载？
VUE

2023-11-15发布

MySQL5.7--------基于无损复制搭建主从

MySQL5.7--------基于无损复制搭建主从

MySQL5.7主从复制-GTID复制搭建

MySQL5.7主从复制-异步复制搭建

基于GTID的主从复制搭建

无损复制（mysql5.7）

Mysql5.7中如何搭建主从复制

MySQL 5.7 基于GTID搭建主从复制

Docker搭建MySQL5.7主从复制的实现

在Mysql5.7中如何搭建主从复制

如何搭建基于Docker的MySQL主从复制

mysql5.7 主从复制

通过 mysqldump 搭建基于 gtid MySQL 5.7 主从复制

mysql5.7搭建主从库

MySQL基于GTID主从搭建

基于GTID 的主从搭建

Mysql主从复制搭建

centos7 maiadb主从复制搭建

MongoDB 主从复制集搭建

逻辑复制主从搭建

Docker搭建Mysql主从复制

关于SQL建表语句使用详解

HBase在大数据审计与合规性追踪中的应用

MySQL与HBase在大数据金融分析中的性能与可扩展性对比

HBase的Region Server之间的网络通信优化

HBase在大数据监控与告警系统中的实时数据处理能力

MySQL与HBase在大数据安全策略中的实现与对比

HBase的分布式事务处理在复杂业务场景中的应用

MySQL与HBase在混合存储架构中的整合策略

HBase如何支持高效的二级索引查询

MySQL与HBase在物联网数据收集与处理中的协作模式