MySQL® Databases | cPanel & WHM Documentation

This comprehensive guide details the process of creating, managing, and deleting MySQL® databases and database users within your cPanel & WHM interface. Efficient database management is crucial for the performance and security of your web applications, and this document will walk you through each essential step.

Overview

The MySQL® Databases interface empowers you to efficiently create, manage, and delete MySQL® databases and their associated users. Understanding the naming conventions and limitations is vital for successful database administration.

• A database name can contain a maximum of 64 characters. It's important to note that due to cPanel & WHM's internal storage methods, each underscore character (_) in a database name 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 database prefix itself and the underscore character that separates it from your chosen database name. Each additional underscore will further reduce the available character count by two.

Note: To optimize database naming, you can utilize the Force short prefix for MySQL and MariaDB databases setting. This option is located in WHM’s Tweak Settings interface (WHM » Home » Server Configuration » Tweak Settings). Enabling this feature limits the database prefix to a concise eight characters. For more detailed information regarding MySQL and MariaDB® prefix settings, please 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 (WHM » Home » Server Configuration » Tweak Settings) will prevent the creation of new accounts whose usernames share the same first eight characters. This is an important consideration for account provisioning.

For operations such as adding or deleting information within existing databases, duplicating a database, or executing complex MySQL queries and scripts, you should utilize cPanel’s phpMyAdmin interface (cPanel » Home » Databases » phpMyAdmin).

Warning: It is crucial to avoid using phpMyAdmin for the initial creation of databases or database users. phpMyAdmin does not correctly map databases within the cPanel & WHM system, which can lead to critical issues with backup and restoration functionalities. Always use the cPanel interface for database and user creation.

Note: For your initial database and user setup, we highly recommend using cPanel’s MySQL Database Wizard interface (cPanel » Home » Databases » MySQL Database Wizard). This wizard streamlines the creation process and ensures proper integration with cPanel & WHM.

Create a Database

To establish a new database for your applications or website, follow these straightforward steps:

1. Within the New Database text box, carefully enter a descriptive name for your database.

   Note: If your hosting provider has activated database prefixing, you will observe a prefix (derived from your account username, followed by an underscore character, e.g., username_) preceding the New Database text box. The system automatically appends this prefix to your specified database name, ensuring uniqueness across the server.

2. Click the Create Database button. A new interface will appear, confirming the creation.
3. Click Go Back to return to the main 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 (cPanel » Home » Databases » phpMyAdmin). For in-depth guidance on utilizing phpMyAdmin, please consult the official phpMyAdmin website.

Remote MySQL Host

Note: This section will only be visible and relevant if your hosting provider has configured a remote MySQL server for your account.

This section displays the network address of the remote MySQL server, which has been pre-configured by your hosting provider. You can utilize this host address to establish external connections to your MySQL server, which is essential for applications running outside the local server environment.

Managing Database Integrity

Maintaining the integrity of your databases is paramount. The cPanel interface provides tools to check for and repair potential errors within your MySQL databases.

Check a Database

To diagnose a database for any potential errors or corruption, perform the following steps:

1. From the Check Database dropdown menu, select the specific database you wish to examine.
2. Click the Check Database button. A new interface will load, and the system will proceed to verify the operational integrity of the selected database.
   • Should the system detect an issue, it will clearly display the name of the corrupted table(s).
   • If the message Check Complete appears, it indicates that the database is functioning correctly and no errors were found.
3. Click Go Back to navigate back to the primary interface.

Repair a Database

If a database becomes corrupted or encounters errors, you can attempt to restore its functionality using the repair tool.

1. In the Repair Database dropdown menu, select the database that requires repair.
2. Click the Repair Database button. A new interface will appear, where the system will automatically attempt to rectify the database issues.
   • If the system is unable to fully repair the database, it will endeavor to identify and report the source of the corrupt data.
   • If the Repair Complete message displays, the system has successfully restored the database to a functional state.
3. Click Go Back to return to the main interface.

Current Databases Overview

The Current Databases table provides a comprehensive listing of all MySQL databases associated with your account, along with critical information and management options:

• Database: This column displays the name of each database. This value also serves as the DB_HOST when connecting to the database from within your applications.
• Size: Indicates the current disk space occupied by the database.
• Privileged Users: Lists the users who have been granted access and permissions to interact with the database. These names correspond to the DB_USERNAME values used for database connections.

  Note: When adjusting database user privileges, it is imperative to confirm that you are modifying access for the correct database. A single user may have access to multiple databases.

  • To revoke a user's access from a particular database, locate the trash can icon () adjacent to the desired user, and then confirm by clicking Revoke User Privileges from Database.
  • To fine-tune a user’s specific privileges for a database, click on the username, select or deselect the appropriate checkboxes to configure the desired permissions, and then click Make Changes to apply your selections.

• Actions: This column provides icons for available management actions for each database, including renaming or deleting the database.

Renaming a Database

