Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

Do you know any 'Debian way' of setting up multiple MySQL instances on a single server? The server would receive data replicated from multiple remote databases.

I could use scripts like mysqlsandbox but I prefer to stick to Debian packages and would like to be able to upgrade the setup without much complications in the future. Another solution is mysqlmanager - it works with MySQL 5.1 but it's deprecated and not released anymore with 5.5.

So what's the 'best practice' of running multiple MySQL instances on a single debian server?

share|improve this question

1 Answer 1

up vote 7 down vote accepted

I believe it's as simple as running mysqld_multi and setting up your my.cnf correctly. mysqld_multi is a part of the mysql distribution - not a packaged frill.

There is a patch to make /usr/share/mysql/mysqld_multi.server appropriate for use in /etc/init.d and another patch to make mysqld_multi use files in /etc/mysql/conf.d.

You can initialize MySQL data directories for new instances with the mysql_install_db command like this:

mysql_install_db --datadir=/var/lib/mysql2

Don't forget to change the root password of the newly created instance:

mysqladmin --port 3307 --user=root password 'new-password'
share|improve this answer
    
thanks; do you use it in practice? any horror stories/hints? –  pQd Mar 10 '12 at 13:15
1  
My horror story is a 2 days long data recovery because of debian's mysql init script was not shutting down gracefully the mysql because shutdown took too long. Since then i do not use debian mysql package, only Percona's binary distribution and the initscript in it –  Gabor Vincze Mar 10 '12 at 14:58
    
@GaborVincze - was it in mysqld_multi setup or 'regular' single-mysql-instance installation? –  pQd Mar 10 '12 at 16:37
    
It was a regular installation. An 5.1 –  Gabor Vincze Mar 10 '12 at 19:06
1  
I do use mysqld_multi in practice. The hardest part is that you have to use mysqld_multi/mysqladmin to start/shut down the instances versus /etc/init.d/mysqld restart - there's not a terrible lot of pre-canned scripts that make it easy. Once you master it, it becomes old hat and is a very convenient way to spin up an instance for say, recovery, delayed replication - whatever. –  thinice Mar 10 '12 at 22:00

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.