011、MySQL主从复制(Replication)部署实战
|
Mysql
|
34

1343 字
|
1.3 小时
本文最后更新于 66 天前，其中的信息可能已经过时，如有错误请发送邮件到wuxianglongblog@163.com

MySQL主从复制(Replication)部署实战

一.MySQL主从复制(Replication)架构概述

1.MySQL主从复制(Replication)架构概述

MySQL主从复制(Replication)简介

    MySQL主从复制(Replication)指的是两台或以上数据库实例服务器，通过二进制日志实现数据的"同步"关系。

    需要注意的是，MySQL主从复制并不是说其工作模式是同步的，实际上，MySQL主从复制(Replication)是一个异步工作模式。

MySQL主从复制(Replication)的前提

    MySQL主从复制(Replication)部署的前提有哪些呢，你是否会想到以下的信息呢?
        (1)MySQL主从复制(Replication)的前提是主库必须开启二进制日志功能;
        (2)至少有两台以上MySQL实例，至少一个主库，至少有一个从库，要有不同角色切换，需要配置"server_id"属性;
        (3)数据库实例之间时间要同步;
        (4)网络要通畅;
        (5)开启专用复制线程;
        (6)确认复制起点，从库需要从主库中拉取数据;
        (7)从库需要复制指定数据库的权限;
        ...

    综上所述，MySQL主从复制(Replication)的前提总结为以下几点:
        (1)需要2台以上数据库实例，时间同步，网络通畅，server_id不同，区分不同角色(即主库和从库);
        (2)主库开启binlog，建立专用复制用户;
        (3)从库需要提前"补课"(将之前"落下"的数据补全);
        (4)从库需要确认主库的链接信息，复制起点等;
        (5)从库需要开启专用的复制线程;

2.MySQL主从复制(Replication)架构中应用的文件和线程资源

主从复制中涉及到的资源

    主库:
        (1)binlog文件
            默认存储在数据目录下，用于存储用户的操作记录，我们可以基于"log_bin_basename"来制定存储的路径及文件名前缀。
                mysql> SELECT @@log_bin;
                +-----------+
                | @@log_bin |
                +-----------+
                |         1 |
                +-----------+
                1 row in set (0.00 sec)

                mysql> 
                mysql> SELECT @@log_bin_basename;
                +---------------------------------------+
                | @@log_bin_basename                    |
                +---------------------------------------+
                | /oldboyedu/data/mysql3308/mysql-bin |
                +---------------------------------------+
                1 row in set (0.00 sec)

                mysql> 
            需要注意的是，我们可以不配置"log_bin_basename"参数，但"log_bin"参数的值必须为1，表示开启状态。否则主从复制将无法进行！

    从库:
        (1)relay-log文件:
            作用:
                用于接收存储binlog，也称为"中继日志"。
            默认存储路径:
                默认存储在数据目录下。
            手动定义存储路径:
                如下所示，我们可以基于"relay_log_basename"来手动指定存储路径。
                    mysql> SELECT @@relay_log_basename;
                    +-------------------------------------------------+
                    | @@relay_log_basename                            |
                    +-------------------------------------------------+
                    | /oldboyedu/data/mysql3308/docker201-relay-bin |
                    +-------------------------------------------------+
                    1 row in set (0.00 sec)

                    mysql> 
                如果我们在配置文件中不指定"relay_log_basename"参数，则默认的文件名为"docker201-relay-bin.000001,docker201-relay-bin.000002,docker201-relay-bin.000003,..."

        (2)master.info文件:
            作用:
                用于存储主库的链接信息，已经接受的binlog位置点信息等数据。
            默认存储路径:
                默认存储在数据目录下。
            手动定义存储路径:
                如下所示，我们可以基于"master_info_repository"来手动指定该文件存储位置。默认以文件方式存储，我们也可以将其存储在MySQL的表中以提升性能。
                    mysql> SELECT @@master_info_repository;
                    +--------------------------+
                    | @@master_info_repository |
                    +--------------------------+
                    | FILE                     |
                    +--------------------------+
                    1 row in set (0.00 sec)

                    mysql> 

        (3)relay-log.info文件:
            作用:
                总所周知，从库会单独开启一个I/O线程从主库拉取二进制日志并存储中继日志(上面提到的"relay-log"文件)中，而后从库开启一个SQL线程基于中继日志进行回放，以达到和主库同样的数据。
                而relay-log.info文件就是用于记录从库回放到relay-log的位置点，这是为了防止从库突然宕机后，重启服务器后知道上一次回放的位置点，而后基于该记录的位置点继续往下执行SQL。
            默认存储路径:
                默认存储在数据目录下。
            手动定义存储路径:
                如下所示，我们可以基于"relay_log_info_repository"来手动指定该文件存储位置。默认以文件方式存储，我们也可以将其存储在MySQL的表中以提升性能。
                    mysql> SELECT @@relay_log_info_repository ;
                    +-----------------------------+
                    | @@relay_log_info_repository |
                    +-----------------------------+
                    | FILE                        |
                    +-----------------------------+
                    1 row in set (0.00 sec)

                    mysql>

