For most of the time, web-based database management control panel such as phpMyAdmin does the job well for importing and exporting backups. However, as your database grows larger and larger, it will reach to a point where it can no longer be done using any web-based control panels anymore.
For instance, our database has reached 500mb just a couple of months ago and phpMyAdmin would stop halfway when doing the job and showing a timeout message. It’s a standard for web server to stop loading a page and declare as timeout once it waited for a certain amount of time. You can of course change the web server configuration to make the timeout really really long but that is not practical and may break your web server.
A better way would be using the command line tools came together with MySQL. Let’s try to import a large database using the command line tool. The first thing you need to do is to open up the command prompt, or terminal. For Windows operating system, you need to access to the bin folder in your MySQL installation directory, for example:
Then, use the following command to import the SQL file to your database.
mysql -u username -p database_name < file.sql
Then, a message will pop out and ask for the database password. Insert the correct password and wait for a while until all the data is being loaded to the database. If you see your mysql directory is showing in the next line, it means the operation has be done. Your command prompt should look something like this:
Next, we will export our database using the command line tool. This time, we will be using the mysqldump utility. To export your database, type in this command line:
mysqldump -u username -p database_name > file.sql
If you don’t specify a target destination for the file, it will be saved to the bin folder, in this case, C:\xampp\mysql\bin.
That’s all! It’s very easy and quick once you’re familiar with the command line.