数据库 - MySQL 高可用主从读写分离 - 《博客专栏》

主从
主主
主主+keepalived
1主 2从配合 MHA 高可用
主从配合mycat读写分离
- mycat
mycat + keepalived
1主 2从配合 MGR 实现多主（忽略不常用了解即可）
常见的错误：

主从

主库开启一个io线程，从库开启一个io和一个sql线程

主库二进制日志，从库中继日志

从库通过io线程实时监控主库，主库做操作后把做操作的二进制日志通过io线程写到从库的中继日志

这时候从库的sql线程监控到中继日志有变动，就会实时的把变动的语句在从库上执行达到同步效果

--主库：
#/etc/my.cnf下的mysqld区域里加入
log-bin=mysql-bin
server-id=10
binlog-ignore-db=mysql
binlog_format=mixed
expire_logs_days=10
#重启MySQL后在mysql里面创建对应用户及规则
systemctl restart mysqld
mysql> grant all on *.* to slave@"%" identified by "slave123";
mysql> flush privileges;
#查询主库二进制文件
mysql> show master status;
--从库：
#测试主库连接
mysql -uslave -pslave123 -h172.16.0.7
#/etc/my.cnf下的mysqld区域里加入
log-bin=mysql-bin
server-id=11
#重启MySQL后执行同步语句
systemctl restart mysqld
mysql> change master to master_host="192.168.0.7",
master_user="slave",
master_password="slave123",
master_port=3306,
master_log_file="mysql-bin.000001",
master_log_pos=587;
mysql> start slave;
#查询从库的主从状态
mysql> show slave status\G

主主

同理主从，在主从的基础上在做一遍方向主从

在主从的基础上加内容
--主库
#/etc/my.cnf下的mysqld区域里加入
log-bin=mysql-bin
server-id=1
#replicate-do-db=需要复制的数据库名，如果复制多个数据库，重复设置这个选项即可
auto_increment_increment=2    #步进值auto_imcrement。一般有n台主MySQL就填n auto_increment_offset=1 #起始值。一般填第n台主MySQL。此时为第二台主MySQL
--从库
#/etc/my.cnf下的mysqld区域里加入
log-bin=mysql-bin
server-id=2
#replicate-do-db=需要复制的数据库名，如果复制多个数据库，重复设置这个选项即可
binlog-ignore-db=mysql
auto-increment-offset=2
auto-increment-increment=2
配置好以后两边都重启数据库
--从库
#创建用户给予其他主机可连接权限
mysql> grant all on *.* to slave@"%" identified by "slave123";
mysql> flush privileges;
--主库
#测试从库的用户是否可以连接
mysql -uslave -pslave123 -h192.168.0.8
#同步从库
mysql> change master to master_host="192.168.0.8",
master_user="slave",
master_password="slave123",
master_port=3306,
master_log_file="mysql-bin.000003",
master_log_pos=597;
mysql> start slave;
#查询从库的主从状态
mysql> show slave status\G

主主+keepalived

keepalived生成虚拟IP 保证一台主mysql数据库有问题的时候自动漂移IP到另一台实现容灾

1、MySQL 01配置

#两台服务器都安装keepalived
yum -y install keepalived
#修改keepalived配置参数    /etc/keepalived/keepalived.conf
! Configuration File for keepalived
global_defs {
    notification_email {
        3037800336@qq.com
    }
    notification_email_from xxx@163.com
    smtp_server 127.0.0.1
    smtp_connect_timeout 30
    router_id kp1    #在另一台设置kp2不能重复
}
vrrp_script check_mysqld {
    script "/etc/keepalived/check_mysql.sh"
    interval 2
    weight -2
}
vrrp_instance cao {
    state BACKUP    #都配置陈backup
    interface ens33
    virtual_router_id 51
    priority 100    #另一台权重99
    advert_int 1
    nopreempt    #不抢占模式，只需在优先级高的上面配置，另一台不需要
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    track_script {
        check_mysqld
    }
    virtual_ipaddress {
        192.168.8.100 dev ens33
    }
smtp_alert    #开启VIP变换时邮件报警，没有这个就收不到邮件。
}
修改后重启keepalived
重启后 ip add 查看IP 一定要确保配置的虚拟IP能查询到

MySQL 高可用主从读写分离 - 图1

2、MySQL 02配置