主从复制中涉及到的线程资源

    主库:
        binlog_dump线程:
            作用:
                用来接收从库请求，并且投递binlog给从库。
            查看方式:
                mysql> SHOW PROCESSLIST;  # 不难发现有两个从库用户，分配的binlog_dump线程ID为5和6。
                +----+------+---------------------------------+------+-------------+-------+---------------------------------------------------------------+------------------+
                | Id | User | Host                            | db   | Command     | Time  | State                                                         | Info             |
                +----+------+---------------------------------+------+-------------+-------+---------------------------------------------------------------+------------------+
                |  5 | copy | docker201.oldboyedu.com:49590 | NULL | Binlog Dump | 33278 | Master has sent all binlog to slave; waiting for more updates | NULL             |
                |  6 | copy | docker201.oldboyedu.com:49592 | NULL | Binlog Dump | 33274 | Master has sent all binlog to slave; waiting for more updates | NULL             |
                | 10 | root | localhost                       | NULL | Query       |     0 | starting                                                      | SHOW PROCESSLIST |
                +----+------+---------------------------------+------+-------------+-------+---------------------------------------------------------------+------------------+
                3 rows in set (0.00 sec)

                mysql> 

    从库:
        IO线程:
            用于向主库请求，接收和存储binlog日志。
        SQL线程:
            用于回放中继日志(上面提到的"relay-log"文件)，执行"relay-log"文件的SQL语句并将执行的位置点记录在"relay-log.info"文件中。
        查看方式:         
            [root@docker201.oldboyedu.com ~]# mysql -S /tmp/mysql3309.sock -e "SHOW SLAVE STATUS\G" | grep "Running:"
                         Slave_IO_Running: Yes
                        Slave_SQL_Running: Yes
            [root@docker201.oldboyedu.com ~]#

3.MySQL主从复制(Replication)架构原理

MySQL主从复制(Replication)架构原理文字说明

    主从复制原理:
        (1)如下所示，从库执行"CHANGE MASTER TO ..."命令，执行命令成功后会将这些主库的链接信息记录在"master.info"文件中;
            mysql> CHANGE MASTER TO
                ->     MASTER_HOST='172.200.1.201',
                ->     MASTER_USER='copy',
                ->     MASTER_PASSWORD='oldboyedu',
                ->     MASTER_PORT=3307,
                ->     MASTER_LOG_FILE='mysql-bin.000004',
                ->     MASTER_LOG_POS=908,
                ->     MASTER_CONNECT_RETRY=10;
            Query OK, 0 rows affected, 2 warnings (0.31 sec)

            mysql> 

        (2)执行"START SLAVE"命令后，从库会开启IO线程和SQL线程这两个线程，其中IO线程负责发送请求到主库，有关主库的链接信息在"master.info"文件中都有记录;
            [root@docker201.oldboyedu.com ~]# mysql -S /tmp/mysql3309.sock -e "SHOW SLAVE STATUS\G" | grep "Running:"
                         Slave_IO_Running: Yes
                        Slave_SQL_Running: Yes
            [root@docker201.oldboyedu.com ~]# 

        (3)主库分配了一个binlog_dump线程来响应从库的IO线程，我们可以通过"SHOW PROCESSLIST"命令来查看响应的binlog_dump线程;
            mysql> SHOW PROCESSLIST;  # 不难发现有两个从库用户，分配的binlog_dump线程ID为5和6。
            +----+------+---------------------------------+------+-------------+-------+---------------------------------------------------------------+------------------+
            | Id | User | Host                            | db   | Command     | Time  | State                                                         | Info             |
            +----+------+---------------------------------+------+-------------+-------+---------------------------------------------------------------+------------------+
            |  5 | copy | docker201.oldboyedu.com:49590 | NULL | Binlog Dump | 33278 | Master has sent all binlog to slave; waiting for more updates | NULL             |
            |  6 | copy | docker201.oldboyedu.com:49592 | NULL | Binlog Dump | 33274 | Master has sent all binlog to slave; waiting for more updates | NULL             |
            | 10 | root | localhost                       | NULL | Query       |     0 | starting                                                      | SHOW PROCESSLIST |
            +----+------+---------------------------------+------+-------------+-------+---------------------------------------------------------------+------------------+
            3 rows in set (0.00 sec)

            mysql> 

        (4)从库的IO线程会请求新日志，有关向主库请求的日志位置点信息在"master.info"文件中都有记录;

        (5)主库的binlog_dump线程接收从库的IO线程请求后，会截取主库的二进制日志文件，并将结果返回给从库的IO线程;

        (6)从库的IO线程接收到主库的binlog后，日志先发送到网卡的缓存区域中，此时由网络层返回ACK给主库，主库工作完成;

        (7)从库的IO线程最终会将数据从网卡的缓冲区拉取并写入中继日志文件(上面提到的"relay-log"文件)中以落地到本地磁盘，于此同时会更新"master.info"文件中记录的位置点信息，以便于下一次从库IO线程知道从哪个位置点请求"新日志",I/O线程工作完成。

        (8)从库的SQL线程读取"relay-log.info"文件，获取上一次中继日志文件(上面提到的"relay-log"文件)执行到的位置点;

        (9)从库的SQL线程根据上一步获得中继日志文件(上面提到的"relay-log"文件)的位置点后，在该位置点继续向后执行新的"relay-log"日志，而后会更新"relay-log.info"文件，，以便于下一次从库SQL线程知道从哪个位置点读取"新日志"，主从复制流程基本结束。

    主从复制中的一些小细节:
        (1)从库的relay_log_purge如果是开启状态，则从库会定期删除已经应用过的中继日志文件(上面提到的"relay-log"文件)，以节省空间;
            mysql> SELECT @@relay_log_purge;
            +-------------------+
            | @@relay_log_purge |
            +-------------------+
            |                 1 |
            +-------------------+
            1 row in set (0.00 sec)

            mysql> 

        (2)主库的binlog_dump线程会实时监控主库的binlog变化，如果有变化，会发送信号给从库，而后从库会发起IO线程向主库的binlog_dump线程发起请求以拉取新日志。

