- Shutdown mysql daemon on master server
- Shutdown mysql daemon on slave server
- Take dump of database.tables on master server
- Take backup of database.tables on slave server
- Load dump (of master) on slave
- Start mysql daemon on master server
- Take a note of new binlog filename on master server
- Update the master binlog filename on slave server
- Start mysql daemon on slave server
Wednesday, November 24, 2010
Getting the data Sync'ed between master & slave servers in mysql replication
In order to get the database.tables sync'ed between master and slave server, follow these guidelines
Subscribe to:
Post Comments (Atom)
Another alternative is to use the following command on the master server
ReplyDeletemysqldump -u xxxx -p xxxx test sample | ssh <> "mysql -u xxxx -p xxxx test"
The advantage here is, you don't have to manage binlog filename management
CAution: execute this command when replication on both the machine is in sync. There should not be any replication delay's.
Performance Test
ReplyDelete----------------
when we tested the above command on a table with 16 Million Records, it took approx. 12 minutes.
Giving us a throughput of 1.33 Million record per Minute.
We experimented further with adding compression to this command, so the new command was as follows
mysqldump -uxxxx -pxxxx test sample | gzip | ssh s4 "gunzip | mysql -uxxxx -pxxxx test"
with this new command, the transfer was completed in 502 seconds
Thus giving us a throughput of 1.91 Million records per Minute
A performance increase of 43%
Got following problem
ReplyDeleteERROR 1103 (42000) at line 147: Incorrect table name './db1/'
mysqldump: Got errno 32 on write
the reason is DATA/INDEX directory specified in CREATE TABLE statement
) ENGINE=MyISAM DEFAULT CHARSET=latin1 DATA DIRECTORY='./db1/' ;
in order to handle we need to tweak the command further as follows
mysqldump -uxxxx -pxxxx test sample | sed "s/DATA DIRECTORY='.\/db1/'//" | sed "s/INDEX DIRECTORY='.\/db1/'//" | gzip | ssh s4 "gunzip | mysql -uxxxx -pxxxx test"