MariaDB Troubleshooting

MariaDB Troubleshooting

I started using MariaDB in 2011, and in mid-2017 I migrated my last MySQL instance to MariaDB, so I am changing the name of this page to reflect my preference. Anything that works with MariaDB should be identical in MySQL, but the products may diverge as time goes on.

Fixing a corrupted relay log

If you are getting a relay log read failure, you can re-sync the servers this way:

stop slave;
show slave status\G

Note the Relay_Master_Log_File name and Exec_Master_Log_Pos byte position; they will be used shortly.

reset slave;

The above line purges all the relay binary logs, including the corrupted one, and writes a new index and starting relay binlog file. At this point if you did another “show slave status” command you would see the counters have been zeroed out.

change master to 
  MASTER_USER='[username]',
  MASTER_LOG_FILE='[the file name you noted above]',
  MASTER_LOG_POS=[the byte value you noted above];
In the version of MySQL I am using (MariaDB 5.1.50, MySQL 14.16) the Master_Host and most other settings remain as they were, but the Master_User gets changed to “test” so it must be reset as shown.

Manually Processing replication log files

If you ever want to take a binlog from a master server and run its contents on a slave, you can do it with a command like this:

mysqlbinlog /tmp/mysql-bin.000001 --start-position=[byte-position-to-start] \
  | sed -e 's/SET \/\*\!\*\//SET AUTOCOMMIT=0/g' \
  | mysql -uUSERNAME -pPASSWORD

Replace the “000001” with the number of the log you want to process, and set the start position to the byte position desired. For example, you might use “show slave status\G” to discover where replication halted and use the value on the Exec_Master_Log_Pos line as the starting position.

Skipping over a replication error

Sometimes you can end up with a replication error that you want to skip past without having to re-sync the servers. For example, you might have users on one server but not another so that when you remove a user on the master it causes an error on the slave that you don't care about.

To skip over a statement use:

SET GLOBAL sql_slave_skip_counter = N

Probably skipping one record is enough. After setting the skip counter you need to start replication again.

SET GLOBAL sql_slave_skip_counter=1; start slave; select sleep(0.1); show slave status\G

Ref: MariaDB Knowledge-base

Re-sync a slave

To re-sync a slave/replication server without taking the master off-line (i.e., by setting a read-lock), assuming that the slave is already configured with a working user name, password, etc. you can do these steps:

mysqldump --skip-lock-tables -q --skip-log-queries --single-transaction -F --hex-blob --master-data=1 --databases <list of DBs> | gzip > dbdump.sql.gz
--skip-lock-tables Do not lock the tables, which allows the DB to keep being used while doing the dump.
For InnoDB tables the --single-transaction option eliminates the need to lock tables.
For MyISAM tables using this option means the database could have data written to it while dumping, so it may not give you a clean state on the slave.
-q or --quick
--skip-log-queries For huge tables you could end up writing gigabytes to your logs when restoring.
--single-transaction Only applies to InnoDB tables: sends a START TRANSACTION statement before dumping.
-F or --flush-logs Write logs to disk before starting the dump.
--hex-blob Write binary data as hexadecimal.
--master-data=1 Setting this to 1 (default) includes MASTER statement. Setting it to 2 inserts the MASTER details as a comment rather than an SQL statement.
--databases Limit the dump to the databases listed. Use -A to dump all databases instead.
|gzip>dbdump.sql.gz Pipes the output to gzip and then to the dump file. This saves the extra step and disk space of doing gzip as a separate step.
rsync --bwlimit=4096 dbdump.sql.gz slave:

Passing --bwlimit= to rsync limits transfer speed to the given number of kilobits-per-second so you don't use all of your available bandwidth while transferring a huge file.

While that is transferring verify the slave settings have things like these:

server-id                               = 4                    ← unique number on each server
log-basename                            = rosalinda            ← helpful to use server name
report-host                             = rosalinda            ← ditto
relay-log                               = rosalinda-relay-bin  ← ditto
relay-log-recovery                      = 1
log-slave-updates                       = 1
read-only                               = 1                    ← if this slave does not write any data
log-bin
general_log
general_log_file                        = /var/log/mariadb/mariadb.log
binlog-format                           = MIXED
# replicate-do-db                       = specific database(s) we want to sync
# replicate-ignore-db                   = some database(s) not wanted to sync
expire_logs_days                        = 7

Then on the slave

mysql -e 'stop slave; show slave status\G'
zcat dbdump.sql.gz | mysql
mysql -e 'start slave; select sleep(1); show slave status\G'