mysql 5.6 GTID global transaction identifier

Posted by KsI on July 21, 2015 Leave a comment (0) Go to comments

Wow! It`s a really nice feature. Now you can do very easy replication.
i.e. In pre 5.6 you should create replica like this:

1. Turn on binary logs at master

vi /etc/mysql/my.cnf
server-id = 11
log_bin = /var/log/mysql/mysql-bin.log
# WARNING: Using expire_logs_days without bin_log crashes the server! See README.Debian!
expire_logs_days = 10
max_binlog_size = 100M
binlog_do_db = mydatabase
#binlog_ignore_db = include_database_name
binlog-format=ROW #I MIXED and STATEMENT sometimes not good
binlog-checksum=crc32 # 5.6 feature speed up binlog
gtid-mode=on #Use force, Luke

2. Create replication User

grant replication slave on *.* to 'repl_user'@'%' identified by 'SecurePassword';

3. Dump all databases

mysqldump --master-data=2 --single-transaction --events --routines --triggers --all-databases > database.sql

4. On slave after dump restore

CHANGE MASTER TO MASTER_HOST='masterHost', MASTER_USER='repl_user',MASTER_LOG_FILE=, MASTER_LOG_POS=,
MASTER_PASSWORD='SecurePassword';
START SLAVE;
show slave status;

But at 5.6 On slave

change master to MASTER_HOST='masterHost", MASTER_AUTO_POSITION=1, MASTER_USER=’repl_user’, MASTER_PASSWORD=’SecurePassword';
START SLAVE;
show slave status;

P.S. If you need to skip one request on slave:

SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; START SLAVE;