So this option is many aspects similar to the default one, with one big difference. In this case each web server will have it's own db server. Reason for this: Higher site availability; if one db server is down, second one can continue to serve customers. Also for this case load balancer must to be able to see both installations (this is some kind of prequel for cloud deployment).

On image you can see multiple web servers / db server but in general simplest option is to have 2 db servers.

Drupal 7 – Setup master-master replication with 2 MySQL db servers

Notes:

server 1 name: drupal-db1
IP: 10.0.1.72
server 2 name: drupal-db2
IP: 10.0.101.31

First step is to configure both mysql using the file my.cnf (usualy on /etc/mysql in GNU/Linux):

on drupal-db1 (10.0.1.72), under the mysqld section we add the following:

    server-id=1
    master-host=10.0.101.31
    master-user=repl
    master-password=repl
    master-port=3306
    log-bin
    binlog-do-db=drupaltestsitedb
    replicate-do-db= drupaltestsitedb
    auto_increment_increment = 10
    auto_increment_offset = 1
    # input the database that should be ignored for replication
    binlog-ignore-db=mysql
    binlog-ignore-db=test
    #Minimizes the number of changes you lose if a problem occurs down to 1
    max_allowed_packet = 32M
    # Don't replicate Drupal cache, watchdog, temporary search data
    replicate-wild-ignore-table=DrupalDB%.cache%
    replicate-wild-ignore-table=DrupalDB%.watchdog%
    replicate-ignore-table=DrupalDB.temp_search_sids
    replicate-ignore-table=DrupalDB.temp_search_results

and on drupal-db2 (10.0.101.31), under the same section:

    server-id=2
    master-host=10.0.1.72
    master-user=repl
    master-password=repl
    master-port=3306
    log-bin
    binlog-do-db= drupaltestsitedb
    replicate-do-db= drupaltestsitedb
    auto_increment_increment = 10
    auto_increment_offset = 2
    # input the database that should be ignored for replication
    binlog-ignore-db=mysql
    binlog-ignore-db=test
    #Minimizes the number of changes you lose if a problem occurs down to 1
    max_allowed_packet = 32M

Explanations of these settings

• server-id: this is an integer id helping to identify the server (must be unique in your replication farm!) 
• master-host: specifies the ip/hostname of the MySQL acting as master for the current server 
• master-user: specifies the user used to make the connection to the master 
• master-password: is the user's password 
• master-port: specifies on which port the master is listening 
• log-bin: needed to start the binary logging process 
• binlog-do-db: specifies on which databases the binary logging must be active (only those databases will be in the binary log) 
• replicate-do-db: which database must be replicated by the server as slave. 
• auto_increment_increment: controls the increment between successive AUTO_INCREMENT values. 
• auto_increment_offset: determines the starting point for AUTO_INCREMENT column values.

The last two options are needed to prevent key collisions when using AUTO_INCREMENT columns, otherwise multiple masters may attempt to use the same AUTO_INCREMENT value when inserting rows. It take you down to this:

• auto_increment_increment should be equal to the number of masters you have, N; 
• auto_increment_offset should be different, from 1 to N, on each master.

Optionally two extra options can be added:

• show-slave-auth-info: Display slave usernames and passwords in the output of SHOW SLAVE HOSTS on the master server.
• slave_compressed_protocol={0|1}: this option enable the use of compression for the slave/master protocol if both the slave and the master support it. The default is 0 (no compression). This accelerates the replication process

Creating the replication user on both nodes

On drupal-db2:

    mysql> grant replication slave, replication client on *.* to repl@"drupal-db1" identified by "repl";
    Query OK, 0 rows affected (0.00 sec)
    mysql> show grants for repl@"drupal-db1"; 
    +--+
    | Grants for repl@drupal-db1 |
    +--+
    | GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'repl'@'drupal-db1' IDENTIFIED BY PASSWORD 'ajskdfja77894347' |
    +--+
    1 row in set (0.00 sec)

On drupal-db1:

    mysql> grant replication slave, replication client on *.* to repl@"drupal-db2" identified by "repl";
    Query OK, 0 rows affected (0.00 sec) 
    mysql> show grants for repl@"drupal-db1"; 
    +---+ 
    | Grants for repl@drupal-db2 | 
    +---+ 
    | GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'repl'@'drupal-db2' IDENTIFIED BY PASSWORD 'ajskdfja77894347' | 
    +---+ 
    1 row in set (0.00 sec)

Create the database (in this example the db is not existing): On the master (drupal-db1)

    mysql> create database drupaltestsitedb; Query OK, 1 row affected (0.05 sec) mysql> show master status; 
    ++++--+ 
    | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | 
    ++++--+ 
    | drupal-db1-bin.000004 | 98 | drupaltestsitedb | | 
    ++++--+ 
    1 row in set (0.00 sec) 
    mysql> show slave status; 
    *************************** 1. row *************************** 
    Slave_IO_State: Connecting to master 
    Master_Host: 10.0.0.2 
    Master_User: repl 
    Master_Port: 3306 
    Connect_Retry: 16 
    Master_Log_File: localhost-bin.000001 
    Read_Master_Log_Pos: 310 
    Relay_Log_File: drupal-db1-relay-bin.000006 
    Relay_Log_Pos: 98 
    Relay_Master_Log_File: localhost-bin.000001 
    Slave_IO_Running: No 
    Slave_SQL_Running: Yes 
    Replicate_Do_DB: drupaltestsitedb 
    Replicate_Ignore_DB: 
    Replicate_Do_Table: 
    Replicate_Ignore_Table: 
    Replicate_Wild_Do_Table: 
    Replicate_Wild_Ignore_Table: 
    Last_Errno: 0 
    Last_Error: 
    Skip_Counter: 0 
    Exec_Master_Log_Pos: 310 
    Relay_Log_Space: 98 
    Until_Condition: None 
    Until_Log_File: 
    Until_Log_Pos: 0 
    Master_SSL_Allowed: No 
    Master_SSL_CA_File: 
    Master_SSL_CA_Path: 
    Master_SSL_Cert: 
    Master_SSL_Cipher: 
    Master_SSL_Key: 
    Seconds_Behind_Master: NULL 
    1 row in set (0.00 sec)

