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