MySQL® Databases | cPanel & WHM Documentation

Overview of MySQL Databases in cPanel & WHM

This interface provides comprehensive tools for creating, managing, and deleting MySQL® databases and their associated users within your cPanel & WHM environment. It serves as your central hub for efficient database administration.

It is important to note the character limits for database names. A database name can contain a maximum of 64 characters. However, specific considerations apply, especially concerning underscores and prefixing:

• When cPanel & WHM stores MySQL database names, each underscore character (_) effectively consumes two characters from this limit.
• If database prefixing is enabled, a database name is limited to a maximum of 63 characters. This count includes the automatically prepended database prefix and the underscore character that separates it from your chosen name. Any additional underscores within your chosen name will also require two characters from this limit.

Note: To enforce an eight-character limit for the database prefix, you can utilize the Force short prefix for MySQL and MariaDB databases setting. This option is located in WHM’s Tweak Settings interface (accessible via WHM » Home » Server Configuration » Tweak Settings). For a more in-depth understanding of MySQL and MariaDB® prefix configurations, refer to our dedicated Tweak Settings documentation.

Warning: Be aware that enabling the Force short prefix for MySQL and MariaDB databases setting in WHM’s Tweak Settings interface could prevent the creation of new accounts if their usernames share the same initial eight characters.

For operations such as adding or deleting data within existing databases, copying databases, or executing MySQL queries and scripts, you should use cPanel’s phpMyAdmin interface (found under cPanel » Home » Databases » phpMyAdmin).

Warning: It is crucial to avoid using phpMyAdmin for creating new databases or database users. Databases created via phpMyAdmin do not undergo proper database mapping, which can lead to critical issues with backups and restorations.

Note: For your initial database and user setup, we highly recommend utilizing cPanel’s MySQL Database Wizard interface (located at cPanel » Home » Databases » MySQL Database Wizard). This wizard streamlines the creation process and ensures proper configuration.

Creating a MySQL Database

Follow these steps to successfully create a new MySQL database within your cPanel account:

1. Locate the New Database text box and enter your desired name for the database.

   Note: Depending on your hosting provider's configuration, database prefixing might be enabled. In such cases, a prefix derived from your account username, followed by an underscore (_), will automatically appear before the New Database text box. The system will automatically prepend this prefix to the database name you enter.

2. Click the Create Database button. A confirmation interface will appear, indicating the database has been successfully created.
3. Click Go Back to return to the main MySQL Databases interface. Your newly created database will now be listed in the Current Databases table.

Once your database is created, you can access and manage its contents using the phpMyAdmin interface (accessible via cPanel » Home » Databases » phpMyAdmin). For detailed instructions on utilizing phpMyAdmin's features, please refer to the comprehensive documentation available on the phpMyAdmin website.

Remote MySQL Host Configuration

Note: This section is only visible if a remote MySQL configuration has been established and provided by your hosting provider.

Here, you will find the address of the remote MySQL server that your hosting provider has configured. This host address is essential for establishing connections to the MySQL server from external applications or tools, allowing for remote database access.

Managing and Modifying MySQL Databases

If you encounter any issues or suspect problems with a database, cPanel & WHM provides robust tools to check for and repair errors, helping you maintain database integrity.

Checking a Database for Errors

To perform an error check on a database, follow these steps:

1. From the Check Database dropdown menu, select the specific database you wish to examine for errors.
2. Click the Check Database button. A new interface will load, and the system will initiate a scan to determine if the database is functioning correctly.
   • Should the system identify any problems within the database, it will display the name of the affected (corrupt) table.
   • If the message "Check Complete" is displayed, it confirms that the database is operating without detected errors.
3. Click Go Back to return to the primary MySQL Databases interface.

Repairing a Corrupt Database

In cases where one of your databases becomes corrupt, you can attempt to repair it using the built-in tools provided by cPanel & WHM.

To initiate a database repair, follow these steps:

1. From the Repair Database dropdown menu, select the database that requires repair.
2. Click the Repair Database button. A new interface will appear, and the system will automatically try to repair the database.
   • If the system is unable to repair the database, it will provide information attempting to pinpoint the source of the corrupt data.
   • If the message "Repair Complete" is displayed, it indicates that the database has been successfully repaired.
3. Click Go Back to return to the main interface.

Current Databases: Overview and Actions

The Current Databases table provides a clear overview of all MySQL databases associated with your account, detailing the following critical information:

• Database: This column displays the name of each database. This value also corresponds to the DB_HOST.
• Size: Indicates the current size of the database, providing insight into its storage consumption.
• Privileged Users: Lists the users who possess the necessary permissions to interact with and manage the database. These names are also referred to as DB_USERNAME values.

  Note: When adjusting database user privileges, it is essential to ensure that you are modifying access for the correct database. A single user may have access to multiple databases, so careful selection is required.

  • To revoke a user's access from a specific database, click the trash can icon next to the desired user, and then confirm by clicking Revoke User Privileges from Database.
  • To fine-tune a user's privileges for a particular database, click on the username. You can then select or deselect the appropriate checkboxes to configure the desired permissions, followed by clicking Make Changes.
• Actions: This column contains icons representing the available actions for each database. You can click the relevant icon to either rename or delete a database.

Renaming a MySQL Database

Warning: Renaming a MySQL database carries potential risks and requires careful consideration. We highly advise creating a comprehensive backup of your MySQL database using cPanel’s Backup interface (cPanel » Home » Files » Backup) before attempting any rename operation.

Additionally, please be aware of the following implications:

