• mysql 主从复制


    MySQL复制允许将主实例(master)上的数据同步到一个或多个从实例(slave)上,默认情况下复制是异步进行的,从库也不需要一直连接到主库来同步数据
    MySQL复制的数据粒度可以是主实例上所有的数据库,也可以是指定的一个或多个数据库,也可以是一个数据库里的指定的表
    MySQL复制所带来的优势在于:
    • 扩展能力:通过复制功能可以将MySQL的性能压力分担到一个或多个slave上。这要求所有 的写操作和修改操作都必须在Master上完成,而读操作可以被分配到一个或多个slave上。 将读写分离到不同服务器执行之后,MySQL的读写性能得到提升
    • 数据库备份:由于从实例是同步主实例的数据,所以可以将备份作业部署到从库
    • 数据分析和报表:同样,一些数据分析和报表的实现可以在从实例执行,以减少对主库的性能影响
    • 容灾能力:可以在物理距离较远的另一个数据中心建立一个slave,保证在主实例所在地区遭遇灾难时,在另一个数据中心能快速恢复;

    MySQL复制有两种方法:
    • 传统方式:基于主库的bin-log将日志事件和事件位置复制到从库,从库再加以 应用来达到主从同步的目的
    • Gtid方式:global transaction identifiers是基于事务来复制数据,因此也就不依赖日志文件位置,同时又能更好的保证主从库数据一致性
    MySQL复制有多种类型:
    • 异步复制:一个主库,一个或多个从库,数据异步同步到从库
    • 同步复制:在MySQL Cluster中特有的复制方式
    • 半同步复制:在异步复制的基础上,确保任何一个主库上的事务在提交之前至 少有一个从库已经收到该事务并日志记录下来
    • 延迟复制:在异步复制的基础上,人为设定主库和从库的数据同步延迟时间,即保证数据延迟至少是这个参数

    复制的工作原理是数据库修改事件记录到bin log中并传递到slave,然后slave在本地还原的过程。而事件记录到bin log的格式会有所不同。
    MySQL复制有三种核心格式
    • 基于语句的复制(statement based replication):基于主库将SQL语句写入到 bin log中完成复制
    • 基于行数据的复制(row based replication):基于主库将每一个行数据变化的信息作为事件写入到bin log中完成日志
    • 混合复制(mixed based replication):上述两者的结合。默认情况下优先使用基于语句的复制,只有当部分语句如果基于语句复制不安全的情况下才会自动切换为基于行数据的复制

    主库
    mysql> show variables like '%binlog_format%';                                                                                              
    +---------------+-------+
    | Variable_name | Value |
    +---------------+-------+
    | binlog_format | ROW   |
    +---------------+-------+
    1 row in set (0.01 sec)
    
    mysql> show variables like '%log_bin%';
    +---------------------------------+------------------------------------+
    | Variable_name                   | Value                              |
    +---------------------------------+------------------------------------+
    | log_bin                         | ON                                 |
    | log_bin_basename                | /usr/local/mysql/data/binlog       |
    | log_bin_index                   | /usr/local/mysql/data/binlog.index |
    | log_bin_trust_function_creators | OFF                                |
    | log_bin_use_v1_row_events       | OFF                                |
    | sql_log_bin                     | ON                                 |
    +---------------------------------+------------------------------------+
    6 rows in set (0.01 sec)
    主库和每个从库都必须有一个唯一ID,server_id;
    mysql> show variables like '%server_id%';
    +----------------+-------+
    | Variable_name  | Value |
    +----------------+-------+
    | server_id      | 1     |
    | server_id_bits | 32    |
    +----------------+-------+
    2 rows in set (0.01 sec)

    从库1和2配置相同

    从库
    mysql> show variables like '%log_bin%';
    +---------------------------------+------------------------------------+
    | Variable_name                   | Value                              |
    +---------------------------------+------------------------------------+
    | log_bin                         | ON                                 |
    | log_bin_basename                | /usr/local/mysql/data/binlog       |
    | log_bin_index                   | /usr/local/mysql/data/binlog.index |
    | log_bin_trust_function_creators | OFF                                |
    | log_bin_use_v1_row_events       | OFF                                |
    | sql_log_bin                     | ON                                 |
    +---------------------------------+------------------------------------+
    6 rows in set (0.03 sec)
    
    mysql> show variables like '%server_id%';
    +----------------+-------+
    | Variable_name  | Value |
    +----------------+-------+
    | server_id      | 2     |
    | server_id_bits | 32    |
    +----------------+-------+
    2 rows in set (0.00 sec)
    
    
    mysql>  show variables like '%binlog_format%'; 
    +---------------+-------+
    | Variable_name | Value |
    +---------------+-------+
    | binlog_format | ROW   |
    +---------------+-------+
    1 row in set (0.00 sec)
    

    在主库创建一个专门用来做复制的数据库用户,这样所有从库都可以用这个用户来连接主库,也可以确保这个用户只有复制的权限虽然可以用任何拥有复制权限的MySQL用户来建立复制关系,但由于被使用的用户名和密码会明文保存在备库的master.info文件中,所以为安全起见,最好是使用仅有复制权限的独立用户

    创建主从复制用户
    主库
    mysql> create user 'repl'@'192.168.204.%' identified by 'mysql';
    Query OK, 0 rows affected (0.12 sec)
    mysql> grant replication slave on *.* to 'repl'@'192.168.204.%';
    Query OK, 0 rows affected (0.07 sec)

    mysql 基于binlog 复制,初始化方法1

    主库上锁
    mysql> flush tables with read lock;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> show master status;
    +---------------+----------+--------------+------------------+-------------------+
    | File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
    +---------------+----------+--------------+------------------+-------------------+
    | binlog.000008 |      709 |              |                  |                   |
    +---------------+----------+--------------+------------------+-------------------+
    1 row in set (0.00 sec)
    主库
    [root@centos7 ~]# mysqldump --all-databases --master-data -u root -p > dbdump.db
    Enter password: 
    mysql> unlock tables; [root@centos7
    ~]# ls anaconda-ks.cfg dbdump.db mysql-8.0.13-linux-glibc2.12-x86_64.tar.xz [root@centos7 ~]# sftp -oPort=22 192.168.204.133 root@192.168.204.133's password: Connected to 192.168.204.133. sftp> put dbdump.db Uploading dbdump.db to /root/dbdump.db dbdump.db 100% 906KB 905.8KB/s 00:00 从库导入 mysql> source dbdump.db; mysql> show databases; +--------------------+ | Database | +--------------------+ | A1 | | A2 | | course | | course2 | | course3 | | course4 | | information_schema | | mysql | | performance_schema | | sys | +--------------------+ 10 rows in set (0.01 sec)

    方法2

    拷文件的方式
    主库和从库都关闭
    主库
    [root@centos7 ~]# /etc/init.d/mysql.server stop
    Shutting down MySQL..... SUCCESS! 
    [root@centos7 ~]# cd /usr/local/mysql/data/
    [root@centos7 data]# cd ..
    [root@centos7 mysql]# tar -zcvf data.tar.gz ./data/
    [root@centos7 mysql]# sftp -oPort=22 root@192.168.204.133
    root@192.168.204.133's password: 
    Permission denied, please try again.
    
    root@192.168.204.133's password: 
    Connected to 192.168.204.133.
    sftp> lls
    bin  data  data.tar.gz    docs  include  lib  LICENSE  LICENSE.router  man  README  README.router  share    support-files
    sftp> put data.tar.gz
    Uploading data.tar.gz to /root/data.tar.gz
    data.tar.gz                                                                                                                           100% 5594KB   5.5MB/s   00:00    
    sftp> 
    从库
    [root@centos7 mysql]# mv data data_bak
    [root@centos7 mysql]# tar xf data.tar.gz 
    [root@centos7 mysql]# ll
    总用量 6080
    drwxr-xr-x.  2 mysql mysql    4096 2月  17 10:05 bin
    drwxr-xr-x. 12 mysql mysql    4096 3月  30 14:32 data
    drwxr-xr-x.  6 mysql mysql    4096 3月  30 19:25 data_bak
    -rw-r--r--.  1 mysql mysql 5751938 3月  30 14:42 data.tar.gz
    drwxr-xr-x.  2 mysql mysql      82 2月  17 10:05 docs
    drwxr-xr-x.  3 mysql mysql    4096 2月  17 10:04 include
    drwxr-xr-x.  6 mysql mysql    4096 2月  17 10:05 lib
    -rw-r--r--.  1 mysql mysql  335809 10月  7 16:44 LICENSE
    -rw-r--r--.  1 mysql mysql  101807 10月  7 16:44 LICENSE.router
    drwxr-xr-x.  4 mysql mysql      28 2月  17 10:04 man
    -rw-r--r--.  1 mysql mysql     687 10月  7 16:44 README
    -rw-r--r--.  1 mysql mysql     700 10月  7 16:44 README.router
    drwxr-xr-x. 28 mysql mysql    4096 2月  17 10:05 share
    drwxr-xr-x.  2 mysql mysql      86 2月  17 10:14 support-files
    [root@centos7 mysql]# cd data
    [root@centos7 data]# rm auto.cnf -f

    创建主从关系

    [root@centos7 data]# /etc/init.d/mysql.server start
    mysql> 
    mysql> CHANGE MASTER TO
        -> MASTER_HOST='192.168.204.132',
        -> MASTER_PORT=3306,
        -> MASTER_USER='repl',
        -> MASTER_PASSWORD='mysql',
        -> MASTER_LOG_FILE='binlog.000008',
        -> MASTER_LOG_POS=709;
    Query OK, 0 rows affected, 2 warnings (0.01 sec)
    
    mysql> start slave;
    Query OK, 0 rows affected (0.02 sec)
    
    mysql> show slave status G;
    *************************** 1. row ***************************
                   Slave_IO_State: Waiting for master to send event
                      Master_Host: 192.168.204.132
                      Master_User: repl
                      Master_Port: 3306
                    Connect_Retry: 60
                  Master_Log_File: binlog.000009
              Read_Master_Log_Pos: 155
                   Relay_Log_File: centos7-relay-bin.000003
                    Relay_Log_Pos: 363
            Relay_Master_Log_File: binlog.000009
                 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: 155
                  Relay_Log_Space: 737
                  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
                      Master_UUID: e30d70f7-325a-11e9-811b-000c2940fa33
                 Master_Info_File: mysql.slave_master_info
                        SQL_Delay: 0
              SQL_Remaining_Delay: NULL
          Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
               Master_Retry_Count: 86400
                      Master_Bind: 
          Last_IO_Error_Timestamp: 
         Last_SQL_Error_Timestamp: 
                   Master_SSL_Crl: 
               Master_SSL_Crlpath: 
               Retrieved_Gtid_Set: 
                Executed_Gtid_Set: 
                    Auto_Position: 0
             Replicate_Rewrite_DB: 
                     Channel_Name: 
               Master_TLS_Version: 
           Master_public_key_path: 
            Get_master_public_key: 0
    1 row in set (0.00 sec)
    mysql> stop slave;
    Query OK, 0 rows affected (0.03 sec)

    创建主从复制报错

    主从复制保错
    Last_IO_Error: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server ids; 
    Last_IO_Error: error connecting to master 'repl@192.168.204.132:3306' - retry-time: 60  retries: 1
    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'
    
    重置主从关系
    主库操作
    mysql> stop slave;
    mysql> reset slave all;

    如果主库不能锁表,主库上有业务操作

    模拟操作
    mysql> use A1;
    mysql> delimiter //
    mysql> create procedure proc1()
        -> begin
        -> declare n int default 1;
        -> while n<=20000 do
        -> insert into temp values(n,'mike');
        -> set n=n+1;
        -> end while;
        -> end;
        -> //
    mysql> delimiter ;
    mysql> show master status;
    +---------------+----------+--------------+------------------+-------------------+
    | File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
    +---------------+----------+--------------+------------------+-------------------+
    | binlog.000009 |     1158 |              |                  |                   |
    +---------------+----------+--------------+------------------+-------------------+
    1 row in set (0.00 sec)
    
    mysql> create table temp(id int,name varchar(20));
    Query OK, 0 rows affected (0.03 sec)
    
    mysql> call proc1();
    Query OK, 1 row affected (2 min 10.79 sec)

    错误的方式创建主从关系

    mysql> show master status;
    +---------------+----------+--------------+------------------+-------------------+
    | File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
    +---------------+----------+--------------+------------------+-------------------+
    | binlog.000009 |  5642155 |              |                  |                   |
    +---------------+----------+--------------+------------------+-------------------+
    1 row in set (0.00 sec)
    
    mysql> call proc1();
    Query OK, 1 row affected (31.77 sec)
    
    
    [root@centos7 ~]# mysqldump --all-databases --master-data -u root -p > dbdump.db
    Enter password: 
    mysql> select count(*) from temp;
    +----------+
    | count(*) |
    +----------+
    |    20000 |
    +----------+
    1 row in set (0.02 sec)
    [root@centos7 ~]# sftp -oPort=22 192.168.204.133
    root@192.168.204.133's password: 
    Connected to 192.168.204.133.
    sftp> put dbdump.db 
    Uploading dbdump.db to /root/dbdump.db
    dbdump.db                                                                                                                                                                      100%  965KB 964.6KB/s   00:00    
    sftp> 
    
    从库
    mysql> source dbdump.db
    mysql> CHANGE MASTER TO
        -> MASTER_HOST='192.168.204.132',
        -> MASTER_PORT=3306,
        -> MASTER_USER='repl',
        -> MASTER_PASSWORD='mysql',
        -> MASTER_LOG_FILE='binlog.000009',
        -> MASTER_LOG_POS=5642155;
    Query OK, 0 rows affected, 2 warnings (0.04 sec)
    
    mysql> start slave;
    Query OK, 0 rows affected (0.10 sec)
    
    mysql> show slave statusG;
    *************************** 1. row ***************************
                   Slave_IO_State: Waiting for master to send event
                      Master_Host: 192.168.204.132
                      Master_User: repl
                      Master_Port: 3306
                    Connect_Retry: 60
                  Master_Log_File: binlog.000009
              Read_Master_Log_Pos: 11282155
                   Relay_Log_File: centos7-relay-bin.000002
                    Relay_Log_Pos: 3577207
            Relay_Master_Log_File: binlog.000009
                 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: 9219043
                  Relay_Log_Space: 5640529
                  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: 124
    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
                      Master_UUID: e30d70f7-325a-11e9-811b-000c2940fa33
                 Master_Info_File: mysql.slave_master_info
                        SQL_Delay: 0
              SQL_Remaining_Delay: NULL
          Slave_SQL_Running_State: waiting for handler commit
               Master_Retry_Count: 86400
                      Master_Bind: 
          Last_IO_Error_Timestamp: 
         Last_SQL_Error_Timestamp: 
                   Master_SSL_Crl: 
               Master_SSL_Crlpath: 
               Retrieved_Gtid_Set: 
                Executed_Gtid_Set: 
                    Auto_Position: 0
             Replicate_Rewrite_DB: 
                     Channel_Name: 
               Master_TLS_Version: 
           Master_public_key_path: 
            Get_master_public_key: 0
    1 row in set (0.00 sec)

    从库报错数据不一致

    mysql> select count(*) from temp;
    +----------+
    | count(*) |
    +----------+
    |    24230 |
    +----------+
    1 row in set (0.01 sec)
    
    mysql> select id,count(*) from temp group by id having count(*)>=2;
    +------+----------+
    | id   | count(*) |
    +------+----------+
    | 1794 |        2 |
    | 1795 |        2 |
    | 1796 |        2 |
    | 1797 |        2 |
    | 1798 |        2 |
    | 1799 |        2 |
    | 1800 |        2 |

    正确方案

    正确方案
    主库操作
    mysqldump --all-databases --master-data=2 -u root -p  > dbdump.db
    mysqldump --all-databases --master-data=2 --single-transaction -u root -p >dbdump.db
    vi dbdump.db  
    CHANGE MASTER TO MASTER_LOG_FILE='binlog.000009', MASTER_LOG_POS=18811367;
    从库操作
    mysql> reset slave all;
    Query OK, 0 rows affected (0.01 sec)
    
    mysql> 
    mysql> 
    mysql> CHANGE MASTER TO
        -> MASTER_HOST='192.168.204.132',
        -> MASTER_PORT=3306,
        -> MASTER_USER='repl',
        -> MASTER_PASSWORD='mysql',
        -> MASTER_LOG_FILE='binlog.000009',
        -> MASTER_LOG_POS=18811367;
    Query OK, 0 rows affected, 2 warnings (0.05 sec)
    
    mysql> start slave;
    Query OK, 0 rows affected (0.04 sec)
    
    mysql> show slave status G;
    *************************** 1. row ***************************
                   Slave_IO_State: Waiting for master to send event
                      Master_Host: 192.168.204.132
                      Master_User: repl
                      Master_Port: 3306
                    Connect_Retry: 60
                  Master_Log_File: binlog.000009
              Read_Master_Log_Pos: 22964381
                   Relay_Log_File: centos7-relay-bin.000002
                    Relay_Log_Pos: 2073019
            Relay_Master_Log_File: binlog.000009
                 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: 20884067
                  Relay_Log_Space: 4153543
                  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: 429
    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
                      Master_UUID: e30d70f7-325a-11e9-811b-000c2940fa33
                 Master_Info_File: mysql.slave_master_info
                        SQL_Delay: 0
              SQL_Remaining_Delay: NULL
          Slave_SQL_Running_State: waiting for handler commit
               Master_Retry_Count: 86400
                      Master_Bind: 
          Last_IO_Error_Timestamp: 
         Last_SQL_Error_Timestamp: 
                   Master_SSL_Crl: 
               Master_SSL_Crlpath: 
               Retrieved_Gtid_Set: 
                Executed_Gtid_Set: 
                    Auto_Position: 0
             Replicate_Rewrite_DB: 
                     Channel_Name: 
               Master_TLS_Version: 
           Master_public_key_path: 
            Get_master_public_key: 0
    1 row in set (0.00 sec)
    
    ERROR: 
    No query specified
    mysql> show databases;
    +--------------------+
    | Database           |
    +--------------------+
    | A1                 |
    | A2                 |
    | A3                 |
    | course             |
    | course2            |
    | course3            |
    | course4            |
    | information_schema |
    | mysql              |
    | performance_schema |
    | sys                |
    +--------------------+
    11 rows in set (0.00 sec)
    
    mysql> use A1;
    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
    mysql> select count(*) from temp;
    +----------+
    | count(*) |
    +----------+
    |    20000 |
    +----------+
    1 row in set (0.01 sec)

    第二个从节点创建主从关系方法同上

    第二个从节点
    [root@oldboy-mysql-slave2 ~]# vi dbdump.db  #查看MASTER_LOG_FILE/MASTER_LOG_POS
    root@oldboy-mysql-slave2 ~]# mysql -u root -p
    Enter password: 
    Welcome to the MySQL monitor.  Commands end with ; or g.
    Your MySQL connection id is 9
    Server version: 8.0.13 MySQL Community Server - GPL
    
    Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
    
    Oracle is a registered trademark of Oracle Corporation and/or its
    affiliates. Other names may be trademarks of their respective
    owners.
    mysql> source dbdump.db
    
    mysql> CHANGE MASTER TO  
        -> MASTER_HOST='10.0.0.200',  
        -> MASTER_PORT=3306,  
        -> MASTER_USER='repl',  
        -> MASTER_PASSWORD='mysql',  
        -> MASTER_LOG_FILE='binlog.000043',  
        -> MASTER_LOG_POS=7570317;
    Query OK, 0 rows affected, 2 warnings (0.07 sec)
    
    mysql> start slave;
    Query OK, 0 rows affected (0.06 sec)
    mysql> show slave statusG
    *************************** 1. row ***************************
                   Slave_IO_State: Waiting for master to send event
                      Master_Host: 10.0.0.200
                      Master_User: repl
                      Master_Port: 3306
                    Connect_Retry: 60
                  Master_Log_File: binlog.000043
              Read_Master_Log_Pos: 11644030
                   Relay_Log_File: oldboy-mysql-slave2-relay-bin.000002
                    Relay_Log_Pos: 226135
            Relay_Master_Log_File: binlog.000043
                 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: 7796133
                  Relay_Log_Space: 4074254
                  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: 2947
    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
                      Master_UUID: fc58cc2d-164b-11e9-95af-000c29129a95
                 Master_Info_File: mysql.slave_master_info
                        SQL_Delay: 0
              SQL_Remaining_Delay: NULL
          Slave_SQL_Running_State: waiting for handler commit
               Master_Retry_Count: 86400
                      Master_Bind: 
          Last_IO_Error_Timestamp: 
         Last_SQL_Error_Timestamp: 
                   Master_SSL_Crl: 
               Master_SSL_Crlpath: 
               Retrieved_Gtid_Set: 
                Executed_Gtid_Set: 
                    Auto_Position: 0
             Replicate_Rewrite_DB: 
                     Channel_Name: 
               Master_TLS_Version: 
           Master_public_key_path: 
            Get_master_public_key: 0
    1 row in set (0.00 sec)

    方法2 利用从库1

    [root@oldboy-mysql-slave1 mysql]#/etc/init.d/mysql.server stop
    [root@oldboy-mysql-slave1 mysql]# tar -zcvf data.tar.gz data
    data/
    data/ibdata1
    data/ib_logfile1
    data/undo_001
    data/undo_002
    data/ib_logfile0
    [root@oldboy-mysql-slave1 mysql]# sftp root@10.0.0.202
    The authenticity of host '10.0.0.202 (10.0.0.202)' can't be established.
    ECDSA key fingerprint is SHA256:/yqIM0T3ZqFIt1SdWZb50q8qffjj7PbwKr+aLXFSw+4.
    ECDSA key fingerprint is MD5:6c:33:8b:79:5b:b3:a6:5e:78:b8:e6:4f:47:bf:0b:07.
    Are you sure you want to continue connecting (yes/no)? yes
    Warning: Permanently added '10.0.0.202' (ECDSA) to the list of known hosts.
    root@10.0.0.202's password: 
    Connected to 10.0.0.202.
    sftp> put data.tar.gz
    Uploading data.tar.gz to /root/data.tar.gz
    data.tar.gz                                                          100%  285MB  27.7MB/s   00:10    
    sftp> exit
    二从节点
    [root@oldboy-mysql-slave2 mysql]# tar -zxvf data.tar.gz 
    data/
    data/ibdata1
    data/ib_logfile1
    data/undo_001
    data/undo_002
    data/ib_logfile0
    data/#innodb_temp/
    data/mysql.ibd
    [root@oldboy-mysql-slave2 data]# rm -rf auto.cnf 
    [root@oldboy-mysql-slave2 data]# /etc/init.d/mysql.server start
    Starting MySQL.Logging to '/data/mysql/mysql/data/oldboy-mysql-slave2.err'.
    .... SUCCESS! 
    [root@oldboy-mysql-slave2 data]# mysql -u root -p
    Enter password: 
    Welcome to the MySQL monitor.  Commands end with ; or g.
    Your MySQL connection id is 8
    Server version: 8.0.13 MySQL Community Server - GPL
    
    Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
    
    Oracle is a registered trademark of Oracle Corporation and/or its
    affiliates. Other names may be trademarks of their respective
    owners.
    
    Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.
    
    mysql> 
    mysql> show slave statusG
    *************************** 1. row ***************************
                   Slave_IO_State: 
                      Master_Host: 10.0.0.200
                      Master_User: repl
                      Master_Port: 3306
                    Connect_Retry: 60
                  Master_Log_File: binlog.000043
              Read_Master_Log_Pos: 11644030
                   Relay_Log_File: oldboy-mysql-slave1-relay-bin.000002
                    Relay_Log_Pos: 4074032
            Relay_Master_Log_File: binlog.000043
                 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: 11644030
                  Relay_Log_Space: 0
                  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
                      Master_UUID: fc58cc2d-164b-11e9-95af-000c29129a95
                 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: 
                   Master_SSL_Crl: 
               Master_SSL_Crlpath: 
               Retrieved_Gtid_Set: 
                Executed_Gtid_Set: 
                    Auto_Position: 0
             Replicate_Rewrite_DB: 
                     Channel_Name: 
               Master_TLS_Version: 
           Master_public_key_path: 
            Get_master_public_key: 0
    1 row in set (0.00 sec)
    我在这里就已经成功了

    联级复制

    从库打开log_slave_updates,主库修改数据会记录到从库的binlog中,用于从库之间做联级复制
    mysql> show variables like "%log_slave_updates%";
    +-------------------+-------+
    | Variable_name     | Value |
    +-------------------+-------+
    | log_slave_updates | ON    |
    +-------------------+-------+
    1 row in set (0.12 sec)
    
    mysql> show variables like "%log_bin%";
    +---------------------------------+------------------------------------+
    | Variable_name                   | Value                              |
    +---------------------------------+------------------------------------+
    | log_bin                         | ON                                 |
    | log_bin_basename                | /usr/local/mysql/data/binlog       |
    | log_bin_index                   | /usr/local/mysql/data/binlog.index |
    | log_bin_trust_function_creators | OFF                                |
    | log_bin_use_v1_row_events       | OFF                                |
    | sql_log_bin                     | ON                                 |
    +---------------------------------+------------------------------------+
    6 rows in set (0.00 sec)
    
    [root@mysql-slave1 data]# mysqlbinlog -v binlog.000007 > abc.log
    ### INSERT INTO `test`.`dept2`
    ### SET
    ###   @1=1
    ###   @2='math'
    # at 895587
    #190401 22:58:11 server id 1  end_log_pos 895618 CRC32 0xeff2d5c4       Xid = 531
    COMMIT/*!*/;
    SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
    DELIMITER ;
    # End of log file
    /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
    /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
    
    [root@mysql-slave1 data]# cat /etc/my.cnf
    [mysqld]
    server-id=2
    log_slave_updates=1
    replicate-do-db=test

    replicate-do-db:该参数用来指定需要复制的数据库。在基于语句复制的环境中,指定该参数之后,则slave的SQL thread进程只会应用在本数据库下的对象相关的语句。如果有多个数据库需要复制,则这

    个参数要使用多次。但如果是涉及到跨库操作语句,则复制会丢失;

    [root@mysql-slave1 data]# /etc/init.d/mysql.server restart
    Shutting down MySQL.. SUCCESS! 
    Starting MySQL. SUCCESS! 
    mysql> show slave statusG;
    *************************** 1. row ***************************
                   Slave_IO_State: Waiting for master to send event
                      Master_Host: 192.168.138.131
                      Master_User: repl
                      Master_Port: 3306
                    Connect_Retry: 60
                  Master_Log_File: binlog.000007
              Read_Master_Log_Pos: 1490
                   Relay_Log_File: mysql-slave1-relay-bin.000006
                    Relay_Log_Pos: 319
            Relay_Master_Log_File: binlog.000007
                 Slave_IO_Running: Yes
                Slave_SQL_Running: Yes
                  Replicate_Do_DB: test
              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: 1490
                  Relay_Log_Space: 534
                  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
                      Master_UUID: 849ecaa5-3386-11e9-9db6-000c29c16499
                 Master_Info_File: mysql.slave_master_info
                        SQL_Delay: 0
              SQL_Remaining_Delay: NULL
          Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
               Master_Retry_Count: 86400
                      Master_Bind: 
          Last_IO_Error_Timestamp: 
         Last_SQL_Error_Timestamp: 
                   Master_SSL_Crl: 
               Master_SSL_Crlpath: 
               Retrieved_Gtid_Set: 
                Executed_Gtid_Set: 
                    Auto_Position: 0
             Replicate_Rewrite_DB: 
                     Channel_Name: 
               Master_TLS_Version: 
           Master_public_key_path: 
            Get_master_public_key: 0
    1 row in set (0.00 sec)
    
    ERROR: 
    No query specified
    
    主库
    mysql> use test;
    Database changed
    mysql> insert into dept2 values(3,'chinese');
    Query OK, 1 row affected (0.07 sec)
    
    mysql> use course;
    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
    mysql> select * from test;
    +------+-------+
    | id   | name  |
    +------+-------+
    |    1 | houpj |
    +------+-------+
    1 row in set (0.00 sec)
    
    mysql> insert into test values(2,'wang');
    Query OK, 1 row affected (0.07 sec)
    
    从库 只有test库更新
    mysql> use test;
    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
    mysql> select * from dept2;
    +------+---------+
    | id   | name    |
    +------+---------+
    |    1 | history |
    |    1 | math    |
    |    3 | chinese |
    +------+---------+
    3 rows in set (0.00 sec)
    
    mysql> use course;
    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
    mysql> select * from test;
    +------+-------+
    | id   | name  |
    +------+-------+
    |    1 | houpj |
    +------+-------+
    1 row in set (0.00 sec)

    另一个基于SQL语句复制和基于行复制的区别在于当语句中包含对多个数据库的表进行
    操作时。比如设置replicate-do-db=db1,
    USE db1;
    UPDATE db1.table1 SET col1 = 10, db2.table2 SET col2 = 20;
    基于SQL语句的复制会将table1和table2都在备库修改,而基于行的复制只会在备库修改table1表

    主库修改
    [root@mysql ~]# cat /etc/my.cnf
    [mysqld]
    server-id=1
    binlog-format=statement
    
    mysql> use test;
    Database changed
    mysql> update course.test2 set name='jiaojiao';
    Query OK, 2 rows affected (0.03 sec)
    Rows matched: 2  Changed: 2  Warnings: 0
    
    mysql> use course;
    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
    mysql> update test.dept2 set name='pengju';
    Query OK, 3 rows affected (0.00 sec)
    Rows matched: 3  Changed: 3  Warnings: 0
    从库1
    [root@mysql-slave1 ~]# cat /etc/my.cnf
    [mysqld]
    server-id=2
    log_slave_updates=1
    replicate-do-db=test
    
    mysql> use test;
    Database changed
    mysql> select * from dept2;
    +------+-------+
    | id   | name  |
    +------+-------+
    |    1 | houpj |
    |    1 | houpj |
    |    3 | houpj |
    +------+-------+
    3 rows in set (0.00 sec)
    
    mysql> use course;
    Reading table information for completion of table and column names
    You can turn off this feature to get a quicker startup with -A
    mysql> select * from test2;
    +----+----------+
    | id | name     |
    +----+----------+
    |  1 | jiaojiao |
    |  2 | jiaojiao |
    +----+----------+
    2 rows in set (0.00 sec)

    replicate-do-table=db_name.tbl_name:通过该参数告知slave的SQL thread仅复制指定表上的数据。如果有多个表,则该参数要使用多次replicate-wild-do-table=db_name.tbl_name:通过该参数告知SQL的SQL thread仅复制符合匹配的表,可以使用_和%作为通配符。比如replicate-wild-do-table=foo%.bar%表示复制以foo打头的数据库下所有bar打头的表数据。如果是replicate-wild-do-table=foo%.%,则表示即复制foo打头的所有表的数据,也复制create/drop/alter database foo打头的命令

    主库
    mysql> use test;
    Database changed
    mysql> show tables;
    +----------------+
    | Tables_in_test |
    +----------------+
    | course         |
    | dept           |
    | dept2          |
    | dept3          |
    | students       |
    | students2      |
    | teacher        |
    | teacher_backup |
    +----------------+
    8 rows in set (0.00 sec)
    
    mysql> insert into dept3 values(1,'english'),(2,'math'),(3,'miniso');
    Query OK, 3 rows affected (0.10 sec)
    Records: 3  Duplicates: 0  Warnings: 0
    
    mysql> select * from dept2;
    +------+-------+
    | id   | name  |
    +------+-------+
    |    1 | houpj |
    |    1 | houpj |
    |    3 | houpj |
    +------+-------+
    3 rows in set (0.00 sec)
    
    mysql> insert into dept2 values(1,'english'),(2,'math'),(3,'miniso');
    Query OK, 3 rows affected (0.04 sec)
    Records: 3  Duplicates: 0  Warnings: 0
    从库1
    [root@mysql-slave1 mysql]# cat /etc/my.cnf
    [mysqld]
    server-id=2
    log_slave_updates=1
    replicate-do-table=test.dept3
    
    
    
    mysql> use test;
    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
    mysql> show tables;
    +----------------+
    | Tables_in_test |
    +----------------+
    | course         |
    | dept           |
    | dept2          |
    | dept3          |
    | students       |
    | students2      |
    | teacher        |
    | teacher_backup |
    +----------------+
    8 rows in set (0.00 sec)
    没变化
    mysql> select * from dept2;
    +------+-------+
    | id   | name  |
    +------+-------+
    |    1 | houpj |
    |    1 | houpj |
    |    3 | houpj |
    +------+-------+
    3 rows in set (0.00 sec)
    有变化
    mysql> select * from dept3;
    +------+---------+
    | id   | name    |
    +------+---------+
    |    1 | english |
    |    2 | math    |
    |    3 | miniso  |
    +------+---------+
    3 rows in set (0.00 sec)

    show processlist

    在主库可以通过执行show processlist命令查看主库的bin log日志生成进程
    mysql> show processlistG;
    *************************** 1. row ***************************
         Id: 4
       User: event_scheduler
       Host: localhost
         db: NULL
    Command: Daemon
       Time: 4882
      State: Waiting on empty queue
       Info: NULL
    *************************** 2. row ***************************
         Id: 14
       User: root
       Host: localhost
         db: test
    Command: Query
       Time: 0
      State: starting
       Info: show processlist
    *************************** 3. row ***************************
         Id: 17
       User: repl
       Host: 192.168.138.133:49137
         db: NULL
    Command: Binlog Dump
       Time: 4653
      State: Master has sent all binlog to slave; waiting for more updates
       Info: NULL
    *************************** 4. row ***************************
         Id: 21
       User: repl
       Host: 192.168.138.132:43994
         db: NULL
    Command: Binlog Dump
       Time: 902
      State: Master has sent all binlog to slave; waiting for more updates
       Info: NULL
    4 rows in set (0.00 sec)

    在statement(sql)模式下实验主从一个表数据不一致的情况下复制是否还能继续

    从库操作
    mysql> delete from dept3;
    Query OK, 3 rows affected (0.10 sec)
    
    主库操作
    mysql> select * from dept3;
    +------+---------+
    | id   | name    |
    +------+---------+
    |    1 | english |
    |    2 | math    |
    |    3 | miniso  |
    +------+---------+
    3 rows in set (0.00 sec)
    
    mysql> insert into dept3 values(4,'history');
    Query OK, 1 row affected (0.06 sec)
    
    从库状态
    mysql> show slave statusG;
    *************************** 1. row ***************************
                   Slave_IO_State: Waiting for master to send event
                      Master_Host: 192.168.138.131
                      Master_User: repl
                      Master_Port: 3306
                    Connect_Retry: 60
                  Master_Log_File: binlog.000008
              Read_Master_Log_Pos: 2952
                   Relay_Log_File: mysql-slave1-relay-bin.000016
                    Relay_Log_Pos: 625
            Relay_Master_Log_File: binlog.000008
                 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: 2952
                  Relay_Log_Space: 1837
                  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
                      Master_UUID: 849ecaa5-3386-11e9-9db6-000c29c16499
                 Master_Info_File: mysql.slave_master_info
                        SQL_Delay: 0
              SQL_Remaining_Delay: NULL
          Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
               Master_Retry_Count: 86400
                      Master_Bind: 
          Last_IO_Error_Timestamp: 
         Last_SQL_Error_Timestamp: 
                   Master_SSL_Crl: 
               Master_SSL_Crlpath: 
               Retrieved_Gtid_Set: 
                Executed_Gtid_Set: 
                    Auto_Position: 0
             Replicate_Rewrite_DB: 
                     Channel_Name: 
               Master_TLS_Version: 
           Master_public_key_path: 
            Get_master_public_key: 0
    1 row in set (0.00 sec)

    row模式

    从库
    mysql> select * from dept3;
    +------+---------+
    | id   | name    |
    +------+---------+
    |    4 | history |
    +------+---------+
    1 row in set (0.00 sec)
    
    
    主库操作
    
    mysql> select * from dept3;
    +------+---------+
    | id   | name    |
    +------+---------+
    |    1 | english |
    |    2 | math    |
    |    3 | miniso  |
    |    4 | history |
    +------+---------+
    4 rows in set (0.00 sec)
    
    mysql> delete from dept3 where id=1;
    Query OK, 1 row affected (0.09 sec)
    从库状态
    mysql> show slave status G;
    *************************** 1. row ***************************
                   Slave_IO_State: Waiting for master to send event
                      Master_Host: 192.168.138.131
                      Master_User: repl
                      Master_Port: 3306
                    Connect_Retry: 60
                  Master_Log_File: binlog.000009
              Read_Master_Log_Pos: 445
                   Relay_Log_File: mysql-slave1-relay-bin.000019
                    Relay_Log_Pos: 363
            Relay_Master_Log_File: binlog.000009
                 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: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 5 failed executing transaction 'ANONYMOUS' at master log binlog.000009, end_log_pos 414. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any.
                     Skip_Counter: 0
              Exec_Master_Log_Pos: 155
                  Relay_Log_Space: 1032
                  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: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 5 failed executing transaction 'ANONYMOUS' at master log binlog.000009, end_log_pos 414. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any.
      Replicate_Ignore_Server_Ids: 
                 Master_Server_Id: 1
                      Master_UUID: 849ecaa5-3386-11e9-9db6-000c29c16499
                 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: 190402 05:23:51
                   Master_SSL_Crl: 
               Master_SSL_Crlpath: 
               Retrieved_Gtid_Set: 
                Executed_Gtid_Set: 
                    Auto_Position: 0
             Replicate_Rewrite_DB: 
                     Channel_Name: 
               Master_TLS_Version: 
           Master_public_key_path: 
            Get_master_public_key: 0
    1 row in set (0.00 sec)

    slave-skip-errors=[err_code1,err_code2,...|all|ddl_exist_errors]:该参数决定了当slave的SQL thread执行过程中碰到何种错误时可以忽略并继续接下来的数据复制。正常情况下当有错误发生时,复制会停止而需要人工干预修复才能继续进行。除非非常自信可以忽略某些错误,否则不要使用这个参数,不然会导致虽然复制执行正常,但其实内部的数据已经完全不一致;

    跳过错误
    [root@mysql-slave1 ~]# cat /etc/my.cnf
    [mysqld]
    server-id=2
    log_slave_updates=1
    slave-parallel-workers=5
    slave-skip-errors=1050
    重启mysql
    mysql> show slave statusG;
    *************************** 1. row ***************************
                   Slave_IO_State: Waiting for master to send event
                      Master_Host: 192.168.138.131
                      Master_User: repl
                      Master_Port: 3306
                    Connect_Retry: 60
                  Master_Log_File: binlog.000009
              Read_Master_Log_Pos: 782
                   Relay_Log_File: mysql-slave1-relay-bin.000006
                    Relay_Log_Pos: 319
            Relay_Master_Log_File: binlog.000009
                 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: 782
                  Relay_Log_Space: 1547
                  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
                      Master_UUID: 849ecaa5-3386-11e9-9db6-000c29c16499
                 Master_Info_File: mysql.slave_master_info
                        SQL_Delay: 0
              SQL_Remaining_Delay: NULL
          Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
               Master_Retry_Count: 86400
                      Master_Bind: 
          Last_IO_Error_Timestamp: 
         Last_SQL_Error_Timestamp: 
                   Master_SSL_Crl: 
               Master_SSL_Crlpath: 
               Retrieved_Gtid_Set: 
                Executed_Gtid_Set: 
                    Auto_Position: 0
             Replicate_Rewrite_DB: 
                     Channel_Name: 
               Master_TLS_Version: 
           Master_public_key_path: 
            Get_master_public_key: 0
    1 row in set (0.00 sec)

    默认创建的MySQL复制是异步的,意味着主库将数据库修改事件写入到自己的bin log,而并不知道从库是否获取了这些事件并应用在自己身上。所以当主库崩溃导致要主从切换时,有可能从库上的数据不是最新的
    从5.7版本开始MySQL通过扩展的方式支持了半同步复制当主库执行一个更新操作事务时,提交操作会被阻止直到至少有一个半同步的复制slave确认已经接收到本次更新操作,主库的提交操作才会继续
    半同步复制的slave发送确认消息只会在本次更新操作记录已经记录到本地的relay log之后如果没有任何slave发送确认消息而导致超时时,半同步复制会转换成异步复制
    半同步复制会对MySQL性能产生影响,因为主库的提交动作只有在收到至少一个从库的确认消息之后才能执行。但这个功能是性能和数据可靠性方面的权衡

    主库master
    mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
    Query OK, 0 rows affected (0.19 sec)
    
    mysql> SET GLOBAL rpl_semi_sync_master_enabled = 1;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> SHOW STATUS LIKE 'rpl_semi_sync%';
    +--------------------------------------------+-------+
    | Variable_name                              | Value |
    +--------------------------------------------+-------+
    | Rpl_semi_sync_master_clients               | 1     |
    | Rpl_semi_sync_master_net_avg_wait_time     | 0     |
    | Rpl_semi_sync_master_net_wait_time         | 0     |
    | Rpl_semi_sync_master_net_waits             | 0     |
    | Rpl_semi_sync_master_no_times              | 0     |
    | Rpl_semi_sync_master_no_tx                 | 0     |
    | Rpl_semi_sync_master_status                | ON    |
    | Rpl_semi_sync_master_timefunc_failures     | 0     |
    | Rpl_semi_sync_master_tx_avg_wait_time      | 0     |
    | Rpl_semi_sync_master_tx_wait_time          | 0     |
    | Rpl_semi_sync_master_tx_waits              | 0     |
    | Rpl_semi_sync_master_wait_pos_backtraverse | 0     |
    | Rpl_semi_sync_master_wait_sessions         | 0     |
    | Rpl_semi_sync_master_yes_tx                | 0     |
    +--------------------------------------------+-------+
    14 rows in set (0.01 sec)
    从库
    mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
    Query OK, 0 rows affected (0.25 sec)
    
    mysql> SET GLOBAL rpl_semi_sync_slave_enabled =1;
    Query OK, 0 rows affected (0.01 sec)
    
    mysql> stop slave;
    Query OK, 0 rows affected (0.11 sec)
    
    mysql> start slave;
    Query OK, 0 rows affected (0.05 sec)
    
    从库
    mysql>  STOP SLAVE IO_THREAD;
    Query OK, 0 rows affected (0.09 sec)
    
    
    主库
    mysql> update dept2 set name="haohan" where id=3;
    Query OK, 2 rows affected (10.03 sec)
    Rows matched: 2  Changed: 2  Warnings: 0
    mysql> show status like 'rpl_semi_sync%';
    +--------------------------------------------+-------+
    | Variable_name                              | Value |
    +--------------------------------------------+-------+
    | Rpl_semi_sync_master_clients               | 0     |
    | Rpl_semi_sync_master_net_avg_wait_time     | 0     |
    | Rpl_semi_sync_master_net_wait_time         | 0     |
    | Rpl_semi_sync_master_net_waits             | 0     |
    | Rpl_semi_sync_master_no_times              | 1     |
    | Rpl_semi_sync_master_no_tx                 | 1     |
    | Rpl_semi_sync_master_status                | OFF   |
    | Rpl_semi_sync_master_timefunc_failures     | 0     |
    | Rpl_semi_sync_master_tx_avg_wait_time      | 0     |
    | Rpl_semi_sync_master_tx_wait_time          | 0     |
    | Rpl_semi_sync_master_tx_waits              | 0     |
    | Rpl_semi_sync_master_wait_pos_backtraverse | 0     |
    | Rpl_semi_sync_master_wait_sessions         | 0     |
    | Rpl_semi_sync_master_yes_tx                | 0     |
    +--------------------------------------------+-------+
    14 rows in set (0.03 sec)
    从库
    mysql>  START SLAVE IO_THREAD;
    Query OK, 0 rows affected (0.03 sec)
    主库
    mysql> show status like 'rpl_semi_sync%';
    +--------------------------------------------+-------+
    | Variable_name                              | Value |
    +--------------------------------------------+-------+
    | Rpl_semi_sync_master_clients               | 1     |
    | Rpl_semi_sync_master_net_avg_wait_time     | 0     |
    | Rpl_semi_sync_master_net_wait_time         | 0     |
    | Rpl_semi_sync_master_net_waits             | 1     |
    | Rpl_semi_sync_master_no_times              | 1     |
    | Rpl_semi_sync_master_no_tx                 | 1     |
    | Rpl_semi_sync_master_status                | ON    |
    | Rpl_semi_sync_master_timefunc_failures     | 0     |
    | Rpl_semi_sync_master_tx_avg_wait_time      | 0     |
    | Rpl_semi_sync_master_tx_wait_time          | 0     |
    | Rpl_semi_sync_master_tx_waits              | 0     |
    | Rpl_semi_sync_master_wait_pos_backtraverse | 0     |
    | Rpl_semi_sync_master_wait_sessions         | 0     |
    | Rpl_semi_sync_master_yes_tx                | 0     |
    +--------------------------------------------+-------+
    14 rows in set (0.00 sec)
    从库2
    mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
    Query OK, 0 rows affected (0.19 sec)
    
    mysql> SET GLOBAL rpl_semi_sync_slave_enabled =1;
    Query OK, 0 rows affected (0.01 sec)
    
    mysql> stop slave;
    Query OK, 0 rows affected (0.05 sec)
    
    mysql> start slave;
    Query OK, 0 rows affected (0.05 sec)
    
    mysql> show status like 'rpl_semi_sync%';
    +--------------------------------------------+-------+
    | Variable_name                              | Value |
    +--------------------------------------------+-------+
    | Rpl_semi_sync_master_clients               | 2     |
    | Rpl_semi_sync_master_net_avg_wait_time     | 0     |
    | Rpl_semi_sync_master_net_wait_time         | 0     |
    | Rpl_semi_sync_master_net_waits             | 1     |
    | Rpl_semi_sync_master_no_times              | 1     |
    | Rpl_semi_sync_master_no_tx                 | 1     |
    | Rpl_semi_sync_master_status                | ON    |
    | Rpl_semi_sync_master_timefunc_failures     | 0     |
    | Rpl_semi_sync_master_tx_avg_wait_time      | 0     |
    | Rpl_semi_sync_master_tx_wait_time          | 0     |
    | Rpl_semi_sync_master_tx_waits              | 0     |
    | Rpl_semi_sync_master_wait_pos_backtraverse | 0     |
    | Rpl_semi_sync_master_wait_sessions         | 0     |
    | Rpl_semi_sync_master_yes_tx                | 0     |
    +--------------------------------------------+-------+
    14 rows in set (0.00 sec)
    
    两个从库
    slave1
    mysql> stop slave io_thread;
    Query OK, 0 rows affected (0.00 sec)
    master 
    mysql> insert into test2 values(3,'houhou');
    Query OK, 1 row affected (0.09 sec)
    slave2
    mysql> stop slave io_thread;
    Query OK, 0 rows affected (0.01 sec)
    master
    mysql> insert into test2 values(4,'qita');
    Query OK, 1 row affected (10.16 sec)

    延迟复制是指定从库对主库的延迟至少是指定的这个间隔时间,默认是0秒。可以通过change master to命令来指定
    CHANGE MASTER TO MASTER_DELAY = N;其原理是从库收到主库的bin log之后,不是立即执行,而是等待指定的秒数之后再执行
    延迟复制的使用场景比如:
    确保在主库上被错误修改的数据能及时找回测试在从库IO集中在恢复bin log过程中对应用程序的访问影响保留一份若干天前的数据库状态,和当前状态可以做对比
    show slave status中SQL_Delay值表明了设置的延迟时长

    延迟复制
    slave1
    mysql> stop slave;
    Query OK, 0 rows affected (0.09 sec)
    
    mysql> CHANGE MASTER TO MASTER_DELAY = 60;
    Query OK, 0 rows affected (0.07 sec)
    
    mysql> start slave;
    Query OK, 0 rows affected (0.10 sec)
    
    mysql> show slave status G;
    *************************** 1. row ***************************
                   Slave_IO_State: Waiting for master to send event
                      Master_Host: 192.168.138.131
                      Master_User: repl
                      Master_Port: 3306
                    Connect_Retry: 60
                  Master_Log_File: binlog.000009
              Read_Master_Log_Pos: 1366
                   Relay_Log_File: mysql-slave2-relay-bin.000002
                    Relay_Log_Pos: 319
            Relay_Master_Log_File: binlog.000009
                 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: 1366
                  Relay_Log_Space: 534
                  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
                      Master_UUID: 849ecaa5-3386-11e9-9db6-000c29c16499
                 Master_Info_File: mysql.slave_master_info
                        SQL_Delay: 60
              SQL_Remaining_Delay: NULL
          Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
               Master_Retry_Count: 86400
                      Master_Bind: 
          Last_IO_Error_Timestamp: 
         Last_SQL_Error_Timestamp: 
                   Master_SSL_Crl: 
               Master_SSL_Crlpath: 
               Retrieved_Gtid_Set: 
                Executed_Gtid_Set: 
                    Auto_Position: 0
             Replicate_Rewrite_DB: 
                     Channel_Name: 
               Master_TLS_Version: 
           Master_public_key_path: 
            Get_master_public_key: 0
    1 row in set (0.00 sec)
    
    ERROR: 
    No query specified
    master
    mysql> show master status;
    +---------------+----------+--------------+------------------+-------------------+
    | File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
    +---------------+----------+--------------+------------------+-------------------+
    | binlog.000009 |     1366 |              |                  |                   |
    +---------------+----------+--------------+------------------+-------------------+
    1 row in set (0.00 sec)
    mysql> insert into teacher_backup values(4,'abc',5);
    Query OK, 1 row affected (0.00 sec)
    
    mysql> show master status;
    +---------------+----------+--------------+------------------+-------------------+
    | File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
    +---------------+----------+--------------+------------------+-------------------+
    | binlog.000009 |     1667 |              |                  |                   |
    +---------------+----------+--------------+------------------+-------------------+
    1 row in set (0.00 sec)
    从库
    mysql> select * from teacher_backup;
    +----+------+---------+
    | id | name | dept_id |
    +----+------+---------+
    |  1 | abc  |       1 |
    |  2 | abc  |       4 |
    |  3 | abc  |       4 |
    +----+------+---------+
    3 rows in set (0.01 sec)
    60s后
    mysql> select * from teacher_backup;
    +----+------+---------+
    | id | name | dept_id |
    +----+------+---------+
    |  1 | abc  |       1 |
    |  2 | abc  |       4 |
    |  3 | abc  |       4 |
    |  4 | abc  |       5 |
    +----+------+---------+
    4 rows in set (0.00 sec)

    未完待续

  • 相关阅读:
    人工大脑项目 —— Nengo
    四种聚类方法之比较
    对淘宝一些规则的一些研究分享
    【转】千万不要在夏季开发苹果应用,否则后果很严重
    Windows2003上使用IIS7 Express使用FastCgi运行php
    傅立叶变换最直白最容易理解最直接最真实最有深度的解释
    音视频技术应用(18) 控制播放进度——av_seek_frame()
    MySQL 中如何定位 DDL 被阻塞的问题
    windows 编译版本异常处理
    R语言的前世今生
  • 原文地址:https://www.cnblogs.com/Honeycomb/p/10645196.html
Copyright © 2020-2023  润新知