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 :

• The best way to reduce the size of ibdata in mysql

• Clarification on MySQL innodb_flush_method variable

Notes