I have MySQL databases. Database A and Database B. Running on Mysql 5.5 and Ubuntu 12.04.

Into Database A i am doing INSERTs, UPDATEs every minute (cron) (The whole database has over 15 millions rows and growing).

I want to synchronize Database B with Database A use it for SELECT queries (for performance).

So I want to know: How can I synchronize new rows with Database B every 4 hours. I looked into MySQL replication. Is it good for this?

  1. I am thinking think about SQLite3 DB.
  2. All tables are InnoDB
  3. I'm looking for a headless command, not a GUI tool.
  4. Everything is on one machine.
  5. I need a fast utility. I tried Mysqldump, but I am trying to sync, not get the entire database.
  • 1
    Running both databases on the same machine will make performance worse, not better. – duskwuff Jan 13 '13 at 0:35

Why not Enable / configure mySQL Replication Service? I am not pro at it but I know you can achieve that by using mySQL Replication. See the link for details. http://dev.mysql.com/doc/refman/5.0/en/replication.html

I am updating my answer here try to guide you to the proper sources or techniques. Try enabling clustering. and try to go through this link to load balance your mySql instances. https://severalnines.com/resources/tutorials/mysql-load-balancing-haproxy-tutorial

one machine running mySql instance and the same one doing the backup will not solve your issue but can be achieved through virtualization. Consider vmWare for example or try Virtualbox too.. but again if your server fails you will not going have nay means to take advantage of such design. However what will be good you go for another box and deploy the sql node there and then enable/ configure clustering

| improve this answer | |
  • And it can be done on one machine? It not need more instances of MySQL? – Roman Antl Jan 12 '13 at 23:34
  • We can implement replication in same machine itself : one_machine_mysql_replication .But when something happens to master(say something like crash), slave also will have same issues. So this method is not advisable – Praveen Prasannan Jan 13 '13 at 4:32
  • @Praveen True that and that's where I updated my answer in favor of that. – A Malik Jan 15 '13 at 13:05
  • clustering is a failover mechanism where one dies other available one takes over it's place... Clustering and Replication are both different techniques.. You can google for details. – A Malik Jan 15 '13 at 21:02
  • @Roman: Replication may be good (for performance) but I don't think having both master and slave on the same machine will do any good. Probably worse performance. – ypercubeᵀᴹ Mar 15 '13 at 9:44

You may try doing this by applying archival logs in Mysql which is called as binary logs. Have your instance my.cnf in the below manner under [mysqld].



Restart mysqld.

Every four hours let cron to exec a script by doing below steps one by one.

1). "flush logs" on the server. Get file names from binary logs generated from restart to latest - 1. (Make a note of latest - 1 file name for later reuse).

2). Convert those logs to .sql .ie., mysqlbinlog mysql.0000021 > mysql.0000021.sql ; mysqlbinlog mysql.0000022 > mysql.0000022.sql ... so on until latest - 1.

3). Now replace string "use A" to "use B" like. sed -i 's/use A/use B/g' mysql.0000021.sql

4). Now apply them to your DB instance. mysql -uwill -psmith << EOF source mysql.0000021.sql ; EOF

5). Capture errors in the below manner at the starting of the script.

exec 7>&2 exec 2> ERR_FILENAME.txt

6). And now if [ -s ERR_FILENAME.txt ] then sendmail ; fi

7). For the next iteration use the binary log file that you took a note at step 1 as a starting file name.


  • Ensure user "will" doesn't have any permissions to update database "A". It should be explicit to "B". By chance even if you forget to replace, it won't change.

  • First try this in your test environment try all your application functionality that are properly replicating without any goofups. There you go and no need to call a guy ;)

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy