The question:
I’m using Bitnami MariaDB. I’ve done fixing the replication and now both Slave_IO_Running and Slave_SQL_Running has value yes. This is the whole output:
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: provisioner-peer
Master_User: repl
Master_Port: 3306
Connect_Retry: 10
Master_Log_File: mysql-bin.000025
Read_Master_Log_Pos: 16161372
Relay_Log_File: mysql-relay-bin.000014
Relay_Log_Pos: 156283
Relay_Master_Log_File: mysql-bin.000025
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: 7356
Exec_Master_Log_Pos: 16161372
Relay_Log_Space: 187679
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: 89
Master_SSL_Crl:
Master_SSL_Crlpath:
Using_Gtid: No
Gtid_IO_Pos:
Replicate_Do_Domain_Ids:
Replicate_Ignore_Domain_Ids:
Parallel_Mode: optimistic
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Slave_DDL_Groups: 2
Slave_Non_Transactional_Groups: 0
Slave_Transactional_Groups: 12
But when I run command SHOW MASTER STATUS on both master and slave, it has different output.
on master:
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000025 | 16817070 | | |
+------------------+----------+--------------+------------------+
on slave:
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000004 | 390 | | |
+------------------+----------+--------------+------------------+
I search for this but ended found nothing. Is it okay? or there is something I need to fix?
The Solutions:
Below are the methods you can try. The first solution is probably the best. Try others if the first one doesn’t work. Senior developers aren’t just copying/pasting – they read the methods carefully & apply them wisely to each case.
Method 1
No problem.
A server can be a Primary or a Replica or both or neither. Your Replica seems to be both a “Master” (as indicated by SHOW MASTER STATUS) and a “Replica” (as indicated by SHOW SLAVE STATUS).
Your Replica is building a binlog file that may (or may not) be used by
- A backup mechanism being run on the Replica, and/or
- A downstream Replica which sees your current Replica at its “Master”.
The binlogs, and the positions in them, are not likely to be the same.
Method 2
There is a strong likelihood you forgot to configure log-slave-updates on the slave. If you are using MySQL 8.0.26 and above, it would be log_replica_updates.
Please add this line to your my.cnf if you are using MySQL 8.0.26 and above
[mysqld]
log_replica_updates
Please add this line to your my.cnf if you are using MySQL 8.0.25 and prior
[mysqld]
log_slave_updates
Then, restart mysqld on the Slave and the binary logs on the Slave will start increasing.
The binary logs events from the Master were executed on the Slave but were not recorded on the Slave’s local binary logs. The data should be fine.
NOTE : log-slave-updates is deprecated but still works for now. Once you upgrade past MySQL 8.0.25, please start using log_replica_updates.
All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0