Introduction à la réplication sur MySQL 5

Commencez par lire le chapitre Replication et son howto.

Pour une explication en français sur la réplication mysql, vous pouvez suivre le séminaire video : La Réplication dans MySQL 5.0 et 5.1 (nécessite le WebEx Player)

Pour la mise en pratique, on peut se baser sur MySQL Replication HowTo Tutorial Video

Vérifier l'état de la synchronisation de la réplication MySQL

Une fois la réplication MySQL en place, tout va bien, on peut

Vérifier l'état du master avec SHOW MASTER STATUS MySQL 5.0 :

Avec un SHOW PROCESSLIST, vous devez également trouver votre utilisateur de réplication :

Binlog Dump	47	Writing to net

Vérifier l'état du slave avec SHOW SLAVE STATUS MySQL 5.0 :

Slave_IO_State

Une copie de la colonne State de la commande SHOW PROCESSLIST pour le thread d'I/O. Elle va vous indiquer si le thread essaye de se connecter au maître, attend des événements, se reconnecte, etc. Les différents états possibles sont listés dans la section Section 6.3, « Détails d'implémentation de la réplication ». Etudier cette colonne est nécessaire, par exemple, car le thread peut fonctionner mais ne pas réussir à se connecter au maître : seule cette colonne vous indiquera ce type de problèmes. D'un autre coté, l'état du thread SQL n'est pas indiqué, car les problèmes sont bien plus simples avec lui : soit il fonctionne, et il n'y a pas de problème; soit il ne fonctionne pas, et vous trouverez les messages d'erreur dans la colonne Last_Error, décrite plus bas.

Master_Log_File

Le nom du fichier de log binaire que le thread d'I/O utilise sur le maître.

Read_Master_Log_Pos

La position que le thread d'I/O a atteint dans le fichier de log binaire du maître.

Exec_Master_Log_Pos

