MySQL Master-Master Replication

To set up a dual master replication with MySQL, only some modification must be done, it's very simple and there is a lot of howto's on the topic. This is my contribution with a basic setup and I've tried to gather common commands to maintain such solution.

The big picture :

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

To set up a dual master replication with MySQL, only some modification must be done, it’s very simple and there is a lot of howto’s on the topic. This is my contribution with a basic setup and I’ve tried to gather common commands to maintain such solution.

The big picture :


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

on matisse (192.168.14.13), under the mysqld section we add the following :

server-id=1
master-host=192.168.14.3
master-user=repl
master-password=repl
master-port=3306
log-bin
binlog-do-db=replication
replicate-do-db=replication
auto_increment_increment      = 10
auto_increment_offset         = 1

and on picasso (192.168.14.3), under the same section :

server-id=2
master-host=192.168.14.13
master-user=repl
master-password=repl
master-port=3306
log-bin
binlog-do-db=replication
replicate-do-db=replication
auto_increment_increment      = 10
auto_increment_offset         = 2

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.

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 miro :

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

on matisse :

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

Create the database (in this example the db is not existing) :

on the master (matisse)

mysql> create database replication;
Query OK, 1 row affected (0.05 sec)
mysql> show master status;
++++--+
| File               | Position | Binlog_Do_DB | Binlog_Ignore_DB |
++++--+
| matisse-bin.000004 |       98 | replication  |                  | 
++++--+
1 row in set (0.00 sec)
mysql> show slave status\G
*************************** 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: matisse-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: replication
        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 | replication | |
+++++
1 row in set (0.00 sec)

mysql> show master status\G

*************************** 1. row ***************************

           File: localhost-bin.000001
       Position: 98
   Binlog_Do_DB: replication

Binlog_Ignore_DB:
1 row in set (0.00 sec)

mysql> show slave status\G
*************************** 1. row ***************************
             Slave_IO_State: Waiting for master to send event
                Master_Host: 192.168.14.13
                Master_User: repl
                Master_Port: 3306
              Connect_Retry: 60
            Master_Log_File: matisse-bin.000004
        Read_Master_Log_Pos: 98
             Relay_Log_File: localhost-relay-bin.000006
              Relay_Log_Pos: 237
      Relay_Master_Log_File: matisse-bin.000004
           Slave_IO_Running: Yes
          Slave_SQL_Running: Yes
            Replicate_Do_DB: replication
        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)

Then, we create a table on the master (matisse) :

mysql> create table users (
    -> id int(4) auto_increment not null primary key,
    -> name varchar(15));
Query OK, 0 rows affected (0.01 sec)

on the slave (miro) :

mysql> use replication;
Database changed
mysql> show tables;
+---+
| Tables_in_replication |
+---+
| users                 | 
+---+
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 |
++++--+
| matisse-bin.000004 |      258 | replication  |                  | 
++++--+
1 row in set (0.00 sec)

and on the slave :

mysql> show slave status\G
*************************** 1. row ***************************
             Slave_IO_State: Waiting for master to send event
                Master_Host: 192.168.14.13
                Master_User: repl
                Master_Port: 3306
              Connect_Retry: 60
            Master_Log_File: matisse-bin.000004
        Read_Master_Log_Pos: 258
             Relay_Log_File: localhost-relay-bin.000006
              Relay_Log_Pos: 397
      Relay_Master_Log_File: matisse-bin.000004
           Slave_IO_Running: Yes
          Slave_SQL_Running: Yes
            Replicate_Do_DB: replication
        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)

And now we can create records. The first scenario is the following :

1) add 2 records on the master
2) add 2 records on the slave

on the master :

mysql> insert into users values (0,"fred"),(0,"vanne");
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

on the slave :

mysql> select * from users;
++---+
| id | name  |
++---+
|  1 | fred  | 
| 11 | vanne | 
++---+
2 rows in set (0.00 sec)
mysql> insert into users values (0,"raskas"),(0,"stintel");
Query OK, 2 rows affected (0.00 sec) 
Records: 2  Duplicates: 0  Warnings: 0

on the master :

mysql> select * from users;
++-+
| id | name    |
++-+
|  1 | fred    | 
| 11 | vanne   | 
| 12 | raskas  | 
| 22 | stintel | 
++-+

Good ! Our first master-master replication is working !

Now let stop the slave and add 2 extar records on the master :

mysql> slave stop;
Query OK, 0 rows affected (0.00 sec

on the master :

mysql> insert into users values (0,"sdog"),(0,"jacke");
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

and on the slave :

mysql> select * from users;
++-+
| id | name    |
++-+
|  1 | fred    | 
| 11 | vanne   | 
| 12 | raskas  | 
| 22 | stintel | 
++-+
4 rows in set (0.00 sec)

let’s try to add some data on the slave too (the databases are not synchronized, but matisse is still slave of miro) :

on miro (the slacve):

mysql> insert into users values (0,"jozzel"),(0,"pleemans");
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0
mysql> select * from users;
++--+
| id | name     |
++--+
|  1 | fred     | 
| 11 | vanne    | 
| 12 | raskas   | 
| 22 | stintel  | 
| 32 | jozzel   | 
| 42 | pleemans | 
++--+
6 rows in set (0.00 sec)

on matisse (the master) :

mysql> select * from users;
++--+
| id | name     |
++--+
|  1 | fred     | 
| 11 | vanne    | 
| 12 | raskas   | 
| 22 | stintel  | 
| 31 | sdog     | 
| 41 | jacke    | 
| 32 | jozzel   | 
| 42 | pleemans | 
++--+
8 rows in set (0.00 sec)

now let’s start slave again on miro :

mysql> slave start;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from users;
++--+
| id | name     |
++--+
|  1 | fred     | 
| 11 | vanne    | 
| 12 | raskas   | 
| 22 | stintel  | 
| 32 | jozzel   | 
| 42 | pleemans | 
| 31 | sdog     | 
| 41 | jacke    | 
++--+
8 rows in set (0.00 sec)

Ok this is great an works like expected.

For our last test we gonna add some new records on both nodes but previously we gonna stop both slave process :

on matisse :

mysql> insert into users values (0,"alain"),(0,"tux");
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0
mysql> select * from users;
++--+
| id | name     |
++--+
|  1 | fred     | 
| 11 | vanne    | 
| 12 | raskas   | 
| 22 | stintel  | 
| 31 | sdog     | 
| 41 | jacke    | 
| 32 | jozzel   | 
| 42 | pleemans | 
| 51 | alain    | 
| 61 | tux      | 
++--+
10 rows in set (0.00 sec)

on miro :
(I will even remove a record)

mysql> insert into users values (0,"billou"),(0,"Linus");
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> delete from users where id=22;
Query OK, 1 row affected (0.02 sec)
mysql> select * from users;
++--+
| id | name     |
++--+
|  1 | fred     | 
| 11 | vanne    | 
| 12 | raskas   | 
| 32 | jozzel   | 
| 42 | pleemans | 
| 31 | sdog     | 
| 41 | jacke    | 
| 52 | billou   | 
| 62 | Linus    | 
++--+
9 rows in set (0.00 sec)

let’s restart both slaves :

from matisse :

mysql> select * from users;
++--+
| id | name     |
++--+
|  1 | fred     | 
| 11 | vanne    | 
| 12 | raskas   | 
| 31 | sdog     | 
| 41 | jacke    | 
| 32 | jozzel   | 
| 42 | pleemans | 
| 51 | alain    | 
| 61 | tux      | 
| 52 | billou   | 
| 62 | Linus    | 
++--+
11 rows in set (0.00 sec)

from miro :

mysql> select * from users;
++--+
| id | name     |
++--+
|  1 | fred     | 
| 11 | vanne    | 
| 12 | raskas   | 
| 51 | alain    | 
| 32 | jozzel   | 
| 42 | pleemans | 
| 31 | sdog     | 
| 41 | jacke    | 
| 52 | billou   | 
| 62 | Linus    | 
| 61 | tux      | 
++--+
11 rows in set (0.00 sec)

and what happens if we try do delete the same record when both slaves are stopped ?

on both :

mysql> slave stop;
Query OK, 0 rows affected (0.00 sec)
mysql> delete from users where id =52;
Query OK, 1 row affected (0.00 sec)
mysql> slave start;
Query OK, 0 rows affected (0.01 sec)
mysql> select * from users;
++--+
| id | name     |
++--+
|  1 | fred     | 
| 11 | vanne    | 
| 12 | raskas   | 
| 31 | sdog     | 
| 41 | jacke    | 
| 32 | jozzel   | 
| 42 | pleemans | 
| 51 | alain    | 
| 61 | tux      | 
| 62 | Linus    | 
++--+
10 rows in set (0.00 sec)
mysql> show slave status\G
*************************** 1. row ***************************
             Slave_IO_State: Waiting for master to send event
                Master_Host: 192.168.14.3
                Master_User: repl
                Master_Port: 3306
              Connect_Retry: 16
            Master_Log_File: localhost-bin.000002
        Read_Master_Log_Pos: 851
             Relay_Log_File: matisse-relay-bin.000009
              Relay_Log_Pos: 344
      Relay_Master_Log_File: localhost-bin.000002
           Slave_IO_Running: Yes
          Slave_SQL_Running: Yes
            Replicate_Do_DB: replication
        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: 851
            Relay_Log_Space: 344
            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)

