Difference between revisions of "Setting up MySQL Master-Master replication"

From DevOps Notebook
(Created page with "=== Prepare mysql setup === After installation, depending on distribution, find my.cnf and under mysqld add (or uncomment) : <br> '''Server 1:''' <pre> [mysqld] server_id...")
 
Line 44: Line 44:
  
 
=== Configure MySQL Master-Master replication ===
 
=== Configure MySQL Master-Master replication ===
 +
'''Server 1:'''
 
<syntaxhighlight lang="mysql">  
 
<syntaxhighlight lang="mysql">  
 
mysql> SHOW MASTER STATUS;
 
mysql> SHOW MASTER STATUS;
Line 52: Line 53:
 
+------------------+----------+--------------+------------------+
 
+------------------+----------+--------------+------------------+
 
1 row in set (0.00 sec)
 
1 row in set (0.00 sec)
 +
</syntaxhighlight>
 +
 +
'''Server 2:'''
 +
<syntaxhighlight lang="mysql">
 +
STOP SLAVE;
 +
CHANGE MASTER TO master_host='x.x.x.x', master_port=3306, master_user='repl', master_password='password', master_log_file='mysql-bin.000001', master_log_pos=277;
 +
START SLAVE;
 +
</pre>
 +
</syntaxhighlight>
 +
 +
And then get master info from server 2:
 +
<syntaxhighlight lang="mysql">
 +
SHOW MASTER STATUS;
 +
</syntaxhighlight>
 +
 +
'''Server 1:'''
 +
And use in on server 1
 +
<syntaxhighlight lang="mysql">
 +
STOP SLAVE;
 +
CHANGE MASTER TO master_host='x.x.x.x', master_port=3306, master_user='replication', master_password='password', master_log_file='mysql-bin.000001', master_log_pos=231;
 +
START SLAVE;
 
</syntaxhighlight>
 
</syntaxhighlight>

Revision as of 16:44, 30 August 2020

Prepare mysql setup

After installation, depending on distribution, find my.cnf and under mysqld add (or uncomment) :

Server 1:

[mysqld]
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
expire_logs_days    = 10
max_binlog_size     = 100M
log_slave_updates   = 1
auto-increment-increment = 2 
auto-increment-offset = 1


Server 2:

[mysqld]
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
expire_logs_days    = 10
max_binlog_size     = 100M
log_slave_updates   = 1
auto-increment-increment = 2
auto-increment-offset = 2

Update bind-address with your local servers ip:

bind-address    = x.x.x.x

Create user which is going to be used on slave (adding slave IP):

 
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'x.x.x.x' IDENTIFIED BY 'password';

Configure MySQL Master-Master replication

Server 1:

 
mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |      277 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

Server 2:

 
STOP SLAVE;
CHANGE MASTER TO master_host='x.x.x.x', master_port=3306, master_user='repl', master_password='password', master_log_file='mysql-bin.000001', master_log_pos=277;
START SLAVE;
</pre>

And then get master info from server 2:

 
SHOW MASTER STATUS;

Server 1: And use in on server 1

 
STOP SLAVE;
CHANGE MASTER TO master_host='x.x.x.x', master_port=3306, master_user='replication', master_password='password', master_log_file='mysql-bin.000001', master_log_pos=231;
START SLAVE;