Category: MySQL

When importing a MySQL dump into Amazon RDS, you might hit an error like:

In the dump you’ll often see a trigger, view, or routine created with a DEFINER that doesn’t match your current user, for example:

On RDS you typically don’t have SUPER (legacy) or SET_USER_ID (modern) privileges, so MySQL refuses to create the object with someone else’s DEFINER. TL;DR (the quick, safe fix) Strip the DEFINER=… clauses from the dump so MySQL defaults to the importing user.

Notes The versioned comments like /*!50017 … */ are fine to keep. Removing only the DEFINER=… text is enough. On macOS, use sed -E -i ” ‘s/…/…/g’ file.sql (empty string after -i). Alternative: keep an explicit definer (without elevated privileges) If you prefer to keep an explicit definer, rewrite to CURRENT_USER:

CURRENT_USER is allowed in object definers and avoids the privilege requirement. Why this happens (in one

When importing a MySQL 5.7 (or older) dump into MySQL 8.0, the process can fail with:

This article explains why it happens and shows safe, repeatable fixes you can apply immediately. TL;DR (One‑liner fix) Strip the deprecated token from the dump as you import it:

Replace DB_HOST, DB_USER, and DB_NAME to match your environment. Why this error appears Older dumps often contain version‑gated statements like:

NO_AUTO_CREATE_USER was removed in MySQL 8.0. Attempting to include it in sql_mode causes MySQL to abort the import with ERROR 1231. The behavior controlled by NO_AUTO_CREATE_USER no longer exists in 8.0 (user creation and grants were redesigned), so it’s safe to remove the token. Solution A — Clean the dump, then import (recommended) Locate the offending token

Remove it everywhere (preserve the rest of the sql_mode list and clean up commas):

Import the cleaned dump

Why this is preferred

Introduction In many scenarios, database servers are not exposed directly to the internet for security reasons. However, developers and applications still need to connect to them securely. One of the best solutions is to create an SSH tunnel that forwards local connections to the remote MySQL server. In this guide, we will: Create a restricted SSH user who can only tunnel and not log in interactively. Use /sbin/nologin to prevent shell access. Set up port forwarding for secure MySQL access. Test and verify the tunnel. Step 1: Create the SSH User On the SSH gateway server, create a dedicated user:

Set a strong password when prompted. Step 2: Restrict the User to SSH Tunneling Only Set the User’s Shell to /sbin/nologin To prevent interactive logins, change the user’s shell:

Restrict the User in SSH Configuration Edit the SSH config file:

Add the following at the bottom:

To add an extra “root” capable MySQL user start by logging in to the server on SSH and connect to MySQL CLI using an existing “root” level account. Next add the new user account:

Next grant permissions:

Now flush the privileges cache:

You might want to double check the results with: