Date: Mon, 18 Mar 2024 22:26:18 -0600 (MDT) Message-ID: <1810736453.35.1710822378914@il-kb1.slc.westdc.net> Subject: Exported From Confluence MIME-Version: 1.0 Content-Type: multipart/related; boundary="----=_Part_34_989339822.1710822378768" ------=_Part_34_989339822.1710822378768 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable Content-Location: file:///C:/exported.html
Configuring MySQL in a master-slave replicate model adds complexity to t= he application utilizing the database. The application has to be programmed= to send the write queries only to the master and distribute the read opera= tions between the master and the slave. In the master-master model both the= servers in the cluster can handle reads as well as writes. Changes to the = database in any one of the servers is replicated to the other.
The following are the steps to configure Master-master MySQL replication= . Please replace the IP addresses and other variables as per your environme= nt.
## Step 1: Configure the first server
Edit the `/etc/mysql/my.cnf` file, find the following line
bind-address =3D 127.0.0.1
and change it to
bind-address =3D 0.0.0.0
This makes MySQL listen on all the ports. Find the following lines and u= ncomment them (remove the =E2=80=9C#=E2=80=9D at the beginning).
server-id =3D 1
log_bin =3D /var/log/mysql/mysql-bin.log
Set the `binlog_ignore_db` option to ignore the database named `mysql` d= uring replication. All other databases will be replicated.
binlog_ignore_db =3D mysql
Restart the MySQL server with the following comment.
service mysqld restart
Create a user with replication privileges.
root@master01# mysql -u root -p
master01> CREATE USER =E2=80=98repl_user=E2=80=99@'<IP of master02&g=
t;=E2=80=99 IDENTIFIED BY =E2=80=98password';
master01> GRANT REPLICATION SLAVE ON *.* TO =E2=80=98repl_user=E2=80=99=
@'<IP of master02>';
Replace `password` with a strong one. Make note of master log file and i= ts position.
master01> SHOW MASTER STATUS;
+=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94+=E2=80=94=E2=80=94=
=E2=80=94-+=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=93+=E2=80=94=E2=80=94=
=E2=80=94=E2=80=94=E2=80=94=E2=80=94+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94+=E2=80=94=E2=80=94=
=E2=80=94-+=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=93+=E2=80=94=E2=80=94=
=E2=80=94=E2=80=94=E2=80=94=E2=80=94+
| mysql-bin.000003 | 441 | | mysql |
+=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94+=E2=80=94=E2=80=94=
=E2=80=94-+=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=93+=E2=80=94=E2=80=94=
=E2=80=94=E2=80=94=E2=80=94=E2=80=94+
1 row in set (0.00 sec)
## Step 2: Configure the second server
The same steps have to be repeated on this server too. Edit the `/etc/my= sql/my.cnf` file, find the following line
bind-address =3D 127.0.0.1
and change it to
bind-address =3D 0.0.0.0
Uncomment the `server-id` line, change its value to `2` and also set the= value of the `binlog_ignore_db` directive.
server-id =3D 2
log_bin =3D /var/log/mysql/mysql-bin.log
binlog_ignore_db =3D mysql
Restart the MySQL service.
service mysqld restart
Create a user with replication privileges.
root@master02# mysql -u root -p
master02> CREATE USER =E2=80=98repl_user=E2=80=99@'<IP of master01&g=
t;=E2=80=99 IDENTIFIED BY =E2=80=98password';
master02> GRANT REPLICATION SLAVE ON *.* TO =E2=80=98repl_user=E2=80=99=
@'<IP of master01>';
Make sure to place the IP address of the first server in the above queri= es.
Note down the values of the master log file and its position.
master02> SHOW MASTER STATUS;
+=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94+=E2=80=94=E2=80=94=
=E2=80=94-+=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=93+=E2=80=94=E2=80=94=
=E2=80=94=E2=80=94=E2=80=94=E2=80=94+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94+=E2=80=94=E2=80=94=
=E2=80=94-+=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=93+=E2=80=94=E2=80=94=
=E2=80=94=E2=80=94=E2=80=94=E2=80=94+
| mysql-bin.000002 | 441 | | mysql |
+=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94+=E2=80=94=E2=80=94=
=E2=80=94-+=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=93+=E2=80=94=E2=80=94=
=E2=80=94=E2=80=94=E2=80=94=E2=80=94+
1 row in set (0.00 sec)
## Step 3: Start replication on the first server
Set the master varibles on the first server. Use the `MASTER_LOG_FILE` a= nd `MASTER_LOG_POS` values taken from the **second server**.
root@master01# mysql -u root -p
master01> SLAVE STOP;
master01> CHANGE MASTER TO MASTER_HOST =3D =E2=80=98<IP of master02&=
gt;=E2=80=99, MASTER_USER =3D =E2=80=98repl_user=E2=80=99, MASTER_PASSWORD =
=3D =E2=80=98password=E2=80=99, MASTER_LOG_FILE =3D =E2=80=98mysql-bin.0000=
02=E2=80=B2, MASTER_LOG_POS =3D 441;
master01> SLAVE START;
Check the status of replication.
mysql> SHOW SLAVE STATUS\G;
## Step 4: Start replication on the second server
Execute the query used in the previous step with the values changed acco= rdingly. Use the `MASTER_LOG_FILE` and `MASTER_LOG_POS` values taken from t= he **first server**.
root@master02# mysql -u root -p
master02> SLAVE STOP;
master02> CHANGE MASTER TO MASTER_HOST =3D =E2=80=98<IP of master01&=
gt;=E2=80=99, MASTER_USER =3D =E2=80=98repl_user=E2=80=99, MASTER_PASSWORD =
=3D =E2=80=98password=E2=80=99, MASTER_LOG_FILE =3D =E2=80=98mysql-bin.0000=
03=E2=80=B2, MASTER_LOG_POS =3D 441;
master02> SLAVE START;
Check the status of replication.
master02> SHOW SLAVE STATUS\G;
## Step 5: Secure the MySQL ports
Since MySQL is listening on all interfaces it becomes a potential target= to attacker. In this step we will configure the firewall to allow MySQL co= nnections only between these two servers.
The following IPTables firewall rule only permits MySQL traffic from the= trusted server.
root@mysql01# iptables -I INPUT -m state =E2=80=93state NEW -p tcp =E2= =80=93dport 3306 -s <IP of master02> -j ACCEPT
On the second server replace the IP with that of the first.
root@mysql02# iptables -I INPUT -m state =E2=80=93state NEW -p tcp =E2= =80=93dport 3306 -s <IP of master02> -j ACCEPT