MySQL Setup

This guide will walk you through configuring your existing MySQL database for use with Sync or Swim. It covers both self-hosted MySQL databases and cloud-hosted services like Amazon RDS, Google Cloud SQL, and Azure Database for MySQL.

Overview

Sync or Swim connects to your MySQL database to synchronize data bidirectionally with other services. The MySQL adapter supports real-time Change Data Capture (CDC) through MySQL’s binary log (binlog), similar to how PostgreSQL uses Write-Ahead Logging (WAL).

You’ll need to:

1. Enable binary logging with row-based format
2. Create a dedicated user with appropriate permissions
3. Configure network access to allow Sync or Swim connections
4. Gather connection credentials

Prerequisites

• Existing MySQL database (version 5.7 or higher, MySQL 8.0+ recommended)
• Administrative access to the database server
• Network access to the database server from where Sync or Swim is running
• Connection details (hostname, port, admin credentials)

Understanding Binary Log (Binlog)

MySQL’s binary log records all changes to your database. Sync or Swim uses this log for real-time change detection, similar to how it uses PostgreSQL’s WAL.

Key binlog settings required:

Setting	Required Value	Purpose
log_bin	ON	Enables binary logging
binlog_format	ROW	Logs actual row data (not just SQL)
binlog_row_image	FULL	Logs complete before/after images

Database Setup

Self-Hosted

This section assumes you already have a MySQL server running and have administrative access.

1. Check Current Binlog Configuration

Connect to your MySQL database using an administrative user:

# Connect as root or admin user
mysql -u root -p

# Or specify host
mysql -u your_admin_user -h your_database_host -p

Check the current binlog configuration:

-- Check if binlog is enabled
SHOW VARIABLES LIKE 'log_bin';

-- Check binlog format (must be ROW)
SHOW VARIABLES LIKE 'binlog_format';

-- Check binlog row image (should be FULL)
SHOW VARIABLES LIKE 'binlog_row_image';

-- Check current binlog file
SHOW MASTER STATUS;

2. Enable Binary Logging (If Not Already Enabled)

If binlog is not enabled or not configured correctly, you’ll need to modify your MySQL configuration.

Find your MySQL config file:

/etc/mysql/mysql.conf.d/mysqld.cnf

# Common locations:
# RHEL/CentOS: /etc/my.cnf
# macOS (Homebrew): /opt/homebrew/etc/my.cnf
# Windows: C:\ProgramData\MySQL\MySQL Server 8.0\my.ini

# Show where MySQL is looking for config files
mysql --help | grep -A1 "Default options"

Edit the configuration file:

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

Add or modify these settings in the [mysqld] section:

[mysqld]
# Enable binary logging
log_bin = mysql-bin
server-id = 1

# Use row-based logging (required for CDC)
binlog_format = ROW

# Log full row images (required for before/after data)
binlog_row_image = FULL

# Recommended settings for replication
binlog_expire_logs_days = 7
max_binlog_size = 100M

# For MySQL 8.0+, use this instead of binlog_expire_logs_days
# binlog_expire_logs_seconds = 604800

Restart MySQL:

# Ubuntu/Debian
sudo systemctl restart mysql

# RHEL/CentOS
sudo systemctl restart mysqld

# macOS (Homebrew)
brew services restart mysql

Verify the configuration:

-- Connect and verify
mysql -u root -p

-- Should return 'ON'
SHOW VARIABLES LIKE 'log_bin';

-- Should return 'ROW'
SHOW VARIABLES LIKE 'binlog_format';

-- Should return 'FULL'
SHOW VARIABLES LIKE 'binlog_row_image';

3. Create a Database (Optional)

You can either create a new database or use an existing one:

-- Option A: Create a new database
CREATE DATABASE your_database_name CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- Option B: Use an existing database (skip to step 4)

4. Create a Dedicated User

Create a user with the necessary permissions for data synchronization and binlog access:

-- Create the user
CREATE USER 'sync_user'@'%' IDENTIFIED BY 'your_secure_password';

-- Grant database privileges
GRANT ALL PRIVILEGES ON your_database_name.* TO 'sync_user'@'%';

-- Grant replication privileges (required for binlog CDC)
GRANT REPLICATION SLAVE ON *.* TO 'sync_user'@'%';
GRANT REPLICATION CLIENT ON *.* TO 'sync_user'@'%';

-- Apply the changes
FLUSH PRIVILEGES;

For MySQL 8.0+ with caching_sha2_password (default):

If you encounter authentication issues, you may need to use the older authentication method:

-- Alternative: Use mysql_native_password for compatibility
ALTER USER 'sync_user'@'%' IDENTIFIED WITH mysql_native_password BY 'your_secure_password';
FLUSH PRIVILEGES;

5. Configure Network Access (If Required)

If Sync or Swim will connect from a different server, ensure MySQL accepts remote connections.

Check current bind address:

SHOW VARIABLES LIKE 'bind_address';

If it shows 127.0.0.1, you’ll need to modify the configuration:

Edit MySQL configuration:

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

Find and modify the bind-address setting:

[mysqld]
# Allow connections from any IP (adjust for security)
bind-address = 0.0.0.0

# Or bind to a specific interface
# bind-address = 192.168.1.100

Restart MySQL:

sudo systemctl restart mysql

Configure firewall (if applicable):

# Ubuntu/Debian with ufw
sudo ufw allow from 192.168.1.100 to any port 3306

# RHEL/CentOS with firewalld
sudo firewall-cmd --zone=public --add-port=3306/tcp --permanent
sudo firewall-cmd --reload

6. Verify the Setup

Test the connection from the Sync or Swim server:

# Test connection
mysql -u sync_user -p -h your_database_host your_database_name

# If successful, you should see the MySQL prompt
# mysql>

# Test replication privileges
SHOW MASTER STATUS;

# Should return binlog file and position
# If you get an error, check that REPLICATION CLIENT was granted

# Test permissions
SHOW TABLES;
\q

Connection Details

You’ll need:

• Hostname: Your database server’s IP or hostname
• Port: 3306 (default)
• Database: Your database name
• Username: sync_user
• Password: The password you set
• SSL: Configure based on your setup

Amazon RDS

For existing RDS MySQL instances, you’ll need to enable binlog replication and create a user.

1. Enable Binary Logging

RDS MySQL needs specific parameter group settings for binlog access.

Create or modify a Parameter Group:

1. Navigate to RDS > Parameter Groups in AWS Console
2. Create a new parameter group or modify your existing one
3. Set these parameters:
   • binlog_format = ROW
   • binlog_row_image = FULL

Enable Automated Backups:

Binary logging requires automated backups to be enabled:

1. Navigate to your RDS instance
2. Click Modify
3. Under Backup, set Backup retention period to at least 1 day
4. Apply the changes (may require a reboot)

Apply the Parameter Group:

1. Navigate to your RDS instance
2. Click Modify
3. Select your custom parameter group
4. Apply immediately or during next maintenance window

2. Configure Security Group

1. Navigate to EC2 > Security Groups
2. Find the security group assigned to your RDS instance
3. Add an inbound rule:
   • Type: MySQL/Aurora
   • Protocol: TCP
   • Port: 3306
   • Source: Your Sync or Swim server’s IP address

3. Create Application User

Connect to your RDS instance:

mysql -h your-rds-endpoint.region.rds.amazonaws.com \
      -u your_master_user \
      -p

Create a user with replication privileges:

-- Create user
CREATE USER 'sync_user'@'%' IDENTIFIED BY 'your_secure_password';

-- Grant database privileges
GRANT ALL PRIVILEGES ON your_database_name.* TO 'sync_user'@'%';

-- Grant replication privileges
GRANT REPLICATION SLAVE ON *.* TO 'sync_user'@'%';
GRANT REPLICATION CLIENT ON *.* TO 'sync_user'@'%';

FLUSH PRIVILEGES;

4. Verify Binlog Configuration

-- Connect as sync_user
mysql -h your-rds-endpoint.region.rds.amazonaws.com -u sync_user -p

-- Verify binlog is enabled
SHOW VARIABLES LIKE 'log_bin';

