Sommaire
Mise en place de la réplication
Config' côté maître
Fichier /etc/mysql/conf.d/local.cnf:
[mysqld] language = /usr/share/mysql/french bind-address = 0.0.0.0 log_slow_queries = /var/log/mysql/mysql-slow.log # réplication master->slave server-id = 130 log_bin = /var/log/mysql/mysql-bin.log # binlog_do_db : a rajouter pour chaque base répliquée binlog_do_db = drupal [mysqldump] quote-names
Commandes MySQL (1/2):
mysql> GRANT REPLICATION SLAVE ON *.* TO 'auf_repl'@'prod-db02-idneuf.vpc02.auf' IDENTIFIED BY 'xxxxxxxxxxxxxxxx'; mysql> FLUSH PRIVILEGES; mysql> FLUSH TABLES WITH READ LOCK; mysql> SHOW MASTER STATUS; +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000001 | 719 | drupal | | +------------------+----------+--------------+------------------+ 1 row in set (0.00 sec)
Faire ensuite un dump de la base a répliquer :
mysqldump --opt drupal > drupal.sql
Commandes MySQL (2/2):
mysql> UNLOCK TABLES;
Config' côté esclave
Fichier /etc/mysql/conf.d/local.cnf :
[mysqld] language = /usr/share/mysql/french bind-address = 0.0.0.0 log_slow_queries = /var/log/mysql/mysql-slow.log #Empêcher tout utilisateur non root l’écriture sur le serveur read-only = 1 # réplication master->slave server-id = 131 relay-log = /var/log/mysql/mysql-relay-bin.log log_bin = /var/log/mysql/mysql-bin.log # binlog_do_db : a rajouter pour chaque base répliquée binlog_do_db = drupal [mysqldump] quote-names
Créer la base a répliquée a partir du dump précédemment créer :
mysql drupal < drupal.sql
Commandes MySQL :
mysql> STOP SLAVE; mysql> CHANGE MASTER TO MASTER_HOST='prod-db01-idneuf.vpc02.auf',MASTER_USER='auf_repl',MASTER_PASSWORD='xxxxxxxxxxxxxxxx',MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=719; mysql> START SLAVE; mysql> SHOW SLAVE STATUS\G; *************************** 1. row *************************** Slave_IO_State: Connecting to master Master_Host: prod-db01-idneuf.vpc02.auf Master_User: auf_repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000001 Read_Master_Log_Pos: 719 Relay_Log_File: mysql-relay-bin.000001 Relay_Log_Pos: 4 Relay_Master_Log_File: mysql-bin.000001 Slave_IO_Running: Connecting Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 719 Relay_Log_Space: 107 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: NULL Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 2005 Last_IO_Error: error connecting to master 'auf_repl@prod-db01-idneuf.vpc02.auf:3306' - retry-time: 60 retries: 86400 Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 130
Relancer la réplication en cas d'erreur
L' Erreur
mysql> show slave status\G; *************************** 1. row *************************** Slave_IO_State: Master_Host: prod-db01-idneuf.vpc02.auf Master_User: auf_repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000032 Read_Master_Log_Pos: 13470437 Relay_Log_File: mysql-relay-bin.000084 Relay_Log_Pos: 13470513 Relay_Master_Log_File: mysql-bin.000032 Slave_IO_Running: No Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 13470367 Relay_Log_Space: 13470739 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: NULL Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 1236 Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'binlog truncated in the middle of event; consider out of disk space on master; the first event 'mysql-bin.000027' at 4521, the last event read from '/var/log/mysql/mysql-bin.000032' at 13470437, the last byte read from '/var/log/mysql/mysql-bin.000032' at 13470456.' Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 130 1 row in set (0.00 sec) ERROR: No query specified
La solution
Sur le master :
mysql> show master status; +------------------+----------+---------------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+---------------------+------------------+ | mysql-bin.000056 | 267388 | drupal,orioai,mysql | | +------------------+----------+---------------------+------------------+ 1 row in set (0.00 sec)
Récupérer les valeur File et Positon.
Elle serviront dans les commandes suivantes sur le/les slaves.
Sur le slave :
mysql> stop slave; Query OK, 0 rows affected (0.02 sec) mysql> CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000056', MASTER_LOG_POS=267388; Query OK, 0 rows affected (0.06 sec) mysql> start slave; Query OK, 0 rows affected (0.04 sec)
Versification :
mysql> show slave status\G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: prod-db01-idneuf.vpc02.auf Master_User: auf_repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000056 Read_Master_Log_Pos: 267388 Relay_Log_File: mysql-relay-bin.000002 Relay_Log_Pos: 253 Relay_Master_Log_File: mysql-bin.000056 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 267388 Relay_Log_Space: 409 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 130 1 row in set (0.00 sec) ERROR: No query specified
InnoDB et ibdata1
Configuratuion de MySQL
Il est possible de réduire la taille du fichier ibdata1 présent dans /var/lib/mysql en les déplacant dans chaque bases.
Ainsi, pour chaque tables, un fichier ce ce type sera associé.
Pour cela, il faut rajouter dans le fichier de configuration les directives suivantes :
innodb_file_per_table innodb_flush_method=O_DIRECT innodb_log_file_size=250M innodb_buffer_pool_size=1G
* Il est important de s'assurer que la valeur d' innodb_log_file_size est de 25% égale à celle de innodb_buffer_pool_size.
* Le paramètre innodb_flush_method=O_DIRECT permet d'assurer une meilleure fiabilité des données, notamment en cas de crash.
En effet, a chaque accès des bases ou des logs, les données sont immédiatement écrites sur disque.
Cependant, cela nécessite deux écritures : 1 écriture pour les nouvelles données et 1 écriture pour mettre a jour le timestamp de l'inode.
En résumé, cette option présente ces avantages et inconvénient :
- Meilleure stabilité
- Consistance des données
- Plus lent (2 écritures)
Activer innodb_file_per_table avec des BDD esistantes
1. Faire un dump de toutes les BDD
2. Faire un DROP de toutes les BDD sauf mysql et performance_schema
3. Stopper le service MySQL
4. Ajouter la conf ci-dessus au serveur MySQL
5. Supprimer ibdata1, ib_logfile0 et ib_logfile1 dans /var/lib/mysql
6. Démarrer MySQL
7. Importer les bases précédemment dumpées
Sources :
Notes
Reste à documenter (dans la section des Notes) :
le choix du server-id = 130 (comment on détermine cette valeur)
Le choix du serveur ID est arbitraire et peut donc prendre n'importe quelle valeur.
la nécessité ou non du paramètre MASTER_LOG_FILE='mysql-bin.000001'
Ce paramètre permet d'indiquer au slave quel fichier bin log est utilisé par le master.
Afin de déterminer la valeur de ce paramètre, il faut exécuter sur le master la commande SQL : SHOW MASTER STATUS (voir ci-dessous) et récupérer la valeur de la colonne "File".
la signification du paramètre MASTER_LOG_POS=719 (comment on détermine cette valeur)
Ce paramètre permet d'indiquer au slave la dernière transaction enregistrée par le master.
Afin de déterminer la valeur de ce paramètre, il faut exécuter sur le master la commande SQL : SHOW MASTER STATUS (voir ci-dessous) et récupérer la valeur de la colonne "Position".
- le séquencement des commandes (doit-on par exemple rattacher l'esclave entre le LOCK et le UNLOCK du maître)
Le LOCK des tables sert a verrouiller les bases en RO afin d'effectuer un dump de la base a répliquée.
Le UNLOCK peut être fait avant de rajouter le slave.
- la généralisation pour l'ajout un esclave supplémentaire après coup
La procedure est identique afin de rajouter un 2e slave.
- les éventuelles URL de pages web de référence utilisées
Reference : How To Set Up Master Slave Replication in MySQL