PlaceOweb Dotclear

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 :

File	Position	Binlog_Do_DB	Binlog_Ignore_DB
mysql-bin.000144	75050945

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	Master_Host	Master_User	Master_Port	Connect_Retry	Master_Log_File	Read_Master_Log_Pos	Relay_Log_File	Relay_Log_Pos	Relay_Master_Log_File	Slave_IO_Running	Slave_SQL_Running	Replicate_Do_DB	Replicate_Ignore_DB	Replicate_Do_Table	Replicate_Ignore_Table	Replicate_Wild_Do_Table	Replicate_Wild_Ignore_Table	Last_Errno	Last_Error	Skip_Counter	Exec_Master_Log_Pos	Relay_Log_Space	Until_Condition	Until_Log_File	Until_Log_Pos	Master_SSL_Allowed	Master_SSL_CA_File	Master_SSL_CA_Path	Master_SSL_Cert	Master_SSL_Cipher	Master_SSL_Key	Seconds_Behind_Master	Master_SSL_Verify_Server_Cert	Last_IO_Errno	Last_IO_Error	Last_SQL_Errno	Last_SQL_Error
	mysql51.placeoweb.com	replication	3306	60	mysql-bin.000140	48059680	VMDEVMYSQL-relay-bin.000283	4	mysql-bin.000140	No	No							0		0	48059680	0	None		0	No						NULL	No	0		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 :

Slave_IO_State	Master_Host	Master_User	Master_Port	Connect_Retry	Master_Log_File	Read_Master_Log_Pos	Relay_Log_File	Relay_Log_Pos	Relay_Master_Log_File	Slave_IO_Running	Slave_SQL_Running	Replicate_Do_DB	Replicate_Ignore_DB	Replicate_Do_Table	Replicate_Ignore_Table	Replicate_Wild_Do_Table	Replicate_Wild_Ignore_Table	Last_Errno	Last_Error	Skip_Counter	Exec_Master_Log_Pos	Relay_Log_Space	Until_Condition	Until_Log_File	Until_Log_Pos	Master_SSL_Allowed	Master_SSL_CA_File	Master_SSL_CA_Path	Master_SSL_Cert	Master_SSL_Cipher	Master_SSL_Key	Seconds_Behind_Master	Master_SSL_Verify_Server_Cert	Last_IO_Errno	Last_IO_Error	Last_SQL_Errno	Last_SQL_Error
Queueing master event to the relay log	mysql51.placeoweb.com	replication	3306	60	mysql-bin.000140	188765299	VMDEVMYSQL-relay-bin.000285	942397	mysql-bin.000140	Yes	Yes							0		0	49001826	140706030	None		0	No						187743	No	0		0

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 ...

Mettre en place une réplication MySQL avec un serveur master qui tourne déjà

En gros, faire un dump du master, que l'on injecte sur le slave avec le les options de slave arrêtées et re-initialisées

• STOP SLAVE Arrête l'esclave
• RESET SLAVE Force l'esclave a oublier toute les positions de réplications dans les logs du maître
• CHANGE MASTER TO

Le dump

Votre dump avec l'option "master-data" indiquera la position à utiliser par le slave, dans l'entête de votre dump vous retrouverez :

mysqldump --host=mysql51.placeoweb.com --user="user_dump" --password="monMonDePasse" --all-databases --opt --delete-master-logs --flush-logs --master-data=2 --lock-all-tables  > dumpMasterToSlave.sql
 
mysqldump --host=mysql51.placeoweb.com --user="user_dump" --password="monMonDePasse" --all-databases --opt --delete-master-logs --flush-logs --master-data=2 --single-transaction > dumpMasterToSlave.sql
 
# En forçant le dump des routines (procédures et functions) et des déclencheurs (triggers)
mysqldump --host=mysql51.placeoweb.com --user="user_dump" --password="monMonDePasse" --all-databases --opt --routines --triggers --lock-all-tables --delete-master-logs --flush-logs --master-data=2 > dumpMasterToSlave.sql

-- Modifie les paramètres que l'esclave utilise pour se connecter et pour communiquer avec le serveur maître.
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000661', MASTER_LOG_POS=106;

L'injection

mysql --host="mysql51.slave.placeoweb.com" --user="user_dump" --password="monMonDePasse" < dumpMasterToSlave.sql

La relance du slave

• START SLAVE démarre les deux threads esclaves :
  • Le thread I/O lire les requêtes du maître et les stocke dans le log de relais.
  • Le thread SQL lire le log de relais, et exécute les requêtes.

En cas de crash du serveur MySQL, comment restaurer la réplication SQL ?

Réparer les tables corrompues

Si votre serveur master c'est brutalement arrêté, vous pourrez avoir des tables marquées comme accidentés/cassés/corrompues (crashed)

SQLSTATE[HY000]: General error: 1194 Table 'matablemyisam' is marked as crashed and should be repaired

Analyse de l'état de la table endommagée, réparation, puis analyse :

-- Avant réparation avec la corruption de table
 
requête SQL: CHECK TABLE matablemyisam;
Lignes: 5
Table 	Op 	Msg_type 	Msg_text
mabase.matablemyisam 	check 	warning 	Table is marked as crashed
mabase.matablemyisam 	check 	warning 	2 clients are using or haven't closed the table properly
mabase.matablemyisam 	check 	warning 	Size of datafile is: 220992904       Should be: 220990988
mabase.matablemyisam 	check 	error 	Wrong bytesec: 103-50-114 at linkstart: 220990868
mabase.matablemyisam 	check 	error 	Corrupt
 
requête SQL: ANALYZE TABLE matablemyisam;
Lignes: 3
Table 	Op 	Msg_type 	Msg_text
mabase.matablemyisam 	analyze 	Error 	Table './mabase/matablemyisam' is marked as crashed and should be repaired
mabase.matablemyisam 	analyze 	Error 	Table 'matablemyisam' is marked as crashed and should be repaired
mabase.matablemyisam 	analyze 	error 	Corrupt
 
 
requête SQL: CHECKSUM TABLE matable;
Lignes: 1
Table 	Checksum
mabase.matablemyisam 	NULL
 
-- Réparation avec mysqlcheck --all-databases --repair --host=$MYSQL_HOST --user=$MYSQL_USER --password=$MYSQL_PASS >mysqlcheck.repair.log
 
mabase.mytableinnodb
note     : The storage engine for the table doesn't support repair
mabase.matablemyisam
info     : Wrong bytesec: 103- 50-114 at 220990868; Skipped
info     : Wrong block with wrong total length starting at 220912328
info     : Wrong block with wrong total length starting at 220990012
info     : Found block that points outside data file at 220990172
info     : Found block that points outside data file at 220990284
info     : Found block that points outside data file at 220990372
info     : Found block that points outside data file at 220991104
info     : Found block with too small length at 220991108; Skipped
warning  : Number of rows changed from 274022 to 274019
status   : OK
 
-- Donc lors de la réparation on a perdu 3 enregistrements (274022 - 274019), aie aie aie... c'est parfait pour des données comptables...
-- Mais bon, quelle idée de mettre des données sensibles sur des tables MyISAM non transactionnelles à la place de tables InnoDB qui sont transactionnelles...
 
-- Avec table réparée, aprés un REPAIR TABLE
 
requête SQL: CHECK TABLE mytableinnodb;
Lignes: 1
Table 	Op 	Msg_type 	Msg_text
mabase.mytableinnodb 	check 	status 	OK
 
requête SQL: ANALYZE TABLE mytableinnodb;
Lignes: 1
Table 	Op 	Msg_type 	Msg_text
mabase.mytableinnodb 	analyze 	status 	OK
 
requête SQL: CHECKSUM TABLE mytableinnodb;
Lignes: 1
Table 	Checksum
mabase.mytableinnodb 	2536974393

Réparer la réplication master/slave

   [Master_Log_File] => mysql-bin.000634
   [Read_Master_Log_Pos] => 129312681
   [Last_IO_Errno] => 1236
   [Last_IO_Error] => Got fatal error 1236 from master when reading data from binary log: 'Client requested master to start replication from impossible position'

Comme le slave avait tout lu jusqu'à la fin du fichier de log binaire du master (mysql-bin.000634) on passe au fichier suivant (mysql-bin.000635), au besoin passer à une postion plus loin dans le dernier fichier non lu.

-- Sur le SLAVE
SLAVE STOP;
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000635', MASTER_LOG_POS=0;
SLAVE START;

Si vous ensuite vous rencontrez des erreurs du type :

Last_Errno 	1062
Last_Error 	Error 'Duplicate entry '1629083' for key 'PRIMARY'' on query. Default database: 'mabase'. Query: 'INSERT INTO log (id, id_user, REMOTE_ADDR, ...

