Datalbi  
Créé le 16/04/2016 par Laurent Bourcier

Mise en oeuvre de la replication MySQL avec GTID

Les GTID (Global Transaction Identifiers) sont apparus dans la version 5.6.5 de MySQL.

C'est cette réplication qui est décrite ici. Par défaut, les GTIDs ne sont pas activés.

1. Configuration des serveurs MASTER et SLAVE

Serveur MASTER :

# rm -f /var/lib/mysql/vmmysql02-binlog*

# service mysql stop
Shutting down MySQL.. SUCCESS!

vi /usr/my.cnf
server-id = 1
log-bin = vmmysql02-binlog
gtid-mode = on
log-slave-updates = on
enforce-gtid-consistency = on

# service mysql start
Starting MySQL. SUCCESS!

create user 'repl'@'%' identified by 'repl';
Query OK, 0 rows affected (0,01 sec)

grant replication slave on *.* to 'repl'@'%';
Query OK, 0 rows affected (0,00 sec)

Serveur SLAVE :

# service mysql stop
Shutting down MySQL.. SUCCESS!

vi /usr/my.cnf
server-id = 2
log-bin = vmmysql03-binlog
gtid-mode = on
log-slave-updates = on
enforce-gtid-consistency = on

# service mysql start
Starting MySQL. SUCCESS!

2. Copie des donnees MASTER VERS LE SLAVE

Serveur MASTER :

mysql> flush tables with read lock;
Query OK, 0 rows affected (0,00 sec)

(autre session)
# mysqladmin -u root -p shutdown

# cd /var/lib/mysql
# tar cf /tmp/data.tar *

# service mysql start
Starting MySQL.. SUCCESS!

Serveur SLAVE :

# service mysql stop
Shutting down MySQL.. SUCCESS!

# scp 192.168.56.132:/tmp/data.tar /tmp
root@192.168.56.132's password:
data.tar                                           100%  175MB  58.3MB/s   00:03

cd /var/lib/mysql

# tar cf /tmp/data_backup.tar *
# rm -rf *
# tar xf /tmp/data.tar
# rm -f vmmysql02-binlog* *err auto.cnf

# service mysql start
Starting MySQL.. SUCCESS!

3. Demarrage de la replication

Serveur SLAVE :

change master to
  master_host = '192.168.56.132',
  master_port = 3306,
  master_user = 'repl',
  master_password = 'repl',
  master_auto_position = 1
;
Query OK, 0 rows affected, 2 warnings (0,05 sec)

mysql> show warnings;
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                                                                                                                                                                                              |
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note  | 1759 | Sending passwords in plain text without SSL/TLS is extremely insecure.                                                                                                                                                                                                               |
| Note  | 1760 | Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information. |
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0,00 sec)

mysql> start slave;
Query OK, 0 rows affected (0,04 sec)

Serveur MASTER :

mysql> show master status\G
*************************** 1. row ***************************
             File: vmmysql02-binlog.000003
         Position: 120
     Binlog_Do_DB:
 Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0,00 sec)

mysql> show slave hosts\G
*************************** 1. row ***************************
 Server_id: 2
      Host:
      Port: 3306
 Master_id: 1
Slave_UUID: b02ffcf6-02f8-11e6-a63c-080027f36211
1 row in set (0,00 sec)

Serveur SLAVE :

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.56.132
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: vmmysql02-binlog.000002
          Read_Master_Log_Pos: 151
               Relay_Log_File: vmmysql03-relay-bin.000002
                Relay_Log_Pos: 375
        Relay_Master_Log_File: vmmysql02-binlog.000002
             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: 151
              Relay_Log_Space: 583
              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_UUID: b5a80632-8cb0-11e5-a2f1-080027f36211
             Master_Info_File: /var/lib/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set:
                Auto_Position: 1
1 row in set (0,00 sec)