mysqldump는 특정 라이브러리 마스터-슬레이브 아키텍처(GTID)를 신속하게 구축합니다.
- 黄舟원래의
- 2017-02-13 11:19:161408검색
데이터량이 적은 MySQL 데이터베이스의 경우 마스터-슬레이브 아키텍처를 구축하려면 MySQL GTID 기능과 결합된 mysqldump 도구를 사용하는 것이 좋습니다. , 고가용성은 매우 쉽습니다. 이 기사는 mysqldump를 기반으로 gtid 마스터-슬레이브 구축에 대한 보충 자료입니다. 전체 인스턴스 레벨이 아닌 멀티 리포지토리 레벨 기반의 GTID 마스터-슬레이브 구현을 주로 소개합니다. 다음은 이 기사에 대한 자세한 설명과 예시입니다.
관련 지식 포인트 참조
mysqldump 기반 gtid 마스터-슬레이브 구축
MySQL GTID 오류 처리 요약
MySQL GTID 마스터-슬레이브 복제 구성
mysqldump 내보내기 데이터베이스 사용
1. 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 12362. 마스터-슬레이브 환경 구성
主服务器：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，需要手动启动3. 메인 데이터베이스 설정
演示环境
(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:/tmp4. 슬레이브 라이브러리 측 설정
(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:5. 마스터-슬레이브 레코드 확인
--以下查询中可以看到，两个数据库的表中的记录在不停的增加
(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 |
+----------+
마스터-슬레이브 아키텍처 구축 총 데이터 양이 적은 MySQL 데이터베이스의 경우 mysqldump 도구를 사용하는 것이 좋습니다. MySQL GTID 기능과 결합하면 고가용성을 쉽게 만들 수 있습니다. 이 기사는 mysqldump를 기반으로 gtid 마스터-슬레이브 구축에 대한 보충 자료입니다. 전체 인스턴스 레벨이 아닌 멀티 리포지토리 레벨 기반의 GTID 마스터-슬레이브 구현을 주로 소개합니다. 다음은 이 기사에 대한 자세한 설명과 예시입니다.
관련 지식 포인트 참조
mysqldump를 기반으로 gtid 마스터-슬레이브 구축
MySQL GTID 오류 처리 요약
MySQL GTID 마스터-슬레이브 복제 구성
mysqldump를 사용하여 데이터베이스
1. 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 12362. 마스터-슬레이브 환경 구성
主服务器：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，需要手动启动3. 메인 라이브러리 측 설정
演示环境
(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:/tmp4 .슬레이브 라이브러리 측 설정
(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:5. 마스터-슬레이브 레코드 확인
--以下查询中可以看到，两个数据库的表中的记录在不停的增加
(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 |
+----------+
위는 특정 라이브러리를 빠르게 빌드하기 위한 mysqldump의 내용입니다. 라이브러리 마스터-슬레이브 아키텍처(GTID) 관련 내용은 PHP 중국어 넷(www.php.cn)을 참고하세요!
성명：
본 글의 내용은 네티즌들의 자발적인 기여로 작성되었으며, 저작권은 원저작자에게 있습니다. 본 사이트는 이에 상응하는 법적 책임을 지지 않습니다. 표절이나 침해가 의심되는 콘텐츠를 발견한 경우 admin@php.cn으로 문의하세요.