MySQL利用mysqldump搭建主从复制（传统模式，GTID模式）

客官°小女子只卖身不卖艺 2022-02-04 09:25 553阅读 0赞

主库为master，从库为slave，使用mysqldump将主库数据导出，导入从库，并搭建主从复制。  
1.主库mysqldump导出

[root@master mysqlData]# mysql
    Welcome to the MySQL monitor.  Commands end with ; or \g.
    Your MySQL connection id is 15
    Server version: 5.7.23-log MySQL Community Server (GPL)
    
    Copyright (c) 2000, 2018, 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.
    
    --主库有一个sam库
    mysql> show databases;
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | mysql              |
    | performance_schema |
    | sam                |
    | sys                |
    +--------------------+
    5 rows in set (0.00 sec)
    
    mysql> use sam;
    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_sam |
    +---------------+
    | test          |
    +---------------+
    1 row in set (0.00 sec)
    
    --sam.test表有一条数据
    mysql> select * from test;
    +----+------+
    | id | name |
    +----+------+
    |  1 | sam  |
    +----+------+
    
    --mysqldump导出数据
    [root@master mysqlData]# mysqldump --defaults-file=/etc/my.cnf -uroot -psam123 --master-data=2 --single-transaction --set-gtid-purged=off --log-error=all.log -A >all.sql

2.从库

[root@slave logs]# mysql
    Welcome to the MySQL monitor.  Commands end with ; or \g.
    Your MySQL connection id is 5
    Server version: 5.7.23-log MySQL Community Server (GPL)
    
    Copyright (c) 2000, 2018, 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> show databases;
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | mysql              |
    | performance_schema |
    | sys                |
    +--------------------+
    4 rows in set (0.01 sec)
    
    mysql> exit
    Bye
    
    --导入主库数据
    [root@slave mysqlData]# mysql -uroot -psam123 < all.sql
    mysql: [Warning] Using a password on the command line interface can be insecure.
    [root@slave mysqlData]# mysql
    Welcome to the MySQL monitor.  Commands end with ; or \g.
    Your MySQL connection id is 8
    Server version: 5.7.23-log MySQL Community Server (GPL)
    
    Copyright (c) 2000, 2018, 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> show databases;
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | mysql              |
    | performance_schema |
    | sam                |
    | sys                |
    +--------------------+
    5 rows in set (0.00 sec)
    
    mysql> use sam;
    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> select * from test;
    +----+------+
    | id | name |
    +----+------+
    |  1 | sam  |
    +----+------+
    1 row in set (0.00 sec)

3.配置复制

--主库创建复制用户repl
    GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%' identified by 'sam123';
    
    --检查all.sql开头的信息，有主库binlog位置信息
    [root@slave mysqlData]# head -50 all.sql 
    -- MySQL dump 10.13  Distrib 5.7.23, for linux-glibc2.12 (x86_64)
    --
    -- Host: localhost    Database: 
    -- ------------------------------------------------------
    -- Server version       5.7.23-log
    
    /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
    /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
    /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
    /*!40101 SET NAMES utf8 */;
    /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
    /*!40103 SET TIME_ZONE='+00:00' */;
    /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
    /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
    /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
    /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
    
    --
    -- Position to start replication or point-in-time recovery from
    --
    
    -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.017725', MASTER_LOG_POS=1549;
    
    --从库配置复制关系
    mysql> change master to 
        -> master_host='192.168.204.128',
        -> master_port=3306,
        -> master_user='repl',
        -> master_password='sam123',
        -> master_log_file='mysql-bin.017725',
        -> master_log_pos=1549;
    Query OK, 0 rows affected, 2 warnings (0.03 sec)
    
    mysql> show warnings;
    +-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    | Level | Code | Message                                                                                                                                                                                                                                                                              |
    +-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    | Note  | 1759 | Sending passwords in plain text without SSL/TLS is extremely insecure.                                                                                                                                                                                                               |
    | Note  | 1760 | Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information. |
    +-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    2 rows in set (0.00 sec)
    
    mysql> show slave status\G
    *************************** 1. row ***************************
                   Slave_IO_State: 
                      Master_Host: 192.168.204.128
                      Master_User: repl
                      Master_Port: 3306
                    Connect_Retry: 60
                  Master_Log_File: mysql-bin.017725
              Read_Master_Log_Pos: 1549
                   Relay_Log_File: slave-relay-bin.000001
                    Relay_Log_Pos: 4
            Relay_Master_Log_File: mysql-bin.017725
                 Slave_IO_Running: No
                Slave_SQL_Running: No
                  Replicate_Do_DB: 
              Replicate_Ignore_DB: mysql,information_schema,performance_schema,sys
               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: 1549
                  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: /mysqlData/data/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: 911a6260-7089-11e9-b349-000c29d2f92c:1-130
                    Auto_Position: 0
             Replicate_Rewrite_DB: 
                     Channel_Name: 
               Master_TLS_Version: 
    1 row in set (0.00 sec)
    
    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: 192.168.204.128
                      Master_User: repl
                      Master_Port: 3306
                    Connect_Retry: 60
                  Master_Log_File: mysql-bin.017725
              Read_Master_Log_Pos: 1549
                   Relay_Log_File: slave-relay-bin.000002
                    Relay_Log_Pos: 320
            Relay_Master_Log_File: mysql-bin.017725
                 Slave_IO_Running: Yes
                Slave_SQL_Running: Yes
                  Replicate_Do_DB: 
              Replicate_Ignore_DB: mysql,information_schema,performance_schema,sys
               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: 1549
                  Relay_Log_Space: 527
                  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: 128
                      Master_UUID: f4859d1f-f82f-11e8-afbe-000c2999b048
                 Master_Info_File: /mysqlData/data/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: 911a6260-7089-11e9-b349-000c29d2f92c:1-130
                    Auto_Position: 0
             Replicate_Rewrite_DB: 
                     Channel_Name: 
               Master_TLS_Version: 
    1 row in set (0.00 sec)

4.检验主从复制

--主库向sam.test表插入数据
    [root@master mysqlData]# mysql
    Welcome to the MySQL monitor.  Commands end with ; or \g.
    Your MySQL connection id is 18
    Server version: 5.7.23-log MySQL Community Server (GPL)
    
    Copyright (c) 2000, 2018, 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> insert into sam.test values (2,'tom');
    Query OK, 1 row affected (0.00 sec)
    
    --从库检查sam.test表是否有数据增加
    [root@slave mysqlData]# mysql
    Welcome to the MySQL monitor.  Commands end with ; or \g.
    Your MySQL connection id is 12
    Server version: 5.7.23-log MySQL Community Server (GPL)
    
    Copyright (c) 2000, 2018, 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> select * from sam.test;
    +----+------+
    | id | name |
    +----+------+
    |  1 | sam  |
    |  2 | tom  |
    +----+------+
    2 rows in set (0.00 sec)

此外，可以利用GTID的方式进行主从搭建，此方式需要在导出数据时开启-set-gtid-purged=on:  
`mysqldump --defaults-file=/etc/my.cnf -uroot -psam123 --master-data=2 --single-transaction --set-gtid-purged=on --log-error=all.log -A >all.sql`  
查看all.sql会发现开头多了`SET @@GLOBAL.GTID_PURGED='f4859d1f-f82f-11e8-afbe-000c2999b048:1-382771';`

[root@master mysqlData]# head -50 all2.sql 
    -- MySQL dump 10.13  Distrib 5.7.23, for linux-glibc2.12 (x86_64)
    --
    -- Host: localhost    Database: 
    -- ------------------------------------------------------
    -- Server version       5.7.23-log
    
    /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
    /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
    /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
    /*!40101 SET NAMES utf8 */;
    /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
    /*!40103 SET TIME_ZONE='+00:00' */;
    /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
    /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
    /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
    /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
    SET @MYSQLDUMP_TEMP_LOG_BIN = @@SESSION.SQL_LOG_BIN;
    SET @@SESSION.SQL_LOG_BIN= 0;
    
    --
    -- GTID state at the beginning of the backup 
    --
    
    SET @@GLOBAL.GTID_PURGED='f4859d1f-f82f-11e8-afbe-000c2999b048:1-382771';
    
    --
    -- Position to start replication or point-in-time recovery from
    --
    
    -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.017725', MASTER_LOG_POS=1806;
    
    --
    -- Current Database: `mysql`
    --
    
    CREATE DATABASE /*!32312 IF NOT EXISTS*/ `mysql` /*!40100 DEFAULT CHARACTER SET utf8mb4 */;
    
    USE `mysql`;
    
    --
    -- Table structure for table `columns_priv`

导入前需要在从库执行reset master清空GTID\_EXECUTED，否则会报错：`ERROR 1840 (HY000) at line 24: @@GLOBAL.GTID_PURGED can only be set when @@GLOBAL.GTID_EXECUTED is empty.`  
由此，在从库导入数据之后，由于已经有了GTID的信息，那么搭建从库时只需要指定master\_auto\_position=1即可：

change master to 
    master_host='192.168.204.128',
    master_port=3306,
    master_user='repl',
    master_password='sam123',
    master_auto_position=1;