Multi-Master single slave for mysql

given below are the steps for setting up the multi master single slave setup.

steps for having multi-master single slave setup

1. generally one mysql server can have only one master but in order to achieve multi-master single slave we can have some work around methods

1. we can have multiple instances in mysql using mysqld_multi command for details give below are the url
http://dev.mysql.com/doc/refman/4.1/en/mysqld-multi.html

2. we have to configure multiple instances say for eg ( mysql running on different ports 3306 and 3307).

3. for example 3306 is the slave of server-1 and 3307 is the slave of server-2

4. since 3306 and 3307 are lying on the same server

5. we can create a federated tables on 3306 servers for 3307 server.

6. given below are the scripts for generating federated tables for each databases.
7. store the output in federated.sql files
8. execute the federated.sql file on 3306 server


####to create the federated tables the code is as follows #############

#!/bin/sh

USER=root
DATABASE_NAME=test

# The default connection string to use
CONNECTION="MYSQL://127.0.0.1:3306/test2"

# Get the list of table names
TABLE_LIST=`mysql -u$USER $DATABASE_NAME -e 'SHOW TABLES\G' | sed -n "/^Tables_in_${DATABASE_NAME}:/s/^.*: //p"`
#echo $TABLE_LIST

# For each table in the list
for TABLE in $TABLE_LIST; do
                # Get the SHOW CREATE TABLE
                mysqldump -u$USER --no-data $DATABASE_NAME $TABLE \
                | sed "s!^) ENGINE=.*!) ENGINE=FEDERATED CONNECTION='${CONNECTION}/${TABLE}';!"
done


########################The sample my.cnf file is as follows ######################

[mysqld]

datadir=/var/lib/mysql/
socket=/var/lib/mysql/mysql.sock
user=mysql

# Server ID should always be there preferably IP address
server-id=111

log-slow-queries=/var/log/mysql/slow-log
long_query_time = 0.5
log-queries-not-using-indexes

# 50-80% of RAM
innodb_buffer_pool_size=4G

# innodb_data_file_path=ibdata1:2000M;ibdata2:10M:autoextend
innodb_log_file_size=100M
innodb_log_buffer_size = 16M
innodb_flush_log_at_trx_commit=2
#innodb_lock_wait_timeout = 50
innodb_additional_mem_pool_size=16M
innodb_flush_method=O_DIRECT

########## Multiple mysql instances #########

[mysqld_multi]
mysqld     = /usr/bin/mysqld_safe
mysqladmin = /usr/bin/mysqladmin
user       = multi_admin
password   = multipass

[mysqld3307]
socket     = /tmp/mysql.sock3307
port       = 3307
pid-file   = /var/lib/mysql3307/hostname.pid3307
datadir    = /var/lib/mysql3307

[mysqld3308]
socket     = /tmp/mysql.sock3308
port       = 3308
pid-file   = /var/lib/mysql3308/hostname.pid3308
datadir    = /var/lib/mysql3308

[mysqld_safe]
# default 1024
open_files_limit=4096
log-error=/var/log/mysqld.log
#pid-file=/var/run/mysqld/mysqld.pid

################################To start the instance the command is as follows###########

mysqld_multi start 3307
mysqld_multi start 3307

##########################################################################################

For more details the url is as follows:

 http://dev.mysql.com/doc/refman/4.1/en/mysqld-multi.html
http://dev.mysql.com/doc/refman/5.0/en/federated-use.html
http://dev.mysql.com/doc/refman/5.0/en/federated-limitations.html