Centos7-MariadbDB多实例基于GTID模式的主从配置

Mariadb GTID

全局事务ID（Global transaction ID，GTID）为每个Event Group（就是一系列Event组成的一个原子单元，要么一起提交要么都无法提交）引入了一个标识，因此GTID是标识“事务”的最佳方式（尽管Event里面还包含一些非事务的DML语句和DDL，它们可以作为一个单独的Event Group）。每当一个Event Group从Master复制到Slave时，它的GTID也通过GTID Event被传到Slave。因为每个GTID在整个复制拓扑结构中都是一个唯一标志，所以这使得在不同的实例之间识别相同的Binlog Events非常简单，然而在有GTID之前，想做到这点是很困难的。MariaDB从10.0.2开始提供GTID支持，但是MariaDB的GTID与MySQL的GTID在实现原理上并不相同，因为MariaDB支持像多源复制啊、多主复制等官方暂时还没考虑的复制模型。

优势

使用GTID有两个主要的优势：

1.在级联复制、一主多从等复杂的复制场景下，可以更简单地将一个Slave的复制修改到另一个Master上，而不用人工去寻找复制的起始位点。从5.0一路走来的同学应该很能理解这种痛苦。

这是因为Slave会保存最后一个执行的Event Group的GTID，因此可以通过这个GTID很容易地在新Master上找到相应的复制起点。而在使用Binlog File和Binlog Pos的时代，这是很难办到的。

2.Slave的状态是Crash-Safe的。

Slave的执行状态（最后一个执行的GTID）被记录在mysql.gtid_slave_pos系统表中。如果这张表使用的是事务引擎（例如InnoDB，默认就是），那么修改用户表的数据和修改Slave状态的系统表这两个操作在就可以放在一个事务中完成，这就保证了Slave状态是Crash-Safe的，如果Slave崩溃了，那么Crash Recovery就可以在重启的时候把用户数据表和Slave状态系统表恢复到一个一致的位点。而在非GTID复制的旧版本中，这也是做不到的，Slave状态只是简单的存放在relay-log.info文件中（MySQL是可以把Binlog File和Binlog Pos也存在slave_relay_log_info和slave_master_info系统表中），而且需要靠不断的fsync()调用才能同步到磁盘上，一旦宕机很可能导致Slave状态跟实际不一致（但是也只有事务引擎的DML能保证一致，非事务引擎和DDL本身就不是Crash-Safe的）。

基于这两个优势，通常我们都建议使用GTID复制。并且传统的基于Binlog文件位置的复制方式，和GTID的复制方式，在MariaDB中是可以相互之间平滑的切换的。

每个GTID，都包含三个数字部分，分别用’-‘号隔开，例如：

0-1-10

第一个数字’0’是Domain ID，这是一个32位的无符号整型。

第二个数字’1’是Server ID，这跟传统的主备复制中Server ID的含义是一样的，也是一个32位无符号整型。因此在一个复制拓扑中每个实例的Server ID必须是唯一的。

第三个数字是序列号（SequenceNumber）。这是一个64位的无符号整型。每个新产生的Event Group记录到Binlog时都会新生成一个单调递增的序列号。

这个规则使得(server_id, sequence_number)总是唯一的，因此GTID也是全局唯一的。

配置mariadb yum源

# vim /etc/yum.repos.d/MariaDB10.repo

[mariadb]

name = MariaDB

baseurl =http://yum.mariadb.org/10.1/centos7-amd64

gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB

gpgcheck=1

安装mariadb

# yum install -y MariaDB-server MariaDB-client tcl bzip2 lsof

创建mariadb 数据和日志目录

# mkdir -p /data/mysql/330{6,7}

# mkdir -p /back/binlog/330{6,7}

# chown -R mysql.mysql /data/mysql

# chown -R mysql.mysql /back/binlog

创建多实例

# mysql_install_db --datadir=/data/mysql/3306 --user=mysql

# mysql_install_db --datadir=/data/mysql/3307 --user=mysql

Master3306配置文件，此处的配置是基础配置，优化参数自行添加

# vim /data/mysql/3306/my.cnf

[mysqld]

datadir=/data/mysql/3306

port=3306

socket=/data/mysql/3306/mysql.sock

slow-query-log=1 #开启慢查询日志

slow-query-log-file=/back/binlog/3306/mysql-slow.log #日志存储位置

long_query_time=1 #执行超过多长时间的sql会记录下来

binlog_format=ROW #binlog日志格式

skip-name-resolve #跳过域名解析

server-id=1

log-bin=/back/binlog/3306/master-bin