MySQL主从复制(Replication)架构原理画图说明

    MySQL主从复制(Replication)架构原理如下图所示。

image-20210723121149781

也可以参考下面的图哟~

1632036270739

二.MySQL的主从复制(Replication)部署实战

1.启动MySQL相同版本的多实例

[root@docker201.oldboyedu.com ~]# ss -ntl
State       Recv-Q Send-Q                                                 Local Address:Port                                                                Peer Address:Port              
LISTEN      0      128                                                    172.200.1.201:6379                                                                           *:*                  
LISTEN      0      128                                                                *:22                                                                             *:*                  
LISTEN      0      128                                                             [::]:80                                                                          [::]:*                  
LISTEN      0      128                                                             [::]:22                                                                          [::]:*                  
[root@docker201.oldboyedu.com ~]# 
[root@docker201.oldboyedu.com ~]# systemctl start mysqld3309 mysqld3308 mysqld3307
[root@docker201.oldboyedu.com ~]# 
[root@docker201.oldboyedu.com ~]# ss -ntl
State       Recv-Q Send-Q                                                 Local Address:Port                                                                Peer Address:Port              
LISTEN      0      128                                                    172.200.1.201:6379                                                                           *:*                  
LISTEN      0      128                                                                *:22                                                                             *:*                  
LISTEN      0      80                                                              [::]:3307                                                                        [::]:*                  
LISTEN      0      80                                                              [::]:3308                                                                        [::]:*                  
LISTEN      0      80                                                              [::]:3309                                                                        [::]:*                  
LISTEN      0      128                                                             [::]:80                                                                          [::]:*                  
LISTEN      0      128                                                             [::]:22                                                                          [::]:*                  
[root@docker201.oldboyedu.com ~]#

2.检查已启动MySQL相同版本的多实例对应的server_id是否不相同

[root@docker201.oldboyedu.com ~]# mysql -S /tmp/mysql3307.sock -e "SELECT @@server_id"
+-------------+
| @@server_id |
+-------------+
|           7 |
+-------------+
[root@docker201.oldboyedu.com ~]# 
[root@docker201.oldboyedu.com ~]# mysql -S /tmp/mysql3308.sock -e "SELECT @@server_id"
+-------------+
| @@server_id |
+-------------+
|           8 |
+-------------+
[root@docker201.oldboyedu.com ~]# 
[root@docker201.oldboyedu.com ~]# mysql -S /tmp/mysql3309.sock -e "SELECT @@server_id"
+-------------+
| @@server_id |
+-------------+
|           9 |
+-------------+
[root@docker201.oldboyedu.com ~]#

3.检查主库的binlog信息

[root@docker201.oldboyedu.com ~]# mysql -S /tmp/mysql3307.sock -e "SELECT @@log_bin"
+-----------+
| @@log_bin |
+-----------+
|         1 |
+-----------+
[root@docker201.oldboyedu.com ~]#

