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 'replica1@10.145.160.26: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 'replica1@10.145.160.26: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 | | | |
+------------------+-----------+--------------+------------------+-------------------+
