MySQL HA DR Setup
8 min read/etc/hosts 127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4 ::1 localhost localhost.localdomain localhost6 localhost6.localdomain6 192.168.56.10 mysql-prim.localdomain mysql-prim 192.168.56.11 mysql-dr.localdomain mysql-dr
User to install and configure mysql: avs
Install MySQL
[avs@mysql-master ~]$ sudo dnf -y install mysql-server.x86_64
[sudo] password for avs:
Last metadata expiration check: 0:14:28 ago on Mon Apr 17 21:28:16 2023.
Dependencies resolved.
======================================================================================================================================================
Package Architecture Version Repository Size
======================================================================================================================================================
Installing:
mysql-server x86_64 8.0.30-3.el9_0 appstream 17 M
Installing dependencies:
mariadb-connector-c-config noarch 3.2.6-1.el9_0 appstream 9.8 k
mecab x86_64 0.996-3.el9.3 appstream 347 k
mysql x86_64 8.0.30-3.el9_0 appstream 2.8 M
mysql-common x86_64 8.0.30-3.el9_0 appstream 70 k
mysql-errmsg x86_64 8.0.30-3.el9_0 appstream 476 k
mysql-selinux noarch 1.0.5-1.el9_0 appstream 35 k
protobuf-lite x86_64 3.14.0-13.el9 appstream 231 k
Transaction Summary
======================================================================================================================================================
Install 8 Packages
Total download size: 21 M
Installed size: 178 M
Downloading Packages:
(1/8): mariadb-connector-c-config-3.2.6-1.el9_0.noarch.rpm 27 kB/s | 9.8 kB 00:00
(2/8): mysql-selinux-1.0.5-1.el9_0.noarch.rpm 84 kB/s | 35 kB 00:00
(3/8): mecab-0.996-3.el9.3.x86_64.rpm 575 kB/s | 347 kB 00:00
(4/8): mysql-common-8.0.30-3.el9_0.x86_64.rpm 93 kB/s | 70 kB 00:00
(5/8): mysql-errmsg-8.0.30-3.el9_0.x86_64.rpm 369 kB/s | 476 kB 00:01
(6/8): mysql-server-8.0.30-3.el9_0.x86_64.rpm 8.2 MB/s | 17 MB 00:02
(7/8): mysql-8.0.30-3.el9_0.x86_64.rpm 2.3 MB/s | 2.8 MB 00:01
(8/8): protobuf-lite-3.14.0-13.el9.x86_64.rpm 281 kB/s | 231 kB 00:00
------------------------------------------------------------------------------------------------------------------------------------------------------
Total 5.7 MB/s | 21 MB 00:03
Running transaction check
Transaction check succeeded.
Running transaction test
Transaction test succeeded.
Running transaction
Preparing : 1/1
Installing : mariadb-connector-c-config-3.2.6-1.el9_0.noarch 1/8
Installing : mysql-common-8.0.30-3.el9_0.x86_64 2/8
Installing : mysql-errmsg-8.0.30-3.el9_0.x86_64 3/8
Installing : mysql-8.0.30-3.el9_0.x86_64 4/8
Installing : protobuf-lite-3.14.0-13.el9.x86_64 5/8
Installing : mecab-0.996-3.el9.3.x86_64 6/8
Running scriptlet: mysql-selinux-1.0.5-1.el9_0.noarch 7/8
Installing : mysql-selinux-1.0.5-1.el9_0.noarch 7/8
Running scriptlet: mysql-selinux-1.0.5-1.el9_0.noarch 7/8
libsemanage.semanage_direct_install_info: Overriding mysql module at lower priority 100 with module at priority 200.
Running scriptlet: mysql-server-8.0.30-3.el9_0.x86_64 8/8
Installing : mysql-server-8.0.30-3.el9_0.x86_64 8/8
Running scriptlet: mysql-server-8.0.30-3.el9_0.x86_64 8/8
Running scriptlet: mysql-selinux-1.0.5-1.el9_0.noarch 8/8
Running scriptlet: mysql-server-8.0.30-3.el9_0.x86_64 8/8
Verifying : mysql-selinux-1.0.5-1.el9_0.noarch 1/8
Verifying : mariadb-connector-c-config-3.2.6-1.el9_0.noarch 2/8
Verifying : mecab-0.996-3.el9.3.x86_64 3/8
Verifying : mysql-server-8.0.30-3.el9_0.x86_64 4/8
Verifying : mysql-errmsg-8.0.30-3.el9_0.x86_64 5/8
Verifying : mysql-common-8.0.30-3.el9_0.x86_64 6/8
Verifying : mysql-8.0.30-3.el9_0.x86_64 7/8
Verifying : protobuf-lite-3.14.0-13.el9.x86_64 8/8
Installed:
mariadb-connector-c-config-3.2.6-1.el9_0.noarch mecab-0.996-3.el9.3.x86_64 mysql-8.0.30-3.el9_0.x86_64
mysql-common-8.0.30-3.el9_0.x86_64 mysql-errmsg-8.0.30-3.el9_0.x86_64 mysql-selinux-1.0.5-1.el9_0.noarch
mysql-server-8.0.30-3.el9_0.x86_64 protobuf-lite-3.14.0-13.el9.x86_64
Complete!
Configure MySQL master node mysql.conf
Change binlog and data dir
Startup with default setup and then we will proceed to change data and bin log
directory:
1. Start mysql (both nodes) [avs@mysql-prim ~]$ sudo systemctl start mysqld
2. Get the binlog and data directory from the database (on primary and standby) mysql> show variables like '%log_bin%';
mysql> show variables like '%datadir%';
3. Stop mysql ( both primary and standby) [avs@mysql-prim binlog]$ sudo systemctl stop mysqld
4. Create new directories and copy the binlog and data to relevant directories New Data Dir: /mysql/data New binlog Dir: /mysql/binlog
[root@mysql-prim ~]# mkdir -p /mysql/{data/,binlog/}
[avs@mysql-prim mysql]$ sudo cp /var/lib/mysql/binlog.*
/mysql/binlog/
[avs@mysql-prim mysql]$ sudo cp /var/lib/mysql/* /mysql/data/
[avs@mysql-prim /]$ sudo chown -R mysql:mysql mysql/
5. Change the /etc/my.cnf.d/mysql-server.cnf in both master and standby)
Master:
[mysqld]
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysql/mysqld.log
datadir=/mysql/data
pid-file=/run/mysqld/mysqld.pid
bind-address=192.168.56.10
server-id=1
binlog-do-db=customer_10121
binlog-do-db=zonos_oss_10121
log-bin=/mysql/binlog/binlog
• datadir: This parameter specifies the path to the directory where MySQL stores the data files for databases and tables. • socket: This parameter specifies the path to the Unix socket file that MySQL uses for local client connections. • log-error: This parameter specifies the path to the log file where MySQL writes error messages and other information about its operation. • pid-file: This parameter specifies the path to the file where MySQL writes its process ID (PID) when it starts up. • bind-address: This parameter specifies the IP address of the network interface that MySQL binds to for accepting client connections. In this case, the value is set to 192.168.56.10. • server-id: This parameter is used to set the unique identifier for the MySQL server in a replication environment. In this case, the value is set to 1. • binlog-do-db: This parameter specifies which databases should be logged to the binary log for replication purposes. In this case, the value is set to zonosdb. • binlog_dir: This parameter specifies the directory where MySQL writes the binary log files. In this case, the value is set to /db_data/binlog.
Standby:
[mysqld]
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysql/mysqld.log
datadir=/mysql/data
pid-file=/run/mysqld/mysqld.pid
bind-address=192.168.56.11
server-id=2
relay-log=/mysql/relaylog/relaylog
log-bin=/mysql/binlog/binlog
read-only=1
#### REPLICATION PARAMETERS #####
log_slave_updates=1
replicate_do_db=customer_10121
replicate_do_db=zonos_oss_10121
• bind-address is set to 192.168.56.11, which is the IP address of the standby node. • server-id is set to 2, which is a unique identifier for this MySQL instance in the replication setup. • relay-log is set to /db_data/relaylog/mysql-relay-bin, which is the directory where the relay log files will be stored. This directory should be different from the directory used for the binary log files on the primary node. • read-only is set to 1, which makes the standby node read-only. This means that clients cannot write to the standby node, which is necessary for it to be a true standby. • log-bin is set to /db_data/binlog/mysql-bin, which is the directory where the standby node will store its own binary log files. This is necessary in case the standby node becomes the primary node. • log-slave-updates is set to 1, which enables the standby node to write changes it receives from the primary node to its own binary log files. • replicate-do-db is set to zonosdb, which specifies that the standby node should only replicate changes to the zonosdb database. This is necessary to avoid replicating changes to other databases, which can cause conflicts or other issues.
Start and Secure MySQL on both primary and standby (on Primary & Standby)
1. [avs@mysql-prim binlog]$ sudo systemctl start mysqld
2. Secure MySQL Installation with password (zonososs)
[avs@mysql-prim binlog]$ mysql_secure_installation
Securing the MySQL server deployment.
Connecting to MySQL using a blank password.
VALIDATE PASSWORD COMPONENT can be used to test passwords
and improve security. It checks the strength of password
and allows the users to set only those passwords which are
secure enough. Would you like to setup VALIDATE PASSWORD component?
Press y|Y for Yes, any other key for No: N
Please set the password for root here.
New password:<zonososs>
Re-enter new password: <zonososs>
By default, a MySQL installation has an anonymous user,
allowing anyone to log into MySQL without having to have
a user account created for them. This is intended only for
testing, and to make the installation go a bit smoother.
You should remove them before moving into a production
environment.
Remove anonymous users? (Press y|Y for Yes, any other key for No) : Y
Success.
Normally, root should only be allowed to connect from
'localhost'. This ensures that someone cannot guess at
the root password from the network.
Disallow root login remotely? (Press y|Y for Yes, any other key for No) : N
... skipping.
By default, MySQL comes with a database named 'test' that
anyone can access. This is also intended only for testing,
and should be removed before moving into a production
environment.
Remove test database and access to it? (Press y|Y for Yes, any other key
for No) : N
... skipping.
Reloading the privilege tables will ensure that all changes
made so far will take effect immediately.
Reload privilege tables now? (Press y|Y for Yes, any other key for No) : y
Success.
All done!
Create Databases that we need to replicate ( on primary & standby)
mysql> CREATE DATABASE zonos_oss_10121 CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
Query OK, 1 row affected (0.01 sec)
mysql> CREATE DATABASE customer_10121 CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
Query OK, 1 row affected (0.01 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| customer_10121 |
| information_schema |
| mysql |
| performance_schema |
| sys |
| zonos_oss_10121 |
+--------------------+
6 rows in set (0.00 sec)
Backup the databases from primary and restore it to standby
Backup on Primary:
[root@mysql-prim ~]# mysqldump -u root -p --opt zonos_oss_10121 > zonososs.sql
Enter password:
[root@mysql-prim ~]# mysqldump -u root -p --opt customer_10121 > customer.sql
Enter password:
[root@mysql-prim ~]# ls -ltrh
total 12K
-rw-------. 1 root root 1.3K Apr 18 14:26 anaconda-ks.cfg
-rw-r--r-- 1 root root 1.3K Apr 18 15:50 zonososs.sql
-rw-r--r-- 1 root root 1.3K Apr 18 15:51 customer.sql
Copy to Standby:
[root@mysql-prim ~]# scp *.sql root@mysql-dr.localdomain:/root/
root@mysql-dr.localdomain's password:
customer.sql
100% 1258 252.1KB/s 00:00
zonososs.sql
100% 1259 208.8KB/s 00:00
Restore on Standby:
[root@mysql-dr ~]# mysql -u root -p < customer.sql
Enter password:
[root@mysql-dr ~]# mysql -u root -p < zonososs.sql
Enter password:
Create a replication user on primary:
mysql> CREATE USER 'replication'@'192.168.56.11' IDENTIFIED WITH
mysql_native_password BY 'zonososs';
Query OK, 0 rows affected (0.01 sec)
mysql> GRANT REPLICATION SLAVE ON *.* TO 'replication'@'192.168.56.11';
Query OK, 0 rows affected (0.01 sec)
Obtain the binary log position of the primary node to start the
replication
mysql> SHOW MASTER STATUS;
+---------------+----------+--------------------------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------------------------+------------------+-------------------+
| binlog.000003 | 3399 | customer_10121,zonos_oss_10121 | | |
+---------------+----------+--------------------------------+------------------+-------------------+
1 row in set (0.00 sec)
Configure the Standby
** Make sure to create relay_log directory that we mentioned in the conf file with owner as
mysql:mysql
**Make sure to check the server Id using it should be unique on both the nodes, if not delete the
mysql> SELECT @@server_uuid;
+--------------------------------------+
| @@server_uuid |
+--------------------------------------+
| 8fa8f7e5-ddca-11ed-a2ec-08002783d321 |
+--------------------------------------+
[root@mysql-dr data]# rm -rf /mysql/data/auto.cnf
[root@mysql-dr data]# systemctl stop mysqld
[root@mysql-dr data]# systemctl start mysqld
Start the Slave
CHANGE MASTER TO
MASTER_HOST='192.168.56.10',
MASTER_USER='replication',
MASTER_PASSWORD='zonososs',
MASTER_LOG_FILE='binlog.000003',
MASTER_LOG_POS=3399;
START SLAVE;
Check the replication status
mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for source to send event
Master_Host: 192.168.56.10
Master_User: replication
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: binlog.000003
Read_Master_Log_Pos: 3399
Relay_Log_File: relaylog.000005
Relay_Log_Pos: 323
Relay_Master_Log_File: binlog.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: customer_10121,zonos_oss_10121
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 3399
Relay_Log_Space: 692
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: 8fa8f7e5-ddca-11ed-a2ec-08002783d321
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Replica has read all relay log; waiting for more
updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
Master_public_key_path:
Get_master_public_key: 0
Network_Namespace:
1 row in set, 1 warning (0.00 sec)
