MySQL/MariaDB数据库的主从级联复制

　　　　 　　MySQL/MariaDB数据库的主从级联复制

　　　　　　　　　　　　　　　　　　　　　　　作者：尹正杰 

版权声明：原创作品，谢绝转载！否则将追究法律责任。

 

 

一.主从复制类型概述

1>.主从复制

　　博主推荐阅读：
　　　　https://www.cnblogs.com/yinzhengjie/p/11816066.html

2>.一主多从

　　其实就是重复主从复制的步骤即可。一个master节点多个slave节点，此时多个slave节点同时同步master节点。

3>.级联复制

　　在一主多从的架构上稍作改进，依旧是一个master节点，指定一个slave节点从master节点拉取数据，其它slave节点从该slave节点拉取数据的架构。

 

二.级联复制案例实战

1>.试验架构说明

node102.yinzhengjie.org.cn(172.30.1.102):
　　master节点

node103.yinzhengjie.org.cn(172.30.1.103):
　　slave节点，级联节点,该节点直接通过master节点复制数据

node104.yinzhengjie.org.cn(172.30.1.104):
　　slave节点，该节点并不直接通过从master节点复制数据，而是通过级联节点复制数据

2>.master节点配置文件("/etc/my.cnf")

[root@node102.yinzhengjie.org.cn ~]# cat /etc/my.cnf
[mysqld]
binlog_format          = row                              #推荐使用基于行数据的复制
log_bin                = /data/logbin/mysql-bin     　　   #需要启用二进制日志,建议和数据文件分开放存放
server-id              = 102         　　　　　　　　　　     #为当前节点设置一个全局惟一的ID号,用于标识当前MySQL实例
log-basename           = master   　　　　　　　　　　　　　　 #可选项，设置datadir中日志名称，确保不依赖主机名
character-set-server   = utf8mb4
default_storage_engine = InnoDB
datadir                = /var/lib/mysql
socket                 = /var/lib/mysql/mysql.sock

[mysqld_safe]
log-error              = /var/log/mariadb/mariadb.log
pid-file               = /var/run/mariadb/mariadb.pid

!includedir /etc/my.cnf.d

[root@node102.yinzhengjie.org.cn ~]#

3>.备份master节点数据并拷贝备份数据级联节点(模拟对生产环境再用的数据库进行级联复制)

[root@node102.yinzhengjie.org.cn ~]# mysqldump -uroot -pyinzhengjie -A -F --single-transaction --master-data=1 > /root/all_bak.sql
[root@node102.yinzhengjie.org.cn ~]# 
[root@node102.yinzhengjie.org.cn ~]# ll -h
total 20M
-rw-r--r-- 1 root root 20M Nov  8 14:26 all_bak.sql
[root@node102.yinzhengjie.org.cn ~]# 
[root@node102.yinzhengjie.org.cn ~]# scp all_bak.sql node103.yinzhengjie.org.cn:~
root@node103.yinzhengjie.org.cn's password: 
all_bak.sql                                                                            100%   20MB  91.4MB/s   00:00    
[root@node102.yinzhengjie.org.cn ~]#
[root@node102.yinzhengjie.org.cn ~]# scp all_bak.sql node103.yinzhengjie.org.cn:~

4>.master节点(node101.yinzhengjie.org.cn)创建有复制权限的用户账号

[root@node102.yinzhengjie.org.cn ~]# mysql -uroot -pyinzhengjie
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 6
Server version: 5.5.64-MariaDB MariaDB Server


Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.


Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.


MariaDB [(none)]> 
MariaDB [(none)]> SELECT user,host,password FROM mysql.user;
+------+-----------+-------------------------------------------+
| user | host | password |
+------+-----------+-------------------------------------------+
| root | localhost | *BD0B1F48FDC55BD27555FC2F22FF29A68A25A1D7 |
| root | 127.0.0.1 | *BD0B1F48FDC55BD27555FC2F22FF29A68A25A1D7 |
| root | ::1 | *BD0B1F48FDC55BD27555FC2F22FF29A68A25A1D7 |
+------+-----------+-------------------------------------------+
rows in set (0.00 sec)


MariaDB [(none)]> 
MariaDB [(none)]> GRANT REPLICATION SLAVE ON *.* TO 'copy'@'172.30.1.10%' IDENTIFIED BY 'yinzhengjie';
Query OK, 0 rows affected (0.00 sec)


MariaDB [(none)]> 
MariaDB [(none)]> SELECT user,host,password FROM mysql.user;
+------+--------------+-------------------------------------------+
| user | host | password |
+------+--------------+-------------------------------------------+
| root | localhost | *BD0B1F48FDC55BD27555FC2F22FF29A68A25A1D7 |
| root | 127.0.0.1 | *BD0B1F48FDC55BD27555FC2F22FF29A68A25A1D7 |
| root | ::1 | *BD0B1F48FDC55BD27555FC2F22FF29A68A25A1D7 |
| copy | 172.30.1.10% | *BD0B1F48FDC55BD27555FC2F22FF29A68A25A1D7 |
+------+--------------+-------------------------------------------+
rows in set (0.00 sec)


MariaDB [(none)]>
MariaDB [(none)]> GRANT REPLICATION SLAVE ON *.* TO 'copy'@'172.30.1.10%' IDENTIFIED BY 'yinzhengjie';

5>.级联节点（node103.yinzhengjie.org.cn）的配置文件(my.cnf)

[root@node103.yinzhengjie.org.cn ~]# cat /etc/my.cnf
[mysqld]
server_id          = 103     　　　　　　　 　　　　　　　#为当前节点设置一个全局惟的ID号
read_only          = ON      　　　　　　　　 　　　　　　#设置数据库只读
binlog_format      = row　　　　　　　　　　　　　　　　　#注意这个二进制日志格式要和master配置一致哟,否则会同步失败。
relay_log          = relay-log  　　　　　　　　 　　　　#relay log的文件路径，默认值hostname-relay-bin
relay_log_index    = relay-log.index  　　　　  　　　　#默认值hostname-relay-bin.index
log_bin            = /data/mysql/logbin/mysql-bin    #由于其它slave节点需要从该节点拷贝数据，因此咱们也得开启二进制日志功能，建议二进制日志和数据文件分开存放
log_slave_updates　　　　　　　　　　　　　　　　　　　　　　#启用级联复制功能
datadir            = /var/lib/mysql
socket             = /var/lib/mysql/mysql.sock


