MySQL / MariaDB: how to repair a broken replication setup

Here is the method I use, in a standard Linux environment (no snapshots, no filesystem with built-in replication, etc.) to repair MySQL or MariaDB replication after a failure serious enough to require rebuilding replication completely. Bear in mind that this is a server where all databases are replicated. You will need to adapt the solution if you replicate only some databases.
 
Note: there is no need to write down the master data information from the master server: it will be included directly in the generated MySQL dumps.
 
The first step is to stop replication. Connect to your SLAVE server and run:

STOP SLAVE;

We then get to work on the MASTER server. I also do a STOP SLAVE; here on the MASTER, because in my case, the replication was initialized in both directions. This step is unnecessary if replication is configured in only one direction.
 
You will need to open 2 sessions on the MASTER server, directly via SSH or through a tool such as screen. We will use the first session to put the server in read-only mode while the dump runs

***ON THE MASTER***

###First terminal###

STOP SLAVE;
FLUSH TABLES WITH READ LOCK;

Now that the server is read-only, we can move on to our second terminal to dump the database, all databases, or those affected by the replication error previously diagnosed in the MySQL log files.
 
I use pigz here for the compression, because it is much faster than the others, but you can use the tool of your choice, such as gzip, bzip2, rar, etc., or send it directly into an uncompressed .sql file if you have the necessary space (otherwise you can always stream the output over ssh to another server).

###Second Terminal###

mysqldump $dbtodump --opt --single-transaction --hex-blob --triggers -R -E --comments --dump-date --no-autocommit --master-data |pigz > db2dump.sql.gz

A few explanations about the command:

$dbtodump: this is the name of the database that you want to back up.
 
–opt: this option includes several other options commonly used to improve the performance of the backup. This includes –add-drop-table, –add-locks, –create-options, –disable-keys, –extended-insert, –lock-tables, and other similar options. Using –opt saves you from having to specify each of those options.
 
–single-transaction: this option carries out the backup inside a single transaction, which guarantees that the database stays consistent during the backup process, even if other operations are in progress.
 
–hex-blob: we tell mysqldump to back up BLOB-type data in hexadecimal form, which can avoid problems or inconsistencies with certain characters when restoring.
 
–triggers: includes the triggers (déclencheurs, in French) in the backup, which means that the SQL statements for the triggers will be included.
 
-R or –routines: this option also backs up the database’s stored procedures and functions.
 
-E or –events: this option also backs up database events, which are scheduled tasks executed automatically by the MySQL server.
 
–comments: this option includes comments in the backup file to explain what each section of the backup contains.
 
–dump-date: adds the creation date of the backup into the comments.
 
–no-autocommit: this option disables autocommit during the backup process, which can be useful to guarantee the consistency of the data.
 
–master-data: finally, this option lets us include the binary log (binlog) position information in the comments of the backup. This saves us from doing it manually on the slave server after importing the dump.
  
If I need to dump several databases, I can do it like this:

mysqlshow  |cut -d " " -f2 |grep -v + |grep [a-z\|A-Z\|0-9] |grep -v -w mysql |grep -v -w phpmyadmin |grep -v -w information_schema |grep -v -w performance_schema > liste.txt

I deliberately ignore the mysql database, because in my case, the master and the slave have an admin account with different passwords.

vim savedbs.sh
#!/bin/bash
mysqldump $1 --opt --single-transaction --hex-blob --triggers -R -E --comments --dump-date --no-autocommit --master-data |pigz > $1.sql.gz
chmod +x savedbs.sh
cat liste.txt |xargs -l ./savedbs.sh</code>

Once the dump or dumps are complete, we can put the MASTER MySQL or MariaDB server back in write mode:

***ON THE MASTER***

###First terminal###

UNLOCK TABLES;

There we are, production can continue while we can take care of the slave server at our leisure. I recommend running these operations in a screen session, especially with a large dump to import, so a dropped connection does not cause the restore to fail.

*** ON THE SLAVE ***

mysql -uroot -p < yourdump(s).sql[/code]
Pour reprendre l'exemple plus haut, si vous aviez plusieurs, ou bien toutes les bases de données à restaurer, vous pouvez procéder de la manière suivante : 
[code]vim restoredbs.sh
#!/bin/bash
zcat $1.sql.gz |mysql --database=$1 
chmod +x restoredbs.sh
cat liste.txt |xargs -l ./restoredbs.sh[/code]
Une fois la restauration terminée, il ne reste plus qu'à <strong>redémarrer la réplication</strong> : 
[code]START SLAVE;[code]
Puis, à vérifier que tout se passe bien : 
[code]SHOW SLAVE STATUS\G

If the replication is established in both directions, you must not forget to restore it in the other direction. For that, on the slave server, after the replication has caught up, note the log name and position:

SHOW MASTER STATUS;

Then simply run the following command on the MASTER server:

*** ON THE MASTER ***

CHANGE MASTER TO MASTER_LOG_FILE='$nameofthelogbinlaststep', MASTER_LOG_POS=$posatlaststep;
START SLAVE;

Then check that everything is fine:

SHOW SLAVE STATUS\G;