MySQL® Databases

Overview

This comprehensive interface empowers users to efficiently create, manage, and delete MySQL® databases and their associated users. Understanding the specific character limitations for database names is crucial for proper configuration.

A database name may contain a maximum of 64 characters. It is important to note the following when determining database name length:

• Due to the method that cPanel & WHM uses to store MySQL database names, each underscore character requires two characters of that limit.
• If you enable database prefixing, a database name may contain a maximum of 63 characters, which includes the database prefix and the underscore character. Each additional underscore requires another two characters of that limit.

Note: To limit the database prefix to eight characters, utilize the Force short prefix for MySQL and MariaDB databases setting within WHM’s Tweak Settings interface (WHM » Home » Server Configuration » Tweak Settings). For more detailed information on MySQL and MariaDB® prefix settings, refer to our 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 prevent you from creating new accounts that share the same first eight characters of their usernames. Exercise caution when enabling this setting.

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

Warning: It is strongly advised not to use phpMyAdmin for creating databases or database users. phpMyAdmin does not correctly map databases, which can lead to issues with backup and restoration processes. Always use the cPanel interface for creation.

Note: For users creating their initial database and user, we highly recommend utilizing cPanel’s MySQL Database Wizard interface (cPanel » Home » Databases » MySQL Database Wizard) for a streamlined setup process.

Create a Database

To successfully create a new MySQL database, follow these straightforward steps:

1. In the New Database text box, carefully enter the desired name for your database.

   Note: If your hosting provider has activated database prefixing, a prefix derived from your account username and an underscore character (_) will be displayed before the New Database text box. The system automatically prepends this prefix to the database name you enter.

2. Click Create Database. A new confirmation interface will appear, indicating the successful creation.
3. Click Go Back to return to the main interface. Your newly created database will now be listed in the Current Databases table.

To access and manage the databases you create, leverage the phpMyAdmin interface (cPanel » Home » Databases » phpMyAdmin). For in-depth guidance on using phpMyAdmin, please refer to the official phpMyAdmin website documentation.

Remote MySQL Host Configuration

Note: This section will only be visible if a remote MySQL configuration has been established by your hosting provider.

This section displays the configured address of the remote MySQL server provided by your hosting provider. This address is essential for establishing connections to the MySQL server from external applications or systems.

Modify and Maintain Databases

Ensuring the integrity and optimal performance of your databases is crucial. If you encounter any issues with a database, you can utilize the following tools to diagnose and potentially resolve problems.

Check a Database for Errors

To perform a diagnostic check on a database for potential errors, execute the following steps:

1. From the Check Database menu, select the specific database you wish to examine.
2. Click Check Database. A new interface will appear, and the system will initiate a check to verify the database's correct functionality.
   • If the system detects a problem within the database, it will clearly display the name of the corrupt table(s).
   • If the Check Complete message is displayed, it confirms that the database is functioning correctly without detected errors.
3. Click Go Back to return to the primary interface.

Repair a Corrupt Database

Should one of your databases become corrupt, cPanel provides a repair utility to attempt automatic restoration of its integrity.

1. In the Repair Database menu, select the database that requires repair.
2. Click Repair Database. A new interface will emerge, and the system will proceed with an attempt to automatically repair the selected database.
   • If the system is unable to repair the database automatically, it will endeavor to identify and report the source of the corrupt data.
   • If the Repair Complete message is displayed, it signifies that the system has successfully repaired the database.
3. Click Go Back to return to the main interface.

Current Databases Management

The Current Databases table offers a comprehensive overview of all MySQL databases associated with your account, presenting key information in an organized manner:

• Database — This column shows the name of the database, which also serves as the DB_HOST value for connection purposes.
• Size — Indicates the current storage size consumed by the database.
• Privileged Users — Lists the users who possess the necessary permissions to interact with and manipulate the database. These names correspond to the DB_USERNAME values.

  Note: When adjusting database user privileges, always ensure you are modifying access for the correct database. A single user might have access to multiple databases.

  • To remove 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 modify a user’s specific privileges for a particular database, click the desired username, then select or deselect the appropriate checkboxes to configure the required privileges, and finally click Make Changes.
• Actions — This column provides accessible actions for each database. Click the relevant icon to either rename or permanently delete a database.

Rename a Database

Warning: Renaming a MySQL database is an operation with potential risks. It is strongly recommended that you create a complete backup of the MySQL database using cPanel’s Backup interface (cPanel » Home » Files » Backup) before proceeding with a rename operation. Be aware of the following implications:

• When a database is renamed, all active connections to that database will be immediately terminated.
• You will be responsible for manually updating any configuration files and applications to reflect the new database name.
• The process of renaming larger and more complex databases may require a significant amount of time to complete.