La position dans les logs binaires du maître (Relay_Master_Log_File) pour le dernier événement exécuté par le thread SQL. ((Relay_Master_Log_File,Exec_Master_Log_Pos) dans le log binaire du maître correspond à (Relay_Log_File,Relay_Log_Pos) dans le log de relais.

On compare et analyse

On l'occurrence ici, les serveurs sont désynchronisés. Sur le master on peut purger les logs binaires antérieures au fichier en cours utilisé par le slave :

PURGE MASTER LOGS TO 'mysql-bin.000140';

En effet le slave n'avait plus de réseau pour accéder au master. Les routes étant posés et les réseaux connectés, la réplication reprends son cours :

On constate que les champs suivants ont changer de valeur :

• Slave_IO_State : vide -> Queueing master event to the relay log
• Slave_IO_Running : No -> Yes
• Slave_SQL_Running : No -> Yes

Re-synchroniser la réplication MySQL

Mais que faire si elle se désynchronise ? et se casse, suite à :

• une perte de réseau
• un renommage du host de la machine, sans stoper la réplication avant le changement de nom
• une écriture sur le slave à la place du master ?....

Perte du réseau

Réactiver le réseau et la réplication reprends.

Changement du hostname

[Warning] Neither --relay-log nor --relay-log-index were used; so replication may break when this MySQL server acts as a slave and has his hostname changed!! Please use '--relay-log=VMDEVMYSQL-relay-bin' to avoid this problem.

Disque slave plein et suppression des logs relay (rm mysqld-relay-bin.0*)

mysql> START SLAVE;
ERROR 1201 (HY000): Could not initialize master info structure; more error messages can be found in the MySQL error log
mysql> STOP SLAVE;
Query OK, 0 rows affected, 1 warning (0,00 sec)

mysql> RESET SLAVE;
Query OK, 0 rows affected (0,00 sec)

mysql> START SLAVE;
Query OK, 0 rows affected (0,10 sec)

mysql> SHOW SLAVE STATUS;
Slave_IO_Running : Yes
Slave_SQL_Running : Yes
1 row in set (0,00 sec)

Perte des log binaires du master (RESET) ou écriture sur le slave à la place du master

Mettre en place la réplication avec des données existantes : Setting Up Replication with Existing Data

Passer son serveur secondaire en lecture seule

Pour passer votre slave en read only pour éviter les erreurs d'écritures indésirable, vous pouvez le lancer en read-only

Cause the slave to allow no updates except from slave threads or from users having the SUPER privilege. On a slave server, this can be useful to ensure that the slave accepts updates only from its master server and not from clients. This variable does not apply to TEMPORARY tables.

# Lecture seule : --read_only
read_only = 1

• Activer le mode Read Only sur un serveur MySQL

mysqld-relay-bin

Notes au sujet des fichiers de log : mysqld-relay-bin

• Replication Relay and Status Files
  • The Slave Relay Log

The SQL thread automatically deletes each relay log file as soon as it has executed all events in the file and no longer needs it. There is no explicit mechanism for deleting relay logs because the SQL thread takes care of doing so.

Le thread SQL supprime automatiquement chaque fichier de log de relais dès qu'il a exécuté tous les événements dans le fichier et qu'il n'en a plus besoin. Il n'existe aucun mécanisme pour effacer les journaux parce que le relais de thread SQL se charge de le faire.

Les logs relai, sont indexés par le fichier mysqld-relay-bin.index

Visiblement, pour supprimer les fichiers de log relay (mysqld-relay-bin.000001, mysqld-relay-bin.000002, ..., mysqld-relay-bin.000xxx), il faut que la réplication soit fonctionnelle ! Sans quoi les fichiers relai mysqld-relay-bin.000xxx vont se cumuler jusqu'à saturation de votre disque dur ...

Ressources

• Réplication MySQL
• Réplication entre 2 serveurs MYSQL (2003) : Configurations du master et du slave, Création des certificats, Compte de réplication, Réplication circulaire, Reprise de la réplication
• mysqldump not flushing logs?

• PDF
  • New features in MySQL Replication
  • MySQL Replication Tutorialhttp://assets.en.oreilly.com/1/event/21/Replication%20Tutorial%20Presentation.pdf
  • MySQL Replication Tutorial
  • MySQL Replication Tutorial

• man mysqldump

      ·   --delete-master-logs
          On a master replication server, delete the binary logs after performing the dump operation. This option automatically enables --master-data.

      ·   --flush-logs, -F
          Flush the MySQL server log files before starting the dump. This option requires the RELOAD privilege. Note that if you use this option in combination with
          the --all-databases (or -A) option, the logs are flushed for each database dumped. The exception is when using --lock-all-tables or --master-data: In this
          case, the logs are flushed only once, corresponding to the moment that all tables are locked. If you want your dump and the log flush to happen at exactly
          the same moment, you should use --flush-logs together with either --lock-all-tables or --master-data.

      ·   --master-data[=value]
          Use this option to dump a master replication server to produce a dump file that can be used to set up another server as a slave of the master. It causes the
          dump output to include a CHANGE MASTER TO statement that indicates the binary log coordinates (file name and position) of the dumped server. These are the
          master server coordinates from which the slave should start replicating.

          If the option value is 2, the CHANGE MASTER TO statement is written as an SQL comment, and thus is informative only; it has no effect when the dump file is
          reloaded. If the option value is 1, the statement takes effect when the dump file is reloaded. If the option value is not specified, the default value is 1.

          This option requires the RELOAD privilege and the binary log must be enabled.

          The --master-data option automatically turns off --lock-tables. It also turns on --lock-all-tables, unless --single-transaction also is specified, in which
          case, a global read lock is acquired only for a short time at the beginning of the dump (see the description for --single-transaction). In all cases, any
          action on logs happens at the exact moment of the dump.

          It is also possible to set up a slave by dumping an existing slave of the master. To do this, use the following procedure on the existing slave:

           1. Stop the slave´s SQL thread and get its current status:

                  mysql> STOP SLAVE SQL_THREAD;
                  mysql> SHOW SLAVE STATUS;

           2. From the output of the SHOW SLAVE STATUS statement, get the binary log coordinates of the master server from which the new slave should start
              replicating. These coordinates are the values of the Relay_Master_Log_File and Exec_Master_Log_Pos values. Denote those values as file_name and
              file_pos.

           3. Dump the slave server:

                  shell> mysqldump --master-data=2 --all-databases > dumpfile

           4. Restart the slave:

                  mysql> START SLAVE;

           5. On the new slave, reload the dump file:

                  shell> mysql < dumpfile

           6. On the new slave, set the replication coordinates to those of the master server obtained earlier:

                  mysql> CHANGE MASTER TO
                      -> MASTER_LOG_FILE = ´file_name´, MASTER_LOG_POS = file_pos;

              The CHANGE MASTER TO statement might also need other parameters, such as MASTER_HOST to point the slave to the correct master server host. Add any such
              parameters as necessary.

      ·   --single-transaction
          This option issues a BEGIN SQL statement before dumping data from the server. It is useful only with transactional tables such as InnoDB, because then it
          dumps the consistent state of the database at the time when BEGIN was issued without blocking any applications.

          When using this option, you should keep in mind that only InnoDB tables are dumped in a consistent state. For example, any MyISAM or MEMORY tables dumped
          while using this option may still change state.

          While a --single-transaction dump is in process, to ensure a valid dump file (correct table contents and binary log position), no other connection should
          use the following statements: ALTER TABLE, DROP TABLE, RENAME TABLE, TRUNCATE TABLE. A consistent read is not isolated from those statements, so use of them
          on a table to be dumped can cause the SELECT performed by mysqldump to retrieve the table contents to obtain incorrect contents or fail.

          This option is not supported for MySQL Cluster tables; the results cannot be guaranteed to be consistent due to the fact that the NDBCLUSTER storage engine
          supports only the READ_COMMITTED transaction isolation level. You should always use NDB backup and restore instead.

          The --single-transaction option and the --lock-tables option are mutually exclusive, because LOCK TABLES causes any pending transactions to be committed
          implicitly.

          To dump large tables, you should combine this option with --quick.

      ·   --opt
          This option is shorthand; it is the same as specifying --add-drop-table --add-locks --create-options --disable-keys --extended-insert --lock-tables --quick
          --set-charset. It should give you a fast dump operation and produce a dump file that can be reloaded into a MySQL server quickly.

          The --opt option is enabled by default. Use --skip-opt to disable it.  See the discussion at the beginning of this section for information about selectively
          enabling or disabling certain of the options affected by --opt.

Script

On peut donc imaginer un petit script de dump : dumpMasterToSlave.sh

#!/bin/bash
 
#mysqldump
#Usage: mysqldump [OPTIONS] database [tables]
#OR     mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
#OR     mysqldump [OPTIONS] --all-databases [OPTIONS]
 
#--opt
#       This option is shorthand; it is the same as specifying --add-drop-table --add-locks --create-options --disable-keys --extended-insert --lock-tables --quick
#           --set-charset. It should give you a fast dump operation and produce a dump file that can be reloaded into a MySQL server quickly.
 
MYSQLDUMPOPTIONS="--all-databases --opt --single-transaction --delete-master-logs"
 
MYSQLHOSTSRC="mysql.master.placeoweb.com"
MYSQLUSERSRC="mylog"
MYSQLPASSSRC="mypass"
 
MYSQLHOSTDEST="mysql.slave.placeoweb.com"
MYSQLUSERDEST="mylogslave"
MYSQLPASSDEST="mypassslave"
 
echo "Dump all-databases ($MYSQLHOSTSRC) FULL"; date;
echo "mysqldump --host="$MYSQLHOSTSRC" --user="$MYSQLUSERSRC" --password="$MYSQLPASSSRC" $MYSQLDUMPOPTIONS > dumpMasterToSlave.sql"
mysqldump --host="$MYSQLHOSTSRC" --user="$MYSQLUSERSRC" --password="$MYSQLPASSSRC" $MYSQLDUMPOPTIONS > dumpMasterToSlave.sql
 
echo "Inject all-databases ($MYSQLHOSTDEST)"; date;
echo "mysql --host="$MYSQLHOSTDEST" --user="$MYSQLUSERDEST" --password="$MYSQLPASSDEST" < dumpMasterToSlave.sql"
mysql --host="$MYSQLHOSTDEST" --user="$MYSQLUSERDEST" --password="$MYSQLPASSDEST" < dumpMasterToSlave.sql
echo "Fin"; date;

Et un autre pour surveiller ces 2 serveurs MySQL : compareAndPurgeMySqlMasterFromSlave.sh

#!/bin/sh
 
MYSQL_HOST=localhost
MYSQL_USER=root
MYSQL_PASS="mypass"
 
MYSQL_SLAVE_HOST=mysql51.slave.placeoweb.com
MYSQL_SLAVE_USER=userMySQLSlave
MYSQL_SLAVE_PASS=passMySQLSlave
 
# Optimisation des tables
mysqlcheck --all-databases --analyze --check-only-changed --optimize --host=$MYSQL_HOST --user=$MYSQL_USER --password=$MYSQL_PASS >mysql_check.log
#>mysql_check.log
 
 
# Vérification MASTER/SLAVE
 
retourSqlMaster=( $(echo "SHOW MASTER STATUS;" | mysql --host=$MYSQL_HOST --user=$MYSQL_USER --password=$MYSQL_PASS --vertical | tr -d " ") )
 
for ((i=0; i<${#retourSqlMaster[@]}; i++))
do
        if echo ${retourSqlMaster[i]} |grep "^File">/dev/null; then File=$(echo ${retourSqlMaster[i]} | awk -F ':' '{print $2}'); fi
        if echo ${retourSqlMaster[i]} |grep "^Position">/dev/null; then Position=$(echo ${retourSqlMaster[i]} | awk -F ':' '{print $2}'); fi
done
 
echo "-------------------------------------MASTER" >mysqlMasterSlave.log
echo "File = $File" >>mysqlMasterSlave.log
echo "Position = $Position" >>mysqlMasterSlave.log
 
 
retour=( $(echo "SHOW SLAVE STATUS;" | mysql --host=$MYSQL_SLAVE_HOST --user=$MYSQL_SLAVE_USER --password=$MYSQL_SLAVE_PASS --vertical | tr -d " ") )
 
for ((i=0; i<${#retour[@]}; i++))
do
        if echo ${retour[i]} |grep "^Slave_SQL_Running">/dev/null; then Slave_SQL_Running=$(echo ${retour[i]} | awk -F ':' '{print $2}'); fi
        if echo ${retour[i]} |grep "^Master_Log_File">/dev/null; then Master_Log_File=$(echo ${retour[i]} | awk -F ':' '{print $2}'); fi
        if echo ${retour[i]} |grep "^Read_Master_Log_Pos">/dev/null; then Read_Master_Log_Pos=$(echo ${retour[i]} | awk -F ':' '{print $2}'); fi
done
 
echo "-------------------------------------SLAVE" >>mysqlMasterSlave.log
echo "Slave_SQL_Running = $Slave_SQL_Running" >>mysqlMasterSlave.log
echo "Master_Log_File =  $Master_Log_File" >>mysqlMasterSlave.log
echo "Read_Master_Log_Pos = $Read_Master_Log_Pos" >>mysqlMasterSlave.log
 
 
echo "-------------------------------------Analyse" >>mysqlMasterSlave.log
 
#http://linux-attitude.fr/post/utiliser-le-pipe-sur-stderr
#1>&2 permet de rediriger stdout sur sterr,
 
if [ -z $Master_Log_File  ]
then
        exec 1>&2
 
        echo "Attention un element de réplication MySQL est VIDE !"
 
        echo "-------------------------------------SLAVE"
        echo "Slave_SQL_Running = $Slave_SQL_Running"
        echo "Master_Log_File =  $Master_Log_File"
        echo "Read_Master_Log_Pos = $Read_Master_Log_Pos"
 
else
        if [ -z $File ]
        then
                exec 1>&2
 
                echo "Attention un element de réplication MySQL est VIDE !"
 
                echo "-------------------------------------MASTER"
                echo "File = $File"
                echo "Position = $Position"
 
        else
                echo "OK, vérification en cours :" >>mysqlMasterSlave.log
                echo "MASTER : $File $Position" >>mysqlMasterSlave.log
                echo "SLAVE  : $Master_Log_File $Read_Master_Log_Pos" >>mysqlMasterSlave.log
 
                echo "PURGE MASTER LOGS TO '$Master_Log_File';"  | mysql --host=$MYSQL_HOST --user=$MYSQL_USER --password=$MYSQL_PASS >>mysqlMasterSlave.log
        fi
fi
 
exit 0