cool ! no errors and everything seems to be as it should be.

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 |
++---+
| matisse-bin.000001 |       381 | 
| matisse-bin.000002 |       117 | 
| matisse-bin.000003 |       219 | 
| matisse-bin.000004 |       427 | 
| matisse-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 |
++---+
| matisse-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;

Next time I’ll discuss about a case study on migrating replicated data on none synchronized tables.

Subscribe to Blog via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

19 Comments

  2. Let say I have 3 servers A, B & C. Server A will be my main Master server. It will be configured to handle the replication schedule to server B and C. Server B & C are not sync. So when server A connect to server B, both database will be replicated. When server A connect to server C, both also will be replicated. I am still new to replication technology. I had tested on dual master replication, it works fantastic. Just wondering how can I configure A <-> B and A <-> C replication. Highly appreciate for your kindly help !

    • Hi! i am new in mysql and trying to establish master master replication in mysql

      on first server i have added these lines to mysqld section of my.cnf(mysql Ver 14.12 Distrib 5.0.77, for redhat-linux-gnu (i686) using readline 5.1)

      datadir=/var/lib/mysql
      socket=/var/lib/mysql/mysql.sock
      log-bin=/var/lib/mysql/mysql-bin.log
      binlog-do-db=sample1
      binlog-ignore-db=mysql
      binlog-ignore-db=information_schema
      binlog-ignore-db=test2
      binlog-ignore-db=sample3
      binlog-ignore-db=example3
      binlog-ignore-db=endpoints
      binlog-ignore-db=meetme
      binlog-ignore-db=test
      binlog-ignore-db=sample2

      server-id=1
      master-host = 192.xxx.x.xxx
      master-user = abc
      master-password = abc_pass
      master-connect-retry = 60

      relay-log = /var/lib/mysql/slave-relay.log
      relay-log-index = /var/lib/mysql/slave-relay-log.index

      On second server i have added these lines to mysqld section of my.cnf(mysql Ver 14.14 Distrib 5.1.41, for debian-linux-gnu (i486) using readline 6.1)

      server-id=2

      master-host = 192.xxx.x.xxx
      master-user = xyz
      master-password = xyz_pass
      master-connect-retry = 60

      relay-log = /var/lib/mysql/slave-relay.log
      relay-log-index = /var/lib/mysql/slave-relay-log.index

      log-bin = /var/lib/mysql/mysql-bin.log
      binlog-do-db = sample1
      binlog-ignore-db=mysql
      binlog-ignore-db=information_schema
      binlog-ignore-db=phpmyadmin

      on first server slave status is

      *************************** 1. row ***************************
      Slave_IO_State: Connecting to master
      Master_Host: 192.xxx.x.xx
      Master_User: abc
      Master_Port: 3306
      Connect_Retry: 60
      Master_Log_File: mysql-bin.000001
      Read_Master_Log_Pos: 36158
      Relay_Log_File: slave-relay.000001
      Relay_Log_Pos: 98
      Relay_Master_Log_File: mysql-bin.000001
      Slave_IO_Running: No
      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: 36158
      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)

      ERROR:
      No query specified

      On second server slave status is

      *************************** 1. row ***************************
      Slave_IO_State: Connecting to master
      Master_Host: 192.xxx.x.xxx
      Master_User: xyz
      Master_Port: 3306
      Connect_Retry: 60
      Master_Log_File:
      Read_Master_Log_Pos: 4
      Relay_Log_File: slave-relay.000003
      Relay_Log_Pos: 4
      Relay_Master_Log_File:
      Slave_IO_Running: No
      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: 5
      Exec_Master_Log_Pos: 0
      Relay_Log_Space: 106
      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
      Master_SSL_Verify_Server_Cert: No
      Last_IO_Errno: 1045
      Last_IO_Error: error connecting to master ‘abc@192.xxx.x.xxx:3306’ – retry-time: 60 retries: 86400
      Last_SQL_Errno: 0
      Last_SQL_Error:
      1 row in set (0.00 sec)

      ERROR:
      No query specified

      Do’nt know why Slave_IO_Running is no.

      i have tried a lot to start replication but i am still unable to start replication.
      i have also commented
      #bind-address = 127.0.0.1
      and
      #skip-external-locking

      i need your help. Please send your suggestions and solution for this problem.

      Thanks in advance.

      regards,
      pradeep

      • Last_IO_Errno: 1045
        Last_IO_Error: error connecting to master

        does the user ‘xyz’@’second_node” the right privileges on first_node ?

        GRANT REPLICATION SLAVE ON *.* TO ‘xyz’@’second_node’ IDENTIFIED BY ‘‘;

        • i have given privileges for both maser as
          GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO ‘xyz’@’second_node’ IDENTIFIED BY ”;

      • Do’nt know why Slave_IO_Running is no.

        have tried a lot to start replication but i am still unable to start replication.

        i need your help. Please send your suggestions and solution for this problem.

        Thanks in advance.
        malathi

        • Hi Malathi,

          First you should check what’s the message of “Slave_IO_State”

          Then you must check at least two things :

          1. your grants are ok (the replica can connect to the master)
          2. the binlog specified is still available on the master

    • 1. Replication Master to multi-Master, possible ?
      Yes, It is possible… make Server A is the master of Server B, Server B is the Master of Server C and Server C is the Master of Server A. Your data will pass one way.

      A > B > C > A

      Hope it will helps… 😀

  4. Very good article! I did as you said and worked perfect for linux. As I unfortunately have to use MySQL on windows, I had to use CHANGE MASTER commands.

    For MySQL running on Windows comment out those lines:

    On Server 1:
    [mysqld]

    ############ Replication #####################
    server-id=1
    #master-host=IP Server-2 xxx.xxx.xxx.xxx
    #master-user=repl
    #master-password=repl
    #master-port=3306
    log-bin=server-bin
    binlog-do-db=DB_name
    binlog-ignore-db=mysql
    binlog-ignore-db=test
    replicate-do-db=DB_name
    auto_increment_increment = 1
    auto_increment_offset = 1
    show-slave-auth-info

    On Server 2:
    [mysqld]

    ############ Replication #####################
    server-id=2
    #master-host=IP Server-1 xxx.xxx.xxx.xxx
    #master-user=repl
    #master-password=repl
    #master-port=3312
    log-bin=server-bin
    binlog-do-db=DB_name
    binlog-ignore-db=mysql
    binlog-ignore-db=test
    replicate-do-db=DB_name
    auto_increment_increment = 1
    auto_increment_offset = 2
    show-slave-auth-info

    ## Commands:
    On Server-1, at the Command Prompt I entered the following commands:

    CHANGE MASTER TO MASTER_HOST=’IP of SERVER2’,
    MASTER_PORT=3306, # No quotes!
    MASTER_USER=’repl’,
    MASTER_PASSWORD=’repl’,
    MASTER_LOG_FILE=’’,
    MASTER_LOG_POS=; # No quotes!

    On Server-1, at the Command Prompt I entered the following commands:

    CHANGE MASTER TO MASTER_HOST=’IP of SERVER1’,
    MASTER_PORT=3312, # No quotes!
    MASTER_USER=’repl’,
    MASTER_PASSWORD=’repl’,
    MASTER_LOG_FILE=’’,
    MASTER_LOG_POS=; # No quotes!

    Because I am using NAT, on Server-1 I changed the port in order to be redirected.

    Here are some CHANGE MASTER Commands:

    CHANGE MASTER TO option [, option] …
    option:
    MASTER_HOST = ‘host_name’
    | MASTER_USER = ‘user_name’
    | MASTER_PASSWORD = ‘password’
    | MASTER_PORT = port_num
    | MASTER_CONNECT_RETRY = interval
    | MASTER_LOG_FILE = ‘master_log_name’
    | MASTER_LOG_POS = master_log_pos
    | RELAY_LOG_FILE = ‘relay_log_name’
    | RELAY_LOG_POS = relay_log_pos
    | MASTER_SSL = {0|1}
    | MASTER_SSL_CA = ‘ca_file_name’
    | MASTER_SSL_CAPATH = ‘ca_directory_name’
    | MASTER_SSL_CERT = ‘cert_file_name’
    | MASTER_SSL_KEY = ‘key_file_name’
    | MASTER_SSL_CIPHER = ‘cipher_list’

    Thanks! I hope this can help in case some of you need to do replication on windows.

  5. hi i’m facing this error..
    My master to slave replication stopped and is showing this error when I use SHOW SLAVE STATUS;
    | Waiting for master to send event | 172.16.10.37 | repl |
    3306 | 60 | mysql-bin.000256 | 39420285 | mysqld-relay-bin.000091
    | 527772829 | mysql-bin.000246 | Yes | No
    | asterisk | | |
    | | | 1032 | Cou ld not execute Delete_rows event on table asterisk.live_channels; Can’t find rec
    ord in ‘live_channels’, Error_code: 1032; handler error HA_ERR_END_OF_FILE; the
    event’s master log mysql-bin.000246, end_log_pos 652865182 |
    0 | 652864948 | 2266665991 | None | |
    0 | No | | |
    | | | NULL | No
    | 0 | | 1032 | Could not
    execute Delete_rows event on table asterisk.live_channels; Can’t find record in
    ‘live_channels’, Error_code: 1032; handler error HA_ERR_END_OF_FILE; the event’s master log mysql-bin.000246, end_log_pos 652865182 |

    kindly help me to resolve this issue.

    • Hi,

      dual-master is (I should write was) almost used for HA perspective and certainly should be used with one active and one passive master, this means that you should write only into the active master. Writing on both nodes at the same time will almost always generate errors.

      That being said, replication is very fragile and also depending on the binarylog method used, some statements can produce different result on the master and the salve(s) (even if the slave is also a master).

      The best way to fix those kind of problem is by checking the data integrity between nodes regularly using a tool like pt-table-checksum.

      In your case the easiest will be to skip the events that fails to replicate and then start the checksuming (and the synchronization using pt-table-sync).

      This is how you can skip one statement :

      set global sql_slave_skip_counter=1;
      start slave sql_thread;

      Hope this helps.

  6. Setting up mysql as dual master does actually work fine if the process is done correctly. The primary use of dual master mysql is to have redundancy on the server level with automatic fail-over. – Jeffrey Nimer

  7. Good a very clear article

    I undertand that on multi master i have to indicate on server A the server B ip, on server B the server C ip, on server C the server D ip and on server D the server A ip.

    Question :
    Server A auto_increment_offset = 1
    Server B auto_increment_offset = 2
    Server C auto_increment_offset = ?
    Server D auto_increment_offset = ?

    in order to not conflict

    TKS

  8. Lefred & other Mysql admins,

    I have a Master Slave Setup.

    Root Partiotions on both the systems got full & my mysql server struck.Afterwards I had added aditional space to both the servers

    I was importing a 10GB database where the replication went to slave, now does deleting both the databases & creating database with the same name will have any problem with replication

    • Hi Kalyani,

      If the replication is still running you can delete the database on the master and it will be deleted on the slave too via the replication. Same for recreating it.

      Regards,

Leave a Reply

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

As MySQL Community Manager, I am an employee of Oracle and the views expressed on this blog are my own and do not necessarily reflect the views of Oracle.

You can find articles I wrote on Oracle’s blog.

Continue