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:
--port
just changes the TCP/IP port number, but when you connect via socket you need to give it the other socket name.