WordPress Database Connection: SSL, Charset, Collation, Repair (tweakswp.com)

01-db-charset-collate.php

<?php
/**
 * DB_CHARSET and DB_COLLATE
 *
 * Place near the top of wp-config.php, before the "stop editing" line.
 * These two constants control the character encoding and sort order used
 * when WordPress connects to MySQL/MariaDB and when it creates new tables.
 *
 * Modern WordPress (4.2+) defaults to utf8mb4, which supports 4-byte
 * characters like emoji, mathematical symbols, and many CJK code points.
 * Older installs upgraded from utf8 may still have utf8 in wp-config.php
 * even though the database itself is utf8mb4. Fix the mismatch here.
 */

// Recommended for all new installs on MySQL 5.5.3+ / MariaDB 10.0+
define( 'DB_CHARSET', 'utf8mb4' );

// Empty string lets WordPress pick the best collation for the charset.
// On utf8mb4 this resolves to utf8mb4_unicode_520_ci for correct sort order.
define( 'DB_COLLATE', '' );

/**
 * When to override DB_COLLATE explicitly:
 *
 * - Replicating an older database that uses utf8mb4_unicode_ci
 *   (pre-MySQL 5.6 behavior) and you want to preserve exact sort order.
 * - Running a mixed-locale site where case-insensitive Unicode sort matters.
 * - Matching a managed host that forces a specific collation on CREATE TABLE.
 *
 * Verify your actual database collation with:
 *   wp db query "SHOW TABLE STATUS;" --skip-column-names | awk '{print $1, $15}'
 */

02-wp-allow-repair.php

<?php
/**
 * WP_ALLOW_REPAIR
 *
 * Enables the built-in repair tool at:
 *   https://example.com/wp-admin/maint/repair.php
 *
 * The page is accessible WITHOUT login, so treat this constant as a
 * temporary flag. Turn it on, run the repair, then set it back to false
 * or remove the line entirely.
 *
 * Under the hood this flag toggles the dbDelta-backed repair routine in
 * wp-admin/includes/upgrade.php, which issues REPAIR TABLE and OPTIMIZE
 * TABLE against every registered table.
 */

// Turn on temporarily to fix a crashed or corrupt table.
define( 'WP_ALLOW_REPAIR', true );

/**
 * Workflow for a production incident:
 *
 * 1. SSH in, back up the database first:
 *      wp db export pre-repair-backup.sql
 *
 * 2. Flip the constant on:
 *      wp config set WP_ALLOW_REPAIR true --raw
 *
 * 3. Visit /wp-admin/maint/repair.php and click "Repair and Optimize".
 *
 * 4. Flip it off immediately after the repair finishes:
 *      wp config set WP_ALLOW_REPAIR false --raw
 *    Or delete the line:
 *      wp config delete WP_ALLOW_REPAIR
 *
 * 5. Re-run WP-CLI health checks:
 *      wp db check
 *      wp db optimize
 */

03-mysql-client-flags-ssl.php

<?php
/**
 * MYSQL_CLIENT_FLAGS and TLS connections
 *
 * WordPress does not expose a dedicated DB_SSL constant. Instead, it
 * forwards MYSQL_CLIENT_FLAGS to mysqli_real_connect() when present.
 * The MYSQLI_CLIENT_SSL bit requests a TLS connection; MYSQLI_CLIENT_COMPRESS
 * enables wire-level compression (useful across WAN links).
 *
 * If the CA bundle, cert, or key must be specified explicitly, drop in a
 * custom db.php that calls mysqli_ssl_set() before mysqli_real_connect().
 * See 04-db-dropin-ssl.php in this gist for that pattern.
 */

// TLS only.
define( 'MYSQL_CLIENT_FLAGS', MYSQLI_CLIENT_SSL );

// TLS + compressed protocol, common for cross-region replicas.
// define( 'MYSQL_CLIENT_FLAGS', MYSQLI_CLIENT_SSL | MYSQLI_CLIENT_COMPRESS );

// TLS without peer verification. Use only for internal networks where
// you control both endpoints and cannot rotate a CA right now.
// Requires PHP 7.0+.
// define( 'MYSQL_CLIENT_FLAGS', MYSQLI_CLIENT_SSL | MYSQLI_CLIENT_SSL_DONT_VERIFY_SERVER_CERT );

/**
 * Verify the connection actually negotiated TLS:
 *
 *   wp db query "SHOW STATUS LIKE 'Ssl_cipher';"
 *
 * If Ssl_cipher is empty, the connection fell back to plaintext and you
 * need to debug the CA path or the server-side require_secure_transport
 * setting in my.cnf.
 */

04-db-dropin-ssl.php

