DirectAdmin MySQL Databases and phpMyAdmin: Create, Connect, Import and Export

Almost every website you will run on shared hosting keeps its content in a MySQL database: WordPress posts, WooCommerce orders, Laravel users, Joomla articles. The files live in public_html, but the words and settings live in the database. DirectAdmin gives you a MySQL Management page to create databases and users, and phpMyAdmin to look inside them. This guide covers both, plus the two places people get stuck: the username prefix and wp-config.php.

How many databases you get on each plan

One database usually serves one application. A single WordPress site needs one, a second WordPress site on an addon domain needs another, and a separate Laravel app needs a third. Our plans set the database limit to match the number of sites each tier is designed for.

Storage still applies. A database counts against the disk space of your plan, and a WooCommerce store with years of orders can grow to several hundred megabytes. See the full limits on our hosting plans page.

Step by step: create a database and a database user

The username prefix pattern, explained once

On shared hosting many accounts share one MySQL server, so database names must be unique across the whole machine. DirectAdmin solves this by prefixing every database name and every database username with your account username followed by an underscore. If your DirectAdmin login is asadtraders and you create a database called wp with a user called wpuser, the real names are asadtraders_wp and asadtraders_wpuser.

Every application that connects to the database needs the full prefixed names. The single most common database error we see, "Access denied for user" or "Unknown database", comes from typing only the short part. When in doubt, copy the names exactly as MySQL Management shows them in the list.

Opening phpMyAdmin from DirectAdmin

phpMyAdmin is the web tool for looking inside a database: browsing tables, running SQL, editing a row, importing and exporting. DirectAdmin links to it from the MySQL Management page and from the main menu, and the link signs you in automatically with your DirectAdmin session. You do not have to type the database username and password.

One rule: create the database in DirectAdmin first. phpMyAdmin only sees databases and users that DirectAdmin created, because that is when the MySQL user account is actually made on the server. If you open phpMyAdmin before creating any database, you will have nothing to log in to.

Typical jobs you will do in phpMyAdmin: reset a WordPress password by editing the wp_users table, change the site URL in wp_options after a domain change, delete leftover tables from a plugin you removed, or run a search and replace after switching to HTTPS. Take an export before editing anything by hand. There is no undo.

Import and export a database

You have two routes, and both work. The DirectAdmin route is simpler for whole-database backups. On MySQL Management, click Manage next to the database. From there you can download an export of the entire database as a .sql file, and you can upload a backup to import. The import does not ask for credentials, and it offers a choice between keeping the existing data or removing it first, which is what you want when restoring over a broken site.

The phpMyAdmin route gives you more control. Select the database on the left, open the Export tab and click Go for a full .sql download, or pick individual tables. The Import tab accepts .sql and gzipped .sql files up to the upload limit shown on the page. For files larger than that limit, use the DirectAdmin import instead, or send the file to support and we will restore it for you.

1. At the old host, export the database as a .sql file (phpMyAdmin Export works almost everywhere).
2. In DirectAdmin, create an empty database and user as described above, and note the prefixed names.
3. Import the .sql file into the new database through MySQL Management or phpMyAdmin.
4. Upload your site files to public_html, then edit the configuration file so it uses the new database name, user and password.
5. Load the site. If you see a database connection error, re-check the prefix and the password.

If you are moving a whole site to us, you do not have to do any of this yourself. Our website migration service moves files, databases and email for you. The guide on migrating a website to a new host explains the full sequence if you prefer to do it by hand.

Connecting WordPress: the wp-config.php settings

If you installed WordPress with the app installer in your DirectAdmin account, the database was created and wp-config.php was written for you, and nothing here needs changing. You only edit this file when you upload WordPress manually, restore a copy from another host, or change the database password.

Open File Manager under System Info & Files, go into public_html, and edit wp-config.php. Near the top you will find four lines that matter. Use your full prefixed names and localhost as the host:

• define( 'DB_NAME', 'username_wp' );
• define( 'DB_USER', 'username_wpuser' );
• define( 'DB_PASSWORD', 'the-password-you-copied' );
• define( 'DB_HOST', 'localhost' );

Save the file and reload the site. The message "Error establishing a database connection" means one of those four values is wrong; the password and the missing prefix account for nearly all cases. The $table_prefix line further down is unrelated to the DirectAdmin prefix: it is the prefix WordPress puts on its own table names, and it must match the tables inside the database you imported. For a walkthrough of a fresh install, see how to install WordPress on DirectAdmin.

Managing database users and access

MySQL Management has a MANAGE USERS view that lists every database user on the account, not just the ones attached to a particular database. Click Manage next to a user to change its password or to see its Allowed Hosts. By default a user can connect only from localhost, meaning from scripts running on the same server. That is the correct setting for a website and you should leave it alone.

Allowed Hosts exists for the rare case where a desktop tool or an external server needs to connect to your database directly. You can add a specific IP address there. Avoid adding the % wildcard, which permits connection attempts from anywhere and turns your database password into the only line of defence. The same Manage page for a database includes a Repair button under Database Operations, which is worth trying when WordPress reports a corrupted table after a crash.

Frequently asked questions