4.主库建立复制用户

    MySQL 8.0之前版本创建复制用户:
        [root@docker201.oldboyedu.com ~]# mysql -S /tmp/mysql3307.sock -e "SELECT user,host FROM mysql.user;"
        +---------------+-----------+
        | user          | host      |
        +---------------+-----------+
        | mysql.session | localhost |
        | mysql.sys     | localhost |
        | root          | localhost |
        +---------------+-----------+
        [root@docker201.oldboyedu.com ~]# 
        [root@docker201.oldboyedu.com ~]# mysql -S /tmp/mysql3307.sock -e "GRANT REPLICATION SLAVE ON *.* TO repl@'172.200.1.%' IDENTIFIED BY 'oldboyedu';"
        [root@docker201.oldboyedu.com ~]# 
        [root@docker201.oldboyedu.com ~]# mysql -S /tmp/mysql3307.sock -e "SELECT user,host FROM mysql.user;"
        +---------------+-------------+
        | user          | host        |
        +---------------+-------------+
        | repl          | 172.200.1.% |
        | mysql.session | localhost   |
        | mysql.sys     | localhost   |
        | root          | localhost   |
        +---------------+-------------+
        [root@docker201.oldboyedu.com ~]# 
        [root@docker201.oldboyedu.com ~]# mysql -S /tmp/mysql3307.sock -e "SHOW GRANTS FOR repl@'172.200.1.%';"
        +--------------------------------------------------------+
        | Grants for repl@172.200.1.%                            |
        +--------------------------------------------------------+
        | GRANT REPLICATION SLAVE ON *.* TO 'repl'@'172.200.1.%' |
        +--------------------------------------------------------+
        [root@docker201.oldboyedu.com ~]# 

    MySQL 8.0之后版本创建复制用户:
        [root@docker201.oldboyedu.com ~]# mysql -S /tmp/mysql3307.sock -e "SELECT user,host FROM mysql.user;"
        +---------------+-------------+
        | user          | host        |
        +---------------+-------------+
        | repl          | 172.200.1.% |
        | mysql.session | localhost   |
        | mysql.sys     | localhost   |
        | root          | localhost   |
        +---------------+-------------+
        [root@docker201.oldboyedu.com ~]# 
        [root@docker201.oldboyedu.com ~]# mysql -S /tmp/mysql3307.sock -e "CREATE USER copy@'172.200.1.%' IDENTIFIED BY 'oldboyedu';"
        [root@docker201.oldboyedu.com ~]# 
        [root@docker201.oldboyedu.com ~]# mysql -S /tmp/mysql3307.sock -e "SELECT user,host FROM mysql.user;"
        +---------------+-------------+
        | user          | host        |
        +---------------+-------------+
        | copy          | 172.200.1.% |
        | repl          | 172.200.1.% |
        | mysql.session | localhost   |
        | mysql.sys     | localhost   |
        | root          | localhost   |
        +---------------+-------------+
        [root@docker201.oldboyedu.com ~]# 
        [root@docker201.oldboyedu.com ~]# mysql -S /tmp/mysql3307.sock -e "SHOW GRANTS FOR copy@'172.200.1.%';"
        +--------------------------------------------+
        | Grants for copy@172.200.1.%                |
        +--------------------------------------------+
        | GRANT USAGE ON *.* TO 'copy'@'172.200.1.%' |
        +--------------------------------------------+
        [root@docker201.oldboyedu.com ~]# 
        [root@docker201.oldboyedu.com ~]# mysql -S /tmp/mysql3307.sock -e "GRANT REPLICATION SLAVE ON *.* TO copy@'172.200.1.%';"
        [root@docker201.oldboyedu.com ~]# 
        [root@docker201.oldboyedu.com ~]# mysql -S /tmp/mysql3307.sock -e "SHOW GRANTS FOR copy@'172.200.1.%';"
        +--------------------------------------------------------+
        | Grants for copy@172.200.1.%                            |
        +--------------------------------------------------------+
        | GRANT REPLICATION SLAVE ON *.* TO 'copy'@'172.200.1.%' |
        +--------------------------------------------------------+
        [root@docker201.oldboyedu.com ~]# 

    测试用户是否正常登录:
        [root@docker201.oldboyedu.com ~]# mysql -h 172.200.1.201 -u copy -poldboyedu -P 3307
        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.7.31-log MySQL Community Server (GPL)

        Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

        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> 
        mysql> SHOW DATABASES;
        +--------------------+
        | Database           |
        +--------------------+
        | information_schema |
        +--------------------+
        1 row in set (0.01 sec)

        mysql> QUIT
        Bye
        [root@docker201.oldboyedu.com ~]# 
        [root@docker201.oldboyedu.com ~]# mysql -h 172.200.1.201 -u repl -poldboyedu -P 3307
        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.7.31-log MySQL Community Server (GPL)

        Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

        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> 
        mysql> SHOW DATABASES;
        +--------------------+
        | Database           |
        +--------------------+
        | information_schema |
        +--------------------+
        1 row in set (0.00 sec)

        mysql> 
        mysql> QUIT
        Bye
        [root@docker201.oldboyedu.com ~]# 

    绑定MySQL实例的IP地址:
        [root@docker201.oldboyedu.com ~]# ss -ntl
        State       Recv-Q Send-Q                                                 Local Address:Port                                                                Peer Address:Port              
        LISTEN      0      128                                                    172.200.1.201:6379                                                                           *:*                  
        LISTEN      0      128                                                                *:22                                                                             *:*                  
        LISTEN      0      80                                                              [::]:3307                                                                        [::]:*                  
        LISTEN      0      80                                                              [::]:3308                                                                        [::]:*                  
        LISTEN      0      80                                                              [::]:3309                                                                        [::]:*                  
        LISTEN      0      128                                                             [::]:80                                                                          [::]:*                  
        LISTEN      0      128                                                             [::]:22                                                                          [::]:*                  
        [root@docker201.oldboyedu.com ~]# 
        [root@docker201.oldboyedu.com ~]# vim /oldboyedu/softwares/mysql3307/my.cnf 
        [root@docker201.oldboyedu.com ~]# 
        [root@docker201.oldboyedu.com ~]# cat /oldboyedu/softwares/mysql3307/my.cnf 
        [mysqld]
        bind-address=172.200.1.201
        basedir=/oldboyedu/softwares/mysql
        datadir=/oldboyedu/data/mysql3307
        socket=/tmp/mysql3307.sock
        log_error=/oldboyedu/data/mysql3307/mysql-err.log
        port=3307
        server_id=7
        log_bin=/oldboyedu/data/mysql3307/mysql-bin
        [root@docker201.oldboyedu.com ~]# 
        [root@docker201.oldboyedu.com ~]# vim /oldboyedu/softwares/mysql3308/my.cnf 
        [root@docker201.oldboyedu.com ~]# 
        [root@docker201.oldboyedu.com ~]# vim /oldboyedu/softwares/mysql3309/my.cnf  # 每个MySQL实例都绑定"bind-address"属性，这样就可以绑定服务器的IP地址啦~
        [root@docker201.oldboyedu.com ~]# 
        [root@docker201.oldboyedu.com ~]# systemctl restart mysqld3307 mysqld3308 mysqld3309
        [root@docker201.oldboyedu.com ~]# 
        [root@docker201.oldboyedu.com ~]# ss -ntl
        State       Recv-Q Send-Q                                                 Local Address:Port                                                                Peer Address:Port              
        LISTEN      0      80                                                     172.200.1.201:3307                                                                           *:*                  
        LISTEN      0      128                                                    172.200.1.201:6379                                                                           *:*                  
        LISTEN      0      80                                                     172.200.1.201:3308                                                                           *:*                  
        LISTEN      0      80                                                     172.200.1.201:3309                                                                           *:*                  
        LISTEN      0      128                                                                *:22                                                                             *:*                  
        LISTEN      0      128                                                             [::]:80                                                                          [::]:*                  
        LISTEN      0      128                                                             [::]:22                                                                          [::]:*                  
        [root@docker201.oldboyedu.com ~]#

