Loading...

Knowledge Base
 Up to 70% off  on  WordPress  hosting for WordPress Websites and Stores!
Home 
 
/
 How to Import or Export a MySQL Database using phpMyAdmin
Categories: PHP, PHP MyAdmin

How to Import or Export a MySQL Database using phpMyAdmin

Knowing how to import or export a MySQL database using phpMyAdmin is crucial for any website owner or developer. phpMyAdmin is a popular web-based tool for managing MySQL databases. With just a few simple steps, you can easily import or export a MySQL database using phpMyAdmin. It also allows you to interact with databases easily and perform database operations such as creating, modifying, and deleting databases, tables, rows, and columns and executing SQL queries. 

These are some of the benefits of importing or exporting a MySQL database:

  • Backing up data: By exporting a MySQL database, you can create a backup of all your data, which can be used to restore the database in case of any data loss or corruption.
  • Migrating data: If you need to move your database from one server to another, you can export the database from the old server and import it into the new one.
  • Transferring data: If you want to share your database with someone else, export it and send them the file. They can then import the file into their own MySQL database.
  • Upgrading database: By exporting the MySQL database and importing it into a newer version, you can upgrade it to take advantage of new features and improvements.

With this easy step-by-step guide, learn how to import or export a MySQL database using phpMyAdmin.

Export a MySQL database using phpMyAdmin

Accessing phpMyAdmin

  1. Log in to your Bluehost Account Manager.
  2. Click the Hosting tab from the side navigation.
    Bluehost-account-manager-hosting
  3. Under Quick Links, click on cPanel.
    Bluehost-account-manager-hosting
  4. Navigate to the Databases section and select phpMyAdmin.
    Account-manager-php-my-admin

Export a MySQL database using phpMyAdmin

  1. Once inside, you will see a list of your databases on the left sidebar.

    Tip: You may expand the list by clicking the + button on the left.

  2. Select the name of the database you would like to export. 
  3. Click Export at the top.
  4. Select the Export method you wish to use. If you aren't sure, leave things as they are.
  5. Choose the file format in the drop-down box. 
  6. Click Go. This will download a .sql file to your computer.
    phpmyadmin-export

Import a MySQL database using phpMyAdmin

  1. Create a new database in cPanel.
  2. Navigate to the Databases section and select phpMyAdmin.
    Account-manager-php-my-admin
  3. Select the new database name to which you would like to import.
  4. Click the Import tab at the top menu.
  5. Select Choose File.
  6. Select the database file you imported earlier on your PC and click Go.
    phpmyadmin-import
  7. This will import your .sql file for you.

Note: The maximum size for a SQL upload via phpMyAdmin is 50MB. Split the database before importing or import via SSH. Please refer to Managing Databases with Command Line (SSH).

Common Issues

Your import file is too large! 

You will know this is the case when phpMyAdmin times out. (It will timeout after 30 seconds of attempting to import.)

If you experience this timeout, you must use an alternate method to import the database. Please see Importing Databases using Command Line(SSH) for more information.
For security reasons, it's best to upload your database to your account's/home/USERNAME section. (Replace USERNAME with your cPanel username.)

 

#1044 - Access denied for user 'username1'@'localhost' to database 'user2_wrdp9' 

The problem here is your import file contains an SQL query that attempts to create a database for the wrong username. Notice the user2 in 'user2_wrdp9' does not match the username1 in 'username1'@'localhost'. Someone must edit the import file and change the old user2 to your new username1. Here's an example of what it looks like (notice 3 places where the username is outdated):

Database: `user2_wrdp9`CREATE DATABASE `user2_wrdp9` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci;USE `user2_wrdp9`;

 

#1044 - Access denied for user 'username1'@'localhost' to database 'wrdp9' 

Same as the previous issue, the correct username is missing. On a shared server, your database names must always look like exampleuser_exampledatabase.

 

#1049 - Unknown database 'username1_wrdp9' 

The problem is your import file needs a query to create the database before importing the data. Go to cPanel > MySQL Databases and create a database with that name (in my example, "wrdp9"). Then, re-attempt your import.

 

#1007 - Can't create database 'username1_wrdp9'; database exists 

The problem is that your import file contains an SQL query attempting to create an existing database. If the database is empty, go to cPanel > MySQL Databases and remove that empty database; then re-attempt your import. If the database is not empty, someone must edit the import file and remove the CREATE DATABASE query. Here is an example of what it looks like:

CREATE DATABASE `username1_wrdp9` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci;/code>

 

#1064 - You have an error in your SQL syntax; check the manual corresponding to your MySQL server version for the right syntax to use near... 

The file does not contain backup data for a database, or the file has been changed or corrupted.

 

phpMyAdmin 404 Not Found 

This happens if you have logged into the cPanel from login.Bluehost.com. For PHP MyAdmin to work, you must log in from Bluehost.com or yourdomainname.com/cpanel.
If logging in from login.Bluehost.com because you are behind a firewall, you must contact your local network administrator to turn off the firewall or get ports 2082 and 2088 opened. You can check if this port is open by going to https://firewalltester.Bluehost.com/cgi-bin/firewall.

 

Less Common Issues

ERROR 1044 (42000): Access denied for user 'username1'@'localhost' to database 'username1_wrdp9' 

If you get this error, you do not have privileges on user_* to use Create. Bluehost will need to correct this issue for you. The problem here is your import file contains at least one SQL query that attempts to create a database, and you need the privilege to do so. Someone must edit the import file and remove the CREATE DATABASE query. Here is an example of what it looks like:

After removing this code, you must go to cPanel and click the MySQL Databases icon. You must create your database with the name removed from the import file (in my example, "wrdp9"). Then, you can Import the modified import file, which will work.

Summary

phpMyAdmin provides a simple and efficient way to import or export MySQL databases. Following the steps outlined in this guide, you can easily transfer your data between different applications or servers. Always back up your database before making any changes, and double-check your settings to ensure a successful transfer. With these tips in mind, you should be able to import or export your MySQL database using phpMyAdmin confidently.

If you need further assistance, feel free to contact us via Chat or Phone:

  • Chat Support - While on our website, you should see a CHAT bubble in the bottom right-hand corner of the page. Click anywhere on the bubble to begin a chat session.
  • Phone Support -
    • US: 888-401-4678
    • International: +1 801-765-9400

You may also refer to our Knowledge Base articles to help answer common questions and guide you through various setup, configuration, and troubleshooting steps.

Did you find this article helpful?

 
* Your feedback is too short

Loading...