How to make mysql slave db to sync with master again

Jephe Wu - http://linuxtechres.blogspot.com

Environment: mysql 5.5, CentOS 5.6 64bit for both master(192.168.0.1) and slave(192.168.0.2)
Objective: to make slave db to sync with master again

You can use manual method as stated on http://mysql-mmm.org/mmm2:guide for configuring mysql replication.


Steps:

1. [slave] stop slave mysql database
/etc/init.d/mysql stop
rm -fr /srv/mysql/data /srv/mysql/log

2. [master]
use mylvmbackup(http://www.lenzg.net/mylvmbackup/) to create snapshot for LVM volume where mysql db data and log file resides.
modify /etc/mylvmbackup.conf to keep_snapshot=1 and keep_mount=1

# copy mounted snapshot data and log files to slave database
scp -pr /snap/backup/data /snap/log slavedb:/srv/mysql/

# record down the master log file and position
root@db02:/srv/mysql-db2-snap/backup-pos/ # more backup-20111129_110147_mysql.pos
Master:File=mysql-bin.000046
Master:Position=25449360

Note: log folder on master database is not useful if there's no slave database which will use it, you can rm -fr log/* after shutting down master database, then make code backup for data folder only.

3. [slave]
cd /srv/mysql
chown mysql:mysql -R data log
/etc/init.d/mysql start
mysql -uroot -ppassword
mysql> show slave status\G

mysql> stop slave;
mysql> change master to MASTER_HOST='192.168.0.1', MASTER_USER='slave1_user', MASTER_PASSWORD='slave1_password', MASTER_LOG_FILE='mysql-bin.000046', MASTER_LOG_POS=25449360;

or after checking 'show slave status\G', the master host is correct, you can just use:
mysql> change master to master_log_file='mysql-bin.000046', MASTER_LOG_POS=25449360;

mysql> start slave;
mysql> show slave status;  make sure it shows:
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

monitoring  Seconds_Behind_Master: 7770 column to make sure it's reducing until 0 which means it finishs syncing with master again.

4. [slave] make master to sync with slave too
on slavedb, run
mysql> show master status\G;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000055 | 17584079 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

5. [master] make it sync with slave
mysql> stop slave;
if not, maybe
mysql> reset slave;
mysql> change master to master_log_file='mysql-bin.000055', MASTER_LOG_POS=17584079;
mysql> start slave;
mysql> show slave status\G

note: you have to run 'show master status' first on slavedb, then record down master_log_file and master_log_pos, then use it on master db to make it sync with slave also.

6. [master] use pt-table-checksum to compare 2 databases
show tables status;
create database pt;
pt-table-checksum -uroot -ppassword  --create-replicate --replicate pt.checksums --databases zabbix --empty-replicate-table --chunk-size=5000 localhost

pt-table-checksum -uroot -ppassword  --create-replicate --replicate pt.checksums --databases zabbix --empty-replicate-table --chunk-size=5000 localhost --replicate-check=1

echo $?
0
----------------------------
or skip some huge tables
----------------------
pt-table-checksum -uroot -ppassword  --create-replicate --replicate pt.checksums --databases zabbix --empty-replicate-table --chunk-size=30000 --chunk-size-limit 50  localhost  --ignore-tables events,history,history_uint,trends,trends_uint --replicate-check 1

note:use phpmyadmin or show table status to get rough size of tables which might be skipped for pt-table-checksum.
 --------------------------
If it's different between master and slaves, it will show by paramter --replicate-check 1

pt-table-checksum -uroot -ppassword  --create-replicate --replicate pt.checksums --databases mysql --empty-replicate-table --chunk-size=30000 --chunk-size-limit 50  localhost --replicate-check 1
Differences on P=3306,h=db03
DB    TBL           CHUNK CNT_DIFF CRC_DIFF BOUNDARIES
mysql db                0        1        1 1=1
mysql help_keyword      0       -1        1 1=1
mysql help_relation     0        1        1 1=1
mysql help_topic        0        1        1 1=1
mysql proc              0        0        1 1=1

echo $?
1
------------------
You can make mysql database itself sync between master and slaves by the following method:

on master:
mysqldump --add-drop-table mysql -uroot -ppassword | ssh db03 'mysql -uroot -ppassword mysql'

mysql -uroot -ppassword -h db03
sql> flush privileges;

FAQ:
root@db02:/etc/cron.d/ # pt-table-checksum -uroot -ppassword  --create-replicate --replicate pt.checksums --databases zabbix --empty-replicate-table --chunk-size=5000 localhost
You do not have the PROCESS privilege at /usr/bin/pt-table-checksum line 3761.
root@db02:/etc/cron.d/ # mysql -uroot -ppassword -h slavedb zabbix
ERROR 1044 (42000): Access denied for user 'root'@'192.168.7.2' to database 'zabbix'

[root@db03 sysconfig]# mysql -uroot -ppassword
mysql> grant all on zabbix.* to root@'192.168.7.22' identified by 'password';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql>  exit

 try again.
or

The following method will definitely work when using pt-table-checksum:

mysql> grant all privileges on *.* to root@'172.17.7.22' identified by 'password';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)