5.主库备份恢复到从库

[root@docker201.oldboyedu.com ~]# mysqldump -S /tmp/mysql3307.sock -A --master-data=2 --single-transaction > /tmp/all_db.sql
[root@docker201.oldboyedu.com ~]# 
[root@docker201.oldboyedu.com ~]# ll /tmp/all_db.sql 
-rw-r--r-- 1 root root 861076 2月  13 22:58 /tmp/all_db.sql
[root@docker201.oldboyedu.com ~]# 
[root@docker201.oldboyedu.com ~]# mysql -S /tmp/mysql3308.sock < /tmp/all_db.sql 
[root@docker201.oldboyedu.com ~]# 
[root@docker201.oldboyedu.com ~]# mysql -S /tmp/mysql3309.sock < /tmp/all_db.sql 
[root@docker201.oldboyedu.com ~]#

6.从库配置主库的链接信息并确认复制起点

    (1)查看官方提供的"CHANGE MASTER TO"命令帮助信息:
        mysql> HELP CHANGE MASTER TO

        ...

        CHANGE MASTER TO
          MASTER_HOST='master2.example.com',
          MASTER_USER='replication',
          MASTER_PASSWORD='password',
          MASTER_PORT=3306,
          MASTER_LOG_FILE='master2-bin.001',
          MASTER_LOG_POS=4,
          MASTER_CONNECT_RETRY=10;

        The next example shows an operation that is less frequently employed.
        It is used when the slave has relay log files that you want it to
        execute again for some reason. To do this, the master need not be
        reachable. You need only use CHANGE MASTER TO and start the SQL thread
        (START SLAVE SQL_THREAD):

        CHANGE MASTER TO
          RELAY_LOG_FILE='slave-relay-bin.006',
          RELAY_LOG_POS=4025;

        URL: https://dev.mysql.com/doc/refman/5.7/en/change-master-to.html

        mysql> 

    (2)获取主库的复制点起始位置信息:
        [root@docker201.oldboyedu.com ~]# egrep "^-- CHANGE MASTER" /tmp/all_db.sql 
        -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000004', MASTER_LOG_POS=908;
        [root@docker201.oldboyedu.com ~]# 

    (3)两个从库建立主库的链接信息，并确定复制的起点
        [root@docker201.oldboyedu.com ~]# mysql -S /tmp/mysql3308.sock
        Welcome to the MySQL monitor.  Commands end with ; or \g.
        Your MySQL connection id is 2
        Server version: 5.7.31-log MySQL Community Server (GPL)

        Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

        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> 
        mysql> CHANGE MASTER TO
            ->     MASTER_HOST='172.200.1.201',
            ->     MASTER_USER='copy',
            ->     MASTER_PASSWORD='oldboyedu',
            ->     MASTER_PORT=3307,
            ->     MASTER_LOG_FILE='mysql-bin.000004',
            ->     MASTER_LOG_POS=908,
            ->     MASTER_CONNECT_RETRY=10;
        Query OK, 0 rows affected, 2 warnings (0.31 sec)

        mysql> 
        mysql> QUIT
        Bye
        [root@docker201.oldboyedu.com ~]# 
        [root@docker201.oldboyedu.com ~]# mysql -S /tmp/mysql3309.sock
        Welcome to the MySQL monitor.  Commands end with ; or \g.
        Your MySQL connection id is 2
        Server version: 5.7.31-log MySQL Community Server (GPL)

        Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

        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> 
        mysql> CHANGE MASTER TO
            ->     MASTER_HOST='172.200.1.201',
            ->     MASTER_USER='copy',
            ->     MASTER_PASSWORD='oldboyedu',
            ->     MASTER_PORT=3307,
            ->     MASTER_LOG_FILE='mysql-bin.000004',
            ->     MASTER_LOG_POS=908,
            ->     MASTER_CONNECT_RETRY=10;
        Query OK, 0 rows affected, 2 warnings (0.29 sec)

        mysql> 
        mysql> QUIT
        Bye
        [root@docker201.oldboyedu.com ~]#

