How do I import an SQL file using the command line in MySQL?

ID : 302

viewed : 318

Tags : mysqlcommand-lineimportmysql





Top 5 Answer for How do I import an SQL file using the command line in MySQL?

vote vote

95

Try:

mysql -u username -p database_name < file.sql 

Check MySQL Options.

Note 1: It is better to use the full path of the SQL file file.sql.

Note 2: Use -R and --triggers to keep the routines and triggers of original database. They are not copied by default.

Note 3 You may have to create the (empty) database from MySQL if it doesn't exist already and the exported SQL don't contain CREATE DATABASE (exported with --no-create-db or -n option), before you can import it.

vote vote

82

A common use of mysqldump is for making a backup of an entire database:

mysqldump db_name > backup-file.sql 

You can load the dump file back into the server like this:

Unix

mysql db_name < backup-file.sql 

The same in the Windows command prompt:

mysql -p -u [user] [database] < backup-file.sql 

PowerShell

cmd.exe /c "mysql -u root -p db_name < backup-file.sql" 

MySQL command line

mysql> use db_name; mysql> source backup-file.sql; 
vote vote

71

Regarding the time taken for importing huge files: most importantly, it takes more time because the default setting of MySQL is autocommit = true. You must set that off before importing your file and then check how import works like a gem.

You just need to do the following thing:

mysql> use db_name;  mysql> SET autocommit=0 ; source the_sql_file.sql ; COMMIT ; 
vote vote

61

Among all the answers, for the problem above, this is the best one:

 mysql> use db_name;  mysql> source file_name.sql; 
vote vote

54

Easiest way to import into your schema:

Login to mysql and issue below mention commands.

mysql> use your_db_name;  mysql> source /opt/file.sql; 

Top 3 video Explaining How do I import an SQL file using the command line in MySQL?







Related QUESTION?