Create MySQL Database and User via cPanel
The MySQL Database Wizard is another useful tool that allows you to create a MySQL database and user via a web interface. If you are about to install a PHP-MySQL application and you do not have a database already created for your script, this tool will guide you through all the steps needed to create such.
To access the MySQL database Wizard, please scroll to the Databases section in your cPanel and click on the MySQL Database Wizard icon.
Further reading:
Backup mysql database tutorial
Beginner Guide to cPanel Control Panel
CPanel Account Information Tutorial
Cannot See My Website Online After Upload, Why?
During the first step of your new database creation you will need to input the desired database name. Be advised that all cPanel databases are created with a prefix. This prefix represent your cPanel username and assures that your database name will be unique on the shared hosting environment even if you use a general database name such as test.
For example, if your cPanel user is "myuser" and you input "database" as your database name, the result will be a database called "myuser_database". You should take this under consideration when you configure your application or script to make sure that the correct settings are applied.
When you have the desired database name set, please click the Next Step button. During the next step of the process you will need to input your database username and password. This user will be the only one able to access the database. The username also follows the rule that its name is generated with a prefix, containing your cPanel username.
For example, if your cPanel user is "myuser" and you input "user" as your database user, the newly created database user will be "myuser_user".
To create the user click on the Create User button.
During the next step you should add privileges to your database user. Database user privileges sets the database actions that can be performed by this specific user. If you are about to setup and configure an application we recommend to apply All Privileges to make sure that your script will work properly.
When you are ready, click on the Next Step button on the bottom of the page. This will complete the process and you can now use your database name, user and password to install your application.
Further reading:
Custom error page is not working
How To Create, Edit, and Delete a File in CPanel Using File Manager
I have a full backup of account through cPanel. How do I restore it?
Manage MySQL Databases and Users
If you would like to delete your MySQL databases or add additional users, modify privileges or change your MySQL user password, the cPanel MySQL Database provides an user-friendly interface to do so.
To access the MySQL Databases tool in cPanel, please refer to the Databases section and click on the MySQL Databases icon.
Via the Add new User section you can create a new MySQL user to which can later add privileges to a specific database or a group of databases.
To add a user to a specific database or change an existing user password you may scroll to the bottom of the page and use the Add User to Database tool or the Current Users section to edit your existing users.
Remember to reconfigure any existing applications if you change your MySQL database user so they have the most recent logins configured. Otherwise, you application will not be able to establish a successful MySQL connection.
Further reading:
How to Use the Hosting Features in cPanel control Panel
How to check your PHP version and configure your PHP settings
How to force your website url with www or non-www using htaccess
How to optimize your website to use less server resources
How to upload files to a website
Overview of cPanel Paper Lantern Theme
Step by Step Approach to Password Protect a Directory in cPanel
Remote MySQL access
If you would like to have access to your database from a remote location or connect an application that is located on a different server to your hosting account databases you will need to use the Remote MySQL Access feature in your account cPanel.
The Remote MySQL Access tool is located under the Database section of your cPanel.
The Remote MySQL Access tool is pretty straight forward - you will need to simply add the IP address of the remote server or computer. If you would like to access your databases via your local computer, you should input your local IP address. You can find out what is your current local IP address by simply typing "What is my IP" into the Google Search box.
Just copy the IP address and add it to the allowed hosts.
If you would like to allow access from any IP address to your databases, you should input the wildcard sign - "%" - without the quotes. Click on the Add Host button when you have the desired host set in the field.
How to Delete a Database
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.
Further reading
Default Home Directory Folders
Getting Familiar With the cPanel User Environment
How Do I Create and Delete a Parked Domain
How do I create and remove an Addon Domain?
How to Backup My Website in cPanel
How to Create a SubDomain in Cpanel
How to Create a Website/Domain Redirect in cPanel
How to Reset or Change to a New CPanel Password
How to Set Up and Delete a Cron Job
How to Setup Google Apps MX, CNAME and SPF records
How to repair a broken database in CPanel
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.
Please back up any database on the account before making changes)
- Log in to your cPanel account.
- Under the Database Tools click on phpMyAdmin.
- On the left will be a list of databases. Select the one to repair/optimize.
- This will load all of the tables in your database into the right side. At the bottom of this list, check the "Check All" checkbox.
- Just to the right of the Check All link, select the "Repair" option in the drop-down list.
- This will list all the tables, with "OK" listed next to them. When this is done, look up to the top of the page and click on the "Structure" tab.
- Once the process has finished the page will revert back to step 4.
- Click "Check All" again, and then choose "Optimize" in the drop down menu.
Many MySQL errors can be fixed by this process. It is recommended to perform these steps frequently to keep any database in good condition.
If the error is not gone, please contact Todhost via ticket 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?
MySQL is only limited to the size of your cPanel's disk space.
MySQL User Has No Privileges
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
Uploading a Database
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
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?
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
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
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.
