What are MySQL databases?

MySQL (now MariaDB on 0724 Hosting servers) is a relational database management system used by WordPress, Joomla, Drupal, WooCommerce, and most dynamic websites to store content, user data, and settings.

DirectAdmin provides tools to create databases, manage database users, and access phpMyAdmin for advanced database operations.

Accessing MySQL Management in DirectAdmin

  1. Log in to DirectAdmin
  2. On the dashboard, look for Advanced Features or Database Management
  3. Click MySQL Management or MySQL Databases
  4. You will see a list of existing databases (if any) and options to create new ones

Creating a new MySQL database

Step 1: Create the database

  1. In MySQL Management, click Create New Database
  2. Enter a database name (use only letters, numbers, and underscores)
  3. Click Create Database

The database will be created with a prefix matching your DirectAdmin username. For example, if your username is example and you name the database wordpress, the full database name will be example_wordpress.

Step 2: Create a database user

Databases require a separate user account with a password. To create one:

  1. After creating the database, click Create User or Manage Users
  2. Enter a username (e.g., wpuser)
  3. Enter a strong password (use the password generator for security)
  4. Confirm the password
  5. Click Create User

The user will be created with the same prefix as the database: example_wpuser.

Step 3: Assign the user to the database

  1. In the MySQL Management page, find your database in the list
  2. Click Manage Users or Assign User
  3. Select the user you just created
  4. Grant All Privileges (for WordPress and most applications)
  5. Click Add User to Database or Save

The user now has full access to the database and can read, write, modify, and delete data.

Database credentials for WordPress and applications

When installing WordPress or other applications, you will need to provide database details:

  • Database Name: The full database name including prefix (e.g., example_wordpress)
  • Database Username: The full username including prefix (e.g., example_wpuser)
  • Database Password: The password you created for the database user
  • Database Host: Usually localhost (the database is on the same server as your website)
  • Database Prefix: For WordPress, use wp_ (default) or a custom prefix for security

Keep these details secure. Anyone with database credentials can access and modify your website data.

Accessing phpMyAdmin

phpMyAdmin is a web-based tool for managing MySQL databases. Use it to import/export databases, run SQL queries, browse tables, and optimise databases.

Opening phpMyAdmin

  1. In DirectAdmin, go to MySQL Management
  2. Find your database in the list
  3. Click phpMyAdmin next to the database name
  4. phpMyAdmin will open in a new tab, automatically logged in to your database

You can also access phpMyAdmin directly through DirectAdmin's main menu under Advanced Features → phpMyAdmin.

Importing a database (restoring from backup)

If you have a database backup in .sql or .sql.gz format (e.g., from a previous host or WordPress backup):

  1. Create a new database and user in DirectAdmin (see above)
  2. Open phpMyAdmin for that database
  3. Click the Import tab at the top
  4. Click Choose File and select your .sql or .sql.gz file
  5. Leave the default settings (Format: SQL, Character set: utf8mb4_general_ci)
  6. Click Go at the bottom
  7. The database will be imported. This may take a few minutes for large databases

Maximum upload size: phpMyAdmin has an upload limit (typically 256MB). For larger databases, use the command line or contact support for assistance.

Exporting a database (creating a backup)

To create a backup of your database:

  1. Open phpMyAdmin for the database you want to back up
  2. Click the Export tab
  3. Select Quick export method and SQL format
  4. Click Go
  5. The database will download as a .sql file to your computer

For advanced options (e.g., gzip compression, custom table selection), choose Custom export method.

Tip: Export your database regularly, especially before updating WordPress or plugins.

Common database operations in phpMyAdmin

Browse database tables

  1. Open phpMyAdmin
  2. Click the database name in the left sidebar
  3. You will see a list of tables (e.g., wp_posts, wp_users for WordPress)
  4. Click a table name to view its contents

Search and replace text in the database

This is useful when migrating a WordPress site and need to update URLs:

  1. Open phpMyAdmin
  2. Click the database name
  3. Click the Search tab at the top
  4. Enter the text to find (e.g., old domain name)
  5. Select the tables to search
  6. Click Go to find occurrences

Better option for WordPress: Use the WP-CLI or a search-replace plugin to avoid breaking serialized data.

Optimise database tables

Over time, databases accumulate overhead and can slow down. To optimise:

  1. Open phpMyAdmin
  2. Click the database name
  3. Check the boxes next to all tables (click Check All at the bottom)
  4. In the With selected dropdown, choose Optimise table
  5. The tables will be optimised

