奥门威尼斯网址:linux晋级之mariadb(三)主从复制

by admin on 2019年9月24日

mysql 的主从错误跳过和mariadb的多源主从复制错误跳过操作不同,请注意:
更改会话的default_master_connection变量

今天我们主要看主从模式下,几种跳过错误的方法,跳过事务,还是跳过event?这个在之前其实我们一直都是忽略的,这在我们维护主从过程中,很容易就导致主从数据更大的不一致。
测试机器5.7.18 主从 gtid 开启
主库数据
奥门威尼斯网址 1
从库数据
奥门威尼斯网址 2
很明显主从数据有一个不一直的地方,从库少了一条(28,2)
的数据。这个时候主库开启以下事务:
奥门威尼斯网址 3
这必然导致从库出现错误,报1032错误,如下所示:
mysql> show slave status\G;
***1. row***
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.56
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000023
Read_Master_Log_Pos: 1928
Relay_Log_File: hadoop2-relay-bin.000012
Relay_Log_Pos: 1595
Relay_Master_Log_File: mysql-bin.000023
Slave_IO_Running: Yes
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 1032
Last_Error: Could not execute Delete_rows event on table
yhtest1.yhtest; Can’t find record in ‘yhtest’, Error_code: 1032;
handler error HA_ERR_KEY_NOT_FOUND; the event’s master log
mysql-bin.000023, end_log_pos 1812
Skip_Counter: 0
Exec_Master_奥门威尼斯网址,Log_Pos: 1502
Relay_Log_Space: 2384
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 1032
Last_SQL_Error: Could not execute Delete_rows event on table
yhtest1.yhtest; Can’t find record in ‘yhtest’, Error_code: 1032;
handler error HA_ERR_KEY_NOT_FOUND; the event’s master log
mysql-bin.000023, end_log_pos 1812
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: ab8c3ec3-b588-11e7-a769-000c29c57be6
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State:
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp: 171130 23:55:18
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: ab8c3ec3-b588-11e7-a769-000c29c57be6:96-101
Executed_Gtid_Set: ab8c3ec3-b588-11e7-a769-000c29c57be6:1:77-100,
b6ddfda0-d8bc-4272-a58f-4ea75acbbc79:1-22:1000012-1000013:2000012-2000013,
d24c1c76-b4ef-11e7-969a-000c29a75f68:1-17
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.01 sec)

跳过错误有两种方式:
1.跳过指定数量的事务:
mysql>slave stop;
mysql>SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1       
#跳过一个事务
mysql>slave start

当mysql/mariadb跨越互联网进行复制时别人可以窃取到mysql/mariadb的复制信息,
这些信息是明文的, 因此存在不安全性, 这里通过ssl对复制的信息进行加密

一、主机要求

1、最少两台互通的服务器
2、设置防火墙规则
3、setenforce 0

STOP SLAVE 'slave_account';
SET @@default_master_connection = 'slave_account';
SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
START SLAVE 'slave_account';
SET @@default_master_connection = '';

解决方法:
方法1
5.7下,由于开启了GTID ,不能通过参数sql_slave_skip_counter=N
跳过错误,但是我们可以通过在从库执行空事物的方法,跳过该错误,但要注意,这样跳过的是一个事物。
从以上报错信息中,我们很容易看出目前执行位置在:
ab8c3ec3-b588-11e7-a769-000c29c57be6:1:77-100 也就是报错位置在:
ab8c3ec3-b588-11e7-a769-000c29c57be6:1:77-101
操作如下:
奥门威尼斯网址 4
这个时候,我们再次show slave status\G
看到主从已经恢复正常,但是我们再对比数据,发现我们刚才主库的三个event
在同一个事件中,被我们全部跳过了,也就是两个插入数据也没有在从库执行!
主库数据:
奥门威尼斯网址 5
从库数据:
奥门威尼斯网址 6

2.修改mysql的配置文件,通过slave_skip_errors参数来跳所有错误或指定类型的错误
vi /etc/my.cnf
[mysqld]
#slave-skip-errors=1062,1053,1146 #跳过指定error no类型的错误
#slave-skip-errors=all #跳过所有错误

  1. 创建证书中心

二、环境要求及原理图

1、两台服务器均安装mariadb-server
2、master IP:192.168.192.105
slave IP:192.168.192.106

奥门威尼斯网址 7

方法2
如果我们觉得以上方法步骤比较多,还可以借住与pt-slave-restart
来进行主从错误跳过,跳过方法如下:
奥门威尼斯网址 8
这个同样可以达到跳过主从错误的目的!但是其跳过的单位也是事务,同样也会导致主库所做的两个插入没有在从库执行!
方法3
利用slave_exec_mode参数来处理主从中遇到的错误,操作步骤如下:

在主服务器上创建证书中心

三、步骤

主节点配置:

[root@test1 mysql]# vim /etc/my.cnf.d/server.cnf 
[mysqld]
server-id = 1 # 设置唯一ID
log-bin = maste-log # 设置日志文件名称
skip_name_resolve = ON # 跳过域名解析
sync_binlog = 1 # 同步二进制日志文件
innodb-file-per-table = ON # 创建的表均为单一表
[root@test1 ~]# mysql -uroot -pwang
MariaDB [(none)]> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| maste-log.000003 |      563 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
# 记录file文件名字和position位置编号,后面配置从服务器时需要使用。
# 授权同步的账号
MariaDB [(none)]> GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO  'wang'@'192.168.192.106' IDENTIFIED BY 'wang';
Query OK, 0 rows affected (0.01 sec)

MariaDB [(none)]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.02 sec)

从节点配置:

[root@test1 mysql]# vim /etc/my.cnf.d/server.cnf 
[mysqld]
server-id = 2 # 设置唯一ID不能与其他ID冲突
skip_name_resolve = ON
innodb-file-per-table = ON 
relay-log = relay-log
read_only = ON
[root@test2 ~]# mysql -uroot -pwang
MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='192.168.192.105',MASTER_USER='wang',MASTER_PASSWORD='wang',MASTER_LOG_FILE='maste-log.000003',MASTER_LOG_POS=563;
MariaDB [(none)]> START SLAVE;
MariaDB [(none)]> SHOW SLAVE STATUS;
MariaDB [(none)]>  SHOW SLAVE STATUS\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.192.105
                  Master_User: wang
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: maste-log.000003
          Read_Master_Log_Pos: 812
               Relay_Log_File: relay-log.000002
                Relay_Log_Pos: 861
        Relay_Master_Log_File: maste-log.000003
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 812
              Relay_Log_Space: 1149
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1
1 row in set (0.00 sec)

测试:
在主服务器上操作

MariaDB [(none)]>CREATE DATABASE WANG;
然后再插入数据,这里就不再插入了。

再从服务器上操作

MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| WANG               |
| mysql              |
| performance_schema |
| test               |
+--------------------+

OK 同步成功。
下面介绍互为主从的两台mariadb服务器,这里我们还是使用上述的两台服务器;
不过是需要修改配置文件。

[root@test1 mysql]# vim /etc/my.cnf.d/server.cnf

[mysqld]
auto_increment_offset=1 # 关键设置自增长ID的初始值为1
auto_increment_increment=2 # 设置自增长ID的每次增大的数值位2
skip_name_resolve = ON
innodb-file-per-table = ON 
relay-log = relay-log
read_only = ON
log-bin = maste-log
sync_binlog = 1 

[root@test2 mysql]# vim /etc/my.cnf.d/server.cnf

[mysqld]
auto_increment_offset=2 # 关键设置自增长ID的初始值为2
auto_increment_increment=2 # 设置自增长ID的每次增大的数值位2
skip_name_resolve = ON
innodb-file-per-table = ON 
relay-log = relay-log
read_only = ON
log-bin = maste-log
sync_binlog = 1 

按照原来的配置上的话我们已经在test1节点上配置了授权用户,在test2节点上认证了属组。这里我们需要将操作反过来在test1节点上认证属组,在test2节点上配置授权用户。
在test2上执行MariaDB

MariaDB [(none)]> GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO  'wang'@'192.168.192.106' IDENTIFIED BY 'wang';
Query OK, 0 rows affected (0.01 sec)

MariaDB [(none)]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.02 sec)

在test1上执行

MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='192.168.192.105',MASTER_USER='wang',MASTER_PASSWORD='wang',MASTER_LOG_FILE='maste-log.000003',MASTER_LOG_POS=563;
MariaDB [(none)]> START SLAVE;
MariaDB [(none)]> SHOW SLAVE STATUS;
MariaDB [(none)]>  SHOW SLAVE STATUS\G;

这里当执行完最后的命令的时候会出现和上面从节点执行的相同命令的界面就OK了。不过这里还是建议实测一下。test1和test2互相创建库文件查看是否在另外一个节点上出现对应的库文件就OK了。

stop slave;
set global slave_exec_mode=’IDEMPOTENT’; 幂等模式
(默认是STRICT严格模式)
start slave;
查看主从数据
奥门威尼斯网址 9
发现一致了,也就是说在调整参数slave_exec_mode后,我们跳过的delete
错误,但是两个插入操作任然是执行的,同时,查看错误日志,我们发现记录如下:
奥门威尼斯网址 10
方法4
利用参数slave_skip_errors跳过错误,操作方法如下,在配置文件中添加slave_skip_errors=1062,1032
等,重启数据库,启动slave ,也会发现测试结果和方法3一致。但是
slave_skip_errors 不支持动态修改!
方法5
从库1032时,我们直接去主库找到对应的记录,插入到从库当中,restart
salve, 1062 时 我们直接在从库中备份删除掉冲突记录即可。

