How to Import and Export Databases in MySQL or MariaDB Print

Here we can know how to Import and Export Databases in MySQL or MariaDB.

How can Export and Import database in MySQL

(A) Method-1: Use phpMyAdmin

1. Login in your Cpanel
2. Click the phpMyAdmin
   3. From the above image you can see that if you want to open Databases then click on Databases and if you want to Export or Import then you can click on those and follow the next step that are describe there.

   (B) Method-2: Use the mysql program

   1. If you can not Export or Import your sql file by the Method one then you can follow the below for Export or Import. Sometime it happens if there have a limit of max upload in Import option then you can use this mysql program.

      (B-1) Import

      1. Let dbfile.sql is the file that you want to import in a database. Transfer/Upload the dbfile.sql in the home directory of your File Manager of cpanel.
         
      2. Log in to your hosting account using SSH. For that follow the link of How to use SSH to access your account
      3. If you uploaded the dbfile.sql to your home directory, type cd ~. But if you upload any other place than you need to change to the directory where you uploaded the dbfile.sql.
         
      4. Type the following command, and then press Enter. Replace dbusername with your username of your database and replace databasename with the name of the database where you want to import the data of dbfile.sql. The database where you want to import the data of dbfile.sql should be new and no data present of dbfile.sql.
         
         

         mysql -u dbusername -p databasename < dbfile.sql​

      5. Type the password of your database where you want to import the data of dbfile.sql and then press Enter. If you now check the database of "databasename" than you find that it contain the data of the dbfile.sql. Most of the time there show some error for wrong username of database and password of the user of database. You can find the username of database and password of user of database from the database config file which is vary website to website where is located. For WordPress it is normally present in /home/username/public_html/wp-config.php.
         In the below we mention a full example of command for importing database by SSH.
         
         

         ComputerUser:~ user$
         ComputerUser:~ user$ ssh -p 7822 username@domainname.com
         ECDSA key fingerprint is SHA256:+lfyLZ831jn1hFz6ksTo0A2AcljtigYcMeVy4kb3bdo.
         Are you sure you want to continue connecting (yes/no/[fingerprint])? yes
         Warning: Permanently added '[domainname.com]:7822' (ECDSA) to the list of known hosts.
         username@domainname.com's password: 
          
         .....here some content.....
         
             are supported and installed on your system.
         perl: warning: Falling back to the standard locale ("C").
         username@hostname [~]# cd ~
         username@hostname [~]# mysql -u dbusername -p databasename < dbfile.sql
         Enter password: 
         username@hostname [~]# 
         

      (B-2) Export

      You can export a MySQL database from the command line using the mysqldump program. To do this, follow these steps:

      1. You need to log in to the the hosting account using SSH from where you want to export the dbfile.sql .
      2. Type the following command, and then press Enter.
         
      
      

      mysqldump -u dbusername -p databasename > dbfile.sql

      • dbusername is the username you can log in to the database with
      • databasename is the name of the database that will be exported
      • dbfile.sql is the file in the current directory that the output will be saved to
      
      Replace dbusername with your username of your database and replace databasename with the name of the database where you want to export the data of dbfile.sql.
      
      1. Type the password of your database from where you want to export the dbfile.sql
      2. The dbfile.sql file now contains all of the data for the database of databasename.
         
         

      How to create a new MySQL database

      If you have no empty database where you want to import than before import the database, you must create a new database in cPanel and assign a user to it. To do this, follow these steps:

      1. Log in to cPanel.
      2. In the DATABASES click MySQL Databases.
      3. Under Create New Database, in the New Database text box, type the name of the database.
      4. Click Create Database. cPanel creates the database.
      5. When the database is created, click Go Back.
      6. Under Add User to Database, in the User list box, select the user that you want to add. If you have no exsting user then you need to create the user first.
      7. In the Database list box, select the new database.
      8. Click Add.
      9. Select the check boxes to grant the user specific privileges, or select the ALL PRIVILEGES check box to grant the user all permissions to the database.
      10. Click Make Changes. cPanel adds the user to the database.