MySQL® Databases

Overview

This interface enables you to efficiently create, manage, and delete MySQL® databases and their associated users within your cPanel account. Understanding the naming conventions and limitations for databases is crucial for smooth operation.

A database name is subject to a maximum length of 64 characters. It is important to note the following specifics:

• Each underscore character (_) within a database name consumes two characters from this limit due to the internal storage methods employed by cPanel & WHM.
• If database prefixing is enabled by your hosting provider, the database name can have a maximum of 63 characters. This limit includes the database prefix and the mandatory underscore character. Each additional underscore will further reduce the available character count by two.

Note: To enforce a shorter, eight-character database prefix, your hosting provider can adjust the Force short prefix for MySQL and MariaDB databases setting, located in WHM’s Tweak Settings interface (WHM » Home » Server Configuration » Tweak Settings). For a comprehensive understanding of MySQL and MariaDB® prefix settings, 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 impose a restriction preventing the creation of new accounts where the first eight characters of their usernames are identical.

For operations such as adding or deleting data within existing databases, copying databases, or executing MySQL queries and scripts, utilize cPanel’s phpMyAdmin interface (cPanel » Home » Databases » phpMyAdmin). This powerful tool provides a web-based interface for database administration.

Warning: It is strongly advised not to use phpMyAdmin for creating new databases or database users. Databases and users created via phpMyAdmin do not properly map with cPanel's internal system, which can lead to critical issues with backups and restorations failing to function correctly.

Note: For users creating their initial database and user, we highly recommend using cPanel’s MySQL Database Wizard interface (cPanel » Home » Databases » MySQL Database Wizard). This wizard streamlines the process, ensuring proper setup and configuration.

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 new database.

   Note: If your hosting provider has activated database prefixing, an automatic prefix will appear before the New Database text box. This prefix is derived from your account username, followed by an underscore (_), and the system will automatically prepend it to your chosen database name.

2. Click the Create Database button to proceed. A confirmation interface will then be displayed.
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 it using the phpMyAdmin interface (cPanel » Home » Databases » phpMyAdmin). For further guidance on utilizing phpMyAdmin's features, please refer to the comprehensive documentation available on the phpMyAdmin website.

Remote MySQL Host

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

This area 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, enabling remote database access.

Modify Databases

If you encounter or suspect issues with any of your databases, cPanel provides tools to diagnose and potentially resolve these problems. It is always a good practice to regularly check your databases for errors to ensure data integrity.

Check a Database for Errors

To perform an error check on a specific database, execute the following steps:

1. From the Check Database menu, select the database you wish to examine.
2. Click the Check Database button. A new interface will appear, and the system will initiate a check to verify the database's functional integrity.
   • Should the system detect any problems within the database, it will indicate the name of the corrupted table.
   • If the message Check Complete is displayed, it confirms that the database is functioning correctly without errors.
3. Click Go Back to return to the primary interface.

Repair a Database

In instances where one of your databases becomes corrupted, the system offers a repair utility to attempt automatic restoration of its integrity.

To initiate a repair process for a database, follow these steps:

1. From the Repair Database menu, select the specific database that requires repair.
2. Click the Repair Database button. A new interface will load, and the system will commence its attempt to automatically repair the selected database.
   • If the system is unable to fully repair the database, it will endeavor to identify and report the source of the corrupt data, providing valuable diagnostic information.
   • If the message Repair Complete appears, it signifies that the database has been successfully repaired by the system.
3. Click Go Back to return to the main interface.

Current Databases

The Current Databases table provides a comprehensive listing of all databases associated with your account, detailing key information for each:

• Database: This column displays the full name of the database. This value also corresponds to the DB_HOST value, crucial for database connection strings.
• Size: This indicates the current storage size consumed by the database, measured in an appropriate unit (e.g., MB).
• Privileged Users: This lists the specific users who have been granted permissions to interact with and manipulate this particular database. These user names are also considered DB_USERNAME values.

  Note: When adjusting database user permissions, it is paramount to ensure that you are modifying access for the correct database. A single user may have access to multiple databases, so precision is key to maintaining security and functionality.

  • To remove a user's access from a database, locate the trash can icon next to the desired user, and then click Revoke User Privileges from Database.
  • To fine-tune a user’s specific privileges for a particular database, click on the username, then select or deselect the checkboxes to configure the appropriate permissions, and finally click Make Changes to apply your updates.
• Actions: This column presents the available management actions for each database. You can click the respective icon to either rename or delete a database.

Rename a Database

