Setting up mutiple MySQL Database servers on a single linux machine

by jagbir on November 25, 2009

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:

cd /root/sandboxes/msb_5_1_41

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:

./use

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.

Tagged as: database, linux

Pingback: Setting up mutiple MySQL Database servers on a single linux … | nighshift@inertz.org
Pingback: Links 02/12/2009: Fedora 13 Naming, Haystack 1.0 Released | Boycott Novell
Pawan Gupta

./start is not working

./start
………………………………………………….. sandbox server not started yet
http://www.jagbir.info jagbir

@Pawan Gupta
can you pls check logs to know where’s its failing?
Gregory

Hi, I’ve created replication server (make_replication_sandbox command). I have a question for you. How can I connect to master/slave server using remote application (for example SQLyoq)? Thanks.

http://www.jagbir.info jagbir

Hi Gregory,

I’m unable to understand your query properly. If you have installed two instances of mysql in single host then obviously they should be running on different ports? so you can specify -P option to specify port while connecting whether from local or remote application. You need to make sure that port used should be open in your firewall etc. if any.

-
Jagbir

Gregory

Hi, Yes, I’ve installed 1 master and 2 slave servers.
my.sandbox.cnf for master look like this:
[client]
user = msandbox
password = msandbox
port = 24758
socket = /tmp/mysql_sandbox24758.sock

[mysqld]
user = root
port = 24758
socket = /tmp/mysql_sandbox24758.sock
————————
for slave1 I have:

[mysql]
prompt=’slave1 [\h] {\u} (\d) > ‘
#

[client]
user = msandbox
password = msandbox
port = 24759
socket = /tmp/mysql_sandbox24759.sock

[mysqld]
user = root
port = 24759
socket = /tmp/mysql_sandbox24759.sock

———————–

ports are open

So, I’m trying to connect to master server from the command line:

[root@localhost rsandbox_5_1_43]# mysql -h localhost -u msandbox -P 24758 -p
Enter password: (here ‘msandbox’)
ERROR 1045 (28000): Access denied for user ‘msandbox’@'localhost’ (using password: YES)

But this works fine:
[root@localhost /]# cd /root/sandboxes/rsandbox_5_1_43
[root@localhost rsandbox_5_1_43]# ./m
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.1.43-log MySQL Community Server (GPL)

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

master [localhost] {msandbox} ((none)) >
—————-

So, I don’t now how can I connect to master using host, pass, user and port…

BTW, I can connect to my 1st mysql server (5.0.77) using ‘mysql -h localhost -u root -p’ command

Thanks for help!

http://www.jagbir.info jagbir

Hi,

The error “ERROR 1045 (28000): Access denied for user ‘msandbox’@’localhost’ (using password: YES)” usually implies that the user ‘msandbox’ doesn’t have access granted for ‘localhost’. while connecting with ./m seems you are connecting using ‘root’ user which have access. You can connect using ./m and then issue ‘select user,host from mysql.user where user = “msandbox”" command at mysql prompt to view whether user ‘msandbox’ has proper entry for ‘localhost’ host or not. you can add this if not already there. let me know the result to investigate further.

Gregory