log-bin-index=/back/binlog/3306/master-bin.index

log-error=/back/binlog/3306/mysql-err.log

master-info-repository=TABLE

relay-log-info-repository=TABLE #此两项为打开从服务器崩溃二进制日志功能，信息记录在事物表而不是保存在文件

sync-master-info=1 #值为1确保信息不会丢失

slave-parallel-threads=2 #同时启动多少个复制线程，最多与要复制的数据库数量相等即可

binlog-checksum=CRC32 #效验码

master-verify-checksum=1 #启动主服务器效验

slave-sql-verify-checksum=1 #启动从服务器效验

binlog-rows-query-log-events=1 #用于在二进制日志详细记录事件相关的信息，可降低故障排除的复杂度；

[mysqld_safe]

pid-file=/data/mysql/3306/mysql.pid

[client-server]

socket=/data/mysql/3306/mysql.sock

Master 3307配置文件

# vim /data/mysql/3307/my.cnf

[mysqld]

datadir=/data/mysql/3307

port=3307

socket=/data/mysql/3307/mysql.sock

slow-query-log=1 #开启慢查询日志

slow-query-log-file=/back/binlog/3307/mysql-slow.log #日志存储位置

long_query_time=1 #执行超过多长时间的sql会记录下来

binlog_format=ROW #binlog日志格式

skip-name-resolve #跳过域名解析

server-id=1

log-bin=/back/binlog/3307/master-bin

log-bin-index=/back/binlog/3307/master-bin.index

log-error=/back/binlog/3307/mysql-err.log

master-info-repository=TABLE

relay-log-info-repository=TABLE #此两项为打开从服务器崩溃二进制日志功能，信息记录在事物表而不是保存在文件

sync-master-info=1 #值为1确保信息不会丢失

slave-parallel-threads=2 #同时启动多少个复制线程，最多与要复制的数据库数量相等即可

binlog-checksum=CRC32 #效验码

master-verify-checksum=1 #启动主服务器效验

slave-sql-verify-checksum=1 #启动从服务器效验

binlog-rows-query-log-events=1 #用于在二进制日志详细记录事件相关的信息，可降低故障排除的复杂度；

[mysqld_safe]

pid-file=/data/mysql/3307/mysql.pid

[client-server]

socket=/data/mysql/3307/mysql.sock

Slave 3306配置文件

# vim /data/mysql/3306/my.cnf

[mysqld]

datadir=/data/mysql/3306

port=3306

socket=/data/mysql/3306/mysql.sock

slow-query-log=1 #开启慢查询日志

slow-query-log-file=/back/binlog/3306/mysql-slow.log #日志存储位置

long_query_time=1 #执行超过多长时间的sql会记录下来

binlog_format=ROW #binlog日志格式

skip-name-resolve #跳过域名解析

server-id=2

log-bin=/back/binlog/3306/master-bin

log-bin-index=/back/binlog/3306/master-bin.index

log-error=/back/binlog/3306/mysql-err.log

master-info-repository=TABLE

relay-log-info-repository=TABLE #此两项为打开从服务器崩溃二进制日志功能，信息记录在事物表而不是保存在文件

sync-master-info=1 #值为1确保信息不会丢失

slave-parallel-threads=2 #同时启动多少个复制线程，最多与要复制的数据库数量相等即可

binlog-checksum=CRC32 #效验码

master-verify-checksum=1 #启动主服务器效验

slave-sql-verify-checksum=1 #启动从服务器效验

binlog-rows-query-log-events=1 #用于在二进制日志详细记录事件相关的信息，可降低故障排除的复杂度；

[mysqld_safe]

pid-file=/data/mysql/3306/mysql.pid

[client-server]

socket=/data/mysql/3306/mysql.sock

Slave 3307配置文件

# vim /data/mysql/3307/my.cnf

[mysqld]

datadir=/data/mysql/3307

port=3307

socket=/data/mysql/3307/mysql.sock

slow-query-log=1 #开启慢查询日志

slow-query-log-file=/back/binlog/3307/mysql-slow.log #日志存储位置

long_query_time=1 #执行超过多长时间的sql会记录下来

binlog_format=ROW #binlog日志格式

skip-name-resolve #跳过域名解析

server-id=2

log-bin=/back/binlog/3307/master-bin

log-bin-index=/back/binlog/3307/master-bin.index

log-error=/back/binlog/3307/mysql-err.log

master-info-repository=TABLE

relay-log-info-repository=TABLE #此两项为打开从服务器崩溃二进制日志功能，信息记录在事物表而不是保存在文件

