Skip to content

Rage Against the Shell

Linux tips and other things…

  • Home
  • Contact
  • Privacy Policy

MySQL Master-Master replication

Posted on July 24, 2016 - September 25, 2016 by Mr. Reboot

Tested on Ubuntu 14 / MySQL 5.5

We will configure mysql master-master replication on two nodes for all databases:

Node A: 1.1.1.1
Node B: 2.2.2.2

First install mysql in both nodes:

$ ~ apt-get install mysql-server mysql-client

In the node A configure this in my.cnf:

server-id                       = 1
log_bin                         = /var/lib/mysql/mysql-bin.log
log_bin_index                   = /var/lib/mysql/mysql-bin.log.index
relay_log                       = /var/lib/mysql/mysql-relay-bin
relay_log_index                 = /var/lib/mysql/mysql-relay-bin.index
binlog_format                   = ROW
slave_exec_mode                 = IDEMPOTENT
expire_logs_days                = 10
max_binlog_size                 = 100M
auto-increment-increment        = 2
auto-increment-offset           = 1

And this in the node B:

server-id                       = 2
log_bin                         = /var/lib/mysql/mysql-bin.log
log_bin_index                   = /var/lib/mysql/mysql-bin.log.index
relay_log                       = /var/lib/mysql/mysql-relay-bin
relay_log_index                 = /var/lib/mysql/mysql-relay-bin.index
binlog_format                   = ROW
slave_exec_mode                 = IDEMPOTENT
expire_logs_days                = 10
max_binlog_size                 = 100M
auto-increment-increment        = 2
auto-increment-offset           = 2

Explication:

server-id: the server identification, it must be different en each node
log_bin: path to the log binary file
log_bin_index: path to the log binary file index
relay_log: path to the log relay file
relay_log_index: path to the log relay file index
binlog_format: in this case is row based replication (ROW), all changes are replicated so is the safest form of replication. The disadvantage is that generates big update logs and more network traffic too.
slave_exec_mode: IDEMPOTENT is the best method for master-master replication, it causes suppression of duplicate-key and no-key-found errors.
expire_logs_days: log expiration in days
max_binlog_size: max size of binary logs
auto-increment-increment: the amount that the server will increment each time an auto incremented value is generated.
auto-increment-offset: the offset of increment. Example: auto-increment-increment=2 and auto-increment-offset, increment sequence will be 2, 4, 6 …

Restart mysql on both nodes:

$ ~ /etc/init.d/mysql restart

Create the replication user on both nodes:

mysql> CREATE USER 'repuser'@'%' identified by 'password'; 
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repuser'@'%'; 

Now in node A get the master status, remember file and position:

 mysql> SHOW MASTER STATUS;

+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |      102 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec) 

Go to the node B and configure replication against the node A, using data before:

mysql> STOP SLAVE;
mysql> CHANGE MASTER TO MASTER_HOST='1.1.1.1', MASTER_USER='repuser', MASTER_PASSWORD='password', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=102, MASTER_PORT=3306;
mysql> START SLAVE;

In this node (B) get the master status, remember file and position:

mysql> SHOW MASTER STATUS;

+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000002 |      102 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec) 

And back to the node A configure replication against node B, using data before:

mysql> STOP SLAVE;
mysql> CHANGE MASTER TO MASTER_HOST='2.2.2.2', MASTER_USER='repuser', MASTER_PASSWORD='password', MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=102, MASTER_PORT=3306;
mysql> START SLAVE; 

Check slave status on both nodes:

mysql> SHOW SLAVE STATUS\G
Posted in MySQL

Post navigation

Postfix + OpenDMARC
MySQL load balancing with mysqlnd_ms plugin

Leave a Reply Cancel reply

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

Search

Calendar

July 2016
M T W T F S S
 123
45678910
11121314151617
18192021222324
25262728293031
« Jun   Sep »

Categories

  • Apache
  • Cisco
  • Command line
  • Distros
  • Dovecot
  • File systems
  • Gadgets
  • GlusterFS
  • MySQL
  • Nginx
  • NTP
  • Opendkim
  • Pacemaker + Corosync
  • Postfix
  • Raspberrypi
  • SSH
  • SSL
  • Varnish

RSS RSS

  • Using qrencode January 16, 2022
  • Compile varnish module vmod_vsthrottle April 22, 2020
  • SSH vpn with sshuttle April 9, 2020
  • Disable swap in systemd December 16, 2019
  • Getting the parent process pid October 12, 2018
Proudly powered by WordPress | Theme: micro, developed by DevriX.