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.
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!
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!
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)