Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.
share|improve this question

migrated from stackoverflow.com Jan 13 '13 at 2:42

This question came from our site for professional and enthusiast programmers.

1  
Running both databases on the same machine will make performance worse, not better. –  duskwuff Jan 13 '13 at 0:35
add comment

2 Answers 2

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. http://www.severalnines.com/resources/clustercontrol-mysql-haproxy-load-balancing-tutorialhttp://www.severalnines.com/resources/clustercontrol-mysql-haproxy-load-balancing-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

share|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
add comment

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].

--binlog-do-db=A

--binlog_format=statement

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.

IMPORTANT

  • 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 ;)

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.