next problem…
I’ve installed phpmyadmin (http://blog.linuxing.org/2009/11/how-to-use-phpmyadmin-for-multiple-mysql-servers-running-on-same-server-machine/). My config.inc.php looks like this:
$cfg['blowfish_secret'] = ‘test’; /* YOU MUST FILL IN THIS FOR COOKIE AUTH! */

/*
* Servers configuration
*/
$i = 0;

/*
* First server
*/
$i++;
/* Authentication type */
$cfg['Servers'][$i]['auth_type'] = ‘cookie’;
/* Server parameters */
$cfg['Servers'][$i]['host'] = ‘localhost’;
$cfg['Servers'][$i]['connect_type'] = ‘tcp’;
$cfg['Servers'][$i]['compress'] = false;
/* Select mysqli if your server has it */
$cfg['Servers'][$i]['extension'] = ‘mysql’;

/*
* Servers 2 configuration, running on port 24758
*/

$i++;
/* Authentication type */
$cfg['Servers'][$i]['auth_type'] = ‘cookie’;
/* Server parameters */
$cfg['Servers'][$i]['host'] = ‘localhost’;
$cfg['Servers'][$i]['port'] = ’24758′;
$cfg['Servers'][$i]['socket'] = ‘/tmp/mysql_sandbox24758.sock’;
$cfg['Servers'][$i]['connect_type'] = ‘socket’;
$cfg['Servers'][$i]['compress'] = false;

/* User for advanced features */
// $cfg['Servers'][$i]['controluser'] = ‘pma’;
// $cfg['Servers'][$i]['controlpass'] = ‘pmapass’;
/* Advanced phpMyAdmin features */
// $cfg['Servers'][$i]['pmadb'] = ‘phpmyadmin’;
// $cfg['Servers'][$i]['bookmarktable'] = ‘pma_bookmark’;
// $cfg['Servers'][$i]['relation'] = ‘pma_relation’;
// $cfg['Servers'][$i]['table_info'] = ‘pma_table_info’;
// $cfg['Servers'][$i]['table_coords'] = ‘pma_table_coords’;
// $cfg['Servers'][$i]['pdf_pages'] = ‘pma_pdf_pages’;
// $cfg['Servers'][$i]['column_info'] = ‘pma_column_info’;
// $cfg['Servers'][$i]['history'] = ‘pma_history’;
// $cfg['Servers'][$i]['designer_coords'] = ‘pma_designer_coords’;

/*
* End of servers configuration
*/

/*
* Directories for saving/loading files from server
*/
$cfg['UploadDir'] = ”;
$cfg['SaveDir'] = ”;
—————————-
I’m able to connect to 1st server via phpmyadmin, but if I trying to connect to localhost:24758 (user: msandbox, pass: msandbox) I have ‘#2002 – The server is not responding (or local MySQL server’s socket is not correctly configured).’ error.

http://www.jagbir.info jagbir

well, this is unusual error. Can you confirm once that you are able to connect second server using mentioned socket and user/pass from command line? if you are not able to do then it might be some other issue instead of phpmyadmin. pls let me know the result to help you out.

-
Jagbir

Gregory

I’m connecting as ‘msandbox’ using ./m (look at last line)
—————–
[root@localhost rsandbox_5_1_43]# ./m
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.1.43-log MySQL Community Server (GPL)

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

master [localhost] {msandbox} ((none)) >
—————-
I’ve checked this also using SHOW PROCESSLIST
I’ve checked ‘user’ table in mysql db. There is user msandbox for localhost

http://www.jagbir.info jagbir

sorry for that. I didnt looked at last line. well, can you paste contents of ./m script here? and also if possible give me the url of your phpmyadmin if I can access it from my place. this problem is weird.. I’m interested to dig more.

-
Jagbir

Gregory

./m:
#!/bin/sh
/root/sandboxes/rsandbox_5_1_43/master/use “$@”

./use:
export LD_LIBRARY_PATH=/root/5.1.43/lib:/root/5.1.43/lib/mysql:$LD_LIBRARY_PATH
export DYLD_LIBRARY_PATH=/root/5.1.43/lib:/root/5.1.43/lib/mysql:$DYLD_LIBRARY_PATH
SBDIR=”/root/sandboxes/rsandbox_5_1_43/master”
BASEDIR=/root/5.1.43
MYSQL=”$BASEDIR/bin/mysql”
export MYSQL_HISTFILE=”$SBDIR/.mysql_history”
PIDFILE=”$SBDIR/data/mysql_sandbox3307.pid”
if [ -f $PIDFILE ]
then
$MYSQL –defaults-file=$SBDIR/my.sandbox.cnf $MYCLIENT_OPTIONS “$@”
#else
# echo “PID file $PIDFILE not found ”
fi

———————–
I’m not able to give you URL to my phpmyadmin, because my pc with centos is behind NAT. I’ve tried use port forwarding, but it dosn’t work (i don’t know why…)

And here is msandbox.log:
root/5.1.43/bin/mysqld, Version: 5.1.43-log (MySQL Community Server (GPL)). started with:
Tcp port: 3307 Unix socket: /tmp/mysql_sandbox3307.sock
Time Id Command Argument
100204 12:21:28 1 Connect msandbox@localhost on
1 Query select @@version_comment limit 1
1 Query select USER()
100204 12:29:02 1 Quit
——————-

These entries has been genereted ONLY when I’m connecting via ./m script.
When I’m using:
mysql -u msandbox -P 3307 -h localhost -p
server dosen’t write any data into this logfile…
:/

http://www.jagbir.info jagbir

Ok. Another tip, pls try to execute this command to connect and let me know the outcome:
mysql -u msandbox -P 3307 -h localhost -p –defaults-file=/root/sandboxes/rsandbox_5_1_43/master/my.sandbox.cnf
it’s a possibility that when you are trying to connect using mysql, its picking up wrong default file (first one’s).

-
Jagbir

Gregory

still the same:

[root@localhost rsandbox_5_1_43]# mysql -u msandbox -P 3307 -h localhost -p –defaults-file=/root/sandboxes/rsandbox_5_1_43/master/my.sandbox.cnf
Enter password:
ERROR 1045 (28000): Access denied for user ‘msandbox’@'localhost’ (using password: YES)

I haven’t ideas…
Gregory

–defaults-file=file_name

Use only the given option file. file_name is the full path name to the file. If the file does not exist or is otherwise inaccessible, the program will exit with an error.

I’ve checked this option with not correct path or file name and I haven’t received an error.
Gregory

I haven’t received an arro because of lines before defaults-file option…
Now I have error “mysql: unknown variable ‘defaults-file=/root/sandboxes/rsandbox_5_1_43/master/my.sandbox.cnf’”
but this can by fixed by setting the –default-file option at the beginning of the mysql command. And now…:
[root@localhost rsandbox_5_1_43]# mysql –defaults-file=/root/sandboxes/rsandbox_5_1_43/master/my.sandbox.cnf -u msandbox -P 3307 -h localhost -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.1.43-log MySQL Community Server (GPL)

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the buffer.

master [localhost] {msandbox} ((none)) >

—–

But I have still problem with connect to master server using standard command:

[root@localhost rsandbox_5_1_43]# mysql -u msandbox -P 3307 -p
Enter password:
ERROR 1045 (28000): Access denied for user ‘msandbox’@'localhost’ (using password: YES)
——————-

BTW, Jagbir, thanks for help and tips! You are great!

http://www.jagbir.info jagbir

You’re welcome Gregory. Glad you’re able to connect, the issue here seems conflict in default file option. Thanks for notifying me about this issue, I’ll research more to resolve it from my end.

-
Jagbir

Gregory

Tomorrow I’ll uninstall mysql server and sandbox and I’ll try install only sandbox (replication) on my OS. I’ll write to you about result.
Gregory

Hi again!
I have installed only sandbox already, but mysqld doesn’t exist…
So I’ve got maybe simply question for you. Do I have to install mysql-server before sandbox installation?

http://www.jagbir.info jagbir

Hi .. welcome again
the best way is to download mysql tar/binary from net and then extract it to some folder like: /usr/src/mysql1 etc. I guess in sandbox you need to specify mysql path so include this folder path and move ahead. Dont go for rpm/yum or automatic package installation.

-
Jagbir

http://geekyninja.com Sharninder

So, I understand that you can do this. But, my question is why would you want to ? Can’t you just have multiple databased within the same mysql instance ? I understand if this is for purely academic purposes, but setting up replication/FT on this just doesn’t make sense.
http://www.jagbir.info jagbir

@Sharninder
Good question Sharninder.. there are few cases where this type of setup can be helpful. like in my case, we have a slave server from very critical production db servers (2 master-master hosts). Slave is read-only you know because if we try to manipulate data in a slave, replication will break. Slave is needed to provide general purpose read-only access and to take data backup.

We have another heavy internal application which requires same data to generate reports and require read/write access which we can’t provide in slave and due to critical nature, dont want to bog down our production hosts for this. Solution? either purchase a new server or run two instances on same slave server. One instance will run as slave, provide backup also and another instance fetch data from slave (not replication) then provide read/write access to application generating reports. 2nd instance is experimental so it can break and repaired any time by fetching data from slave. such experiments can’t be done with slave or production servers.

Hope this will give you an idea about such setup.
imran

i am unable to fine where it is logging as i want to see why ./start is not working? where are log files?
imran

hi pawan gupta how you resolved this issue sandbox server not started yet ? any body know kindly reply me at is@mobileweaver.dk i am waiting.
Pingback: den led chieu sang

Previous post: Detect directory or file changes in *nix

Next post: Disable ssl ver 2 in apache for pci compliance

Setting up mutiple MySQL Database servers on a single linux machine

Search

Popular Posts

Archives

Setting up mutiple MySQL Database servers on a single linux machine

Search

Popular Posts

Archives

Tags