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];
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
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'