To rename an existing database, follow these steps:

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

It's important to understand that MySQL itself does not natively support renaming a database directly. When cPanel & WHM performs a "rename" operation, it executes a series of intricate steps behind the scenes:

1. The system first creates an entirely new database with the specified new name.
2. All data from the old database is then meticulously moved and transferred to the newly created database.
3. Existing grants and stored code (such as procedures and functions) are recreated within the new database.
4. Finally, the original old database and its associated grants are securely deleted.

Warning: Be aware of potential issues during the renaming process:

• If any of the first three steps (creating the new database, moving data, or recreating grants) fail, the system will return an error and attempt to revert the database to its original state. Should the restoration process also fail, the API function's error response will provide details on these additional failures.
• In rare instances, the system may successfully create the second database but fail to delete the old database or its grants. In such cases, the system will still report the rename action as a success; however, the API function will issue warnings detailing the failure to delete the old database or grants.

Delete a 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 the permanent deletion of the database, click Delete Database in the confirmation prompt.
3. Click Go Back to return to the main interface after the deletion is complete.

Add a MySQL User

Once you have created your database, the next crucial step is to add users to it and configure their appropriate privileges. This ensures that applications and authorized personnel can interact with your database securely.

Note: It is important to distinguish between MySQL user accounts and mail or web administrator accounts; they must be created separately. Furthermore, you must create a user account before you can assign that user to an existing database.

To create a new MySQL user account, follow these instructions:

1. Enter the desired username in the Username text box. Understanding database username limitations is important, and these vary based on your MySQL/MariaDB version:

   MySQL 5.6 and Earlier Username Limits

   MySQL 5.6 restricts database usernames 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 utilizing the db_ prefix (which is 3 characters long) allows usernames containing up to 13 characters (16 minus 3).
   • A MySQL database with an example_ prefix (which is 8 characters long) allows usernames containing up to eight characters (16 minus 8).

   MySQL 5.7 and Later Username Limits

   MySQL 5.7 and newer versions enforce a database username limit of 32 characters. This character count includes the database prefix, which is typically derived from your cPanel account's username and an underscore. The prefix itself can be up to seventeen characters long (comprising the first sixteen characters of the cPanel username plus an underscore). The available characters for the custom part of your username will vary depending on the actual length of the applied prefix.

   For instance:

   • If a short prefix such as db_ (which is 3 characters long) is used, usernames can contain up to 29 characters (32 minus 3).
   • If a prefix like example_ (which is 8 characters long) is applied, usernames can accommodate up to 24 characters (32 minus 8).
   • Should the prefix utilize the full sixteen characters of your cPanel username plus an underscore (totaling 17 characters), the remaining space for the custom username would be 15 characters (32 minus 17).

   MariaDB Username Limits

   MariaDB limits database usernames to a generous 47 characters. This limit incorporates the database prefix, which consists of the entirety of the cPanel account’s username followed by an underscore character. Consequently:

   • A MariaDB database with the db_ prefix (which is 3 characters long) allows usernames containing up to 44 characters (47 minus 3).
   • A MariaDB database with an example_ prefix (which is 8 characters long) allows usernames containing up to 39 characters (47 minus 8).
2. Enter the new password in the first text box, and then re-enter it in the confirmation text box to ensure accuracy.

   Note: This password value corresponds to the user’s DB_PASSWORD. However, cPanel & WHM does not display this value after creation. If you forget it, you will need to change the user’s password through the _Current Users_ table.

   The system evaluates the strength of the password you enter on a scale of 1 to 100, where 0 signifies a weak password and 100 indicates a highly secure password. Be aware that some web hosts enforce a minimum password strength. A green Strength meter confirms that your chosen password meets or exceeds the required security level. For assistance in generating a robust password, click the Password Generator button. Further details can be found in our Password & Security documentation.

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

Add a User to a Database

After creating a MySQL user, you must grant them access to a specific database and define their permissions. This step is critical for database security and functionality.

1. In the Add User To Database section of the interface, select the desired user from the first menu and the target database from the second menu.
2. Click Add. This action will redirect you to the MySQL Account Maintenance interface, where you can configure privileges.
3. Carefully select the checkboxes that correspond to the specific privileges 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.

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

For more detailed information regarding MySQL user privileges and their implications, consult the official MySQL documentation.

Current Users Management

The Current Users table provides a comprehensive listing of all MySQL database users associated with your account, along with options to manage them effectively:

• 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 apply the changes.
• Rename — Use this option to change a database user’s username. Enter the new desired username, and then click Change Username to update it.
• Delete — Click this to permanently remove a database user. A confirmation prompt will appear; click Delete User to proceed with the permanent deletion.