7.从库开启专用的复制线程

[root@docker201.oldboyedu.com ~]# mysql -S /tmp/mysql3308.sock -e "START SLAVE;"
[root@docker201.oldboyedu.com ~]# 
[root@docker201.oldboyedu.com ~]# mysql -S /tmp/mysql3309.sock -e "START SLAVE;"
[root@docker201.oldboyedu.com ~]#

8.验证主从复制状态

[root@docker201.oldboyedu.com ~]# mysql -S /tmp/mysql3308.sock -e "SHOW SLAVE STATUS\G" | grep "Running:"  # 如下所示，如果有两个"yes"说明主从复制环境部署完毕。
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
[root@docker201.oldboyedu.com ~]# 
[root@docker201.oldboyedu.com ~]# mysql -S /tmp/mysql3309.sock -e "SHOW SLAVE STATUS\G" | grep "Running:"
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
[root@docker201.oldboyedu.com ~]#

9.如果主从复制搭建不成功，可以重置主从配置信息，而后重做上述操作。

    需要注意的是，如果主从复制为搭建成功才会关心该步骤，如果已经搭建成功，请直接忽略该步骤。

    重置主从复制的配置信息:
        [root@docker201.oldboyedu.com ~]# mysql -S /tmp/mysql3309.sock -e "STOP SLAVE;RESET SLAVE ALL;"

10.在主库创建一个数据库，查看两个从库是否有对应的数据库生成

    查看原始的主库，从库信息:
        [root@docker201.oldboyedu.com ~]# mysql -S /tmp/mysql3307.sock -e "SHOW DATABASES"
        +--------------------+
        | Database           |
        +--------------------+
        | information_schema |
        | mysql              |
        | performance_schema |
        | sys                |
        +--------------------+
        [root@docker201.oldboyedu.com ~]# 
        [root@docker201.oldboyedu.com ~]# mysql -S /tmp/mysql3308.sock -e "SHOW DATABASES"
        +--------------------+
        | Database           |
        +--------------------+
        | information_schema |
        | mysql              |
        | performance_schema |
        | sys                |
        +--------------------+
        [root@docker201.oldboyedu.com ~]# 
        [root@docker201.oldboyedu.com ~]# mysql -S /tmp/mysql3309.sock -e "SHOW DATABASES"
        +--------------------+
        | Database           |
        +--------------------+
        | information_schema |
        | mysql              |
        | performance_schema |
        | sys                |
        +--------------------+
        [root@docker201.oldboyedu.com ~]# 

    主库操作:
        [root@docker201.oldboyedu.com ~]# mysql -S /tmp/mysql3307.sock -e "CREATE DATABASE oldboyedu CHARSET utf8mb4;"
        [root@docker201.oldboyedu.com ~]# 
        [root@docker201.oldboyedu.com ~]# mysql -S /tmp/mysql3307.sock -e "SHOW DATABASES"
        +--------------------+
        | Database           |
        +--------------------+
        | information_schema |
        | mysql              |
        | performance_schema |
        | sys                |
        | oldboyedu        |
        +--------------------+
        [root@docker201.oldboyedu.com ~]# 

    从库操作:
        [root@docker201.oldboyedu.com ~]# mysql -S /tmp/mysql3308.sock -e "SHOW DATABASES"
        +--------------------+
        | Database           |
        +--------------------+
        | information_schema |
        | mysql              |
        | performance_schema |
        | sys                |
        | oldboyedu        |
        +--------------------+
        [root@docker201.oldboyedu.com ~]# 
        [root@docker201.oldboyedu.com ~]# mysql -S /tmp/mysql3309.sock -e "SHOW DATABASES"
        +--------------------+
        | Database           |
        +--------------------+
        | information_schema |
        | mysql              |
        | performance_schema |
        | sys                |
        | oldboyedu        |
        +--------------------+
        [root@docker201.oldboyedu.com ~]#

