MySQL Databases – How To Create, Import & Export Through cPanel
Creating a MySQL Database
1. Log into your cPanel account and go to the MySQL Databases section
2. In the Create New Database field, type in the name of your new database and click Create Database
3. In order for it to be functional, you must also create a user and assign this user with “ALL” privileges to the database
Database Settings & Third Party Software
There are a few common, yet specific, settings that need to be in place for your config files when software is connecting to a database. The most common are:
- Database Name
In most cases, the hostname will be ‘localhost’, meaning that the software is going to connect ‘locally’ — to a database on the same machine.
The database and usernames on a cPanel system will have two parts, your hosting account username and then the database name/username. So assuming your hosting account username is userna5 and you have a database named “forums” with a user called “admin,” your database name and username would be:
The password will be the password set for the corresponding database user.
PhpMyAdmin is an administrative interface to manage MySQL databases in cPanel. In order for this to work, you must have a database already created within cPanel.
To access phpMyAdmin:
1. Click the phpMyAdmin link in your cPanel (Clicking this link will open a new window)
2. Next, select a database (Once in phpMyAdmin, you’ll see a drop-down box on the left side where you can select a database. Doing this will display a list of tables in the database, and you’ll notice that next to each table name is a table icon, which allows you to edit specific tables.)
3. Click on a table icon to open the corresponding table in Edit Mode
(a) There there are the two basic functions that you can use:
i. Pencil icon – Will allow you edit the contents of a row in a table (used to change values)
ii. X – Will remove a particular row in a table
4. Once changes are made, click “Save” or “Go”
Importing a Database
You can use phpMyAdmin and sometimes even in your software’s admin panel itself to easily import a database.
There are currently two main requirements for importing a database with phpMyAdmin:
1. The tables must not already exist (phpMyAdmin will NOT let you overwrite tables)
2. The database must be under 50MB (If you have a database over 50MB, it’s best to upload your database backup to your account and contact your hosting company for further assistance)
If you want to replace an existing database:
1. You will need to “drop” the tables first if they already exist
(a) sometimes the easiest way to do this is to delete and recreate the database in cPanel
(b) or you can do this in phpMyAdmin
i. Selecting the database from the drop down menu
ii. In the right-hand frame, “Check All” and issue the command “With Selected: Drop” (This will remove all the checked tables from the database)
If importing the database through phpMyAdmin, there are two different ways to go about doing this depending on the version of software running on the server you are on.
1. Usually there will be an “Import” link at the top of the window which will allow you to upload a database file.
2. If not, there should be an “SQL” link with the same feature.
Doing either of these will automatically populate the database upon upload.
Some databases that are larger in size will need to be imported manually be a technical support representative from your hosting company due to limitations within PHP software.
Exporting a Database
When you exporting a database, you’re basically downloading selected tables into an SQL file. This can be easily done using the “Export” option in phpMyAdmin.
1. Click “Export” and select the tables that you wish to download,
2. Then “Save as File.” and “Go.” This will prompt you to download the SQL file.
If you need to connect to your database remotely, whether from another site or with software from your computer, you will need to give that host permission to access the database (This setting is not as commonly used with local software). This can be done using the Remote MySQL section of your cPanel.
The entries can be either hostnames or IP addresses. If there is no specific hostname or IP to connect from, a wildcard can be used instead. MySQL uses a percent sign (%) to denote a wildcard, similar to the Windows/Unix asterisk (*).
Below are examples:
% = Will allow all hosts and IP’s
205.134.252.% = Will allow all I’s starting with 205.134.252.
- PhpMyAdmin & cPanel: Optimizing a Database
- Mailing Lists: How To Install PhpList With CPanel & Fantastico
- Subdirectories – 3 Ways To Hide Them Through CPanel
- cPanel: Starting Up Your Hosting Control Panel
- SSH – How To Get Started
- 27 Users Found This Useful
Powered by WHMCompleteSolution