Setting Up Database Replication
= 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.
- 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.
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).
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/
sudo nano /etc/postgresql//main/postgresql.conf
sudo nano /etc/postgresql//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//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//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//main/standby.signal
5. Configure `postgresql.conf` on Slave: Edit `postgresql.conf` on the slave.
sudo nano /etc/postgresql//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-
Troubleshooting
Monitoring Replication
Regularly monitor the replication status to ensure it's functioning correctly.
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.
Category:Database Setup Category:MySQL Category:PostgreSQL Category:Server Administration