0

MySQL Master stopped sending updates 1 day ago to Slave there is no obvious error. Everything was working fine

I tried to create DB, and Users, no updates on Slave also

SO far I tried to remove the binlog_do_db and binlog_ignore_db configuration but as you can see in my configs no such options included. related question

I can't see any exact error. Plz guide me to fix this issue. DB is running on production

I'm afraiding of doing RESET SLAVE; because Master is 1 day ahead from slave. If I do reset slave it will skip one day and will start sync from this moment Am I right?

Plz help me Any guide, hint highly appreciated

UPDATE

mysqld.log last output

[ERROR] [MY-010575] [Repl] Error looking for file after /var/lib/mysql/node1-relay-bin.000103

/var/log/mysql/mysqld.log

2024-02-15T07:39:23.998067Z 0 [ERROR] [MY-010575] [Repl] Error looking for file after /var/lib/mysql/node1-relay-bin.000101.
2024-02-15T07:39:23.998095Z 0 [ERROR] [MY-013129] [Server] A message intended for a client cannot be sent there as no client-session is attached. Therefore, we're sending the information to the error-log instead: MY-001803 - Cannot recover after SLAVE errored out in parallel execution mode. Additional error messages can be found in the MySQL error log.
2024-02-15T07:39:23.999339Z 5 [System] [MY-010562] [Repl] Slave I/O thread for channel '': connected to master '[email protected]:3306',replication started in log 'node2-bin.000083' at position 865547161
2024-02-15T07:39:24.003146Z 0 [ERROR] [MY-011025] [Repl] Failed to start slave threads for channel ''.
2024-02-15T07:39:24.007208Z 0 [System] [MY-010931] [Server] /usr/libexec/mysqld: ready for connections. Version: '8.0.32'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  Source distribution.
2024-02-15T07:39:24.007192Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060, socket: /var/lib/mysql/mysqlx.sock
2024-02-15T07:40:18.162487Z 10 [Warning] [MY-010055] [Server] IP address '10.145.160.26' could not be resolved: Name or service not known
2024-02-15T07:42:54.068567Z 11 [Warning] [MY-010055] [Server] IP address '185.97.115.83' could not be resolved: Name or service not known
2024-02-15T08:23:57.795886Z 0 [System] [MY-013172] [Server] Received SHUTDOWN from user <via user signal>. Shutting down mysqld (Version: 8.0.32).
2024-02-15T08:24:07.799211Z 0 [Warning] [MY-010909] [Server] /usr/libexec/mysqld: Forcing close of thread 9  user: 'root'.
2024-02-15T08:24:07.799510Z 0 [Warning] [MY-010909] [Server] /usr/libexec/mysqld: Forcing close of thread 11  user: 'root'.
2024-02-15T08:24:07.799622Z 0 [Warning] [MY-010909] [Server] /usr/libexec/mysqld: Forcing close of thread 12  user: 'root'.
2024-02-15T08:24:08.996162Z 0 [System] [MY-010910] [Server] /usr/libexec/mysqld: Shutdown complete (mysqld 8.0.32)  Source distribution.
2024-02-15T08:24:50.256299Z 0 [Warning] [MY-011069] [Server] The syntax '--master-info-file' is deprecated and will be removed in a future release.
2024-02-15T08:24:50.256307Z 0 [Warning] [MY-011069] [Server] The syntax '--relay-log-info-file' is deprecated and will be removed in a future release.
2024-02-15T08:24:50.258254Z 0 [System] [MY-010116] [Server] /usr/libexec/mysqld (mysqld 8.0.32) starting as process 160765
2024-02-15T08:24:50.264518Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2024-02-15T08:24:50.542808Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2024-02-15T08:24:50.735372Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2024-02-15T08:24:50.735403Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
2024-02-15T08:24:52.764022Z 0 [Warning] [MY-010818] [Server] Error reading GTIDs from relaylog: -1
2024-02-15T08:24:52.769513Z 5 [Warning] [MY-010897] [Repl] Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information.
2024-02-15T08:24:52.770277Z 0 [ERROR] [MY-010575] [Repl] Error looking for file after /var/lib/mysql/node1-relay-bin.000102.
2024-02-15T08:24:52.770302Z 0 [ERROR] [MY-013129] [Server] A message intended for a client cannot be sent there as no client-session is attached. Therefore, we're sending the information to the error-log instead: MY-001803 - Cannot recover after SLAVE errored out in parallel execution mode. Additional error messages can be found in the MySQL error log.
2024-02-15T08:24:52.771340Z 5 [System] [MY-010562] [Repl] Slave I/O thread for channel '': connected to master '[email protected]:3306',replication started in log 'node2-bin.000083' at position 865547161
2024-02-15T08:24:52.775146Z 0 [ERROR] [MY-011025] [Repl] Failed to start slave threads for channel ''.
2024-02-15T08:24:52.779242Z 0 [System] [MY-010931] [Server] /usr/libexec/mysqld: ready for connections. Version: '8.0.32'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  Source distribution.
2024-02-15T08:24:52.779258Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060, socket: /var/lib/mysql/mysqlx.sock
2024-02-15T08:25:00.225702Z 9 [Warning] [MY-010055] [Server] IP address '185.97.115.83' could not be resolved: Name or service not known
2024-02-15T08:25:05.803370Z 11 [Warning] [MY-010055] [Server] IP address '10.145.160.26' could not be resolved: Name or service not known

my configs

[mysqld]

server-id=2
replicate-same-server-id=0
auto-increment-increment=2
auto-increment-offset=2
relay-log=/var/lib/mysql/node2-relay-bin
relay-log-index=/var/lib/mysql/node2-relay-bin.index
log-error=/var/log/mysql/mysql.error
master-info-file=/var/lib/mysql/mysql-master.info
relay-log-info-file=/var/lib/mysql/node1-relay-log.info
log-bin=/var/lib/mysql/node2-bin
max_connections = 1000

datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysql/mysqld.log
pid-file=/run/mysqld/mysqld.pid

SHOW REPLICA STATUS\G

*************************** 1. row ***************************
             Replica_IO_State:
                  Source_Host: 10.145.160.26
                  Source_User: replica1
                  Source_Port: 3306
                Connect_Retry: 60
              Source_Log_File: node2-bin.000083
          Read_Source_Log_Pos: 865547161
               Relay_Log_File: node1-relay-bin.000097
                Relay_Log_Pos: 868558182
        Relay_Source_Log_File: node2-bin.000083
           Replica_IO_Running: No
          Replica_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_Source_Log_Pos: 868557966
              Relay_Log_Space: 868557934
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Source_SSL_Allowed: No
           Source_SSL_CA_File:
           Source_SSL_CA_Path:
              Source_SSL_Cert:
            Source_SSL_Cipher:
               Source_SSL_Key:
        Seconds_Behind_Source: NULL
Source_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Source_Server_Id: 2
                  Source_UUID: c8cae929-b045-11ee-acb2-d89d67344edc
             Source_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
    Replica_SQL_Running_State:
           Source_Retry_Count: 86400
                  Source_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Source_SSL_Crl:
           Source_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set:
                Auto_Position: 0
         Replicate_Rewrite_DB:
                 Channel_Name:
           Source_TLS_Version:
       Source_public_key_path:
        Get_Source_public_key: 0
            Network_Namespace:
1 row in set (0.01 sec)

mysql> show variables like 'log%';

+----------------------------------------+----------------------------------------+
| Variable_name                          | Value                                  |
+----------------------------------------+----------------------------------------+
| log_bin                                | ON                                     |
| log_bin_basename                       | /var/lib/mysql/node1-bin               |
| log_bin_index                          | /var/lib/mysql/node1-bin.index         |
| log_bin_trust_function_creators        | OFF                                    |
| log_bin_use_v1_row_events              | OFF                                    |
| log_error                              | /var/log/mysql/mysqld.log              |
| log_error_services                     | log_filter_internal; log_sink_internal |
| log_error_suppression_list             |                                        |
| log_error_verbosity                    | 2                                      |
| log_output                             | FILE                                   |
| log_queries_not_using_indexes          | OFF                                    |
| log_raw                                | OFF                                    |
| log_replica_updates                    | ON                                     |
| log_slave_updates                      | ON                                     |
| log_slow_admin_statements              | OFF                                    |
| log_slow_extra                         | OFF                                    |
| log_slow_replica_statements            | OFF                                    |
| log_slow_slave_statements              | OFF                                    |
| log_statements_unsafe_for_binlog       | ON                                     |
| log_throttle_queries_not_using_indexes | 0                                      |
| log_timestamps                         | UTC                                    |
+----------------------------------------+----------------------------------------+

mysql> SHOW MASTER STATUS;

+------------------+-----------+--------------+------------------+-------------------+
| File             | Position  | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+-----------+--------------+------------------+-------------------+
| node2-bin.000084 | 524540819 |              |                  |                   |
+------------------+-----------+--------------+------------------+-------------------+

1 Answer 1

0

after 12h brainstorming I fixed the issue

  1. Using command SHOW SLAVE STATUS\G I noted 2 values Source_Log_File: node2-bin.000083 and Read_Source_Log_Pos: 865547161 this values means where slave stopped.

  2. On slave I run STOP SLAVE; and RESET SLAVE;

  3. And resumed Slave replication from exact place where is stopped CHANGE MASTER TO MASTER_HOST='10.145.160.26', MASTER_USER='replica1', MASTER_PASSWORD='replica1', MASTER_LOG_FILE='node2-bin.000083', MASTER_LOG_POS=865547161;

  4. Again START SLAVE;

  5. Check status SHOW SLAVE STATUS\G if there is no more you're lucky

    Most probably you will get error like

    Duplicate entry Primary key' or 'HA_ERR_FOUND_DUPP_KEY;

  • You can check full error using

      SELECT CHANNEL_NAME, WORKER_ID, THREAD_ID, SERVICE_STATE, LAST_ERROR_NUMBER, LAST_ERROR_MESSAGE
      FROM performance_schema.replication_applier_status_by_worker;
    

    In order to skip duplicate records. You need some more steps

    Here is solution for skipping duplicates

  • Once you run above script check error status from a) if there other errors which not covered by script you can manually skip them

     STOP SLAVE;
     SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
     START SLAVE;
     SELECT SLEEP(5);
     SHOW SLAVE STATUS\G
    

    Repeat it until all error fixes.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.