Managing MySQL Databases with cPanel and phpMyAdmin

If your website runs on WordPress, Joomla, a custom PHP application, or virtually any other dynamic platform, there is a MySQL database running behind the scenes. The database stores your posts, pages, user accounts, settings, orders, and everything else that makes your site dynamic and interactive. Understanding how to manage that database is an important skill for any website owner. Fortunately, cPanel and phpMyAdmin make it far more approachable than it sounds.

What Is a MySQL Database?

MySQL is a relational database management system - essentially a highly organized way of storing and retrieving structured data. Think of it as a collection of spreadsheets (called tables) that are linked together and can be queried almost instantly, even with millions of rows of data.

When you install WordPress through Softaculous, it automatically creates a database and fills it with tables for posts, comments, users, options, and more. Every time someone visits your site, WordPress queries the database to retrieve the content to display.

Creating a MySQL Database in cPanel

If you need to create a database manually (for a custom application, or if you are migrating an existing site), here is how:

1. Log in to cPanel and scroll to the Databases section
2. Click MySQL Databases
3. Under "Create New Database," enter a name for your database (e.g., mysite_db) and click Create Database. Note that cPanel prepends your account username automatically (e.g., username_mysite_db).
4. Next, create a database user - scroll down to "MySQL Users," enter a username and a strong password, and click Create User
5. Finally, add the user to the database - scroll to "Add User to Database," select your new user and database, and click Add
6. On the privilege screen, click All Privileges and then Make Changes

Your database is now ready. Write down the database name, username, and password - you will need these when configuring your application.

Using the MySQL Database Wizard

If the multi-step process above seems complex, cPanel also offers a MySQL Database Wizard that walks you through all the steps in a single guided flow. Look for it under the Databases section alongside the standard MySQL Databases tool.

Introduction to phpMyAdmin

phpMyAdmin is a web-based application for managing MySQL databases through a graphical interface. It lets you:

• Browse your database tables and their contents
• Run SQL queries
• Import and export databases (as .sql files)
• Edit, add, and delete rows of data
• Modify table structure

To access it: in cPanel, under the Databases section, click phpMyAdmin. You will be logged in automatically.

The left sidebar shows all the databases in your account. Click a database name to expand it and see its tables. Click a table name to see its contents.

Common Tasks in phpMyAdmin

Browsing and editing data: Click on a table (e.g., wp_options in a WordPress database) and then click the Browse tab to see its contents. Click the pencil icon on any row to edit it inline.

Running SQL queries: Click a database in the left sidebar, then click the SQL tab at the top. Type your query in the text box and click Go. For example, to change the WordPress admin email:

UPDATE wp_options SET option_value = 'newemail@yourdomain.ca' WHERE option_name = 'admin_email';

Exporting a database backup: Click your database in the left sidebar, then click the Export tab. Choose "Quick" export and "SQL" format, then click Go. Save the downloaded .sql file - this is a complete backup of your database.

Importing a database: Click your database in the left sidebar, then click the Import tab. Click "Choose File," select your .sql file, and click Go. This is how you restore a database from backup or migrate one from another server.

Important Safety Practices

Working directly with a database is powerful - and that power comes with risk. Follow these practices:

• Always export a backup before making any changes. A single wrong SQL query can corrupt data instantly.
• Be extremely careful with DELETE and UPDATE statements - especially without a WHERE clause, which would affect every row in the table.
• Do not delete tables you do not recognize in a WordPress database - many plugins add their own tables, and removing them can break functionality.
• Do not edit the database directly for routine tasks if there is a WordPress admin interface option available - use the database only when no other option exists.

Checking Database Disk Usage

Large databases can contribute significantly to your disk space usage. In cPanel, go to MySQL Databases and scroll down to see the size of each database. In phpMyAdmin, you can see table-by-table sizes by clicking a database and looking at the Size column in the table list.

To reduce database bloat in WordPress, plugins like WP-Optimize can clean up post revisions, spam comments, transients, and other orphaned data safely.

Managing your MySQL databases does not need to be intimidating. cPanel and phpMyAdmin give you all the tools you need through a visual interface, without requiring you to become a database administrator. At dotCanada, our hosting plans include full MySQL database support, and our support team is happy to help if you have questions about your database setup.