How to run 2 MySQL servers on 1 computer?

[killdurst]

Hi, can't find this info anywhere. I'm trying to run 2 mysql servers on the same machine. I managed to do it before but cannot do it again. i'm trying to install both mysql 4 and 5 on a linux machine. got most of the info from http://dev.mysql.com/doc/refman/5.0/en/multiple-unix-servers.html.

I tried to run mysql 4 but couldnt:

[root@avocet bin]# pwd
/usr/local/mysql-standard-4.0.18-pc-linux-i686/bin
[root@avocet bin]# ./mysql -uroot
ERROR 2002: Can't connect to local MySQL server through socket '/tmp/mysql4.sock' (111)

When I looked in "tmp", there is a "mysql4.sock".

[shoz]

[quote author=killdurst link=topic=109790.msg442834#msg442834 date=1159413288]
Hi, can't find this info anywhere. I'm trying to run 2 mysql servers on the same machine. I managed to do it before but cannot do it again. i'm trying to install both mysql 4 and 5 on a linux machine. got most of the info from http://dev.mysql.com/doc/refman/5.0/en/multiple-unix-servers.html.

I tried to run mysql 4 but couldnt:

[root@avocet bin]# pwd
/usr/local/mysql-standard-4.0.18-pc-linux-i686/bin
[root@avocet bin]# ./mysql -uroot
ERROR 2002: Can't connect to local MySQL server through socket '/tmp/mysql4.sock' (111)

When I looked in "tmp", there is a "mysql4.sock".
[/quote]
Are you looking in the "tmp" directory in /usr/local/mysql-standard-4.0.18-pc-linux-i686/ or /tmp?. Assuming that the server has been started you can specify the socket file using
[code]
mysql -u ... -S /path/to/mysql.sock
[/code]

[killdurst]

Hi, I'm looking in /tmp.

Since i'm trying to run 2 different versions of mysql (4 and 5). Should the my.cnf file be in /etc or in both "/usr/local/mysql-standard-4.0.18-pc-linux-i686/data" and "/usr/local/mysql-standard-5.0.18-linux-i686/data"?

Currently, in my linux computer, there is no my.cnf in /etc, just in the data folders of the two mysql versions. In mysql4's version of my.cnf, the port is 3306 and the socket is pointing to /tmp/mysql4.sock. In mysql5's version of my.cnf, the port is 3307 and the socket is pointing to /tmp/mysql5.sock.

When I go to "/usr/local/mysql-standard-4.0.18-pc-linux-i686/bin" and typed "./mysql -uroot", I get this error message: ERROR 2002: Can't connect to local MySQL server through socket '/tmp/mysql4.sock' (111)

I went to /tmp and there wasn't any mysql4.sock this time, but there was a mysql5.sock. So I copied that mysql5.sock and renamed it mysql4.sock.

I went back to "/usr/local/mysql-standard-4.0.18-pc-linux-i686/bin" and typed "./mysql -uroot" and still got the same error message.

When I tried to shutdown mysql4, i typed "./mysqld shutdown" but got this error message:
060928 12:46:27  Can't start server: Bind on TCP/IP port: Address already in use
060928 12:46:27  Do you already have another mysqld server running on port: 3306 ?
060928 12:46:27  Aborting

Any help would be greatly appreciated. Thanks.

[shoz]

[quote author=killdurst link=topic=109790.msg442862#msg442862 date=1159418887]
Hi, I'm looking in /tmp.

Since i'm trying to run 2 different versions of mysql (4 and 5). Should the my.cnf file be in /etc or in both "/usr/local/mysql-standard-4.0.18-pc-linux-i686/data" and "/usr/local/mysql-standard-5.0.18-linux-i686/data"?

Currently, in my linux computer, there is no my.cnf in /etc, just in the data folders of the two mysql versions. In mysql4's version of my.cnf, the port is 3306 and the socket is pointing to /tmp/mysql4.sock. In mysql5's version of my.cnf, the port is 3307 and the socket is pointing to /tmp/mysql5.sock.

When I go to "/usr/local/mysql-standard-4.0.18-pc-linux-i686/bin" and typed "./mysql -uroot", I get this error message: ERROR 2002: Can't connect to local MySQL server through socket '/tmp/mysql4.sock' (111)

I went to /tmp and there wasn't any mysql4.sock this time, but there was a mysql5.sock. So I copied that mysql5.sock and renamed it mysql4.sock.

I went back to "/usr/local/mysql-standard-4.0.18-pc-linux-i686/bin" and typed "./mysql -uroot" and still got the same error message.

When I tried to shutdown mysql4, i typed "./mysqld shutdown" but got this error message:
060928 12:46:27  Can't start server: Bind on TCP/IP port: Address already in use
060928 12:46:27  Do you already have another mysqld server running on port: 3306 ?
060928 12:46:27  Aborting

Any help would be greatly appreciated. Thanks.
[/quote]

Don't rename any of the files. Keep the "my.cnf" files where they are. In the data dir. You should probably be getting a warning in MYSQL 5 saying that the my.cnf should be in the installation root rather than the "data" dir. That shouldn't be a problem right now however.

I don't believe "mysqld" has a "shutdown" option. Meaning by invoking it you're asking the server to start. Which explains the error message. Perhaps you meant to use "mysqladmin"?

Keep in mind that the client "mysql" may not be looking for the socket file in the correct directory. Don't use the error message that it gives about it not being able to connect as anything more than what it says. It can't connect. It doesn't mean that you need to go out of your way to get the file in that location. In fact, IMO when running multiple versions of MYSQL you should be explicit about the location of the socket file you're using.

When starting MYSQL it's recommended that you use safe_mysqld.
[code]
cd /path/to/mysql-version
bin/safe_mysqld &
[/code]

You can shutdown the server using "mysqladmin"
[code]
mysqladmin .... shutdown
[/code]

If you're not seeing a socket file for mysql4 in the /tmp dir. Then you should be able to connect using the port it's bound to and find out where it's currently creating the socket file.

Either by using mysqladmin
[code]
mysqladmin -u user -h `hostname` -P 3306 -p variables | grep -i socket
[/code]

or by connecting with "mysql"
[code]
mysql -u user -h `hostname` -P 3306 -p
[/code]

and running
[code]
SHOW VARIABLES LIKE '%socket%';
[/code]

The "`hostname`" section puts the output of "hostname" on the command line. You may try replacing it with "localhost.localdomain".

You can explicitly set the location that should be used for the socket file in the my.cnf under the [mysqld] section using.
[code]
[mysqld]
socket = /path/to/mysql.sock
[/code]

You'll have to restart the server.

[killdurst]

Hi, thanks for replying!

Ok, I finally managed to get the two different versions of MySQL to run on the same server. But when I log on to MySQL 5 and looked at the databases and tables, it's showing the databases and tables from MySQL 4! Below are the 7 steps I took:

01.) Reboot the system.
02.) Ensure that my.cnf in "/usr/local/mysql4/data" contains the following (not in order):
[code]
[client]
#password      = your_password
port            = 3306
socket          = /tmp/mysql4.sock
[mysqld]
port            = 3306
socket          = /tmp/mysql4.sock
innodb_data_home_dir = /usr/local/mysql4/data/
innodb_log_group_home_dir = /usr/local/mysql4/data/
innodb_log_arch_dir = /usr/local/mysql4/data/
[/code]
03.) Ensure that the my.cnf in "/usr/local/mysql5" contains the following lines (not in order):
[code]
[client]
#password      = your_password
port            = 3307
socket          = /tmp/mysql5.sock
[mysqld]
port            = 3307
socket          = /tmp/mysql5.sock
innodb_data_home_dir = /usr/local/mysql5/data/
innodb_log_group_home_dir = /usr/local/mysql5/data/
innodb_log_arch_dir = /usr/local/mysql5/data/
[/code]
04.) Configured MySQL 4 by going to "/usr/local/mysql4" and typing:
[code]
./configure --with-tcp-port=3306 --with-unix-socket-path=/tmp/mysql4.sock --prefix=/usr/local/mysql4
[/code]
05.) Configured MySQL 5 by going to "/usr/local/mysql5" and typing:
[code]
./configure --with-tcp-port=3307 --with-unix-socket-path=/tmp/mysql5.sock --prefix=/usr/local/mysql5
[/code]
06.) Started MySQL 4 by going to "/usr/local/mysql4/bin" and typing:
[code]
./mysqld -uroot -P3306 --socket=/tmp/mysql4.sock
[/code]
07.) Started MySQL 5 by going to "/usr/local/mysql5/bin" and typing:
[code]
./mysqld -uroot -P3307 --socket=/tmp/mysql7.sock
[/code]

In my.cnf for MySQL 5, I've ensured that the data folders are all pointing to the ones in MySQL 5 but when I logged on to MySQL 5 by typing:
[code]./mysql -uroot -pkooprime -P3307 --socket=/tmp/mysql5.sock[/code]
the databases and tables I see are all the ones that belong in "/usr/local/mysql4/data" instead of "/usr/local/mysql5/data". Did I forget to edit some files? How do I troubleshoot this? Appreciate all your help. Thanks!

[shoz]

I assume you haven't downloaded the MYSQL source distribution and compiled it, but instead are using a binary distribution. If that's the case you should not be trying to run "configure" (./configure ...). There should be a "NOTE" explaining this at the top of the output when you try to run "./configure".

When you run it, it tries to setup the db for you and start the server. Read the output of the "configure" script to find out what it's doing exactly. Again, you don't need to run it.

If you've followed the instructions in the file "INSTALL-BINARY" the database should already be setup. By running "scripts/mysql_install_db" etc.

[quote author=killdurst link=topic=109790.msg442931#msg442931 date=1159435242]
Hi, thanks for replying!

Ok, I finally managed to get the two different versions of MySQL to run on the same server. But when I log on to MySQL 5 and looked at the databases and tables, it's showing the databases and tables from MySQL 4! Below are the 7 steps I took:

01.) Reboot the system.
[/quote]