• All active connections to the database will be terminated during the renaming process.
• You are solely responsible for manually updating all relevant configuration files and applications to reflect the new database name. Failure to do so will result in connection errors.
• The system requires more time to rename larger and more complex databases, so plan accordingly.

To rename a database, follow these steps:

1. In the Current Databases table, locate the desired database and click the Rename action icon.
2. Enter the new database name into the New name text box.
3. Click Proceed to confirm the renaming operation.

It is important to understand that MySQL itself does not directly support renaming databases. When cPanel & WHM "renames" a database, the system executes a series of underlying operations to achieve this outcome:

1. A new database is created with the specified new name.
2. All data from the original database is meticulously moved to the newly created database.
3. Existing grants and stored code (e.g., stored procedures, functions, triggers) are recreated within the new database.
4. Finally, the old database and its associated grants are permanently deleted.

Warning:

• Should any of the first three steps (creating the new database, moving data, or recreating grants/code) fail, the system will report an error and attempt to revert the database to its original state. If this restoration process also fails, the API function's error response will provide detailed descriptions of these additional failures.
• In rare scenarios, the system may successfully create the new database but fail to delete the old database or its grants. In such cases, the rename action is still considered a success; however, the API function will return warnings indicating the failure to delete the old database or grants.

Deleting a MySQL Database

To permanently remove a database from your account, follow these steps:

1. In the Current Databases table, locate the database you wish to delete and click the Delete action icon.
2. To confirm and permanently delete the database, click Delete Database. This action is irreversible.
3. Click Go Back to return to the main interface.

Adding and Managing MySQL Users

Once you have created your databases, the next crucial step is to add users to these databases and meticulously configure their access privileges to control data interaction.

Note:

• It is imperative to create MySQL user accounts distinctly and separately from any mail or web administrator accounts to maintain proper security segregation.
• A user account must be created before it can be assigned to an existing database.

Adding a New MySQL User

To create a new MySQL user account, follow these detailed steps:

1. In the Username text box, enter the desired username. Understanding the character limits for database usernames is important, and these vary based on your MySQL or MariaDB version:

   MySQL 5.6 and earlier

   MySQL 5.6 imposes a limit of 16 characters for database usernames. This character count includes the database prefix, which typically consists of the first eight characters of your cPanel account's username followed by an underscore, totaling nine characters.

   For example:

   • For a MySQL database with a db_ prefix, usernames can contain up to 13 characters.
   • For a MySQL database with an example_ prefix, usernames can contain up to eight characters.

   MySQL 5.7 and later

   MySQL 5.7 extends the database username limit to 32 characters. This limit also accounts for the database prefix, which generally comprises the first sixteen characters of the cPanel account's username plus an underscore, summing up to seventeen characters.

   For example:

   • With a db_ prefix for a MySQL database, usernames are allowed up to 29 characters.
   • With an example_ prefix, usernames for a MySQL database can contain up to 24 characters.

   MariaDB

   MariaDB offers the most generous limit, allowing database usernames up to 47 characters. The database prefix in MariaDB includes the entirety of the cPanel account's username and an underscore character.

   For example:

   • For a MariaDB database with a db_ prefix, usernames can extend to 44 characters.
   • For a MariaDB database with an example_ prefix, usernames can have up to 39 characters.
2. Enter and then confirm the new password in the designated text boxes.

   Note:

   • This password serves as the user's DB_PASSWORD value. However, cPanel & WHM does not display this value for security reasons. If you forget it, you will need to change the user’s password.
   • The system provides a password strength evaluator, rating passwords on a scale of 0 (weak) to 100 (very secure).
   • Some web hosting providers enforce a minimum password strength requirement. A green Strength meter indicates that your chosen password meets or exceeds this required strength.
   • For assistance in creating a highly secure password, click the Password Generator button. More information on creating strong passwords can be found in our Password & Security documentation.
3. Click the Create User button to finalize the user creation.
4. Click Go Back to return to the main MySQL Databases interface.

Adding a User to a Database and Setting Privileges

After creating a MySQL user, you must assign them to a database and define their access privileges. Follow these steps to complete the process:

1. Navigate to the Add User To Database section. From the respective dropdown menus, select the user you wish to add and the database they will access.
2. Click the Add button. This will open the MySQL Account Maintenance interface.
3. On the MySQL Account Maintenance page, select the checkboxes corresponding to the specific privileges you intend to grant to this user for the selected database.

   Note: To assign all available privileges to the user, simply select the ALL PRIVILEGES checkbox.

4. Click Make Changes to apply the selected privileges.
5. Click Go Back to return to the main MySQL Databases interface.

For a more comprehensive understanding of MySQL user privileges and their implications, we recommend consulting the official MySQL documentation.

Current Users: Password, Rename, and Delete Options

The Current Users table provides a complete list of all MySQL database users associated with your cPanel account. From this table, you can perform several essential management actions:

• Change Password: Click this option to modify a database user’s password. You will be prompted to enter and confirm the new password, then click Change Password to save the changes.
• Rename: Click here to rename an existing database user. Enter the new desired username in the provided field, and then click Change Username to apply the modification.
• Delete: This option allows you to permanently remove a database user. After clicking Delete, you will need to confirm your action by clicking Delete User. This action is irreversible.

Additional Resources

For further assistance and detailed information on related topics, please refer to the following documentation:

• Database Wizard
• Manage My Databases
• MySQL® Database Wizard
• MySQL® Manager
• Remote Database Access
• The cPanel Glossary