Manually installing multiple MySQL instances on Linux: HOWTO

February 19, 2009

Installing a single MySQL instance on a linux machine is a very simple operation. It may be as simple as:

apt-get install mysql-server

But you cannot use this method to install another MySQL instance. Moreover, if you try to manually install another instance, you may find that some collisions occur.

For example, when trying to install two 5.0 servers, apt-get or yum will just tell me "package is already installed".

When trying to install a new 5.1 server along with 5.0, an implicit upgrade is performed.

But even if we try forcing the installation, there are collisions:

  • A dpkg or rpm will install my.cnf under /etc. Two installations will override one another. With RPM you may get a .rpmsave backup file, but that doesn't help too much.
  • The daemon file: /etc/init.d/mysql(d) is overwritten.
  • The default data directory is used for both installations: /var/lib/mysql
  • The binaries are overwritten
  • Both installations will use port 3306.
  • In both installations, the same socket file (e.g. /var/run/mysql/mysql.sock) is used.

Interestingly, on Windows, multiple MySQL installations are by far easier:

  • Binaries are under Program Files\\MySQL\\MySQLX.X. With two installations, you specify different directories.
  • Data files are by default directly under the installations paths (MySQL 5.0) or under "Documents And Settings..." (MySQL 5.1) with no collisions.
  • The my.ini files are located directly under the installation paths.
  • The installer asks you for a service name, and notifies you if that name is already in use.
  • The installer let's you know if port 3306 is already taken, and allows you to specify another one.
  • Of course, there's no unix socket file.

I usually install MySQL on Linux using the binary tarball. When there's only one instance expected, I go with the standards: my.cnf is in /etc, mysqld is under /etc/init.d, etc. (no pun intended)

Steps for multiple installation on Linux

When more than one installation is expected, here's a safe way to ensure no collisions occur. We will assume a 5.0 and 5.1 installation (say we want to upgrade):

Install the MySQL binaries under /usr/local

Following the INSTALL document file, we make symbolic links to the full path in the names

ln -s /usr/local/your-mysql-5.0-full-installation-path /usr/local/mysql50
ln -s /usr/local/your-mysql-5.1-full-installation-path /usr/local/mysql51

Do not put my.cnf under /etc

Instead, put them directly in the installation path:

touch /usr/local/mysql50/my.cnf
touch /usr/local/mysql51/my.cnf

Setup different port numbers in the my.cnf files

For example, in /usr/local/mysql50/my.cnf, use port 3350:

[mysql]
port=3350

[mysqld]
port=3350

Choose another port (e.g. 3351) for the 5.1 installation, then have it written as above in the 5.1 my.cnf file.

Choose distinct socket files

For example, in /usr/local/mysql50/my.cnf, add:

[mysql]
port=3350
socket=/tmp/mysql50.sock

[mysqld]
port=3350
socket=/tmp/mysql50.sock

Choose another socket and set it up in the second my.cnf file. You may also choose to put the socket files under the data paths or installation paths.

Choose distinct data paths

Either do not specify them at all, in which case they will reside under the installation path, or, if you want to enjoy another partition, use:

[mysql]
port=3350
socket=/tmp/mysql50.sock

[mysqld]
port=3350
socket=/tmp/mysql50.sock
datadir=/my_raid_path/mysql50/

Create distinct daemons

Manually copy support_files/mysql.server to /etc/init.d under distinct names. For example:

cp /usr/local/mysql50/support_files/mysql.server /etc/init.d/mysqld50
cp /usr/local/mysql51/support_files/mysql.server /etc/init.d/mysqld51

Other settings

You may wish to set up a soft link for the client binaries, for example:

ln -s /usr/local/mysql50/bin/mysql /usr/bin/mysql50

chkconfig (RedHat and derived) can be used to start/stop daemon as service:

chkconfig --add mysqld50

Conclusion

