Configure two mysql server and connecting php
One would think what is there in configuring two mysql server, or even think what the purpose behind achieving this. Well there are different requirements, and these different requirements may lead to take us through various possiblities. For instance it may be that certain projects may need the advanced features of MySQL 5.2, where as some others could even be run on MySQL 4.12. In my case it was very peculiar and different, in that about half of our projects used transactional tables and other half could go without transactional tables. And we preferred that these two were configured on two different mysql servers. When the system was explained and the need described to the management, they ruled out the option to have different dedicated server for those projects which was not using transactional tables. Thus I thought about configuring multiple mysql server on the same hardware and operating system.
I had one advantage though, that both my servers could be the same version, and that would help me in configuring php to connect, that I would need to compile php only once. Yet another important matter was our MySQL server will never ever need a connection from the outside, means all connections were from localhost, providing me the flexibility to switch off networking ie skip-networking. And the OS being linux I could depend on the named pipes for all mysql clients.
Finally the decision was made, and we (me and my associate), started the real work, by first taking a backup of the whole projects, for which I used the first part of innodb resizer scripts. And took my server down.
In /usr/local created a new link ln -s mysql-4.12 mysqlt. So I am having /usr/local/mysql and /usr/local/mysqlt. Then moved the /usr/local/mysql/data to /home/mysql_data, created /home/mysqlt_data, assigned ownership to mysql. Now I copied cp /etc/rc.d/init.d/mysql /etc/rc.d/init.d/mysqlt. Fired up my editor and modified the line datadir=”/var/lib/mysql”, to point the respective folders, as created above. Proceeded to copy a sample cnf to /home/mysqlt_data and editied this, as to line socket=/tmp/mysql.sock, under the [mysqld] section to socket=/tmp/mysqlt.sock, enabled the required innodb configuration directives, and specified skip-networking.
Tried test starting the service mysqlt start, well the message was promising and saw that the server had started, following this, I tried service mysql start, lo that was also running.. Okay now.. how do I connect using the command line mysql tool ? could I always say /usr/local/mysql/bin/mysql -S /tmp/mysql.sock, well there are different methods for this, from which I adopted is adding an alias for mysql and mysqlt.
So far so good, I was already having my custom php running with this mysql version, so that was not a very big task, other than adding ini_set('mysql.default_socket','/tmp/mysqlt.sock');, to those projects which needed to use transactional tables.