Make a Copy of Live MySQL database periodically

Question

I am stuck with a problem. I am working on a backoffice project which needs to access the live database of our website which is very big and complicated. Now when i connect my backoffice with live Mysql database and run my queries, some slow queries are also there which in turn locks the database for that time period and the live website becomes slow.

Somebody suggested me to make a copy of the live database periodically and use the copied database with my backoffice app. But i have also seen that on one server, if you have 2 databases on PHP Myadmin, and there is a slow query already running on one database, the CPU usage is pretty high on the server and i think even if i use the copied database for backoffice, it will still block the queries and make either backoffice or live website slow.

Any ideas on how i can implement a good solution which does not affect consistency and speed of the applications. Any nice step by step procedure will be greatly appreciated.

Thanks

Answer 1

Here is a link that describes the idea based on john's suggestion: http://www.howtoforge.com/back_up_mysql_dbs_without_interruptions

Answer 2

you might want to look at mysql replication, a litte more is described here

http://dev.mysql.com/doc/refman/5.0/en/replication.html

basically you replicate your live (master) database to another box that you can use to develop.