[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid
[root@node103.yinzhengjie.org.cn ~]#

6>.配置级联节点与master节点的主从复制

[root@node103.yinzhengjie.org.cn ~]# ll
total 20192
-rw-r--r-- 1 root root 20674386 Nov  8 14:28 all_bak.sql
[root@node103.yinzhengjie.org.cn ~]# 
[root@node103.yinzhengjie.org.cn ~]# head -25 all_bak.sql | tail 
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Position to start replication or point-in-time recovery from
--

CHANGE MASTER TO MASTER_LOG_FILE='master-bin.000003', MASTER_LOG_POS=245;

--
-- Current Database: `devops`
[root@node103.yinzhengjie.org.cn ~]# 
[root@node103.yinzhengjie.org.cn ~]# vim all_bak.sql 　　　#需要传过来的备份文件稍作修改，主要是配置使用有复制权限的用户账号连接至主服务器　
[root@node103.yinzhengjie.org.cn ~]# 
[root@node103.yinzhengjie.org.cn ~]# head -30 all_bak.sql | tail

CHANGE MASTER TO 
    MASTER_HOST='172.30.1.102', 
    MASTER_USER='copy', 
    MASTER_PASSWORD='yinzhengjie', 
    MASTER_PORT=3306,
    MASTER_LOG_FILE='master-bin.000003', 
    MASTER_LOG_POS=245,
    MASTER_CONNECT_RETRY=10;

[root@node103.yinzhengjie.org.cn ~]# 
[root@node103.yinzhengjie.org.cn ~]#
[root@node103.yinzhengjie.org.cn ~]# vim all_bak.sql 　    #修改备份文件主要是配置使用有复制权限的用户账号连接至master
[root@node103.yinzhengjie.org.cn ~]# mkdir -pv /data/mysql/logbin
mkdir: created directory ‘/data’
mkdir: created directory ‘/data/mysql’
mkdir: created directory ‘/data/mysql/logbin’
[root@node103.yinzhengjie.org.cn ~]# 
[root@node103.yinzhengjie.org.cn ~]# ll /data/mysql/logbin/ -d
drwxr-xr-x 2 root root 6 Nov  8 14:50 /data/mysql/logbin/
[root@node103.yinzhengjie.org.cn ~]# 
[root@node103.yinzhengjie.org.cn ~]# 
[root@node103.yinzhengjie.org.cn ~]# chown -R mysql:mysql /data/mysql/logbin/ 
[root@node103.yinzhengjie.org.cn ~]# 
[root@node103.yinzhengjie.org.cn ~]# ll /data/mysql/logbin/ -d
drwxr-xr-x 2 mysql mysql 6 Nov  8 14:50 /data/mysql/logbin/
[root@node103.yinzhengjie.org.cn ~]# 
[root@node103.yinzhengjie.org.cn ~]# ll /data/mysql/logbin/ 
total 0
[root@node103.yinzhengjie.org.cn ~]# 
[root@node103.yinzhengjie.org.cn ~]# ll /var/lib/mysql/
total 0
[root@node103.yinzhengjie.org.cn ~]# 
[root@node103.yinzhengjie.org.cn ~]# 
[root@node103.yinzhengjie.org.cn ~]# systemctl start mariadb　　　　　　#启动mariadb数据库
[root@node103.yinzhengjie.org.cn ~]# 
[root@node103.yinzhengjie.org.cn ~]# 
[root@node103.yinzhengjie.org.cn ~]# ll /var/lib/mysql/
total 37852
-rw-rw---- 1 mysql mysql    16384 Nov  8 14:52 aria_log.00000001
-rw-rw---- 1 mysql mysql       52 Nov  8 14:52 aria_log_control
-rw-rw---- 1 mysql mysql 18874368 Nov  8 14:52 ibdata1
-rw-rw---- 1 mysql mysql  5242880 Nov  8 14:52 ib_logfile0
-rw-rw---- 1 mysql mysql  5242880 Nov  8 14:52 ib_logfile1
drwx------ 2 mysql mysql     4096 Nov  8 14:52 mysql
srwxrwxrwx 1 mysql mysql        0 Nov  8 14:52 mysql.sock
drwx------ 2 mysql mysql     4096 Nov  8 14:52 performance_schema
drwx------ 2 mysql mysql        6 Nov  8 14:52 test
[root@node103.yinzhengjie.org.cn ~]# 
[root@node103.yinzhengjie.org.cn ~]# ll /data/mysql/logbin/ 
total 1056
-rw-rw---- 1 mysql mysql   30394 Nov  8 14:52 mysql-bin.000001
-rw-rw---- 1 mysql mysql 1038814 Nov  8 14:52 mysql-bin.000002
-rw-rw---- 1 mysql mysql     245 Nov  8 14:52 mysql-bin.000003
-rw-rw---- 1 mysql mysql     108 Nov  8 14:52 mysql-bin.index
[root@node103.yinzhengjie.org.cn ~]# 
[root@node103.yinzhengjie.org.cn ~]#
[root@node103.yinzhengjie.org.cn ~]# systemctl start mariadb　　　　　　#启动mariadb数据库
[root@node103.yinzhengjie.org.cn ~]# mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 5.5.64-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> 
MariaDB [(none)]> 
MariaDB [(none)]> SHOW VARIABLES LIKE '%sql_log_bin%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sql_log_bin   | ON    |
+---------------+-------+
1 row in set (0.00 sec)

MariaDB [(none)]> 
MariaDB [(none)]> SET sql_log_bin = OFF;　　　　　　　　#在导入master数据之前，应该临时关闭二进制日志功能
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> 
MariaDB [(none)]> SHOW VARIABLES LIKE '%sql_log_bin%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sql_log_bin   | OFF   |
+---------------+-------+
1 row in set (0.00 sec)

MariaDB [(none)]> 
MariaDB [(none)]>
MariaDB [(none)]> SET sql_log_bin = OFF;　　　　　　　　　　　　#在导入master数据之前，应该临时关闭二进制日志功能
MariaDB [(none)]> SYSTEM ls -l /var/lib/mysql/
total 28700
-rw-rw---- 1 mysql mysql    16384 Nov  8 14:52 aria_log.00000001
-rw-rw---- 1 mysql mysql       52 Nov  8 14:52 aria_log_control
-rw-rw---- 1 mysql mysql 18874368 Nov  8 14:52 ibdata1
-rw-rw---- 1 mysql mysql  5242880 Nov  8 14:52 ib_logfile0
-rw-rw---- 1 mysql mysql  5242880 Nov  8 14:52 ib_logfile1
drwx------ 2 mysql mysql     4096 Nov  8 14:52 mysql
srwxrwxrwx 1 mysql mysql        0 Nov  8 14:52 mysql.sock
drwx------ 2 mysql mysql     4096 Nov  8 14:52 performance_schema
drwx------ 2 mysql mysql        6 Nov  8 14:52 test
MariaDB [(none)]> 
MariaDB [(none)]> SOURCE /root/all_bak.sql　　　　　　#将咱们修改后的master备份数据导入进来
......

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

MariaDB [yinzhengjie2019]> 
MariaDB [yinzhengjie2019]> SYSTEM ls -l /var/lib/mysql/
total 78828
-rw-rw---- 1 mysql mysql    16384 Nov  8 14:52 aria_log.00000001
-rw-rw---- 1 mysql mysql       52 Nov  8 14:52 aria_log_control
drwx------ 2 mysql mysql       60 Nov  8 14:57 devops
-rw-rw---- 1 mysql mysql 69206016 Nov  8 14:58 ibdata1
-rw-rw---- 1 mysql mysql  5242880 Nov  8 14:58 ib_logfile0
-rw-rw---- 1 mysql mysql  5242880 Nov  8 14:57 ib_logfile1
-rw-rw---- 1 mysql mysql       84 Nov  8 14:57 master.info
drwx------ 2 mysql mysql     4096 Nov  8 14:57 mysql
srwxrwxrwx 1 mysql mysql        0 Nov  8 14:52 mysql.sock
drwx------ 2 mysql mysql     4096 Nov  8 14:52 performance_schema
-rw-rw---- 1 mysql mysql      245 Nov  8 14:57 relay-log.000001
-rw-rw---- 1 mysql mysql       19 Nov  8 14:57 relay-log.index
-rw-rw---- 1 mysql mysql       43 Nov  8 14:57 relay-log.info
drwx------ 2 mysql mysql        6 Nov  8 14:52 test
drwx------ 2 mysql mysql       39 Nov  8 14:57 yinzhengjie2019
MariaDB [yinzhengjie2019]> 
MariaDB [yinzhengjie2019]>
MariaDB [(none)]> SOURCE /root/all_bak.sql　　　　　　　　　　  #将咱们修改后的master备份数据导入进来
MariaDB [yinzhengjie2019]> SHOW VARIABLES LIKE '%sql_log_bin%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sql_log_bin   | OFF   |
+---------------+-------+
1 row in set (0.00 sec)

MariaDB [yinzhengjie2019]> 
MariaDB [yinzhengjie2019]> SET sql_log_bin = ON;　　　　　　　　#导入成功后别忘记开启二进制日志功能哟
Query OK, 0 rows affected (0.00 sec)

MariaDB [yinzhengjie2019]> 
MariaDB [yinzhengjie2019]> SHOW VARIABLES LIKE '%sql_log_bin%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sql_log_bin   | ON    |
+---------------+-------+
1 row in set (0.00 sec)

MariaDB [yinzhengjie2019]>
MariaDB [yinzhengjie2019]> SET sql_log_bin = ON;　　　　　　　 #导入成功后别忘记开启二进制日志功能哟
MariaDB [yinzhengjie2019]> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
               Slave_IO_State: 
                  Master_Host: 172.30.1.102
                  Master_User: copy
                  Master_Port: 3306
                Connect_Retry: 10
              Master_Log_File: master-bin.000003
          Read_Master_Log_Pos: 245
               Relay_Log_File: relay-log.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: master-bin.000003
             Slave_IO_Running: No
            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: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 245
              Relay_Log_Space: 245
              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: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 0
1 row in set (0.00 sec)

MariaDB [yinzhengjie2019]> 
MariaDB [yinzhengjie2019]> 
MariaDB [yinzhengjie2019]> 
MariaDB [yinzhengjie2019]> START SLAVE;　　　　　　#别忘记手动开启复制线程哟
Query OK, 0 rows affected (0.00 sec)

MariaDB [yinzhengjie2019]> 
MariaDB [yinzhengjie2019]> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.30.1.102
                  Master_User: copy
                  Master_Port: 3306
                Connect_Retry: 10
              Master_Log_File: master-bin.000003
          Read_Master_Log_Pos: 245
               Relay_Log_File: relay-log.000002
                Relay_Log_Pos: 530
        Relay_Master_Log_File: master-bin.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: 245
              Relay_Log_Space: 818
              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: 102
1 row in set (0.00 sec)

MariaDB [yinzhengjie2019]>
MariaDB [yinzhengjie2019]> START SLAVE;　　　　　　　　　　　　#别忘记手动开启复制线程哟
[root@node102.yinzhengjie.org.cn ~]# mysql -uroot -pyinzhengjie
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 15
Server version: 5.5.64-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> 
MariaDB [(none)]> SELECT COUNT(*) FROM yinzhengjie2019.testlog;
+----------+
| COUNT(*) |
+----------+
|   599994 |
+----------+
1 row in set (0.14 sec)

MariaDB [(none)]> 
MariaDB [(none)]> USE yinzhengjie2019
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [yinzhengjie2019]> 
MariaDB [yinzhengjie2019]> CALL pro_testlog;
Query OK, 1 row affected (57.94 sec)

MariaDB [yinzhengjie2019]> 
MariaDB [yinzhengjie2019]> SELECT COUNT(*) FROM yinzhengjie2019.testlog;
+----------+
| COUNT(*) |
+----------+
|   699993 |
+----------+
1 row in set (0.16 sec)

MariaDB [yinzhengjie2019]> 
MariaDB [yinzhengjie2019]>  SHOW PROCESSLIST\G
*************************** 1. row ***************************
      Id: 15
    User: root
    Host: localhost
      db: yinzhengjie2019
 Command: Query
    Time: 0
   State: NULL
    Info: SHOW PROCESSLIST
Progress: 0.000
*************************** 2. row ***************************
      Id: 17
    User: copy
    Host: node103.yinzhengjie.org.cn:45496
      db: NULL
 Command: Binlog Dump
    Time: 546
   State: Master has sent all binlog to slave; waiting for binlog to be updated
    Info: NULL
Progress: 0.000
2 rows in set (0.00 sec)

MariaDB [yinzhengjie2019]> 
MariaDB [yinzhengjie2019]>
主库写入测试数据
[root@node103.yinzhengjie.org.cn ~]# mysql -uroot -pyinzhengjie
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 7
Server version: 5.5.64-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> SELECT COUNT(*) FROM yinzhengjie2019.testlog;
+----------+
| COUNT(*) |
+----------+
|   699993 |
+----------+
1 row in set (0.31 sec)

MariaDB [(none)]> 
MariaDB [(none)]> SHOW PROCESSLIST\G
*************************** 1. row ***************************
      Id: 1
    User: system user
    Host: 
      db: NULL
 Command: Connect
    Time: 537
   State: Waiting for master to send event
    Info: NULL
Progress: 0.000
*************************** 2. row ***************************
      Id: 2
    User: system user
    Host: 
      db: NULL
 Command: Connect
    Time: 837
   State: Slave has read all relay log; waiting for the slave I/O thread to update it
    Info: NULL
Progress: 0.000
*************************** 3. row ***************************
      Id: 7
    User: root
    Host: localhost
      db: NULL
 Command: Query
    Time: 0
   State: NULL
    Info: SHOW PROCESSLIST
Progress: 0.000
3 rows in set (0.00 sec)

MariaDB [(none)]>
从库数据和主库数据相同

7>.备份级联节点(node103.yinzhengjie.org.cn)的数据并发送到slave节点中(node104.yinzhengjie.org.cn)

[root@node103.yinzhengjie.org.cn ~]# mysqldump -uroot -pyinzhengjie -A -F --single-transaction --master-data=1 > /root/all_bak103.sql
[root@node103.yinzhengjie.org.cn ~]# 
[root@node103.yinzhengjie.org.cn ~]# ll -h
total 23M
-rw-r--r-- 1 root root 23M Nov  8 15:31 all_bak103.sql
[root@node103.yinzhengjie.org.cn ~]# 
[root@node103.yinzhengjie.org.cn ~]# scp all_bak103.sql node104.yinzhengjie.org.cn:~
The authenticity of host 'node104.yinzhengjie.org.cn (172.30.1.104)' can't be established.
ECDSA key fingerprint is SHA256:F3IVf82keybIystuO6PYRfwr0o5dTftrmAHJWzqO4IA.
ECDSA key fingerprint is MD5:02:5d:d8:0a:4a:b4:70:0f:61:be:2c:97:56:db:24:e7.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added 'node104.yinzhengjie.org.cn,172.30.1.104' (ECDSA) to the list of known hosts.
root@node104.yinzhengjie.org.cn's password: 
all_bak103.sql                                                                         100%   23MB  93.5MB/s   00:00    
[root@node103.yinzhengjie.org.cn ~]# 
[root@node103.yinzhengjie.org.cn ~]#

8>.slave节点的配置文件

[root@node104.yinzhengjie.org.cn ~]# cat /etc/my.cnf
[mysqld]
server_id          = 104     　　　　　　　 　　　#为当前节点设置一个全局惟的ID号
read_only          = ON      　　　　　　　　 　　#设置数据库只读
relay_log          = relay-log  　　　　　　　　 #relay log的文件路径，默认值hostname-relay-bin
relay_log_index    = relay-log.index  　　　　  #默认值hostname-relay-bin.index
datadir            = /var/lib/mysql
socket             = /var/lib/mysql/mysql.sock


[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid
[root@node104.yinzhengjie.org.cn ~]#

8>.配置slave节点与级联节点主从复制

[root@node104.yinzhengjie.org.cn ~]# ll -h
total 23M
-rw-r--r-- 1 root root 23M Nov  8 15:33 all_bak103.sql
[root@node104.yinzhengjie.org.cn ~]# 
[root@node104.yinzhengjie.org.cn ~]# head -25 all_bak103.sql | tail 
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Position to start replication or point-in-time recovery from
--

CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000007', MASTER_LOG_POS=245;

--
-- Current Database: `devops`
[root@node104.yinzhengjie.org.cn ~]# 
[root@node104.yinzhengjie.org.cn ~]# vim all_bak103.sql 　　　　#需要传过来的备份文件稍作修改，主要是配置使用有复制权限的用户账号连接至级联服务器　
[root@node104.yinzhengjie.org.cn ~]# 
[root@node104.yinzhengjie.org.cn ~]# head -30 all_bak103.sql | tail

CHANGE MASTER TO 
    MASTER_HOST='172.30.1.103', 　　　　　　#注意此处主机应该指定的是级联节点的IP
    MASTER_USER='copy', 
    MASTER_PASSWORD='yinzhengjie', 
    MASTER_PORT=3306,
    MASTER_LOG_FILE='mysql-bin.000007', 
    MASTER_LOG_POS=245,
    MASTER_CONNECT_RETRY=10;

[root@node104.yinzhengjie.org.cn ~]#
[root@node104.yinzhengjie.org.cn ~]# vim all_bak103.sql 　　　　#修改配置使用有复制权限的用户账号连接至级联服务器
[root@node104.yinzhengjie.org.cn ~]# ll /var/lib/mysql/
total 0
[root@node104.yinzhengjie.org.cn ~]# 
[root@node104.yinzhengjie.org.cn ~]# systemctl start mariadb    　　#启动mariadb数据库
[root@node104.yinzhengjie.org.cn ~]# 
[root@node104.yinzhengjie.org.cn ~]# ll /var/lib/mysql/
total 37852
-rw-rw---- 1 mysql mysql    16384 Nov  8 15:45 aria_log.00000001
-rw-rw---- 1 mysql mysql       52 Nov  8 15:45 aria_log_control
-rw-rw---- 1 mysql mysql 18874368 Nov  8 15:45 ibdata1
-rw-rw---- 1 mysql mysql  5242880 Nov  8 15:45 ib_logfile0
-rw-rw---- 1 mysql mysql  5242880 Nov  8 15:45 ib_logfile1
drwx------ 2 mysql mysql     4096 Nov  8 15:45 mysql
srwxrwxrwx 1 mysql mysql        0 Nov  8 15:45 mysql.sock
drwx------ 2 mysql mysql     4096 Nov  8 15:45 performance_schema
drwx------ 2 mysql mysql        6 Nov  8 15:45 test
[root@node104.yinzhengjie.org.cn ~]#
[root@node104.yinzhengjie.org.cn ~]# systemctl start mariadb 　　#启动mariadb数据库
[root@node104.yinzhengjie.org.cn ~]# ll /var/lib/mysql/
total 37852
-rw-rw---- 1 mysql mysql    16384 Nov  8 15:45 aria_log.00000001
-rw-rw---- 1 mysql mysql       52 Nov  8 15:45 aria_log_control
-rw-rw---- 1 mysql mysql 18874368 Nov  8 15:45 ibdata1
-rw-rw---- 1 mysql mysql  5242880 Nov  8 15:45 ib_logfile0
-rw-rw---- 1 mysql mysql  5242880 Nov  8 15:45 ib_logfile1
drwx------ 2 mysql mysql     4096 Nov  8 15:45 mysql
srwxrwxrwx 1 mysql mysql        0 Nov  8 15:45 mysql.sock
drwx------ 2 mysql mysql     4096 Nov  8 15:45 performance_schema
drwx------ 2 mysql mysql        6 Nov  8 15:45 test
[root@node104.yinzhengjie.org.cn ~]# 
[root@node104.yinzhengjie.org.cn ~]# ll -h
total 23M
-rw-r--r-- 1 root root 23M Nov  8 15:36 all_bak103.sql
[root@node104.yinzhengjie.org.cn ~]# 
[root@node104.yinzhengjie.org.cn ~]# mysql < all_bak103.sql 　　　　　　#将级联节点备份数据导入进来当前实例中
[root@node104.yinzhengjie.org.cn ~]# 
[root@node104.yinzhengjie.org.cn ~]# ll /var/lib/mysql/
total 136172
-rw-rw---- 1 mysql mysql    16384 Nov  8 15:45 aria_log.00000001
-rw-rw---- 1 mysql mysql       52 Nov  8 15:45 aria_log_control
drwx------ 2 mysql mysql       60 Nov  8 15:46 devops
-rw-rw---- 1 mysql mysql 77594624 Nov  8 15:46 ibdata1
-rw-rw---- 1 mysql mysql  5242880 Nov  8 15:46 ib_logfile0
-rw-rw---- 1 mysql mysql  5242880 Nov  8 15:46 ib_logfile1
-rw-rw---- 1 mysql mysql       84 Nov  8 15:46 master.info
drwx------ 2 mysql mysql     4096 Nov  8 15:46 mysql
srwxrwxrwx 1 mysql mysql        0 Nov  8 15:45 mysql.sock
drwx------ 2 mysql mysql     4096 Nov  8 15:45 performance_schema
-rw-rw---- 1 mysql mysql      245 Nov  8 15:46 relay-log.000001
-rw-rw---- 1 mysql mysql       19 Nov  8 15:46 relay-log.index
-rw-rw---- 1 mysql mysql       43 Nov  8 15:46 relay-log.info
drwx------ 2 mysql mysql        6 Nov  8 15:45 test
drwx------ 2 mysql mysql       39 Nov  8 15:46 yinzhengjie2019
[root@node104.yinzhengjie.org.cn ~]# 
[root@node104.yinzhengjie.org.cn ~]#
[root@node104.yinzhengjie.org.cn ~]# mysql < all_bak103.sql　　　　#将级联节点备份数据导入进来当前实例中
[root@node104.yinzhengjie.org.cn ~]# mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 3
Server version: 5.5.64-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> 
MariaDB [(none)]> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
               Slave_IO_State: 
                  Master_Host: 172.30.1.103
                  Master_User: copy
                  Master_Port: 3306
                Connect_Retry: 10
              Master_Log_File: mysql-bin.000007
          Read_Master_Log_Pos: 245
               Relay_Log_File: relay-log.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: mysql-bin.000007
             Slave_IO_Running: No
            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: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 245
              Relay_Log_Space: 245
              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: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 0
1 row in set (0.00 sec)

MariaDB [(none)]> 
MariaDB [(none)]> START SLAVE;    　　　　　　　　　　#别忘记手动开启复制线程哟
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> 
MariaDB [(none)]> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.30.1.103
                  Master_User: copy
                  Master_Port: 3306
                Connect_Retry: 10
              Master_Log_File: mysql-bin.000007
          Read_Master_Log_Pos: 245
               Relay_Log_File: relay-log.000002
                Relay_Log_Pos: 529
        Relay_Master_Log_File: mysql-bin.000007
             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: 245
              Relay_Log_Space: 817
              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: 103
1 row in set (0.00 sec)

MariaDB [(none)]>
MariaDB [(none)]> START SLAVE; 　　　　　　　　　　#别忘记手动开启复制线程哟

9>.验证级联复制是否生效

[root@node102.yinzhengjie.org.cn ~]# mysql -uroot -pyinzhengjie
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 19
Server version: 5.5.64-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> USE yinzhengjie2019;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [yinzhengjie2019]> 
MariaDB [yinzhengjie2019]> SHOW TABLES;
+---------------------------+
| Tables_in_yinzhengjie2019 |
+---------------------------+
| testlog                   |
+---------------------------+
1 row in set (0.00 sec)

MariaDB [yinzhengjie2019]> 
MariaDB [yinzhengjie2019]> CREATE TABLE students(id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,name VARCHAR(30) NOT NULL,sex
 ENUM('boy','girl') DEFAULT 'boy',age TINYINT UNSIGNED,mobile CHAR(11),address VARCHAR(50));Query OK, 0 rows affected (0.00 sec)

MariaDB [yinzhengjie2019]> INSERT INTO students (name,age,mobile,address) VALUES ('Jason Yin',18,10000,'beijing'),('Jay',
'40',10086,'Taiwan');Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

MariaDB [yinzhengjie2019]> INSERT INTO students SET name='yinzhengjie',age=27,address='shanxi';
Query OK, 1 row affected (0.01 sec)

MariaDB [yinzhengjie2019]> INSERT students (age,sex,name,mobile,address) VALUES (28,'girl','Gloria Tang Tsz-Kei',null,'Ho
ng Kong');Query OK, 1 row affected (0.00 sec)

MariaDB [yinzhengjie2019]> 
MariaDB [yinzhengjie2019]> SELECT * FROM students;
+----+---------------------+------+------+--------+-----------+
| id | name                | sex  | age  | mobile | address   |
+----+---------------------+------+------+--------+-----------+
|  1 | Jason Yin           | boy  |   18 | 10000  | beijing   |
|  2 | Jay                 | boy  |   40 | 10086  | Taiwan    |
|  3 | yinzhengjie         | boy  |   27 | NULL   | shanxi    |
|  4 | Gloria Tang Tsz-Kei | girl |   28 | NULL   | Hong Kong |
+----+---------------------+------+------+--------+-----------+
4 rows in set (0.00 sec)

MariaDB [yinzhengjie2019]> QUIT
Bye
[root@node102.yinzhengjie.org.cn ~]# 
[root@node102.yinzhengjie.org.cn ~]#
master节点(node102.yinzhengjie.org.cn)写入测试数据
[root@node103.yinzhengjie.org.cn ~]# mysql -uroot -pyinzhengjie
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 13
Server version: 5.5.64-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> 
MariaDB [(none)]> SELECT * FROM yinzhengjie2019.students;
+----+---------------------+------+------+--------+-----------+
| id | name                | sex  | age  | mobile | address   |
+----+---------------------+------+------+--------+-----------+
|  1 | Jason Yin           | boy  |   18 | 10000  | beijing   |
|  2 | Jay                 | boy  |   40 | 10086  | Taiwan    |
|  3 | yinzhengjie         | boy  |   27 | NULL   | shanxi    |
|  4 | Gloria Tang Tsz-Kei | girl |   28 | NULL   | Hong Kong |
+----+---------------------+------+------+--------+-----------+
4 rows in set (0.00 sec)

MariaDB [(none)]> QUIT
Bye
[root@node103.yinzhengjie.org.cn ~]# 
[root@node103.yinzhengjie.org.cn ~]#
级联节点(node103.yinzhengjie.org.cn)验证数据同步成功
[root@node104.yinzhengjie.org.cn ~]# mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 6
Server version: 5.5.64-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> SELECT * FROM yinzhengjie2019.students;
+----+---------------------+------+------+--------+-----------+
| id | name                | sex  | age  | mobile | address   |
+----+---------------------+------+------+--------+-----------+
|  1 | Jason Yin           | boy  |   18 | 10000  | beijing   |
|  2 | Jay                 | boy  |   40 | 10086  | Taiwan    |
|  3 | yinzhengjie         | boy  |   27 | NULL   | shanxi    |
|  4 | Gloria Tang Tsz-Kei | girl |   28 | NULL   | Hong Kong |
+----+---------------------+------+------+--------+-----------+
4 rows in set (0.00 sec)

MariaDB [(none)]> QUIT
Bye
[root@node104.yinzhengjie.org.cn ~]# 
[root@node104.yinzhengjie.org.cn ~]#
slave节点(node104.yinzhengjie.org.cn)验证数据同步成功

 

三.复制架构中应该注意的问题

1>.限制从服务器为只读

在从服务器上设置read_only=ON
　　注意：此限制对拥有SUPER权限的用户均无效

阻止所有用户, 包括主服务器复制的更新
　　mysql> FLUSH TABLES WITH READ LOCK;
[root@node102.yinzhengjie.org.cn ~]# mysql -uroot -pyinzhengjie
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 21
Server version: 5.5.64-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> 
MariaDB [(none)]> SELECT @@read_only;　　　　　　#Mysql节点不要开启只读属性
+-------------+
| @@read_only |
+-------------+
|           0 |
+-------------+
row in set (0.00 sec)

MariaDB [(none)]>
MariaDB [mysql]> SELECT user,host,password FROM mysql.user;
+------+--------------+-------------------------------------------+
| user | host         | password                                  |
+------+--------------+-------------------------------------------+
| root | localhost    | *BD0B1F48FDC55BD27555FC2F22FF29A68A25A1D7 |
| root | 127.0.0.1    | *BD0B1F48FDC55BD27555FC2F22FF29A68A25A1D7 |
| root | ::1          | *BD0B1F48FDC55BD27555FC2F22FF29A68A25A1D7 |
| copy | 172.30.1.10% | *BD0B1F48FDC55BD27555FC2F22FF29A68A25A1D7 |
+------+--------------+-------------------------------------------+
rows in set (0.00 sec)

MariaDB [mysql]> 
MariaDB [mysql]> GRANT SELECT,UPDATE,DELETE,INSERT ON devops.* TO test@'node104.yinzhengjie.org.cn' IDENTIFIED BY 'yinzhengjie';
Query OK, 0 rows affected (0.00 sec)

MariaDB [mysql]> 
MariaDB [mysql]> SELECT user,host,password FROM mysql.user;
+------+----------------------------+-------------------------------------------+
| user | host                       | password                                  |
+------+----------------------------+-------------------------------------------+
| root | localhost                  | *BD0B1F48FDC55BD27555FC2F22FF29A68A25A1D7 |
| root | 127.0.0.1                  | *BD0B1F48FDC55BD27555FC2F22FF29A68A25A1D7 |
| root | ::1                        | *BD0B1F48FDC55BD27555FC2F22FF29A68A25A1D7 |
| copy | 172.30.1.10%               | *BD0B1F48FDC55BD27555FC2F22FF29A68A25A1D7 |
| test | node104.yinzhengjie.org.cn | *BD0B1F48FDC55BD27555FC2F22FF29A68A25A1D7 |
+------+----------------------------+-------------------------------------------+
rows in set (0.00 sec)

MariaDB [mysql]> QUIT
Bye
[root@node102.yinzhengjie.org.cn ~]#
master节点为slave节点(node104.yinzhengjie.org.cn)创建测试用户
[root@node104.yinzhengjie.org.cn ~]# mysql 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 10
Server version: 5.5.64-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> 
MariaDB [(none)]> SELECT user,host,password FROM mysql.user;
+------+----------------------------+-------------------------------------------+
| user | host                       | password                                  |
+------+----------------------------+-------------------------------------------+
| root | localhost                  | *BD0B1F48FDC55BD27555FC2F22FF29A68A25A1D7 |
| root | 127.0.0.1                  | *BD0B1F48FDC55BD27555FC2F22FF29A68A25A1D7 |
| root | ::1                        | *BD0B1F48FDC55BD27555FC2F22FF29A68A25A1D7 |
| copy | 172.30.1.10%               | *BD0B1F48FDC55BD27555FC2F22FF29A68A25A1D7 |
| test | node104.yinzhengjie.org.cn | *BD0B1F48FDC55BD27555FC2F22FF29A68A25A1D7 |
+------+----------------------------+-------------------------------------------+
5 rows in set (0.00 sec)

MariaDB [(none)]> QUIT
Bye
[root@node104.yinzhengjie.org.cn ~]# 
[root@node104.yinzhengjie.org.cn ~]# mysql -utest -pyinzhengjie -h node104.yinzhengjie.org.cn　　　　#连接当前节点
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 7
Server version: 5.5.64-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> 
MariaDB [(none)]> SELECT @@READ_ONLY;　　　　　　#当前节点为只读节点
+-------------+
| @@READ_ONLY |
+-------------+
|           1 |
+-------------+
1 row in set (0.00 sec)

MariaDB [(none)]> 
MariaDB [devops]> SELECT USER();
+---------------------------------+
| USER()                          |
+---------------------------------+
| test@node104.yinzhengjie.org.cn |
+---------------------------------+
1 row in set (0.00 sec)

MariaDB [devops]> 
MariaDB [(none)]> USE devops;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [devops]> SHOW TABLES;
+------------------+
| Tables_in_devops |
+------------------+
| employee         |
| students         |
+------------------+
2 rows in set (0.00 sec)

MariaDB [devops]> 
MariaDB [devops]> DELETE FROM students;　　　　　　　　#虽说我们当前用户有insert,delete,select,update权限，但当前数据库是只读的。因此会抛出如下异常。
ERROR 1290 (HY000): The MariaDB server is running with the --read-only option so it cannot execute this statement
MariaDB [devops]> 
MariaDB [devops]>
限制从服务器为只读对普通用户有效
[root@node103.yinzhengjie.org.cn ~]# mysql -uroot -pyinzhengjie
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 15
Server version: 5.5.64-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> 
MariaDB [(none)]> USE yinzhengjie2019
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [yinzhengjie2019]> SHOW TABLES;
+---------------------------+
| Tables_in_yinzhengjie2019 |
+---------------------------+
| students                  |
| testlog                   |
+---------------------------+
2 rows in set (0.00 sec)

MariaDB [yinzhengjie2019]> 
MariaDB [yinzhengjie2019]> SELECT @@read_only;
+-------------+
| @@read_only |
+-------------+
|           1 |
+-------------+
1 row in set (0.00 sec)

MariaDB [yinzhengjie2019]> 
MariaDB [yinzhengjie2019]> DELETE FROM students;　　　　　　#虽然当前数据库实例是只读的，单对于超级用户root来说，说删就直接删了，read_only参数对超级用户无效!
Query OK, 4 rows affected (0.01 sec)

MariaDB [yinzhengjie2019]> 
MariaDB [yinzhengjie2019]> SELECT USER();
+----------------+
| USER()         |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)

MariaDB [yinzhengjie2019]> 
MariaDB [yinzhengjie2019]> 
MariaDB [yinzhengjie2019]> QUIT
Bye
[root@node103.yinzhengjie.org.cn ~]#
限制从服务器为只读,此限制对拥有SUPER权限的用户均无效
[root@node103.yinzhengjie.org.cn ~]# mysql -uroot -pyinzhengjie
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 18
Server version: 5.5.64-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> 
MariaDB [(none)]> FLUSH TABLES WITH READ LOCK;          #添加全局锁,阻止所有用户, 包括主服务器复制的更新
Query OK, 0 rows affected (0.01 sec)

MariaDB [(none)]> 
MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| devops             |
| mysql              |
| performance_schema |
| test               |
| yinzhengjie2019    |
+--------------------+
6 rows in set (0.00 sec)

MariaDB [(none)]> 
MariaDB [(none)]> DROP DATABASE devops;
ERROR 1223 (HY000): Can't execute the query because you have a conflicting read lock
MariaDB [(none)]> 
MariaDB [(none)]> 
MariaDB [(none)]> CREATE DATABASE db1;
ERROR 1223 (HY000): Can't execute the query because you have a conflicting read lock
MariaDB [(none)]> 
MariaDB [(none)]> SELECT USER();
+----------------+
| USER()         |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)

MariaDB [(none)]> 
MariaDB [(none)]> UNLOCK TABLES;                     #解锁
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> 
MariaDB [(none)]> QUIT
Bye
[root@node103.yinzhengjie.org.cn ~]# 
[root@node103.yinzhengjie.org.cn ~]#
MariaDB [(none)]> FLUSH TABLES WITH READ LOCK; #添加全局锁,阻止所有用户, 包括主服务器复制的更新

2>.RESET SLAVE

　　在从服务器清除master.info ，relay-log.info, relay log ，开始新的relay log ,注意：需要先STOP SLAVE

　　RESET SLAVE ALL 清除所有从服务器上设置的主服务器同步信息如：PORT, HOST, USER和 PASSWORD 等

3>.sql_slave_skip_counter = N

　　从服务器忽略几个主服务器的复制事件，global变量
[root@node102.yinzhengjie.org.cn ~]# mysql -uroot -pyinzhengjie
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 32
Server version: 5.5.64-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> 
MariaDB [(none)]> CREATE DATABASE linux;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> 
MariaDB [(none)]> USE linux
Database changed
MariaDB [linux]> 
MariaDB [linux]>  CREATE TABLE students(id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,name VARCHAR(30) NOT NULL,sex ENUM('bo
y','girl') DEFAULT 'boy',age TINYINT UNSIGNED,mobile CHAR(11),address VARCHAR(50));Query OK, 0 rows affected (0.01 sec)

MariaDB [linux]> 
MariaDB [linux]> INSERT INTO students (name,age,mobile,address) VALUES ('Jason Yin',18,10000,'beijing'),('Jay','40',10086
,'Taiwan');Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

MariaDB [linux]> 
MariaDB [linux]> SELECT * FROM students;
+----+-----------+------+------+--------+---------+
| id | name      | sex  | age  | mobile | address |
+----+-----------+------+------+--------+---------+
|  1 | Jason Yin | boy  |   18 | 10000  | beijing |
|  2 | Jay       | boy  |   40 | 10086  | Taiwan  |
+----+-----------+------+------+--------+---------+
2 rows in set (0.00 sec)

MariaDB [linux]>
使用超级用户在master节点(node102.yinzhengjie.org.cn)上创建测试数据库及表信息（含主键）
[root@node103.yinzhengjie.org.cn ~]# mysql -uroot -pyinzhengjie
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 23
Server version: 5.5.64-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> 
MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| devops             |
| linux              |
| mysql              |
| performance_schema |
| test               |
| yinzhengjie2019    |
+--------------------+
7 rows in set (0.00 sec)

MariaDB [(none)]> 
MariaDB [(none)]> USE linux
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [linux]> 
MariaDB [linux]> SHOW TABLES;
+-----------------+
| Tables_in_linux |
+-----------------+
| students        |
+-----------------+
1 row in set (0.00 sec)

MariaDB [linux]> 
MariaDB [linux]> SELECT * FROM students;
+----+-----------+------+------+--------+---------+
| id | name      | sex  | age  | mobile | address |
+----+-----------+------+------+--------+---------+
|  1 | Jason Yin | boy  |   18 | 10000  | beijing |
|  2 | Jay       | boy  |   40 | 10086  | Taiwan  |
+----+-----------+------+------+--------+---------+
2 rows in set (0.00 sec)

MariaDB [linux]> 
MariaDB [linux]> INSERT INTO students SET name='yinzhengjie',age=27,address='shanxi';
Query OK, 1 row affected (0.01 sec)

MariaDB [linux]> SELECT * FROM students;
+----+-------------+------+------+--------+---------+
| id | name        | sex  | age  | mobile | address |
+----+-------------+------+------+--------+---------+
|  1 | Jason Yin   | boy  |   18 | 10000  | beijing |
|  2 | Jay         | boy  |   40 | 10086  | Taiwan  |
|  3 | yinzhengjie | boy  |   27 | NULL   | shanxi  |
+----+-------------+------+------+--------+---------+
3 rows in set (0.00 sec)

MariaDB [linux]> 
MariaDB [linux]>
使用超级用户在级联节点(node103.yinzhengjie.org.cn)上修改从master节点同步过来的数据
[root@node102.yinzhengjie.org.cn ~]# mysql -uroot -pyinzhengjie
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 33
Server version: 5.5.64-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> 
MariaDB [(none)]> USE linux
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [linux]> 
MariaDB [linux]> SELECT * FROM students;
+----+-----------+------+------+--------+---------+
| id | name      | sex  | age  | mobile | address |
+----+-----------+------+------+--------+---------+
|  1 | Jason Yin | boy  |   18 | 10000  | beijing |
|  2 | Jay       | boy  |   40 | 10086  | Taiwan  |
+----+-----------+------+------+--------+---------+
2 rows in set (0.00 sec)

MariaDB [linux]> 
MariaDB [linux]> INSERT students (age,sex,name,mobile,address) VALUES (28,'girl','Gloria Tang Tsz-Kei',null,'Hong Kong');
Query OK, 1 row affected (0.00 sec)
MariaDB [linux]> 
MariaDB [linux]> SELECT * FROM students;
+----+---------------------+------+------+--------+-----------+
| id | name                | sex  | age  | mobile | address   |
+----+---------------------+------+------+--------+-----------+
|  1 | Jason Yin           | boy  |   18 | 10000  | beijing   |
|  2 | Jay                 | boy  |   40 | 10086  | Taiwan    |
|  3 | Gloria Tang Tsz-Kei | girl |   28 | NULL   | Hong Kong |
+----+---------------------+------+------+--------+-----------+
3 rows in set (0.00 sec)

MariaDB [linux]>
此时再用使用超级用户在master节点(node102.yinzhengjie.org.cn)上同一张表插入数据
[root@node102.yinzhengjie.org.cn ~]# mysql -uroot -pyinzhengjie
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 33
Server version: 5.5.64-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> 
MariaDB [(none)]> USE linux
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [linux]> 
MariaDB [linux]> SELECT * FROM students;
+----+-----------+------+------+--------+---------+
| id | name      | sex  | age  | mobile | address |
+----+-----------+------+------+--------+---------+
|  1 | Jason Yin | boy  |   18 | 10000  | beijing |
|  2 | Jay       | boy  |   40 | 10086  | Taiwan  |
+----+-----------+------+------+--------+---------+
rows in set (0.00 sec)

MariaDB [linux]> 
MariaDB [linux]> INSERT students (age,sex,name,mobile,address) VALUES (28,'girl','Gloria Tang Tsz-Kei',null,'Hong Kong');
Query OK, 1 row affected (0.00 sec)
MariaDB [linux]> 
MariaDB [linux]> INSERT students (age,sex,name,mobile,address) VALUES (38,'girl','张娜拉',null,'韩国');
Query OK, 1 row affected (0.00 sec)

MariaDB [linux]> 
MariaDB [linux]> INSERT students (id,age,sex,name,mobile,address) VALUES (5,37,'girl','胡歌',null,'上海');
Query OK, 1 row affected (0.01 sec)

MariaDB [linux]> 
MariaDB [linux]> SELECT * FROM students;
+----+---------------------+------+------+--------+-----------+
| id | name                | sex  | age  | mobile | address   |
+----+---------------------+------+------+--------+-----------+
|  1 | Jason Yin           | boy  |   18 | 10000  | beijing   |
|  2 | Jay                 | boy  |   40 | 10086  | Taiwan    |
|  3 | Gloria Tang Tsz-Kei | girl |   28 | NULL   | Hong Kong |
|  4 | 张娜拉              | girl |   38 | NULL   | 韩国      |
|  5 | 胡歌                | girl |   37 | NULL   | 上海      |
+----+---------------------+------+------+--------+-----------+
5 rows in set (0.00 sec)

MariaDB [linux]>
此时再用使用超级用户在master节点(node102.yinzhengjie.org.cn)上同一张表插入数据
MariaDB [linux]> SELECT * FROM students;
+----+-------------+------+------+--------+---------+
| id | name        | sex  | age  | mobile | address |
+----+-------------+------+------+--------+---------+
|  1 | Jason Yin   | boy  |   18 | 10000  | beijing |
|  2 | Jay         | boy  |   40 | 10086  | Taiwan  |
|  3 | yinzhengjie | boy  |   27 | NULL   | shanxi  |
+----+-------------+------+------+--------+---------+
3 rows in set (0.00 sec)

MariaDB [linux]> 
MariaDB [linux]> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.30.1.102
                  Master_User: copy
                  Master_Port: 3306
                Connect_Retry: 10
              Master_Log_File: master-bin.000003
          Read_Master_Log_Pos: 22191700
               Relay_Log_File: relay-log.000007
                Relay_Log_Pos: 3046
        Relay_Master_Log_File: master-bin.000003
             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: 1062
                   Last_Error: Could not execute Write_rows event on table linux.students; Duplicate entry '3' for key 'P
RIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log master-bin.000003, end_log_pos 22191673                 Skip_Counter: 0
          Exec_Master_Log_Pos: 22191477
              Relay_Log_Space: 3798
              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: 1062
               Last_SQL_Error: Could not execute Write_rows event on table linux.students; Duplicate entry '3' for key 'P
RIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log master-bin.000003, end_log_pos 22191673  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 102
1 row in set (0.00 sec)

MariaDB [linux]>
此时再去级联节点查看信息，发现主从复制线程竟然出错啦！报错原因是主键冲突！(这是咱们意料中的）
[root@node103.yinzhengjie.org.cn ~]# mysql -uroot -pyinzhengjie
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 24
Server version: 5.5.64-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> 
MariaDB [(none)]> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
               Slave_IO_State: 
                  Master_Host: 172.30.1.102
                  Master_User: copy
                  Master_Port: 3306
                Connect_Retry: 10
              Master_Log_File: master-bin.000003
          Read_Master_Log_Pos: 22192537
               Relay_Log_File: relay-log.000007
                Relay_Log_Pos: 3046
        Relay_Master_Log_File: master-bin.000003
             Slave_IO_Running: No
            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: 1062
                   Last_Error: Could not execute Write_rows event on table linux.students; Duplicate entry '3' for key 'P
RIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log master-bin.000003, end_log_pos 22191673                 Skip_Counter: 1
          Exec_Master_Log_Pos: 22191477
              Relay_Log_Space: 4635
              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: 1062
               Last_SQL_Error: Could not execute Write_rows event on table linux.students; Duplicate entry '3' for key 'P
RIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log master-bin.000003, end_log_pos 22191673  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 102
row in set (0.00 sec)

MariaDB [(none)]> 
MariaDB [(none)]> SELECT * FROM linux.students;
+----+-------------+------+------+--------+---------+
| id | name        | sex  | age  | mobile | address |
+----+-------------+------+------+--------+---------+
|  1 | Jason Yin   | boy  |   18 | 10000  | beijing |
|  2 | Jay         | boy  |   40 | 10086  | Taiwan  |
|  3 | yinzhengjie | boy  |   27 | NULL   | shanxi  |
+----+-------------+------+------+--------+---------+
rows in set (0.00 sec)

MariaDB [(none)]> 
MariaDB [(none)]> USE  linux
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [linux]> 
MariaDB [linux]> STOP SLAVE;
Query OK, 0 rows affected (0.01 sec)

MariaDB [linux]> 
MariaDB [linux]> SELECT @@sql_slave_skip_counter;
+--------------------------+
| @@sql_slave_skip_counter |
+--------------------------+
|                        0 |
+--------------------------+
row in set (0.00 sec)

MariaDB [linux]> 
MariaDB [linux]> SET GLOBAL sql_slave_skip_counter = 1;  　　　　　　#我这里设置从服务器忽略1个主服务器的复制事件，注意它是global变量。
Query OK, 0 rows affected (0.00 sec)

MariaDB [linux]> 
MariaDB [linux]> SELECT @@sql_slave_skip_counter;
+--------------------------+
| @@sql_slave_skip_counter |
+--------------------------+
|                        1 |
+--------------------------+
row in set (0.00 sec)

MariaDB [linux]> 
MariaDB [linux]>
MariaDB [linux]> START SLAVE;
Query OK, 0 rows affected (0.00 sec)

MariaDB [linux]> 
MariaDB [(none)]> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.30.1.102
                  Master_User: copy
                  Master_Port: 3306
                Connect_Retry: 10
              Master_Log_File: master-bin.000003
          Read_Master_Log_Pos: 22192537
               Relay_Log_File: relay-log.000009
                Relay_Log_Pos: 530
        Relay_Master_Log_File: master-bin.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: 22192537
              Relay_Log_Space: 1103
              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: 102
row in set (0.00 sec)

MariaDB [(none)]> 
MariaDB [linux]> SELECT * FROM students;　　　　　　#此时数据同步啦
+----+-------------+------+------+--------+---------+
| id | name        | sex  | age  | mobile | address |
+----+-------------+------+------+--------+---------+
|  1 | Jason Yin   | boy  |   18 | 10000  | beijing |
|  2 | Jay         | boy  |   40 | 10086  | Taiwan  |
|  3 | yinzhengjie | boy  |   27 | NULL   | shanxi  |
|  4 | 张娜拉      | girl |   38 | NULL   | 韩国    |
|  5 | 胡歌        | girl |   37 | NULL   | 上海    |
+----+-------------+------+------+--------+---------+
rows in set (0.00 sec)

MariaDB [linux]> 
MariaDB [linux]> QUIT
Bye
[root@node103.yinzhengjie.org.cn ~]# 
[root@node103.yinzhengjie.org.cn ~]#
MariaDB [linux]> SET GLOBAL sql_slave_skip_counter = 1;　　#我设置从服务器忽略1个主服务器的复制事件
[root@node103.yinzhengjie.org.cn ~]# mysql -uroot -pyinzhengjie 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 31
Server version: 5.5.64-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> 
MariaDB [(none)]> USE linux
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [linux]> 
MariaDB [linux]> SELECT * FROM students;
+----+-------------+------+------+--------+---------+
| id | name        | sex  | age  | mobile | address |
+----+-------------+------+------+--------+---------+
|  1 | Jason Yin   | boy  |   18 | 10000  | beijing |
|  2 | Jay         | boy  |   40 | 10086  | Taiwan  |
|  3 | yinzhengjie | boy  |   27 | NULL   | shanxi  |
|  4 | 张娜拉      | girl |   38 | NULL   | 韩国    |
|  5 | 胡歌        | girl |   37 | NULL   | 上海    |
+----+-------------+------+------+--------+---------+
rows in set (0.00 sec)

MariaDB [linux]> 
MariaDB [linux]> UPDATE students SET name='Gloria Tang Tsz-Kei',sex='girl',age=28,address='Hong Kong' WHERE id = 3;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [linux]> 
MariaDB [linux]> SELECT * FROM students;
+----+---------------------+------+------+--------+-----------+
| id | name                | sex  | age  | mobile | address   |
+----+---------------------+------+------+--------+-----------+
|  1 | Jason Yin           | boy  |   18 | 10000  | beijing   |
|  2 | Jay                 | boy  |   40 | 10086  | Taiwan    |
|  3 | Gloria Tang Tsz-Kei | girl |   28 | NULL   | Hong Kong |
|  4 | 张娜拉              | girl |   38 | NULL   | 韩国      |
|  5 | 胡歌                | girl |   37 | NULL   | 上海      |
+----+---------------------+------+------+--------+-----------+
rows in set (0.00 sec)

MariaDB [linux]> 
MariaDB [linux]> QUIT
Bye
[root@node103.yinzhengjie.org.cn ~]# 
[root@node103.yinzhengjie.org.cn ~]#
上面只是临时解决不报错,但并没有解决数据不同步的问题，因此我们在生产环境中需要手动修改和主库一样的数据

4>.如何保证主从复制的事务安全

在master节点启用参数：
　　sync_binlog=1 每次写后立即同步二进制日志到磁盘，性能差
　　如果用到的为InnoDB存储引擎：
　　　　innodb_flush_log_at_trx_commit=1 每次事务提交立即同步日志写磁盘
　　　　innodb_support_xa=ON 默认值，分布式事务MariaDB10.3.0废除
　　　　sync_master_info=# #次事件后master.info同步到磁盘

在slave节点启用服务器选项：
　　skip_slave_start=ON 不自动启动slave，服务器选项，默认为OFF即自动启动slave

在slave节点启用参数：
　　sync_relay_log=# #次写后同步relay log到磁盘
　　sync_relay_log_info=# #次事务后同步relay-log.info到磁盘

博主推荐阅读:
    https://mariadb.com/kb/en/library/server-system-variables/

 

四.实战部署时遇到的问题及解决方案

1>.主从的BINLOG_FORMAT不一致，主为ROW,从为STATEMENT

MariaDB [yinzhengjie2019]> SHOW SLAVE STATUS\G          　　#仔细查看Last_Error和Last_SQL_Error的报错提示信息
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.30.1.102
                  Master_User: copy
                  Master_Port: 3306
                Connect_Retry: 10
              Master_Log_File: master-bin.000003
          Read_Master_Log_Pos: 15836165
               Relay_Log_File: relay-log.000002
                Relay_Log_Pos: 530
        Relay_Master_Log_File: master-bin.000003
             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: 1666
                   Last_Error: Error executing row event: 'Cannot execute statement: impossible to write to binary log si
nce statement is in row format and BINLOG_FORMAT = STATEMENT.'                 Skip_Counter: 0
          Exec_Master_Log_Pos: 245
              Relay_Log_Space: 15836738
              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: 1666
               Last_SQL_Error: Error executing row event: 'Cannot execute statement: impossible to write to binary log si
nce statement is in row format and BINLOG_FORMAT = STATEMENT.'  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 102
1 row in set (0.00 sec)

MariaDB [yinzhengjie2019]>
MariaDB [yinzhengjie2019]> SHOW SLAVE STATUS\G 　　#仔细查看Last_Error和Last_SQL_Error的报错提示信息
报错分析:
　　由于主从复制使用的二进制格式不一致导致的报错。

解决方案:
　　可以在配置文件中2个数据库实例显示指定"binlog_format=row"，当二进制日志格式相同时这个报错就自然解决了，下面时查看二进制日志格式的命令。


MariaDB [(none)]> SHOW VARIABLES  LIKE 'BINLOG_FORMAT';　　#推荐将"binlog_format=row"写入"my.cnf"文件并重启实例可永久修改，并不推荐临时使用SET命令设置
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW   |
+---------------+-------+
1 row in set (0.00 sec)

MariaDB [(none)]> 
[root@node103.yinzhengjie.org.cn ~]# mysql -uroot -pyinzhengjie
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 27
Server version: 5.5.64-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> 
MariaDB [(none)]> 
MariaDB [(none)]> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.30.1.102
                  Master_User: copy
                  Master_Port: 3306
                Connect_Retry: 10
              Master_Log_File: master-bin.000003
          Read_Master_Log_Pos: 22192537
               Relay_Log_File: relay-log.000007
                Relay_Log_Pos: 3269
        Relay_Master_Log_File: master-bin.000003
             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: 1666
                   Last_Error: Error executing row event: 'Cannot execute statement: impossible to write to binary log si
nce statement is in row format and BINLOG_FORMAT = STATEMENT.'                 Skip_Counter: 0
          Exec_Master_Log_Pos: 22191700
              Relay_Log_Space: 5208
              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: 1666
               Last_SQL_Error: Error executing row event: 'Cannot execute statement: impossible to write to binary log si
nce statement is in row format and BINLOG_FORMAT = STATEMENT.'  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 102
1 row in set (0.00 sec)

MariaDB [(none)]> 
MariaDB [(none)]> SELECT @@binlog_format;
+-----------------+
| @@binlog_format |
+-----------------+
| STATEMENT       |
+-----------------+
1 row in set (0.00 sec)

MariaDB [(none)]> 
MariaDB [(none)]> STOP SLAVE; 
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> 
MariaDB [(none)]> SET GLOBAL  binlog_format = 'ROW';
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> 
MariaDB [(none)]> SELECT @@binlog_format;
+-----------------+
| @@binlog_format |
+-----------------+
| STATEMENT       |
+-----------------+
1 row in set (0.00 sec)

MariaDB [(none)]> 
MariaDB [(none)]> SET binlog_format = 'ROW';
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> 
MariaDB [(none)]> SELECT @@binlog_format;
+-----------------+
| @@binlog_format |
+-----------------+
| ROW             |
+-----------------+
1 row in set (0.00 sec)

MariaDB [(none)]> 
MariaDB [(none)]> START SLAVE;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> 
MariaDB [(none)]> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.30.1.102
                  Master_User: copy
                  Master_Port: 3306
                Connect_Retry: 10
              Master_Log_File: master-bin.000003
          Read_Master_Log_Pos: 22192537
               Relay_Log_File: relay-log.000009
                Relay_Log_Pos: 530
        Relay_Master_Log_File: master-bin.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: 22192537
              Relay_Log_Space: 1103
              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: 102
1 row in set (0.00 sec)

MariaDB [(none)]>
MariaDB [(none)]> SET GLOBAL binlog_format = 'ROW';　　　　　　#戳这里查看详细过程

2>.主从二进制日志文件名称配置不一致(Could not find first log file name in binary log index file)

MariaDB [(none)]> SHOW SLAVE STATUS\G          　　　　#仔细查看Last_IO_Error错误提示信息
*************************** 1. row ***************************
               Slave_IO_State: 
                  Master_Host: 172.30.1.103
                  Master_User: copy
                  Master_Port: 3306
                Connect_Retry: 10
              Master_Log_File: master-bin.000007
          Read_Master_Log_Pos: 245
               Relay_Log_File: relay-log.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: master-bin.000007
             Slave_IO_Running: No
            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: 245
              Relay_Log_Space: 245
              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: 1236
                Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Could not find first 
log file name in binary log index file'               
 　　　　　　    Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 103
1 row in set (0.00 sec)

MariaDB [(none)]>
MariaDB [(none)]> SHOW SLAVE STATUS\G 　　　　#仔细查看Last_IO_Error错误提示信息
报错分析:
　　报这个错一般是由于从节点通过在配置CHANGE MASTER TO时，将MASTER_LOG_FILE的所对应的文件名称配置错误导致的。

解决方案:
　　要解决这个问题首先得确认主从复制关系，然后找到master节点的二进制日志文件名称，然后在slave节点指向master的真正存在的名称即可。
即手动修改对应的master的二进制日志位置参数:"CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000008',MASTER_LOG_POS=107;"

3>.主键冲突类错误(Duplicate entry '3' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY;)

MariaDB [linux]> SHOW SLAVE STATUS\G   　　　　　　#仔细观察Last_SQL_Error和Last_Error的报错信息
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.30.1.102
                  Master_User: copy
                  Master_Port: 3306
                Connect_Retry: 10
              Master_Log_File: master-bin.000003
          Read_Master_Log_Pos: 22191700
               Relay_Log_File: relay-log.000007
                Relay_Log_Pos: 3046
        Relay_Master_Log_File: master-bin.000003
             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: 1062
                   Last_Error: Could not execute Write_rows event on table linux.students; Duplicate entry '3' for key 'P
RIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log master-bin.000003, end_log_pos 22191673                 Skip_Counter: 0
          Exec_Master_Log_Pos: 22191477
              Relay_Log_Space: 3798
              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: 1062
               Last_SQL_Error: Could not execute Write_rows event on table linux.students; Duplicate entry '3' for key 'P
RIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log master-bin.000003, end_log_pos 22191673  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 102
1 row in set (0.00 sec)

MariaDB [linux]>
MariaDB [linux]> SHOW SLAVE STATUS\G 　　　　　　#仔细观察Last_SQL_Error和Last_Error的报错信息
报错分析:
　　这种错误一般是由于从库和主库数据不一致到的报错信息，我上面就通过模拟了主键冲突导致MySQL主从同步失败。

解决办法:
　　可以使用sql_slave_skip_counter参数来忽略一些复制时间，从而让从库可以进行复制，剩下的数据不同步的问题在另行解决方案。

 

posted @ 2019-11-08 13:22  尹正杰  阅读(576)  评论(0)  编辑  收藏  举报