Happywzy

非GTID模式下

当BIN-LOG里面出现错误导致主从不能同步的时候,

-- 错误状态的部分截图
mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.41.141
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
              Replicate_Do_DB:  
                   Last_Errno: 1032
                   Last_Error: Could not execute Update_rows event on table test.testttt; Can't find record in 'testttt', Error_code: 1032; handler error HA_ERR_END_OF_FILE; the event's master log mysql-bin.000004, end_log_pos 404
                 Skip_Counter: 0
               Last_SQL_Errno: 1032
               Last_SQL_Error: Could not execute Update_rows event on table test.testttt; Can't find record in 'testttt', Error_code: 1032; handler error HA_ERR_END_OF_FILE; the event's master log mysql-bin.000004, end_log_pos 404
  Replicate_Ignore_Server_Ids: 
           Retrieved_Gtid_Set: 69a5d660-cfd9-11ea-8174-00505626c27e:1-5
            Executed_Gtid_Set: 69a5d660-cfd9-11ea-8174-00505626c27e:4,
b3802ae3-cfd8-11ea-85e8-00505630c9f5:1-2
1 row in set (0.00 sec)

可以用下面方法掠过该错误语句行,继续同步:

stop slave;
set global sql_slave_skip_counter=1;
start slave;

当set global sql_slave_skip_counter=1;是可能会出现一下错误:不支持GTID_MODE模式运行的数据库

ERROR 1858 (HY000): sql_slave_skip_counter can not be set when the server is running with GTID_MODE = ON. Instead, for each transaction that you want to skip, generate an empty transaction with the same GTID as the transaction

GTID模式下

1. 找到错误点

show slave status\G
-- 找到这一行
Executed_Gtid_Set: 69a5d660-cfd9-11ea-8174-00505626c27e:1-5

2. 重置master和slave

reset master;
stop slave;
reset slave;

3. 设置跳过错误点

-- 1-5 可能出错,ID加1,1-6
set global gtid_purged='69a5d660-cfd9-11ea-8174-00505626c27e:1-6';

set global gtid_purged='xxxxx';要求GTID_EXECUTED为空,所以需要第二部重置操作.

4. 重启复制

change master to master_host='192.168.41.141',master_port=3306,master_user='slave',master_password='123456',master_auto_position=1;
start slave;

5. 查看

-- 确认Slave_IO_Running,Slave_SQL_Running,Last_Error,Last_SQL_Error等是否有错
-- 如果还有错但和上面的错误ID不一样,需要重复1~4
show slave status\G

双主模式更麻烦,需要两边都要做1~4操作,确认没有错误.

说明

前文仅仅通过mysql双主 + keepalived实现高可用，这种方式在一台出现问题的时候可以切换，但是正常情况下只有一台服务使用，无法实现负载均衡，本文引入haproxy实现正常情况下也能有负载均衡的效果.

配置

1. 安装haproxy

yum install haproxy

2. 修改haproxy配置文件
   默认目录：/etc/haproxy/haproxy.cfg

#---------------------------------------------------------------------
# Example configuration for a possible web application.  See the
# full configuration options online.
#
#   http://haproxy.1wt.eu/download/1.4/doc/configuration.txt
#
#---------------------------------------------------------------------

#---------------------------------------------------------------------
# Global settings
#---------------------------------------------------------------------
global
    # to have these messages end up in /var/log/haproxy.log you will
    # need to:
    #
    # 1) configure syslog to accept network log events.  This is done
    #    by adding the '-r' option to the SYSLOGD_OPTIONS in
    #    /etc/sysconfig/syslog
    #
    # 2) configure local2 events to go to the /var/log/haproxy.log
    #   file. A line like the following can be added to
    #   /etc/sysconfig/syslog
    #
    #    local2.*                       /var/log/haproxy.log
    #
    log         127.0.0.1 local2

    chroot      /var/lib/haproxy
    pidfile     /var/run/haproxy.pid
    maxconn     4000
    user        haproxy
    group       haproxy
    daemon

    # turn on stats unix socket
    stats socket /var/lib/haproxy/stats

#---------------------------------------------------------------------
# common defaults that all the 'listen' and 'backend' sections will
# use if not designated in their block
#---------------------------------------------------------------------
defaults
    mode                    http
    log                     global
    option                  httplog
    option                  dontlognull
    option http-server-close
    option forwardfor       except 127.0.0.0/8
    option                  redispatch
    retries                 3
    timeout http-request    10s
    timeout queue           1m
    timeout connect         10s
    timeout client          1m
    timeout server          1m
    timeout http-keep-alive 10s
    timeout check           10s
    maxconn                 3000


