Create MySQL databases and users in DirectAdmin

Applications like WordPress, PrestaShop and WHMCS store their data in MySQL or MariaDB. DirectAdmin's MySQL Management creates databases and users in one step, and phpMyAdmin lets you work with the data. This guide covers the whole process.

Before you start

• Your package must allow at least one more database.
• Note that DirectAdmin prefixes database and user names with your account name, for example john_shop.

Create a database

1. Log in and go to Account Manager → MySQL Management.
2. Click Create new Database.
3. Enter a Database Name; DirectAdmin shows the full name with the prefix.
4. Enter a Database Username (often the same) and click the generate icon for a strong password, or type your own.
5. Click Create. DirectAdmin shows the database, user, password and host (localhost). Copy them now.

Add another user or change privileges

1. Click the database name in MySQL Management.
2. Use Create New User or Add Existing User to give another user access.
3. Click Modify Privileges next to a user to limit it, for example to SELECT only for a reporting tool.
4. Click Change Password to set a new password; then update your application's config file.

Open phpMyAdmin

In MySQL Management, click phpMyAdmin (or open it from the top bar of the Evolution skin). On recent DirectAdmin versions, single sign-on logs you in automatically; otherwise use the database user and password.

Import a database

1. Click the database in MySQL Management and choose Upload backup (or Restore).
2. Select your .sql or .gz file and confirm.

Alternatively open phpMyAdmin, select the database, click Import, choose the file and click Import. phpMyAdmin is limited by PHP's upload size, so large dumps are better imported through DirectAdmin or over SSH with mysql -u user -p dbname < dump.sql.

Export a database

Click the database and choose Download to get a compressed dump, or use phpMyAdmin's Export → Quick → SQL.

Allow remote access

1. Click the database and find Access Hosts.
2. Add the IP address of the remote computer, for example your office IP. Avoid % (any host).
3. Connect using the server's hostname or IP as host, port 3306. The server firewall must also allow the IP.

Troubleshooting

• Error establishing a database connection: check the full prefixed names, the password and host localhost in your config file.
• Access denied for user: the user is not added to this database or lacks privileges.
• #1044 or #1227 errors on import: the dump contains CREATE DATABASE, DEFINER or SUPER statements. Remove those lines and import again.
• Unknown collation: replace utf8mb4_0900_ai_ci with utf8mb4_unicode_ci when moving from MySQL 8 to MariaDB.

Next steps

• Back up databases with DirectAdmin backups.
• Upload files with the File Manager.
• Tune MySQL memory on a VPS.