cd /etc/pki/CA
生成私钥
(umask 077;openssl genrsa -out private/cakey.pem 2048)

生成自签名证书
openssl req -new -x509 -key /etc/pki/CA/private/cakey.pem -out /etc/pki/CA/cacert.pem -days 7300

创建证书编号
mkdir certs crl newcerts
touch index.txt
echo 00 > serial

这里稍微提一下:mariadb 的Gtid
错误,我们可以采用参数sql_slave_skip_counter=N直接跳过,但是,却不支持使用pt-slave-restart
跳过错误,而且mariadb 的Gtid
和官方不通用,实现原理相同,实现方法缺不同,所以我们也不能用这两个版本之间搭建gtid主从。

2.  为主服务器创建证书

服务器的名称必须固定, 在申请证书时要输入服务器名称,
这书和服务器名称对应;

创建私钥
mkdir /usr/lcoal/mysql/ssl
cd /usr/local/mysql/ssl
(umask 077;openssl genrsa -out /etc/mysql/ssl/master.key 2048)
生成证书申请
openssl req -new -key master.key -out master.csr
在证书服务器上对master的证书进行签发
openssl ca -in master.csr -out master.crt -days 365

3. 创建从服务器证书

(umask 077;openssl genrsa -out /etc/mysql/ssl/slave.key 2048)
openssl req -new -key slave.key -out slave.csr
将从服务器的证书申请文件复制到证书服务器上进行签发
openssl ca -in slave.csr -out slave.crt -days 365
  1. 修改证书权限和mysql配置文件

    将证书的公钥cacert.pem复制到主从服务器的目录下
    cd /etc/mysql/ssl
    cp /etc/pki/CA/cacert.pem ./
    chown -R mysql.mysql master.crt master.key cacert.pem
    chmod 600 master.crt master.key cacert.pem

    vim /etc/my.cnf

    log-bin=master-log
    server-id=1
    skip_name_resolve = ON
    innodb_file_per_table = ON
    ssl
    ssl_ca = /etc/mysql/ssl/cacert.pem
    ssl_cert = /etc/mysql/ssl/master.crt
    ssl_key = /etc/mysql/ssl/master.key

    修改从服务器配置
    cd /etc/mysql/ssl
    cp /etc/pki/CA/cacert.pem ./
    chown -R mysql.mysql slave.crt slave.key cacert.pem
    chmod 600 slave.crt slave.key cacert.pem

    vim /etc/my.cnf

    relay-log=relay-log
    server-id=2
    skip_name_resolve = ON
    innodb_file_per_table = ON
    ssl
    ssl_ca = /etc/mysql/ssl/cacert.pem
    ssl_cert = /etc/mysql/ssl/slave.crt
    ssl_key = /etc/mysql/ssl/slave.key

  2. 在主服务上创建复制用户

    MariaDB [(none)]> GTANT REPLICATION SLAVE,REPLICATION CLIENT ON . TO ‘repluser’@’10.1.52.%’ IDENTIFIED BY ‘replpass’ REQUIRE SSL;
    MariaDB [(none)]> FLUSH PRIVILEGES;
    查看主服务器当前二进制位置
    MariaDB [(none)]> SHOW MASTER STATUS;
    +——————-+———-+————–+——————+
    | File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
    +——————-+———-+————–+——————+
    | master-log.000005 | 7918 | | |
    +——————-+———-+————–+——————+
    1 row in set (0.00 sec)

6. 在从服务器上开始复制    

MariaDB [(none)]> CHANGE MASTER TO
    -> MASTER_HOST='10.1.52.11',
    -> MASTER_USER='repluser',
    -> MASTER_PASSWORD='replpass',
    -> MASTER_LOG_FILE='master-log.000001',
    -> MASTER_LOG_POS=495,
    -> MASTER_SSL=1,
    -> MASTER_SSL_CA='/etc/mysql/ssl/cacert.pem',
    -> MASTER_SSL_CERT='/etc/mysql/ssl/slave.crt',
    -> MASTER_SSL_KEY='/etc/mysql/ssl/slave.key';
MariaDB [(none)]> START SLAVE;

7. 查看从服务器的状态

MariaDB [(none)]> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.1.52.11
                  Master_User: repluser
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master-log.000005
          Read_Master_Log_Pos: 7918
               Relay_Log_File: relay-log.000002
                Relay_Log_Pos: 7940
        Relay_Master_Log_File: master-log.000005
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 7918
              Relay_Log_Space: 8228
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: Yes
           Master_SSL_CA_File: /etc/mysql/ssl/cacert.pem
           Master_SSL_CA_Path:
              Master_SSL_Cert: /etc/mysql/ssl/slave.crt
            Master_SSL_Cipher:
               Master_SSL_Key: /etc/mysql/ssl/slave.key
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 1
1 row in set (0.00 sec)

发表评论

电子邮件地址不会被公开。 必填项已用*标注

网站地图xml地图