I would prefer MySQL to come bundled in self-contained directory. The tarball is almost that, except it expects socket file to be on /tmp, and by default uses the 3306 port. I would further like to have a dpkg-reconfigure script to setup the above issues.

Till then, it's manual configuration.

tags: , ,
posted in MySQL by shlomi

« | »

Follow comments via the RSS Feed | Leave a comment | Trackback URL

28 Comments to "Manually installing multiple MySQL instances on Linux: HOWTO"

  1. Joshua Kugler wrote:

    I could have used these instructions a few months ago. :)

    Oddly enough, the Debian packages for PostgreSQL are all somewhat self contained, and you can install several versions in parallel, and as long as you configure the port before firing up, you can run them all at once, if you really want. It would not be that hard to make the MySQL RPM and debs that would be more parallel-installation friendly.

  2. Anonymous wrote:

    Ever heard of MySQL Sandbox[1]?

    [1]: https://launchpad.net/mysql-sandbox

  3. shlomi wrote:

    Hi,

    Indeed, I should have mentioned sandbox.
    However, sandbox works for multiple installations in advance only, and does not help out with installations which are not simultaneous. Please correct me if I'm wrong.

    Regards

  4. Gregory Haase wrote:

    Don't forget there is also mysqld_multi. (http://dev.mysql.com/doc/refman/5.0/en/mysqld-multi.html). This allows multiple servers using a single set of binaries, is permanent (can survive a restart), and is most likely exactly what you want.

  5. Joshua Kugler wrote:

    MySQL sandbox might have helped me. mysql_multi would not have, as we were installing two different version since we are moving from one version to another.

  6. Log Buffer #136: A Carnival of the Vanities for DBAs wrote:

    [...] Noach offers his HOWTO on manually installing multiple MySQL instances on Linux. He says, “Installing a single MySQL instance on a linux machine is a very simple operation. [...]

  7. shlomi wrote:

    Lots of typos have been fixed in this post, most of which appear to have been created by WordPress trying to outsmart the coder.

  8. Andi wrote:

    How does MySQL know where my.cnf is located. It seems that the alternative version is connecting through the wrong socket and so tries to read /etc/mysql/my.cnf instead of the alternative my.cnf

  9. shlomi wrote:

    Hi Andi,

    Search path is described in: http://dev.mysql.com/doc/refman/5.0/en/option-files.html
    And namely it looks at the installation path per installation.

    But, I must say that I've been using MySQL sandbox quite a lot lately, and, if it suits your needs - it's really great! You should give it a try.

  10. Andi wrote:

    I have been on this site already and just tried to start the alternative server this way:

    bin/mysqld_safe --user=mysqlg --datadir=/usr/local/mysqlg/data &
    [1] 8472
    root@xx:/usr/local/mysqlg# 090612 12:55:15 mysqld_safe Logging to '/usr/local/mysqlg/data/mesolt11.err'.
    090612 12:55:15 mysqld_safe The file /usr/local/mysql/bin/mysqld
    does not exist or is not executable. Please cd to the mysql installation
    directory and restart this script from there as follows:
    ./bin/mysqld_safe&
    See http://dev.mysql.com/doc/mysql/en/mysqld-safe.html for more information

    So you can see that I have set up an own group and user for this version and I have put the edited my.cnf to the local mysql dir.

    Thank you for the tip, I will try the sandbox when I have understood more.

  11. shlomi wrote:

    Andi,

    Try another one: add --basedir=/usr/local/mysqlg/
    Let me know if this worked out

    Shlomi

  12. Pavel wrote:

    Guys thank you for starting this thread. This has been very helpful for me to get up to speed. and getting both of the mysql instances running in parallel

    Andi,
    I've encountered exactly the same problem, try this - i think this will resolve your problem:
    /usr/local/mysqlg/bin/mysqld_safe --defaults-file=/usr/local/mysqlg/my.cnf &

    By doing so you explicitly tell mysql to use your new my.cnf.

    Also, i've made a change in mysqld_safe script as well to use my own user - u might want to try it as well. In my version it's on line 21 user='mysqlg'

  13. Andi wrote:

    I have tried out MySQL sandbox and recommend it to everyone - it's very easy to handle. http://mysqlsandbox.net/

  14. Water ionizer wrote:

    Hi,

    Thanks for the information,just found this post my technorati news feed section! I was searching for this since past 3 months and i am glad to see it here. Thanking you much

    Martin

  15. shlomi wrote:

    Hi Martin,

    I'm glad it helped. But do try MySQL sandbox. It automates so much.

    Shlomi

  16. To not yum or to not apt-get? | code.openark.org wrote:

    [...] written shortly on this before. I like yum; I love apt-get; I prefer not to use them for MySQL [...]

  17. Hasee wrote:

    I created another mysql service in my linux machine as per this document mentioned. Unfortunately I couldn't start the created service. When trying to start the service, it shows some

    errorERROR! MySQL server PID file could not be found!

    /etc/init.d/mysqldtest: line 276: cd: /usr/local/mysql: No such file or directory
    Starting MySQL ERROR! Couldn't find MySQL server (/usr/local/mysql/bin/mysqld_safe)

    Could you please anyone help me... ???

  18. shlomi wrote:

    @Hasee,
    Try setting the basedir param in your /etc/init.d/mysqldtest (somewhere in the top 25 lines of the file) and point it to your installation directory.

  19. Hasee wrote:

    Could you please explain it??? Iam a beginner in mysql with linux

  20. shlomi wrote:

    @Hasee,

    If you are beginner, then perhaps all the above post is not for you. Nevertheless, if you followed it, then you have installed MySQL somewhere. Let's say its /usr/local/mysql55

    In /etc/init.d/mysqldtest, set
    basedir=/usr/local/mysql55

    If you don't manage the above I strongly recommend that you do not attempt a manual dual install of MySQL.

  21. Hasee wrote:

    Iam doing the same thing in Windows successfully. But in linux, I face some difficulties. I follow your valuable suggestions, but Iam facing another problem:

    Starting MySQL..................................................................................................... ERROR! The server quit without updating PID file (/var/lib/mysql/Test.pid).

    Why this happends???

  22. shlomi wrote:

    Windows and Linux make for two completely different installation process. You CAN'T be "doing the same thing".

    I can't presume to help you out by negotiating comments. Check out your MySQL error log is the one thing I can tell you.

    Otherwise, best place to get advice is stackoverflow.com, or otherwise get consulting.

  23. Suryadilaga wrote:

    Hi
    I have this multiple instance on my machine with 3 different port.
    How do I reset root password on every port?
    Thanks

  24. shlomi wrote:

    @Suryadilaga,
    See this post: Recovering a MySQL `root` password: the fourth solution.

  25. suryadilaga wrote:

    Hi shlomi,
    I follow your step using mysql-init and work like a charm.
    Thank you very much

  26. Mysql în paralel cu Percona | Tiberiu Contiu wrote:

    [...] http://code.openark.org/blog/mysql/manually-installing-multiple-mysql-instances-on-linux-howto [...]

  27. Ben wrote:

    Hello, should these steps work when installing 32 and 64 bit versions in parallel? I have a system with 32 bit MySQL installed and in production taht cant be touched or changed, I need to import a database that is in excess of 30G. As this is to big for 32 bit MySQL I need to install 64bit on the same system without touching the 32bit installation. However, when I try and install I keep getting conflicts between the 32 and 64 bit library and dependencies.

  28. shlomi wrote:

    So the problem is not with two MySQL instances, one 32 bit and another 64 bit. The problem is with dependencies on your OS.
    To be honest, I'm not sure. It depends on which OS you've got. Debian based? RedHat based?
    I would suggest trying to set the new instance via mysqlsandbox, and resolve the dependencies one by one. Which packages are failing?

Leave Your Comment

 

 
Powered by Wordpress and MySQL. Theme by openark.org