How to Create a MySQL Database, a User and Delete Database in Cpanel

Your cPanel allows you to easily create a MySQL database, create database users, and assign database user privileges. You can also delete databases and remove database users. This is a standard practice for anyone looking to manually install third party programs that make use of MySQL databases.

Also read: How to Reset or Change to a New CPanel Password

 

1. How to Create a MySQL Database

Create: If you are using any type of PHP software or keeping a collection of records which need to be accessed in some way, you will need to create a database. Below will show you how to create a MySQL database in cPanel.
   

  • Login to cPanel with the required information for authentication
  • Under the Database section in cPanel, click the MySQL Database icon.
  • Under "Create New Database" enter the name of the Database you wish to create.
  • Click the Create Database button.

Your database is now created. You will now need to create a user to access the database.

Also read: How to Set Up and Delete a Cron Job

 

2. Create a Database User and Assign Privileges

Whenever you have any type of MySQL database, you will need to assign a user to be able to access the database. For you to create a user and give them access to the database, please do the following:

  • Click MySQL Databases
  • Scroll down until you see "Add New Users".
  • Enter your database username.
  • Enter your database password.
  • Enter your database password one more time.
  • Click Create User.

You will need to save the database user password because you will later need it to connect to the database.

Also read: How to Setup Google Apps MX, CNAME and SPF records

 

2.1 Assigning the Database a User.

Now you need to allow user access to your database by granting access privileges. The following steps will show you how to do that:

    Under "Add User To Database" you will see two dropdown menus. These will reflect your existing Databases and Users.
    In the User drop down, Select the username you wish to assign to your Database.
    In the Database drop down, Select the Database you would like assigned to the username.
    Click "Add" Once you have selected desired Database and username to be assigned.

Please note that anytime you create a Database or Database Username it will always appear as your cPanel username and then the Database name or Database username

An example of this would be : if your cpanel username for your webhosting account is "cpaneluser" and the database and database user is dbuser, then it should be entered as: cpaneluser_dbuser This kind of information will be required when you are installing scripts like WordPress, Joomla, Magento or any other scripts which requires a connection to a database.

 

2.2 Assigning the user privileges.

    Click "ALL PRIVILEGES" to select all of the required Database privileges.
    Click "Make Changes" to finalize the changes and add the user to the database.

 

 3. Delete a Database

Delete : If you are deleting a script or would like to restart a new database, the first step is to delete the old MySQL database. To do so, please do the following :

    Click MySQL Databases
    Under Current Databases, you will find a list of all your current databases.
    Click "Delete Database" next to the name of the database you would like to delete.

You have now deleted a MySQL Database.

 

How to repair a broken database in CPanel

You could have a problem of MySQL queries or tables stop working

If you get an error saying, "..........marked as crashed and needs to be repaired," you can easily fix this in cPanel.

Even if you are getting a different kind of error like "supplied argument is not a valid" or "table attribute does not exist", you can also use this test to try and fix the problem.

Please follow the proceedure below to fix a database error.

    Login to cPanel and click MySQL Databases.
    Under Modify Databases, look for the drop-down called "Check DB".
    Select the database name which is having trouble.
    Click the Check DB button.

This should reveal the cause of the problem. If the problem is "marked as crashed", then proceed.

    Go back to MySQL Databases.
    Under Modify Databases, look for the drop-down called "Repair DB".
    Select the database name which is having trouble.
    Click the Repair DB button.
    Once complete, reload your web site.

If the error is not gone, please contact Todhost via ticket, phone, or email with the details.

 

Restore mysql database tutorial

This tutorial will take you through the process of restoring a adatabase you have already backed up using the phpmyadmin tool in cpanel,

To restore (import) a database via phpMyAdmin, first choose the database you'll be importing data into. This can be done from the corresponding menu on the left. Then click the Import tab:

You have the option of importing an .sql file. Choose the file from your local computer. Note that you are given the option to pick the character set of the file from the drop down-menu just below the upload box. If you are not certain about the character set your database is using just leave the default one.

In order to start the restore click on the Go button at the bottom-right. A notification will be displayed upon a successful database import.

 

Is There a Limit to MySQL Database Size?

There is no limit for the size of databases.
MySQL is only limited to the size of your cPanel's disk space.

 

How to Connect to the MySQL Database

 
For your MySQL database to work properly and as may be intended, it will need a script calling or querying the database for the information it stores. In order to do this, you must connect your script to the database with a configuration file.

If you are not sure where your configuration file is, check this list of different scripts to find the location of the configuration file. You will find the configuration files for the popular software in the following location:

 

Location of Configuration Files

These paths are relative to the root of the script, not the account nor domain. So if it's a vBulletin forum, under /home/username/www/forum/, the configuration file would be in /home/username/www/forum/includes/config.php
4Images Gallery /config.php
B2 Evolution /conf/_basic_config.php
Boonex Dolphin /inc/header.inc.php
Concrete5 /site/config.php
Coppermine Photo Gallery /include/config.inc.php
Crafty Syntax Live Help /config.php
Cube Cart /includes/global.inc.php
dotProject /includes/config.php
Drupal /sites/default/settings.php
e107 /e107_config.php
FAQMasterFlex /faq_config.php
Gallery /config.php
Geeklog /db-config.php
/siteconfig.php
/lib-common.php
glfusion /private/db-config.php
Hotaru /hotaru_settings.php
Joomla /configuration.php
LiveSite \local\config\lib\content_center\ livesite_customer_src\etc\conf\livesite_customer\database.properties
LifeType /config/config.properties.php
Magento /app/etc/local.xml
Mambo /configuration.php
MODx /core/config/config.inc.php
Moodle /config.php
MyBB /inc/config.php
Noahs Classifieds /app/config.php
Nucleus /config.php
ocPortal /info.php
osCommerce /includes/configure.php
/admin/includes/configure.php
PHP-Nuke /config.php
phpBB /config.php
phpFormGenerator /index.php
/mysql.class.php
phpFreeChat (only if you have saved form input to a database)
/forms/admin/config.inc.php
PHPlist /config/config.php
phpMyDirectory /defaults.php
phpWCMS /include/inc_conf/conf.inc.php
phpWebSite /conf/config.php
PhpWiki /admin.php
/lib/config.php
Pligg /libs/dbconnect.php
Post-Nuke /config.php
Saurus CMS /config.php
Siteframe /config.php
SMF /Settings.php
Soholaunch /sohoadmin/config/isp.conf.php
Textpattern /textpattern/config.php
Tiki /db/local.php
Trendy Site Builder (not applicable)
TYPO3 /typo3conf/localconf.php
vBulletin /includes/config.php
WebCalendar /includes/settings.php
WHMCS /configuration.php
WordPress /wp-config.php
X7 Chat /config.php
Xoops /mainfile.php
Zen Cart /includes/configure.php
/admin/includes/configure.php
Zikula /config.php


 

Configuration Settings

 
Database driven scripts, such as Joomla, WordPress, Drupal, WHMCS and Magento will generally have a default configuration file ready for you to edit with the appropriate information. Below you will find examples of what this access information will look like:

Host Name = localhost (literally input localhost)
Database Name = cpanelUsername_databaseName
Database Username = cpanelUsername_databaseUsername
Database Password = whatever you selected

WordPress Example
Username = joe1337     Database Name = wrdp1
Database-Username = wp1     Database-User Password = eHTb7%Pxa9

// ** MySQL Settings ** //
/** The name of the database for WordPress */
define('DB_NAME', 'joe1337_wrdp1');

/** MySQL database username */
define('DB_USER', 'joe1337_wp1');

/** MySQL database password */
define('DB_PASSWORD', 'eHTb7%Pxa9');

/** MySQL hostname */
define('DB_HOST', 'localhost');


While it is possible to connect to the database using your cPanel username and password, we do not recommend this because whenever you change or reset your cPanel password your databases will stop working until configuration files are updated.

    When you move databases to our servers, the database name and username may change. This change must be updated in your script code. (The database name and username is clearly displayed in cPanel, in the MySQL area.)

    On Shared servers, the database username cannot be changed to something without the cPanel username in it. This is to keep database names and usernames from conflicting with others on the server.

 

MySQL User Has No Privileges

You can have a situation where your MySQL user has no privileges. Connection to the database is stalled by such situation. You will have to correct this by doing the following:

    Go to your MySQL Databases in cPanel.
    Re-add the user to each database by selecting the user and the database from the drop-down menus.
    Reassign the privileges.
    Click Add.
Now you now how to connect to MySQL databases
 

 

How to Connect to the MySQL Database Remotely

 
To connect remotely to a MySQL database is often done to enable a program on your personal computer to access a database on the server.
If you are connecting from your home computer, you need a MySQL client like Navicat, phpMyAdmin, Workbench or Dreamweaver.

Configuration Settings

