Overview of MySQL® Databases
This interface provides comprehensive tools for creating, managing, and deleting MySQL® databases and their associated users within your cPanel & WHM environment. Understanding the specific character limits and naming conventions for databases is crucial for smooth operation.
A database name is subject to a maximum length of 64 characters. It is important to note that due to cPanel & WHM's internal storage methods for MySQL database names, each underscore character (`_`) counts as two characters toward this limit. If database prefixing is enabled by your hosting provider, a database name is limited to a maximum of 63 characters, which includes the automatically prepended database prefix and its underscore character. Any additional underscores within the custom part of the name will also consume two characters each from this limit.
Note: To enforce an 8-character limit for the database prefix, your hosting provider can enable the Force short prefix for MySQL and MariaDB databases setting in WHM’s Tweak Settings interface (WHM » Home » Server Configuration » Tweak Settings). For more detailed information regarding MySQL and MariaDB® prefix settings, please refer to our dedicated Tweak Settings documentation.
Warning: Activating the Force short prefix for MySQL and MariaDB databases setting in WHM’s Tweak Settings interface (WHM » Home » Server Configuration » Tweak Settings) will restrict the creation of new accounts that share the same initial eight characters of their usernames. Exercise caution when implementing this setting.
For operations such as adding or deleting information within existing databases, copying databases, or executing MySQL queries and scripts, utilize cPanel’s powerful phpMyAdmin interface (cPanel » Home » Databases » phpMyAdmin).
Warning: It is strongly advised not to use phpMyAdmin for the initial creation of databases or database users. phpMyAdmin does not properly map databases within the cPanel system, which can lead to issues with backup and restoration functionalities. Always use the cPanel interface for these foundational tasks.
Note: For your initial database and user setup, we highly recommend using cPanel’s intuitive MySQL Database Wizard interface (cPanel » Home » Databases » MySQL Database Wizard). This wizard streamlines the process, ensuring proper configuration.
Create a Database
To successfully create a new MySQL database, follow these straightforward steps:
- In the New Database text box, provide a unique and descriptive name for your new database.
Note: If your hosting provider has implemented database prefixing, you will observe a prefix consisting of your account username and an underscore character (`_`) preceding the New Database text box. The system automatically appends this prefix to the database name you enter, forming the complete database identifier.
- Click the Create Database button. A confirmation interface will then appear, indicating the successful creation.
- 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 detailed guidance on utilizing phpMyAdmin's features, please visit the official phpMyAdmin website.
Remote MySQL Host
Note: This particular section is only visible if a remote MySQL configuration has been established by your hosting provider.
This section displays the network address of the remote MySQL server, as configured by your hosting provider. This address is essential for establishing connections to the MySQL server from external applications or systems, allowing for remote database interaction.
Modify Databases
Regular maintenance and troubleshooting are key to database health. If you encounter any issues with a database, cPanel provides tools to check for and repair errors.
Check a Database
To check a database for potential errors and ensure its integrity, follow these steps:
- From the Check Database menu, select the specific database you wish to examine.
- Click the Check Database button. A new interface will display the system's progress as it verifies the database's functionality.
- Should the system detect any problems within the database, it will clearly indicate the name of any corrupt tables.
- If the message Check Complete is displayed, it confirms that the database is functioning correctly and no errors were found.
- Click Go Back to return to the main interface.
Repair a Database
If a database becomes corrupt, you can attempt to repair it using the built-in tools. This process aims to restore the database to a functional state.
- From the Repair Database menu, select the database that requires repair.
- Click the Repair Database button. A new interface will appear, where the system will attempt to automatically rectify any corruption within the database.
- In cases where the system cannot fully repair the database, it will endeavor to identify and report the source of the corrupt data.
- If the message Repair Complete is displayed, it signifies that the system has successfully repaired the database.
- Click Go Back to return to the main interface.
Current Databases
The Current Databases table provides a comprehensive overview of all MySQL databases associated with your account, detailing important information for each:
- Database: This column displays the full name of the database. This value also often corresponds to the
DB_HOST
value used in application configurations. - Size: This indicates the current size of the database in megabytes or gigabytes, offering insight into its storage consumption.
- Privileged Users: This lists the database users who have been granted permissions to interact with and manipulate this specific database. These names frequently serve as
DB_USERNAME
values for applications.Note: When adjusting database user privileges, always ensure you are modifying access for the correct database. A single user may have access to multiple databases, so precision is key.
- To revoke a user's access from a database, click the trash can icon
next to the desired user, then confirm by clicking Revoke User Privileges from Database.
- To fine-tune a user’s privileges for a specific database, click the username, select or deselect the appropriate checkboxes to configure the desired permissions, and then click Make Changes to apply.
- To revoke a user's access from a database, click the trash can icon
- Actions: This column contains various icons representing the available administrative actions for each database. You can click the relevant icon to either rename or delete a database.
Rename a Database
Warning: Renaming a MySQL database carries inherent risks and is a potentially disruptive operation. We therefore strongly recommend creating a full backup of the MySQL database using cPanel’s Backup interface (cPanel » Home » Files » Backup) before attempting any rename operation. Furthermore:
- Renaming a database will terminate all active connections to it.
- You are responsible for manually updating all configuration files and applications to reflect the new database name.
- Larger and more complex databases will naturally require a longer time to complete the renaming process.
To rename a database, follow these steps:
- In the Current Databases table, locate the desired database and click its Rename action icon.
- Enter the new, desired database name into the New name text box.
- Click Proceed to initiate the renaming process.
It's important to understand that MySQL itself does not natively support renaming databases. When cPanel & WHM performs a "rename" operation, it effectively executes a series of underlying steps to achieve this:
- A new database with the specified name is created.
- All data from the old database is meticulously moved to the newly created database.
- Database grants and any stored procedures, functions, or triggers from the old database are recreated in the new database.
- Finally, the original (old) database and its associated grants are permanently deleted.
Warning: Be aware of potential issues during this process:
- If any of the first three steps (creation, data movement, or grant/code recreation) 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 about these additional failures.
- In some rare scenarios, the system might successfully create the new database but fail to delete the old database or its grants. While the system may still report the rename action as a success, the API function will return warnings detailing the failure to remove the old database or grants. It is crucial to check for these warnings.
Delete a Database
To permanently remove a database from your account, follow these steps. Please proceed with caution, as this action is irreversible.
- In the Current Databases table, locate the database you wish to delete and click its Delete action icon.
- To confirm and permanently delete the database, click Delete Database.
- Click Go Back to return to the main interface.
Add a MySQL User
After creating your database, the next crucial step is to create and add users to it, then configure their specific privileges. This ensures secure and controlled access to your database resources.
Note: It is imperative to remember that MySQL user accounts are distinct and must be created separately from your mail or web administrator accounts. Additionally, a user must be created before you can assign them to an existing database.
To create a new MySQL user account, perform the following steps:
- Enter a desired username in the Username text box. For information on specific database username limits based on your database type, please refer to the details below:
- MySQL 5.6 and earlier: Database usernames are restricted to a maximum of 16 characters. This character count includes the database prefix, which typically consists of the first eight characters of the cPanel account’s username followed by an underscore (totaling nine characters). For example:
- A MySQL database with a
db_
prefix allows usernames of up to 13 characters. - A MySQL database with an
example_
prefix allows usernames of up to eight characters.
- A MySQL database with a
- MySQL 5.7 and later: Database usernames can be up to 32 characters long. The database prefix, which includes the first sixteen characters of the cPanel account’s username and an underscore (totaling seventeen characters), is counted towards this limit. For example:
- A MySQL database with a
db_
prefix allows usernames of up to 29 characters. - A MySQL database with an
example_
prefix allows usernames of up to 24 characters.
- A MySQL database with a
- MariaDB: MariaDB permits database usernames up to 47 characters in length. The database prefix, which comprises the entirety of the cPanel account’s username and an underscore character, is included in this character count. For example:
- A MariaDB database with a
db_
prefix allows usernames of up to 44 characters. - A MariaDB database with an
example_
prefix allows usernames of up to 39 characters.
- A MariaDB database with a
- MySQL 5.6 and earlier: Database usernames are restricted to a maximum of 16 characters. This character count includes the database prefix, which typically consists of the first eight characters of the cPanel account’s username followed by an underscore (totaling nine characters). For example:
- Enter and then re-enter the new password in the designated text boxes to confirm it.
Note: This password value corresponds to the user’s
DB_PASSWORD
. 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 assesses the strength of the password you enter on a scale of 0 to 100, where 0 indicates a very weak password and 100 signifies a highly secure one. Some web hosts may enforce a minimum password strength; a green Strength meter confirms that your password meets or exceeds the required security level. For assistance in generating a robust password, click Password Generator. More information is available in our Password & Security documentation. - Click Create User to finalize the user creation.
- Click Go Back to return to the main interface.
Add a User to a Database
Once you have created a MySQL user, you can grant them access to specific databases and define their permissions. This step is essential for controlling who can interact with your database resources and in what capacity.
- In the Add User To Database section of the interface, select the desired user from the first dropdown menu and the target database from the second.
- Click Add. The MySQL Account Maintenance interface will then appear, presenting a list of possible privileges.
- Select the checkboxes corresponding to each privilege you wish to grant to this user for the chosen database.
Note: To grant the user all available privileges on the database, simply select the ALL PRIVILEGES checkbox.
- Click Make Changes to apply the selected privileges.
- Click Go Back to return to the main interface.
For a comprehensive understanding of MySQL user privileges and their implications, we recommend consulting the official MySQL documentation.
Current Users
The Current Users table provides a centralized location to view all MySQL database users associated with your account and perform various administrative actions on them:
- Change Password: Click this option to modify an existing database user's password. You will be prompted to enter and confirm the new password, then click Change Password to update it.
- Rename: Use this option to change a database user's username. Enter the desired new username, and then click Change Username.
- Delete: This action allows you to permanently remove a database user from your system. After clicking Delete, you will need to confirm the action by clicking Delete User. Please exercise caution as this action is irreversible.