Accueil > Bases de données, Tutoriel > Réplication MySQL

Réplication MySQL

25/05/2021 Categories: Bases de données, Tutoriel Tags: , ,
Print Friendly, PDF & Email

Source: Howto MySQL

Préparation

  • Prérequis : disposer de deux serveurs MySQL avec un datadir identique

Dans le cas où le futur master est en production et ne peut être arrété :

# mysqldump --master-data --all-databases > mysql.dump

--master-data ajoute un CHANGE MASTER TO dans le dump contenant les informations nécessaires au slave sur les logs (nom de fichier et position). Cette option implique--lock-all-tables qui bloquera toutes les tables pendant le dump.

  • Autoriser les connections MySQL distantes
  • Activer les logs binaires sur chaque serveur : log_bin = /var/log/mysql/mysql-bin.log dans le format mixed : binlog_format = mixed
  • Positionner un server-id différent sur chaque serveur (a priori, ne pas utiliser 0…)
  • Créer un utilisateur dédié pour la réplication sur chaque serveur avec le droit REPLICATION SLAVE :grant replication slave on *.* to repl@'%' identified by 'XXX';

Activation

  • Exécuter SHOW MASTER STATUS sur le premier serveur (qui sera le serveur master en mode master-slave) et noter les informations
  • Sur le serveur B (le slave en mode master-slave), exécuter :
CHANGE MASTER TO
   MASTER_HOST='$MASTER_IP',
   MASTER_USER='repl',
   MASTER_PASSWORD='XXX',
   MASTER_LOG_FILE='mysql-bin.NNNNNN',
   MASTER_LOG_POS=NNN;
  • Pour exclure une base de la réplication, dans /etc/mysql/my.cnf :
    binlog-ignore-db = mysql
  • Puis démarrer la réplication sur le serveur B avec la commande : START SLAVE
  • Enfin, exécuter SHOW SLAVE STATUS pour vérifier le bon fonctionnement

Désactivation

Pour supprimer toute trace de réplication (sauf si des infos sont en dur dans la configuration) :

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

Pour éviter que la réplication démarre automatiquement au démarrage, on ajoutera dans la configuration :

skip-slave-start

Trucs et astuces pour la réplication MySQL

Astuce 0 : Une astuce parfois très utile est la possibilité d’exécuter des requêtes qui ne seront pas prises en compte par le binlog (et donc non répliquée !). Cela nécessite le droit SUPER :

mysql> SET sql_log_bin = 0;

Astuce 1 : Pour divers raisons (notamment la réplication de données déjà répliquées !), on devra activer l’option suivante dans le my.cnf :

log-slave-updates

Astuce 2 : Sauter une requête déjà présente dans les binlog sur le slave (à tester) : https://stackoverflow.com/questions/17701524/mysql-replication-skip-statement-is-it-possible

Etapes supplémentaires pour une réplication master-master

  • Positionner la directive auto-increment-increment = 10 sur chaque serveur
  • Positionner la directive auto-increment-offset avec une valeur numérique différente sur chaque serveur

Exemple : auto-increment-offset = 1 sur le serveur A, auto-increment-offset = 2 sur le serveur B

  • Effectuer l’étape Activation dans le sens A->B et B->A

Résolution des erreurs lors de la réplication

