Upgrading from MySQL

Step-by-step guide to upgrading from upstream MySQL to VillageSQL.

VillageSQL is a drop-in replacement for MySQL with extensions.

All examples in this guide work on VillageSQL. Install Now →

VillageSQL Server is a drop-in replacement for MySQL. Migrating from MySQL to VillageSQL is functionally identical to a minor version upgrade (e.g., 8.4.5 to 8.4.6), preserving your existing data.

Prerequisites

• VillageSQL Binaries: Ensure you have the VillageSQL binaries ready (either compiled from source or downloaded).
• MySQL Version: VillageSQL is based on MySQL 8.4 LTS. Upgrading from MySQL 8.4 is the supported path.

Always backup your data before performing an upgrade. While the process is designed to be safe, database upgrades always carry a risk.

Step 1: Backup Your Data

Before touching any system files or services, create a full backup of your database.

Option A: Logical Backup (mysqldump)

Create a SQL dump of all databases. This is the safest and most portable method.

mysqldump -u root -p --all-databases --events --routines --triggers > full_backup.sql

Option B: Physical Backup (Data Directory)

If your dataset is large, stop the server and copy the data directory.

# Stop the server first (see Step 2)
cp -r /var/lib/mysql /var/lib/mysql-backup

Step 2: Stop the MySQL Server

Stop your currently running MySQL server instance to release locks on the data files.

Using Systemd (Linux):

sudo systemctl stop mysql
# OR
sudo systemctl stop mysqld

Using SysV Init:

sudo service mysql stop

Manual Install:

mysqladmin -u root -p shutdown

Step 3: Replace Binaries

The core of the upgrade involves switching the executable files (mysqld, mysql, etc.) from the upstream MySQL versions to the VillageSQL versions.

Method A: Swapping Installation Directories (Recommended)

If you installed MySQL via tarball (e.g., to /usr/local/mysql), the cleanest method is to install VillageSQL alongside it and switch the symlink.

1. Extract/Install VillageSQL to a new directory, e.g., /usr/local/villagesql-8.4.
2. Update the symlink:
   # Remove link to old MySQL
   sudo unlink /usr/local/mysql
   
   # Link to VillageSQL
   sudo ln -s /usr/local/villagesql-8.4 /usr/local/mysql
   
3. Verify permissions: Ensure the user running the database (usually mysql) has execute permissions on the new binaries.

Method B: Overwriting Binaries

If you compiled VillageSQL from source and want to overwrite the system installation (e.g., /usr/bin or /usr/local/bin):

# Assuming you are in the VillageSQL build directory
sudo make install

If you installed MySQL via a package manager (apt, yum), overwriting binaries manually is risky as the package manager may overwrite them back during updates. It is recommended to uninstall the mysql-server package (keeping data files!) or use update-alternatives if available.

Step 4: Configuration Check

Ensure your configuration file (usually /etc/my.cnf or /etc/mysql/my.cnf) points to your existing data directory.

[mysqld]
datadir=/var/lib/mysql  # Should point to your EXISTING data
socket=/var/run/mysqld/mysqld.sock

VillageSQL uses the standard MySQL configuration options, so your existing my.cnf should work without modification.

Step 5: Start VillageSQL

Start the server using your service manager or manually.

Using Systemd:

sudo systemctl start mysql

Manual Start:

/usr/local/mysql/bin/mysqld_safe --user=mysql &

Automatic Upgrade

Upon startup, VillageSQL will automatically detect if the data files are from an older version and perform necessary upgrades to system tables.

Check the error log (usually in /var/log/mysql/error.log or inside your datadir) to confirm the upgrade finished successfully. Look for: [System] [MY-013381] [Server] Server upgrade from '...' to '...' completed.

Step 6: Verify Installation

Connect to the server and verify you are running VillageSQL.

mysql -u root -p

Run the following query:

SELECT @@version, @@version_comment;

You can now verify your data is intact and begin using VillageSQL-exclusive features like INSTALL EXTENSION.

Troubleshooting

• “Data Dictionary Upgrade Failed”: If the server refuses to start, you may be trying to downgrade (e.g., 8.4 to 8.0) or the data files are corrupted. Check the error log.
• Permission Errors: Ensure the mysql OS user owns the datadir and the new VillageSQL installation directory.
  sudo chown -R mysql:mysql /var/lib/mysql
  

See also

• Schema Migrations in MySQL — running safe DDL changes after upgrading
• MySQL Backup Strategies — back up before upgrading