You don't need to do that. Shutdown the server using "mysqladmin" the same way you use "mysql" but instead add the string "shutdown" to the end of the line
[code]
mysqladmin -u root -p ...... shutdown
[/code]


[quote=killdurst]
02.) Ensure that my.cnf in "/usr/local/mysql4/data" contains the following (not in order):
[code]
[client]
#password      = your_password
port            = 3306
socket          = /tmp/mysql4.sock
[mysqld]
port            = 3306
socket          = /tmp/mysql4.sock
innodb_data_home_dir = /usr/local/mysql4/data/
innodb_log_group_home_dir = /usr/local/mysql4/data/
innodb_log_arch_dir = /usr/local/mysql4/data/
[/code]
03.) Ensure that the my.cnf in "/usr/local/mysql5" contains the following lines (not in order):
[code]
[client]
#password      = your_password
port            = 3307
socket          = /tmp/mysql5.sock
[mysqld]
port            = 3307
socket          = /tmp/mysql5.sock
innodb_data_home_dir = /usr/local/mysql5/data/
innodb_log_group_home_dir = /usr/local/mysql5/data/
innodb_log_arch_dir = /usr/local/mysql5/data/
[/code]
[/quote]
The my.cnf files look ok to me. Is it possible that you have multiple my.cnf files in the directory. Perhaps one left over in "/usr/local/mysql5/data/"?

[quote=killdurst]
06.) Started MySQL 4 by going to "/usr/local/mysql4/bin" and typing:
[code]
./mysqld -uroot -P3306 --socket=/tmp/mysql4.sock
[/code]
07.) Started MySQL 5 by going to "/usr/local/mysql5/bin" and typing:
[code]
./mysqld -uroot -P3307 --socket=/tmp/mysql7.sock
[/code]
[/quote]

Shutdown the servers. Start them by running the following commands
[code]
cd /usr/local/mysql5

./bin/safe_mysqld &

cd /usr/local/mysql4

./bin/safe_mysqld &
[/code]
Note that you don't go/cd into the "bin" dir.

[quote=killdurst]
In my.cnf for MySQL 5, I've ensured that the data folders are all pointing to the ones in MySQL 5 but when I logged on to MySQL 5 by typing:
[code]./mysql -uroot -p -P3307 --socket=/tmp/mysql5.sock[/code]
the databases and tables I see are all the ones that belong in "/usr/local/mysql4/data" instead of "/usr/local/mysql5/data". Did I forget to edit some files? How do I troubleshoot this? Appreciate all your help. Thanks!
[/quote]

First thing to do is to make sure you've actually connected to the MYSQL 5 server. You can get the version using
[code]
SELECT VERSION()
[/code]

If it's not the correct version then the problem is you've connected to the wrong server. If it is the correct server then the data_dir is set somehow to the wrong location.

You can see what it's currently set to using

[code]
SHOW VARIABLES LIKE 'datadir';
[/code]

Remember that both will have a default "test" database.

[killdurst]

Hi I've managed to run the two different versions of MySQL on the same server!

To fix the problem above, I just included the basedir and datadir variables when I started MySQL:

XX.) To run mysql4: #./mysqld -uroot -P3306 --socket=/tmp/mysql4.sock --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data/ --log=/usr/local/mysql/data/mysqld.log --pid-file=/usr/local/mysql/data/mysql.pid

XX.) To run mysql5: #./mysqld -uroot -P3307 --socket=/tmp/mysql5.sock --basedir=/usr/local/mysql5 --datadir=/usr/local/mysql5/data/ --log=/usr/local/mysql5/data/mysqld.log --pid-file=/usr/local/mysql5/data/mysql5.pid

Thanks a lot guys!