mysqldump 快速搭建特定库主从架构(GTID)

对于数据总量不大的MySQL数据库搭建主从架构,借助mysqldump工具来实现是不错的选择,再结合MySQL GTID特性,使得高可用轻而易举。本文是基于mysqldump搭建gtid主从的补充。主要是介绍基于多库级别实现GTID主从,即非整个实例级别。下面是本文的具体描述及示例。

相关知识点参考 基于mysqldump搭建gtid主从 MySQL GTID 错误处理汇总 配置MySQL GTID 主从复制 使用mysqldump导出数据库

一、mysqldump时GTID参数

# mysqldump --help|grep gtid-purged -A8
  --set-gtid-purged[=name] 
                      Add 'SET @@GLOBAL.GTID_PURGED' to the output. Possible
                      values for this option are ON, OFF and AUTO. If ON is
                      used and GTIDs are not enabled on the server, an error is
                      generated. If OFF is used, this option does nothing. If
                      AUTO is used and GTIDs are enabled on the server, 'SET
                      @@GLOBAL.GTID_PURGED' is added to the output. If GTIDs
                      are disabled, AUTO does nothing. If no value is supplied
                      then the default (AUTO) value will be considered.
        这个参数用于控制在导出数据库时是否导出GTID,针对已开启GTID的mysql实例                      
        就是说导出的数据中已经包含了这些GTID,因此在从库开启从之后需要被跳过
        缺省值为AUTO,如果导出时指定为OFF,则在从库开启从之后会收到error 1236

二、主从环境配置

        主服务器:192.168.1.233:3306  server_id : 233
        从服务器:192.168.1.245:3306  server_id : 245

--在主库端创建复制用户
        (root@Master)[(none)]> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'repl'@'%' IDENTIFIED BY '123456'; 

-- 查看主库端的配置文件
        (root@Master)[(none)]>system grep -v ^# /etc/my.cnf

        [mysqld]

        sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

        basedir = /usr/local/mysql
        datadir = /data

        server_id=233                
        gtid_mode=on                 
        enforce_gtid_consistency=on  

        log_bin=node233-binlog
        log-slave-updates=1    
        binlog_format=row              
        report_host=Master   
        report_port=3306
        master-info-repository = TABLE
        relay-log-info-repository = TABLE
        replicate-do-db=tempdb
        replicate-do-db=testdb

        skip_slave_start=1    ###该参数在启动DB时不会自启动slave,需要手动启动  

-- 查看从库端的配置文件 
        (root@Slave)[(none)]>system grep -v ^# /etc/my.cnf

        [mysqld]

        basedir = /usr/local/mysql 
        datadir = /data  

        sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

        server_id=245                
        gtid_mode=on                 
        enforce_gtid_consistency=on  

        log_bin=node245-binlog
        log-slave-updates=1    
        binlog_format=row            
        report_host=Slave
        report_port=3306
        master-info-repository = TABLE
        relay-log-info-repository = TABLE
        replicate-do-db=tempdb
        replicate-do-db=testdb

        skip_slave_start=1  ###该参数在启动DB时不会自启动slave,需要手动启动   

三、主库端的设置

演示环境
        (root@Master)[(none)]>show variables like 'version';
        +---------------+------------+
        | Variable_name | Value      |
        +---------------+------------+
        | version       | 5.7.12-log |
        +---------------+------------+

--创建需要复制的数据库tempdb与testdb
        (root@Master)[(none)]>create database tempdb;

        (root@Master)[(none)]>use tempdb;

        (root@Master)[tempdb]>create table tb(`userId` int);

        (root@Master)[(none)]>create database testdb;

        (root@Master)[(none)]>use testdb;

        (root@Master)[testdb]>create table tb(`userId` int);

--主库端执行sql,使用如下脚本
        # more insert_id.sh 
        #/bin/sh
        cnt=1

        while [ $cnt -le 10000 ]
        do
                mysql -uroot -ppass -e "insert into tempdb.tb(userId) values($cnt);
                                        insert into testdb.tb(userId) values($cnt)"
                let cnt=$cnt+1
                   sleep 1 
                echo "Insert $cnt"
        done

--执行脚本
        # ./insert_id.sh 
        mysql: [Warning] Using a password on the command line interface can be insecure.
        Insert 2
        mysql: [Warning] Using a password on the command line interface can be insecure.
        Insert 3
        mysql: [Warning] Using a password on the command line interface can be insecure.
        Insert 4
             ...........

--dump导出库文件     
        # mysqldump --single-transaction --triggers --routines --events --user=root --password=pass \
        > --databases tempdb testdb  >/tmp/multidb.sql  

