Setting Up Database Replication

From Server rental store
Jump to navigation Jump to search

Setting Up Database Replication

This guide explains how to set up master-slave replication for MySQL and PostgreSQL databases, a crucial technique for improving database availability, read scalability, and disaster recovery. We will cover the fundamental concepts and provide practical, step-by-step instructions for both database systems.

Prerequisites

Before you begin, ensure you have the following:

  • Two or more Linux servers: One will act as the master, and the others as slaves. These servers should have network connectivity between them. For reliable performance, consider using a VPS provider like PowerVPS (https://powervps.net/?from=32) or Immers Cloud GPU (https://en.immers.cloud/signup/r/20241007-8310688-334/) for your database servers.
  • Root or sudo access: You'll need administrative privileges on all servers.
  • Installed Database Software: MySQL (or MariaDB) and/or PostgreSQL must be installed on all participating servers.
  • Basic Networking Knowledge: Understanding of IP addresses, ports, and firewalls.
  • Firewall Configuration: Ensure that the database ports (default 3306 for MySQL, 5432 for PostgreSQL) are open between your master and slave servers.

Understanding Replication

Database replication is the process of copying data from one database server (the master) to one or more other database servers (the slaves).

  • Master Server: Handles all write operations (INSERT, UPDATE, DELETE).
  • Slave Server: Receives changes from the master and applies them. Slaves can handle read operations, distributing the read load from the master.

There are different replication methods, but we will focus on asynchronous replication, which is the most common.

MySQL/MariaDB Master-Slave Replication

MySQL replication works by logging all changes made to the master database in a binary log (binlog). Slave servers connect to the master, read these binlog events, and apply them to their own data.

Step 1: Configure the Master Server

1. Edit MySQL Configuration File: 

   Open your MySQL configuration file (typically `/etc/mysql/my.cnf` or `/etc/my.cnf`) with a text editor.

    sudo nano /etc/mysql/my.cnf

2. Add/Modify Replication Settings: 

   Under the `[mysqld]` section, add or uncomment the following lines. Replace `your_server_id` with a unique integer for this server (e.g., `1`).

    [mysqld]
    server-id = 1
    log_bin = /var/log/mysql/mysql-bin.log
    binlog_format = ROW
    # Optional: If you want to replicate specific databases
    # binlog_do_db = your_database_name
    # Optional: If you want to exclude specific databases
    # binlog_ignore_db = mysql
    # binlog_ignore_db = information_schema
    
   *   `server-id`: Must be unique for each server in the replication setup.
   *   `log_bin`: Specifies the base name for the binary log files.
   *   `binlog_format`: `ROW` is generally recommended for consistency.

3. Restart MySQL Server: 

   Apply the changes by restarting the MySQL service.

    sudo systemctl restart mysql

4. Create a Replication User: 

   Connect to your MySQL server as root and create a dedicated user for replication. Replace `slave_user`, `your_password`, and `slave_ip_address` with your desired credentials and the IP address of your slave server.

    mysql -u root -p
    
   ```sql
   CREATE USER 'slave_user'@'slave_ip_address' IDENTIFIED BY 'your_password';
   GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'slave_ip_address';
   FLUSH PRIVILEGES;
   EXIT;
   ```

5. Get Master Status: 

   You'll need the current binary log file name and position.

    mysql -u root -p
    
   ```sql
   SHOW MASTER STATUS;
   ```

   Note down the `File` (e.g., `mysql-bin.000001`) and `Position` (e.g., `12345`).

Step 2: Configure the Slave Server

1. Edit MySQL Configuration File: 

   Open your MySQL configuration file on the slave server.

    sudo nano /etc/mysql/my.cnf

2. Add/Modify Replication Settings: 

   Under the `[mysqld]` section, add or uncomment the following lines. Use a different `server-id` (e.g., `2`) than the master.

    [mysqld]
    server-id = 2
    relay_log = /var/log/mysql/mysql-relay-bin.log
    read_only = 1 # Recommended for slaves to prevent accidental writes
    # Optional: If you want to replicate specific databases
    # replicate_do_db = your_database_name
    # Optional: If you want to exclude specific databases
    # replicate_ignore_db = mysql
    # replicate_ignore_db = information_schema
    
   *   `server-id`: Must be unique and different from the master.
   *   `relay_log`: Specifies the base name for relay log files.
   *   `read_only`: Prevents direct writes to the slave.

3. Restart MySQL Server: 

   Apply the changes.

    sudo systemctl restart mysql

4. Configure Slave Connection: 

   Connect to your MySQL server as root and configure the slave to connect to the master using the details obtained in Step 1. Replace `master_ip_address`, `slave_user`, `your_password`, and the `MASTER_LOG_FILE` and `MASTER_LOG_POS` with your actual values.

    mysql -u root -p
    
   ```sql
   CHANGE MASTER TO
       MASTER_HOST='master_ip_address',
       MASTER_USER='slave_user',
       MASTER_PASSWORD='your_password',
       MASTER_LOG_FILE='mysql-bin.000001', -- Use the File from SHOW MASTER STATUS
       MASTER_LOG_POS=12345;             -- Use the Position from SHOW MASTER STATUS

   START SLAVE;
   SHOW SLAVE STATUS\G
   ```

   Check the output of `SHOW SLAVE STATUS\G`. Look for `Slave_IO_Running: Yes` and `Slave_SQL_Running: Yes`. If not, check your logs and firewall.

PostgreSQL Master-Slave Replication (Streaming Replication)

PostgreSQL uses a WAL (Write-Ahead Logging) system for replication. Changes are written to WAL files, and these can be streamed to replica servers.

Step 1: Configure the Master Server

1. Edit PostgreSQL Configuration Files: 

   Locate your `postgresql.conf` and `pg_hba.conf` files. The location varies by PostgreSQL version and installation method (e.g., `/etc/postgresql/<version>/main/`).

    sudo nano /etc/postgresql/<version>/main/postgresql.conf
    sudo nano /etc/postgresql/<version>/main/pg_hba.conf

2. Modify `postgresql.conf`: 

   Add or modify the following parameters:

    wal_level = replica
    max_wal_senders = 5       # Number of concurrent WAL sender processes
    wal_keep_segments = 64    # Number of WAL files to keep on disk (adjust based on network speed and load)
    # Or for newer versions:
    # wal_keep_size = 1GB     # Equivalent to wal_keep_segments but in size
    archive_mode = on
    archive_command = 'cp %p /path/to/wal/archive/%f' # Example: copy WAL files to a directory
    
   *   `wal_level = replica`: Enables WAL streaming.
   *   `max_wal_senders`: Allows multiple replicas to connect.
   *   `wal_keep_segments`/`wal_keep_size`: Prevents WAL files from being removed before the replica receives them.
   *   `archive_mode` and `archive_command`: Essential for recovery and ensuring WAL segments are not lost. Ensure the archive directory exists and is writable by the PostgreSQL user.

3. Modify `pg_hba.conf`: 

   Allow replication connections from your replica server(s). Add a line like this, replacing `replica_ip_address` and `replication_user` with your details.

    # TYPE  DATABASE        USER            ADDRESS                 METHOD
    host    replication     replication_user  replica_ip_address/32   md5
    
   *   `METHOD=md5` requires a password for the `replication_user`.

4. Restart PostgreSQL Server: 

   Apply the configuration changes.

    sudo systemctl restart postgresql

5. Create a Replication User: 

   Connect to your PostgreSQL master and create a user with replication privileges.

    sudo -u postgres psql
    
   ```sql
   CREATE USER replication_user REPLICATION LOGIN PASSWORD 'your_password';
   \q
   ```

Step 2: Prepare the Slave Server

1. Stop PostgreSQL on Slave: 

   Ensure PostgreSQL is stopped on the replica server.

    sudo systemctl stop postgresql

2. Clean Slave Data Directory: 

   Remove the existing data directory to ensure a clean copy from the master. WARNING: This will delete all data on the slave server.

    sudo rm -rf /var/lib/postgresql/<version>/main/*

3. Perform Base Backup: 

   Use `pg_basebackup` to copy the master's data directory to the slave. Replace `master_ip_address`, `replication_user`, and `your_password` accordingly.

    sudo -u postgres pg_basebackup -h master_ip_address -U replication_user -D /var/lib/postgresql/<version>/main -P -v -X stream
    
   *   `-h`: Master host.
   *   `-U`: Replication user.
   *   `-D`: Destination data directory on the slave.
   *   `-P`: Show progress.
   *   `-v`: Verbose output.
   *   `-X stream`: Stream WAL files during the backup.

4. Create `standby.signal` File: 

   Create an empty file named `standby.signal` in the slave's data directory. This tells PostgreSQL to start in standby mode.

    sudo touch /var/lib/postgresql/<version>/main/standby.signal

5. Configure `postgresql.conf` on Slave: 

   Edit `postgresql.conf` on the slave.

    sudo nano /etc/postgresql/<version>/main/postgresql.conf
    
   Ensure the following (or similar):

    hot_standby = on        # Allows read queries on the standby
    primary_conninfo = 'host=master_ip_address port=5432 user=replication_user password=your_password'
    # For older versions, you might need:
    # restore_command = 'cp /path/to/wal/archive/%f %p'
    # Or for streaming:
    # primary_slot_name = 'your_slot_name' # If using replication slots
    
   *   `hot_standby = on`: Allows read queries on the slave.
   *   `primary_conninfo`: Connection string to the master.

6. Start PostgreSQL Server: 

    sudo systemctl start postgresql
    
   Check the PostgreSQL logs (`/var/log/postgresql/postgresql-<version>-main.log`) for connection status.

Troubleshooting

  • Firewall Issues: Ensure the database ports (3306 for MySQL, 5432 for PostgreSQL) are open between master and slave.
  • Incorrect Credentials: Double-check replication user, password, and IP addresses.
  • MySQL: `SHOW SLAVE STATUS\G` errors: Look for `Last_IO_Error` and `Last_SQL_Error` for clues. Common issues include incorrect `MASTER_LOG_FILE` or `MASTER_LOG_POS`.
  • PostgreSQL: Logs not showing connection: Verify `pg_hba.conf` rules, `primary_conninfo` settings, and firewall rules. Ensure the `replication_user` has the `REPLICATION` privilege.
  • Master Binary Logs/WAL Files Missing: If a slave falls too far behind, it might miss WAL segments. Ensure `binlog_expire_logs_seconds` (MySQL) or `wal_keep_segments`/`wal_keep_size` (PostgreSQL) are set appropriately, or use archiving.

Monitoring Replication

Regularly monitor the replication status to ensure it's functioning correctly.

  • MySQL: `SHOW SLAVE STATUS\G`
  • PostgreSQL: `SELECT pg_is_in_recovery();` (should return `t` on slave), and check logs.

Conclusion

Setting up database replication is a vital step for creating robust and scalable database systems. By following these steps, you can establish reliable master-slave replication for both MySQL and PostgreSQL, enhancing your server's availability and performance.

Retrieved from "https://serverrental.store/index.php?title=Setting_Up_Database_Replication&oldid=5744"