MySQL databases in cPanel

MySQL is a relational database management system (RDBMS) used by most popular CMSs (WordPress, Joomla, Drupal) and web applications.

Opening MySQL Databases

1. Log in to cPanel
2. In the DATABASES section, choose MySQL® Databases
3. The database management page opens

Creating a database

Method 1: using MySQL Databases

1. Go to the Create New Database section
2. Enter a name for the database (your account prefix is added automatically)
   • Example: if you enter mysite, the database is named username_mysite
3. Click Create Database
4. A message confirms that the database has been created
5. Click Go Back to return

Method 2: using the MySQL Database Wizard (step by step)

1. Go to MySQL® Database Wizard
2. Step 1: Create a database
   • Enter a database name
   • Click Next Step
3. Step 2: Create a user
   • Enter a username
   • Enter a password (or use the Password Generator)
   • Click Create User
4. Step 3: Assign privileges
   • Choose ALL PRIVILEGES for full access
   • Or choose specific privileges
   • Click Next Step
5. Step 4: Finish
   • Make a note of the connection details
   • The database is ready to use

Creating a database user

Database users are needed to connect to databases.

1. In the MySQL Users section → Add New User
2. Enter a Username (the prefix is added automatically)
3. Enter a Password or use the generator
4. The password strength must be at least “Good” (preferably “Strong”)
5. Click Create User

Password Generator

To create a strong password:

1. Click Password Generator
2. Set the options:
   • Length: at least 12 characters (16–20 recommended)
   • Include special characters
   • Include numbers
   • Include capital letters
3. Click Generate Password
4. Copy the password (click Use Password)

Adding a user to a database

A user must be linked to a database before it can access it.

1. In the Add User To Database section
2. Choose a User from the drop-down list
3. Choose a Database from the list
4. Click Add
5. Choose the privileges:

Types of privilege

1. Click Make Changes

Managing existing databases

Viewing databases

The Current Databases section shows:

• The database name
• The size of the database
• The users assigned to it
• Actions (rename, delete)

Checking the size of a database

1. Look at the Size column in the list of current databases
2. Or use phpMyAdmin for a more detailed analysis
3. Keep an eye on usage against your account quota

Deleting a database

1. Find the database in the Current Databases list
2. Click Delete next to the database you want to remove
3. Confirm the deletion

Renaming a database

1. Click Rename next to the database
2. Enter the new name
3. Click Proceed

Managing database users

Viewing users

The Current Users section lists all MySQL users.

Changing a user’s password

1. Find the user in the Current Users section
2. Click Change Password
3. Enter a new password, or use the generator
4. Click Change Password

Deleting a user

1. Click Delete next to the user
2. Confirm the deletion

Removing a user’s privileges

1. In the Current Databases section, find the database
2. Next to the user’s name, click X (remove privileges)
3. Confirm the action

Working with phpMyAdmin

phpMyAdmin is a web interface for managing MySQL databases.

Opening phpMyAdmin

1. In the DATABASES section, click phpMyAdmin
2. The interface opens in a new tab
3. The list of databases is on the left

Basic operations in phpMyAdmin

Viewing tables

1. Select a database in the left-hand panel
2. A list of tables is shown
3. Click a table to see its data

Running SQL queries

1. Select a database
2. Go to the SQL tab
3. Enter an SQL query, for example:

SELECT * FROM wp_users WHERE user_login = 'admin';

1. Click Go

Exporting a database

1. Select a database
2. Go to the Export tab
3. Choose a method:
   • Quick — a quick export with the default settings
   • Custom — an export you can configure
4. Format: usually SQL
5. Click Go
6. The .sql file is downloaded to your computer

Importing a database

1. Select a database
2. Go to the Import tab
3. Click Choose File
4. Select the .sql file
5. Click Go
6. Wait for the import to finish

Optimising tables

1. Select a database
2. Tick the tables to optimise (or click Check All)
3. In the With selected drop-down menu, choose Optimize table
4. Click Go

Repairing tables

If a table is damaged:

1. Select the table
2. In the With selected menu, choose Repair table
3. Click Go

Search and replace

1. Select the table
2. Open the Search tab
3. Enter your search criteria
4. Or use SQL:

UPDATE wp_options 
SET option_value = REPLACE(option_value, 'old-domain.com', 'new-domain.com');

Backing up databases

Method 1: using Backup in cPanel

1. Go to Backup
2. In the Download a MySQL Database Backup section
3. Click the name of the database
4. A .sql.gz file is downloaded to your computer

Method 2: using phpMyAdmin

See the section Exporting a database

Method 3: from the command line (SSH)

mysqldump -u username_dbuser -p username_dbname > backup.sql

Automatic backups

Set this up with Cron Jobs:

1. Go to Cron Jobs
2. Add a job:

0 2 * * * mysqldump -u username_dbuser -p'password' username_dbname | gzip > /home/username/backups/db_$(date +\%Y\%m\%d).sql.gz

Restoring databases

Using Backup

1. Go to Backup
2. Open the Restore a MySQL Database section
3. Click Choose File
4. Select the .sql or .sql.gz file
5. Click Upload

Using phpMyAdmin

See the section Importing a database

Monitoring and optimisation

Checking performance

1. In phpMyAdmin, select the database
2. The Status tab shows:
   • Table sizes
   • The number of records
   • Overhead (unused space)

Optimising queries

Use EXPLAIN to analyse a query:

EXPLAIN SELECT * FROM wp_posts WHERE post_status = 'publish';

Adding indexes

To speed up searches:

CREATE INDEX idx_post_status ON wp_posts(post_status);

Clearing out unused data

For WordPress:

-- Delete post revisions
DELETE FROM wp_posts WHERE post_type = 'revision';

-- Empty the trash
DELETE FROM wp_posts WHERE post_status = 'trash';

-- Delete spam comments
DELETE FROM wp_comments WHERE comment_approved = 'spam';

-- Optimise the tables after clearing out
OPTIMIZE TABLE wp_posts, wp_comments;

Troubleshooting common problems

Database connection error

Error establishing a database connection

Causes and solutions:

1. Incorrect connection details
   • Check the database name, username and password in the configuration file
   • Make sure the user has been added to the database
2. The MySQL server isn’t running
   • Contact our support team
3. Too many connections
   • Optimise your code
   • Check for infinite loops
4. Damaged tables
   • Use Repair in phpMyAdmin

Too many connections

The limit on simultaneous connections has been exceeded:

Solutions:

• Use persistent connections
• Optimise your queries
• Turn on caching
• Contact us to ask for a higher limit

Table doesn’t exist

Causes:

• The table really is missing
• A mistake in the table name (case matters!)
• No database has been selected

Solution:

-- List the existing tables
SHOW TABLES;

-- Check the table structure
SHOW CREATE TABLE table_name;

The database is too large

Symptoms:

• Slow performance
• Quota exceeded
• Errors when taking backups

Solutions:

1. Clear out unused data
2. Optimise the tables
3. Archive old data
4. Use external storage for files
5. Upgrade your hosting plan

I can’t import a large database

Limits:

• PHP memory_limit
• PHP max_execution_time
• MySQL max_allowed_packet
• The size of the uploaded file

Solutions:

1. Raise the limits in MultiPHP INI Editor
2. Split the dump into parts
3. Use the BigDump script
4. Import over SSH:

mysql -u username_dbuser -p username_dbname < backup.sql

Database security

Best practice

1. Strong passwords
   • At least 16 characters
   • Letters, numbers and special characters
2. Minimum privileges
   • Give only the privileges that are needed
   • Don’t use the root account
3. Regular backups
   • At least once a day for critical data
   • Keep copies off the server
4. Updates
   • Update your CMS and plugins regularly
   • Apply security patches
5. Monitoring
   • Check the logs for suspicious activity
   • Use intrusion detection systems

Protecting against SQL injection

In your PHP code, use:

Prepared statements (mysqli):

$stmt = $conn->prepare("SELECT * FROM users WHERE username = ?");
$stmt->bind_param("s", $username);
$stmt->execute();

PDO:

$stmt = $pdo->prepare("SELECT * FROM users WHERE username = :username");
$stmt->execute(['username' => $username]);

Never do this:

// ❌ DANGEROUS - vulnerable to SQL injection!
$query = "SELECT * FROM users WHERE username = '$username'";

Limits and quotas

Typical hosting limits:

Check your limits in the right-hand panel of cPanel.

See also

• A detailed guide to phpMyAdmin
• Optimising MySQL performance
• Backups
• An alternative: PostgreSQL
• Database security

Useful SQL queries

Basic operations

-- Create a table
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Insert data
INSERT INTO users (username, email) VALUES ('john', 'john@example.com');

-- Update data
UPDATE users SET email = 'newemail@example.com' WHERE username = 'john';

-- Delete data
DELETE FROM users WHERE id = 5;

-- Select data
SELECT * FROM users WHERE created_at > '2024-01-01';

-- Count records
SELECT COUNT(*) FROM users;

-- Sort
SELECT * FROM users ORDER BY created_at DESC LIMIT 10;

External links

• Official cPanel MySQL documentation
• MySQL Documentation
• phpMyAdmin Documentation