On vérifie les erreurs avec les commandes SHOW SLAVE STATUS et SHOW MASTER STATUS. En cas d’erreur, il faut « simplement » résoudre l’erreur, puis relancer la réplication avec la commande START SLAVE. Voici quelques erreurs possibles

  • Si l’on veut zapper l’erreur en cours : SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; START SLAVE;
  • Incorrect key file for table ‘./base/table.MYI’; try to repair it : il faut réparer la table concernée
  • Duplicate entry ‘NNNNNN’ for key N : une solution *peut* être de supprimer la ligne concernée (ou de zapper l’erreur)
  • Si pour une raison ou un autre, on a plein de DUPLICATE ENTRY mais que l’est sûr‘ de vouloir les ignorer, on peut faire cela en redémarrant MySQL avec le paramètre : slave-skip-errors = 1062 ; on peut faire également cela avec d’autres types d’erreurs. Malheureusement, il faut forcément redémarrer MySQL car cette commande ne se fait pas à chaud :http://bugs.mysql.com/bug.php?id=35611
  • En cas d’erreur du type [ERROR] Error reading packet from server: Client requested master to start replication from impossible position (server_errno=1236), cela signifie que la position indiquée sur le binlog du master est impossible à récupérer. On peut le vérifier avec une commande du type mysqlbinlog mysqld-bin.00123 --start-position=251 sur le master. Si l’on constate que le binlog est corrompu avec des erreurs du type ERROR: Error in Log_event::read_log_event(): ‘read error’ # Warning: this binlog is either in use or was not closed properly. ou ERROR: Error in Log_event::read_log_event(): ‘Event too small’, data_len: 0, event_type: 0 l’idée sera d’identifier les requêtes non jouées sur le slave dans le binlog corrompu (le Relay_Master_Log_File via un SHOW SLAVE STATUS) et de les rejouer (cf procédure décrite) puis de passer au binlog suivant via une commande du type CHANGE MASTER TO MASTER_LOG_FILE=’mysql-bin.000124′ , MASTER_LOG_POS=106; START SLAVE; (la position à indiquer est souvent 106, cf mysqlbinlog mysql-bin.000124). Si l’on juge cela non nécessaire (données non critiques), on pourra bien sûr passer directement au binlog suivant en ignorant les requêtes du binlog corrompu. Bien sûr, suite à ces manipulations risquées, on vérifiera ensuite la cohérence de la base de données répliquée (COUNT(*) ou outils plus avancés).
  • En cas d’erreur du type Could not parse relay log event entry. The possible reasons are: the master’s binary log is corrupted (you can check this by running ‘mysqlbinlog’ on the binary log), the slave’s relay log is corrupted (you can check this by running ‘mysqlbinlog’ on the relay log), a network problem, or a bug in the master’s or slave’s MySQL code. If you want to check the master’s binary log or slave’s relay log, you will be able to know their names by issuing ‘SHOW SLAVE STATUS’ on this slave. : souvent un binlog corrompu (le Relay_Master_Log_File via un SHOW SLAVE STATUS), appliquer la même solution que ci-dessus !! Jusqu’à MySQL <= 5.1 au moins, changer la position dans un Relay_log avec un CHANGE MASTER TO ne marche pas. Voir #ChangementdelapositiondansunRelay_log.
  • Dans certains cas exceptionnels, une solution radicale est de réinitialiser la réplication avec un STOP SLAVE; RESET SLAVE; START SLAVE; Attention, cela doit être fait dans de très rares cas maîtrisés (attention notamment aux conflits DUPLICATE ENTRY que cela risque de provoquer).
  • Si un SHOW SLAVE STATUS ne retourne pas d’erreur mais que la réplication ne se fait pas, les logs du slave peuvent contenir une erreur du type :
    [Note] Slave I/O thread: Failed reading log event, reconnecting to retry, log 'mysql-bin.003357' at position 389449
[Note] Slave: received end packet from server, apparent master shutdown:

    Il se peut que le master se réplique sur 2 slaves ayant un server-id identique !

Changement de la position dans un Relay_log

À faire uniquement si en tentant de changer la position d’un Relay_log sur un slave, vous obtenez cette erreur :

Error initializing relay log position: Could not find target log during
relay log initialization

Il faut alors stopper le processus slave de réplication :

mysql> STOP SLAVE;

Puis éditer (en gardant une sauvegarde) le fichier ${datadir}/relay-log.info. La première ligne correspond au Relay_Log_File, la seconde au Relay_Log_Pos. Redémarrer MySQL.

Diagnostic des erreurs

Error 24

Si vous obtenez des erreurs de ce type, lors d’un mysqldump par exemple :

mysqldump: Got error: 1016: Can't open file: './db/table.frm' (errno: 24) when using LOCK TABLES
mysqldump: Got error: 23: Out of resources when opening file '.\db\table.MYD' (Errcode: 24) when using LOCK TABLES

C’est que votre serveur MySQL tente d’ouvrir trop de fichiers simultanément.

Pour augmenter le nombre maximal de fichiers pouvant être ouverts, vous pouvez ajuster le paramètre suivant dans la section [mysqld] du fichier my.cnf, dans la limite permise par votre système d’exploitation :

open_files_limit = 2048

La valeur par défaut étant de 1024.

Note : inutile de positionner une valeur pour ulimit -n dans les scripts de démarrage, mysqld_safe s’en charge tout seul.

Error 2020

Si vous obtenez l’erreur suivante lors d’un mysqldump :

