Just had this scenario:
Servers A, B, and C each running a different rails app using a MySQL DB installed locally on each server. Had server D that should work as a slave for each of the DBs in order to have an up-to-date copy of the DBs in case of a HD crash. The backups of the DBs are also performed from the slave in order to avoid the locking of the DB on the prod servers in connection with the mysqldump command.
So how do we do that?
First step is to be able to run multiple MySQL instances on server D.
Seems that the preferred way to do this with MySQL 5.0 is to use the MySQL Instance Manager.
Unfortunately, the /etc/init.d/mysql script you get when installing MySQL on Ubuntu using
apt-get does not use the MySQL instance manager.
So I installed from source:
wget http://dev.mysql.com/get/Downloads/MySQL-5.0/mysql-5.0.85.tar.gz/from/http://mirrors.dotsrc.org/mysql/
tar xvzf mysql-5.0.85.tar.gz
cd mysql-5.0.85/
CFLAGS="-O3" CXX=gcc CXXFLAGS="-O3 -felide-constructors \
-fno-exceptions -fno-rtti" ./configure \
--prefix=/usr/local/mysql --enable-assembler \
--with-mysqld-ldflags=-all-static
make
sudo make install
Setup some symlinks
sudo ln -s /usr/local/mysql/bin/mysql /usr/local/bin
sudo ln -s /usr/local/mysql/bin/mysqldump /usr/local/bin
sudo ln -s /usr/local/mysql/libexec/mysqlmanager /usr/local/sbin
Installed the /etc/init.d/mysql script and made it use the MySQL Instance Manager
sudo sh -c "sed 's/use_mysqld_safe=1/use_mysqld_safe=0/' support-files/mysql.server > /etc/init.d/mysql"
sudo chmod 755 /etc/init.d/mysql
sudo update-rc.d mysql defaults
Installed the MySQL configuration file. Note that this lives in /etc/my.cnf and _not_ in /etc/mysql/my.cnf
sudo cp support-files/my-large.cnf /etc/my.cnf
Added the following to the top of /etc/my.cnf
[manager]
socket = /var/lib/mysql/manager.sock
pid-file = /var/run/mysql/manager.pid
password-file = /etc/mysqlmanager.passwd
monitoring-interval = 3600
user = mysql
log = /var/log/mysql/mysql-man.log
run-as-service
[mysql.server]
use-manager
Create the mysql user and the necessary directories
sudo groupadd mysql
sudo useradd -g mysql mysql
sudo mkdir -p /var/lib/mysql /var/run/mysql /var/log/mysql
sudo chown mysql:mysql /var/lib/mysql /var/run/mysql /var/log/mysql
Create the mysqlmanager password
sudo sh -c "mysqlmanager --passwd > /etc/mysqlmanager.passwd"
sudo chown mysql:mysql /etc/mysqlmanager.passwd
sudo chmod 600 /etc/mysqlmanager.passwd
Create the data directories
sudo /usr/local/mysql/bin/mysql_install_db --user=mysql --datadir=/usr/local/mysql/var/data
sudo /usr/local/mysql/bin/mysql_install_db --user=mysql --datadir=/usr/local/mysql/var/data1
sudo /usr/local/mysql/bin/mysql_install_db --user=mysql --datadir=/usr/local/mysql/var/data2
Replace the [mysqld] section in /etc/my.cnf with the following:
[mysqld]
datadir=/usr/local/mysql/var/data
port = 3306
socket = /tmp/mysql.sock
log-bin=mysql-bin
server-id = 10
relay_log = mysql-relay-bin
log_slave_updates = 1
[mysqld1]
datadir=/usr/local/mysql/var/data1
port = 3307
socket = /tmp/mysql1.sock
log-bin=mysql-bin
server-id = 11
relay_log = mysql-relay-bin
log_slave_updates = 1
[mysqld2]
datadir=/usr/local/mysql/var/data2
port = 3308
socket = /tmp/mysql2.sock
log-bin=mysql-bin
server-id = 12
relay_log = mysql-relay-bin
log_slave_updates = 1
Start the MySQL server
sudo /etc/init.d/mysql start
Connect to the MySQL Instance Manager
mysql -u root --socket=/var/lib/mysql/manager.sock -p
mysql> show instances;
+---------------+--------+
| instance_name | status |
+---------------+--------+
| mysqld | online |
| mysqld2 | online |
| mysqld1 | online |
+---------------+--------+
Yay!
Exit and connect to the MySQL DB running on port 3308:
mysql -u root -P 3308 -h 127.0.0.1Note that you need to specify the host (-h) option. Otherwise, the mysql command will ignore the port option and just connect to the default instance running on port 3306.
I will make a followup post on how to setup the actual replication. Hope someone finds this useful 
