Mysql replica is up and running but I can't see updates on my slave

I have a problem with MySQL replica. MySQL version is 8.0.23.

MySQL replica is up and running but I can’t see updates on my slave.

mysql> show replica status\G
*************************** 1. row ***************************
             Replica_IO_State: Waiting for master to send event
                  Source_Host: dccaz012avm.gruppogavio.corp
                  Source_User: replica_user
                  Source_Port: 3306
                Connect_Retry: 60
              Source_Log_File: MySQL_log.000022
          Read_Source_Log_Pos: 520071650
               Relay_Log_File: MySQL_relaylog.000023
                Relay_Log_Pos: 520071865
        Relay_Source_Log_File: MySQL_log.000022
           Replica_IO_Running: Yes
          Replica_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_Source_Log_Pos: 520071650
              Relay_Log_Space: 520072160
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Source_SSL_Allowed: Yes
           Source_SSL_CA_File:
           Source_SSL_CA_Path:
              Source_SSL_Cert:
            Source_SSL_Cipher:
               Source_SSL_Key:
        Seconds_Behind_Source: 0
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: 1
                  Source_UUID: 8cce0b8e-75ed-11eb-9cb3-000d3a3a9cb9
             Source_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
    Replica_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Source_Retry_Count: 86400
                  Source_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Source_SSL_Crl:
           Source_SSL_Crlpath:
           Retrieved_Gtid_Set: 8cce0b8e-75ed-11eb-9cb3-000d3a3a9cb9:1992417-2050018
            Executed_Gtid_Set: 2f3b2e03-76b2-11eb-b016-000d3a44150a:1-48,
8cce0b8e-75ed-11eb-9cb3-000d3a3a9cb9:1-891211298
                Auto_Position: 0
         Replicate_Rewrite_DB:
                 Channel_Name:
           Source_TLS_Version:
       Source_public_key_path:
        Get_Source_public_key: 0
            Network_Namespace:

If I create a new database on master server or I create a table on an existing db on master server, I can’t see the db or the table on my slave that has read_only and super_read_only set.

Any suggestions?

Hi @Fborgoglio71,

I see seconds behind source is 0 and binlogs are moving based on the coordinates. If you’re writing on the source but not getting the data on replica then I’d check the following.

  • Source is not writing to binary log? Are you using sql_log_bin=0? Do we have filters like binlog_do_db?

  • I don’t see any filters on replica relicate_do/ignore… thus it is not a problem.

  • Does the binary log on master contains the DDL/DML that you expect to replicate?
    mysqlbinlog --base64-output=decode-rows -vv MySQL_log.000022 > MySQL_log.000022.dbg

  • Is there anything in your replica error log?

About read_ony / super_read_only … that will not impact replication threads and that may not be the reason for you not s eeing your data on the replica.

Thanks,
K

-) sql_log_bin is set equal to ON on master and slave servers, but not as GLOBAL variables.

mysql> show global variables like ‘%bin%’;
±-----------------------------------------------±---------------------------------------------+
| Variable_name | Value |
±-----------------------------------------------±---------------------------------------------+
| bind_address | * |
| binlog_cache_size | 32768 |
| binlog_checksum | CRC32 |
| binlog_direct_non_transactional_updates | OFF |
| binlog_encryption | OFF |
| binlog_error_action | ABORT_SERVER |
| binlog_expire_logs_seconds | 259200 |
| binlog_format | ROW |
| binlog_group_commit_sync_delay | 0 |
| binlog_group_commit_sync_no_delay_count | 0 |
| binlog_gtid_simple_recovery | ON |
| binlog_max_flush_queue_time | 0 |
| binlog_order_commits | ON |
| binlog_rotate_encryption_master_key_at_startup | OFF |
| binlog_row_event_max_size | 8192 |
| binlog_row_image | FULL |
| binlog_row_metadata | MINIMAL |
| binlog_row_value_options | |
| binlog_rows_query_log_events | OFF |
| binlog_stmt_cache_size | 32768 |
| binlog_transaction_compression | OFF |
| binlog_transaction_compression_level_zstd | 3 |
| binlog_transaction_dependency_history_size | 25000 |
| binlog_transaction_dependency_tracking | COMMIT_ORDER |
| innodb_api_enable_binlog | OFF |
| log_bin | ON |
| log_bin_basename | /opt/mysqldata00/bin_log_dir/MySQL_log |
| log_bin_index | /opt/mysqldata00/bin_log_dir/MySQL_log.index |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
| log_statements_unsafe_for_binlog | ON |
| max_binlog_cache_size | 18446744073709547520 |
| max_binlog_size | 1073741824 |
| max_binlog_stmt_cache_size | 18446744073709547520 |
| mysqlx_bind_address | * |
| sync_binlog | 1 |
±-----------------------------------------------±---------------------------------------------+
36 rows in set (0.03 sec)

