Zero Downtime Deployment with MySQL and Laravel

As a developer working with Laravel and MySQL, you’ve likely encountered the frustration of downtime during deployments. Whether it’s a sudden surge in traffic or an unexpected schema change, these moments can be costly and damage your application’s reputation. You’re probably no stranger to the “database down” error page, the one that screams “something has gone terribly wrong”.

You’ll build a robust deployment process that minimizes downtime, leveraging GitHub Actions for automated deployments and Deployer (with its Laravel recipe) for zero-downtime releases and migrations. Along the way, you’ll learn how to create database migrations using Laravel’s built-in tools and configure your application to handle schema changes without skipping a beat.

Setting Up MySQL with Zero-Downtime Capabilities

To set up MySQL for zero-downtime capabilities, you’ll need to enable the binary log and configure your server to use a replication setup. This will allow you to maintain an exact copy of your production database on another server, which can take over in case of a failure.

First, connect to your MySQL server using the mysql command-line client:

$ mysql -u root -p

Create a new user for replication purposes and grant it the necessary privileges:

CREATE USER 'replication_user'@'%' IDENTIFIED BY 'replication_password';
GRANT REPLICATION SLAVE ON *.* TO 'replication_user'@'%';
FLUSH PRIVILEGES;

Next, enable binary logging on your server by setting the log_bin variable to a non-empty value in your MySQL configuration file (/etc/my.cnf or /etc/mysql/my.cnf, depending on your Linux distribution). Every server taking part in replication also needs a unique server_id, and enabling GTIDs lets the replica track its position automatically:

[mysqld]
server_id = 1
log_bin = /var/log/mysql/binlog
gtid_mode = ON
enforce_gtid_consistency = ON

Restart your MySQL service for the changes to take effect:

$ sudo systemctl restart mysql

Now, you’ll need to configure a replication setup. This involves creating a slave server that will replicate the data from the master server in real-time. For this tutorial, we’ll assume you have another server set up with an identical MySQL installation, configured with the same settings but a different server_id (for example, server_id = 2).

To enable replication, run the following command on your slave (replica) server, pointing it at the master (source) server:

CHANGE REPLICATION SOURCE TO
  SOURCE_HOST='source_server_ip',
  SOURCE_USER='replication_user',
  SOURCE_PASSWORD='replication_password',
  SOURCE_AUTO_POSITION=1,
  GET_SOURCE_PUBLIC_KEY=1;

Replace source_server_ip, replication_user, and replication_password with your actual master server’s IP address, replication user credentials, and password.

Finally, start the replication process on the replica by running:

START REPLICA;

This will begin replicating data from your master server to the slave server. In a production environment, you’ll want to monitor this process closely to ensure that it’s working correctly.

With binary logging and replication set up, we’re now ready to move on to creating database migrations using Laravel’s built-in tools.

Creating a Database Migration using Laravel’s Built-in Tools

Now that our MySQL setup supports zero-downtime capabilities, it’s time to create a database migration using Laravel’s built-in tools. This will allow us to make changes to the database schema without disrupting our application.

First, we’ll need to create a new migration using Artisan:

php artisan make:migration add_zero_downtime_support --table=my_table

This command creates a new migration file in the database/migrations directory with the current timestamp. We can then add or modify database schema definitions in this file.

For example, let’s say we want to add a new column called lock_version to our my_table table:

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

class AddZeroDowntimeSupport extends Migration
{
    public function up()
    {
        Schema::table('my_table', function (Blueprint $table) {
            $table->unsignedInteger('lock_version')->default(0);
        });
    }

    public function down()
    {
        Schema::table('my_table', function (Blueprint $table) {
            $table->dropColumn('lock_version');
        });
    }
}

The up method defines the changes we want to make, while the down method reverses those changes. This ensures that our migration is reversible.

Once we’ve defined our migration, we can migrate our database using Artisan:

php artisan migrate

This will apply the changes defined in our migration file to our database schema.

Implementing GitHub Actions for Automated Deployments

To automate our deployment process and ensure that our database migrations are applied seamlessly with zero-downtime capabilities, we will leverage GitHub Actions.

First, create a new file in your repository’s .github/workflows directory called deploy.yml. This is where we’ll define the workflow for automating our deployments. Here’s an example of what it might look like:

name: Deploy to Production

on:
  push:
    branches:
      - main

jobs:
  deploy:
    runs-on: ubuntu-latest
    steps:
      - name: Checkout code
        uses: actions/checkout@v7
      - name: Setup PHP
        uses: shivammathur/setup-php@v2
        with:
          php-version: '8.3'
      - name: Run composer install
        run: composer install --no-interaction --prefer-dist
      - name: Run deployer command
        uses: deployphp/action@v1
        with:
          dep: deploy stage=production
          private-key: ${{ secrets.DEPLOY_KEY }}
          known-hosts: ${{ secrets.DEPLOY_KNOWN_HOSTS }}