sync-master-info=1 #值为1确保信息不会丢失

slave-parallel-threads=2 #同时启动多少个复制线程，最多与要复制的数据库数量相等即可

binlog-checksum=CRC32 #效验码

master-verify-checksum=1 #启动主服务器效验

slave-sql-verify-checksum=1 #启动从服务器效验

binlog-rows-query-log-events=1 #用于在二进制日志详细记录事件相关的信息，可降低故障排除的复杂度；

[mysqld_safe]

pid-file=/data/mysql/3307/mysql.pid

[client-server]

socket=/data/mysql/3307/mysql.sock

3306启动脚本

# vim /etc/init.d/mysql3306

#init

port=3306

mysql_user="root"

mysql_pwd="123456"

CmdPath="/usr/bin"

#startup function

function_start_mysql()

{

printf "Starting MySQL...\n"

/bin/sh ${CmdPath}/mysqld_safe --defaults-file=/data/mysql/3306/my.cnf 2>&1 > /dev/null &

}

#stop function

function_stop_mysql()

{

printf "Stoping MySQL...\n"

${CmdPath}/mysqladmin -u ${mysql_user} -p${mysql_pwd} -S /data/mysql/3306/mysql.sock shutdown

}

#restart function

function_restart_mysql()

{

printf "Restarting MySQL...\n"

function_stop_mysql

sleep 2

function_start_mysql

}

case $1 in

start)

function_start_mysql

;;

stop)

function_stop_mysql

;;

restart)

function_restart_mysql

;;

printf "Usage: /data/${port}/mysql {start|stop|restart}\n"

esac

3307启动脚本

# vim /etc/init.d/mysql3307

#init

port=3307

mysql_user="root"

mysql_pwd="123456"

CmdPath="/usr/bin"

#startup function

function_start_mysql()

{

printf "Starting MySQL...\n"

/bin/sh ${CmdPath}/mysqld_safe --defaults-file=/data/mysql/3307/my.cnf 2>&1 > /dev/null &

}

#stop function

function_stop_mysql()

{

printf "Stoping MySQL...\n"

${CmdPath}/mysqladmin -u ${mysql_user} -p${mysql_pwd} -S /data/mysql/3307/mysql.sock shutdown

}

#restart function

function_restart_mysql()

{

printf "Restarting MySQL...\n"

function_stop_mysql

sleep 2

function_start_mysql

}

case $1 in

start)

function_start_mysql

;;

stop)

function_stop_mysql

;;

restart)

function_restart_mysql

;;

printf "Usage: /data/${port}/mysql {start|stop|restart}\n"

esac

启动mariadb

# chown -R mysql.mysql /data/mysql

# chmod +x /etc/init.d/mysql330{6,7}

# /etc/init.d/mysql3306 start

# /etc/init.d/mysql3307 start

设置root密码

# mysqladmin -uroot password "123456" -S /data/mysql/3306/mysql.sock

# mysqladmin -uroot password "123456" -S /data/mysql/3307/mysql.sock

添加开机启动

# vim /etc/rc.d/rc.local

/etc/init.d/mysql3306 start

/etc/init.d/mysql3307 start

# chmod +x /etc/rc.local

安装jemalloc

# wgethttp://www.canonware.com/download/jemalloc/jemalloc-4.2.0.tar.bz2

# tar -xjf jemalloc-4.2.0.tar.bz2

# cd jemalloc-4.2.0

# ./configure --prefix=/usr/local/jemalloc --libdir=/usr/local/lib

# make && make install

# make clean

# echo "/usr/local/lib" > /etc/ld.so.conf.d/usr_local_lib.conf

# /sbin/ldconfig

配置mariadb使用jemalloc

# sed -i 's@executing mysqld_safe@executing mysqld_safe\nexport LD_PRELOAD=/usr/local/lib/libjemalloc.so@' /usr/bin/mysqld_safe

# lsof -n | grep jemalloc

配置GTID模式的主从

Master 3306创建主从同步用户

# mysql -uroot -p123456 -S /data/mysql/3306/mysql.sock

>grant replication slave,replication client on *.* to 'rep'@'192.168.1.%' identified by '123456';

>flush privileges;

导出数据库，将导出的数据放到从库上

# mysqldump -uroot -p123456 -S /data/mysql/3306/mysql.sock -A > dataall-3306.sql

Master 3307创建主从同步用户

# mysql -uroot -p123456 -S /data/mysql/3307/mysql.sock

>grant replication slave,replication client on *.* to 'rep'@'192.168.1.%' identified by '123456';

>flush privileges;

导出数据库，将导出的数据放到从库上

# mysqldump -uroot -p123456 -S /data/mysql/3307/mysql.sock -A > dataall-3307.sql

Slave 3306开启主从同步

# mysql -uroot -p123456 -S /data/mysql/3306/mysql.sock < dataall-3306.sql

# mysql -uroot -p123456 -S /data/mysql/3306/mysql.sock

>CHANGE MASTER TO master_host="192.168.1.1", master_port=3306, master_user="rep",MASTER_PASSWORD='123456',master_use_gtid=slave_pos;

>start slave;

>show slave status\G;

Slave 3307开启主从同步

# mysql -uroot -p123456 -S /data/mysql/3307/mysql.sock < dataall-3307.sql

# mysql -uroot -p123456 -S /data/mysql/3307/mysql.sock

>CHANGE MASTER TO master_host="192.168.1.1", master_port=3307, master_user="rep",MASTER_PASSWORD='123456',master_use_gtid=slave_pos;

>start slave;

>show slave status\G;

在数据库重新初始化时，可能会出现输入密码无法登陆问题，需要重新设置密码

# vim my.cnf

skip-grant-tables

# /etc/init.d/mysql3307 start

# mysql -uroot -p -S /data/mysql/3307/mysql.sock

>use mysql

>update user set password = password("123456") where user = "root";

删除my.cnf中的skip-grant-tables，重启mysql就可以了

mysql锁定所有表

> flush tables with read lock;

> unlock tables;

这个命令是全局读锁定，执行了命令之后所有库所有表都被锁定只读，一般都是用在数据库联机备份，这个时候数据库的写操作将被阻塞，读操作顺利进行

mysql锁定单个表

> lock tables tbl_name [AS alias] {read [local] | [low_priority] write}

> unlock tables;

这个命令是表级别的锁定，可以定制锁定某一个表，不影响其它表的写操作

这两个锁表语句在执行的时候都需要注意个特点，就是隐式提交的语句，在退出mysql终端的时候都会隐式的执行unlock tables，也就是如果要让表锁定生效就必须一直保持会话

最后编辑于：2017.12.05 01:59:47

人面猴
序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
沈念sama阅读 158,847评论 4赞 362
死咒
序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
沈念sama阅读 67,208评论 1赞 292
救了他两次的神仙让他今天三更去死
文/潘晓璐我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
开封第一讲书人阅读 108,587评论 0赞 243
道士缉凶录：失踪的卖姜人
文/不坏的土叔我叫张陵，是天一观的道长。经常有香客问我，道长，这世上最难降的妖魔是什么？我笑而不...
开封第一讲书人阅读 43,942评论 0赞 205
港岛之恋（遗憾婚礼）
正文为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
茶点故事阅读 52,332评论 3赞 287
恶毒庶女顶嫁案：这布局不是一般人想出来的
文/花漫我一把揭开白布。她就那样静静地躺着，像睡着了一般。火红的嫁衣衬着肌肤如雪。梳的纹丝不乱的头发上，一...
开封第一讲书人阅读 40,587评论 1赞 218
城市分裂传说
那天，我揣着相机与录音，去河边找鬼。笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。我是一名探鬼主播，决...
沈念sama阅读 31,853评论 2赞 312
双鸳鸯连环套：你想象不到人心有多黑
文/苍兰香墨我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
开封第一讲书人阅读 30,568评论 0赞 198
万荣杀人案实录
序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
沈念sama阅读 34,273评论 1赞 242
护林员之死
正文独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋以下内容为张勋视角年9月15日...
茶点故事阅读 30,542评论 2赞 246
白月光启示录
正文我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
茶点故事阅读 32,033评论 1赞 260
活死人
序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
沈念sama阅读 28,373评论 2赞 253
日本核电站爆炸内幕
正文年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
茶点故事阅读 33,031评论 3赞 236
男人毒药：我在死后第九天来索命
文/蒙蒙一、第九天我趴在偏房一处隐蔽的房顶上张望。院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
开封第一讲书人阅读 26,073评论 0赞 8
一桩弑父案，背后竟有这般阴谋
文/苍兰香墨我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。一阵脚步声响...
开封第一讲书人阅读 26,830评论 0赞 195
情欲美人皮
我被黑心中介骗来泰国打工，没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。一个月前我还...
沈念sama阅读 35,628评论 2赞 274
代替公主和亲
正文我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
茶点故事阅读 35,537评论 2赞 269

Centos7-MariadbDB多实例基于GTID模式的主从配置

推荐阅读更多精彩内容