mysql> show variables like ‘%bin%’;
±-----------------------------------------------±---------------------------------------------+
| Variable_name | Value |
±-----------------------------------------------±---------------------------------------------+
| bind_address | * |
| binlog_cache_size | 32768 |
| binlog_checksum | CRC32 |
| binlog_direct_non_transactional_updates | OFF |
| binlog_encryption | OFF |
| binlog_error_action | ABORT_SERVER |
| binlog_expire_logs_seconds | 259200 |
| binlog_format | ROW |
| binlog_group_commit_sync_delay | 0 |
| binlog_group_commit_sync_no_delay_count | 0 |
| binlog_gtid_simple_recovery | ON |
| binlog_max_flush_queue_time | 0 |
| binlog_order_commits | ON |
| binlog_rotate_encryption_master_key_at_startup | OFF |
| binlog_row_event_max_size | 8192 |
| binlog_row_image | FULL |
| binlog_row_metadata | MINIMAL |
| binlog_row_value_options | |
| binlog_rows_query_log_events | OFF |
| binlog_stmt_cache_size | 32768 |
| binlog_transaction_compression | OFF |
| binlog_transaction_compression_level_zstd | 3 |
| binlog_transaction_dependency_history_size | 25000 |
| binlog_transaction_dependency_tracking | COMMIT_ORDER |
| innodb_api_enable_binlog | OFF |
| log_bin | ON |
| log_bin_basename | /opt/mysqldata00/bin_log_dir/MySQL_log |
| log_bin_index | /opt/mysqldata00/bin_log_dir/MySQL_log.index |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
| log_statements_unsafe_for_binlog | ON |
| max_binlog_cache_size | 18446744073709547520 |
| max_binlog_size | 1073741824 |
| max_binlog_stmt_cache_size | 18446744073709547520 |
| mysqlx_bind_address | * |
| sql_log_bin | ON |
| sync_binlog | 1 |
±-----------------------------------------------±---------------------------------------------+
37 rows in set (0.01 sec)

-) binlog_do_db is not set

-) Yes. Binary log on master contains the DDL/DML that I expect to replicate.

-) I have no errors on my replica error log

Could you please run this command on the replica:

SHOW VARIABLES LIKE ‘%replicate%’;

Here it is the output:

mysql> show variables like ‘%replicate%’;
Empty set (0.00 sec)

Hi @Fborgoglio71,
Can you try this one exercise and provide me with the output. Sorry but I doubt we’re missing something very basic:

Connect to replica ( ??.gruppogavio.corp)

mysql -uUSER -p
show replica status\G
SHOW BINARY LOG STATUS;

Connect to source ( dccaz012avm.gruppogavio.corp)

SHOW BINARY LOG STATUS; 
create database debug;
use debug;
create table debug.test (id int not null auto_increment primary key);
SHOW BINARY LOG STATUS;

Connect to replica ( ??.gruppogavio.corp)

show replica status\G
SHOW BINARY LOG STATUS;

Thanks,
K

OK.
But when I run “SHOW BINARY LOG STATUS;” I get this error:

mysql> SHOW BINARY LOG STATUS;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘LOG STATUS’ at line 1

Your on 8.0.23. Run this command:

show master status;

2 Likes

Thank you very much.

Here is it the result.

–) Connected to replica

mysql> show replica status\G
*************************** 1. row ***************************
Replica_IO_State: Waiting for master to send event
Source_Host: dccaz012avm.gruppogavio.corp
Source_User: replica_user
Source_Port: 3306
Connect_Retry: 60
Source_Log_File: MySQL_log.000195
Read_Source_Log_Pos: 1060428051
Relay_Log_File: MySQL_relaylog.000542
Relay_Log_Pos: 1060428266
Relay_Source_Log_File: MySQL_log.000195
Replica_IO_Running: Yes
Replica_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_Source_Log_Pos: 1060428051
Relay_Log_Space: 1060428561
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Source_SSL_Allowed: Yes
Source_SSL_CA_File:
Source_SSL_CA_Path:
Source_SSL_Cert:
Source_SSL_Cipher:
Source_SSL_Key:
Seconds_Behind_Source: 0
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: 1
Source_UUID: 8cce0b8e-75ed-11eb-9cb3-000d3a3a9cb9
Source_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Replica_SQL_Running_State: Slave has read all relay log; waiting for more updates
Source_Retry_Count: 86400
Source_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Source_SSL_Crl:
Source_SSL_Crlpath:
Retrieved_Gtid_Set: 8cce0b8e-75ed-11eb-9cb3-000d3a3a9cb9:1992417-4078062
Executed_Gtid_Set: 2f3b2e03-76b2-11eb-b016-000d3a44150a:1-48,
8cce0b8e-75ed-11eb-9cb3-000d3a3a9cb9:1-891211298
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.00 sec)