三.主从复制中从库的线程管理

1.启动从库IO线程和SQL线程

[root@docker201.oldboyedu.com ~]# mysql -S /tmp/mysql3309.sock -e "SHOW SLAVE STATUS\G" | grep "Running:"
             Slave_IO_Running: No
            Slave_SQL_Running: No
[root@docker201.oldboyedu.com ~]# 
[root@docker201.oldboyedu.com ~]# mysql -S /tmp/mysql3309.sock -e "START SLAVE;"
[root@docker201.oldboyedu.com ~]# 
[root@docker201.oldboyedu.com ~]# mysql -S /tmp/mysql3309.sock -e "SHOW SLAVE STATUS\G" | grep "Running:"
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
[root@docker201.oldboyedu.com ~]#

2.停止从库IO线程和SQL线程

[root@docker201.oldboyedu.com ~]# mysql -S /tmp/mysql3309.sock -e "SHOW SLAVE STATUS\G" | grep "Running:"
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
[root@docker201.oldboyedu.com ~]# 
[root@docker201.oldboyedu.com ~]# mysql -S /tmp/mysql3309.sock -e "STOP SLAVE;"
[root@docker201.oldboyedu.com ~]# 
[root@docker201.oldboyedu.com ~]# mysql -S /tmp/mysql3309.sock -e "SHOW SLAVE STATUS\G" | grep "Running:"
             Slave_IO_Running: No
            Slave_SQL_Running: No
[root@docker201.oldboyedu.com ~]#

3.只启动从库的SQL线程

[root@docker201.oldboyedu.com ~]# mysql -S /tmp/mysql3309.sock -e "SHOW SLAVE STATUS\G" | grep "Running:"
             Slave_IO_Running: No
            Slave_SQL_Running: No
[root@docker201.oldboyedu.com ~]# 
[root@docker201.oldboyedu.com ~]# mysql -S /tmp/mysql3309.sock -e "START SLAVE SQL_THREAD;"
[root@docker201.oldboyedu.com ~]# 
[root@docker201.oldboyedu.com ~]# mysql -S /tmp/mysql3309.sock -e "SHOW SLAVE STATUS\G" | grep "Running:"
             Slave_IO_Running: No
            Slave_SQL_Running: Yes
[root@docker201.oldboyedu.com ~]# 
[root@docker201.oldboyedu.com ~]#

4.只启动从库的IO线程

[root@docker201.oldboyedu.com ~]# mysql -S /tmp/mysql3309.sock -e "SHOW SLAVE STATUS\G" | grep "Running:"
             Slave_IO_Running: No
            Slave_SQL_Running: No
[root@docker201.oldboyedu.com ~]# 
[root@docker201.oldboyedu.com ~]# mysql -S /tmp/mysql3309.sock -e "START SLAVE IO_THREAD;"
[root@docker201.oldboyedu.com ~]# 
[root@docker201.oldboyedu.com ~]# mysql -S /tmp/mysql3309.sock -e "SHOW SLAVE STATUS\G" | grep "Running:"
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
[root@docker201.oldboyedu.com ~]# 
[root@docker201.oldboyedu.com ~]#

5.只停止从库的SQL线程

[root@docker201.oldboyedu.com ~]# mysql -S /tmp/mysql3309.sock -e "SHOW SLAVE STATUS\G" | grep "Running:"
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
[root@docker201.oldboyedu.com ~]# 
[root@docker201.oldboyedu.com ~]# mysql -S /tmp/mysql3309.sock -e "STOP SLAVE SQL_THREAD;"
[root@docker201.oldboyedu.com ~]# 
[root@docker201.oldboyedu.com ~]# mysql -S /tmp/mysql3309.sock -e "SHOW SLAVE STATUS\G" | grep "Running:"
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
[root@docker201.oldboyedu.com ~]#

6.只停止从库的IO线程

[root@docker201.oldboyedu.com ~]# mysql -S /tmp/mysql3309.sock -e "SHOW SLAVE STATUS\G" | grep "Running:"
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
[root@docker201.oldboyedu.com ~]# 
[root@docker201.oldboyedu.com ~]# mysql -S /tmp/mysql3309.sock -e "STOP SLAVE IO_THREAD;"
[root@docker201.oldboyedu.com ~]# 
[root@docker201.oldboyedu.com ~]# mysql -S /tmp/mysql3309.sock -e "SHOW SLAVE STATUS\G" | grep "Running:"
             Slave_IO_Running: No
            Slave_SQL_Running: Yes
