how to do mysql master-master replication

caveat - i avoid master-master configurations unless absolutely necessary (for some value of absolutely necessary.)

for the following steps, the following values are used:

database name

mydb

replication user

repluser

replication user password

mysecret

server1 ip address

10.0.0.1

server2 ip address

10.0.0.2

sync databases

  • sync server1 to server2

    • server1: + mysqldump -c mydb > dumpfile

    • copy dumpfile to server 2

    • server2: + mysql mydb < dumpfile

  • create replication user

    • in the following query, other_server_ip is the ip address of the other server. in other words, on server1, enter 10.0.0.2, and on server2, enter 10.0.0.1.

    • on each server do:

      mysql mysql <<EOM
      insert into user
        (Host,User,Password,Select_priv,Reload_priv,Super_priv,Repl_slave_priv)
      values
        ('other_server_ip','repluser',password('mysecret'),'Y','Y','Y','Y');
      flush privileges;
      EOM

update the configuration for each server

  • auto-increment-increment is set to the number of servers.

  • auto-increment-offset on each server is set to the number of that server, i.e. for server1 it is 1, and for server2, it is 2.

# server1 configuration
#
server-id = 1
replicate-same-server-id = 0
auto-increment-increment = 2
auto-increment-offset = 1
master-host = 10.0.0.2
master-user = repluser
master-password = mysecret
master-connect-retry = 60
log-bin = /var/lib/mysql/bin.log
# server2 configuration
#
server-id = 2
replicate-same-server-id = 0
auto-increment-increment = 2
auto-increment-offset = 2
master-host = 10.0.0.1
master-user = repluser
master-password = mysecret
master-connect-retry = 60
replicate-do-db = mydb
log-bin = /var/lib/mysql/bin.log

synchronize the servers

  • on each server: mysql -e "slave stop; show master status;"

  • the output looks like:

    +------------------+----------+--------------+------------------+
    | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
    +------------------+----------+--------------+------------------+
    | mysql-bin.000014 |    73348 |              |                  |
    +------------------+----------+--------------+------------------+
    • note the File and Position columns in the output. for this example, the values for server1 are mysql-bin.000014, 73348, and for server2, mysql-bin.000010, 70018.

    • enter the following commands to sync the servers:

# server1:
mysql <<EOM
change master to master_host =
'10.0.0.2',
master_user='repluser',
master_password='mysecret',
master_log_file='mysql-bin.000014',
master_log_pos=73348;
EOM
# server2:
mysql <<EOM
change master to master_host =
'10.0.0.1',
master_user='repluser',
master_password='mysecret',
master_log_file='mysql-bin.000010',
master_log_pos=70018;
EOM
  • start replication: mysql -e "start slave;"

  • verify. on both servers do: mysql -e "show slave status\G"

    • Slave_IO_Running and Slave_SQL_Running should both be yes.

    • Seconds_Behind_Master should be 0, or close to it.

      • reset replication if the servers get out of sync:

  • stop both servers

  • delete their relay logs

  • synchronize the servers (as described above)

    • checks

  • primary keys generated on server1 should always be odd

  • primary keys generated on server2 should always be even