Building a Robust Dual Database( Write DB - Read DB ) Setup with MariaDB on AWS - For High Availability and Data Consistency

Setting up a dual database setup with MariaDB on AWS involves several steps to ensure high availability, data consistency, and security for your applications. Below is a detailed step-by-step guide to help you through the process:

1. Launch EC2 Instances:

   • Log in to the AWS Management Console and navigate to the EC2 dashboard.

   • Launch two EC2 instances, one for each MariaDB database (db1 and db2). Choose an appropriate instance type and configuration based on your requirements.

   • Ensure that the instances are launched in the same VPC (Virtual Private Cloud) and subnet for network connectivity.

2. Install MariaDB:

   • SSH into each EC2 instance using a secure terminal client.

   • Install MariaDB on both instances using the package manager or by downloading the installation package from the MariaDB website.

   • Follow the installation instructions for your Linux distribution to complete the installation process.

3. Configure MariaDB:

   • Edit the MariaDB configuration file (typically located at /etc/my.cnf or /etc/mysql/my.cnf) to configure settings such as server ID, replication settings, and binlog format.

   • Enable binary logging (binlog) and set the server ID on the master (db1) MariaDB instance.

   • Configure the slave (db2) MariaDB instance to connect to the master and replicate data from it.

Sure, let's break down the steps to configure MariaDB for replication between the master (db1) and slave (db2) instances:

1. a. Edit MariaDB Configuration File:

   • SSH into the master (db1) MariaDB instance.

   • Locate the MariaDB configuration file. It's typically located at /etc/my.cnf, /etc/mysql/my.cnf, or /etc/mysql/mariadb.conf.d/50-server.cnf.

   • Open the configuration file in a text editor such as nano or vim.

b. Configure Server ID and Binary Logging on Master (db1):

• Add or modify the following settings in the MariaDB configuration file:

    [mysqld]
    server-id = 1
    log-bin = /var/log/mysql/mysql-bin.log
  

  • server-id: Set a unique identifier for the master server. In this example, we set it to 1. Ensure each server in the replication topology has a unique server ID.

  • log-bin: Enable binary logging. Specify the location where binary log files will be stored. In this example, logs will be stored in /var/log/mysql/mysql-bin.log.

c. Restart MariaDB Service:

• Save the changes to the configuration file and exit the text editor.

• Restart the MariaDB service to apply the changes:

    sudo systemctl restart mariadb
  

d. Configure Slave (db2) to Replicate Data from Master (db1):

• SSH into the slave (db2) MariaDB instance.

• Open the MariaDB configuration file as described above.

e. Set Server ID and Replication Settings on Slave (db2):

• Add or modify the following settings in the MariaDB configuration file:

    [mysqld]
    server-id = 2
  

  • server-id: Set a unique identifier for the slave server. In this example, we set it to 2. Ensure it's different from the master's server ID.

4. Set Up Replication:

   • On the master (db1) MariaDB instance, create a replication user with the necessary privileges for replication.

   • Configure the master MariaDB instance to allow connections from the slave (db2) instance.

   • On the slave (db2) MariaDB instance, configure replication settings to connect to the master and replicate data.

   • Start the replication process and monitor the replication status to ensure it's functioning correctly.

     a. Configure Replication Settings:

     • Add the following replication settings to the MariaDB configuration file on the slave (db2):

         replicate-do-db = your_database_name
         master-host = master_db_host
         master-user = replication_user
         master-password = replication_password
         master-connect-retry = 60
       

       • replicate-do-db: Specify the name of the database to replicate. Replace your_database_name with the name of your database.

       • master-host, master-user, master-password: Set the hostname, replication user, and password of the master (db1) MariaDB instance.

       • master-connect-retry: Specify the interval (in seconds) that the slave should wait before attempting to reconnect to the master if the connection fails.

b. Restart MariaDB Service:

• Save the changes to the configuration file and exit the text editor.

• Restart the MariaDB service on the slave (db2) instance:

    sudo systemctl restart mariadb
  

c. Verify Replication Status:

• After restarting MariaDB on both the master and slave, verify the replication status on the slave by running the following command:

    SHOW SLAVE STATUS\G
  

• Check the output for Slave_IO_Running and Slave_SQL_Running to ensure both are set to Yes, indicating that replication is running without errors.

  • By following these steps, you can configure MariaDB for replication between the master (db1) and slave (db2) instances, enabling data synchronization between them.

5. Security Configuration:

   • Configure security groups and network ACLs (Access Control Lists) to restrict access to the MariaDB instances from only trusted sources.

   • Set up SSH key-based authentication for secure remote access to the EC2 instances.

   • Implement encryption for data in transit using SSL/TLS certificates.

6. Data Synchronization:

   • Develop custom scripts or use built-in MariaDB replication features to synchronize data changes between the master and slave instances.

   • Monitor replication lag and performance metrics to ensure timely synchronization and data consistency.

7. High Availability and Failover:

   • Implement high availability solutions such as Amazon RDS Multi-AZ deployment or MariaDB Galera Cluster for automatic failover and redundancy.

   • Configure Amazon Route 53 DNS failover to route traffic to healthy database instances in case of failure.

8. Monitoring and Maintenance:

   • Set up CloudWatch alarms to monitor key performance metrics such as CPU usage, disk I/O, and replication lag.

   • Perform regular backups of the MariaDB databases using Amazon RDS automated backup or custom backup scripts.

   • Schedule maintenance tasks for database optimization, security patching, and performance tuning.

9. Testing and Validation:

   • Test the replication setup by performing write operations on the master and verifying that changes are replicated to the slave.

   • Conduct failover tests to ensure that the replication setup can handle failover scenarios without data loss or downtime.

By following these steps, you can set up a robust and reliable dual database setup with MariaDB on AWS, ensuring high availability, data consistency, and security for your applications.