Use the following configuration settings for connecting to your database:

Host name = (use the server IP address)
Database name = (cpanelUsername_databaseName)
Database username = (cpanelUsername_databaseUsername)
Database password = (the password you entered for that database user)
MySQL Connection Port = 3306
TCP or UDP, either is fine.

    MySQL Connection Strings

A connection string is a sequence of variables which will address a specific database and allow you to connect your code to your MySQL database.

You need to have a database already created. If not, you can follow the following article on how to create one

Configuration

Once you have a database set up, create the database's tables via phpMyAdmin, MySQL software or use an online PHP or Perl script.

Use the following configuration settings:

    Version: MySQL 5 (or applicable version)
    Username: cpUsername_dbName
    Database Name: cpUsername_dbUsername
    Password: the password for cpUsername_dbUsername
    Hostaddress: localhost
    Port: 3306

Enable Your Computer as an Access Host

By default, all IPs are blocked and must be added to an access list in order to access the server. So, before connecting to MySQL from another computer, the connecting computer must be enabled as an Access Host.

If you do not know your IP address, find it by going to whatismyip.com

Dynamic IP Addresses

Having a dynamic IP address means that the connecting IP address can change periodically, depending on the Internet Service Provider (ISP). You must update the connecting IP in Remote MySQL every time it changes.
Many of our servers block port 3306 inbound. If you are getting a "Connection Refused" error when trying to connect, please contact our support department for immediate help so that we open port 3306 for your IP to be able to connect to MySQL remotely.

When Using cPanel X3 Theme

    Log into cPanel.
    Under Databases, click the Remote MySQL icon.
    In the Host field, type in the connecting IP address.
    Click Add Host.

You should now be able to connect remotely to your database.

Accessing Your Databases

After whitelisting your local IP, you should be able to make the proper remote MySQL connection. Be sure that the username you use matches the username created in the My SQL database section of cPanel. The most common problem in connecting is due to the use of an incorrect username and password.

Removing an Access Host

To remove a host from accessing databases on your server:

    In Remote MySQL, click the X icon next to the IP address of the user you wish to remove.
    If you are sure you wish to remove the IP address as an access host, click Remove Access Host.

This tutorial primarily focuses on database upload in cPanel. This will involve uploading from localhost or re-uploading a database you have backed up or downloaded.

This kind of activity is used in restoring a database backup or in duplicating a website. If you wish to.

 

Uploading a Database

There is a simple utility tool in cPanel which can be used to upload a database or restore downloaded database in cPanel. This tool is called phpMyAdmin. It is available under the database section in cPanel.


Follow this process to use the phpMyAdmin tool to upload a database in cPanel

1.    Login to your website cPanel environment. This ill usually require authentication with a username and password.
2.    Once you are successfully logged in, scroll down to the database section and click on the phpMyAdmin icon. This ill lead to a window where you will have all your databases displayed. Depending on the configuration of your cPanel and the version you are running, you will have all databases listed. In latest version of cPanel, you will find a button to click which will display all the databases on the website control panel.
3.    Click on the database you want to populate. From the top menu bar, click on “import”, locate your database file, usually an “sql” file and then you upload the file. Once it is done successfully, you will find the green bar indicating success.


The database you upload could have been downloaded from the web or saved from localhost.
Let’s briefly look at how to download a database from a live server.

1.    Login to the cPanel control panel.
2.    Scroll down to the database section and click phpMyAdmin.
3.    Click on the database you want to download. Scroll down to the bottom and click the check all button. From the options that follow, click “export” to begin download. You need not do anything else to download an “sql” file of your database.
4.     Click “Go” to download your database. The file will save on your local computer. By default, it will save in the downloads folder.

 

Downloading  a database from localhost

To download a database from localhost, let us assume you are using WampServer, you will follow these steps:
1.    Start WampServer on your local machine.
2.    Click on the phpMyAdmin tool
3.    Click on the database you wish to export.
4.    Scroll down to the bottom and check all the database tables.
5.    Click on the export button to begin download.
6.    Click “Go” to get started.

The database will be downloaded to your local machine and can be imported from there to a live server.

 

What if there be an error?

There are cases when you upload a database and all the tables are not uploaded or an error is reported. Normally, there will be an error message which should guide you on how to go about it.

As a  rule, you can try re-uploading your database, if the error continues, try to re-download your database. If the problem is not fixed, check your internet connection. Slow internet can create a problem for file upload.

