共计 7135 个字符，预计需要花费 18 分钟才能阅读完成。
对于数据总量不大的 MySQL 数据库搭建主从架构，借助 mysqldump 工具来实现是不错的选择，再结合 MySQL GTID 特性，使得高可用轻而易举。本文是基于 mysqldump 搭建 gtid 主从的补充。主要是介绍基于多库级别实现 GTID 主从，即非整个实例级别。下面是本文的具体描述及示例。
一、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='Master', | |
| -> 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://www.linuxidc.com | |
| 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 | | |
| +----------+ |
本文永久更新链接地址 ：http://www.linuxidc.com/Linux/2016-10/136127.htm
正文完
星哥玩云-微信公众号
