Tag Archives: replication

mysql 5.6 GTID global transaction identifier

Posted by on July 21, 2015 No 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;

Mysql slave lag monitoring

Posted by on May 16, 2013 2 comments

Everybody know

SHOW SLAVE STATUS;

Also everybody know that ‘Seconds_Behind_Master’ shows difference in seconds between the slave SQL thread and the slave I/O thread.

Sometime it shows nonsense, and if you build monitoring It is not good practice to use ‘Seconds_Behind_Master’
Example from real life:
If replication becomes stalled due to connectivity problems, Seconds_Behind_Master shows 0 while replica is far away from master, changing timeout values not help. 🙁 i mean:

slave_net_timeout=300

So we implement following monitoring – every 3 second write current timestamp at master.
and check replica delay.
Or You can use percona heartbeat, It do almost the same.
Read more »