<?php
/**
 * /wp-content/db.php
 *
 * Custom db drop-in that wires TLS with explicit CA, cert, and key paths.
 * Drop this file into wp-content/db.php (not plugins, not mu-plugins).
 * WordPress loads it in wp-settings.php right after the cache drop-in.
 *
 * Required constants in wp-config.php:
 *   define( 'DB_SSL_CA',   '/etc/mysql/ssl/ca.pem' );
 *   define( 'DB_SSL_CERT', '/etc/mysql/ssl/client-cert.pem' );  // optional
 *   define( 'DB_SSL_KEY',  '/etc/mysql/ssl/client-key.pem' );   // optional
 */

if ( ! defined( 'ABSPATH' ) ) {
	exit;
}

require_once ABSPATH . WPINC . '/class-wpdb.php';

class WPDB_TLS extends wpdb {

	protected function db_connect( $allow_bail = true ) {
		$this->is_mysql = true;

		$this->dbh = mysqli_init();

		// Client flags: TLS + optional compression.
		$client_flags = defined( 'MYSQL_CLIENT_FLAGS' ) ? MYSQL_CLIENT_FLAGS : 0;

		// Explicit TLS material if provided.
		if ( defined( 'DB_SSL_CA' ) && DB_SSL_CA ) {
			$cert = defined( 'DB_SSL_CERT' ) ? DB_SSL_CERT : null;
			$key  = defined( 'DB_SSL_KEY' )  ? DB_SSL_KEY  : null;
			mysqli_ssl_set( $this->dbh, $key, $cert, DB_SSL_CA, null, null );
			$client_flags |= MYSQLI_CLIENT_SSL;
		}

		$host = $this->dbhost;
		$port = 3306;
		if ( strpos( $host, ':' ) !== false ) {
			list( $host, $port ) = explode( ':', $host );
			$port = (int) $port;
		}

		$connected = @mysqli_real_connect(
			$this->dbh,
			$host,
			$this->dbuser,
			$this->dbpassword,
			$this->dbname,
			$port,
			null,
			$client_flags
		);

		if ( ! $connected && $allow_bail ) {
			wp_load_translations_early();
			$this->bail( 'TLS DB connect failed.', 'db_connect_fail' );
		}

		if ( $connected ) {
			$this->set_charset( $this->dbh );
			$this->ready = true;
			$this->select( $this->dbname, $this->dbh );
		}
	}
}

global $wpdb;
$wpdb = new WPDB_TLS( DB_USER, DB_PASSWORD, DB_NAME, DB_HOST );

05-wpcli-db-health.sh

#!/usr/bin/env bash
#
# WP-CLI database health playbook
# Run from the WordPress install root.
set -euo pipefail

echo "== Connection ping =="
wp db check

echo
echo "== Charset and collation per table =="
wp db query "
  SELECT TABLE_NAME, TABLE_COLLATION
  FROM information_schema.TABLES
  WHERE TABLE_SCHEMA = DATABASE()
  ORDER BY TABLE_NAME;
" --skip-column-names

echo
echo "== TLS status (Ssl_cipher should NOT be empty) =="
wp db query "SHOW STATUS LIKE 'Ssl_cipher';"

echo
echo "== MySQL wait_timeout and max_allowed_packet =="
wp db query "SHOW VARIABLES WHERE Variable_name IN ('wait_timeout','max_allowed_packet','character_set_client','collation_connection');"

echo
echo "== Table health =="
wp db query "CHECK TABLE $(wp db tables --format=csv | tr ',' ' ');"

echo
echo "== Optimize (rewrites tables, reclaims space) =="
wp db optimize

echo
echo "== Repair any crashed tables =="
wp db repair

06-convert-to-utf8mb4.sh

#!/usr/bin/env bash
#
# Convert an older utf8 database to utf8mb4 safely.
# Runs on a fresh backup, never directly on prod without a restore plan.
set -euo pipefail

DB="$(wp config get DB_NAME)"
BACKUP_FILE="pre-utf8mb4-$(date +%Y%m%d-%H%M%S).sql"

echo "== Backup first =="
wp db export "$BACKUP_FILE"
echo "Backup: $BACKUP_FILE"

echo
echo "== Alter database default =="
wp db query "ALTER DATABASE \`$DB\` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci;"

echo
echo "== Alter every table =="
TABLES=$(wp db query "
  SELECT TABLE_NAME
  FROM information_schema.TABLES
  WHERE TABLE_SCHEMA = DATABASE()
  AND TABLE_TYPE = 'BASE TABLE';
" --skip-column-names)

for T in $TABLES; do
  echo "-> $T"
  wp db query "ALTER TABLE \`$T\` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci;"
done

echo
echo "== Update wp-config.php =="
wp config set DB_CHARSET utf8mb4
wp config set DB_COLLATE ''

echo
echo "== Flush caches =="
wp cache flush
wp rewrite flush

echo
echo "Done. Smoke-test the front end and the editor before deleting $BACKUP_FILE."