Warning: Renaming a MySQL database carries potential risks and should be approached with caution. We strongly recommend creating a comprehensive backup of your MySQL database using cPanel’s Backup interface (cPanel » Home » Files » Backup) before attempting any rename operation.

• When you rename a database, all active connections to that database will be immediately terminated.
• It is your responsibility to manually update any configuration files and applications that rely on the database to reflect its new name. Failure to do so will result in connection failures.
• The process of renaming larger and more complex databases naturally requires a longer duration to complete.

To change the name of an existing database, follow these steps:

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

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

1. A new database is first created with the specified new name.
2. All data from the original database is then meticulously moved and migrated to this newly created database.
3. Existing grants and stored code (such as stored procedures and functions) are meticulously recreated within the new database to ensure all functionalities are preserved.
4. Finally, the old database and its associated grants are securely deleted from the system.

Warning:

• If any of the initial three steps (creating the new database, moving data, or recreating grants/code) fail during this process, the system will return 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 system may successfully create the new database but fail to delete the original database or its grants. In such cases, the rename action is still considered a success, but the API function will issue warnings detailing the failure to remove the old database or grants, indicating that manual cleanup may be required.

Delete a Database

To permanently remove a database from your account, proceed with the following steps:

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

Add a MySQL User

After successfully creating a database, the next essential step is to add users to it and precisely configure their access privileges. This ensures that only authorized individuals or applications can interact with your database.

Note: It is crucial to remember that MySQL user accounts are distinct and must be created separately from email and web administrator accounts. Furthermore, a user account must be created before it can be assigned to an existing database.

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

1. Enter a unique username into the Username text box. For specific information regarding database username limitations, please consider your database type:
   • MySQL 5.6 and earlier: Database usernames are restricted to a maximum of 16 characters. This character count includes the database prefix, which typically comprises the first eight characters of the cPanel account’s username plus an underscore, totaling nine characters.
     • For example, a MySQL database with a db_ prefix allows usernames of up to 13 characters (16 - 3 prefix = 13).
     • Similarly, a MySQL database with an example_ prefix allows usernames of up to 8 characters (16 - 8 prefix = 8).
   • MySQL 5.7 and later: Database usernames are limited to 32 characters. The database prefix for these versions usually consists of the first sixteen characters of the cPanel account’s username plus an underscore, accounting for seventeen characters.
     • For example, a MySQL database with a db_ prefix allows usernames of up to 29 characters (32 - 3 prefix = 29).
     • Conversely, a MySQL database with an example_ prefix allows usernames of up to 24 characters (32 - 8 prefix = 24).
   • MariaDB: MariaDB database usernames can be up to 47 characters long. In this case, the database prefix includes the entirety of the cPanel account’s username followed by an underscore character.
     • For example, a MariaDB database with a db_ prefix allows usernames of up to 44 characters (47 - 3 prefix = 44).
     • Alternatively, a MariaDB database with an example_ prefix allows usernames of up to 39 characters (47 - 8 prefix = 39).
2. Enter the new password into the first password text box, and then confirm it by re-entering the exact same password in the confirmation text box.

   Note:

   • This entered value serves as the user’s DB_PASSWORD. However, cPanel & WHM does not store or reveal this value for security reasons. If the password is forgotten, it must be changed manually.
   • The system provides a visual strength meter, evaluating your password on a scale of 0 to 100 points, where 0 indicates a very weak password and 100 signifies an exceptionally secure one.
   • Certain web hosts may enforce a minimum password strength requirement. A green Strength meter indicates that your chosen password meets or exceeds this required level.
   • For enhanced security, click the Password Generator button to automatically create a strong, complex password. More information 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 primary interface.

Add a User to a Database

After creating both a database and a user, the next step is to link them by granting the user access to the specific database. This process defines which databases a user can interact with.

To assign a user to a database and configure their permissions, perform the following steps:

1. 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 dropdown menu.
2. Click the Add button. This action will navigate you to the MySQL Account Maintenance interface.
3. On the MySQL Account Maintenance interface, carefully select the checkboxes corresponding to the specific privileges you wish to grant to this user for the chosen database.

   Note: To assign all available privileges to the user, simply select the ALL PRIVILEGES checkbox. Exercise caution when granting all privileges, as this provides extensive control over the database.

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

For more in-depth information concerning various user privileges and their implications, consult the official MySQL documentation.

Current Users

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 desired password, then click Change Password to update it.
• Rename: Click this option to change the username of a database user. Enter the new desired username, and then click Change Username to apply the alteration.
• Delete: Click this option to permanently remove a database user. A confirmation step will require you to click Delete User again to finalize this irreversible action.