Mysql – Confusion when running multiple MySQL instances

MySQL

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

Best Answer

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.

Related Topic