0

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 ?

2

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 ! enter image description here


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 !!

  1. Install default tables for this new database instance

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

  2. Start the new instance

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

  4. Finally add it to server start-up list

  5. And now setup your replication.

| improve this answer | |
  • But i want both instances running in same server – sara Feb 28 '14 at 13:03
  • No problem startup a new mysql instance - but remember that mysql love's it's RAM – Up_One Feb 28 '14 at 13:27
  • 1
    You can easily run multiple instances on the same server. Each instance requires separate configuration, datadir and sockets/ports. It's pretty simple to set up multiple instances on one machine. Though as Up_One mentioned, large MySQL instances can be very RAM heavy. – Phil Sumner Feb 28 '14 at 14:33
  • 1
    I'd like to suggest how to setup multiple instances on the same server : dba.stackexchange.com/questions/6370/… – RolandoMySQLDBA Feb 28 '14 at 15:38
  • @Sara it seams that @ Ronaldo already answered this question in a different post - with great level of detail. – Up_One Feb 28 '14 at 16:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.