MySQL

The most-deployed relational database in the world.

Database Cluster-ready #sql#relational

MySQL is a mature, fast, and battle-tested SQL database that powers WordPress, Joomla, Drupal, Magento, and countless LAMP-stack applications. Pier deploys the official Docker image with automated backups, per-database management, and one-click replicas via the InnoDB Cluster image.

Deploy with Pier

1 Open the Pier dashboard and click Add service.
2 Pick MySQL from the template list.
3 Choose the version, set a service name, and Pier provisions the container, storage, and ports automatically.
4 Attach a domain if you want HTTPS. Traefik auto-provisions the Let's Encrypt certificate.

What is MySQL?

MySQL is the most-deployed relational database in history. WordPress runs on it. So do roughly half of the top one-million websites. Originally built in 1995 in Sweden, acquired by Sun in 2008 and then Oracle in 2010, MySQL is now on its 9.x release with ongoing development.

For most LAMP-stack and PHP applications, MySQL is the path of least resistance — every host, every framework, every CMS expects it. Where PostgreSQL wins on advanced features, MySQL wins on ubiquity and operational familiarity.

How Pier deploys it

Pier uses the official mysql Docker image, mounting /var/lib/mysql as the data volume. The default is latest (currently MySQL 9.x); 8-oracle and 8.0 builds are available for strict 8.x deployments. For clusters, Pier switches to the bitnami/mysql image which supports primary-replica replication out of the box (2 to 5 nodes total).

Pier generates a strong root password on creation, exposes 3306 internally, and (optionally) publishes via Traefik. Backups run mysqldump --single-transaction on schedule and can be pushed to any S3-compatible store.

When NOT to use MySQL

For embedded apps — SQLite is simpler. For complex types and SQL standard compliance — PostgreSQL is stronger. For pure key-value or cache workloads — Redis. MySQL is the right answer when you need a familiar, well-supported relational database and have no exotic schema needs.

Key features

InnoDB transactional engine

Row-level locking, MVCC, foreign keys, crash recovery. Hot-swappable with the legacy MyISAM engine for read-only datasets.

Replication out of the box

Primary/replica via binlog, semi-sync, group replication, and multi-source. The bitnami/mysql image used in cluster mode handles topology automatically.

Window functions & CTEs (8.0+)

Full SQL-99 window functions, recursive CTEs, JSON functions, and EXPLAIN ANALYZE. The 8.x line closed most of the gap with PostgreSQL.

Ecosystem & tooling

Every host stack ships a MySQL client — phpMyAdmin, DBeaver, MySQL Workbench, JetBrains DataGrip, every backup tool, every ORM. Hiring is easier than for any other database.

JSON support

Native JSON column type with path expressions, indexing on virtual generated columns, JSON_TABLE() to project documents into relational rows.

Performance Schema

Built-in instrumentation — slow query log, EXPLAIN ANALYZE, sys schema views, performance_schema tables. Tune without external profilers.

Use cases

WordPress & PHP applications

The default backend for WordPress, WooCommerce, Magento, Drupal, Joomla, MediaWiki, phpBB. Every PHP framework defaults to MySQL.

General-purpose web app backend

Rails, Django, Laravel, Symfony, Express — every web framework has first-class MySQL support.

Multi-tenant SaaS

One database per tenant or one row-key column per tenant. Combined with read replicas for analytics offload.

BI source for dashboards

Metabase, Superset, Grafana, Looker — every BI tool has native MySQL connectors.

Legacy migration target

When porting from older Oracle/SQL Server systems, MySQL is the most compatible OSS landing pad thanks to mature ANSI/ISO SQL support.

Code examples

Create table with InnoDB + UTF-8 sql

CREATE TABLE posts (
  id        BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  slug      VARCHAR(200) NOT NULL UNIQUE,
  title     VARCHAR(255) NOT NULL,
  body      MEDIUMTEXT,
  meta      JSON,
  created   DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  KEY idx_created (created)
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_unicode_ci;

Query JSON column sql

SELECT id, title,
       JSON_EXTRACT(meta, '$.tags') AS tags,
       JSON_UNQUOTE(JSON_EXTRACT(meta, '$.author')) AS author
FROM posts
WHERE JSON_CONTAINS(meta, '"featured"', '$.flags');

Window function (running total) sql

SELECT id, amount,
       SUM(amount) OVER (
         PARTITION BY customer_id
         ORDER BY created
       ) AS running_total
FROM invoices;

Backup with mysqldump bash

# Pier runs this automatically on schedule — also fine for one-off:
docker exec -i pier-mysql \
  mysqldump -u root -p"$MYSQL_ROOT_PASSWORD" \
            --single-transaction --routines --triggers appdb \
  | gzip > appdb-$(date +%F).sql.gz

How it compares

vs PostgreSQL	Postgres has a richer type system (JSONB, arrays, ranges), stricter SQL standard compliance, and more advanced indexing. MySQL is faster for simple read-heavy WordPress-style workloads and has a wider hosting ecosystem.
vs MariaDB	MariaDB is a community fork of MySQL with binary compatibility through 5.x and divergent features afterward (Galera cluster, ColumnStore engine). Use MariaDB if you want a fully open-governance fork; MySQL if you want Oracle-blessed releases.
vs SQLite	SQLite is unbeatable for embedded, single-process apps. MySQL takes over when you need concurrent writers, network access, replication, or larger-than-RAM data.
vs Cloud-managed MySQL (RDS, Cloud SQL)	Self-hosting on Pier is 10–30× cheaper for small/medium workloads. You lose the managed UI but gain control, backups to your own S3, and full version flexibility.

Frequently asked questions

Which MySQL version does Pier deploy?

Default is `latest` (currently MySQL 9.x). 8-oracle and 8.0 builds are also available. Pin a major version in production so future image updates don't bump you across a release line.

Can I run replicas?

Yes — the MySQL template supports clusters of 2 to 5 nodes (1 primary + N read replicas) via the bitnami/mysql image with InnoDB primary-replica replication.

How do automated backups work?

Pier runs `mysqldump --single-transaction` on schedule, gzips, and (optionally) uploads to S3. Per-database and cluster-wide backups are configurable.

Default port and connection string?

3306/tcp. Pier exposes `mysql://root:password@host:3306/db` on the service detail page; root password is auto-generated.

Can I use phpMyAdmin or DBeaver to connect?

Yes, any MySQL client works. Either expose the port via Traefik (with caution — DBs should not face the public internet) or tunnel via SSH for admin access.

Can I migrate from MariaDB to MySQL or vice versa?

Through MySQL 5.x they're nearly drop-in. From MySQL 8+ to MariaDB 10+ requires more attention — dump as SQL, restore on the other side, audit any storage-engine-specific features.

How do I tune InnoDB buffer pool?

Set `innodb_buffer_pool_size` to 50–70% of container memory via custom my.cnf or environment overrides. Pier doesn't expose this directly today — use a config-file mount.

Related services

Deploy on your VPS

Deploy this service →