Web HostingPublished on December 10, 20257 min read

cPanel MySQL database: create a database, a user and privileges

A database stores the content of a WordPress site, a PrestaShop store or a PHP application. This guide shows how to create a database and its user in the client area, then how to manage MySQL databases on cPanel, with phpMyAdmin, privileges and remote access. The server runs MariaDB 10.11, which is compatible with MySQL. For the MySQL plan sold separately, follow How to create a MySQL user and database instead.


Create a database in the client area

  1. Log in to dash.ouiheberg.com.

  2. In the left-hand menu, click My Services, then your hosting plan.

  3. Open the Databases tab. In the Users card, click Add to create the user that will connect to the database.

  4. Click Create a database. Enter the Database name (letters, numbers and underscores), choose the User with all privileges, then click Create.

To connect a site to its database, you need four details. The server localhost, the database name, the user and that user's password. The Remote access card also gives the server to use from outside and port 3306.

Databases tab with the Users and Remote access cards

The other tabs of the page are covered in Access cPanel and manage your OuiHeberg web hosting. For everything else, cPanel takes over.


Create a database in cPanel

Click Advanced panel at the top right of the page. cPanel opens straight away, with no login details to type. The Databases section groups four tools.

  • phpMyAdmin shows and edits the content of the databases.

  • Manage My Databases creates and manages databases and users.

  • Database Wizard creates a database, a user and its privileges in three steps.

  • Remote Database Access allows connections from outside.

Databases section of cPanel with its four tools, from phpMyAdmin to Remote Database Access

cPanel puts your username in front of the name of every database and every user. A database named wordpress, for example, becomes exemple_wordpress. Your site must use this full name.

With the Database Wizard

  1. Click Database Wizard. In step 1, type the database name in New Database, then click Next Step.

  2. In step 2, create the user. Choose a name, then a password, or let the Password Generator suggest one. Write it down.

  3. In step 3, tick ALL PRIVILEGES for a WordPress site or a CMS, then confirm.

Database Wizard at step 1 with the New Database field

With Manage My Databases

  1. Under Create New Database, type the name in New Database, then click Create Database.

  2. Further down, under Add New User, fill in Username, Password and Password (Again), then click Create User.

  3. Under Add User To Database, choose the User and the Database, then click Add.

  4. On the privileges page, tick ALL PRIVILEGES and click Make Changes.

The database appears under Current Databases, with the user in the Privileged Users column.

Manage My Databases page with database creation and the list of current databases

Details to write down

Database name      exemple_wordpress
User               exemple_wpuser
Password           the one you chose
Server             localhost
Port               3306

Choose the privileges

A privilege allows an action on the database. For a CMS such as WordPress or PrestaShop, give its user ALL PRIVILEGES. For an application that only reads data, give only what it needs.

PrivilegeWhat it allows
SELECTread data
INSERT, UPDATE, DELETEadd, change and delete rows
CREATE, ALTER, INDEXcreate tables, change their structure, manage indexes
DROPdelete whole tables
REFERENCEScreate foreign keys
CREATE TEMPORARY TABLES, LOCK TABLEStemporary tables and locks, useful for backups and imports
CREATE VIEW, SHOW VIEWcreate and read views
CREATE ROUTINE, ALTER ROUTINE, EXECUTEcreate, change and run stored procedures
EVENT, TRIGGERmanage scheduled events and triggers
UsePrivileges
WordPress, PrestaShop, other CMSALL PRIVILEGES
read-only applicationSELECT
application that reads and writes dataSELECT, INSERT, UPDATE, DELETE
export toolSELECT, LOCK TABLES

Manage User Privileges page with ALL PRIVILEGES ticked

To change the privileges later, open Manage My Databases and click the user's name in the Privileged Users column.


Manage existing databases and users

Everything happens in Manage My Databases.

  • Under Current Users, Change Password replaces a user's password. You can also do this in the client area.

  • Under Current Databases, Rename changes a database's name, and Delete erases it.

  • Under Current Users, Delete removes a user. The bin icon next to a user, in the database list, only removes that user's access to that database.

  • Under Modify Databases, Check Database checks the tables, and Repair Database tries to fix damaged ones.

After a password change or a rename, update the site's configuration (wp-config.php, .env…). Otherwise it will no longer reach its database.

Deleting a database is permanent. Export it first with phpMyAdmin.


Use phpMyAdmin

In the Databases section of cPanel, click phpMyAdmin. It opens without a password. Its language does not follow cPanel's. If needed, change it from its home page. The left-hand column lists your databases, for example exemple_wordpress. The tabs at the top hold the tools.

Browse and query a database

Click the database, then a table. The Browse tab shows its rows. The SQL tab runs a query, with the Go button.

-- Show the WordPress users
SELECT * FROM wp_users;

-- Count the published posts
SELECT COUNT(*) FROM wp_posts WHERE post_type = 'post' AND post_status = 'publish';

Import a database

  1. Click the target database in the left-hand column.

  2. Open the Import tab and choose your .sql file. A file compressed as .gz or .zip is accepted too.

  3. Click Import at the bottom of the page.

phpMyAdmin Import tab with the file picker and the maximum size

The page states the maximum size allowed, for example “Max: 50MiB”. It depends on the PHP settings upload_max_filesize and post_max_size, which you can raise in the PHP version tab of the client area, within the allowed range.

Export a database

  1. Click the database in the left-hand column.

  2. Open the Export tab, keep the SQL format, then click Export.

The downloaded file is a full copy of the database. OuiHeberg also backs up your databases, as described in the Backups tab of the client area.


Allow remote access

By default, your databases can only be reached from the hosting. This is the safest setting. To connect from your computer (with a tool such as DBeaver or MySQL Workbench), allow your IP address.

  1. In the client area, Databases tab, Remote access card, click Allow an IP address and add your address.

  2. In your tool, use the Server (remote) address, port 3306, the user and that user's password.

In cPanel, the Remote Database Access tool does the same. Type the address in Host (% wildcard is allowed), then click Add Host. The % character works as a wildcard.

ValueAddresses allowed
203.0.113.25a single address
203.0.113.%every address from 203.0.113.0 to 203.0.113.255
%.example.commachines whose name ends in example.com

Remote Database Access tool with the field to add an allowed host

Never allow % on its own, which opens your databases to the whole world. Remove an address as soon as you no longer need it.


Configure your application

Use the full database and user names, with the prefix.

WordPress (wp-config.php)

/** Database name */
define( 'DB_NAME', 'exemple_wordpress' );

/** Database user */
define( 'DB_USER', 'exemple_wpuser' );

/** User password */
define( 'DB_PASSWORD', 'your_password' );

/** Database server */
define( 'DB_HOST', 'localhost' );

/** Character set */
define( 'DB_CHARSET', 'utf8mb4' );

/** Collation (leave empty) */
define( 'DB_COLLATE', '' );

PrestaShop (app/config/parameters.php)

'database_host' => 'localhost',
'database_port' => '',
'database_name' => 'exemple_prestashop',
'database_user' => 'exemple_psuser',
'database_password' => 'your_password',

Laravel (.env)

DB_CONNECTION=mysql
DB_HOST=localhost
DB_PORT=3306
DB_DATABASE=exemple_laravel
DB_USERNAME=exemple_appuser
DB_PASSWORD=your_password

PHP with PDO

<?php
$host = 'localhost';
$dbname = 'exemple_wordpress';
$username = 'exemple_wpuser';
$password = 'your_password';

try {
    $pdo = new PDO(
        "mysql:host=$host;dbname=$dbname;charset=utf8mb4",
        $username,
        $password,
        [
            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC
        ]
    );
    echo "Connected";
} catch (PDOException $e) {
    die("Connection error: " . $e->getMessage());
}

Secure your databases

  • Create one user per application, with a generated password.

  • Give each user only the privileges it needs.

  • Keep remote access closed, except while you need it.

  • Keep configuration files out of reach, and never publish them in a public repository.


Fix common errors

Access denied for user

The password is wrong, or the user has no access to the database. Check the Privileged Users column in Manage My Databases. If needed, change the password, then update it in the site's configuration.

Unknown database

The database name is wrong. It must include the prefix, for example exemple_wordpress and not wordpress.

Too many connections

The site opens too many connections at once. Close connections in your code as soon as they are no longer needed, and avoid persistent connections. A cache plugin also cuts the number of queries on a WordPress site.

Damaged tables

Queries fail or data is missing. Run Repair Database in Manage My Databases. In phpMyAdmin, you can also tick the tables, then choose the repair option in the list at the bottom.

The import fails, file too large

  • Compress the file as .gz before importing it.

  • Raise upload_max_filesize and post_max_size in the PHP version tab of the client area.

  • Split the file into several parts and import them one after the other.

To import on the command line with mysql, SSH is available on request on every web hosting plan. Open a ticket to have it enabled.


Need help?

Open a ticket from Tickets › Open a ticket (dash.ouiheberg.com/tickets/new), or click the Contact support tile at the bottom of your hosting page. You can also ask your question on our Discord.

Was this article helpful?

395 views
Share this article
Hébergement web
Need web hosting?

cPanel hosting with email accounts and databases, managed from the OuiHeberg client area.

View plans →

Related Articles