Warning: Renaming a MySQL database is an operation with potential risks. We strongly advise performing a comprehensive backup of your MySQL database using cPanel’s Backup interface (cPanel » Home » Files » Backup) before attempting any rename operation.

Furthermore, when a database is renamed, all active connections to that database will be immediately terminated. You will also be responsible for manually updating any configuration files and applications that reference the old database name to reflect the new one. Be aware that the renaming process may take longer for larger and more complex databases due to data transfer requirements.

Although MySQL itself does not natively support direct database renaming, cPanel & WHM facilitates this process through a series of automated steps:

1. In the Current Databases table, click the Rename action for the database you wish to modify.
2. Enter the desired new database name into the New name text box.
3. Click Proceed to initiate the renaming process.

The system undertakes the following actions during a database "rename":

1. A completely 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 (such as stored procedures and functions) are recreated within the new database.
4. Finally, the old database and its associated grants are securely deleted.

Warning: If any of the initial 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. Should this restoration process also fail, the API function's error response will provide details on these additional failures.

In rare circumstances, the new database might be successfully created, but the system may fail to delete the old database or its grants. In such cases, the rename action is still considered successful, but the API function will return warnings indicating the failure to delete the old resources. Manual intervention may be required in these specific scenarios.

Deleting a Database

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

1. In the Current Databases table, click the Delete action corresponding to the database you intend to remove.
2. To confirm the permanent deletion, click Delete Database. This action is irreversible.
3. Click Go Back to return to the main interface.

Managing MySQL Users

After you have created your databases, the next essential step is to create and assign users to them, configuring appropriate privileges to control access.

Note: It is important to understand that MySQL user accounts are separate and distinct from mail or web administrator accounts. You must create a MySQL user account independently before you can associate it with any existing database.

Adding a MySQL User

To create a new user account for your MySQL databases, perform the following steps:

1. Enter a unique username in the Username text box. To understand the specific character limits for database usernames, please refer to the relevant section below 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, a MySQL database with the db_ prefix allows usernames of up to 13 characters (16 - 3 = 13).
     • Similarly, a MySQL database with an example_ prefix allows usernames of up to eight characters (16 - 8 = 8).
   • MySQL 5.7 and later
     MySQL 5.7 expands the database username limit to 32 characters. This limit also incorporates the database prefix, which for this version typically includes the first sixteen characters of the cPanel account’s username followed by an underscore, summing up to seventeen characters.
     • For instance, a MySQL database with the db_ prefix permits usernames containing up to 29 characters (32 - 3 = 29).
     • A MySQL database with an example_ prefix allows usernames with up to 24 characters (32 - 8 = 24).
   • MariaDB
     MariaDB offers a more generous database username limit of 47 characters. The system includes the database prefix, which for MariaDB comprises the entirety of the cPanel account’s username followed by an underscore character.
     • As an illustration, a MariaDB database with the db_ prefix accommodates usernames of up to 44 characters (47 - 3 = 44).
     • A MariaDB database with an example_ prefix allows usernames up to 39 characters (47 - 8 = 39).
2. Enter your desired password into the Password text box, and then re-enter it in the Password (Again) text box to confirm.

   Note: This password value corresponds to the user’s DB_PASSWORD. For security reasons, cPanel & WHM does not reveal this value once set. If you forget the password, you will need to change the user’s password through the interface.

   The system provides a visual strength meter, evaluating your password on a scale of 0 to 100 points, where 0 is weak and 100 is highly secure. Many web hosts enforce a minimum password strength requirement; a green strength meter indicates that your chosen password meets or exceeds this standard. You can also utilize the Password Generator tool to create a robust and secure password. For further details on password best practices, please consult our Password & Security documentation.

3. Click Create User to finalize the user account creation.
4. Click Go Back to return to the main interface.

Adding a User to a Database

Once a MySQL user has been created, you can grant them access to one or more databases and define their specific privileges.

1. In the Add User To Database section, use the respective dropdown menus to select the desired user and the database to which you wish to grant access.
2. Click the Add button. This will open the MySQL Account Maintenance interface.
3. On the MySQL Account Maintenance interface, select the checkboxes that correspond to the specific privileges you intend to grant to this user for the chosen database.

   Note: To grant 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 interface.

For more comprehensive information regarding MySQL user privileges and their implications, we recommend referring to the official MySQL documentation.

Current Users Management

The Current Users table lists all MySQL database users associated with your cPanel account and provides essential management actions:

• Change Password: Click this option to modify an existing database user’s password. You will be prompted to enter and confirm the new password before clicking Change Password to update it.
• Rename: Use this action to change a database user’s username. Enter the desired new username, and then click Change Username to apply the modification.
• Delete: Click this option to permanently remove a database user. You will be asked to confirm by clicking Delete User. This action is irreversible and will remove the user from all databases they were associated with.

Additional Resources

For further assistance and related topics, please explore the following documentation:

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