How to Import and Export MySQL Databases Easily

Introduction

MySQL, one of the most popular database management systems, plays a critical role in storing, retrieving, and managing data. Whether you’re a seasoned developer or just starting, understanding how to import and export MySQL databases is essential. An example of this would be that your WordPress backup restore has failed so it requires manual intervention by uploading the database. This guide will walk you through the process step-by-step, ensuring you can import & export your database when needed.

What is MySQL?

MySQL is an open-source relational database management system that uses Structured Query Language (SQL) to manage its data. It is widely used for online applications and is an essential component of many web-based applications.

Preparing for Import and Export

Before diving into the technical process, it’s crucial that you have everything you need:

Access Requirements

• Shell Access (If using command line): Most import and export operations will require access to the command line. If you don’t have access to terminal inside cPanel then you will need to contact your hosting provider.
• Permissions & Credentials: Ensure you have the necessary permissions to access the database.If you are importing/exporting a WordPress database you can find database details inside the WP-Config.php file.

Tools and Resources

• phpMyAdmin: A popular tool for managing MySQL databases through a web interface.
• Terminal:A command-line interface you can enter commands and scripts to perform tasks

How To Export A MySQL Database

How To Export A MySQL Database Using phpMyAdmin

1. Login to your cPanel account. If you’re unsure of how to log in to cPanel please follow this article.
2. Scroll down until you see phpMyAdmin then select it.
3. Select the database you want to export from the left sidebar.
4. Select ‘Export’ in the top menu.
5. Choose the format and settings, We recommend keeping the SQL format for compatibility
6. Select ‘Export’.

How To Export A MySQL Database using Command Line

1. 1. Login to your cPanel account. If you’re unsure of how to log in to cPanel please follow this article.
   2. Access your server via SSH. If you are using cPanel you can use Terminal instead.
   3. Navigate to the directory where you want the exported file to be saved.
      *You can do this via the following command “cd directory_name”. We recommend that you export the database in the same folder to keep it organised so in this instance our command would be cd public_html/.
   4. In the terminal window type the mysqldump command: mysqldump -u username -p database_name > filename.sql
      *Replace username, database_name, and filename.sql with your MySQL username, the database name, and the desired filename.
      
      WordPress Users
      If you are using WordPress you can find this information in the wp-config.php file which will look like the following:// ** Database settings – You can get this info from your web host ** //
      /** The name of the database for WordPress */
      define( ‘DB_NAME’, ‘sharedwe_wp178’ );
      /** Database username */
      define( ‘DB_USER’, ‘sharedwe_wp178’ );
      /** Database password */
      define( ‘DB_PASSWORD’, ‘h!)24JlS1p’ );

Our command would be: mysqldump -u sharedwe_wp178 -p sharedwe_wp178 > exporteddatabase.sql

1. Enter your database password then press enter
2. Your database will now be exported.
   *You can check this by using the “ls” command which will list your files in that directory. You can also visit the file manager to find the exported file.

How To Import A MySQL Database

How To Import A MySQL Database Using Command Line

1. Login to your cPanel account. If you’re unsure of how to log in to cPanel please follow this article.
2. Make sure the .sql file you wish to import is accessible on the server.
   *You can do this inside cPanel by uploading the .SQL file via file manager. If you are unsure on how to do this please follow this article.
3. Once the file has been uploaded access your server via SSH. If you are using cPanel you can use Terminal instead.
4. Navigate to the directory where you have uploaded the .SQL file.
   *You can do this via the following command “cd directory_name”. In this instance our command would be cd public_html/.
5. In terminal type the mysql command:mysql -u username -p database_name < filename.sql
   *Replace username, database_name, and filename.sql with your MySQL username, the database name, and the desired filename.
   
   WordPress Users Only
   If you are using WordPress you can find this information in the wp-config.php file which will look like the following:// ** Database settings – You can get this info from your web host ** //
   /** The name of the database for WordPress */
   define( ‘DB_NAME’, ‘sharedwe_wp178’ );
   /** Database username */
   define( ‘DB_USER’, ‘sharedwe_wp178’ );
   /** Database password */
   define( ‘DB_PASSWORD’, ‘h!)24JlS1p’ );

Our command would be: mysql -u sharedwe_wp178 -p sharedwe_wp178 < exporteddatabase.sql

6. Enter your database password then press enter
7. Your database will now be imported.

How To Import A MySQL Database Using phpMyAdmin

1. Login to your cPanel account. If you’re unsure of how to log in to cPanel please follow this article.
2. Scroll down until you see phpMyAdmin then select it.
3. Select the database you want to import to from the left sidebar.
4. Select ‘Import’ in the top menu.
5. Upload your .sql file by selecting the “choose file button”
6. Select ‘Import’.
   
7. Your database should be successfully imported.
   *If you encounter the following error #1050 – Table ‘table_name’ already exists It means that your database contains a “create_table” statement(s) which will need to be removed, commented out or table dropped. A way to double-check this is by Selecting copy next to “SQL query:” and then pasting the copied content into a text file. It should output similar to the following:
   —- Database: `sharedwe_wp178`—- ————————————————————
   Table structure for table `wp83_commentmeta`–CREATE TABLE `wp83_commentmeta` ( `meta_id` bigint(20) UNSIGNED NOT NULL, `comment_id` bigint(20)Now we can confirm that it is trying to create a table, which is why it won’t import.The easiest way to fix this issue is to drop all the tables manually which can be done via the following steps:
8. Select the database you wish to delete tables from the left sidebar.
9. Select each table by selecting the tickbox.
   *You will need to check which ones have the create statement which can be done by opening the database in a text editor. If your database is a full export it may be easier to drop all the tables since there will be a create statement for each table exported.
10. Select the “with selected” dropdown then select drop
    
11. Select “Yes”
12. The tables will now be dropped.