Page tree
Skip to end of metadata
Go to start of metadata

This document is for a previous release of cPanel & WHM. To view our latest documentation, visit our Home page.

For cPanel & WHM 11.46

(Home >> Databases >> MySQL Databases)

Overview

This interface allows you to create, manage, and delete MySQL® databases and database users.

Notes:

  • To perform these functions, some cPanel users may prefer to use the streamlined MySQL Database Wizard interface (Home >> Databases >> MySQL Database Wizard).
  • The maximum length of the database name is 64 characters. However, due to the method that cPanel & WHM uses to store MySQL database names, each underscore character requires two characters of that limit. Therefore, if you enable database prefixing, the maximum length of the database name is 63 characters, which includes both the database prefix and the underscore character. Each additional underscore requires another two characters of that limit.
  • To enter information in a database, use the phpMyAdmin interface (Home >> Databases >> phpMyAdmin).

Create a database

To create the database, perform the following steps:

  1. In the New Database text box, enter a name for the database.

  2. Click Create Database.
  3. Click Go Back. The new database will appear in the Current Databases table.

Modify Databases

Check a database

If you experience problems with a database on your server, check your databases for errors.

To check a database for errors, perform the following steps:

  1. In the Check DB menu, select the database that you wish to check.
  2. Click Check DB.
  3. cPanel will run a script that checks to ensure that the database is functional.
    • If cPanel detects a problem in the database, it displays the name of the corrupt table.
    • If the Check Complete message displays, that database is functional.

Repair a database

If one of your databases is corrupt, you will need to repair it.

To repair a database, perform the following steps:

  1. In the Repair DB menu, select the database that you wish to repair.
  2. Click Repair DB.
  3. cPanel will run a script that attempts to automatically repair the database.

 

Note:

This will only attempt to repair the database. The repair may not succeed. If it does not, cPanel will attempt to help you locate the source of the corrupt data.

Current Databases

This table contains a list of databases on your server.

For each database, the table displays the following information:

  • Database — The name of the database.
  • Size — The size of the database.
  • Privileged Users — Users who can manipulate the database.
  • Actions — Click the appropriate icon in this column to rename or delete a database.

Search databases

To search the Current Databases table, enter the desired criteria in the Search text box and click Go. The table will display the search results.

Modify a user's privileges

To modify a user’s privileges for database management, perform the following steps:

  1. Click the user’s name in the row that corresponds to the database for which you wish to modify privileges. The MySQL Account Maintenance interface will appear.
  2. Select the checkboxes that correspond to the privileges that you wish to grant to the user. Deselect any privileges that you do not wish to grant to the user.
  3. Click Make Changes.

Remove a user from a database

To remove a user from a specific database, perform the following steps:

  1. In the Privileged Users column, click next to the user that you wish to delete.
  2. If you are sure that you wish to remove the user from the database, click Revoke User Privileges.

Rename a database

Warning:

  • It is potentially dangerous to rename a MySQL database. We strongly recommend that you perform a backup of the MySQL database before you attempt to rename it.
  • If you rename a database, the system will terminate all active connections to the database.
  • You must manually update configuration files and applications to use the new database name.
  • The system requires more time to rename larger and more complex databases.

To rename a database, perform the following steps:

  1. In the Current Databases table, click the Delete icon in the Actions column that corresponds to the database that you wish to delete.
  2. Enter the new database name in the New name text box
  3. Click Proceed.

How cPanel renames a MySQL database

MySQL does not natively allow you to rename a database. When cPanel & WHM "renames" a database, the system performs the following steps:
  1. The system creates a new database.
  2. The system moves data from the old database to the new database.
  3. The system recreates grants and stored code in the new database.
  4. The system deletes the old database and its grants.

Warning:

  • If any of the first three steps fail, the system will return an error and attempt to restore the database's original state. If the restoration process fails, the API call’s error response will also describe these additional failures.
  • In rare cases, the system creates the second database successfully, but fails to delete the old database or grants. The system treats this as a success; however, the API call will return warnings that describe the failure to delete the old database or grants.

Delete a database

To delete a database, perform the following steps:

  1. In the Current Databases table, click the Delete icon in the Actions column that corresponds to the database that you wish to delete.
  2. If you are certain that you want to permanently delete the database, click Delete Database.

Add a MySQL user

After you create a database, add users to the database and configure their privileges.

Note:

You must create MySQL user accounts separately from mail and web administrator accounts. 

 

To create a new user account, perform the following steps:

  1. Enter a username in the Username text box.
  2. Enter and confirm the new password in the appropriate text boxes.

    Notes:

    • The system grades the password that you enter on a scale of 100 points. 0 indicates a weak password, while 100 indicates a very secure password.
    • Some web hosts require a minimum password strength. A green password Strength meter indicates that you met the required password strength.
    • Click Password Generator to generate a strong password. For more information, read our Password Generator documentation.

    Important:

    MySQL limits the database username to 16 characters. The system includes the database prefix in the character count for the username.

    For example:

    • A database with the db_ prefix would allow usernames up to 13 characters long.
    • A database with the example_ prefix would allow usernames up to eight characters long.
  3. Click Create User.

Add a user to a database

When you add a user to a database, you must also select that user's privileges. Privileges define how the user interacts with the database. For example, they determine whether the user can add and delete information.

To add a user to a database, perform the following steps:

  1. Select a user from the User menu.
  2. Select the database to which you wish to allow the user access from the Database menu.
  3. Click Add. The MySQL Account Maintenance interface will appear.
  4. Select the checkboxes that correspond to the privileges that you wish to grant to the user.

    Note:

    To grant all of the available privileges to the user, select the ALL PRIVILEGES checkbox. 

  5. Click Make Changes.

Current Users

The Current Users table lists all of your MySQL users. Use the functions in this table to change user passwords, usernames, and delete users.

Change MySQL user's password

To change a MySQL user's password, perform the following steps:

  1. In the Current Users table, click the Set Password icon in the Actions column that corresponds to the user that you wish to change.
  2. Enter and confirm the new password in the appropriate text boxes.

    Notes:

    • The system grades the password that you enter on a scale of 100 points. 0 indicates a weak password, while 100 indicates a very secure password.
    • Some web hosts require a minimum password strength. A green password Strength meter indicates that you met the required password strength.
    • Click Password Generator to generate a strong password. For more information, read our Password Generator documentation.

    Warning:

    The password must contain seven characters or less.

  3. Click Change Password.

Change a MySQL user's name

To change a MySQL user's username, perform the following steps:

  1. Click the Rename icon in the Actions column for the user who you wish to rename.
  2. Enter the new username in the New Username text box.
  3. Click Change Username.

Delete a MySQL user

To delete a MySQL user account, perform the following steps:

  1. Click the Delete icon in the row that corresponds to the user that you wish to delete.
  2. If you are sure you want to permanently delete the account, click Delete MySQL Users.