暂无图片
暂无图片
暂无图片
暂无图片
暂无图片
首页 / MySQL复制基础练习（八）-复制拓扑调整-扩展从库-纵向扩展

MySQL复制基础练习（八）-复制拓扑调整-扩展从库-纵向扩展

原创 Henry 2024-04-10
178

一、基本信息

1、练习环境信息

OS版本：CentOS Linux release 7.8.2003 (Core)

数据库版本：MySQL 8.0.33

服务器列表：Master：   192.168.106.131           330601 （server-id）

            Slave1:     192.168.106.132           330661

            Slave2：    192.168.106.133           330663

2、拓扑变更情况

原拓扑： 一主一从：  Master——> slave1

调整为： 一主二从  Master——> slave1——> slave2

3、配置文件主要配置

-----Master

server-id=330601

log-bin=/data/mysql/binlog/mysql-bin

binlog-format=ROW

max_binlog_size=128M

gtid-mode=on

enforce-gtid-consistency=on

------slave1

server-id=330661

log-bin=/data/mysql/binlog/mysql-bin

relay-log=/data/mysql/relaylog/DB3-relay-bin

binlog-format=ROW

max_binlog_size=128M

gtid-mode=on

enforce-gtid-consistency=on

------slave2

server-id=330663

log-bin=/data/mysql/3306/binlog/mysql-bin

relay-log=/data/mysql/3306/relaylog/mysql-relay-bin

binlog-format=ROW

max_binlog_size=128M

gtid-mode=on

enforce-gtid-consistency=true

二、详细过程

1、模拟真实生产环境持续写入状态（可忽略）

1.1 sysbench主库持续造数

[root@DB2 ~]# sysbench --db-driver=mysql --time=1666 --report-interval=1 --mysql-host=192.168.106.131 --mysql-port=3307 --mysql-user=sysbench --mysql-password='dsg$1234' --mysql-db=sysbench --tables=8 --table-size=5000 --db-ps-mode=disable oltp_read_write run --threads=1

 

1.2复制状态查看

1）master

root@localhost : (none) 11:27:26>show master status\G;

*************************** 1. row ***************************

             File: mysql-bin.000061

         Position: 63330337

     Binlog_Do_DB:

 Binlog_Ignore_DB:

Executed_Gtid_Set: 4b6fb5b6-1265-11ed-b91b-000c29a77d1d:1-526201

1 row in set (0.00 sec)

root@localhost : (none) 11:27:34>show slave hosts;

+-----------+------+------+-----------+--------------------------------------+

| Server_id | Host | Port | Master_id | Slave_UUID                           |

+-----------+------+------+-----------+--------------------------------------+

|    330661 |      | 3306 |    330601 | f759a28a-876c-11ed-8181-000c29b9004b |

+-----------+------+------+-----------+--------------------------------------+

1 row in set, 1 warning (0.00 sec)

2）slave1

root@localhost : (none) 11:28:34>show slave status\G;

*************************** 1. row ***************************

               Slave_IO_State: Waiting for source to send event

                  Master_Host: 192.168.106.131

                  Master_User: rep1

                  Master_Port: 3306

                Connect_Retry: 60

              Master_Log_File: mysql-bin.000061

          Read_Master_Log_Pos: 89382353

               Relay_Log_File: DB3-relay-bin.000004

                Relay_Log_Pos: 89315841

        Relay_Master_Log_File: mysql-bin.000061

             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: 89315625

              Relay_Log_Space: 89382864

              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: 1

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: 330601

                  Master_UUID: 4b6fb5b6-1265-11ed-b91b-000c29a77d1d

             Master_Info_File: mysql.slave_master_info

                    SQL_Delay: 0

          SQL_Remaining_Delay: NULL

      Slave_SQL_Running_State: Replica 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: 4b6fb5b6-1265-11ed-b91b-000c29a77d1d:448134-541037

            Executed_Gtid_Set: 4b6fb5b6-1265-11ed-b91b-000c29a77d1d:1-541037

                Auto_Position: 1

         Replicate_Rewrite_DB:

                 Channel_Name:

           Master_TLS_Version:

       Master_public_key_path:

        Get_master_public_key: 0

            Network_Namespace:

1 row in set, 1 warning (0.00 sec)

2、搭建从库slave2

2.1在从库slave1上使用xtrabackup备份并传至slave2 

[root@DB3 tmp]#xtrabackup -u sysbench -p -S /tmp/mysql2.sock  --backup --parallel=2 --slave-info --target-dir=/data/mysql2/tmp

[root@DB3 tmp]#  scp -r /data/mysql2/tmp/* root@192.168.106.133:/data/mysql/tmp    

2.2在从库slave2上进行恢复及启动实例

[root@DB4 tmp]#  xtrabackup --prepare --target-dir=/data/mysql/tmp

[root@DB4 tmp]# xtrabackup --defaultes-file=/etc/my.cnf --datadir=/data/mysql/3306/data --copy-back --parallel=2 --target-dir=/data/mysql/tmp

[root@DB4 3306]# chown -R mysql:mysql data binlog/

[root@DB4 3306]# systemctl start mysqld@3306

 

2.3 在从库slave2启动复制

1) 获取GTID信息

从备份临时目录下xtrabackup_binlog_info文件中获取GTID信息

[root@DB4 tmp]# cat xtrabackup_binlog_info

mysql-bin.000051        1946    4b6fb5b6-1265-11ed-b91b-000c29a77d1d:1-758747

2）slave2中查看GTID_PURGED信息

mysql> show global variables where variable_name in ('gtid_executed','gtid_purged');

+---------------+-----------------------------------------------+

| Variable_name | Value                                         |

+---------------+-----------------------------------------------+

| gtid_executed | 4b6fb5b6-1265-11ed-b91b-000c29a77d1d:1-758747 |

| gtid_purged   | 4b6fb5b6-1265-11ed-b91b-000c29a77d1d:1-758746 |

+---------------+-----------------------------------------------+

2 rows in set (0.01 sec)

3) slave2中使用change master to语句配置复制

mysql> change master to master_host='192.168.106.132',master_port=3306,master_user='rep1',master_password='dsg$1234',master_auto_position=1,get_master_public_key=1;

 

Query OK, 0 rows affected, 9 warnings (0.01 sec)

4)启动slave2的复制线程

mysql> reset slave;

Query OK, 0 rows affected, 1 warning (0.01 sec)

 

mysql> start slave;

Query OK, 0 rows affected, 1 warning (0.02 sec)

 

mysql> show slave status\G;

*************************** 1. row ***************************

               Slave_IO_State: Queueing source event to the relay log

                  Master_Host: 192.168.106.132

                  Master_User: rep1

                  Master_Port: 3306

                Connect_Retry: 60

              Master_Log_File: mysql-bin.000051

          Read_Master_Log_Pos: 94996028

               Relay_Log_File: mysql-relay-bin.000003

                Relay_Log_Pos: 2256630

        Relay_Master_Log_File: mysql-bin.000051

             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: 2258156

              Relay_Log_Space: 94994961

              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: 330661

                  Master_UUID: f759a28a-876c-11ed-8181-000c29b9004b

             Master_Info_File: mysql.slave_master_info

                    SQL_Delay: 0

          SQL_Remaining_Delay: NULL

      Slave_SQL_Running_State: Waiting for dependent transaction to commit

           Master_Retry_Count: 86400

                  Master_Bind:

      Last_IO_Error_Timestamp:

     Last_SQL_Error_Timestamp:

               Master_SSL_Crl:

           Master_SSL_Crlpath:

           Retrieved_Gtid_Set: 4b6fb5b6-1265-11ed-b91b-000c29a77d1d:758748-813059

            Executed_Gtid_Set: 4b6fb5b6-1265-11ed-b91b-000c29a77d1d:1-760220

                Auto_Position: 1

         Replicate_Rewrite_DB:

                 Channel_Name:

           Master_TLS_Version:

       Master_public_key_path:

        Get_master_public_key: 1

            Network_Namespace:

1 row in set, 1 warning (0.00 sec)

 

2.4 查看复制状态

1）Slave1

root@localhost : (none) 12:21:35>show slave hosts;

+-----------+------+------+-----------+--------------------------------------+

| Server_id | Host | Port | Master_id | Slave_UUID                           |

+-----------+------+------+-----------+--------------------------------------+

|    330663 |      | 3306 |    330661 | 11225465-f5c3-11ee-bb50-000c29aa12df |

+-----------+------+------+-----------+--------------------------------------+

1 row in set, 1 warning (0.00 sec)

 

root@localhost : (none) 12:23:25>show processlist;

+----+-----------------+-----------------------+------+------------------+------+-----------------------------------------------------------------+------------------+

| Id | User            | Host                  | db   | Command          | Time | State                                                           | Info             |

+----+-----------------+-----------------------+------+------------------+------+-----------------------------------------------------------------+------------------+

|  8 | event_scheduler | localhost             | NULL | Daemon           | 4517 | Waiting on empty queue                                          | NULL             |

| 15 | root            | localhost             | NULL | Query            |    0 | init                                                            | show processlist |

| 16 | system user     | connecting host       | NULL | Connect          | 3677 | Waiting for source to send event                                | NULL             |

| 17 | system user     |                       | NULL | Query            |    0 | Replica has read all relay log; waiting for more updates        | NULL             |

| 18 | system user     |                       | NULL | Query            |   -1 | waiting for handler commit                                      | NULL             |

| 19 | system user     |                       | NULL | Query            | 3643 | Waiting for an event from Coordinator                           | NULL             |

| 20 | system user     |                       | NULL | Connect          | 3677 | Waiting for an event from Coordinator                           | NULL             |

| 21 | system user     |                       | NULL | Connect          | 3677 | Waiting for an event from Coordinator                           | NULL             |

| 33 | rep1            | 192.168.106.133:49418 | NULL | Binlog Dump GTID |   50 | Source has sent all binlog to replica; waiting for more updates | NULL             |

+----+-----------------+-----------------------+------+------------------+------+-----------------------------------------------------------------+------------------+

9 rows in set (0.00 sec)

 

2）Slave2

mysql> show slave status\G;

*************************** 1. row ***************************

               Slave_IO_State: Waiting for disk space

                  Master_Host: 192.168.106.132

                  Master_User: rep1

                  Master_Port: 3306

                Connect_Retry: 60

              Master_Log_File: mysql-bin.000053

          Read_Master_Log_Pos: 35241512

               Relay_Log_File: mysql-relay-bin.000003

                Relay_Log_Pos: 8126274

        Relay_Master_Log_File: mysql-bin.000051

             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: 8127800

              Relay_Log_Space: 303678794

              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: 330661

                  Master_UUID: f759a28a-876c-11ed-8181-000c29b9004b

             Master_Info_File: mysql.slave_master_info

                    SQL_Delay: 0

          SQL_Remaining_Delay: NULL

      Slave_SQL_Running_State: Waiting for dependent transaction to commit

           Master_Retry_Count: 86400

                  Master_Bind:

      Last_IO_Error_Timestamp:

     Last_SQL_Error_Timestamp:

               Master_SSL_Crl:

           Master_SSL_Crlpath:

           Retrieved_Gtid_Set: 4b6fb5b6-1265-11ed-b91b-000c29a77d1d:758748-932375

            Executed_Gtid_Set: 4b6fb5b6-1265-11ed-b91b-000c29a77d1d:1-763557

                Auto_Position: 1

         Replicate_Rewrite_DB:

                 Channel_Name:

           Master_TLS_Version:

       Master_public_key_path:

        Get_master_public_key: 1

            Network_Namespace:

1 row in set, 1 warning (0.00 sec)

 

mysql复制gtid复制扩展从库一主多从
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

评论