The developers used to deal with the MySQL databases. When they complete the development work, they need to move the database to the other server. Sometimes, you need to import a production database on the development server for adding/testing new functionalities. Reasons can be anything. The developers should be aware of how to export and import MySQL databases.
There are 2 ways to accomplish this task – using GUI tools and a command-line tool. You must know the GUI tools used to interact with the MySQL database. One of them is phpMyAdmin. The phpMyAdmin is an open-source administrative tool for MySQL. Though the intention of this article is to show MySQL commands for export and import of MySQL databases, it is worth showing the same process with phpMyAdmin also. Ultimately, users can decide on either option for their project.
Export and Import Database with phpMyAdmin
To get started, you first need to open your phpMyAdmin tool. On the local server, you can do it by visiting http://localhost/phpmyadmin. If you are trying to access it from a hosting account then you have to find this tool under the hosting CPanel. In some hosting environments, you might have a unique URL to access the phpMyAdmin.
Once you are in the phpMyAdmin, go to the database you want to export. On the top heading bar, you’d see the option ‘Export’. Click on it.
You will redirect to the next page. Choose either the Quick or Custom option and click on the ‘Go’ button.
The SQL file will be downloaded to your system. This SQL file contains all details of your exported database. It’s a dump version of your database.
Now to import this database, again go to the phpMyAdmin tool of the targeted machine. Go to the targeted database from phpMyAdmin. Then click on the ‘import’ option from the top bar.
On the next screen, browse the SQL file downloaded in the previous step and hit the ‘Go’ button.
The process of importing the database would start. You have to wait until the process is finished. It can take a while depending on your database size.
Export and Import Database using Command Line Tool
The phpMyAdmin is a nice tool for moving a database. However, sometimes you need to use a command-line tool to export and import a database. You may face problems with phpMyAdmin when you are dealing with a large database. The errors can be anything ranging from timeout to interruption in the process.
If you are a victim of this scenario, go for the command-line tool. It should solve your problem. All you need to do is run a few commands and you are done.
As an example, I will show you running MySQL commands on the local server. The same steps you can apply on your local machine or on the hosting environment.
Export MySQL Database
Open the command prompt on your machine and navigate the path where you have installed your MySQL server. In my case the path is D:\xampp\mysql\bin.
After this, using a mysqldump command one can export the database.
mysqldump -u [username] -p [database name] > EXPORT_DIRECTORY_PATH\[database name].sql
Replacing the placeholders, my actual command would be
mysqldump -u root -p my_old_db > C:\databases\my_old_db.sql
Where
root– The username of MySQL.my_old_db– The name of the database to be exported.C:\databases\my_old_db.sql– The path where I am exporting the database.
When you press Enter after writing a command, it asks for a password. Type a password or directly hit Enter if no password is set. It will then start the process of dumping a database and you will find the final SQL file on the given path.
Import MySQL Database
Next, to import the MySQL database command is as follows.
mysql -u [username] -p [database name] < PATH_OF_SQL_FILE
Here the [database name] is the targeted database where you want to import the SQL file(previously exported). So my command would become
mysql -u root -p my_new_db < C:\databases\my_old_db.sql
Hit the Enter and you should see the SQL file is imported in your new database which is my_new_db in my case. You can check it by directly going to the phpMyAdmin or inside the command-line tool.
To view the tables in the new database using a command prompt you have to run the below commands in a sequence.
mysql -u root -pUSE my_new_db;SHOW tables;
Check out the article Run MySQL Queries on Command Line Tool for better understanding.
Export and Import MySQL Table
Similar to the database, you can also export and import MySQL tables. When your database table is too large, it is wise to use a command-line tool to perform this task.
Export the MySQL table using the below command.
mysqldump -u [username] -p [database name] [table name] > EXPORT_DIRECTORY_PATH\[table name].sql
Considering this, I will export my database table ‘wp_users’ with the command
mysqldump -u root -p my_old_db wp_users > C:\databases\wp_users.sql
And finally, you can import the MySQL table through the command
mysql -u root -p my_new_db < C:\databases\wp_users.sql
We have covered how to export and import MySQL databases using the command line tool and phpMyAdmin. We also showed the export and import of MySQL tables. I hope you can use these steps in your project and accomplish the task in less time.
Related Articles
- How to Take Backup of Laravel Application
- Backup Laravel Application on Amazon S3
- How to Take Backup of MySQL Database using PHP
If you liked this article, then please subscribe to our YouTube Channel for video tutorials.