Hello, please sign in or register
You are here: Home

Recreate replication with MySQLBinLog

This article describes how to simulate MYSQL's builtin replication. And create a controllable replication process in its place.

We have replication and our slave (webserver) is configured to ignore requests from our products database "db_product". When we want the products to be published on to our slave server we need to reflect  any INSERT, UPDATE and the DELETE that might have occured. Obviously we can't "STOP SLAVE" because we have other queries that need to be replicated close to real time.

We milled through various options including two instances of MySQL on each Slave and Master... Use certain databases for publishable data. And simply turn off and on one replication setup as required. This comes with a headache.

Here's the solution which i'm trying to impress upon my colleagues.

Each of our tables has a commonly named timestamp column which updates whenever the row is changed. This is the only condition i dont agree with but i instinctively put them into most of the tables anyway.

i.e.

`tms_admin_updated` TIMESTAMP DEFAULT TIMESTAMP CURRENT_TIMESTAMP() ON UPDATE CURRENT_TIMESTAMP()

1. Get all Inserts and deletes since date @last_updated

Then we can easily create a mysqldump of the database with results after this date

mysqldump -tfB db_product --replace --where="WHERE `tms_admin_updated` > @last_updated" >> statemensts.sql

On mysql4 "--replace" flag isn't avaiable so pass through an editor e.g

mysqldump ... | sed -e "s/^INSERT INTO/REPLACE INTO/g"

2. Get all deleted from binarylogs

The deletes are a problem, we've considered doing a key comparison but with half a million results in the smaller of our tables this is impractical. So here's the special bit using the mysqlbinlogs to generate all the delete statements.

mysqlbinlog -s ./db1-bin.000121 | grep -i -P "^DELETE FROM db_product(\n|.)*?;\n"  > statements.sql

Run the 2nd one first.

Those are the operations. Pretty simple. However i can't see this as being scallable. The process of searching the Binary logs is pretty fast 1gb file took 20seconds on our quad core server. Whilst the selects will have to look at everyrow of our database.

 

Optimisation

For performance we are reducing the number of concurrent writes and reads, by bunching all the writes together, and can take advantage of "DISABLE KEYS" whilst the REPLACE is being performed (mysqldump adds this automatically to its output).

delay_key_writes

An alternative is to set up delay_key_writes to speed up the data modifications for heavy writes.Effectively it doesn't write to the .myi (it will write to .myd) file until all table locks are off, It does write the changes to memory. So should the server crash you should repair the index files. Writing to the index file is often laborious task because of the ordering of data. So this will "drastically improve writes", see comments http://www.mysqlperformanceblog.com/2006/06/17/using-myisam-in-production/#comment-220 and http://marksverbiage.blogspot.com/2007/10/mysql-delaykeywrite-is-good.html.

 This was interesting quote http://markmail.org/message/u46oy5y3fow3jbyh... the jist

delay_key_write determines whether DELAY_KEY_WRITE on CREATE TABLE will be ignored or respected.

DELAY_KEY_WRITE influences when changes to the key buffer will be flushed to disk, i.e. without DELAY_KEY_WRITE, MySQL will call the appropriate OS function to write the changes to disk after every change of the key buffer. With DELAY_KEY_WRITE, this will be done only when the table is closed.

The key buffer hold the indexes and is usually changed when you insert a new row, update an indexed column in a row and so on. With DELAY_KEY_WRITE it has to be expected that on abnormal termination of MySQL, the some of indexes were not written to disk yet and therefore will be corrupted or inconsistent with the data file.

That is not really harmful as the indexes can be recreated from the data file and your MySQL daemon should not terminate abnormally that often. ;-)

However, as the manual recommends, you should add an automatic table check to your MySQL start up script.

 

Reading the mysql binary log file.

On a heavy write system the binary log file is going to get large. So here are some things to consider.

  1. Reduce the size of the binary log files - instead create more of them. By default they grow to 1.1GB
    === master my.cnf ===
    #256MB
    max_binlog_size=268435456
    
  2.  Be selective. Dont put into Binlog things which dont need replicating. NOTE: These will only ignore where "USE dbname" is set or if the statement is a CREATE dbname.tbname...
    === master my.cnf ===
    binlog-ignore-db=dblog
    
  3.  Dont use wildcharacters to select multiple Binary logs. Instead use the modification timestamp of the file to determine whether its contains stuff required for the big push.
    # BAD: mysqlbinlog /data/mysql/[a-z\_\-]*-bin.[0-9]* ...
    # A script like the following will retrieve files since 
    
    DATEFROM="2008-01-01 12:00:00"
    DATETO=`date +'%Y-%m-%d %H:%M:%S'`
    MMIN=$(((`date +%s`-`date +%s -d "$DATEFROM"`)/60))
    BINLOG=`find /data/mysql/ -regex .*/[a-z0-9\_\-]*-bin.[0-9]+ -type f -mmin -$MMIN` 
    
    mysqlbinlog --start-datetime="$DATEFROM" --stop-datetime="$DATETO" -s $BINLOG > "publish.sql"

 

Comments

Cucumucz
Well done man ,I read hundreds of articles but this was one for which I was searching Thanks a lot
Created 03/03/09
Mysql REplication issue
My testdb Lab setup crashed due to low disk space on root dirve on my ec2 Amazon linux systems. I have a DB on Mysql servers. Does removing the Mysql database on Master & slave & also removing Bin log files from both the servers have any negative ...
Created 08/12/13
0921maoqiuyun
cheap nba jerseys, http://www.nbajerseys.net/ jordan shoes, http:/...
Created 21/09/15
1015maoqiuyun
coach outlet online, http://www.coachoutletonline-store.us.com/
Created 15/10/15
clibin
Tyrion 5c cases watched her ray ban sunglasses read. His timber...
Created 18/04/16
chenyan
Bond grinned. pandora-bracciali.it "We bcbgmax.in.net only chaussure...
Created 15/06/16
chenyingying
nike tn 2016 coach outlet online cheap nhl j...
Created 16/11/16
michael kors factory outlet
adidas nmd pandora bracelet
Created 16/05/17
Title*
Comment

Prove you are not a robot

To prove you are not a robot, please type in the six character code you see in the picture below
Security confirmation codeI can't see this!
Contact
Name*
Email never shown*
Home Page

Author

Andrew Dodson
Since:Feb 2007

Comment | flag

Categories

Bookmark and Share