mysql> show master status;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect…
Connection id: 29
Current database: *** NONE ***

±-----------------±---------±-------------±-----------------±--------------------------------------------------------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
±-----------------±---------±-------------±-----------------±--------------------------------------------------------------------------------------------+
| MySQL_log.000008 | 196 | | | 2f3b2e03-76b2-11eb-b016-000d3a44150a:1-48,
8cce0b8e-75ed-11eb-9cb3-000d3a3a9cb9:1-891211298 |
±-----------------±---------±-------------±-----------------±--------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

–) Connecte to source

mysql> show master status;
±-----------------±---------±-------------±-----------------±-----------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
±-----------------±---------±-------------±-----------------±-----------------------------------------------+
| MySQL_log.000196 | 34393362 | | | 8cce0b8e-75ed-11eb-9cb3-000d3a3a9cb9:1-4079233 |
±-----------------±---------±-------------±-----------------±-----------------------------------------------+
1 row in set (0.00 sec)

mysql> create database debug;
Query OK, 1 row affected (0.03 sec)

mysql> use debug;
Database changed

mysql> create table debug.test (id int not null auto_increment primary key);
Query OK, 0 rows affected (0.08 sec)

mysql> show master status;
±-----------------±---------±-------------±-----------------±-----------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
±-----------------±---------±-------------±-----------------±-----------------------------------------------+
| MySQL_log.000196 | 70953379 | | | 8cce0b8e-75ed-11eb-9cb3-000d3a3a9cb9:1-4079563 |
±-----------------±---------±-------------±-----------------±-----------------------------------------------+
1 row in set (0.00 sec)

Hi @Fborgoglio71,

Could you see the problem? Hint Hint Look at GTID.

mysql> show master status;
±-----------------±---------±-------------±-----------------±-----------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
±-----------------±---------±-------------±-----------------±-----------------------------------------------+
| MySQL_log.000196 | 70953379 | | | **8cce0b8e-75ed-11eb-9cb3-000d3a3a9cb9:1-4079563** |
±-----------------±---------±-------------±-----------------±-----------------------------------------------+

but on replica

 Executed_Gtid_Set: 2f3b2e03-76b2-11eb-b016-000d3a44150a:1-48,
**8cce0b8e-75ed-11eb-9cb3-000d3a3a9cb9:1-891211298**

Let me say it… The replica is ahead of primary, it has more gtids than the primary! Thus all the writes on master are getting ignored! I think the replica was not correctly moved / cofigured under the writer…
Best is for you to reseed the replica.

Thanks,
K

1 Like

I published this document some time back. I will help you rebuild your replica using the clone plugin. Its an option to help you to re-seed your replica.

Thank You very much Wayne.
I really don’t know Clone Plugin.
I’m considering it but have you also a doc for reseeding my replica?
I followed all the step and I can’t understand as it has more gtids than the primary.

I have run by mistake, the command

CHANGE MASTER TO
MASTER_HOST = ‘dccaz012avm.gruppogavio.corp’,
MASTER_PORT = 3306,
MASTER_USER = ‘replica_user’,
MASTER_PASSWORD = ‘mysql_replica8.0.23’,
MASTER_SSL = 1,

on my master server.

Could be this mistake that have caused the replica ahead of primary?

How can I fix the master server?

How can I reseed the replica server?

Hi @Fborgoglio71,
Only executing CHANGE MASTER TO will do no harm unless you have “start replica” after that OR you had a stopped replica on that master itself. I doubt either of that was the case.
Any issue on primary cannot raise txns on replica and make it go ahead of primary! I think investigating that will need more information but if it is not important I’d recommend you to prioritize reseeding the replica.

I don’t think you have done any harm on Master and hence you can just say “reset replica all” and that should take care of it. Besides, dccaz012avm is master itself, thus it won’t do self-replication :slight_smile:

How can I reseed the replica server?
In a high level

  • Take fresh backup from primary
  • Perform restore
  • Setup replication

Thanks,
K