• Soit vous supprimer l'id gênant sur le SLAVE avant de relancer le SLAVE.
• Soit ignorer le prochain binlog (celui qui pose souci) SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; START SLAVE;
• Soit vous outrepassez cette écriture en faisant un saut dans les positions du relay-log depuis le serveur slave :

-- Sur le SLAVE
SLAVE STOP;
CHANGE MASTER TO RELAY_LOG_POS=3439351;
SLAVE START;

• SET GLOBAL SQL_SLAVE_SKIP_COUNTER
  • Commandes SQL de contrôle des esclaves de réplication :: SET GLOBAL SQL_SLAVE_SKIP_COUNTER Ignore les n prochains événements du maître.
  • SQL Statements for Controlling Slave Servers :: SET GLOBAL sql_slave_skip_counter Syntax
• MySQL skip duplicate replication errors

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 Tutorial
  • 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"
MYSQLDUMPOPTIONS="--all-databases --opt --routines --triggers --lock-all-tables --delete-master-logs --flush-logs --master-data=2"
 
 
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

Droits pour dumper le serveur master MySQL

-- Déclaration d'un compte permettant de dumper le master (incluant les vues et les triggers) avec le lock des tables et les droits de FLUSH (pour les logs)
-- mysqldump --host=mysql51.placeoweb.com --user=user_dump --password=monMotDePasse --all-databases --opt --delete-master-logs --lock-all-tables --master-data=2 > dumpMasterToSlave.sql
GRANT 
SELECT,
SHOW VIEW,
TRIGGER,
SUPER,
RELOAD,
SHOW DATABASES,
LOCK TABLES,
REPLICATION CLIENT
ON *.* TO 'user_dump'@'%' IDENTIFIED BY 'monMotDePasse';
 
-- SHOW VIEW nécessaire pour voir la création de la vue SHOW CREATE VIEW `maTable`
-- TRIGGER nécessaire pour les voir SHOW TRIGGERS
-- Pour les procédures stockées ROUTINE, rien n'est nécessaire pour les voir SHOW PROCEDURE STATUS
-- Pour les événements EVENT (cron/schedul) ... je n'ai pas essayé ...
 
-- Couldn't execute 'SHOW MASTER STATUS': Access denied; you need the SUPER,REPLICATION CLIENT privilege for this operation (1227)

• Droits fournis par MySQL 5.0
• Privileges Provided by MySQL 5.1
• Privileges Provided by MySQL 5.5

• Données
  • SELECT Permission de lire des données
  • INSERT Permission d'ajouter et de remplacer des données
  • UPDATE Permission de changer des données
  • DELETE Permission de détruire des données
  • FILE Permission d'importer et d'exporter des données à partir de / dans des fichiers
• Structure
  • CREATE Permission de créer des bases de données et des tables
  • ALTER Permission de modifier la structure des tables existantes
  • INDEX Permission de créer et d'effacer des index
  • DROP Permission d'effacer des bases de données et des tables
  • CREATE TEMPORARY TABLES Permission de créer des tables temporaires
  • SHOW VIEW Permission d'exécuter SHOW CREATE VIEW
  • CREATE ROUTINE Permission de créer des procédures stockées
  • ALTER ROUTINE Permission de modifier et de supprimer des procédures stockées
  • EXECUTE Permission d'exécuter des procédures stockées
  • CREATE VIEW Permission de créer des vues
  • EVENT Permission de mettre en place des événements pour le programmateur d'événements
  • TRIGGER Permission de consulter, de créer et de supprimer des déclencheurs