mysqldump: Error 2020: Got packet bigger than 'max_allowed_packet' bytes when dumping table `mytable` at row: 6542

Augmentez la valeur de max_allowed_packet dans la section [mysqldump] du fichier my.cnf :

[mysqldump]
max_allowed_packet      = 64M

Error 1267

Si vous obtenez une erreur du type :

ERROR 1267 (HY000): Illegal mix of collations (binary,IMPLICIT) and (utf8_bin,NONE) for operation 'like'

C’est qu’il y a souci entre votre charset client (character_set_client, collation_connection) et votre requête. Vous pouvez les ajuster avec des commandes du type :

mysql> set character_set_client=utf8;
Query OK, 0 rows affected (0.00 sec)
mysql> set collation_connection=utf8_unicode_ci;
Query OK, 0 rows affected (0.00 sec)

Dans certain cas (création d’une vue par exemple), cela peut venir d’une version de MySQL trop ancienne (on a constaté des requêtes qui passaient en 5.1 mais pas en 5.0).

Column count of mysql.proc is wrong

Si vous avez des erreurs de ce type :

[ERROR] Column count of mysql.db is wrong. Expected 22, found 20. The table is probably corrupted
[ERROR] mysql.user has no `Event_priv` column at position 29
[ERROR] Event Scheduler: An error occurred when initializing system tables. Disabling the Event Scheduler.
[ERROR] Column count of mysql.proc is wrong. Expected 20, found 16. The table is probably corrupted

Cela signifie que les tables de la base mysql ne correspondent pas à la version de MySQL en cours. Vous avez sûrement mis à jour MySQL ou réinjecter des données d’une autre base.

Plusieurs solutions, réinjecter les tables incorrectes ou utilisez mysql_upgrade qui est sensé adapter les tables.

Désactiver la complétion avec l’option –skip-auto-rehash

En cas de souci lors de la connexion en ligne de commande MySQL, vous pouvez désactiver la complétion automatique. En effet, cette complétion peut créer de soucis si certaines tables sont corrompues :

$ mysql --skip-auto-rehash

InnoDB: ERROR: the age of the last checkpoint is NNNNN

Si vous avez une erreur du type :

mysqld: 120313 12:16:10  InnoDB: ERROR: the age of the last checkpoint is 9433587,
mysqld: InnoDB: which exceeds the log group capacity 9433498.
mysqld: InnoDB: If you are using big BLOB or TEXT rows, you must set the
mysqld: InnoDB: combined size of log files at least 10 times bigger than the
mysqld: InnoDB: largest such row.

Il faut augmenter le log InnoDB, notamment innodb_log_file_size. Attention, il faudra ensuite stopper MySQL et effacer les fichiers ib_logfile* ! Pour plus de détails, voir :http://www.mysqlperformanceblog.com/2008/11/21/how-to-calculate-a-good-innodb-log-file-size/ et http://dev.mysql.com/doc/refman/5.1/en/innodb-parameters.html

InnoDB: Error: trying to load index PRIMARY for table … but the index tree has been freed!

Si vous obtenez une erreur du type :

InnoDB: Error: trying to load index PRIMARY for table foo/bar
InnoDB: but the index tree has been freed!
121222 11:28:48 - mysqld got signal 11;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help diagnose
the problem, but since we have already crashed, something is definitely wrong
and this may fail.

key_buffer_size=713031680
read_buffer_size=131072
max_used_connections=31
max_connections=384
threads_connected=29
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 1531901 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

thd=0x84dc0a0
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
Cannot determine thread, fp=0x84dc0a0, backtrace may not be correct.
Bogus stack limit or frame pointer, fp=0x84dc0a0, stack_bottom=0x44660000, thread_stack=196608, aborting backtrace.
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd->query at 0x7f0d9c284ba0  is invalid pointer
thd->thread_id=355
The manual page at http://www.mysql.com/doc/en/Crashing.html contains
information that should help you find out what is causing the crash.

Cela semble être une table corrompue, et a priori il faut réinstaurer la table concernée. Se connecter en ligne de commande avec l’option mysql –skip-auto-rehash puis supprimer la table concernée (voir ci-dessous si besoin) et réinjecter là.

Diverses astuces sont listées ici : http://dba.stackexchange.com/questions/23296/mysql-innodb-index-in-swap

InnoDB: Souci lors de la création/suppression de table InnoDB