######## 监控界面配置 #################
listen admin_status
        bind 0.0.0.0:8888
        mode http
        stats refresh 30s
        stats uri /
        stats realm welcome login\ Haproxy
        stats auth admin:123456
        stats hide-version
        stats admin if TRUE
        ########frontend配置##############

######## mysql负载均衡配置 ###############
listen mysql
        bind 0.0.0.0:13306
        mode tcp
        balance roundrobin                  # 负载均衡策略
        #option tcplog                      # tcp日志
        option mysql-check user haproxy     # 在mysql中创建无任何权限用户haproxy，且无密码
        server mysql_1 192.168.41.141:3306 check weight 1 maxconn 2000
        server mysql_2 192.168.41.142:3306 check weight 1 maxconn 2000
        option tcpka                        # 缺少这个配置,可能无法查看到数据库

option mysql-check user haproxy这里是配置健康检查的，需要在mysql中创建无任何权限用户haproxy，且无密码.

• CREATE USER 'haproxy'@'%' IDENTIFIED BY '';

3. 修改ulimit配置
   haproxy要求ulimit大于（maxconn*2 + 15 ）

#临时修改
ulimit -n 65536

#永久修改，需要修改/etc/security/limits.conf配置文件，文末增加以下内容，然后重新登录就可以生效
* soft nofile 65536
* hard nofile 65536
* soft nproc 65565
* hard nproc 65565

4. 启动服务并配置自启动

启动后访问8888端口，使用admin/123456登录就可以看到UI界面了。

systemctl start haproxy
systemctl enable haproxy

5. 修改keepalived配置
   前文配置的keepalived监控的是mysqld进程，需要修改成监控haproxy进程.这样不管哪台机器的mysql或者haproxy或者keepalived出现问题，都不影响使用.

# 修改的配置如下
vrrp_script chk_mysql {
    script "killall -0 haproxy"
    interval 2
    timeout 2
    fall 3
}

6. 测试
   通过VIP连接数据库，增删改查测试.

说明

mysql双主模式下，在任意一台mysql上写数据都会同步到另一台上，本章通过keepalived VIP实现如果一台服务挂了会自动切换到另一台mysql上.

配置

# 安装keepalived,centos默认安装了
yum install keepalived -y

修改双主机/etc/keepalived/keepalived.conf配置.

global_defs {
   router_id mysql
}

vrrp_script chk_mysql {
    script "killall -0 mysqld"      // 检测mysql进程是否存活
    interval 2
    timeout 2
    fall 3
}

vrrp_instance mysql {
    state MASTER
    interface ens33
    virtual_router_id 51
    priority 100                // 另一台权重可以设置90，防止争抢VIP
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    virtual_ipaddress {
        192.168.41.140          // 设置虚拟ip
    }
    track_script {
        chk_mysql
    }
}

测试

# 通过命令可以看到VIP已经在一台机器上了，也可以通过虚拟IP连接上mysql
[root@mysql01 mysql]# ip addr sh ens33
2: ens33: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000
    link/ether 00:50:56:26:c2:7e brd ff:ff:ff:ff:ff:ff
    inet 192.168.41.141/24 brd 192.168.41.255 scope global ens33
       valid_lft forever preferred_lft forever
    inet 192.168.41.140/32 scope global ens33
       valid_lft forever preferred_lft forever
    inet6 fe80::250:56ff:fe26:c27e/64 scope link 
       valid_lft forever preferred_lft forever

# 关掉mysql服务,命令可能根据你安装方式有区别
service mysql stop

# 再次在两台机器上执行命令，可以看到VIP转移了,可以继续通过VIP连接mysql
ip addr sh ens33

前提准备

原理

MySQL中有一种日志叫做 bin日志（二进制日志），这个日志会记录下所有修改了数据库的SQL语句。主从复制的原理其实就是从服务器向主服务器请求这个日志文件，主服务器会把这个 bin日志复制到从服务器上执行一遍，这样从服务器上的数据就和主服务器上的数据相同了。

