I have a DB Server running CentOS 6.4 and MySQL 5.5 from RPMs (not source).
I have dozens of databases but one of them is being hit quite hard with large selects which locks out other updates which in turn locks out other selects (at least I think this is what's happening!)
To alleviate this I would like to replicate one of the databases onto the same server so that the writes can write to the "master" and reads can be done on the "slave". I don't have the luxury of additional hardware.
I have found a lot of guides on the internet describing how to replicate to a different server, and one (http://www.howtoforge.com/one_machine_mysql_replication) describing how to replicate to the same server but it doesn't seem to be geared towards retrofitting this functionality to an existing database with 10s of GB of data in it already. It also talks about compiling MySQL from source.
Does anyone know how I can retrospectively convert my MySQL 5.5 config to replicate one of my databases on the same server?
My existing DB runs on port 3306 so I feel it should be as easy as firing up another instance of MySQL on port 3307 and making some changes to my.cnf but not sure what changes.
Thanks in anticipation.