on the slave:

    mysql> show master status; 
    +++++ 
    | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | 
    +++++ 
    | localhost-bin.000001 | 98 | drupaltestsitedb | | 
    +++++ 
    1 row in set (0.00 sec) 
    mysql> show master status; 
    *************************** 1. row *************************** 
    File: localhost-bin.000001 
    Position: 98 
    Binlog_Do_DB: drupaltestsitedb 
    Binlog_Ignore_DB: 
    1 row in set (0.00 sec) 
    mysql> show slave status; 
    *************************** 1. row *************************** 
    Slave_IO_State: Waiting for master to send event 
    Master_Host: 10.0.1.72 
    Master_User: repl 
    Master_Port: 3306 
    Connect_Retry: 60 
    Master_Log_File: drupal-db1-bin.000004 
    Read_Master_Log_Pos: 98 
    Relay_Log_File: localhost-relay-bin.000006 
    Relay_Log_Pos: 237 
    Relay_Master_Log_File: drupal-db1-bin.000004 
    Slave_IO_Running: Yes 
    Slave_SQL_Running: Yes 
    Replicate_Do_DB: drupaltestsitedb 
    Replicate_Ignore_DB: 
    Replicate_Do_Table: 
    Replicate_Ignore_Table: 
    Replicate_Wild_Do_Table: 
    Replicate_Wild_Ignore_Table: 
    Last_Errno: 0 
    Last_Error: 
    Skip_Counter: 0 
    Exec_Master_Log_Pos: 98 
    Relay_Log_Space: 237 
    Until_Condition: None 
    Until_Log_File: 
    Until_Log_Pos: 0 
    Master_SSL_Allowed: No 
    Master_SSL_CA_File: 
    Master_SSL_CA_Path: 
    Master_SSL_Cert: 
    Master_SSL_Cipher: 
    Master_SSL_Key: 
    Seconds_Behind_Master: 0
     1 row in set (0.00 sec)

We can also check this in the status on the master:

    mysql> show master status; 
    ++++--+ 
    | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | 
    ++++--+ 
    | drupal-db1-bin.000004 | 258 | replication | | 
    ++++--+ 
    1 row in set (0.00 sec)

and on the slave:

    mysql> show slave status; 
    *************************** 1. row *************************** 
    Slave_IO_State: Waiting for master to send event
    Master_Host: 10.0.1.72 
    Master_User: repl 
    Master_Port: 3306 
    Connect_Retry: 60 
    Master_Log_File: drupal-db1-bin.000004 
    Read_Master_Log_Pos: 258 
    Relay_Log_File: localhost-relay-bin.000006 
    Relay_Log_Pos: 397 
    Relay_Master_Log_File: drupal-db1-bin.000004 
    Slave_IO_Running: Yes 
    Slave_SQL_Running: Yes 
    Replicate_Do_DB: drupaltestsitedb 
    Replicate_Ignore_DB: 
    Replicate_Do_Table: 
    Replicate_Ignore_Table: 
    Replicate_Wild_Do_Table: 
    Replicate_Wild_Ignore_Table: 
    Last_Errno: 0 
    Last_Error: 
    Skip_Counter: 0 
    Exec_Master_Log_Pos: 258 
    Relay_Log_Space: 397 
    Until_Condition: None 
    Until_Log_File: 
    Until_Log_Pos: 0 
    Master_SSL_Allowed: No 
    Master_SSL_CA_File: 
    Master_SSL_CA_Path: 
    Master_SSL_Cert: 
    Master_SSL_Cipher: 
    Master_SSL_Key: 
    Seconds_Behind_Master: 0
    1 row in set (0.00 sec)

Some maintenance

It's also nice to know some maintenance commands. The first dba replication action will be to manage the binary logs that could increase very quickly.

To see the logs use the following command:

    mysql> show master logs;
    ++---+ 
    | Log_name | File_size | 
    ++---+ 
    | drupal-db1-bin.000001 | 381 | 
    | drupal-db1-bin.000002 | 117 | 
    | drupal-db1-bin.000003 | 219 | 
    | drupal-db1-bin.000004 | 427 | 
    | drupal-db1-bin.000005 | 739 | 
    ++---+
     5 rows in set (0.00 sec)

Then after having done a backup (long silence), you can purge then since a date (could be a day in the past or even now:

    mysql> purge master logs before now(); 
    Query OK, 0 rows affected (0.00 sec) 
    mysql> show master logs; 
    ++---+ 
    | Log_name | File_size | 
    ++---+ 
    | drupal-db1-bin.000005 | 739 | 
    ++---+ 
    1 row in set (0.00 sec)

or

    mysql> show master logs; 
    ++-+ 
    | Log_name | File_size | 
    ++-+ 
    | localhost-bin.000001 | 271 | 
    | localhost-bin.000002 | 851 | 
    ++-+ 
    2 rows in set (0.00 sec) 
    mysql> purge master logs to 'localhost-bin.000002'; 
    Query OK, 0 rows affected (0.00 sec) 
    mysql> show master logs; 
    ++-+ 
    | Log_name | File_size | 
    ++-+ 
    | localhost-bin.000002 | 851 | 
    ++-+ 
    1 row in set (0.00 sec)

To reset the slave and the master you should use:

    reset master; reset slave;