How to Import or Export Your MySQL Database?

Usually MySQL is imported for the following reasons:

• Switching from a web hosting provider to another
• Implementing a new third-party database
• Backing up your database

This article explores how you can switch, implement or backup a MySQL database.

To start, briefly we'll rediscover reasons to choose MySQL, challenges MySQL faces, and how we manage these challenges.

What is MySQL Database?

MySQL is an open-source software system that enables users to define, create, maintain and control access to the database (Connolly and Begg, 2014).

The best 5 reasons to choose MySQL database:

1. Safe money transactions
• Transactions in MySQL work as a grouped unit, and unless every operational function is successfully completed, a transaction will not clear. Meaning, if an operation fails at any stage, an entire transaction occurring within that group collapses.
2. Scale on-demand
• MySQL has complete customisation enabled. Everything from big data centers to an E-commerce business, MySQL is able to match the database needs.
3. High availability
• A reliable feature of MySQL is its consistent availability through its wide variety of configurations enabling instant failover or uninterrupted access.
4. Reliable
• MySQL supports SSH and SSL encryptions to ensure safer connections. Combined with restricted user access (even at the machine level) and a backup feature facilitating point-in-time recovery.
5. Quick-start capable
• From download to installation within 15 minutes. Implementation of this robust, free database is exceptionally quick, regardless of the underlying platform.

Challenges of MySQL and how to manage these challenges

Some of the challenges faced by MySQL:

• Development time
• Replication
• Database logging costs
• Query caches
• High connection churn

How to manage these challenges?

Implementation of database load balancing software. Enabled automatic read / write split so you can a host of advantages (includes: readable replicas, augment failover, analytical visibility, and one-click caching).

The benefit of implementing a database load balancing software is the ability to upgrade your apps quickly and remain up all year round.

How to export your MySQL database?

To export your MySQL database, use:

1. phpMyAdmin, or
2. 'mysqldump' database command line program.

Here's a quick note on how I've organised this guide:

• green highlight = an action you need to take
• blue highlight = location reference

If your choice to export your database is to use phpMyAdmin

This can be done through the phpMyAdmin web interface, and can be accomplished following the steps below:

1. Login to your cPanel.
2. Under the DATABASES section of the cPanel home screen, select phpMyAdmin.
3. At the left pane of your phpMyAdmin page, select the database that you want to export.
4. When you have selected the database you wish to export, click on the Export tab to begin the exporting process.
5. A prompt for you to choose an Export method will appear, confirm that Quick is selected.
6. Under Format, confirm that SQL is selected.
7. Select Go.
8. In the Save File dialog box, choose where on your computer you would like to save your exported database to.
9. Select Save, and the export process will start.

If your choice to export your database is to use the 'mysqldump database program'

1. Transfer the dbexport.sql file to your hosting account using SCP, SFTP, or FTP.
2. Login to your hosting account using SSH.
3. Change to the directory where you uploaded the dbexport.sql file.
4. Type the following command, and press Enter.
• [ mysql -u username -p dbname > dbexport.sql ]
5. Enter your password.
6. The dbexport.sql file now contains the data for the dbname database 
• (if your dbexport.sql file was saved on your servers download the file to your local computer).
7. Your export is complete.

How to import your MySQL Database

After you have created a new database in cPanel, and similar to importing your MySQL database you can import the contents of your exported database by using either:

1. phpMyAdmin, or
2. 'mysqldump' database command line program.

If your choice to import your database is to use phpMyAdmin

1. Login to your cPanel.
2. Under the DATABASES section of the cPanel home screen, select phpMyAdmin.
3. At the left pane of your phpMyAdmin page, select the database that you want to export.
4. When you have selected the database you wish to export, click on the Import tab to begin the exporting process.
5. Under File to Import, select Browse.
6. Choose your exported dbexport.sql file.
7. Select Go.
8. The import process has started and should show in your database.

If your choice to export your database is to use the 'mysqldump database program'

1. Transfer the dbexport.sql file to your hosting account using SCP, SFTP, or FTP.
2. Login to your hosting account using SSH.
3. Change to the directory where you uploaded the dbexport.sql file.
4. Type the following command, and press Enter.
• [ mysql -u username -p dbname < dbexport.sql ]
5. Enter your password.
6. The dbexport.sql file now contains the data for the dbname database 
• (if your dbexport.sql file was saved on your servers download the file to your local computer).

Further reading 

• For more information about the mysqldump database command line program.
• For more information about the mysql command line program.
• For more information about phpMyAdmin.

Find attractive packages on Malaysia's leading hosting provider here.

Posted in: mysql, Tips, Web Hosting