One of my friend requested to setup two independent MySQL DB servers in his CentOS 5.4 server box. One MySQL (5.0.77) service was already running on the machine, So I had to install another one. Though, I dont recommend running multiple instances on a single server, instead due to cheap hardware you may better setup a MySQL cluster for best performance. In this case, after initial investigation, I’ve found that there are few ways to implement this. The idea of using MySQL Sandbox interested me most. At the end of day, there was two server running simultaneously on the using different ports for connections. Here are the steps, I took to install and configure multiple MySQL servers:
1. Download, compile and install MySQL sandbox:
cd /usr/src wget http://edge.launchpad.net/mysql-sandbox/mysql-sandbox-3/mysql-sandbox-3/+download/MySQL-Sandbox-3.0.05.tar.gz tar xzf MySQL-Sandbox-3.0.05.tar.gz cd MySQL-Sandbox-3.0.05 perl Makefile.PL make make test make install
2. Get the latest binary tarball for MySQL server:
cd /usr/src wget http://dev.mysql.com/get/Downloads/MySQL-5.1/mysql-5.1.41-linux-x86_64-icc-glibc23.tar.gz/from/http://mirror.csclub.uwaterloo.ca/mysql/
Here, I’ve downloaded MySQL 5.1.41 server tarball. Please note that one MySQL was already there on server having different version (ver 5.0.77). For second installation, I’ve downloaded different version to test out things but for best compatibility, its recommended to download same version.
3. Create new sandbox for single MySQL server. Pls note that you should run following command from a non-root user. Though, in my case, I went ahead and wanted to run command by root user and for that, we must set an environment variable to > 0 value.
export SANDBOX_AS_ROOT=1 ## no need if your are executing below command from non-root user make_sandbox /usr/src/mysql-5.1.41-linux-x86_64-icc-glibc23.tar.gz
4. Sandbox should be created without any glitch. you can go inside the config directory and check that out:
Pls replace root here with your normal username in case you created sandbox with non-root user. Also note down that sandbox created new directory using 5_1_41 naming based on your MySQL server version. This is the default behaviour and we can change it via configuration of sandbox. Also note down that the new MySQL server will listen on same port number ie. 5141.
5. Start and use the service of newly installed MySQL server:
./start ## make sure you are in /root/sandboxes/msb_5_1_41 directory
server should be started. now try to login in MySQL:
You should be in MySQL prompt. You can update these scripts (use, start, stop etc.) as per your taste. The default configuration file for MySQL will be in this directory having name as my.sandbox.cnf. You can update this MySQL config file as per your requirements.
Also dont forget to update root password and if you ever encounter situation where you forgot the password, refer these instructions to quickly reset MySQL root password.
Try to connect this MySQl service using your script after making sure that you must specify port 5141 (or whatever you mentioned in configs) while connecting because this service is not listening to default MySQL port 3306.
If you want to use PhpMyAdmin to manage these multiple MySQL services, Here’s post that will help you configuring PhpMyAdmin.
Other useful posts on MySQL in this blog:
* Setup MySQL Cluster in Redhat based Linux.
* Optimize a large installation of MySQL.
* Quickly repair a huge corrupted or crashed table.
* How to ignore MySQL errors while executing bulk statements
* Fix MySQL running slow without any know reason.