As a former MySQL DBA, I am less familiar with some MariaDB details. Also, the evolution of MariaDB Server is ongoing, with many new features, data types, and syntax, and more…
I decided then to create a new blog series for those like me who need to reinforce their MariaDB knowledge and be unbeatable during IT quizzes š
We start this series with the following question:
Since when is SHOW REPLICA STATUS available in MariaDB?
MariaDB Server supports SHOW REPLICA STATUS starting with MariaDB 10.5.1. The statement docs explicitly show that for versions before 10.5.1, only the old SHOW SLAVE STATUS form was available, while SHOW REPLICA STATUS is supported from 10.5.1 onward. [1]
I wasn’t aware that MariaDB server already supported the REPLICA term.
On JIRA, the concrete server task was MDEV-20601: āMake REPLICA a synonym for SLAVE in SQL statements.ā That task was assigned to Sujatha Sivakumar, marked Fixed, and its Fix Version is 10.5.1. It was resolved on 2020-02-04. [2]
The broader umbrella issue is MDEV-18777, which was reported by Geoff Montee and assigned to Sergei Golubchik. That epic covers the overall rename/alias effort for slave-related statements and explicitly mentions SHOW REPLICA STATUS as an example alias. [3]
So the first public apparition of SHOW REPLICA STATUS in MariaDB is already on Valentine’s Day in 2020!
MariaDB -vs- MySQL
The fix to replace offensive words on the server differs slightly from the one in MySQL, as those changes were not synchronized between the two products.
The first big change is that in the new version of MySQL, the old syntax is completely gone. In MariaDB, we opted for a dual choice.
Examples
Let’s see some examples of common statements performed on members of a replication architecture.
On the Primary / Master:
MySQL 8.4
MySQL > show master status\G
ERROR 1064 (42000): You have ane rror in your SQL syntax; check
the manual that corresponds to your MySQL server version for the
right syntax to use near 'master status' at line 1
MySQL > show binary log status\G
*************************** 1. row ***************************
File: binlog.000608
Position: 912623018
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set: 525c30b0-af38-11ec-abd0-f4a475a3749c:1-19872:dba:1
1 row in set (0.00 sec)
MySQL > show slave hosts;
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 'slave hosts' at line 1
MySQL > show replicas\G
*************************** 1. row ***************************
Server_Id: 300
Host: node-3
Port: 20211
Source_Id: 100
Replica_UUID: 00020211-3333-3333-3333-333333333333
*************************** 2. row ***************************
Server_Id: 200
Host: node-2
Port: 20210
Source_Id: 100
Replica_UUID: 00020210-2222-2222-2222-222222222222
2 rows in set (0.00 sec)MariaDB 13.0
MariaDB > show master status\G
*************************** 1. row ***************************
File: mysql-bin.000001
Position: 3066
Binlog_Do_DB:
Binlog_Ignore_DB:
Gtid_Binlog_Pos: 0-100-13
1 row in set (0.000 sec)
MariaDB > show binlog status\G
*************************** 1. row ***************************
File: mysql-bin.000001
Position: 3066
Binlog_Do_DB:
Binlog_Ignore_DB:
Gtid_Binlog_Pos: 0-100-13
1 row in set (0.000 sec)
MariaDB > show slave hosts;
+-----------+--------+-------+-----------+
| Server_id | Host | Port | Master_id |
+-----------+--------+-------+-----------+
| 300 | node-3 | 24003 | 100 |
| 200 | node-2 | 24002 | 100 |
+-----------+--------+-------+-----------+
2 rows in set (0.000 sec)
MariaDB > show replica hosts;
+-----------+--------+-------+-----------+
| Server_id | Host | Port | Master_id |
+-----------+--------+-------+-----------+
| 300 | node-3 | 24003 | 100 |
| 200 | node-2 | 24002 | 100 |
+-----------+--------+-------+-----------+
2 rows in set (0.000 sec)As we can see, the new alternative syntax is different. Which one makes more sense? I let you decide š
On the Replica / Slave:
MySQL 8.4
MySQL > show slave status\G
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 'slave status' at line 1
MySQL > show replica status\G
*************************** 1. row ***************************
Replica_IO_State: Waiting for source to send event
Source_Host: 127.0.0.1
Source_User: rsandbox
Source_Port: 20209
Connect_Retry: 60
Source_Log_File: mysql-bin.000001
Read_Source_Log_Pos: 8329
Relay_Log_File: mysql-relay.000002
Relay_Log_Pos: 8546
Relay_Source_Log_File: mysql-bin.000001
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: 8329
Relay_Log_Space: 8753
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: 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: 100
Source_UUID: 00020209-1111-1111-1111-111111111111
Source_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Replica_SQL_Running_State: Replica has read all relay log;
waiting for more updates
Source_Retry_Count: 10
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: 1
Network_Namespace:
1 row in set (0.00 sec)MariaDB 13.0
MariaDB > show replica status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 127.0.0.1
Master_User: rsandbox
Master_Port: 24001
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 3066
Relay_Log_File: mysql-relay.000003
Relay_Log_Pos: 643
Relay_Master_Log_File: mysql-bin.000001
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: 0
Exec_Master_Log_Pos: 3066
Relay_Log_Space: 4058
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: Yes
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: Yes
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 100
Master_SSL_Crl:
Master_SSL_Crlpath:
Using_Gtid: Slave_Pos
Gtid_IO_Pos: 0-100-13
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: 9
Slave_Non_Transactional_Groups: 4
Slave_Transactional_Groups: 0
Replicate_Rewrite_DB:
Connects_Tried: 1
Master_Retry_Count: 100000
1 row in set (0.002 sec)And SHOW SLAVE STATUS provides the same output in MariaDB.
Conclusion
In conclusion, in MariaDB, most commands now have synonyms that reflect the new terms, but the old syntax remains available. It seems the community’s feedback was divided, so for compatibility, both options are available.
Here is a summary of the two corresponding syntaxes:
| Historical Syntax | New Syntax |
|---|---|
| SHOW MASTER STATUS | SHOW BINLOG STATUS |
| SHOW SLAVE HOSTS | SHOW REPLICA HOSTS |
| SHOW SLAVE STATUS | SHOW REPLICA STATUS |
| RESET MASTER | / |
| RESET SLAVE | RESET REPLICA |
| START SLAVE | START REPLICA |
| STOP SLAVE | STOP REPLICA |
| CHANGE MASTER TO … | / |
The replication statements are documented here.
If you have any topics you would like covered in this category, let me know.