Is it possible to take same copy of mysql database in another instance(periodically) in the same server?

Question

My current mysqldb is slow due to more data. So we are planning to take a copy of production db into another instance in the same server and redirect all the select queries to that instance without affecting the production one. Is it possible we can syncronize all the changes from the prodcution to the second instance periodically in the same server ?

Answer 1

Yes , what you need is a replication environment !
Where your primary server (Master) will receive all updates and inserts, basiclly all DML's and the secondary server (slave) will receive only select statements ! Here is a link to an article showing you how to do this.
As for the load-balancing , in my case i use F5 to do this for me ! Here is link of the article that shows how this is done ! i have to worn you is quite hard to setup this puppy !

---

To run multiple instances using MySQL you need to have a couple of things separate from the initial install on MySQL like data directory, init script and config file. Ok i will place the steps here : -all you need is for you complete them:

1. Create a new data directory
2. Create/copy existing mysql configuration file
3. Create/copy existing mysql init file to start/stop/reload etc on this new instance

Edit the init file and make some minor changes to make it this instance specific - like port number ,instance name, take care at this step !!

4. Install default tables for this new database instance

   mysql_install_db --datadir=/var/lib/mysql_new --defaults-file=/etc/my_new.cnf --user=mysql

5. Start the new instance

6. Set password for this instance and connect to this new instance

7. Finally add it to server start-up list

8. And now setup your replication.