To delete a database and re-upload, simply check all the tables again, this time, click on “Drop” and click the “Go” button.
Note, terminated databases cannot be recovered so make sure you have a rethink before you terminate. Infact, it is advisable you download to your local machine before you terminate your database.
 

 

How to Copy a Database Tutorial

Login to cPanel. You will do this by providing the required username and password for authentication.
  • Locate the Database Section with your cPanel control panel.
  • Click on phpMyAdmin. You will now be presented with all the databases listed on your account.
  • Check on the database whose table you want to copy. You may want to copy the entire tables or select the tables you want to create.
  • After checking your desired table, from the dropdown or the top bar menu, click Export. On the “Export” page, click “Go” to copy database to your local computer.

 

How to Import Your Database

You can import your copied database tables into an empty database. Just follow these steps to do that:

  • Login to cPanel
  • From the Database section, click on phpMyAdmin
  • Select the database into which you intend to import tables.
  • From the top menu bar in cPanel, click on IMPORT.
  • Locate the exported file, it should be an sql file, and clic “Go”
Your database will be uploaded and you will have a new set of database table.

 

How to Rename database tables

From the database section, you will find phpMyAdmin. Click phpMyAdmin.

Click on the database table you wish to rename

Now click the table you wish to rename

Then click the Operations button

Enter a new name for the table here

Then click Go

That is all. You now know how to copy a database table and how to recreate your database from exported file.

 

How to Upload a Database in cPanel

There is a simple utility tool in cPanel which can be used to upload a database or restore downloaded database in cPanel. This tool is called phpMyAdmin. It is available under the database section in cPanel.

Follow this process to use the phpMyAdmin tool to upload a database in cPanel

1.    Login to your website cPanel environment. This ill usually require authentication with a username and password.
2.    Once you are successfully logged in, scroll down to the database section and click on the phpMyAdmin icon. This ill lead to a window where you will have all your databases displayed. Depending on the configuration of your cPanel and the version you are running, you will have all databases listed. In latest version of cPanel, you will find a button to click which will display all the databases on the website control panel.
3.    Click on the database you want to populate. From the top menu bar, click on “import”, locate your database file, usually an “sql” file and then you upload the file. Once it is done successfully, you will find the green bar indicating success.


The database you upload could have been downloaded from the web or saved from localhost.
 
Let’s briefly look at how to download a database from a live server.

1.    Login to the cPanel control panel.
2.    Scroll down to the database section and click phpMyAdmin.
3.    Click on the database you want to download. Scroll down to the bottom and click the check all button. From the options that follow, click “export” to begin download. You need not do anything else to download an “sql” file of your database.
4.     Click “Go” to download your database. The file will save on your local computer. By default, it will save in the downloads folder.

 

Downloading  a database from localhost

To download a database from localhost, let us assume you are using WampServer, you will follow these steps:
1.    Start WampServer on your local machine.
2.    Click on the phpMyAdmin tool
3.    Click on the database you wish to export.
4.    Scroll down to the bottom and check all the database tables.
5.    Click on the export button to begin download.
6.    Click “Go” to get started.

The database will be downloaded to your local machine and can be imported from there to a live server.

 

What if there be an error?

There are cases when you upload a database and all the tables are not uploaded or an error is reported. Normally, there will be an error message which should guide you on how to go about it.

As a  rule, you can try re-uploading your database, if the error continues, try to re-download your database. If the problem is not fixed, check your internet connection. Slow internet can create a problem for file upload.

To delete a database and re-upload, simply check all the tables again, this time, click on “Drop” and click the “Go” button.
Note, terminated databases cannot be recovered so make sure you have a rethink before you terminate. Infact, it is advisable you download to your local machine before you terminate your database.

That will be all. Was this tutorial useful, please let us know by clicking one of the feedback buttons below.
  • 37 Users Found This Useful
Was this answer helpful?

Related Articles

I have a full backup of account through cPanel. How do I restore it?

It is recommended that you maintain healthy backup of your website and possibly download the...

How To Create, Edit, and Delete a File in CPanel Using File Manager

This tutorial will be useful for you to understand how to create a file, edit a file or delete...

How do I create and remove an Addon Domain?

An add-on domain is a separate domain name that you add to your web hosting subscription with...

How to Set Up and Delete a Cron Job

A cron job allows you to run a certain command at times set by the job. For example, you could...

How Do I Create and Delete a Parked Domain

Parked domains are additional domains which you host on your account. They will display the same...

Powered by WHMCompleteSolution