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...") |
|||
| (2 intermediate revisions by the same user not shown) | |||
| Line 37: | Line 37: | ||
bind-address = x.x.x.x | bind-address = x.x.x.x | ||
</pre> | </pre> | ||
| − | + | Create user which is going to be used on slave (adding slave IP, in this case of both servers): | |
| − | Create user which is going to be used on slave (adding slave IP): | ||
<syntaxhighlight lang="mysql"> | <syntaxhighlight lang="mysql"> | ||
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'x.x.x.x' IDENTIFIED BY 'password'; | GRANT REPLICATION SLAVE ON *.* TO 'repl'@'x.x.x.x' IDENTIFIED BY 'password'; | ||
| Line 44: | Line 43: | ||
=== 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 52: | ||
+------------------+----------+--------------+------------------+ | +------------------+----------+--------------+------------------+ | ||
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='repl', master_password='password', master_log_file='mysql-bin.000001', master_log_pos=231; | ||
| + | START SLAVE; | ||
</syntaxhighlight> | </syntaxhighlight> | ||
Latest revision as of 19:31, 8 October 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, in this case of both servers):
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='repl', master_password='password', master_log_file='mysql-bin.000001', master_log_pos=231;
START SLAVE;
