Mysql synchronization databases on one machine

linuxMySQL

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?

I am thinking think about SQLite3 DB.
All tables are InnoDB
I'm looking for a headless command, not a GUI tool.
Everything is on one machine.
I need a fast utility. I tried Mysqldump, but I am trying to sync, not get the entire database.

Best Answer

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

Related Solutions

MySQL – Separate Databases for Front-End and Back-End

From your comments, one solution might be to make a Master-Slave replication setup (link to mysql replication here)

I would make the backend the Master, and the front-end the slave. If your front-end needs to write (contact forms, tracking etc) you would update your code in the Front-end to read from the slave, and write to the master.

The downside is, depending on the load, your backend-writes might be delayed a few seconds or more. But as an anecdote, I've got a server that handles 120 commands /second (averaged, as reported by munin) and the slave server isn't behind by more than 3 seconds.

Mysql – How to recover data from corrupted innoDB table from a crashed theSQL server

You could try to use MySQL's CHECK TABLE and REPAIR TABLE functions (but make sure you grab a copy of the table first):

mysql -uroot -p database1
CHECK TABLE table1

If that doesn't work, try using:

CHECK TABLE table1 EXTENDED

According to the CHECK TABLE documentation:

EXTENDED is to be used only after you have run a normal check but still get strange errors from a table when MySQL tries to update a row or find a row by key. This is very unlikely if a normal check has succeeded.

If any errors are reported by either of the above, try:

REPAIR TABLE table1

If that doesn't work, you can try:

REPAIR TABLE table1 EXTENDED

Finally, if that still doesn't work, you may be able to use myisamchk while MySQL is offline - according to the documentation:

REPAIR TABLE does not implement all the options of myisamchk

Best Answer

Related Solutions

MySQL – Separate Databases for Front-End and Back-End

Mysql – How to recover data from corrupted innoDB table from a crashed theSQL server

Related Question