Database
Mysql Tutorial
mysqldump quickly builds a specific library master-slave architecture (GTID)
mysqldump quickly builds a specific library master-slave architecture (GTID)
For a MySQL database with a small amount of data to build a master-slave architecture, it is a good choice to use the mysqldump tool to implement it. Combined with the MySQL GTID feature, it makes high availability a breeze. This article is a supplement to building gtid master-slave based on mysqldump. It mainly introduces the implementation of GTID master-slave based on multi-repository level, that is, not the entire instance level. The following is a detailed description and examples of this article.
Related knowledge point reference
Build gtid master-slave based on mysqldump
MySQL GTID error handling summary
Configure MySQL GTID master-slave replication
Use mysqldump Export database
1. GTID parameters when mysqldump
1 2 3 4 5 6 7 8 9 10 11 12 |
|
2. Master-slave environment configuration
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 |
|
3. Settings on the main database side
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 |
|
4. Slave Library-side settings
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 |
|
5. Verify the master-slave record
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
|
##To build a master-slave architecture for a MySQL database with a small amount of data, use The mysqldump tool is a good choice to implement, and combined with the MySQL GTID feature, it makes high availability a breeze. This article is a supplement to building gtid master-slave based on mysqldump. It mainly introduces the implementation of GTID master-slave based on multi-repository level, that is, not the entire instance level. The following is a detailed description and examples of this article.Related knowledge point reference
Build gtid master-slave based on mysqldump
MySQL GTID error handling summary
Configure MySQL GTID master-slave replication
Use mysqldump to export the database
1
2
3
4
5
6
7
8
9
10
11
12
# 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
Copy after loginCopy after login
2. Master-slave environment configuration1 2 3 4 5 6 7 8 9 10 11 12 |
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
主服务器：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，需要手动启动
Copy after loginCopy after login
3. Settings on the main library side1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 |
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
演示环境
(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
Copy after loginCopy after login
4. Settings on the slave library side1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 |
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
(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:
Copy after loginCopy after login
5. Verify the master-slave record1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 |
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
--以下查询中可以看到，两个数据库的表中的记录在不停的增加
(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 |
+----------+
Copy after loginCopy after login
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
|
Hot AI Tools
Undresser.AI Undress
AI-powered app for creating realistic nude photos
AI Clothes Remover
Online AI tool for removing clothes from photos.
Undress AI Tool
Undress images for free
Clothoff.io
AI clothes remover
Video Face Swap
Swap faces in any video effortlessly with our completely free AI face swap tool!
Hot Article
Hot Tools
Notepad++7.3.1
Easy-to-use and free code editor
SublimeText3 Chinese version
Chinese version, very easy to use
Zend Studio 13.0.1
Powerful PHP integrated development environment
Dreamweaver CS6
Visual web development tools
SublimeText3 Mac version
God-level code editing software (SublimeText3)
Hot Topics
1655
14
1413
52
1306
25
1252
29
1226
24
Starting from scratch: Springboot guide to quickly build kafka integrated environment
Feb 01, 2024 am 09:29 AM
Overview of Springboot integrated Kafka Apache Kafka is a distributed streaming service that allows you to produce, consume and store data with extremely high throughput. It is widely used to build a wide variety of applications such as log aggregation, metric collection, monitoring, and transactional data pipelines. Springboot is a framework for simplifying Spring application development. It provides out-of-the-box autowiring and conventions to easily integrate Kafka into Spring applications
How to use component library to quickly build pages in uniapp
Oct 21, 2023 am 09:49 AM
How to use component libraries to quickly build pages in uniapp When developing mobile applications, we often need to use component libraries to quickly build pages. The component library provides a series of encapsulated components. Developers only need to use them according to the specifications of the component library, which greatly improves development efficiency. This article will introduce how to use the component library to quickly build pages in uniapp, and provide specific code examples. 1. Select a component library First, we need to choose a suitable component library to use. In uniapp, commonly used component libraries include uVi
Development suggestions: How to use the ThinkPHP framework to quickly build applications
Nov 23, 2023 am 09:56 AM
Development suggestions: How to use the ThinkPHP framework to quickly build applications Introduction: In the Internet era, rapid application development has become the key to business success. In order to meet this demand, many developers began to seek efficient development frameworks. Among many frameworks, ThinkPHP has become the first choice of many developers because of its simplicity, ease of use, efficiency and stability. This article will share with you some suggestions for quickly building applications using the ThinkPHP framework. 1. Choose the appropriate version. The ThinkPHP framework has multiple versions to choose from.
Project practice: Experience summary on how to use CSS framework to quickly build web pages
Nov 02, 2023 am 10:36 AM
Project practice: Experience summary on how to use CSS framework to quickly build web pages. In today's Internet era, web pages have become an indispensable part of our daily life and work. To build web pages quickly and efficiently, CSS frameworks are widely used. This article will share some experience summaries to help everyone better use the CSS framework to quickly build web pages. 1. Understand different CSS frameworks Before starting to use CSS frameworks, you must first understand different frameworks and choose the framework that suits your project needs. Currently commonly used CSS frameworks include Bo
Use Java Jersey to quickly build a RESTful API, which is simple, efficient and easy to use.
Feb 27, 2024 pm 06:10 PM
In software development, RESTful API is widely used in various fields. It is an application programming interface based on the HTTP protocol, which can easily realize the interaction between different components. In the field of Java, the Jersey framework has become a popular choice for building RESTful APIs due to its simplicity, efficiency, and ease of use. 1. Introduction Jersey is an open source Java framework that provides a complete toolkit to help developers quickly build RESTful APIs. The design philosophy of the Jersey framework is simplicity, efficiency, and ease of use. It allows developers to focus on business logic without worrying about underlying details. 2. Features The main features of the Jersey framework include: Simple: J
How to use Webman to quickly build a personal blog
Aug 25, 2023 pm 01:33 PM
How to use Webman to quickly build a personal blog Introduction: In the information age, personal blogs have become an important way for many individuals to express themselves. There are many ways to build a personal blog, and one of the quick and convenient ways is to use Webman. This article will introduce how to use Webman to build a personal blog and provide code examples for reference. 1. Introduction to Webman Webman is an open source blog engine based on Python, which is lightweight and easy to use. It uses the Flask framework as the bottom layer
How to use PHP technology to quickly build powerful websites and applications in 2023?
Sep 10, 2023 am 08:45 AM
How to use PHP technology to quickly build powerful websites and applications in 2023? With the rapid development of the Internet, websites and applications have become a part of people's lives. Over the past few years, PHP has been playing an important role in website and application development as a powerful server-side scripting language. In 2023, using PHP technology to quickly build powerful websites and applications will become even more of a trend. First, in order to quickly build a powerful website or application, we need to choose a
Introduction to the quick construction method of layout component library in Vue documentation
Jun 21, 2023 am 09:28 AM
Vue.js is a popular front-end development framework. According to Vue’s official documentation, the layout component library is an important tool that can help developers quickly build web page layouts. This article will introduce how to quickly build the layout component library in the Vue document. In Vue.js, the layout component library is a set of components used to create basic web page layouts. The official Vue.js documentation provides a layout component library called VueLayout, which can be used to quickly build basic pages. Use VueLayout to build the page, mainly
