Installation master/slave

Prérequis

• Avoir deux serveurs mariadb

Master

• Ajouter ou modifier ce qui suit dans le fichier /etc/mysql/mariadb.conf.d/50-server.cnf
  

  [mysqld]
  bind-address = 0.0.0.0
  server-id = 1
  log_bin = mysql-bin

• Redémarrer mariadb
• Création de l'utilisateur de réplication
  

  CREATE USER 'repli'@'%' IDENTIFIED BY 'STRONGPASSWORD';
  GRANT REPLICATION SLAVE ON *.* TO 'repli'@'%';
  MariaDB [(none)]> show master status;
  +------------------+----------+--------------+------------------+
  | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
  +------------------+----------+--------------+------------------+
  | mysql-bin.000001 |      663 |              |                  |
  +------------------+----------+--------------+------------------+
  1 row in set (0.000 sec)

SLAVE

• Ajouter ou modifier ce qui suit dans le fichier /etc/mysql/mariadb.conf.d/50-server.cnf
  

  [mysqld]
  bind-address = 0.0.0.0
  server-id = 2
  log_bin = mysql-bin

• Redémarrer mariadb
• Mise en place de la réplication
  

  CHANGE MASTER TO
      -> MASTER_HOST='172.16.0.5',
      -> MASTER_USER='repli',
      -> MASTER_PASSWORD='STRONGPASSWORD',
      -> MASTER_LOG_FILE='mysql-bin.000001',
      -> MASTER_LOG_POS=663;
  start slave;
  SHOW SLAVE STATUS\G
  *************************** 1. row ***************************
                  Slave_IO_State: Waiting for master to send event
                     Master_Host: 172.16.0.5
                     Master_User: repli
                     Master_Port: 3306
                   Connect_Retry: 60
                 Master_Log_File: mysql-bin.000001
             Read_Master_Log_Pos: 663
                  Relay_Log_File: mysqld-relay-bin.000002
                   Relay_Log_Pos: 555
           Relay_Master_Log_File: mysql-bin.000001
                Slave_IO_Running: Yes
               Slave_SQL_Running: Yes
            Replicate_Rewrite_DB: 
                 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: 663
                 Relay_Log_Space: 865
                 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: 1
                  Master_SSL_Crl: 
              Master_SSL_Crlpath: 
                      Using_Gtid: No
                     Gtid_IO_Pos: 
         Replicate_Do_Domain_Ids: 
     Replicate_Ignore_Domain_Ids: 
                   Parallel_Mode: optimistic
                       SQL_Delay: 0
             SQL_Remaining_Delay: NULL
         Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
                Slave_DDL_Groups: 0
  Slave_Non_Transactional_Groups: 0
      Slave_Transactional_Groups: 0
  1 row in set (0.000 sec)

  
  

TEST

• Sur le Master céer une BDD
  

  create database test;
  show databases;
  +--------------------+
  | Database           |
  +--------------------+
  | information_schema |
  | mysql              |
  | performance_schema |
  | sys                |
  | test               |
  +--------------------+
  5 rows in set (0.001 sec)
  

  
  
• Vérifier que la base de donné créée sur le master est bien répliqué
  

  show databases;
  +--------------------+
  | Database           |
  +--------------------+
  | information_schema |
  | mysql              |
  | performance_schema |
  | sys                |
  | test               |
  +--------------------+
  5 rows in set (0.001 sec)