How to create and manage MySQL databases in cPanel

Databases are essential components of modern web applications and content management systems like WordPress. They store and organise data, allowing dynamic content generation and user interaction. Whether you're setting up a new WordPress site or developing a custom web application, managing your MySQL databases effectively is crucial for performance and security. This guide will walk you through creating and managing MySQL databases using cPanel.

Create a database

To create a MySQL database in cPanel, follow these steps:

1. Log in to your cPanel account through the server hostname provided in your welcome email, using port 2083. Ensure you have the correct credentials, as failed login attempts may temporarily lock your access.
2. In the cPanel dashboard, locate the "Databases" section and click on "MySQL Databases." This section allows you to manage all aspects of your databases, including creation, deletion, and user management.
3. In the "Create New Database" field, enter a name for your database. It's a good practice to use a naming convention that reflects the purpose of the database, such as yourdomain_wp for a WordPress site. Avoid using special characters or spaces to prevent potential issues with database queries.
4. Click "Create Database." You will see a confirmation message that your database has been created successfully. This message indicates that the database is ready for configuration and use.

Once your database is created, it will appear in the "Current Databases" list, where you can manage it further. This list provides options to rename, delete, or check the status of your databases, ensuring you have full control over your data management.

Create a database user

After creating a database, the next step is to create a user who can access it. Follow these steps:

1. In the "MySQL Users" section of the MySQL Databases page, enter a username in the "Username" field. Choose a username that is easy to remember but not easily guessable to enhance security.
2. Enter a strong password in the "Password" field. Use the Password Generator tool to create a secure password. A strong password typically includes a mix of uppercase and lowercase letters, numbers, and special characters.
3. Click "Create User." Once the user is created, you need to assign it to a database. This step is crucial as it links the user to specific databases, allowing for controlled access.
4. Scroll down to the "Add User to Database" section. Select the user and the database you created from the dropdown menus. This selection ensures that the user has the necessary permissions to interact with the database.
5. Click "Add." On the next screen, select "All Privileges" to grant the user full access to the database, then click "Make Changes." Granting all privileges is usually necessary for applications like WordPress, but consider limiting permissions for other applications to enhance security.

Your user now has the necessary permissions to interact with the database. Regularly review user permissions to ensure they align with your security policies and application requirements.

Connect to your website

To connect your website to the MySQL database, you need to configure your application's database connection settings. For WordPress, this involves editing the wp-config.php file:

1. Access your website files via cPanel File Manager or an FTP client. Ensure you have the necessary permissions to edit files in your web directory.
2. Locate the wp-config.php file in the root directory of your WordPress installation. This file contains critical configuration settings for your WordPress site.
3. Edit the file and update the following variables with your database details:
4. define('DB_NAME', 'yourdomain_wp'); - This specifies the name of the database you created.
5. define('DB_USER', 'yourusername'); - Enter the username associated with your database.
6. define('DB_PASSWORD', 'yourpassword'); - Use the password you set for the database user.
7. define('DB_HOST', 'localhost'); - Typically, 'localhost' is used unless your hosting provider specifies a different host.

Save the changes. Your website should now be connected to the database. Test the connection by accessing your website to ensure it loads correctly. If you encounter errors, double-check the database credentials and ensure the MySQL server is running.

Using phpMyAdmin

phpMyAdmin is a powerful tool for managing MySQL databases through a web interface. To access phpMyAdmin:

1. In cPanel, go to the "Databases" section and click on "phpMyAdmin." This tool provides a graphical interface to perform complex database operations with ease.
2. Select the database you want to manage from the list on the left panel. This action will display all the tables within the selected database.
3. You can browse tables, run SQL queries, and perform various operations such as exporting and importing data. Use the SQL tab to execute custom queries for advanced database manipulations.
4. To export a database, click "Export" and choose your export settings. Opt for the "Quick" export method for a straightforward export or "Custom" for more control over the output format and options.
5. For importing, click "Import" and upload your SQL file. Ensure the file size does not exceed the limit set by your server, or consider splitting large files.
6. Use the "Search" feature to find specific data within your tables. This feature is useful for locating records without manually browsing through large datasets.

phpMyAdmin offers a comprehensive set of tools for database management tasks. Familiarise yourself with its features to efficiently manage and troubleshoot your databases.

Database maintenance

Regular maintenance of your MySQL databases is essential for performance and data integrity. Here are some key maintenance tasks:

• Repair tables: If you suspect a table is corrupted, use the "Repair Table" function in phpMyAdmin. Regularly check for and repair corrupted tables to prevent data loss or application errors.
• Optimise tables: This process reclaims unused space and defragments the database. In phpMyAdmin, select the tables and choose "Optimize Table." Regular optimisation can improve query performance and reduce load times.
• Backup databases: Regularly back up your databases to prevent data loss. Use cPanel's backup tools or third-party solutions to automate this process.
• Update database software: Ensure your MySQL server and phpMyAdmin are up to date to benefit from the latest security patches and performance improvements.

Troubleshooting

Here are solutions to common MySQL database issues:

• Connection errors: Verify database credentials in your configuration file and ensure the database server is running. Check firewall settings to ensure they are not blocking database connections.
• Import too large: If your SQL file exceeds the upload limit, split the file or increase the upload_max_filesize and post_max_size in the php.ini. Alternatively, use command-line tools like MySQL command-line client for large imports.
• Crashed table: Use the "Repair Table" function in phpMyAdmin to fix crashed tables. Regularly monitor your database for signs of crashes, such as slow queries or missing data, and address issues promptly.
• Slow queries: Use the "Slow Query Log" in MySQL to identify and optimise slow queries. Indexing frequently accessed columns can significantly improve query performance.

For advanced database management, refer to the cPanel database documentation.

Related guides

• How to Install WordPress on GHost Cloud
• WordPress Troubleshooting - Common Errors and Fixes
• cPanel Overview - A Complete Beginner's Guide
• How to Manage Files Using cPanel File Manager