otherwise, you might encounter error like 'You do not have the PROCESS privilege at /usr/bin/pt-table-checksum line 3761.'


References:
1. how to completely delete snapshot logical volume
df -h
umount /mountpoint_for_snapshot_volume
lvdisplay -v
lvremove /dev/VolGroup02/snap02
cd /dev/mapper
dmsetup remove snap02
or
kpartx -d xxxxx


2. purge master log - http://dev.mysql.com/doc/refman/5.0/en/purge-binary-logs.html
This statement is safe to run while slaves are replicating. You need not stop them, it will Purge all log files up to but not including the target file.
example:
PURGE BINARY LOGS TO 'mysql-bin.010';
PURGE BINARY LOGS BEFORE '2008-04-02 22:46:26';

You can also set the expire_logs_days system variable to expire binary log files automatically after a given number of days (see Section 5.1.3, ę¸¯erver System Variables?. If you are using replication, you should set the variable no lower than the maximum number of days your slaves might lag behind the master.

mysql> show master logs;


#!/bin/bash
CURRENT_LOGFILE=$(/usr/bin/mysql -e "SHOW SLAVE STATUS\G" | awk '$1 == "Master_Log_File:" {print $2}')
/usr/bin/mysql -h MASTER -e "PURGE MASTER LOGS TO '${CURRENT_LOGFILE}'"
exit $?


note: cronjob script to purge master log on slave db:


3. master/slave /etc/my.cnf configuration:
 note: server_id must be different. If not, change it, restart mysqld service. 
sql> reset slave;
sql> change master to ....
sql> start slave;


slave:

root@db01:/srv/mysql/log/ # cat /etc/my.cnf | grep -v ^# | grep -v ^$
[client]
port            = 3306
socket          = /var/run/mysqld/mysqld.sock

[mysqld_safe]
socket          = /var/run/mysqld/mysqld.sock
nice            = 0

[mysqld]
default-storage-engine=innodb
innodb_file_per_table
innodb_lock_wait_timeout = 150
innodb_rollback_on_timeout
innodb_buffer_pool_size=20000M
innodb_flush_method=O_DIRECT
innodb_flush_log_at_trx_commit=2
innodb_log_buffer_size=4M
innodb_thread_concurrency=8
transaction-isolation=READ-COMMITTED
log-queries-not-using-indexes
key_buffer_size = 64M
user            = mysql
socket          = /var/run/mysqld/mysqld.sock
port            = 3306
basedir         = /usr
datadir         = /srv/mysql/data
tmpdir          = /tmpfs
skip-external-locking
bind-address            = 0.0.0.0
key_buffer              = 128M
max_allowed_packet      = 128M
thread_stack            = 192K
thread_cache_size       = 8
myisam-recover         = BACKUP
max_connections=512
tmp_table_size=256M
max_heap_table_size=256M
table_cache=1024
thread_cache=16
read_buffer_size        = 2M
read_rnd_buffer_size    = 2M  
sort_buffer_size        = 2M   
thread_stack            = 256K
join_buffer_size        = 8M   
log-queries-not-using-indexes
thread_concurrency     = 32
query_cache_type = 1
query_cache_size = 256M
query_cache_limit = 16M
log_error                = /srv/mysql/log/error.log
log_slow_queries        = /srv/mysql/log/mysql-slow.log
long_query_time = 2
server_id=1
log_bin             = /srv/mysql/log/mysql-bin.log
log_bin_index       = /srv/mysql/log/mysql-bin.log.index
relay_log           = /srv/mysql/log/mysql-relay-bin
relay_log_index     = /srv/mysql/log/mysql-relay-bin.index
expire_logs_days    = 2
max_binlog_size     = 100M
log_slave_updates   = 1
binlog-format=MIXED
sync_binlog=1

[mysqldump]
quick
quote-names
max_allowed_packet      = 16M

[mysql]
[isamchk]
key_buffer              = 16M
------------------------------------
root@db02:/srv # cat /etc/my.cnf | grep -v ^# | grep -v ^$
[client]
port        = 3306
socket        = /var/run/mysqld/mysqld.sock

[mysqld_safe]
socket        = /var/run/mysqld/mysqld.sock
nice        = 0

[mysqld]
default-storage-engine=innodb
innodb_file_per_table
innodb_lock_wait_timeout = 150
innodb_rollback_on_timeout
innodb_buffer_pool_size=20000M
innodb_flush_method=O_DIRECT
innodb_flush_log_at_trx_commit=2
innodb_log_buffer_size=4M
innodb_thread_concurrency=8
transaction-isolation=READ-COMMITTED
log-queries-not-using-indexes
key_buffer_size = 64M
user        = mysql
socket        = /var/run/mysqld/mysqld.sock
port        = 3306
basedir        = /usr
datadir        = /srv/mysql/data
tmpdir        = /tmpfs
skip-external-locking
bind-address        = 0.0.0.0
key_buffer        = 128M
max_allowed_packet    = 128M
thread_stack        = 192K
thread_cache_size       = 8
myisam-recover         = BACKUP
max_connections=512
tmp_table_size=256M
max_heap_table_size=256M
table_cache=1024
thread_cache=16
read_buffer_size        = 2M
read_rnd_buffer_size    = 2M   
sort_buffer_size        = 2M  
thread_stack            = 256K 
join_buffer_size        = 8M  
log-queries-not-using-indexes
thread_concurrency     = 32
query_cache_type = 1
query_cache_size = 256M
query_cache_limit = 16M
log_error                = /srv/mysql/log/error.log
log_slow_queries    = /srv/mysql/log/mysql-slow.log
long_query_time = 2
server_id=2
log_bin             = /srv/mysql/log/mysql-bin.log
log_bin_index       = /srv/mysql/log/mysql-bin.log.index
relay_log           = /srv/mysql/log/mysql-relay-bin
relay_log_index     = /srv/mysql/log/mysql-relay-bin.index
expire_logs_days    = 2
max_binlog_size     = 100M
log_slave_updates   = 1
binlog-format=MIXED


#added by Jephe to ignore replication error, use all or comma separated error codes,such as 1507 for dropping non-existed partitions.
#slave-skip-errors=all

[mysqldump]
quick
quote-names
max_allowed_packet    = 16M

[mysql]
[isamchk]
key_buffer        = 16M

4. commands
a. grant pt.checksums table to my pc ip 192.168.0.100 with login id root and password as 'password'

mysql> grant all privileges on pt.* to root@'192.168.0.100' identified by 'password';
mysql> flush privileges;
mysql> show grants;


5. documents
http://www.howtoforge.com/mysql5_master_master_replication_debian_etch


6. References

http://dev.mysql.com/doc/refman/5.5/en/replication.html
http://mysql-mmm.org/mmm2:guide  - 2 masters/2 slaves mysql db and mmm2 setup guide 


7. how to ignore db for replication setup ( http://www.packtpub.com/article/install-manage-use-mmm-for-mysql-high-availability )
Now configure the mysqld section /etc/my.cnf on both nodes with the following steps:

  1. Prevent the server from modifying its data until told to do so by MMM. Note that this does not apply to users with SUPER privilege (that is, probably you at the command line!):
    
read-only
  2. Prevent the server from modifying its mysql database as a result of a replicated query it receives as a slave:
    
replicate-ignore-db = mysql
  3. Prevent this server from logging changes to its mysql database:
    
binlog-ignore-db = mysql

at 11/29/2011 11:51:00 PM

Labels: , ,