--dump文件的内容
        # more /tmp/multidb.sql
        -- MySQL dump 10.13  Distrib 5.7.12, for linux-glibc2.5 (x86_64)
        --
        -- Host: localhost    Database: tempdb
        -- ------------------------------------------------------
        -- Server version       5.7.12-log
        -- 非重要的信息省略 

        SET @MYSQLDUMP_TEMP_LOG_BIN = @@SESSION.SQL_LOG_BIN;
        SET @@SESSION.SQL_LOG_BIN= 0;

        --
        -- GTID state at the beginning of the backup
        --

        --GTID信息,重要,用于主从复制跳过)
        SET @@GLOBAL.GTID_PURGED='1b64c25d-8d2b-11e6-9ac0-000c29b82d0d:1-2318';

        --
        -- Current Database: `tempdb`
        --

        CREATE DATABASE /*!32312 IF NOT EXISTS*/ `tempdb` /*!40100 DEFAULT CHARACTER SET latin1 */;

        USE `tempdb`;

        --
        -- Table structure for table `tb`
        --

        DROP TABLE IF EXISTS `tb`;
        /*!40101 SET @saved_cs_client     = @@character_set_client */;
        /*!40101 SET character_set_client = utf8 */;
        CREATE TABLE `tb` (
          `userId` int(11) DEFAULT NULL
        ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
        /*!40101 SET character_set_client = @saved_cs_client */;

        --
        -- Dumping data for table `tb`
        --

        LOCK TABLES `tb` WRITE;
        /*!40000 ALTER TABLE `tb` DISABLE KEYS */;
        INSERT INTO `tb` VALUES (1),(2),(3),(4),(5);
        /*!40000 ALTER TABLE `tb` ENABLE KEYS */;
        UNLOCK TABLES;

        --
        -- Dumping events for database 'tempdb'
        --

        --
        -- Dumping routines for database 'tempdb'
        --

        --
        -- Current Database: `testdb`
        --

        CREATE DATABASE /*!32312 IF NOT EXISTS*/ `testdb` /*!40100 DEFAULT CHARACTER SET latin1 */;

        USE `testdb`;

        --
        -- Table structure for table `tb`
        --

        DROP TABLE IF EXISTS `tb`;
        /*!40101 SET @saved_cs_client     = @@character_set_client */;
        /*!40101 SET character_set_client = utf8 */;
        CREATE TABLE `tb` (
          `userId` int(11) DEFAULT NULL
        ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
        /*!40101 SET character_set_client = @saved_cs_client */;

        --
        -- Dumping data for table `tb`
        --

        LOCK TABLES `tb` WRITE;
        /*!40000 ALTER TABLE `tb` DISABLE KEYS */;
        INSERT INTO `tb` VALUES (1),(2),(3),(4),(5);
        /*!40000 ALTER TABLE `tb` ENABLE KEYS */;
        UNLOCK TABLES;

        --
        -- Dumping events for database 'testdb'
        --

        -- 在上面dump出来的每个表中可以看到导出的时候已经产生了数据1-5
        -- Dumping routines for database 'testdb'
        --
        SET @@SESSION.SQL_LOG_BIN = @MYSQLDUMP_TEMP_LOG_BIN;

--将导出文件复制到从服务器
        [root@node233 ~]# scp /tmp/multidb.sql 192.168.1.245:/tmp

四、从库端的设置

(root@Slave)[(none)]>reset master;
        Query OK, 0 rows affected (0.02 sec)

        (root@Slave)[(none)]>reset slave all;
        Query OK, 0 rows affected (0.04 sec)

        (root@Slave)[(none)]>source /tmp/multidb.sql

        (root@Slave)[tempdb]>CHANGE MASTER TO  
            -> MASTER_HOST='192.168.1.233',    
            -> MASTER_USER='repl',    
            -> MASTER_PASSWORD='123456',    
            -> MASTER_PORT=3306,    
            -> MASTER_AUTO_POSITION = 1;
        Query OK, 0 rows affected, 2 warnings (0.07 sec)

        (root@Slave)[tempdb]>start slave;
        Query OK, 0 rows affected (0.05 sec)

        (root@Slave)[testdb]>show slave status\G
        *************************** 1. row ***************************
                       Slave_IO_State: Waiting for master to send event
                          Master_Host: Master
                          Master_User: repl
                          Master_Port: 3306
                        Connect_Retry: 60
                      Master_Log_File: node233-binlog.000008
                  Read_Master_Log_Pos: 201141
                       Relay_Log_File: node245-relay-bin.000002
                        Relay_Log_Pos: 96813
                Relay_Master_Log_File: node233-binlog.000008
                     Slave_IO_Running: Yes
                    Slave_SQL_Running: Yes
                      Replicate_Do_DB: tempdb,testdb
                  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: 201141
                      Relay_Log_Space: 97062
                      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: 233
                          Master_UUID: 1b64c25d-8d2b-11e6-9ac0-000c29b82d0d
                     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:   -- Author : Leshami
              Last_IO_Error_Timestamp:   -- Blog   : http://blog.csdn.net/leshami
             Last_SQL_Error_Timestamp: 
                       Master_SSL_Crl: 
                   Master_SSL_Crlpath: 
                   Retrieved_Gtid_Set: 1b64c25d-8d2b-11e6-9ac0-000c29b82d0d:2319-2702
                    Executed_Gtid_Set: 1b64c25d-8d2b-11e6-9ac0-000c29b82d0d:1-2702
                        Auto_Position: 1
                 Replicate_Rewrite_DB: 
                         Channel_Name: 
                   Master_TLS_Version: 

五、验证主从记录

--以下查询中可以看到,两个数据库的表中的记录在不停的增加
        (root@Slave)[testdb]>select count(*) from tb;
        +----------+
        | count(*) |
        +----------+          
        |      206 |
        +----------+

        (root@Slave)[testdb]>select count(*) from tempdb.tb;
        +----------+
        | count(*) |
        +----------+
        |      214 |
        +----------+

        (root@Slave)[testdb]>select count(*) from tb;
        +----------+
        | count(*) |
        +----------+
        |      216 |
        +----------+

        (root@Slave)[testdb]>select count(*) from tempdb.tb;
        +----------+
        | count(*) |
        +----------+
        |      218 |
        +----------+

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏杨建荣的学习笔记

ORACLE数据文件名导致的奇怪问题 (51天)

今天创建了一些表空间,准备做data guard来看看效果。 为了方便起见,我用gridcontrol来做,主库也开了Omf,省去了好多步骤。 一路点下来,就等...

3254
来自专栏杨建荣的学习笔记

impdp ORA-39002,ORA-39166,ORA-39164的问题及解决(r2第6天)

今天在做imp和impdp的性能测试时,发现如果表中存在lob字段,加载真是慢的厉害,每秒钟大概1000条的样子,按照这种速度,基本上不用干活了。 比如5千万条...

3227
来自专栏耕耘实录

记一次生产环境MySQL数据库的备份与还原

版权声明:本文为耕耘实录原创文章,各大自媒体平台同步更新。欢迎转载,转载请注明出处,谢谢

1024
来自专栏杨建荣的学习笔记

生产环境sql语句调优实战第九篇(r3笔记第34天)

生产环境中有一些sql语句是不定时炸弹,不声不响的运行着,可能相关的表很大,运行时间达数小时,甚至数天。 上周在生产环境中发现一条sql语句,运行时间几乎是按照...

3445
来自专栏文渊之博

利用PowerShell复制SQLServer账户的所有权限

问题   对于DBA或者其他运维人员来说授权一个账户的相同权限给另一个账户是一个很普通的任务。但是随着服务器、数据库、应用、使用人员地增加就变得很枯燥乏味又耗时...

2928
来自专栏数据库新发现

关于dirty buffer

SQL> select VIEW_DEFINITION from v$fixed_view_definition where VIEW_NAME = 'GV$B...

1013
来自专栏杨建荣的学习笔记

生产环境sql语句调优实战第五篇(r2笔记41天)

今天在生产环境中发现一条sql语句尽管走了主键索引,但是查询还是很慢。 sql语句类似下面的形式: SELECT /*+ index (bl1_cyc_paye...

3468
来自专栏杨建荣的学习笔记

不经意发现的dba_objects和dba_tables中的细节(r7笔记第56天)

今天有一个同学问我一个问题,因为白天比较忙也没有在意,在下班后坐地铁的时候抽空看了这个问题,感觉还是蛮有意思的。但是当时也没有任何答案,就准备自己回去好好实验一...

3743
来自专栏乐沙弥的世界

PL/SQL 包编译时hang住的处理

       最近PL/SQL包在编译时被hang住,起初以为是所依赖的对象被锁住。结果出乎意料之外。下面直接看代码演示。

926
来自专栏大内老A

谈谈基于SQL Server 的Exception Handling[中篇]

三、TRY CATCH & Return 在上面一节中,我通过RAISERROR重写了创建User的Stored procedure,实际上上面的Stored ...

1846

扫码关注云+社区

领取腾讯云代金券