Import Tables Into MySQL

I did this to import dumped .sql files from a WordPress site onto an AWS RDS database. You’ll need a terminal, an installation of MySQL, or MariaDB, and some .sql dump files to load.

Step 1 — Create your User

This part is skippable if you don’t need any users other than root. However, it’s good practice to have non-root users for access.

Open a terminal and log in to MySQL with this command:

mysql -u <root-user-name> -p

-u is a flag for username, -p says you’re going to enter a password. If you’re logging into an RDS instance like me, you’ll need to use this command instead:

mysql -h <your-host-endpoint> -u <root-user-name> -p

-h is the flag for a host other than 'localhost’. Put the endpoint of your RDS instance here. Run the command and you’ll be prompted for your password. Enter it, and you’re terminal will present you with a screen like this:

Your screen will vary depending on your version and location. Add a new user with this command:

CREATE USER '<your-username-here>'@'<your-ip-or-DNS-here>' IDENTIFIED BY '<your-password-here>';

The capitalisation is optional. This creates a user with the name: <your-username-here> and associates it with an IP address or DNS name. The username only works if it comes from that IP or DNS.

If you’re working on a local database, 'localhost' is a common address. To work from anywhere use '%'. Repeat this to make multiple users.

If you’re doing this for a WordPress site, make sure your username and password match your wp-config.php file.

Step 2 — Create your Database

Next, create your databases with this command:

CREATE DATABASE `<your-database-name-here>`;

You don’t need the backticks if there are no hyphens in your database name. However, if you’re creating a standard WordPress table, you’ll need them to use the standard database name: 'wordpress-db'.

Next, grant your user account access to the database with this command:

GRANT ALL PRIVILEGES ON `<your-database-name-here>`.* TO '<your-username-here>'@'<your-ip-or-DNS-here>';

This allows your user account to read, write, update, delete, etc. the database. This builds an empty database, ready to be filled with your SQL dump files. Repeat this step to set up multiple databases.

WordPress

If you’re doing this for WordPress, make sure your database name matches your wp-config.php file. The relevant section is below:

/** Database name */
define( 'DB_NAME', '<your-database-name-here>' );

/** Database username */
define( 'DB_USER', '<your-username-here>' );

/** Database password */
define( 'DB_PASSWORD', '<your-password-here>' );

If they don’t match, WordPress won’t be able to find your database.

Step 3 — Import your SQL dump files

To fill your empty databases, log out with the following command:

exit

Expect to see this sort of output:

Load your SQL dump files into your databases with this command:

mysql -u your-username-here -p your-database-name-here < your-dumpfile-name-here

I’ve removed the usual <> tags around names to avoid confusion, as the < tag is used as a directional input command. It tells MySQL to import the data from your dump file into your database.

If you get errors from this command, read the error message. I encountered a problem at line 1, which was easily fixed by deleting the top line — a comment generated by the original data dump from CPanel.

WordPress and RDS

If your database is on RDS like mine, you’ll need to include the -h flag again. Also, ensure your table prefix matches the tables you’ve loaded from your dump file. The prefix is in your wp-config.php file; this is the relevant section:

/**
 * WordPress database table prefix.
 *
 * You can have multiple installations in one database if you give each
 * a unique prefix. Only numbers, letters, and underscores please!
 */
$table_prefix = 'wpun_';