How to export and import a .sql file from command line with options?

Question

Not Duplicate! looking for some feature have phpmyadmin during export in command line

I want to export and import a .sql file to and from a MySQL database from command line.

Is there any command to export .sql file in MySQL? Then how do I import it?

When doing the export/import, there may be constraints like enable/disable foreign key check or export only table structure.

Can we set those options with mysqldump?

some example of Options

Solution 1

Type the following command to import sql data file:

$ mysql -u username -p -h localhost DATA-BASE-NAME < data.sql

In this example, import 'data.sql' file into 'blog' database using vivek as username:

$ mysql -u vivek -p -h localhost blog < data.sql

If you have a dedicated database server, replace localhost hostname with with actual server name or IP address as follows:

$ mysql -u username -p -h 202.54.1.10 databasename < data.sql

To export a database, use the following:

mysqldump -u username -p databasename > filename.sql

Note the < and > symbols in each case.

Solution 2

If you're already running the SQL shell, you can use the source command to import data:

use databasename;
source data.sql;

Solution 3

mysqldump will not dump database events, triggers and routines unless explicitly stated when dumping individual databases;

mysqldump -uuser -p db_name --events --triggers --routines > db_name.sql

Solution 4

Well you can use below command to export,

mysqldump --databases --user=root --password your_db_name > export_into_db.sql

and the generated file will be available in the same directory where you had ran this command.

Now login to mysql using command,

mysql -u[username] -p

then use "source" command with the file path.

Solution 5

Dump an entire database to a file:

mysqldump -u USERNAME -p password DATABASENAME > FILENAME.sql

View more solutions

Comments

[AZinkey]

And How can we set constraints like enable/disable foreign key check or export only table structure with mysqldump

[Ondrej Burkert]

I ran into the ERROR 1227 (42000). Not sure what did you mean by add sed/awk.

[Sweet Chilly Philly]

Does this include Indicies?

[MNA]

Best option to import dumped data.if you have exported data using mysqldump then this option to import will work in seconds.

[apr]

simplest way to import. thanks

[iamafasha]

When you are exporting using mysqldump how do you add the port?