How Do I Create a MySQL Database, a User, and Then Delete if Needed?

 

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


Jump to a Section

This is a standard practice for anyone looking to manually install third party programs that make use of MySQL databases.


Create or Delete a MySQL Database

Create a MySQL Database

  1. Log into cPanel.
  2. Under Databases, click MySQL Databases.
  3. In the New Database field, type a name for the database.
  4. Click Create Database.
  5. Click Go Back.
    • The new database will appear in the Current Databases table.

Delete a MySQL Database

  1. Navigate to the Current Databases section of MySQL Databases.
  2. In the Actions column of the table, click Delete Database next to the database you wish to delete.
If you would like to remove a user that has been assigned to a database, click the Xnext to the user on this table. Please see the instructions below See Below on how to assign a database user.

Create or Delete a Database User

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.

Create a Database User

  1. Log into cPanel.
  2. Under Databases, click MySQL Databases.
  3. Click Jump to MySQL Users in the top-right corner of the page.
  4. Under Add New User, enter a username. Be sure that the username is 7 letters or shorter.
  5. Enter a password in the Password field.
    • For help generating a strong password, click the Generate Passwordbutton.
  6. Retype the password in the Password (Again) field.
  7. Click Create User.

Delete a Database User

  1. Navigate to the Current Users section of MySQL Databases.
  2. In the Delete column, click the X next to the user you wish to delete.
    Delete User from Database

Define a User's Privileges

Privileges determine how a user is able to interact with the database. For example, privileges will dictate whether or not the user can add and delete information.

Assign Privileges to Database User

  1. Log into cPanel.
  2. Under Databases, click MySQL Databases.
  3. Under Add User to Database, select a user from the User drop-down menu.
  4. From the Database drop-down menu, select the database to which you wish to allow the user access.
  5. Click Add.
  6. From the MySQL Account Maintenance screen, select the privileges you wish to grant the user, or select ALL PRIVILEGES.
  7. Click Make Changes.

Un-assign a User from a Database

  1. Navigate to the Current Databases section of MySQL Databases.
  2. Locate the database you wish to modify.
  3. In the Users column for that database, click the X next to the user you wish to delete. (You can see an example of this table by clicking here.)

Using the Database

Now that you can create databases and users and assign appropriate privileges, you can use the following articles as a reference to editing and connecting to your databases:

  • How to Copy or Rename a Database

Begin by accessing phpMyAdmin via cPanel or Plesk.

  1. Select the database you wish to copy (by clicking on the database from the phpMyAdmin home screen).
  2. Once inside the database, select the Operations tab.
  3. Scroll down to the section where it says "Copy database to:"
  4. Type in the name of the new database.
  5. Select "structure and data" to copy everything.  Alternately, you can select "Structure only" if you want the columns but not the data.
  6. Check the box "CREATE DATABASE before copying" to create a new database.
  7. Check the box "Add AUTO_INCREMENT value."
  8. Click on the Go button to proceed.

If you click on the home icon, you will notice your new database listed in the left column.  If you go inside, you will see the database is a copy of the previous database.

How to rename a database in phpMyAdmin

The latest version of phpMyAdmin allows you to change the name of the database.

Keep in mind that when you change the name of a database, you will have to reconfigure user permissions and any scripts that you want to continue referencing this database.

Before You Begin

For cPanel: If you want the same database user to have access to the renamed database, you should take note of the username already assigned to the database, which may or may not be similar to the name of the current database.

  1. In cPanel, click on MySQL Databases.
  2. Scroll down to the Current Databases section of the page.
  3. Find the database you are about to rename, and take note or remember the database user already assigned to the database.  You will need to know this for step 2.
  4. You can return to the cPanel home screen to begin Step 1.

Step 1: Renaming the Database

  1. From cPanel, click on phpMyAdmin. (It should open in a new tab.)
  2. Click on the database you wish to rename in the left hand column.
  3. Click on the Operations tab.
  4. Where it says "Rename database to:" enter the new database name.
  5. Click the Go button.
  6. When it asks you to want to create the new database and drop the old database, click OK to proceed. (This is a good time to make sure you spelled the new name correctly.)
  7. Once the operation is complete, click OK when asked if you want to reload the database.

Step 2: Reconfiguring User Permissions

If your hosting plan has cPanel, then you will need to reconfigure user permissions.

  1. Go back to the main cPanel page.
  2. Click on MySQL Databases.
  3. Scroll down to the Add User To Database section of the page.
  4. Select the database from the list (should be the new name).
  5. Select the database user from the list (the same one that used to be associated with this database).
  6. Click on Add button.
  7. On the Manage User Privileges page that appears, check the box next to All Privileges.
  8. Click on the Make Changes button.

Step 3: Update Scripts

You may need to update any scripts or applications that reference this database, since the name has changed, if you want them to continue to have access to this database.

There are many applications that can be installed on your computer for managing your databases. Here are some suggested applications you may use for that purpose. These applications are supplied by third parties.  Some are free and some require you to purchase a license.

The database management program (application) that you chose depends on what type of database you are connected to.  All our web hosting plans offer MySQL, and MS SQL is available on our Windows hosting.

For MySQL, if you do not want to use a third-party application, you can usephpMyAdmin in cPanel or Plesk to manage your database, which can be accessed with a web browser.

 


Step 1: Download the Application

MySQL (cPanel or Plesk)

Download one of these applications:

The applications above are designed for managing databases.  Some web development software, like Dreamweaver, can also connect to MySQL remotely.  Many other applications are available and can be found by using a search engine.

 


Step 2: Whitelisting Your IP

To connect remotely, you will need to whitelist your IP address.  You can get your IP address from: https://www.whatismyip.com/

cPanel

  1. Login to cPanel and click the Remote MySQL icon, under Databases.
  2. Type in the connecting IP address, and click the Add Host button.
  3. All done, try connecting remotely.

 


Step 3: Connecting

How you connect will depend on which program you pick.  The website where you downloaded the applications should have instructions on how to connect and use their application.

  • 16 Users Found This Useful
Was this answer helpful?

Related Articles

How to Move MySQL Datadir from the /root or / to /home on a Centos 7 server

Introduction   Running out of disk is typical for the primary filesystem or partition on a...