-- Verify row-based format
SHOW VARIABLES LIKE 'binlog_format';

-- Check current position
SHOW MASTER STATUS;

Connection Details

You’ll need:

• Hostname: Found in RDS console (e.g., your-instance.abc123.us-east-1.rds.amazonaws.com)
• Port: 3306 (default)
• Database: Your database name
• Username: sync_user
• Password: The password you set
• SSL: true (recommended for RDS)

Google Cloud SQL

1. Enable Binary Logging

1. Navigate to your Cloud SQL instance in the Cloud SQL Console
2. Click Edit
3. Expand Flags
4. Add or verify these flags:
   • binlog_format = ROW
   • binlog_row_image = FULL
5. Click Save

Note: Binary logging is automatically enabled when you enable point-in-time recovery.

Enable Point-in-Time Recovery:

1. Go to Backups
2. Enable Point-in-time recovery
3. This automatically enables binary logging

2. Configure Network Access

1. Go to Connections > Networking
2. Under Authorized networks, add your Sync or Swim server’s IP address

3. Create User

Connect to your Cloud SQL instance:

# Using gcloud
gcloud sql connect your-instance-name --user=root

# Or using mysql with public IP
mysql -h your-instance-ip -u root -p

Create the user:

CREATE USER 'sync_user'@'%' IDENTIFIED BY 'your_secure_password';
GRANT ALL PRIVILEGES ON your_database_name.* TO 'sync_user'@'%';
GRANT REPLICATION SLAVE ON *.* TO 'sync_user'@'%';
GRANT REPLICATION CLIENT ON *.* TO 'sync_user'@'%';
FLUSH PRIVILEGES;

Connection Details

• Hostname: Found in Cloud SQL console (public IP or Cloud SQL Proxy)
• Port: 3306
• Database: Your database name
• Username: sync_user
• Password: The password you set
• SSL: true (recommended)

Azure

1. Configure Server Parameters

1. Navigate to your Azure Database for MySQL in the Azure Portal
2. Go to Server parameters
3. Set these parameters:
   • binlog_format = ROW
   • binlog_row_image = FULL
4. Click Save

Note: Azure Database for MySQL Flexible Server has binlog enabled by default with automated backups.

2. Configure Firewall

1. Go to Networking or Connection security
2. Add a firewall rule with your Sync or Swim server’s IP address

3. Create User

Connect using the admin user:

mysql -h your-server.mysql.database.azure.com \
      -u your_admin_user@your-server \
      -p

Create the user:

CREATE USER 'sync_user'@'%' IDENTIFIED BY 'your_secure_password';
GRANT ALL PRIVILEGES ON your_database_name.* TO 'sync_user'@'%';
GRANT REPLICATION SLAVE ON *.* TO 'sync_user'@'%';
GRANT REPLICATION CLIENT ON *.* TO 'sync_user'@'%';
FLUSH PRIVILEGES;

Connection Details

• Hostname: your-server.mysql.database.azure.com
• Port: 3306
• Database: Your database name
• Username: sync_user@your-server (Azure format)
• Password: The password you set
• SSL: true (required by Azure)

Configuring Sync or Swim

Once your MySQL database is set up, configure Sync or Swim through the web UI:

1. Navigate to /settings in the Sync or Swim web interface
2. Click “Add Service”
3. Select “MySQL” as the adapter type
4. Enter your connection details:
   • Hostname: Your database host
   • Port: 3306 (default)
   • Database: Your database name
   • Username: sync_user
   • Password: Your secure password
   • SSL: Enable for cloud providers
5. Click “Test Connection” to verify
6. Click “Create Service” to save

The system will automatically validate that binlog is properly configured when you test the connection.

Security Best Practices

1. Use Strong Passwords

• Minimum 16 characters
• Mix of uppercase, lowercase, numbers, and special characters
• Use a password manager
• Rotate passwords regularly

2. Limit Network Access

• Use firewall rules to restrict access to known IP addresses
• For cloud providers, use VPC/Private endpoints when possible
• Never use 0.0.0.0/0 in production environments

3. Enable SSL/TLS

