Replication is a feature in MariaDB that allows data mirroring across multiple servers. It helps in replicating data from one or more primary servers to replica servers. You can either replicate the complete database in one go or select a specific object from the database. Some common replication setups in MariaDB include Standard Replication, Ring Replication, and Ring Replication with slaves.
In MariaDB, replication uses a master-slave configuration and requires the binary logging to be enabled. All changes you made to your database get recorded in the binlog file. Each server in the replication setup is assigned a unique server_id. This id, along with the replication user, helps the replica server to connect to the primary server.
The master (primary) server uses a global transaction ID (GTID) for every transaction and writes it in the binary log. The primary server provides the binary log file to replicas when they connect to it. The replicas server first uses its relay log to store the binary log data temporarily and the IO/Thread to read and write all the data from the binary log file to the relay log. The replica server then uses an SQL thread to read the data from its relay log and apply the changes to its database. MariaDB Master-Slave Replication ensures high availability and add redundancy.
However, sometimes, you can face different errors and issues while working in the replication environment. Let’s take a look at some of the users’ queries:
Could not execute Delete_rows_v1 event on table db.tableName; Index for table ‘./db/tableName.MYI’ is corrupt; try to repair it, Error_code: 126; handler error HA_ERR_WRONG_IN_RECORD; the event’s master log binlog-file-01, end_log_pos 8980
hostA.my.internal mysqld[2666]: InnoDB: Failing assertion: btr_page_get_next(get_block->frame) == page_get_page_no(page)” in MariaDB in master-slave replication setup
Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: ‘Error: connecting slave requested to start from GTID 1-1-426253, which is not in the master’s binlog’
Such replication errors can occur due to various reasons. In this article, we will discuss the methods to fix the replication issues that occur due to corruption in MariaDB database.
Causes of MariaDB Master-Slave Replication Issues
The replication issues in MariaDB can occur due to the below reasons:
- Corruption in Binary log file.
- Corruption in Relay log file.
- Incorrect configuration settings on primary server or replicas.
- Binary log file is not enabled.
- MariaDB database is corrupted.
- Indexes, tablespace, or tables in the database are corrupted or damaged.
- The GTID number is incorrect or unavailable in the Binary log file.
- The value of Max_allowed_packed in replicas is less than the value set in master server.
Troubleshooting Methods to Resolve Issues in MariaDB Master-Slave Replication
The master-slave replication errors in MariaDB can occur due to corrupt binary log files or relay log files. If corruption in log files is the cause behind the error, then you can use the MariaDB mysqlbinlog utility to recover the log files. If it doesn’t work, then follow the below methods to troubleshoot the replication errors in MariaDB.
Method 1: Run SHOW REPLICA STATUS Command
Incorrect replication settings or internet issues on the replicas can cause the replication errors. You can run the SHOW REPLICA STATUS command in MariaDB replica’s server to get the status information about the important parameters required for the replica threads. Here’s the command:
SHOW SLAVE ["connection_name"] STATUS [FOR CHANNEL "connection_name"]
SHOW REPLICA ["connection_name"] STATUS [FOR CHANNEL "connection_name"] – From MariaDB 10.5.1
Note: To execute the above command, you must have the SUPER privilege, the REPLICATION SLAVE ADMIN privilege, the REPLICA MONITOR privilege, and the REPLICATION_CLIENT privilege on the system. You can use the SHOW PRIVILEGES statement to check the system privileges.
Method 2: Check and Enable Binary Logging Feature
The replicas server uses the binary log file, provided by the master server, to apply changes to the database to keep data in sync. If the binary logging feature is disabled in the master system, then no changes are recorded in the binary file. The replicas will fail to receive the required changes, which can lead to MariaDB Master-Slave replication process failure. To resolve this issue, you check and enable the binary logging feature in MariaDB:
- Locate the MariaDB configuration file. By default, it is located at the below path:
System Windows Directory\my.cnf
By default, the name of binary log files is set according to the host name of MariaDB server. But if the host name is changed, it can cause replication issues. So, it is recommended to check and make sure each server connected in the replication setup has a unique log file name. You can give a specific name to the file or user –log-basename in the MariaDB configuration file of both master and slave system.
Method 3: Check Configuration Settings
Configuration settings are stored in the form of system variables in MariaDB. If the replication-related variables, such as relay_log_file, relay_log_index, binlog_format, server_id, log_slave_updates, skip_networking, bind_address, etc, in the configuration files are incorrect, then you can encounter replication errors. You can check the variables using the below syntax:
SHOW [GLOBAL | SESSION] VARIABLES
[LIKE 'pattern' | WHERE expr]
If the variable values are incorrect, then you can change the configuration settings.
Method 4: Run SHOW MASTER STATUS Command
You can run the SHOW MASTER STATUS command to check the current position of the binary log file. It helps in finding the right position to resync the replication process. Here is the command:
SHOW MASTER STATUS;
You can use the information from the output of the command to update the replication configuration on replicas. You can run the SHOW REPLICA STATUS \G command to check the replication status on the replica.
Method 5: Check and Repair MariaDB Database
If the MariaDB database or its objects, like indexes, tablespaces, etc., on the primary (master) server gets corrupted, it can lead to replication issues in MariaDB. To check if corruption in the master database is causing the data sync and replication issues, you can use the CHECKSUM TABLE command. This command checks the tables on the master and replicas for any inconsistency. Here’s the syntax:
Note: To run the below command, make sure you have the SELECT privileges.
CHECKSUM TABLE tbl_name [, tbl_name] ... [ QUICK | EXTENDED ]
It any table is corrupted, then you can use the Mariabackup tool to restore the table from backup (see the below command).
Note: Make sure to empty the Data Directory from the MariaDB configuration file and use mysql.server stop to disable MariaDB Server service.
$ mariabackup --copy-back \
--target-dir=/var/mariadb/backup/
Then, restart the MariaDB Server using the mysql.server start command.
If the backup is obsolete or not available, then you can repair MariaDB database using the dump and reload method. If you are not able to start MariaDB, then can restart it using the –innodb-force-recovery=# option. Once you are able to restart the MariaDB, then follow the below steps:
- Run the SELECT command as given below to dump data from the corrupt MariaDB table:
SELECT * FROM customers
- After this, you can run the SHOW CREATE TABLE command to know the declaration of the corrupt table.
SHOW CREATE TABLE table_name
- Next, create a table with the same structure as the corrupt table. To do this, execute the CREATE TABLE command as given below:
CREATE TABLE table_name (
column1 datatype,
column2 datatype,
);
- After this, save the data in the newly created table.
- Now, use the below command to restart MariaDB:
mysqld start
- Drop the table by executing the DROP TABLE command as given below.
DROP [TEMPORARY] TABLE [IF EXISTS] [/*COMMENT TO SAVE*/]
table_name [, table_name] ...
[WAIT n|NOWAIT]
[RESTRICT | CASCADE]
- Now, recreate the table and restore the dump.
Note: Dumping and reloading a large-sized corrupted database can be a time-consuming process.
Method 6: Use a Professional MariaDB Repair Tool
You can use Stellar Repair for MySQL – a professional MariaDB repair tool – to quickly repair the MariaDB database without any data loss. Using this tool, you can repair databases created in InnoDB and MyISAM storage engines. You can even repair multiple MariaDB databases in a single process without file size restrictions. You can recover foreign keys, table space, indexes, partitioned tables, unique keys, and other objects from corrupted MariaDB database. It supports all MariaDB versions up to 11.3.2.
Conclusion
The Master-Slave Replication errors in MariaDB can occur due to various reasons. In this article, we have discussed the causes of the replication errors in MariaDB and the troubleshooting methods to resolve the errors. If corruption in indexes or database tables is causing the issues, you can restore the backup file using the Mariabackup tool. If the backup is not up-to-date or unavailable, you can repair the MariaDB database using Stellar Repair for MySQL. It can help resolve replication errors in MariaDB if occurred due to corruption in database.
Was this article helpful?