#两台服务器都安装keepalived
yum -y install keepalived
#修改keepalived配置参数    /etc/keepalived/keepalived.conf
! Configuration File for keepalived
global_defs {
    notification_email {
        3037800336@qq.com
    }
    notification_email_from xxx@163.com
    smtp_server 127.0.0.1
    smtp_connect_timeout 30
    router_id kp2
}
vrrp_script check_mysqld {
    script "/etc/keepalived/check_mysql.sh"
    interval 2
    weight -2
}
vrrp_instance cao {
    state BACKUP    #都配置陈backup
    interface ens33
    virtual_router_id 51
    priority 99
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    track_script {
        check_mysqld
    }
    virtual_ipaddress {
        192.168.8.100 dev ens33
    }
smtp_alert    #开启VIP变换时邮件报警，没有这个就收不到邮件。
}
修改后重启keepalived
重启后 ip add 查看IP 一定要确保配置的虚拟IP能查询到

3、检测的check_mysqld脚本文件

vi /etc/keepalived/check_mysql.sh
#!/bin/bash
if [ "$(ps -ef | grep mysqld | grep -v grep)" == "" ];then
    systemctl restart mysqld
    sleep 2
    if [ "$(ps -ef | grep mysqld | grep -v grep)" == "" ];then
        systemctl stop keepalived
        fi
fi

1主 2从配合 MHA 高可用

为了实现高可用数据库做mha的时候两台从库需要禁止写权限，当故障发生自动切换master的时候会自动给上写在主库故障的时候自动切换slave为主库，并把相应的IP也分给slave，保证数据的正常使用

用到的node和manager包：https://wwa.lanzouo.com/b00v3idlc 密码:8ern
三台都要做ssh免密
三台的mysql配置文件my.cnf都要修改
在主从的基础上做的部署环境：
    master：192.168.8.76
    slave：192.168.8.77
    slave+MHA：192.168.8.78

1、master

做ssh免密分发密钥到其他机器
cd /root/.ssh
ssh -keygen
cp /root/.ssh/id_rsa.pub /root/.ssh/authorized_keys
chmod 700 /root/.ssh/authorized_keys
scp /root/.ssh/authorized_keys 192.168.8.77:/root/.ssh/
scp /root/.ssh/authorized_keys 192.168.8.78:/root/.ssh/
#修改MySQL的主配置文件my.cnf参数在下边
主要修改basedir以及server_id
创建相应的log-error文件  给600和mysql所属组所有者权限
systemctl restart mysqld
#安装mha node客户端
yum -y install epel-release perl-DBD-MySQL
rpm -ivh mha4mysql-node-0.58-0.el7.centos.noarch.rpm
#创建对应的用户
mysql> grant all on *.* to mha@"%" identified by "123";
mysql> grant all on *.* to rep@"%" identified by "123";
#做mysqlbinlog和mysql的软连接
ln -s /usr/local/mysql-5.7.26/bin/mysqlbinlog /usr/local/bin/mysqlbinlog
ln -s /usr/local/mysql-5.7.26/bin/mysql /usr/local/bin/mysql
#添加一个虚拟IP 需要与mha的IP漂移脚本的IP一致
ifconfig ens33:1 192.168.8.200/24

2、slave

做ssh免密分发密钥到其他机器
cd /root/.ssh
ssh -keygen
cat /root/.ssh/id_rsa.pub >> /root/.ssh/authorized_keys
chmod 700 /root/.ssh/authorized_keys
scp /root/.ssh/id_rsa.pub 192.168.8.76:/root/.ssh/authorized_keys1
scp /root/.ssh/id_rsa.pub 192.168.8.78:/root/.ssh/authorized_keys1
#分别在76，78两台机器上追加写入公钥
cat /root/.ssh/authorized_keys1 >> /root/.ssh/authorized_keys
#修改MySQL的主配置文件my.cnf参数在下边
主要修改basedir以及server_id
创建相应的log-error文件  给600和mysql所属组所有者权限
systemctl restart mysqld
#安装mha node客户端
yum -y install epel-release perl-DBD-MySQL
rpm -ivh mha4mysql-node-0.58-0.el7.centos.noarch.rpm
#创建对应的用户，以及禁止写
mysql> grant all on *.* to mha@"%" identified by "123";
mysql> grant all on *.* to rep@"%" identified by "123";
mysql> set global read_only=1;
mysql> flush privileges;
#做mysqlbinlog和mysql的软连接
ln -s /usr/local/mysql-5.7.26/bin/mysqlbinlog /usr/local/bin/mysqlbinlog
ln -s /usr/local/mysql-5.7.26/bin/mysql /usr/local/bin/mysql

