R: Starting a 2nd MySQL instance on UNIX
I faced this issues a few years ago and I'd like to give my contributions.
The easy and clean way I've found:
<--> One installation for each mysql instance
<--> On each instance you can have as many databases as you want.
<--> One different mysql user and homedir (mysql41,mysql50) for each mysql installation.
<--> Put .my.cnf file in the home directory of each mysql user with the right parameters (different ports and socket files at least, this is the way I have found not to conflict between instances)
# The MySQL server
port = 3515
socket = /tmp/mysql5015.sock
key_buffer = 256M
max_allowed_packet = 1M
<--> Start each mysql instance with his own user
In this way I have several MySQL server instances (from 3.23 to 5.x) wonderfully working on the same 2.7 Solaris machine.
Da: Eramo, Mark [mailto:firstname.lastname@example.org]
Inviato: martedì 29 aprile 2008 15.54
A: Arthur Fuller
Oggetto: RE: Starting a 2nd MySQL instance on UNIX
Sure, no problem!
This actually proved to be rather tricky because the first instance was installed in /usr/local/mysql. It seems that by default, MySQL looks for things in this locations so if you deviate from it, you have to be explicit with mysql and tell it where to look for things. My new MySQL 50 instance had to run alongside the 4.0.20 instance so I installed it into /usr/local/mysql-50
** Here is a summary of things I had to do to bring this up properly **
I created a my.cnf and put it in /usr/local/mysql-50
In the my.cnf, I defined the following (The first entry is for the mysql program and the 2nd entry is for the mysqld program).
max_allowed_packet=32M <== I had to set this so I could import the MySQL 4.0.20 database properly. Some users may not need this set as high.
#Path to installation directory. All paths are usually resolved relative to this.
#Path to the database root
You have to be careful to not only pick a separate port, but also a separate pid file for the process ID and a separate socket file, especially ifyou are running both instance at the same time.
I modified the mysql.server in the /support-files folder. I had to set the basedir and the datadir. I then copied this to the /bin folder.
I edited the mysql_install_db file in the /scripts folder and set the basedir and datadir. I then ran that to create the mysql database.
As root, I started the server ./bin/mysql.server start (The server starts up :) ). Since the user mysql50 is defined in this script, it then starts mysqld as the mysql50 user.
Now, the tricky part, to run mysql and access the mysql database, you cannot just say mysql -u root -p mysql, it will try to connect to the default instance in /usr/local/mysql.
What you have to do is:
Mysql --defaults-file=/usr/local/mysql-50/my.cnf -u root -p mysql
This now properly connects to the new mysql50 instance. Also, if you wantto run mysqladmin, you need to specify the --defaults-file option. Makesure wherever you use the --defaults-file option that it is the FIRST command line option used.
It took me quite some time to get this all working but now, I understand MySQL much better. I hope this proves to be some help to you and others out there who may be going through the same thing.
From: Arthur Fuller [mailto:email@example.com]
Sent: Tuesday, April 29, 2008 9:29 AM
To: Eramo, Mark
Subject: Re: Starting a 2nd MySQL instance on UNIX
Would you kindly supply the changes you made, for our collective education? Thanks.
On Mon, Apr 28, 2008 at 11:54 AM, Mark-E > wrote:
Thanks for the reply. I was specifying the new port of 3307. I actually
got it working over the weekend. Turns out I had to add a few entries in the
mysqld section of the my.cnf file and I was able to connect.
"Questo messaggio ed ogni suo allegato sono confidenziali e possono essere riservati o, comunque, protetti dall'essere diffusi. Se il ricevente non é il destinatario diretto del presente messaggio, é pregato di contattare l'originario mittente e di cancellare questo messaggio ed ogni suo allegato dal sistema di posta. Se il ricevente non é il destinatario diretto del presente messaggio, sono vietati l'uso, la riproduzione e la stampa di questo messaggio e di ogni suo allegato, nonché ladiffusione del loro contenuto a qualsiasi altro soggetto"
"This message and any attachment are confidential and may be privileged or otherwise protected from disclosure. If you are not the intended recipient,please contact the sender and delete this message and any attachment from your system. If you are not the intended recipient you must not use, copy or print this message or attachment or disclose the contents to any other person."