FLX_BACKEND_MYSQL_002 Alert

Description

A critical Alert is raised when MySQL Slave replication is not running"

Severity

This alert is flagged as Critical.

Customer Impact

This alert is triggered when the MySQL slave replication is completely broken and not running. This makes the environment run on a single DB master node without any backup node. That means that we have no failover in case of an incident on the main DB.

The database backup script takes backup from the slave node and doesn't take any backup from the master node. Hence it becomes crucial to have a healthy slave replication sync in place to have an up-to-date backup of the databases.

Operational Remediation Process

There are two possibilities to have a failed slave Replication

  1. Network outage o master restart: If the master is unavailable an event: "LOST EVENT" will be sent to secondary
  2. A new table is created in the NDB cluster. The slaves can not create NDB tables so they stop at the creation point.

Find the root cause of the error. Log in to the MySQL shell in the client and run:

mysql> SHOW SLAVE STATUS\G

Check the answers for

  • Slave_IO_Running: Whether the slave I/O thread is running and connected (Yes), running but not connected to a master (Connecti ng) or not running (No).
  • Slave_SQL_Running: Whether or not the SQL thread is running.
  • Last_IO_Error: Error message of the most recent error that caused the I/O thread to stop (also recorded in the slave's error log). An empty string means no error.
  • Last_SQL_Error: Error message of the most recent error that caused the SQL thread to stop (also recorded in the slave's error log). An empty string means no error.

For more information follow the mariadb documentation SHOW SLAVE STATUS

Case 1:

ERROR: Last_SQL_Error: "The incident LOST_EVENTS occurred on the master. Message: cluster disconnect"

EXPLANATION: Some error on the cluster forced a disconnect, you can skip the last statement (the error?) and restart replication DO:

  1. Stop slave: STOP SLAVE;

  2. Tell MySQL slave to skip the first line: SET GLOBAL sql_slave_skip_counter = 1;

  3. Start slave: START SLAVE

  4. Check everything is ok: SHOW SLAVE STATUS\G;

  5. slave_IO_running=yes 2. slave_SQL_running=yes

Case 2:

ERROR: Last_SQL_Error: Slave SQL thread retried transaction 10 time(s) in vain, giving up. Consider raising the value of the slave_transaction_retries variable.

EXPLANATION: The slave seems to have a single thread, while the master can parallelize transactions.

The best-recommended approach is to reimport DB and restart replication. node running the

  1. Stop slave (log in slave, mysql CLI, STOP SLAVE)

  2. Using mysqldump (from the node running the slave db) connect to the master db server and dump the database that we want to replicate.

  • ssh into the server running the slave db
$ mysqldump -u root -p --master-data \
        --single-transaction --add-drop-table \
        -h < DB master URL or IP>  \
        miomaster > /<path to backup dir>/<dbname>.dump
  1. Import the DB
$ mysql -u root -p <dbname> < <dbname>.dump
  1. Start slave
  • log in to mysql cli
START SLAVE
  1. Check that the replication is going
  `SHOW SLAVE STATUS\G; `
    Search for
    `slave_IO_running=yes `
     `slave_SQL_running=yes`