• Administration
  • GRANT Permission d'ajouter des utilisateurs et des privilèges sans avoir besoin de recharger les privilèges
  • SUPER Permission de se connecter, même si le nombre maximal de connexions permises a été atteint; ceci est requis pour la plupart des opérations de type administratif, par exemple les changements de variables globales ou la destruction de processus
  • PROCESS Permission de voir les processus de tous les utilisateurs
  • RELOAD Permission de recharger les réglages du serveur, et de vidanger la mémoire cache
  • SHUTDOWN Permission d'arrêter le serveur MySQL
  • SHOW DATABASES Permission de voir la liste complète des noms de bases de données
  • LOCK TABLES Permission de verrouiller des lignes dans le fil courant (unité d'exécution)
  • REFERENCES Non effectif dans cette version de MySQL (5.1.56)
  • REPLICATION CLIENT Permission de demander où sont les maîtres et les esclaves (système de duplication)
  • REPLICATION SLAVE Nécessaire pour les esclaves (système de duplication)
  • CREATE USER Permission de créer, supprimer et renommer des comptes utilisateurs
• Limites de ressources (Note: Une valeur de 0 (zero) enlève la limite)
  • MAX QUERIES PER HOUR Limite du nombre de requêtes qu'un utilisateur peut envoyer au serveur, par heure
  • MAX UPDATES PER HOUR Limite du nombre de commandes changeant une table ou base de données, qu'un utilisateur peut exécuter, par heure
  • MAX CONNECTIONS PER HOUR Limite du nombre de nouvelles connexions qu'un utilisateur peut démarrer, par heure
  • MAX USER_CONNECTIONS Limite le nombre de connexions simultanées autorisées pour un utilisateur

-- Liste des droits des utilisateurs
SELECT Password , Host, User, 
CONCAT( Select_priv, Lock_tables_priv ) AS selock, 
CONCAT( Insert_priv, Update_priv, Delete_priv, Create_priv, Drop_priv ) AS modif, 
CONCAT( Grant_priv, References_priv, Index_priv, Alter_priv ) AS meta, 
CONCAT( Create_tmp_table_priv, Create_view_priv, Show_view_priv ) AS views, 
CONCAT( Create_routine_priv, Alter_routine_priv, Execute_priv ) AS funcs, 
CONCAT( Repl_slave_priv, Repl_client_priv ) AS replic, 
CONCAT( Super_priv, Shutdown_priv, Process_priv, File_priv, Show_db_priv, Reload_priv ) AS admin
FROM mysql.user
ORDER BY user, host

Error 'Field 'myField' doesn't have a default value' on query.

Last_Errno 	1364
Last_Error 	Error 'Field 'myField' doesn't have a default value' on query. Default database: 'myDataBase'. Query: 'REPLACE INTO `myTable` (`table_num`,`ip`) VALUES('0','192.168.0.253')

L'erreur MySQL 1364 de colonne non spécifié qui n'a pas de valeur par défaut dans votre table peut stopper la réplication à cause du mode strict (sql_mode 2097152) de MySQL

Server Error Codes and Messages : error code 1364 Field '%s' doesn't have a default value

Error: 1364 SQLSTATE: HY000 (ER_NO_DEFAULT_FOR_FIELD)
Message: Field '%s' doesn't have a default value

# at 581709
#110422 17:36:03 server id 1  end_log_pos 51901254      Query   thread_id=4015896       exec_time=0     error_code=0
SET TIMESTAMP=1303486563/*!*/;
SET @@session.sql_mode=2097152/*!*/;
BEGIN

STRICT_TRANS_TABLES When I format a binary log file with "mysqlbinlog", the following line appears in the output: SET @@session.sql_mode=2097152; Bug #13897 mysqlbinlog outputs wrong 'SET @@session.sql_mode' statement

slave exec mode

• New in MySQL 5.1: Sheeri’s Presentation

slave-exec-mode: This is a new mode to deal with conflict resolution in replication scenarios such as circular replication and master/master replication. STRICT mode is the default, which acts as we are used to---errors are not suppressed. IDEMPOTENT suppresses some errors, including duplicate-key errors (1062) and no-key-found errors.

• Server System Variables
• Tuning Server Parameters

• Replication Slave Options and Variables : slave_exec_mode

Controls whether IDEMPOTENT or STRICT mode is used in replication conflict resolution and error checking. IDEMPOTENT mode causes suppression of duplicate-key and no-key-found errors. Beginning with MySQL 5.1.23-ndb-6.2.14 and MySQL 5.1.24, this mode should be employed in multi-master replication, circular replication, and some other special replication scenarios. STRICT mode is the default, and is suitable for most other cases. Note : MySQL Cluster ignores any value explicitly set for slave_exec_mode, and always treats it as IDEMPOTENT.

SET slave_exec_mode=IDEMPOTENT; #1229 - Variable 'slave_exec_mode' is a GLOBAL variable and should be set with SET GLOBAL
SET GLOBAL slave_exec_mode=IDEMPOTENT;
SHOW VARIABLES;
-- Ne change rien à l'erreur :
-- Last_Errno 	1364
-- Last_Error 	Error 'Field 'myField' doesn't have a default value' on query. Default database: 'myDataBase'. Query: 'REPLACE INTO `myTable` (`table_num`,`ip`) VALUES('0','192.168.0.253')

Replication Slave Options and Variables : slave_skip_errors Normally, replication stops when an error occurs on the slave. This gives you the opportunity to resolve the inconsistency in the data manually. This option tells the slave SQL thread to continue replication when a statement returns any of the errors listed in the option value.

SET GLOBAL slave_skip_errors = 1364;  #1238 - Variable 'slave_skip_errors' is a read only variable

• SET Syntax

slave-skip-errors

Pour ignorer des erreurs (mais perdre les transactions concernées) telle que le STRICT MODE 1364 Error 'Field 'myField' doesn't have a default value' on query. slave_skip_errors

Dans my.cnf :

slave-skip-errors = 1364

sql mode 	
slave skip errors 	OFF
=>
sql mode 	
slave skip errors 	1 364

Par exemple, dans le cas où un administrateur supprime un utilisateur MySQL utilisé dans les triggers sur votre serveur Master, vous aurez la non erreur (Last_Errno:0) suivante :

Last_Errno 	0
Last_Error 	Query caused different errors on master and slave. Error on master: message (format)='The user specified as a definer ('%-.64s'@'%-.64s') does not exist' error code=1449 ; Error on slave: actual message='no error', error code=0. Default database: 'ma_base'. Query: 'REPLACE INTO ma_table ...

MySQL Error 1449 : ER_NO_SUCH_USER

Error: 1449 SQLSTATE: HY000 (ER_NO_SUCH_USER)
Message: The user specified as a definer ('%s'@'%s') does not exist

Cette erreur provient du log binaire du serveur :

# at 78574258
#120309 12:17:18 server id 1  end_log_pos 78575944      Query   thread_id=14301750      exec_time=0     error_code=1449
use ma_base/*!*/;
SET TIMESTAMP=1331291838/*!*/;
SET @@session.sql_mode=0/*!*/;
/*!\C latin1 *//*!*/;
SET @@session.character_set_client=48,@@session.collation_connection=48,@@session.collation_server=48/*!*/;
REPLACE INTO ma_table ...

Pour y remédier, il vous faudra configurer l'option slave-skip-errors dans votre my.cnf, avant de relancer MySQL :

slave-skip-errors = 1449

N'oubliez ou/pas de désactiver par la suite cette option.

Cas d'école à la con : transformation des tables MyISAM en InnoDB, puis erreurs dans le slave

• Replication and Server SQL Mode For best results, you should always use the same server SQL mode on the master and on the slave
• Server SQL Modes
• Le mode SQL du serveur

Bien que nous ayons le même sql_mode sur le serveur master et slave MySQL, suite à des transformation du moteur de table de MyISAM vers InnoDB, le master autorisait à tord des insertions avec champs non spécifiés dans le statement et ayant une valeur non nulle mais non définie dans la table.

SET @@session.sql_mode=537001984; -- MYSQL40,HIGH_NOT_PRECEDENCE
SET @@session.sql_mode=6291456; -- STRICT_TRANS_TABLES,STRICT_ALL_TABLES 
SET @@session.sql_mode=2097152; -- STRICT_TRANS_TABLES
INSERT INTO maTable (id) VALUE (123);
-- ERROR 1364 (HY000): Field 'maColonne' doesn't have a default value

Notre solution fut de dumper/injecter les tables InnoDB pour retrouver l'utilisation normale du STRICT_TRANS_TABLE (sql_mode 2097152)

-- Lister les colonnes dont la valeur par défaut est non spécifié
SELECT *
FROM information_schema.COLUMNS
WHERE
COLUMN_DEFAULT IS NULL
AND IS_NULLABLE = 'NO'
-- AND DATA_TYPE NOT IN (	'bigint', 'int', 'smallint', 'tinyint', 'bit', 'enum' )
LIMIT 0,5000
;

Le concept de backup MySQL

• MySQL Backup Concepts for (Linux) System Administrators – Part 1
  • mysqldump
  • File System Snapshots
    • perform a 'FLUSH TABLES"
    • shutdown the database
    • create the snapshot
    • restart the database
  • xtrabackup
• Getting use of Slave in MySQL Replication I use another concept: On master, I issue SHOW MASTER STATUS after write, save File and Position to session variable and if client wants to read written data, I issue DO MASTER_POS_WAIT() on the slave. So master is exclusively used for writting and slave for writting. With this concept, it could take long for the slave to catch up. Thus instead of MASTER_POS_WAIT(), there could be SHOW SLAVE STATUS and if (Master_Log_File, Read_Master_Log_Pos) is lower than session variables, data would be read from master, instead from slave.
• MASTER_POS_WAIT() MASTER_POS_WAIT()