Les GTID (Global Transaction Identifiers) sont apparus dans la version 5.6.5 de MySQL.
Pour les versions précédentes, la synchronisation des slaves est suivie par le nom et la position dans le binlog sur serveur master. C'est cette réplication qui est décrite ici.
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 # service mysql start Starting MySQL. SUCCESS! mysql> show global variables like 'server_id'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | server_id | 1 | +---------------+-------+ mysql> show global variables like 'log_bin'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | log_bin | ON | +---------------+-------+ mysql> show global variables like 'binlog_format'; +---------------+-----------+ | Variable_name | Value | +---------------+-----------+ | binlog_format | STATEMENT | +---------------+-----------+ mysql> show binary logs; +-------------------------+-----------+ | Log_name | File_size | +-------------------------+-----------+ | vmmysql02-binlog.000001 | 120 | +-------------------------+-----------+ 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 # service mysql start Starting MySQL. SUCCESS!
Serveur MASTER :
mysql> flush tables with read lock; Query OK, 0 rows affected (0,00 sec) mysql> show master status; +-------------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +-------------------------+----------+--------------+------------------+-------------------+ | vmmysql02-binlog.000002 | 212 | | | | +-------------------------+----------+--------------+------------------+-------------------+ 1 row in set (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 :
mysql> change master to master_host = '192.168.56.132', master_port = 3306, master_user = 'repl', master_password = 'repl', master_log_file = 'vmmysql02-binlog.000002', master_log_pos = 212 ; 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.000003
Read_Master_Log_Pos: 120
Relay_Log_File: vmmysql03-relay-bin.000004
Relay_Log_Pos: 290
Relay_Master_Log_File: vmmysql02-binlog.000003
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: 120
Relay_Log_Space: 637
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: 0
1 row in set (0,00 sec)
mysql> show global variables like 'master_info_repository';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| master_info_repository | FILE |
+------------------------+-------+
# cat /var/lib/mysql/master.info
23
vmmysql02-binlog.000005
427
192.168.56.132
repl
repl
3306
60
0
0
1800.000
0
b5a80632-8cb0-11e5-a2f1-080027f36211
86400
1