Loading...

Knowledge Base

How to Create or Delete a MySQL Database or User

Did you find this article helpful?
Copy Link

 
* Your feedback is too short
Share

 

Databases offer a method for easily managing large amounts of information over the web. They are necessary to run many web-based applications, such as bulletin boards, content management systems, and online retail shops.

Note: Resellers can create accounts with usernames up to 16 characters long. Please note that MySQL Databases and Usernames will only include the first 8 characters. For example:
  • cPanel username: lengthyusername
  • MySQL Database: lengthyu_wrdp1
  • MySQL Username: lengthyu_johndoe

This article discusses the different features of the MySQL Database. Please click the links below for the instructions.


This video will show you how effortless it is to create the database and the database user and assign that user to your new database. This is standard practice for anyone looking to install third-party programs that use MySQL databases manually.
 

The video above may look slightly different from your current cPanel layout. However, the functionality described should be the same.


Create or delete a MySQL database

How to create a MySQL database

  1. Log in to cPanel. There are two ways to access your cPanel.
    • Option 1: Access your cPanel directly.
    • Option 2: Access your cPanel via your Customer Portal.
      1. Log in to your Customer Portal.
      2. Click Hosting in the left-side menu.

        Customer Portal - Hosting menu

      3. If you have a single hosting package in your account, scroll down to the Quick Links section.


        If you have multiple hosting packages in your account, locate the hosting package you want to manage on the Hosting Packages page, then click its Manage button.

        Hosting tab - Manage button

      4. Under the Quick Links section, click the cPanel button.

        Hosting menu - Quick Links - Launch cPanel

  2. Within cPanel, go to the Databases section, then click MySQL Databases.

    cPanel MySQL Database icon

  3. In the New Database field, type a name for your database.

    Create Database

  4. Click Create Database.
  5. Click Go Back. The new database will appear in the Current Databases section.

How to delete a MySQL database

  1. Within cPanel, navigate to the Current Databases section of MySQL Databases.
  2. In the Actions column of the table, click Delete next to the database you wish to delete.
  3. Confirm that you wish to remove the database permanently.

    Delete Database


Create or delete a database user

What is a MySQL user, and why is it important?

The MySQL user is a record in the MySQL server created for authentication purposes. This is different from your usernames when logging into Windows or even in your cPanel/WHM. Setting up a MySQL user provides more security to your website's databases as you can assign permissions to each user. Aside from you, the website's owner, you do not wish to grant just any user access to your database.

After creating the database, you will need to create a user and assign privileges. Please note that MySQL user accounts must be created separately from mail and web administrator accounts.

How to create a database user

  1. Within cPanel, under the Databases section, click MySQL Databases.

    cPanel MySQL Database icon

  2. Click the Jump to MySQL Users link at the top-right corner of the page. You may also scroll a little bit down to the MySQL User section.
  3. Under Add New User, enter a username.

    MySQL Users Add New User

  4. Enter a password in the Password field.
    • To generate a strong password, click the Generate Password button.
  5. Once the password is confirmed, click on the Create User button.

How to delete a database user

  1. Within cPanel, navigate to the Current Users section of MySQL Databases.
  2. Locate the database user you wish to delete, then click its Delete icon.

    delete database user

  3. Click the Delete User icon to confirm the deletion.

    confirm deletion of database user

     


Define a user's privileges

What are user "privileges," and why is it important?

You need specific users to have permission to perform website management tasks, like running queries or modifying databases. This is when we grant privileges. Privileges determine how a user can interact with the database. For example, privileges will dictate whether or not the user can add and delete information.

How to assign privileges to a database user

  1. Within cPanel, under the Databases section, click MySQL Databases
  2. cPanel MySQL Database icon

  3. Under Add User to Database, select a user from the User dropdown menu.

    Add User To Database

  4. From the Database dropdown menu, select the database you wish to allow the user access to.
  5. Click Add.
  6. Select the privileges you wish to grant the user or select ALL PRIVILEGES on the next page.

    Add User To Database

  7. Click Make Changes, then click Go Back.

How to unassign a user from a database

  1. Within cPanel, navigate to the Current Databases section of MySQL Databases.
  2. Locate the database you wish to modify.
  3. In the Privileged Users column for that database, click the trash icon.

    Priveleged Users


Use SSH to delete a database, drop 1 or multiple users, and view and grant privileges

Now that you can create databases and users and assign appropriate privileges, you can use the following articles as references in editing and connecting to your databases.

How to delete a database without using cPanel

The steps below are done using SSH. If you know how to use it, follow these steps; however, if you need help, contact us via phone or chat for assistance.

  1. Make a backup of the database with SSH using the command line below. Use your cPanel password.
    username@domain [~] # mysqldump --password username_database > username_database.db  
    Enter password:  (cPanel Password)  
    username@domain [~] #  
    
  2. Next, use the DROP DATABASE command inside mysql to delete the database.
    mysql> SHOW DATABASES LIKE "username_database";
    +------------------------------+  
    | Database (username_database) |  
    +------------------------------+  
    | username_database            |  
    +------------------------------+ 
    1 row in set (0.02 sec)
    
    mysql> DROP DATABASE username_database;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> SHOW DATABASES LIKE "username_database"; 
    Empty set (0.02 sec)  
    
    mysql>
      

Go to the DROP DATABASE Statement link for further information on this command.

How to drop one or multiple users at a time

Using the MySQL statement, DROP USER allows you to remove user accounts and their privileges from the database.

Syntax:

DROP USER ‘user’@’host’;
  • User: The user account you want to drop.
  • Host: The host server name of the user account. Format: ‘user_name’@’host_name.’

Example:

DROP USER ‘snappy01’@’localhost’;

To DROP multiple user accounts, follow this format:

DROP USER ‘snappy01’@’localhost’, ‘snappy02’@’localhost’;

How to grant privileges in MySQL

To grant privileges to users in MySQL, you are first required to have CREATE USER and GRANT privileges.

Syntax:

GRANT permission1, permission2 ON database_name TO 'user'@'localhost';

Example:

GRANT SELECT, INSERT, DELETE ON example_db TO ‘snappy02’@’localhost’;  

How to view privileges in MySQL

To show existing users and their privileges in MySQL, run the command SHOW GRANT.

Syntax:

SHOW GRANTS FOR 'database_user'@'localhost';

Example:

SHOW GRANTS for ‘snappy02’@’localhost’;

Sample Output:

mysql> SHOW GRANTS FOR ‘snappy02’@'localhost';  
+------------------------------------------------------------------+  
| Grants for snappy02@localhost |  
+------------------------------------------------------------------+  
| GRANT USAGE ON *.* TO `snappy02`@`localhost` |  
| GRANT SELECT, INSERT, UPDATE ON `example_db`.* TO `snappy02`@`localhost` |

 

Did you find this article helpful?
Copy Link

 
* Your feedback is too short

Loading...