[root@docker201.oldboyedu.com ~]# 
[root@docker201.oldboyedu.com ~]#

7.解除从库身份

[root@docker201.oldboyedu.com ~]# mysql -S /tmp/mysql3309.sock 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 57
Server version: 5.7.31-log MySQL Community Server (GPL)

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

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> 
mysql> STOP SLAVE;  # 在接触从库之前，需要先停止所有的线程，即IO线程和SQL线程。
Query OK, 0 rows affected (0.00 sec)

mysql> 
mysql> SHOW SLAVE STATUS\G  # 查看"Slave_IO_Running"和"Slave_SQL_Running"的值是否均为"NO"
*************************** 1. row ***************************
               Slave_IO_State: 
                  Master_Host: 172.200.1.201
                  Master_User: copy
                  Master_Port: 3307
                Connect_Retry: 10
              Master_Log_File: mysql-bin.000005
          Read_Master_Log_Pos: 350
               Relay_Log_File: docker201-relay-bin.000005
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000005
             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: 350
              Relay_Log_Space: 697
              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: 7
                  Master_UUID: 7c5bfd7b-52db-11eb-b842-000c29820c67
             Master_Info_File: /oldboyedu/data/mysql3309/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)

mysql> 
mysql> RESET SLAVE ALL;  # 解除从库的所有配置
Query OK, 0 rows affected (0.29 sec)

mysql> 
mysql> SHOW SLAVE STATUS\G  # 解除后再次查看从库的状态，不难发现，从库的状态信息为空了。
Empty set (0.00 sec)

mysql>

可能会遇到的报错

1.error connecting to master 'copy@10.0.0.108:3308' - retry-time: 10 retries: 1 message: Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection.

问题原因:

解决方案:
    ALTER USER copy@'10.0.0.%' IDENTIFIED WITH mysql_native_password BY 'oldboyedu';

1631963879659

2.Fatal error: The slave I/O thread stops because master and slave have equal MySQL server ids; these ids must be different for replication to work (or the --replicate-same-server-id option must be used on slave but this does not always make sense; please check the manual before using it).

问题原因:

解决方案:

1631964099809

3. Error 'Can't create database 'zabbix'; database exists' on query. Default database: 'zabbix'. Query: 'CREATE DATABASE zabbix'

问题原因:

解决方案：

1631964826540

4.ERROR 3081 (HY000): This operation cannot be performed with running replication threads; run STOP SLAVE FOR CHANNEL '' first

问题原因:
    RESET SLAVE ALL;前记得

解决方案:
    STOP SLAVE;
谨此笔记，记录过往。凭君阅览，如能收益，莫大奢望。
MySQL运维篇
暂无评论

发送评论 编辑评论 


				

			

						

				

					

						

							

								
							

							
						

					

				

				

					

						

							

								
							

							
						

					

				

				

					

						

							

								
							

							
							
													

					

				

			

			
			

				

					
				

			

				

											

							
							
						

																

							
							
						

																

							
							
						

										
					
											
						

	

		
|´・ω・)ノ
ヾ(≧∇≦*)ゝ
(☆ω☆)
（╯‵□′）╯︵┴─┴
￣﹃￣
(/ω＼)
∠( ᐛ 」∠)＿
(๑•̀ㅁ•́ฅ)
→_→
୧(๑•̀⌄•́๑)૭
٩(ˊᗜˋ*)و
(ノ°ο°)ノ
(´இ皿இ｀)
⌇●﹏●⌇
(ฅ´ω`ฅ)
(╯°A°)╯︵○○○
φ(￣∇￣o)
ヾ(´･ ･｀｡)ノ"
( ง ᵒ̌皿ᵒ̌)ง⁼³₌₃
(ó﹏ò｡)
Σ(っ °Д °;)っ
( ,,´･ω･)ﾉ"(´っω･｀｡)
╮(╯▽╰)╭
o(*////▽////*)q
＞﹏＜
( ๑´•ω•) "(ㆆᴗㆆ)
😂
😀
😅
😊
🙂
🙃
😌
😍
😘
😜
😝
😏
😒
🙄
😳
😡
😔
😫
😱
😭
💩
👻
🙌
🖕
👍
👫
👬
👭
🌚
🌝
🙈
💊
😶
🙏
🍦
🍉
😣
Source: github.com/k4yt3x/flowerhd
	

	

		
颜文字
Emoji
小恐龙
花!
	


									

			

			
			
		

	






上一篇
下一篇 

                    

                    
			        推荐文章
		            

		            

							
014、MySQL主从复制(Replication)架构从库延迟复制故障分析及处理

							
							

							
016、数据库生态圈概述及部署实战案例

							
							

							
006、Mycat分库分表之枚举分片

							
							

							
014、MySQL索引及执行计划

							
							

							
009、MySQL常用备份工具