This ist HowTo setup MySQL mutli-master replication for fail-over scenarios, a basic step by step guide for setting up a replication system where all nodes act as master and slave at the same time, with a built-in fail-over mechanism.
This can be very useful for sharing SpamAssassin's and dspam's Bayes data and amavisd-new's SQL lookups and storage between mailgateways ;)

First we'll start with installing and setting up MySQL.
List MySQL, its dependencies, and the USE flags they take. If you missed a USE flag, you can always edit your package.use and add it

# emerge dev-db/mysql -av

Install MySQL, and any dependencies it may have by simply answering with yes.

Creating the Databases

As asked by the mysql ebuild please run this to create the initial mysql databases

emerge --config =dev-db/mysql-5.0.60

Replace '5.0.60' with the version of MySQL that you have installed.

Complete any directions output by the config.

Starting MySQL
Start MySQL

/etc/init.d/mysql start

To add MySQL load at boot add it to the default runlevel.

rc-update add mysql default

Testing

To test your install login to mysql and provide your admin password.

# mysql -u root --password

you should receive a mysql prompt

mysql>

type in the following command to see if the databases were created properly

mysql>show databases;

and if you receive a list of databases you're all set.

Setting up replication

Two servers srv-01(Master1/Slave2) and srv-02(Master2/Slave1) are used in this example setup. Their basic configuration is:

node A - (srv-01) setup

# nano /etc/mysql/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
old_passwords=1
log-bin
binlog-do-db=testDB
binlog-ignore-db=mysql
binlog-ignore-db=test
server-id = 1
#auto_increment_increment = 10
#auto_increment_offset = 1
#master-host = srv-02
#master-user = replication
#master-password = slave

On srv-01, create a replication slave account in mysql.

# mysql -p
mysql> grant replication slave on *.* to 'replication'@srv-02 identified by 'slave';

and restart the mysql srv-01.

Now edit my.cnf on srv-02 (Slave1/Master2)

node B - (srv-02) setup

# nano /etc/mysql/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
old_passwords=1
server-id = 2
auto_increment_increment = 10
auto_increment_offset = 2
master-host = srv-01
master-user = replication
master-password = slave

and restart the mysql srv-02.

Choosing appropriate values for the auto_increment_increment and auto_increment_offset server variables prevents conflicts between auto-generated keys in this circular replication setup.

The two important issues here are that you can use circular replication almost seamlessly in any application that now uses a single database server, and that performance is adequate for our purposes.

Open a client and start the slave on srv-02 and check it's status

mysql> start slave;
mysql> show slave status\G;

In the Status output the related log files have to be indicated and Slave_IO_Running and Slave_SQL_Running: must be set to YES.

On srv-01 check the master status:

mysql> show master status;

This scenario is for master-slave, now we will create a slave master scenario for the same systems and it will work as master master. Write down the binlog filename and it's position.

Now edit my.cnf on srv-02 and add the following

# nano /etc/mysql/my.cnff
log-bin
binlog-do-db=testDB

srv-01 already has got a replication account, now we will create one on srv-02 for srv-01

# mysql -p
mysql> grant replication slave on *.* to 'replication'@srv-01 identified by 'slave2';

Edit my.cnf on srv-01 and add information for its master (srv-02).

# nano /etc/mysql/my.cnf
auto_increment_increment = 10
auto_increment_offset = 1
master-host = srv-02
master-user = replication
master-password = slave

Restart both mysql Master1 and Master2.

On srv-01:

mysql -p
mysql> start slave;
mysql> show slave status\G;

On srv-02:

mysql -p
mysql > show master status;

Make sure its running, check the logfile and position. Now you can create tables in the database and you will see changes in slave.