On suppose que vous utilisez bien l’option innodb_file_per_table comme conseillé sur cette page.

Pour supprimer une table problématique, vous pouvez éteindre MySQL et supprimer le fichier .frm correspondant ! Néanmoins la table sera toujours référencée par InnoDB, et vous devez créer un .frm simple pour tromper le moteur :

mysql> create table foo(foo int) ENGINE=InnoDB;
# /etc/init.d/mysql stop
# cp /var/lib/mysql/foo/foo.frm /var/lib/mysql/foo/TABLE.frm

Quelques informations supplémentaire sur : http://dev.mysql.com/doc/refman/5.1/en/innodb-troubleshooting-datadict.html

Error 121 : InnoDB : ERROR 1005 (HY000): Can’t create table ‘./foo/bar.frm’ (errno: 121)

Il s’agit d’un problème avec les clés. Par exemple, les clés que vous crééez sont déjà référencée par InnoDB. Cela peut ainsi se produire si vous avez du supprimer une table InnoDB via son fichier .frm

Une astuce possible est simplement de créer la table sans ses clés. Une fois créée, vous devez voir les clés avec un SHOW CREATE TABLE. À vous de voir si vous devez les modifier/supprimer.

InnoDB: Failing assertion: addr.page == FIL_NULL || addr.boffset >= FIL_PAGE_DATA

Si le service MySQL/InnoDB refuse de démarrer à cause d’une erreur du type :

mysqld: InnoDB: Starting in background the rollback of uncommitted transactions
mysqld: 140130 16:01:44  InnoDB: Rolling back trx with id 13B87781, 3 rows to undo
mysqld: 140130 16:01:44  InnoDB: Assertion failure in thread 140516188849920 in file fut0lst.ic line 83
mysqld: InnoDB: Failing assertion: addr.page == FIL_NULL || addr.boffset >= FIL_PAGE_DATA

C’est que la base est corrompue…

Il faut sauvegarder le datadir. Puis tenter de redémarrer avec l’option innodb_force_recovery=1 puis innodb_force_recovery=2 puis innodb_force_recovery=3 etc. jusqu’à ce que le service démarre. Attention, à partir de innodb_force_recovery=3 vous devrez sûrement ajouter innodb_purge_threads=0 :

innodb_force_recovery = 3
innodb_purge_threads = 0

Dès que le service démarre (il sera peut être en read-only), faites un dump de toutes vos bases MySQL.

Vous devrez sûrement repartir de zéro en recréant un datadir tout neuf (mysql_install_db –datadir=/var/lib/mysql.new) et en réinjectant votre dump.

Voir http://dev.mysql.com/doc/refman/5.5/en/forcing-innodb-recovery.html

Error 13 : mysqld: #007/usr/sbin/mysqld: File ‘/var/log/mysql/mysql-bin.00NNNN’ not found (Errcode: 13)

Si vous obtenez des erreurs de ce type :

mysqld: #007/usr/sbin/mysqld: File '/var/log/mysql/mysql-bin.005655' not found (Errcode: 13)
mysqld: 130202 19:49:05 [ERROR] Failed to open log (file '/var/log/mysql/mysql-bin.005655', errno 13)
mysqld: 130202 19:49:05 [ERROR] Could not open log file
mysqld: 130202 19:49:05 [ERROR] Can't init tc log
mysqld: 130202 19:49:05 [ERROR] Aborting

C’est que MySQL n’arrive pas à accéder au dernier binlog. Cela peut par exemple être une question de droits. Pour vérifier que tout est correct, faire :

# sudo -u mysql head /var/log/mysql/mysql-bin.00NNNN

Une erreur étrange mais classique est que le fichier binlog prenne les droits de root. On le corrigera ainsi :

# chown mysql:adm /var/log/mysql/mysql-bin.00NNNN

ERROR 130 (HY000): Incorrect file format '[…]'

Si vous obtenez l’erreur ci-dessus, lors d’un mysqldump par exemple, et que les fichier ${mysql_datadir}/base/table.{MYD,MYI} sont vides mais pas le .frm, il faut réparer la table comme ceci :

# mysqlcheck --auto-repair --check --use-frm <base> <table>

 

Lire aussi:  MySQL show users - how to show the users in a MySQL database
Categories: Bases de données, Tutoriel Tags: , ,
Les commentaires sont fermés.