This workflow listens for pushes to the main branch and performs a series of tasks, including checking out our code, installing dependencies, and deploying the application with Deployer, which also runs the database migrations on the server before the new release goes live (which we’ll cover in more detail later).

Make sure to add the DEPLOY_KEY (an SSH private key that can log in to your server) and DEPLOY_KNOWN_HOSTS (the output of ssh-keyscan for your server) secrets in your repository settings. Your database credentials stay in the .env file on the server. We’ll discuss how to manage sensitive data like this securely in future sections.

Configuring Zero-Downtime Deployments with Deployer

Now that we have our GitHub Actions set up for automated deployments and a database migration in place, it’s time to configure zero-downtime deployments using Deployer and its built-in Laravel recipe.

First, install the deployer/deployer package via Composer:

composer require --dev deployer/deployer

Next, create a new file named deploy.php in your project root. This is where we’ll define our deployment configuration. Add the following code to set up zero-downtime deployments with Deployer:

<?php

namespace Deployer;

require 'recipe/laravel.php';

// Repository to deploy from
set('repository', 'git@github.com:your-org/your-app.git');
set('keep_releases', 5);

// Configure your deployment hosts
host('example.com')
    ->set('remote_user', 'deployer')
    ->set('deploy_path', '/var/www/your-app')
    ->setLabels(['stage' => 'production']);

// The Laravel recipe's deploy task already runs artisan:migrate
// before deploy:publish switches the "current" symlink to the new release.

This configuration sets up zero-downtime deployments using Deployer’s release model: each deployment is built in a new directory under releases/, dependencies are installed and migrations are run, and only then is the current symlink switched to the new release. Your database connection details live in the shared .env file on the server, which the Laravel recipe links into every release. Make sure to update the repository, deployment hosts, deploy path, and other settings to match your project’s requirements. With this setup in place, your application will now be configured for seamless zero-downtime deployments.

With Deployer configured and GitHub Actions set up, we’re ready to automate our deployments and ensure a smooth experience for our users. In the next section, we’ll explore how to handle schema changes and data integrity during deployment.

Handling Schema Changes and Data Integrity During Deployment

One of the most critical aspects of zero-downtime deployments is handling schema changes without disrupting data integrity. In Laravel, we can use database migrations to make these changes, but we need to ensure that they are properly executed during deployment.

To handle schema changes, we’ll create a new migration in our database/migrations directory. We’ll use the up method to apply the schema change and the down method to revert it if necessary.

// database/migrations/[timestamp]_add_foreign_key_to_table.php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

class AddForeignKeyToTable extends Migration
{
    public function up()
    {
        Schema::table('table_name', function (Blueprint $table) {
            $table->foreignId('foreign_key_id')->nullable()->constrained();
        });
    }

    public function down()
    {
        Schema::table('table_name', function (Blueprint $table) {
            $table->dropConstrainedForeignId('foreign_key_id');
        });
    }
}

You don’t need a separate step to run this migration during deployment: the Deploy to Production workflow we created earlier runs dep deploy, and the Laravel recipe’s deploy task includes Deployer’s artisan:migrate task, which runs php artisan migrate --force on the server inside the new release before it goes live. If you ever need to re-run migrations against the release that is currently live (for example, after fixing a migration that failed), you can add a separate, manually triggered workflow that calls just that task:

# .github/workflows/migrate.yml

name: Run migrations

on:
  workflow_dispatch:

jobs:
  deploy:
    runs-on: ubuntu-latest
    steps:
      - name: Checkout code
        uses: actions/checkout@v7

      - name: Run migrations
        uses: deployphp/action@v1
        with:
          dep: artisan:migrate stage=production
          private-key: ${{ secrets.DEPLOY_KEY }}
          known-hosts: ${{ secrets.DEPLOY_KNOWN_HOSTS }}

By following these steps, we can ensure that our schema changes are properly executed during deployment without disrupting data integrity. This is a crucial step in achieving zero-downtime deployments with MySQL and Laravel.

Testing the Deployment Process with Example Use Cases

Now that we’ve set up our GitHub Actions workflow and configured zero-downtime deployments, it’s essential to test the deployment process thoroughly. We’ll use example use cases to simulate real-world scenarios and ensure our setup is working as expected.

Example 1: Adding a new column to an existing table

Let’s say we want to add a status column to the users table. We create a migration using Laravel’s built-in tools:

// php artisan make:migration add_status_column_to_users_table

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

class AddStatusColumnToUsersTable extends Migration
{
    public function up()
    {
        Schema::table('users', function (Blueprint $table) {
            $table->string('status')->default('active');
        });
    }

    public function down()
    {
        Schema::table('users', function (Blueprint $table) {
            $table->dropColumn('status');
        });
    }
}

We then run php artisan migrate and verify that the column has been added successfully.

Example 2: Updating a schema to use the latest MySQL features