1. 主服务器必须启用二进制日志（log-bin），记录任何修改了数据库数据的事件；
2. 从服务器开启一个线程（I/O Thread)把自己扮演成 MySQL 的客户端，通过 MySQL 协议，请求主服务器的二进制日志文件中的事件；
3. 主服务器启动一个线程（Dump Thread），检查自己二进制日志中的事件，跟对方请求的位置对比，如果不带请求位置参数，则主服务器就会从第一个日志文件中的第一个事件一个一个发送给从服务器；
4. 从服务器接收到主服务器发送过来的数据把它放置到中继日志（relay log）文件中。并记录该次请求到主服务器的具体哪一个二进制日志文件内部的哪一个位置（主服务器中的二进制文件会有多个，其名结尾以6位数递增）；
5. 从服务器启动另外一个线程（SQL Thread），把 relay log 中的事件读取出来，并在本地再执行一次。

双主架构思路

• 两台mysql都可读写，互为主备
• 两台主库之间做高可用,可以采用haproxy、keepalived等方案

双主配置

主要配置文件/etc/my.cnf

1. 修改配置

vim /etc/my.cnf

# 添加以下配置
# id 保证唯一
server-id=1
# 开启binlog 日志
log-bin=mysql-bin
# 自增ID保证不冲突, 一主1,3,5 另一主2,4,6
auto-increment-increment=2
auto-increment-offset=1
# 将复制事件写入binlog,一台服务器既做主库又做从库此选项必须要开启
log-slave-updates=true
# 忽略同步的数据库
replicate-ignore-db=information_schema
replicate-ignore-db=performance_schema
replicate-ignore-db=mysql
replicate-ignore-db=sys
# binlog文件大小限制
max_binlog_size=1024M
# binlog格式
binlog-format=ROW

另一台配置

server-id=2
log-bin=mysql-bin
auto-increment-increment=2
auto-increment-offset=2
log-slave-updates=true
replicate-ignore-db=information_schema
replicate-ignore-db=performance_schema
replicate-ignore-db=mysql
replicate-ignore-db=sys
max_binlog_size=1024M
binlog-format=ROW
#binlog_rows_query_log_events=on # 在row 模式的binlog中包含SQL EVENTS（即SQL语句也会保留）
#master-info-repository=TABLE
#relay-log-info-repository=TABLE
#slave-skip-errors=all # 忽略复制产生的错误
#slave-skip-errors=1062,1032,1060 # 跳过已知错误,主键冲突、表已存在等错误代码如1062,1032,1060等
#relay-log-purge = 1    #是否自动清空不再需要中继日志时。默认值为1(启用)
#expire_logs_days = 30  #超过 30 天的 binlog 删除

slave_skip_errors参数说明:

slave_skip_errors选项有四个可用值，分别为：off，all，ErorCode，ddl_exist_errors, 默认情况下该参数值是off.
mysql5.6及MySQL Cluster NDB 7.3以及后续版本增加了参数ddl_exist_errors，该参数包含一系列error code（1007,1008,1050,1051,1054,1060,1061,1068,1094,1146）
一些error code代表的错误如下：
    1007：数据库已存在，创建数据库失败
    1008：数据库不存在，删除数据库失败
    1050：数据表已存在，创建数据表失败
    1051：数据表不存在，删除数据表失败
    1054：字段不存在，或程序文件跟数据库有冲突
    1060：字段重复，导致无法插入
    1061：重复键名
    1068：定义了多个主键
    1094：位置线程ID
    1146：数据表缺失，请恢复数据库
    1053：复制过程中主服务器宕机
    1062：主键冲突 Duplicate entry '%s' for key %d

2. 重启mysql

# 根据安装方式选择
service mysql restart

3. 添加主从同步账户

-- mysql01
grant replication slave on *.* to 'slave'@'192.168.41.142' identified by '123456';
flush privileges;

-- mysql02
grant replication slave on *.* to 'slave'@'192.168.41.141' identified by '123456';
flush privileges;

4. 查看主库状态

-- 分别在mysql01,mysql02查看, 这里由于都是新安装的，status完全一样，实际可能不一样
-- 记住file的名字和position的值，因为主从同步，从数据库是通过读取主数据库的日志文件来完成同步的，所以需要文件名字和日志的当前位置
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      604 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

5. 配置同步信息

-- mysql01
change master to master_host='192.168.41.142',master_port=3306,master_user='slave',master_password='123456',master_log_file='mysql-bin.000001',master_log_pos=604;

start slave;

-- mysql02
change master to master_host='192.168.41.141',master_port=3306,master_user='slave',master_password='123456',master_log_file='mysql-bin.000001',master_log_pos=604;

start slave;

6. 查看同步状态

-- 分别在mysql01,mysql02查看同步状态
mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.41.141
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 604
               Relay_Log_File: mysql02-relay-bin.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes      -- 状态正常
            Slave_SQL_Running: Yes      -- 状态正常

