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.
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.
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;
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
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)
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.
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.
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
How can I reseed the replica server?
In a high level