This can improve WordPress performance, especially on older or high-traffic sites.

Run custom SQL queries

  1. Open phpMyAdmin
  2. Click the SQL tab
  3. Enter your SQL query (e.g., SELECT * FROM wp_posts WHERE post_type = 'page';)
  4. Click Go
  5. The query results will display below

Caution: Running SQL queries can permanently alter or delete data. Always back up your database before running UPDATE or DELETE queries.

Deleting a database

To delete a database you no longer need:

  1. Go to MySQL Management in DirectAdmin
  2. Find the database you want to delete
  3. Click Delete Database or the trash icon
  4. Confirm the deletion

Warning: Deleting a database is permanent and cannot be undone. Back up the database first if you may need it in the future.

Managing database users

Change a database user's password

  1. Go to MySQL Management in DirectAdmin
  2. Click Manage Users or find the user in the list
  3. Click Change Password next to the user
  4. Enter the new password
  5. Click Change Password

Important: After changing the password, update your website's configuration file (e.g., wp-config.php for WordPress) with the new password, or your website will not be able to connect to the database.

Remove a user from a database

  1. Go to MySQL Management
  2. Find the database
  3. Click Manage Users
  4. Click Remove next to the user you want to detach

This removes the user's access to the database but does not delete the user account.

Delete a database user

  1. Go to MySQL Management
  2. Click Manage Users or find the user list
  3. Click Delete next to the user
  4. Confirm the deletion

Deleting a user removes them from all databases. Ensure no websites or applications are using this user before deleting.

Database best practices

  • Use strong passwords: Use the DirectAdmin password generator for database user passwords
  • Limit user privileges: Only grant the privileges required by the application
  • Back up regularly: Export your database weekly or before major changes
  • Optimise periodically: Run table optimisation monthly to reduce overhead
  • Use localhost: For database host, always use localhost unless instructed otherwise
  • Clean up unused databases: Delete old databases to save disk space and keep your account organised
  • Never share credentials: Database passwords should be unique and not shared with FTP or email passwords

Troubleshooting database connection errors

"Error establishing a database connection" in WordPress

This error means WordPress cannot connect to the database. Common causes:

  • Incorrect database credentials: Check that the database name, username, password, and host in wp-config.php match the credentials in DirectAdmin
  • Database does not exist: Verify the database exists in DirectAdmin MySQL Management
  • User not assigned to database: Ensure the database user has been added to the database with full privileges
  • Database server down: Check the 0724 Hosting server status page or contact support

Cannot access phpMyAdmin

If phpMyAdmin does not load:

  • Clear your browser cache and try again
  • Ensure your hosting account is active and not suspended
  • Try accessing phpMyAdmin from the DirectAdmin main menu instead of the database list
  • Contact support if the issue persists

Database import fails or times out

If your database import fails:

  • The file may be too large for phpMyAdmin (typical limit: 256MB)
  • Try compressing the .sql file to .sql.gz before uploading
  • Split the database into smaller files and import them separately
  • Contact support to import large databases via command line

WordPress-specific database tips

Changing the WordPress site URL in the database

If your WordPress site URL is wrong (e.g., after migration):

  1. Open phpMyAdmin for your WordPress database
  2. Click the wp_options table
  3. Find the row where option_name is siteurl
  4. Click Edit and change option_value to your correct URL (e.g., https://yourdomain.co.za)
  5. Repeat for the row where option_name is home
  6. Save and refresh your website

Resetting a WordPress admin password via database

If you are locked out of WordPress:

  1. Open phpMyAdmin for your WordPress database
  2. Click the wp_users table
  3. Find your admin user (usually ID = 1)
  4. Click Edit
  5. In the user_pass field, enter a new password
  6. In the Function dropdown for user_pass, select MD5
  7. Click Go to save
  8. Log in to WordPress with the new password

Database size and disk space

Databases count toward your hosting account's disk space quota. You can view database sizes in DirectAdmin under MySQL Management or Account Statistics.

To reduce database size:

  • Delete spam comments, post revisions, and transient data in WordPress
  • Use a plugin like WP-Optimise to clean up the database
  • Remove old or unused tables left by deleted plugins

Getting help with databases

If you need assistance with database management, importing large databases, troubleshooting connection errors, or optimising database performance, contact 0724 Hosting support. We can help with database recovery, migrations, and advanced database operations.