#### 1 MariaDB主从复制原理
主数据库更新数据,生成二进制日志写入到binlog中,启动dumpthread 线程(salve服务线程),
从数据库开启IOthread线程读取主数据库binlog日志,写入到中继日志(relay log)中并启动SQL thread 读取日志,更新数据到从数据库中。实现主从数据一致。
#### 2 MariaDB一主一从架构构建
准备两台纯新的CentOS7.6服务器,其中主机称为node1的服务器地址为192.168.130.132,主机称为node2的服务器地址为192.168.130.133
主节点node1 192.168.130.132 配置文件
[mysqld]
log_bin
server-id=132
#修改数据库配置文件需要重启数据库
systemctl restart mariadb
主服务器主节点node1 192.168.130.132 创建可以复制权限的账号及密码
GRANT REPLICATION SLAVE ON *.* TO repl@'192.168.130.%' IDENTIFIED BY 'replpass';
show master statusG #查询当前主库日志文件及位置
File: maridb-bin.000004
Position: 154
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)
从节点node2 192.168.130.133 配置文件
[mysqld]
log_bin
server-id=133
read_only=on #默认会开启
relay_log=relay-log
relay_log_index=relay-log.index
#修改数据库配置文件需要重启数据库
systemctl restart mariadb
#从节点使用有权限的账号及密码连接主库,通过日志文件及位置,开启同步。直至合主库数据一致
CHANGE MASTER TO MASTER_HOST='192.168.130.132',
MASTER_USER='repl',
MASTER_PASSWORD='replpass',
MASTER_LOG_FILE='maridb-bin.000004',
MASTER_LOG_POS='154';
start slave;
show slave statusG
(注意 yes ,yes 代表同步开启)
#### 3 MariaDB级联复制
上一节中我们完成了一主一从的MariaDB的主从复制架构,但是有的时候,我们期望有一个后备的MariaDB的节点,用只
动词}备份存储数据,不需要对外提供服务。为了实现该功能,我们可以在之前的一主一从架构上进行调整,即将从节点的二进
制文件打开,然后给他配置一个远程同步数据用户,接着使用一台新的服务器做为从服务器的从属,同步从服务器数据的即
柯林斯,下面我们在原来的架构上添加一台新的CentOS7.6,作为节点3,IP地址为192.168.132.134
#主从同步一致的情况下
#中间节点 node2 192.168.130.133 配置文件
[mysqld]
log_bin
server-id=133
read_only=on #默认会开启
log_slave_updates
#修改数据库配置文件需要重启数据库
systemctl restart mariadb
#中间节点服务器全库备份库
mysqldump -A -F --single-transaction --master-data=2 >/back/all.sql
scp /back/all.sql root@192.168.130134:/root/
#node3 192.168.130.134 配置文件
[mysqld]
log_bin
server-id=134
read_only=on
#修改数据库配置文件需要重启数据库
systemctl restart mariadb
vim /back/all.sql
CHANGE MASTER TO MASTER_HOST='192.168.130.133',
MASTER_USER='repl',
MASTER_PASSWORD='replpass',
MASTER_LOG_FILE='maridb-bin.000004',
MASTER_LOG_POS='154';
source /back/all.sql;
show master logs;
start slave statusG
#### 4 MariaDB半同步复制
主节点半同步设置
[mysqld]
server-id=132
log-bin
plugin_load_add=semisync_master
rpl_semi_sync_master_enabled=on
rpl_semi_sync_master_timeout = 20000
#修改数据库配置文件需要重启数据库
systemctl restart mariadb
注意官方提供安装插件不用修改配置文件重启库
主节点半同步变量查看
show GLOBAL VARIABLES LIKE '%semi%';
主节点半同步状态查看
show GLOBAL STATUS LIKE ‘%semi%’;
从节点配置
[mysqld]
server-id=133
plugin_load_add = semisync_slave
rpl_semi_sync_slave_enabled=on
从节点半同步变量查看
show GLOBAL VARIABLES LIKE '%semi%';
从节点半同步状态查看
show GLOBAL STATUS LIKE ‘%semi%’;
#### 5 MariaDB高可用方案MHA
准备三台新安装的CentOS7.6服务器,并使用yum安装MariaDB10.2.23,主机名分别为node1,node2,node3,
IP地址分别为192.168.130.132-134
1 ssh 验证省略 (实现三台机器互联)
2 安装 mha
三个节点服务器分别安装 yum -y install mariadb-server
192.168.130.132 master 主节点安装
yum -y install mha4mysql-manger-0.56-0.el6.noarch.rpm
yum -y install mha4mysql-manger-0.56-0.el6.noarch.rpm
192.168.130.133 ,192.168.130.134 slave 从节点安装
yum -y install mha4mysql-manger-0.56-0.el6.noarch.rpm
3 修改 192.168.130.132 master配置及mha manger 配置文件
[mysqld]
server_id=132
log-bin
skip_name_resolve=1 #禁止反向解析
general_log
systemctl restart mariadb
创建复制的主的从账号
grant replication slave on *.* to repl@'192.168.130.%' identified by 'replpass'
创建可以切换的主账号
grant all on * .* to mhauser@'192.168.130.%' identified by 'mhapass'
vip 配置供外部访问,主库崩溃可以飘到其他机器上的IP
ifconfig eht0:1 192.168.130.100/24
mkdir //etc/mastermha/
vim /etc/mastermha/app.cnf
[server default]
user=mhauser
password=mhapass
manger_workdir=/data/mastermha/app/
manger_log=/data/mastermha/app/
remote_workdir=/data/mastermha/app/
ssh_user=root
repl_user=repl
repl_password=replpass
ping_interval=1
master_ip_failover_script=/usr/local/bin/master_ip_failover
report_script=/usr/local/bin/sendmail.sh
check_repl_delay=0
[server1]
hostname=192.168.130.133
[server2]
hostname=192.168.130.134
cat master_ip_failover
#!/usr/bin/env perl
use strict;
use warnings FATAL => 'all';
use Getopt::Long;
my (
$command, $ssh_user, $orig_master_host, $orig_master_ip,
$orig_master_port, $new_master_host, $new_master_ip, $new_master_port
);
my $vip = '192.168.130.100';#设置Virtual IP
my $gateway = '192.168.130.254';#网关Gateway IP
my $interface = 'eth0';
my $key = "1";
my $ssh_start_vip = "/sbin/ifconfig $interface:$key $vip;/sbin/arping -I $interface -c 3 -s $vip $gateway >/dev/null 2>&1";
my $ssh_stop_vip = "/sbin/ifconfig $interface:$key down";
GetOptions(
'command=s' => $command,
'ssh_user=s' => $ssh_user,
'orig_master_host=s' => $orig_master_host,
'orig_master_ip=s' => $orig_master_ip,
'orig_master_port=i' => $orig_master_port,
'new_master_host=s' => $new_master_host,
'new_master_ip=s' => $new_master_ip,
'new_master_port=i' => $new_master_port,
);
exit &main();
sub main {
print "
IN SCRIPT TEST====$ssh_stop_vip==$ssh_start_vip===
";
if ( $command eq "stop" || $command eq "stopssh" ) {
# $orig_master_host, $orig_master_ip, $orig_master_port are passed.
# If you manage master ip address at global catalog database,
# invalidate orig_master_ip here.
my $exit_code = 1;
eval {
print "Disabling the VIP on old master: $orig_master_host
";
&stop_vip();
$exit_code = 0;
};
if ($@) {
warn "Got Error: $@
";
exit $exit_code;
}
exit $exit_code;
}
elsif ( $command eq "start" ) {
# all arguments are passed.
# If you manage master ip address at global catalog database,
# activate new_master_ip here.
# You can also grant write access (create user, set read_only=0, etc) here.
my $exit_code = 10;
eval {
print "Enabling the VIP - $vip on the new master - $new_master_host
";
&start_vip();
$exit_code = 0;
};
if ($@) {
warn $@;
exit $exit_code;
}
exit $exit_code;
}
elsif ( $command eq "status" ) {
print "Checking the Status of the script.. OK
";
`ssh $ssh_user@$orig_master_host " $ssh_start_vip "`;
exit 0;
}
else {
&usage();
exit 1;
}
}
# A simple system call that enable the VIP on the new master
sub start_vip() {
`ssh $ssh_user@$new_master_host " $ssh_start_vip "`;
}
# A simple system call that disable the VIP on the old_master
sub stop_vip() {
`ssh $ssh_user@$orig_master_host " $ssh_stop_vip "`;
}
sub usage {
print
"Usage: master_ip_failover --command=start|stop|stopssh|status --orig_master_host=host --orig_master_ip=ip --orig_master_port=port --new_master_host=host --new_master_ip=ip --new_master_port=port
";
}
cat sendmail.sh
#!/bin/bash
echo "MySQL is down" | mail -s "MHA Warning" root@tyzh.com
slave 从节点192.168.130.133,192.168.130.134配置
[mysqld]
server_id=133(134机器需要修改)
log-bin
skip_name_resolve=1 #禁止反向解析
relay_log_purge=0
read_only
systemctl restart mriadb
CHANGE MASTER TO MASTER_HOST='192.168.130.133',
MASTER_USER='repl',
MASTER_PASSWORD='replpass',
MASTER_LOG_FILE='maridb-bin.000004',
MASTER_LOG_POS='154';
start slave statusG
检查
masterha_check_ssh --conf=/etc/mastermha/app.cnf
masterha_check_repl --conf=/etc/mastermha/app.cnf
启动mha
nohup masterha_manager --conf =/etc/mastermha/app.cnf &> /dev/null
masterha_check_status --conf =/etc/mastermha/app.cnf