Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I'm trying to set up multiple MySQL database instances on a single server and I'm getting some very weird results. I can get the different instances up and running, listening on different ports, using different .cnf files, using different datadirs, etc. As far as I can tell, I've got two separate, independent instances. Below is a copy of when I run "ps aux | grep mysql".

root      2827  0.0  0.0   2592  1140 pts/1    T    14:30   0:00 /bin/sh /usr/bin/mysqld_safe --defaults-file=/etc/my.cnf
mysql     2858  0.0  1.0 119692 18312 pts/1    Sl   14:30   0:00 /usr/libexec/mysqld --defaults-file=/etc/my.cnf --basedir=/usr --datadir=/var/lib/mysql --user=mysql --pid-file=/var/run/mysqld/mysqld.pid --skip-external-locking --port=3306 --socket=/var/lib/mysql/mysql.sock
root      2887  0.0  0.0   2592  1140 pts/1    T    14:34   0:00 /bin/sh /usr/bin/mysqld_safe --defaults-file=/etc/my2.cnf
mysql     2921  0.0  1.0 119492 17596 pts/1    Sl   14:34   0:00 /usr/libexec/mysqld --defaults-file=/etc/my2.cnf --basedir=/usr --datadir=/var/lib/mysql2 --user=mysql --pid-file=/var/run/mysqld/mysqld2.pid --skip-external-locking --port=3307 --socket=/var/lib/mysql2/mysql2.sock

Now is where it gets weird. When I log into the db's remotely with Navicat, I can edit the contents of the databases, no problem. However, when I run a script to access the data on the different dbs, I get a a table doesn't exist error because the script is trying to access the first instance running on port 3306, even though I specify port 3307 for my second connection resource.

Another thing I noticed is that if I SSH into the server housing the two DB instances and log in to one of them using mysql -u root --port=3306 -p and do something like change the password for the root user, the root password has changed when I try to connect using mysql -u root --port=3307 -p;

I've been working on this for a few days now and I'm completely stumped. I'm working with Fedora 8 on an Amazon AWS server. Any help would be most appreciated!

Here's the contents of my.cnf:

[mysqld] datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
port=3306
user=mysql
old_passwords=1
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

And here's the contents of my2.cnf:

[mysqld]
datadir=/var/lib/mysql2
socket=/var/lib/mysql2/mysql2.sock
port=3307
user=mysql
old_passwords=1
[mysqld_safe]
log-error=/var/log/mysqld2.log
pid-file=/var/run/mysqld/mysqld2.pid
share|improve this question

migrated from stackoverflow.com Mar 26 '11 at 10:40

This question came from our site for professional and enthusiast programmers.

    
You need to post the contents of /etc/my.cnf and /etc/my2.cnf –  Ex Umbris Mar 25 '11 at 20:41
add comment

2 Answers

up vote 0 down vote accepted

To connect to the mysql server running on port 3307, use:

$ mysql --socket=/var/lib/mysql2/mysql2.sock

--port just changes the TCP/IP port number, but when you connect via socket you need to give it the other socket name.

share|improve this answer
    
Awesome! That fixed it and got everything working like expected. Thanks for the help! –  Mike Levy Mar 28 '11 at 17:02
add comment

The issue is that when you run mysql and don't explicitly specify a hostname other than localhost (I suggest using the IP address of the local machine: 127.0.0.1), all MySQL commands (including mysql itself) will assume a socket connection. Your --port parameter was effectively ignored, and the mysql client was connecting via /var/lib/mysql/mysql.sock, which if the first instance's socket.

Check this out:

$ mysql --host=localhost --port=3306
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)

Versus:

$ mysql --host=127.0.0.1 --port=3306
ERROR 2003 (HY000): Can't connect to MySQL server on '127.0.0.1' (111)
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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