Mysql: Multi-Master Replication

Posted on Posted in Mysql

Greeting to all readers, the first thing is to apologize for not write time ago but I have been studying the LPIC-2 Certification ;-).

In this article I’ll explain how mount a Mysql Multi-Master replication, this system consist in two databases running in separate servers, the production machine, with read/write access, and the read-only CDR server.

Configuration

Active –> Passive replication

Server1

This is the Mysql active server with read/write access, the changes that commit in this server will be replicated to the passive Server2.

my.cnf

Edit your Mysql configuration file, normally /etc/my.cnf

  • log-bin: The label of your binary log
  • server-id: 1 because is the first Mysql Master server
  • max_binarylog_size: The maximum size of the binary logs
  • expire_logs_days: The binary logs policy retention
  • report-host: The host name or IP address of the slave to be reported to the master during slave registration
...
[mysqld]
log-bin=mysql-bin
server-id = 1
max_binlog_size=50M
expire_logs_days=15
report-host = server1.albertolarripa.com
  • binlog-ignore-db: If  you need not replicate one database, simply specify it like
binlog-ignore-db = db_cache

Grants

Now create the replication user with the following permissions

mysql> grant REPLICATION SLAVE on *.* to 'replicator'@'%' identified by 'password';

Dump Database

We perform a full export of the database that contains the binary log position

[root@server1]# time /usr/local/etc/mysql/bin/mysqldump --defaults-file=/etc/my.cnf -uroot -p<password> --all-databases --master-data > /backup/mysql.all.backup-20121021

Master status

Take the Binary name and the Master position

mysql> show master status;
+------------------------+----------+--------------+------------------+
| File                   | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------------+----------+--------------+------------------+
| mysql-bin.001568       |      107 |              | db_cache         |
+------------------------+----------+--------------+------------------+

Server2

This is passive server with read-only access, normally this server received the changes from the active server, but in case of disaster, this server wake up with write access.

my.cnf

Like the server1 configuration but changing the server-id and the report-host

...
[mysqld]
log-bin=mysql-bin
server-id = 2
max_binlog_size=50M
expire_logs_days=15
report-host = server2.albertolarripa.com
binlog-ignore-db = db_cache

Activate the read-only access adding this option in your configuration file

read_only

Import Database

Before setup the replication import the database with the previous export file

mysql> source /backup/mysql.all.backup-20121021

Master configuration

Logon in your mysql passive server and configure the master information

  • master_host: The server1 IP
  • master_port: Mysql server1 port
  • master_log_file: This is the name of the binary log that obtain previously
  • master_log_pos: And the active binary log position
mysql> change master to
 -> master_host='192.168.100.1',
 -> master_port=3306,
 -> master_user='replicator',
 -> master_password='password',
 -> master_log_file='mysql-bin.001568',
 -> master_log_pos=107;

Before start the slave, check if the information introduced are correct

mysql> show slave status \G

If all is OK, start the slave

mysql> show slave status \G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 159.237.100.1
                  Master_User: replicator
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.001568
          Read_Master_Log_Pos: 107
               Relay_Log_File: mysql-relay-bin.000028
                Relay_Log_Pos: 259
        Relay_Master_Log_File: mysql-bin.001568
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          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: 107
              Relay_Log_Space: 424
              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
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 1

 Passive <– Active replication

Now is the moment to configure the opposite replication, this replication don’t affect the Mysql active server because the passive server is in read mode, and no change is replicated.

Server2

Grants

In Mysql the REPLICATION permissions won’t replicate to others servers, is necessary create new user with this grant.

mysql> grant REPLICATION SLAVE on *.* to 'replicator'@'%' identified by 'password';

Master status

Take the Binary name and the Master position of you passive Master server.

mysql> show master status;
+------------------------+----------+--------------+------------------+
| File                   | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------------+----------+--------------+------------------+
| mysql-bin.000003       |      107 |              | db_cache         |
+------------------------+----------+--------------+------------------+

Server1

Master configuration

Logon in your mysql active server and configure the master information

  • master_host: The server2 IP
  • master_port: Mysql server2 port
  • master_log_file: This is the name of the binary log that obtain previously
  • master_log_pos: And the active binary log position
mysql> change master to
 -> master_host='192.168.200.1',
 -> master_port=3306,
 -> master_user='replicator',
 -> master_password='password',
 -> master_log_file='mysql-bin.000003',
 -> master_log_pos=107;

Before start the slave, check if the information introduced are correct

mysql> show slave status \G

If all is OK, start the slave

mysql> show slave status \G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 159.237.200.1
                  Master_User: replicator
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000003
          Read_Master_Log_Pos: 107
               Relay_Log_File: mysql-relay-bin.000002
                Relay_Log_Pos: 259
        Relay_Master_Log_File: mysql-bin.000003
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          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: 107
              Relay_Log_Space: 424
              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
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 2

Ok we’re done with the configuration, both replications are running.

Manage

To change between active<–>passive mode you only need to change the write<–>read role’s.

For example if you need startup your passive server like active, with write access, you’ll follow this operation:

  1. Stop Mysql passive server (server2)
  2. Stop Mysql active server (server1)
  3. Comment in your server2 /etc/my.cnf file, the read_only option
  4. Start the new Mysql active server (server2)
  5. Add the read_only option in you new Mysql passive server (server1) /etc/my.cnf file
  6. Start both Mysql slave’s and check

And this is all, the configuration is very simple but very robust, as characterizes Mysql 😉

Leave a Reply

Your email address will not be published. Required fields are marked *