3、slave+mha

做ssh免密分发密钥到其他机器
cd /root/.ssh
ssh -keygen
cat /root/.ssh/id_rsa.pub >> /root/.ssh/authorized_keys
chmod 700 /root/.ssh/authorized_keys
scp /root/.ssh/id_rsa.pub 192.168.8.76:/root/.ssh/authorized_keys1
scp /root/.ssh/id_rsa.pub 192.168.8.77:/root/.ssh/authorized_keys1
#分别在76，77两台机器上追加写入公钥
cat /root/.ssh/authorized_keys1 >> /root/.ssh/authorized_keys
#修改MySQL的主配置文件my.cnf参数在下边
主要修改basedir以及server_id
创建相应的log-error文件  给600和mysql所属组所有者权限
systemctl restart mysqld
#安装mha node客户端
yum -y install epel-release perl-DBD-MySQL
rpm -ivh mha4mysql-node-0.58-0.el7.centos.noarch.rpm
#安装mha manager客户端
yum -y install perl-Config-Tiny perl-Log-Dispatch perl-Parallel-ForkManager
rpm -ivh mha4mysql-manager-0.58-0.el7.centos.noarch.rpm
#编辑写入mha主配置文件，以及IP漂移脚本文件
都在下方代码块中，注意：
    主配置文件：
        server1一般是master机器，server2一般是slave机器，server3一般是mha机器
        需要注意好server块的binlog路径要与mysqld的data路径一致，其他路径保证存在，没有的手动创建
        ssh以及repl的用户名密码要正确
    脚本文件：
        确定好vip的IP要与网卡网段一致，vip的网卡名字也要与网卡名字一致
        给脚本权限：chmod +x /usr/local/bin/master_ip_failover
#安装插件测试脚本
yum -y install dos2unix
dos2unix /etc/mha/script/master_ip_failover
#重启数据库创建并对应的用户，以及禁止写
systemctl restart mysqld
mysql> grant all on *.* to mha@"%" identified by "123";
mysql> grant all on *.* to rep@"%" identified by "123";
mysql> set global read_only=1;
mysql> flush privileges;
#做mysqlbinlog和mysql的软连接
ln -s /usr/local/mysql-5.7.26/bin/mysqlbinlog /usr/local/bin/mysqlbinlog
ln -s /usr/local/mysql-5.7.26/bin/mysql /usr/local/bin/mysql 
#安装nohup,并配置环境变量
yum install coreutils
cat >> /etc/profile <<EOF
:/usr/bin/nohup
EOF
source /etc/profile
#检测并启动
查看通信状态：masterha_check_ssh --conf=/etc/mha/app1.cnf
查看同步状态：masterha_check_repl --conf=/etc/mha/app1.cnf
启动：nohup masterha_manager --conf=/etc/mha/app1.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null > /var/log/mha/app1/manager.log 2>&1 &
查看集群状态：masterha_check_status --conf=/etc/mha/app1.cnf
注意：master宕机切换master之后，原来的master节点配置会从app1.cnf中自动删除，如果想用回原来的节点，需要先把原来的master的节点信息重新加进去

— mysql主配置文件

/etc/my.cnf

[mysqld]
#skip-grant-tables
#default-character-set=utf8
port=3306
user=mysql
basedir=/usr/local/mysql-5.7.24
datadir=/usr/local/mysql-5.7.24/data
socket=/tmp/mysql.sock
server_id=8
innodb_flush_log_at_trx_commit=0
innodb_autoextend_increment=128M
innodb_log_buffer_size=20M
innodb_log_file_size=128M
innodb_buffer_pool_size=2024M
back_log=500
wait_timeout=1800
max_connections=3000
query_cache_size=50M
read_buffer_size=4M
sort_buffer_size=4M
read_rnd_buffer_size=8M
symbolic-links=0
open_files_limit  = 5000
tmp_table_size=56M
binlog_cache_size = 1M
thread_cache_size=64
log-error=/usr/local/mysql-5.7.24/log/mysqld.log
pid-file=/tmp/mysqld.pid
log-bin = mysql-bin
binlog_format=mixed
expire_logs_days=10
character-set-server=utf8
slow_query_log = ON
slow_query_log_file = /usr/local/mysql-5.7.24/log/slow.log
long_query_time = 5
[mysql]
socket=/tmp/mysql.sock
#slave_parallel-type=LOGICAL_CLOCK
#slave_parallel_workers=2
#master_info_repository=TABLE
#relay_log_info_repository=TABLE
#relay_log_recovery=ON

