Creating a MySQL database and user

If you've ever encountered the Error establishing a database connection error in WordPress, you know how critical it is to create and configure a database correctly. Almost all dynamic websites — from online stores to personal blogs — need a MySQL database to store content, settings, and user information. But creating a database isn't just about clicking a button; you need to create the right user, set permissions precisely, and then establish the connection from your site. In this article, you'll learn step by step how to do this without mistakes.

Prerequisites for Creating a Database

Before you start, make sure you have access to your hosting control panel. Depending on the hosting company, the control panel can be cPanel, DirectAdmin, or a custom panel. In all these panels, the database management tool is usually found under names like MySQL Databases or Database Manager.

You will also need the following information:

• The main control panel username and password
• The domain or subdomain name where your site runs
• If using WordPress, the required PHP and MySQL versions (usually MySQL 5.7 or 8.0)

Important note: If your site is on shared hosting, a prefix based on your hosting username is usually added to the database name. For example, if your username is srv_ali, your database name will be something like srv_ali_wp. This is a security restriction, and you should keep it in mind for the next steps.

Steps to Create a Database and User in the Control Panel

In this section, we assume you're using cPanel, but the steps are very similar in DirectAdmin and custom panels.

1. Create a New Database

1. Log in to the control panel and open the MySQL Databases section.
2. In the Create New Database section, enter a name for the database. It's better to choose a name related to your project, such as shop or blog.
3. Click the Create Database button. You'll see a success message confirming the operation.

Your full database name will now be something like srv_ali_shop. Write this name down; you'll need it in the next steps.

2. Create a MySQL User

Now you need to create a separate user to connect to this database. Never use the main hosting account user to connect to the database; this is a common security mistake.

1. On the same page, go to the Add New User section.
2. Choose a username, for example, shop_user.
3. Choose a strong password. Use a combination of uppercase and lowercase letters, numbers, and symbols. You can use the Generate Password button for help.
4. Click Create User.

Your full username will also be created with the hosting prefix, for example, srv_ali_shop_user. Store this username and password in a safe place.

3. Assign Permissions to the User

Creating the database and user alone isn't enough; you need to attach the user to the database and define their access level.

1. In the Add User to Database section, select the user and the created database from the dropdown menus.
2. Click Add.
3. On the next page, a list of privileges will be displayed. For most websites, select the ALL PRIVILEGES option. This includes SELECT, INSERT, UPDATE, DELETE, and more.
4. Click Make Changes.

If your site only needs to read data (e.g., a display-only site), you can select only SELECT. However, for WordPress and most content management systems, full access is required.

Connecting Your Site to the Database

Now that the database and user are ready, you need to connect your site to it. The connection method depends on the type of your site.

Connecting in WordPress

In WordPress, the wp-config.php file is located in the site's root directory. Replace the following values with your own information:

define('DB_NAME', 'srv_ali_shop');
define('DB_USER', 'srv_ali_shop_user');
define('DB_PASSWORD', 'YourStrongPassword');
define('DB_HOST', 'localhost');
define('DB_CHARSET', 'utf8mb4');
define('DB_COLLATE', '');

The DB_HOST value is usually localhost, but on some hosts it might be a different address like mysql.servernet.ir. You can find this information in your hosting's help section.

Connecting in PHP (Custom Site)

If you've written a custom PHP site, use the following code to connect with PDO:

<?php
$host = 'localhost';
$dbname = 'srv_ali_shop';
$user = 'srv_ali_shop_user';
$pass = 'YourStrongPassword';

try {
    $pdo = new PDO("mysql:host=$host;dbname=$dbname;charset=utf8mb4", $user, $pass);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    echo "Connection successful";
} catch (PDOException $e) {
    die("Connection error: " . $e->getMessage());
}
?>

Using PDO instead of mysqli is recommended because it offers better security and supports prepared statements.

Testing the Connection and Troubleshooting

After the setup, be sure to test the connection. The simplest way is to create a test file and open it in your browser.

1. Create a file named test_db.php in your site's root directory.
2. Place the PDO connection code in it.
3. Open the file in your browser. If you see the "Connection successful" message, everything is working.
4. Delete the test file immediately to keep your site secure.

Common Errors and Solutions

• Unknown database error: You've entered the database name incorrectly. Check the full name with the hosting prefix.
• Access denied error: The password is wrong, or the user hasn't been added to the database. Recheck the permissions.
• Connection refused error: The DB_HOST value is incorrect. If you're using localhost and it doesn't work, contact your hosting support.
• Too many connections error: If your site has high traffic, you may have reached the maximum number of concurrent connections. This issue is usually resolved by upgrading your hosting plan or optimizing queries.

Security Tips for the Database

After creating the database and user, take a few simple but effective security measures:

• Use a strong password and change it periodically.
• Never use default or simple passwords like 123456.
• If your site uses only one database, restrict the user to that database only and don't grant access to other databases.
• Keep the wp-config.php file out of public access. Hosts usually do this automatically, but if you're not sure, create an .htaccess file in the same directory and add this line:

<Files wp-config.php>
Order Allow,Deny
Deny from all
</Files>

Also, if you're using phpMyAdmin, make sure to log out of your account when you're done.

Summary

Creating a MySQL database and user in the control panel isn't complicated, but it requires attention to detail. By following the steps in this tutorial — creating the database, creating the user, assigning permissions, and connecting from your site — you can do it without errors. The most important thing is to write down the names and passwords accurately and set permissions based on your site's actual needs. If you run into a problem at any step, first read the error carefully, then try the solutions mentioned in the troubleshooting section. Finally, if your hosting supports managed database services — like what ServerNet offers in its hosting plans — you can also use the official guide for that service to follow the most reliable method.