https://www.howtoforge.com/tutorial/how-to-setup-mysql-master-master-replication
This article consolidates information from several sources into the format I use to setup MySQL Master/Master Replication. The beauty of Linux and open source is that there are many different ways to do this. Please take a look at my references and use them to accommodate any needs you may have. If you have any questions or run into any issues feel free to drop me a line in the comments.
This article consolidates information from several sources into the format I use to setup MySQL Master/Master Replication. The beauty of Linux and open source is that there are many different ways to do this. Please take a look at my references and use them to accommodate any needs you may have. If you have any questions or run into any issues feel free to drop me a line in the comments.
Assumptions
This article assumes you have already installed MySQL on to each of your servers. If not you can easily do so through the MySQL website at https://www.mysql.org/downloads. This article has not been tested on MariaDB but should work if you prefer to use MariaDB.Change SELINUX to permissive (if installed)
Server A
[root@mysqla ~]# vi /etc/selinux/config
# This file controls the state of SELinux on the system. # SELINUX= can take one of these three values: # enforcing - SELinux security policy is enforced. # permissive - SELinux prints warnings instead of enforcing. # disabled - No SELinux policy is loaded. SELINUX=permissive # SELINUXTYPE= can take one of these two values: # targeted - Targeted processes are protected, # mls - Multi Level Security protection. SELINUXTYPE=targeted
Server B
[root@mysqlb ~]# vi /etc/selinux/config
# This file controls the state of SELinux on the system. # SELINUX= can take one of these three values: # enforcing - SELinux security policy is enforced. # permissive - SELinux prints warnings instead of enforcing. # disabled - No SELinux policy is loaded. SELINUX=permissive # SELINUXTYPE= can take one of these two values: # targeted - Targeted processes are protected, # mls - Multi Level Security protection. SELINUXTYPE=targeted
Stop and disable firewalld on each server
Server A
[root@mysqla ~]# systemctl stop firewalld
[root@mysqla ~]# systemctl disable firewalld
Run the following command to insure there are no firewall rules[root@mysqla ~]# systemctl disable firewalld
[root@mysqla ~]# iptables -L
The result should look like:
Chain INPUT (policy ACCEPT)
target prot opt source destination
Chain FORWARD (policy ACCEPT)
target prot opt source destination
Chain OUTPUT (policy ACCEPT)
target prot opt source destination
target prot opt source destination
Chain FORWARD (policy ACCEPT)
target prot opt source destination
Chain OUTPUT (policy ACCEPT)
target prot opt source destination
Server B
[root@mysqlb ~]# systemctl stop firewalld
[root@mysqlb ~]# systemctl disable firewalld
Run the following command to ensure there are no firewall rules.[root@mysqlb ~]# systemctl disable firewalld
[root@mysqlb ~]# iptables -L
The result should look like:
Chain INPUT (policy ACCEPT)
target prot opt source destination
Chain FORWARD (policy ACCEPT)
target prot opt source destination
Chain OUTPUT (policy ACCEPT)
target prot opt source destination
target prot opt source destination
Chain FORWARD (policy ACCEPT)
target prot opt source destination
Chain OUTPUT (policy ACCEPT)
target prot opt source destination
Edit /etc/my.cnf on both servers
Add the following information to the bottom of the [mysqld] section
Server A
[root@mysqla ~]# vi /etc/my.cnf
server-id=1
log-bin="mysql-bin"
binlog-do-db=name_of_database
replicate-do-db=name_of_database
relay-log="mysql-relay-log"
auto-increment-offset = 1
Server B
[root@mysqlb ~]# vi /etc/my.cnf
server-id=2
log-bin="mysql-bin"
binlog-do-db=name_of_database
replicate-do-db=name_of_database
relay-log="mysql-relay-log"
auto-increment-offset = 2
Make sure you replace name_of_database with the name of the database that you want to replicate
Restart and enable the MySQL daemon on each server
Server A
[root@mysqla ~]# systemctl restart mysqld
[root@mysqla ~]# systemctl enable mysqld
Server B
[root@mysqlb ~]# systemctl restart mysqld
[root@mysqlb ~]# systemctl enable mysqld
Create the replicator user on each server
[root@mysqla ~]# mysql -u root -p
mysql> CREATE USER 'replicator'@'%' IDENTIFIED BY 'change_me';
mysql> GRANT REPLICATION SLAVE ON foo.* TO 'replicator'@'%'
mysql> CREATE USER 'replicator'@'%' IDENTIFIED BY 'change_me';
mysql> GRANT REPLICATION SLAVE ON foo.* TO 'replicator'@'%'
[root@mysqlb ~]# mysql -u root -p
mysql> CREATE USER 'replicator'@'%' IDENTIFIED BY 'change_me';
mysql> GRANT REPLICATION SLAVE ON foo.* TO 'replicator'@'%'
mysql> CREATE USER 'replicator'@'%' IDENTIFIED BY 'change_me';
mysql> GRANT REPLICATION SLAVE ON foo.* TO 'replicator'@'%'
Get log file information for use on the other server
Server A
[root@mysqla ~]# mysql -u root -p
mysql> SHOW MASTER STATUS;
+------------------+----------+------------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+------------------+------------------+
| mysql-bin.000001 | 154 | name_of_database | |
+------------------+----------+------------------+------------------+
1 row in set (0.00 sec)
mysql> SHOW MASTER STATUS;
+------------------+----------+------------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+------------------+------------------+
| mysql-bin.000001 | 154 | name_of_database | |
+------------------+----------+------------------+------------------+
1 row in set (0.00 sec)
Note the "File" and "Position" from this command
Server B
[root@mysqlb ~]# mysql -u root -p
mysql> STOP SLAVE;
mysql> CHANGE MASTER TO MASTER_HOST = 'Server A IP Address or HOSTNAME',MASTER_USER = 'replicator', MASTER_PASSWORD = 'change_me', MASTER_LOG_FILE = 'mysql-bin.000001', MASTER_LOG_POS = 154;
mysql> START SLAVE;
Repeat the same steps on Server Bmysql> STOP SLAVE;
mysql> CHANGE MASTER TO MASTER_HOST = 'Server A IP Address or HOSTNAME',MASTER_USER = 'replicator', MASTER_PASSWORD = 'change_me', MASTER_LOG_FILE = 'mysql-bin.000001', MASTER_LOG_POS = 154;
mysql> START SLAVE;
Server B
[root@mysqlb ~]# mysql -u root -p mysql> SHOW MASTER STATUS;
+------------------+----------+------------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+------------------+------------------+
| mysql-bin.000001 | 154 | name_of_database | |
+------------------+----------+------------------+------------------+
1 row in set (0.00 sec)
+------------------+----------+------------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+------------------+------------------+
| mysql-bin.000001 | 154 | name_of_database | |
+------------------+----------+------------------+------------------+
1 row in set (0.00 sec)
Note the "File" and "Position" from this command
Server A
[root@mysqla ~]# mysql -u root -p
mysql> STOP SLAVE; CHANGE MASTER TO MASTER_HOST = 'Server B IP Address or HOSTNAME', MASTER_USER = 'replicator', MASTER_PASSWORD = 'passw0rd', MASTER_LOG_FILE = 'mysql-bin.000001', MASTER_LOG_POS = 154;
mysql> START SLAVE;
mysql> STOP SLAVE; CHANGE MASTER TO MASTER_HOST = 'Server B IP Address or HOSTNAME', MASTER_USER = 'replicator', MASTER_PASSWORD = 'passw0rd', MASTER_LOG_FILE = 'mysql-bin.000001', MASTER_LOG_POS = 154;
mysql> START SLAVE;
Reboot both servers
Server A
[root@mysqla ~]# systemctl reboot
Server B
[root@mysqlb ~]# systemctl reboot
On either server create your database
[root@mysqla ~]# mysql -u root -p
mysql> CREATE DATABASE foo;
mysql> CREATE DATABASE foo;
On the other server check to see that the database is there
[root@mysqlb ~]# mysql -u root -p
mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| foo |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| foo |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
No comments:
Post a Comment