— mha主配置文件

/etc/mha/app1.cnf

[server default]
manager_workdir=/var/log/mha/app1
manager_log=/var/log/mha/app1/manager
master_binlog_dir=/usr/local/mysql-5.7.26/data
user=mha
password=123
#设置监控主库，发送ping包的时间间隔，默认是3秒，尝试三次没有回应的时候自动进行railover
ping_interval=2
ssh_user=root
repl_user=rep
repl_password=123
#指定并设置自动failover时候的切换脚(可省略)
master_ip_failover_script="/etc/mha/script/master_ip_failover"
#设置手动切换时候的切换脚本(可省略)
#master_ip_online_change_script="/etc/mha/script/master_ip_online_change"
#设置发生切换后发送的报警的脚本(可省略)
report_script="/etc/mha/script/send"
#设置故障发生后关闭故障主机脚本（该脚本的主要作用是关闭主机放在发生脑裂,这里没有使用）(可省略)
[server1]
hostname=192.168.8.76
port=3306
[server2]
hostname=192.168.8.77
port=3306
[server3]
hostname=192.168.8.78
port=3306

— IP漂移脚本

/etc/mha/script/master_ip_failover

#!/usr/bin/env perl
#  Copyright (C) 2011 DeNA Co.,Ltd.
#  You should have received a copy of the GNU General Public License
#   along with this program; if not, write to the Free Software
#  Foundation, Inc.,
#  51 Franklin Street, Fifth Floor, Boston, MA  02110-1301  USA
## Note: This is a sample script and is not complete. Modify the script based on your environment.
use strict;
use warnings FATAL => 'all';
use Getopt::Long;
use MHA::DBHelper;
my (
  $command,        $ssh_user,         $orig_master_host,
  $orig_master_ip, $orig_master_port, $new_master_host,
  $new_master_ip,  $new_master_port,  $new_master_user,
  $new_master_password
);
my $vip = '192.168.8.200/24';
my $key = '1';
my $ssh_start_vip = "/sbin/ifconfig ens33:$key $vip";
my $ssh_stop_vip = "/sbin/ifconfig ens33:$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,
  'new_master_user=s'     => \$new_master_user,
  'new_master_password=s' => \$new_master_password,
);
exit &main();
sub main {
    print "\n\nIN SCRIPT TEST====$ssh_stop_vip==$ssh_start_vip===\n\n";
    if ( $command eq "stop" || $command eq "stopssh" ) {
        my $exit_code = 1;
        eval {
            print "Disabling the VIP on old master: $orig_master_host \n";
            &stop_vip();
            $exit_code = 0;
        };
        if ($@) {
            warn "Got Error: $@\n";
            exit $exit_code;
        }
        exit $exit_code;
    }
    elsif ( $command eq "start" ) {
        my $exit_code = 10;
        eval {
            print "Enabling the VIP - $vip on the new master - $new_master_host \n";
            &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 \n";
        exit 0;
    }
    else {
        &usage();
        exit 1;
    }
}
sub start_vip() {
    `ssh $ssh_user\@$new_master_host \" $ssh_start_vip \"`;
}
sub stop_vip() {
     return 0  unless  ($ssh_user);
    `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\n";
}

主从配合mycat读写分离

用于mysql的读写分离，分库分表

数据库遇到瓶颈了，用来分流读写减轻压力

用到的mycat包：https://wwa.lanzouo.com/iBxllxa5ckf
在主从的基础上做的部署环境：
    master：192.168.8.76
    slave：192.168.8.77
    mycat：192.168.8.78
日志在：/mycat/logs/wrapper.log

mycat

#安装配置java的jdk
#解压mycat 配置参数
--配置/mycat/config/schema.xml
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
        #schema 标签定义逻辑库，name 定义逻辑库的库名，datanode 配置该逻辑库默认的分片。
        <schema name="test1" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn2"></schema>
        <schema name="test2" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn3"></schema>
    #dataNode 标签定义真实库，name 定义分片的名字，database 定义真实库
       <dataNode name="dn2" dataHost="localhost1" database="test1" />
       <dataNode name="dn3" dataHost="localhost1" database="test2" />
    <dataHost name="localhost1" maxCon="500" minCon="20" balance="1" writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
        <heartbeat>select user()</heartbeat>
        #user password 配置server中定义的用户名密码
        <writeHost host="master" url="192.168.8.76:3306" user="admin" password="admin">
            <readHost host="slave" url="192.168.8.77:3306" user="admin" password="admin" />
        </writeHost>
    </dataHost>
</mycat:schema>
--这里定义的用户名密码需要提前在主从服务器上都做好相应的用户授权
--配置/mycat/config/server.xml，主要是最后那部分
<user name="admin">        #配置mycat虚拟数据库的用户名
    <property name="password">admin</property>        #配置mycat虚拟数据库的密码
    <property name="schemas">test1,test2</property>        #配置schema中定义的逻辑库
</user>
#测试
mysql -uadmin -padmin -P8066 -h127.0.0.1

mycat + keepalived

实现分离容灾

#在主从 + mycat基础上再加一个mycat，用两台mycat配合keepalived实现容灾
#另一台mycat直接scp 复制做好mycat的文件夹，然后启用
scp -r mycat 192.168.8.79:/data
./data/mycat/bin/mycat start
#启用后跟主主 + keepalived一样两台mycat都装上keepalived
yum -y install keepalived
#keepaliived配置参数和主主的配置一样主要是虚拟IP配置
virtual_ipaddress {
        192.168.8.100 dev ens33        #自定义一个虚拟IP 绑定上真实网卡
    }
#启用keepalived
systemctl start keepalived
#验证
ip addr
等待出现虚拟IP后，mysql测试连接到虚拟IP，然后down掉一台mycat测试即可

1主 2从配合 MGR 实现多主（忽略不常用了解即可）

MySQL Group Replication

为了实现从库也可以读写，减少主从同步的时间

#在 1主 2从的基础上做操作
--所有节点执行
mysql> stop group_replication;
mysql> set global group_replication_single_primary_mode=OFF;
mysql> set global group_replication_enforce_update_everywhere_checks=ON;
--随便选个节点执行
mysql> set global group_replication_bootstrap_group=ON;
mysql> start group_replication;
mysql> set global group_replication_bootstrap_group=OFF;
--其他节点执行
mysql> start group_replication;
--查看组信息，查询到所有节点的 member_role 都为 primary 则成功
mysql> select * from performance_schema.replication_group_members;

常见的错误：

1、sql线程状态为no（1005-1010报错）

MySQL 高可用主从读写分离 - 图2

报错：
    1005 创建表失败
    1006 创建数据库失败
    1007 数据库已存在，创建数据库失败
    1008 数据库已存在，删除数据库失败
    1009 不能删除数据库文件导致删除数据库失败
    1010 不能删除数据目录导致删除数据库失败
解决：
    mysql> stop slave;
    mysql> set global sql_slave_skip_counter=1;
    mysql> start slave;
    mysql> show slave status\G;

2、sql线程状态为no（1062报错）

MySQL 高可用主从读写分离 - 图3

原因：
    主库重启后从库数据不同步，主键冲突
解决：
    mysql> stop slave;    #从库停止同步：
    mysql> flush tables with read lock;    #主库锁表
    mysql> change master to master_host="192.168.0.7",
master_user="slave",
master_password="slave123",
master_port=3306,
master_log_file="ufo.000063",
master_log_pos=159164526;        #去从库从主库的当前的时间点从新同步
    mysql> start slave;    #从库开启同步
    mysql> unlock tables;    #主库解除锁表

3、启动slave报错ERROR 1872 (HY000)

MySQL 高可用主从读写分离 - 图4

原因：
    从库已经存在之前的relay log
解决：
    mysql> stop slave;    #从库停止同步：
    mysql> reset slave;    #清除master信息和relay日志的信息，删除所有的relay日志文件，并开始创建一个全新的中继日志
    mysql> start slave;    #从库开启同步

4、mha启动报错read-only is not set

MySQL 高可用主从读写分离 - 图5

原因：
    relay only没有配置
解决：
    mysql> stop slave;    #从库停止同步：
    mysql> set global read_only=1;    #配置read_only
    mysql> start slave;    #从库开启同步