全部栏目

首页 旅游资讯 线路攻略 景点大全 国内游 境外游 美食特产
您的当前位置：首页正文

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

2020-11-09 来源：我们爱旅游

相关知识点参考
基于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 |
 +----------+

DBA牛鹏社(SQL/NOSQL/LINUX)

对于数据总量不大的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 |
 +----------+

DBA牛鹏社(SQL/NOSQL/LINUX)