mysql gtid 主从配置_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

--