Let’s say we want to update our users table to use the InnoDB engine, which supports row-level locking. We create another migration:

// php artisan make:migration update_users_table_engine

use Illuminate\Database\Migrations\Migration;
use Illuminate\Support\Facades\DB;

class UpdateUsersTableEngine extends Migration
{
    public function up()
    {
        DB::statement('ALTER TABLE users ENGINE = InnoDB');
    }

    public function down()
    {
        // Since we're switching from MyISAM to InnoDB, there's no need for a rollback
    }
}

We run php artisan migrate again and verify that the table engine has been updated successfully.

By testing our deployment process with these example use cases, we can ensure that our zero-downtime setup is working correctly and that our database schema changes are being applied smoothly.

Troubleshooting Common Issues with MySQL Zero-Downtime Deployments

When implementing zero-downtime deployments with MySQL, you may encounter issues that can hinder your application’s availability. Here are some common problems and their solutions:

Connection Errors

Sometimes, the deployment process might fail due to connection errors between your application and the database.

# In your Laravel configuration (usually .env)
DB_CONNECTION=mysql
DB_HOST=your-mysql-host
DB_PORT=3306

# Make sure your MySQL host is accessible from the machine running the deployment script

To resolve this issue, ensure that your MySQL host is accessible from the machine running the deployment script. You can do this by checking the firewall rules and network settings.

Lock Wait Timeout Errors

Another common problem is the lock wait timeout error, which occurs when a query takes too long to execute.

-- To increase the lock wait timeout in MySQL (adjust values as needed)
SET GLOBAL innodb_lock_wait_timeout = 60;

To fix this issue, you can adjust the lock wait timeout value in your MySQL configuration. This will give your application more time to complete queries without timing out.

Deployment Script Failures

If the deployment script fails for any reason, it’s essential to investigate and debug the issue promptly.

# In your GitHub Actions workflow file (.yml)
on:
  push:
    branches: [ main ]

jobs:
  deploy:
    runs-on: ubuntu-latest
    steps:
      - name: Checkout code
        uses: actions/checkout@v7

      - name: Setup PHP
        uses: shivammathur/setup-php@v2
        with:
          php-version: '8.3'

      - name: Run composer install
        run: composer install --no-interaction --prefer-dist

      - name: Run deployment script
        id: deploy
        continue-on-error: true
        uses: deployphp/action@v1
        with:
          dep: deploy stage=production
          private-key: ${{ secrets.DEPLOY_KEY }}
          known-hosts: ${{ secrets.DEPLOY_KNOWN_HOSTS }}

      # Add a retry mechanism to handle deployment script failures
      - name: Wait before retrying
        if: steps.deploy.outcome == 'failure'
        run: echo "Deployment failed, retrying in 1 minute" && sleep 60

      - name: Unlock the failed deployment
        if: steps.deploy.outcome == 'failure'
        uses: deployphp/action@v1
        with:
          dep: deploy:unlock stage=production
          private-key: ${{ secrets.DEPLOY_KEY }}
          known-hosts: ${{ secrets.DEPLOY_KNOWN_HOSTS }}

      - name: Retry deployment script if it fails
        if: steps.deploy.outcome == 'failure'
        uses: deployphp/action@v1
        with:
          dep: deploy stage=production
          private-key: ${{ secrets.DEPLOY_KEY }}
          known-hosts: ${{ secrets.DEPLOY_KNOWN_HOSTS }}

By implementing these solutions and troubleshooting techniques, you can ensure that your MySQL zero-downtime deployments are reliable and minimize downtime for your application.

This concludes our tutorial on zero-downtime deployments for Laravel and MySQL with Deployer and GitHub Actions.

Frequently Asked Questions

How do I set up MySQL replication if my slave server is not identical to the master server?

You can still set up replication, but you’ll need to manually configure the slave server’s database schema and create a custom replication setup.

What are some common pitfalls when setting up binary logging in MySQL?

One common mistake is forgetting to restart the MySQL service after enabling binary logging. Another issue can be incorrect configuration of the log_bin variable, leading to corrupted binary logs.

Why should I use Deployer for zero-downtime migrations instead of a different deployment tool?

Deployer’s Laravel recipe integrates well with Laravel’s built-in migration tools and provides a simple way to handle database schema changes without downtime. Other deployment tools may require more manual configuration and can lead to errors.

How do I troubleshoot issues with MySQL replication?

You can use the SHOW REPLICA STATUS command (SHOW SLAVE STATUS on MySQL versions before 8.0.22) to check the replication status, look for error messages in the MySQL error log, and monitor the slave server’s performance metrics.

Can I set up zero-downtime deployments without using GitHub Actions?

Yes, you can use other continuous integration/continuous deployment (CI/CD) tools like Jenkins or CircleCI to automate your deployments. However, GitHub Actions provides a simple and free way to integrate with Deployer and automate your deployment process.

Comments

comments