Always use SSL for connections, especially over the internet:

-- Verify SSL is available
SHOW VARIABLES LIKE '%ssl%';

-- Check if user requires SSL
SELECT user, host, ssl_type FROM mysql.user WHERE user = 'sync_user';

Require SSL for the sync user:

ALTER USER 'sync_user'@'%' REQUIRE SSL;
FLUSH PRIVILEGES;

4. Use Dedicated Users

• Create a specific user for Sync or Swim
• Grant only necessary privileges
• Use different users for different environments

5. Regular Backups

Self-hosted MySQL:

# Automated backup script
mysqldump -u sync_user -p your_database_name > backup_$(date +%Y%m%d).sql

Cloud Providers:

• Ensure automated backups are enabled
• Point-in-time recovery is recommended (also enables binlog)
• Test restore procedures regularly

6. Monitor Binlog Usage

-- Check binlog files
SHOW BINARY LOGS;

-- Check disk usage for binlogs
SELECT SUM(FILE_SIZE) / 1024 / 1024 AS 'Binlog Size (MB)'
FROM information_schema.FILES
WHERE FILE_TYPE = 'BINLOG';

Troubleshooting

Connection Refused

Symptoms: Can't connect to MySQL server

Solutions:

• Verify MySQL is running: sudo systemctl status mysql
• Check firewall rules allow port 3306
• Verify bind-address in MySQL configuration
• Check cloud provider security groups/firewall rules

Access Denied

Symptoms: Access denied for user 'sync_user'@'...'

Solutions:

• Verify username and password are correct
• Check user exists: SELECT user, host FROM mysql.user;
• Ensure connecting from correct host
• For Azure, use username@servername format

Binlog Not Enabled

Symptoms: Binary logging is not enabled during Sync or Swim setup

Solutions:

• Verify binlog is ON: SHOW VARIABLES LIKE 'log_bin';
• Check binlog format: SHOW VARIABLES LIKE 'binlog_format';
• For cloud providers, enable automated backups
• Restart MySQL after configuration changes

Replication Permission Denied

Symptoms: Access denied; you need the REPLICATION SLAVE privilege

Solutions:

• Grant replication privileges:

  GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'sync_user'@'%';
  FLUSH PRIVILEGES;

• Verify grants: SHOW GRANTS FOR 'sync_user'@'%';

SSL Connection Required

Symptoms: SSL connection error or Connections using insecure transport are prohibited

Solutions:

• Set ssl: true in Sync or Swim configuration
• Ensure MySQL is configured for SSL: SHOW VARIABLES LIKE '%ssl%';

Authentication Plugin Issues (MySQL 8.0+)

Symptoms: Authentication plugin 'caching_sha2_password' cannot be loaded

Solutions:

• Use mysql_native_password:

  ALTER USER 'sync_user'@'%' IDENTIFIED WITH mysql_native_password BY 'password';

• Or update your MySQL client library

Performance Considerations

Binlog Retention

Configure appropriate binlog retention to balance disk usage and recovery needs:

-- MySQL 8.0+
SET GLOBAL binlog_expire_logs_seconds = 604800;  -- 7 days

-- MySQL 5.7
SET GLOBAL expire_logs_days = 7;

Monitor Replication Lag

-- Check binary log position
SHOW MASTER STATUS;

-- Monitor connections
SHOW PROCESSLIST;

Index Optimization

Ensure tables being synchronized have appropriate indexes, especially on:

• Primary keys
• Foreign keys used in relationships
• Fields used for filtering

-- Check table indexes
SHOW INDEX FROM your_table_name;

Additional Resources

• MySQL Official Documentation
• MySQL Binary Log Documentation
• Amazon RDS for MySQL
• Google Cloud SQL for MySQL
• Azure Database for MySQL

Support

If you encounter issues not covered in this guide, please contact Sync or Swim support with:

• MySQL version: SELECT VERSION();
• Binlog status: SHOW VARIABLES LIKE 'log_bin';
• Error messages from the Sync or Swim logs
• Connection details (hostname, port, database name - not passwords!)
• Cloud provider and service tier (if applicable)