Import/Export sql Dump into Mysql.

Hello Friends,

This is one of my tutorial related to MySQL database. While using MySql database you may have come across this issue to import your data from one place to another. But how to do that when the data we have is smaller in size but what when the data is heavy. In such case Mysql provides the option to export your data as a dump (.sql file) and then import the same data into new server or new system. The import and export of MySQL database not only is important to recover the data when disaster strikes, but also provides an easy way to migrate or move to another server, such as when switching web hosting providers.

1) To Export the data into dump file.:-

To export a database into a dump, use the following command.

Replace username with a valid username you have used to login to MySql, password with the valid password for the username (IMPORTANT: dont keep any white  space after -p and the password,else it will treat the password as database name, so the backup will fail ). And database_name with the name of the existing database of that you want to create a dump. Lastly any name you like for your output SQL dump file, here we use dumpfile.sql.

All the data, tables, structures and database will be backed up into a SQL text file named dumpfile.sql with the above command. The file will look like a simple txt file with .sql extension. This file contains the all the queries and data information that you are exporting, you can just check that file by opening it as a text file.

2) How to Export A MySQL Database Structures Only:-

In case when you want to create a new database similar to existing one but different data, i.e. all the tables and columns in the database will be same but with different data then you can try this. Simply add –no-data before the database name to export only the tables’ structures.

3) How to Backup Only Data of a MySQL Database

This is just opposite of the previous statements. If you only want the data to be backed up, use –no-create-info option. With this, the dump will not create the database, tables, fields, and other structures when importing but only the data.

4) How to Dump More than one MySQL Databases into a single Text File:-

When you have to create a single dump for several databases then use –databases option along with the export statement. This allows you to specify more than 1 database.

5) How to Dump All Databases in MySQL Server

We can also dump all the databases into a single file. To dump all databases, use the –all-databases option. And no databases’ name need to be specified into the statement, since it will automatically take all the present databases in the mysql srever.

6) How to Restore and Import MySQL Database

Now we will check how to import the exported databases into the mysql server. For this, upload the dump file to the MySQL server, and use the following command to import the databases back into the MySQL server.

Note:- Here note the symbols that we use for import(<) and Export (>).

Hope this will help you.

Thanks,

Nikhil.