<> = 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 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 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 : * [[http://dba.stackexchange.com/questions/8982/what-is-the-best-way-to-reduce-the-size-of-ibdata-in-mysql| The best way to reduce the size of ibdata in mysql]] * [[http://dba.stackexchange.com/questions/1568/clarification-on-mysql-innodb-flush-method-variable|Clarification on MySQL innodb_flush_method variable]] == Notes == {{{#!wiki caution '''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 : [[https://www.digitalocean.com/community/tutorials/how-to-set-up-master-slave-replication-in-mysql|How To Set Up Master Slave Replication in MySQL | DigitalOcean]] }}}