7. 测试

-- 随便在 mysql01 或 mysql02 上创建库、表或者数据都能同步到另一台机器上
create database test;

CREATE TABLE `t_type` (
                            `type_id` INT(2) NOT NULL AUTO_INCREMENT COMMENT '资源类型id',
                            `type_name` VARCHAR(32) NOT NULL COMMENT '资源类型名称',
                            `type_remark` VARCHAR(255) DEFAULT NULL COMMENT '备注',
                            `create_time` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
                            `update_time` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
                            PRIMARY KEY (`type_id`),
                            UNIQUE KEY (type_name)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

insert into t_type(type_name, type_remark) VALUES ('菜单资源','用于确定页面菜单资源，分多级菜单');
insert into t_type(type_name, type_remark) VALUES ('按钮资源','用于确定页面按钮资源');
insert into t_type(type_name, type_remark) VALUES ('路由资源','用于确定网关路由资源');

8. 开启MySQL5.6之后引入的GTID复制功能

vim /etc/my.cnf
# 增加以下配置,开启gtid
gtid-mode=on
enforce-gtid-consistency=on
# 修改完my.cnf需要重启mysql才能生效

-- mysql01,mysql02分别执行以下命令
mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)

mysql> change master to MASTER_AUTO_POSITION=1;
Query OK, 0 rows affected (0.01 sec)

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

GTID又叫全局事务ID（Global Transaction ID），是一个已提交事务的编号，并且是一个全局唯一的编号。MySQL5.6版本之后在主从复制类型上新增了GTID复制。

• GTID！MySQL复制中的核武器

双主配置之前数据备份

使用mysqldump命令,MySQL操作mysqldump命令详解

-- 导出主数据库的数据
mysqldump -uroot -p --opt --all-databases >/all_databases.sql

-- 导入数据
mysql -u root -p  < /all_databases.sql

-- 或者使用source导入
-- mysql> source /all_databases.sql;

部署说明

Linux上安装软件常见的几种方式：

• 源码编译
• 压缩包解压（一般为tar.gz）
• 编译好的安装包（RPM、DPKG等）
• 在线安装（YUM、APT等）

部署方式简单程度：YUM > RPM > tar.gz > 源码，本文介绍RPM方式.

部署步骤

1. 删除旧包

# 查找
rpm -qa | grep -i mysql
# 删除
rpm -ev mysql-libs-* --nodeps

2. 下载RPM包mysql-5.7.31-1.el7.x86_64.rpm-bundle.tar

   由于国外资源下载缓慢，可以使用华为开源镜像

3. 解压压缩包

[root@mysql03 ~]# tar -xvf mysql-5.7.31-1.el7.x86_64.rpm-bundle.tar 
mysql-community-embedded-devel-5.7.31-1.el7.x86_64.rpm
mysql-community-libs-5.7.31-1.el7.x86_64.rpm
mysql-community-client-5.7.31-1.el7.x86_64.rpm
mysql-community-server-5.7.31-1.el7.x86_64.rpm
mysql-community-embedded-5.7.31-1.el7.x86_64.rpm
mysql-community-embedded-compat-5.7.31-1.el7.x86_64.rpm
mysql-community-common-5.7.31-1.el7.x86_64.rpm
mysql-community-libs-compat-5.7.31-1.el7.x86_64.rpm
mysql-community-devel-5.7.31-1.el7.x86_64.rpm
mysql-community-test-5.7.31-1.el7.x86_64.rpm

4. 安装
   只需要安装以下四个

mysql-community-libs-5.7.31-1.el7.x86_64.rpm
mysql-community-client-5.7.31-1.el7.x86_64.rpm
mysql-community-server-5.7.31-1.el7.x86_64.rpm
mysql-community-common-5.7.31-1.el7.x86_64.rpm

四个包有依赖关系。安装有先后顺序要求。

shell> rpm -ivh mysql-community-common-5.7.31-1.el7.x86_64.rpm
shell> rpm -ivh mysql-community-libs-5.7.31-1.el7.x86_64.rpm
shell> rpm -ivh mysql-community-client-5.7.31-1.el7.x86_64.rpm
shell> rpm -ivh mysql-community-server-5.7.31-1.el7.x86_64.rpm

更简单的安装命令

# 这样，包之间会自动处理依赖和先后顺序
yum install mysql-community-{server,client,common,libs}-*

