Mysql-主从复制

一、环境准备

 

 

可以一主一从、多主多从、一主多从。下面以一主一从为例

1.master配置

[mysqld]加入

server-id=1
log-bin=mysql-bin

2.master 添加 同步用户

grant replication slave on *.* to 'tongbu'@'%' identified by '123456';
show master status;

3.slave配置

[mysqld]加入

server-id=2

4.slave 修改master配置

change master to
master_host='IP地址',
master_user='tongbu',
master_password='123456',
master_log_file='mysql-bin.000001',
master_log_pos=971;

 

二、启动slave

start slave;

show slave status\G;

 

出现下面两个yes，即表示连接正常。

Slave_IO_Running:Yes

Slave_SQL_Running:Yes

 

三、参数解析

show slave status\G;

show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 
                  Master_User: tongbu
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 971
               Relay_Log_File: aliyun-relay-bin.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000001
             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: 971
              Relay_Log_Space: 528
              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: afe3091f-b7dc-11ec-86a3-00163e19499e
             Master_Info_File: /app/mantis/data/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: 
1 row in set (0.00 sec)

ERROR: 
No query specified

 

Slave_IO_State            : I/O线程连接master状态

Master_User                 : 用于连接master的用户

Master_Port                  : master端监听端口

Connect_Retry              :  主从连接失败，重试时间间隔。

Master_Log_File          ：I/O线程读取的master二进制日志文件的名称

Read_Master_Log_Pos        : I/O线程已读取的master二进制日志文件的位置。

Relay_Log_FIile             : SQL线程读取和执行的中继日志文件的名称。

Relay_Log_Pos              : SQL线程已读取和执行的中继日志文件的位置

Relay_Master_Log_File : SQL线程执行的master二进制日志文件的名称。

Slave_IO_Running         : I/O线程是否被启动并成功得连接到主服务器上

 Slave_SQL_Running     : SQL线程是否被启动。

Relicate_Do_DB             :指定的同步的数据库列表。

 

四、主从同步错误排错

show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 
                  Master_User: tongbu
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 1623
               Relay_Log_File: aliyun-relay-bin.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000001
             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: 1049
                   Last_Error: Error 'Unknown database 'test'' on query. Default database: 'test'. Query: 'create table student(
id int,
name varchar(20),
age int,
address varchar(20)
)'
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 971
              Relay_Log_Space: 1180
              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: 1049
               Last_SQL_Error: Error 'Unknown database 'test'' on query. Default database: 'test'. Query: 'create table student(
id int,
name varchar(20),
age int,
address varchar(20)
)'
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1
                  Master_UUID: afe3091f-b7dc-11ec-86a3-00163e19499e
             Master_Info_File: /app/mantis/data/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: 220411 15:59:16
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

ERROR: 
No query specified

1.主库宕机

部分数据数据未同步至从库。重启主库，会导致同步失败

 

方法1: 忽略错误，适用于主从数据库内容相差不大的情况

master端执行，不允许写入数据。

flush table with read lock;

slave端执行,跳过错误会导致数据不一致

stop slave;
set global sql_slave_skip_counter=1;
start slave;

 

方法2: 重新做主从同步，适用于主从数据库内容相差很大的情况

master端执行，不允许写入数据。

flush table with read lock;

将master的完整备份导入从库，重新配置主从关系。当slave的IO和SQL线程都为YES后，解锁master

unlock tables