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 ?
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:
- Create a new data directory
- Create/copy existing mysql configuration file
- 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 !!
Install default tables for this new database instance
mysql_install_db --datadir=/var/lib/mysql_new --defaults-file=/etc/my_new.cnf --user=mysql
Start the new instance
Set password for this instance and connect to this new instance
Finally add it to server start-up list
And now setup your replication.