注意上面命令要求在解压后的目录里执行，不然yum找不到包就会到公网下载，速度非常慢.

5. 启动服务

service mysqld start
service mysqld stop
service mysqld status

6. 登录修改默认密码

# 获取root用户临时密码
cat /var/log/mysqld.log | more

# mysqld.log 临时密码
2020-07-28T01:23:53.588166Z 1 [Note] A temporary password is generated for root@localhost: dDO>*a4!uA+o

# 登录, 使用初始化生成的临时密码登录
mysql -u root -p

# 修改密码
set password for root@localhost = password('123456');

# 开放远程连接
use mysql;
update user set user.Host='%' where user.User='root';
flush privileges;

修改密码时会报错ERROR 1819 (HY000): Your password does not satisfy the current policy requirements,意思是密码强度不符合要求.
可以修改/etc/my.cnf, 添加validate_password=OFF, 关闭密码强度校验, 然后重启service mysqld restart.

mysql安装RPM方式比tar.gz方式简单不少.

参考链接

• CentOS安装MySQL详解
• linux MySQL5.7 rpm安装

部署说明

Linux上安装软件常见的几种方式：

• 源码编译
• 压缩包解压（一般为tar.gz）
• 编译好的安装包（RPM、DPKG等）
• 在线安装（YUM、APT等）

部署方式简单程度：YUM > RPM > tar.gz > 源码，本文介绍tar.gz方式.

部署步骤

1. 卸载已有

# 通过rpm方式安装的
rpm -qa|grep mysql
# 如果有, 卸载
rpm -e --nodeps xxx

# 查找mysql文件夹
whereis mysql
find / -name mysql

2. 下载tar.gz文件, mysql-5.7.31-linux-glibc2.12-x86_64.tar.gz

   由于国外资源下载缓慢，可以使用华为开源镜像

3. 安装依赖包

yum -y install make gcc-c++ cmake bison-devel ncurses-devel numactl libaio

4. 创建系统用户和组

# 创建组
groupadd mysql
# 创建不需要登录的系统账号，启动MySQL服务时会使用该账号
useradd -s /sbin/nologin -g mysql -M mysql
# useradd -r -g mysql -s /bin/false mysql
# useradd -r -g mysql mysql

5. 解压到/usr/local/

# 解压
tar -zxvf mysql-5.7.31-linux-glibc2.12-x86_64.tar.gz

# 重命名
mv mysql-5.7.31-linux-glibc2.12-x86_64 mysql

# 移动到/usr/local/下
mv mysql /usr/local/

# mysql目录下创建data文件夹
cd /usr/local/mysql/
mkdir data

# 设置权限
chown -R mysql:mysql /usr/local/mysql
chmod -R 755 /usr/local/mysql

6. 初始化

# 注意打印日志最后返回的root用户临时密码，登录时用到
bin/mysqld --initialize --user=mysql --datadir=/usr/local/mysql/data --basedir=/usr/local/mysql

# 初始化日志
2020-07-27T07:13:42.627407Z 1 [Note] A temporary password is generated for root@localhost: 4Mt4Zka<6j)&

1. 编辑配置文件/etc/my.cnf

# 如果有这个文件，先清空
[root@localhost bin]#  vi /etc/my.cnf

[mysqld]
datadir=/usr/local/mysql/data
port=3306
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
symbolic-links=0
max_connections=600
innodb_file_per_table=1
lower_case_table_names=1

8. 启动

/usr/local/mysql/support-files/mysql.server start

9. 添加软链接

ln -s /usr/local/mysql/support-files/mysql.server /etc/init.d/mysql 
ln -s /usr/local/mysql/bin/mysql /usr/bin/mysql
# 重启mysql: service mysqld start|stop|restart|status
service mysql restart

# 添加环境变量,可选
echo "PATH=$PATH:/usr/local/mysql/bin  " >> /etc/profile  
source /etc/profile

10. 设置开机启动

# 将服务文件拷贝到init.d下，并重命名为mysql
cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
# 赋予可执行权限
chmod +x /etc/init.d/mysqld
# 添加服务
chkconfig --add mysqld
# 显示服务列表
chkconfig --list

11. 登录并修改root密码

-- 使用初始化生成的临时密码登录
mysql -u root -p

-- 修改密码
set password for root@localhost = password('123456');
--  ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';

-- 开放远程连接
use mysql;
update user set user.Host='%' where user.User='root';
flush privileges;

参考链接

• Linux下安装mysql-5.7.24
• CentOS安装MySQL详解