MySQL 5.7—主从复制

简单的主从复制配置
1、主服务器，启用二进制日志记录，配置服务器ID
vi /etc/my.cnf

[mysqld]
#如果不配置server-id或设置为0，则表示拒绝接受主从同步的请求
server-id=1
#复制设置时尽可能提高持久性和一致性
#innodb_flush_log_at_trx_commit = 1
#sync_binlog = 1

#--------------mysql replication master ------------------------------
symbolic-links=0
skip_name_resolve = 1

log-bin = /usr/local/mysql/logs/mysql-bin.log
expire-logs-days = 14
max-binlog-size = 500M
#mixed、row
binlog_format = row
#binlog-do-db=
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
binlog-ignore-db=sys

master_info_repository = TABLE
relay_log_info_repository = TABLE
relay_log = /usr/local/mysql/logs/mysql-relay.log
relay_log_recovery = 1
binlog_gtid_simple_recovery = 1

loose_rpl_semi_sync_master_enabled = 1
loose_rpl_semi_sync_slave_enabled = 1
loose_rpl_semi_sync_master_timeout = 5000

保存配置，然后重启MySQL。

2、主服务器，创建复制用户

CREATE USER 'repl_user'@'172.17.%' IDENTIFIED BY 'test123';
GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.*  TO  'repl_user'@'172.17.%';
flush privileges;

3、使用XtraBackup先将主库全备份，并恢复到从库

参考文章：使用XtraBackup做MySQL同步

4、配置从库

[mysqld]
#一定不能与主库的server-id相同
server-id=20

#---------mysql replication slave---------------------------
log-slave-updates=0
##binlog-do-db=
replicate-ignore-db=mysql
replicate-ignore-db=information_schema
replicate-ignore-db=performance_schema
replicate-ignore-db=sys
slave-skip-errors=all
slave-net-timeout=60

保存配置，然后重启MySQL。

5、设置主从同步

1、启动master

# 查看master是否启动bin log
 show variables like 'log_bin';
 #查看master的同步起点bin log文件和position
 show master status;
 #假如bin log文件是mysql-bin.000009
 #假如position是154

2、根据master 的同步起点bin log文件和position设置从库

先停止slave

stop slave;

配置

CHANGE MASTER TO MASTER_HOST='mastr ip',
MASTER_PORT=53806,
MASTER_USER='repl_user',
MASTER_PASSWORD='test123',
MASTER_LOG_FILE='mysql-bin.000009',
MASTER_LOG_POS=154;

重启slave

start slave;

3、查看同步是否成功

执行命令

show slave  status\G;

查看结果，没有error表示成功。

*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: master ip
                  Master_User: repl_user
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000009
          Read_Master_Log_Pos: 154
               Relay_Log_File: iZ2zefa0pteda8m4scnmghZ-relay-bin.000003
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000009
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: mysql,information_schema,performance_schema,sys
           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: 154
              Relay_Log_Space: 545
              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: d0540855-114d-11ec-8ff8-00163e0e1a86
             Master_Info_File: /usr/local/mysql/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: