1. 主从复制简单构建

1. 搭建主从复制前要注意

• 主库需要要开启二进制日志(log_bin)，主从的server_id和server_uuid要不一样
• 重要的参数要开启(gtid_mode、enforce_gtid_consistency、log_slave_updates)

2.简单搭建主从

• 主库上创建复制用户

mysql> CREATE USER 'repl'@'%.example.com' IDENTIFIED BY 'password';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%.example.com';

• 从库上执行

# 基于GTID(推荐)   
change master to master_host='xxx',master_user='repl',master_password='xxx' ,MASTER_PORT=3306,MASTER_AUTO_POSITION=1;
# 基于位置点
change master to CHANGE MASTER TO MASTER_HOST='xxx',MASTER_USER='repl',MASTER_PASSWORD='xxx',MASTER_PORT=3306,MASTER_LOG_FILE='xxx',MASTER_LOG_POS=xxx,MASTER_CONNECT_RETRY=10;

• 开启复制： start slave
• 查看复制状态：  show slave status\G;

3.监控主从复制状态

• show slave status\G;

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.211.55.230
                  Master_User: root
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: bin.000009         # The name of the source binary log file from which the I/O thread is currently reading.
          Read_Master_Log_Pos: 196                # The position in the current source binary log file up to which the I/O thread has read.
               Relay_Log_File: relay.000015
                Relay_Log_Pos: 359
        Relay_Master_Log_File: bin.000009         # The name of the source binary log file containing the most recent event executed by the SQL thread.
             Slave_IO_Running: Yes                 #这里有两个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: 196               # The position in the current source binary log file to which the SQL thread has read and executed
              Relay_Log_Space: 558
              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: 55230
                  Master_UUID: b517a57b-c07e-11ed-80e0-001c42402fec
             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: 58911c0e-9031-11ed-b31c-001c4294d606:1,
b517a57b-c07e-11ed-80e0-001c42402fec:1-9
                Auto_Position: 1
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
       Master_public_key_path: 
        Get_master_public_key: 0
            Network_Namespace: 
1 row in set, 1 warning (0.01 sec)

• Exec_Master_Log_Pos

The position in the current source binary log file to which the SQL thread has read and executed, marking the start of the next transaction or event to be processed. You can use this value with the CHANGE MASTER TO statement's MASTER_LOG_POS option when starting a new replica from an existing replica, so that the new replica reads from this point. The coordinates given by (Relay_Master_Log_File, Exec_Master_Log_Pos) in the source's binary log correspond to the coordinates given by (Relay_Log_File, Relay_Log_Pos) in the relay log.

• Retrieved_Gtid_Set

  The set of global transaction IDs corresponding to all transactions received by this replica. Empty if GTIDs are not in use. See GTID Sets for more information.

  This is the set of all GTIDs that exist or have existed in the relay logs. Each GTID is added as soon as the Gtid_log_event is received. This can cause partially transmitted transactions to have their GTIDs included in the set.

  When all relay logs are lost due to executing RESET SLAVE or CHANGE MASTER TO, or due to the effects of the --relay-log-recovery option, the set is cleared. When relay_log_purge = 1, the newest relay log is always kept, and the set is not cleared.

• Executed_Gtid_Set

  The set of global transaction IDs written in the binary log. This is the same as the value for the global gtid_executed system variable on this server, as well as the value for Executed_Gtid_Set in the output of SHOW MASTER STATUS on this server. Empty if GTIDs are not in use. See GTID Sets for more information.

参考：https://dev.mysql.com/doc/refman/5.7/en/show-slave-status.html