Setting up MySQL Master-Master replication - Revision history

MilosZ at 18:31, 8 October 2020

2020-10-08T18:31:41Z

MilosZ at 15:46, 30 August 2020

2020-08-30T15:46:23Z

MilosZ at 15:44, 30 August 2020

2020-08-30T15:44:39Z

MilosZ: Created page with "=== Prepare mysql setup === After installation, depending on distribution, find my.cnf and under mysqld add (or uncomment) :
'''Server 1:'''

 [mysqld] server_id..."

2020-08-30T10:50:45Z

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..."

New page

=== Prepare mysql setup ===
After installation, depending on distribution, find my.cnf and under mysqld add (or uncomment) : <br>

'''Server 1:'''
<pre>
[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
</pre>

'''Server 2:'''
<pre>
[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
</pre>

Update bind-address with your local servers ip:
<pre>
bind-address = x.x.x.x
</pre>

Create user which is going to be used on slave (adding slave IP):
<syntaxhighlight lang="mysql">
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'x.x.x.x' IDENTIFIED BY 'password';
</syntaxhighlight>

=== Configure MySQL Master-Master replication ===
<syntaxhighlight lang="mysql">
mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 277 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
</syntaxhighlight>

@@ Line 38: / Line 38: @@
 </pre>
-Create user which is going to be used on slave (adding slave IP):
+Create user which is going to be used on slave (adding slave IP, in this case of both servers):
 <syntaxhighlight lang="mysql">
 GRANT REPLICATION SLAVE ON *.* TO 'repl'@'x.x.x.x' IDENTIFIED BY 'password';
@@ Line 72: / Line 72: @@
 <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;
+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=231;
 START SLAVE;
 </syntaxhighlight>

@@ Line 44: / Line 44: @@
 === Configure MySQL Master-Master replication ===
 <syntaxhighlight lang="mysql">
 mysql> SHOW MASTER STATUS;
@@ Line 52: / Line 53: @@
 +------------------+----------+--------------+------------------+
 row in set (0.00 sec)
 </syntaxhighlight>

Setting up MySQL Master-Master replication - Revision history

MilosZ at 18:31, 8 October 2020

MilosZ at 15:46, 30 August 2020

MilosZ at 15:44, 30 August 2020

MilosZ: Created page with "=== Prepare mysql setup === After installation, depending on distribution, find my.cnf and under mysqld add (or uncomment) : '''Server 1:''' [mysqld] server_id..."

MilosZ: Created page with "=== Prepare mysql setup === After installation, depending on distribution, find my.cnf and under mysqld add (or uncomment) :
'''Server 1:'''
[mysqld] server_id..."