How to execute a SQL file using MySQL command line tool?

Question

I'm using symfony, putty and other SQL commands seem to work fine but when I try to run an .sql file (of which there are many), the command line throws an error:

You have an error in your SQL syntax; check the manual ...

This is the command I'm trying to execute:

mysql < data/MySQLFile.sql;

I already used the use command on the proper database and tried using the full path, relative path, etc. Not sure what the problem is.

Here's the statement:

ALTER TABLE tablename
    ADD COLUMN `blahblah` varchar(10) NULL;
ALTER TABLE tablename
    ADD COLUMN `ggggggg` varchar(50) NULL;

What about showing us the SQL statement that generates the error? We can't see the SQL file from here. — user330315
– user330315, Commented Jan 15, 2013 at 22:40
Yeah I posted the sql statement that generates the error it's mysql < data/MySQLFile.sql; — John Smith
– John Smith, Commented Jan 15, 2013 at 22:57
No. That's not the statement. The statement that generates the error is somewhere in that SQL script. — user330315
– user330315, Commented Jan 15, 2013 at 22:59
The error message should tell you exactly which line of the file failed, and which part of the line had the problem. For example: ERROR 1064 (42000) at line 17 in file: 'MySQLFile.sql': You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '--I tried to do a comment but forgot the space after the dashes' at line 1 — Ike Walker
– Ike Walker, Commented Jan 16, 2013 at 0:04
Those two lines seem correct to me. Does that work when you run them standalone? Did you maybe forget a ; in the statement right before them? — user330315
– user330315, Commented Jan 16, 2013 at 7:57

NarasimhaTejaJ · Accepted Answer · 2015-12-14 09:38:07Z

14

if you want to execute through command line

mysql -u user -p < file.sql

and if you want to execute once logged in to mysql database. use any of the below commands.

source file.sql

or

\. file.sql

or

mysql db_name <file.sql

answered Dec 14, 2015 at 9:38

NarasimhaTejaJ

1,6062 gold badges13 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Mm-Art-In Over a year ago

After hours of searching this source file.sql was what I was looking for! Thank you!

Abraham · Accepted Answer · 2014-07-22 16:27:19Z

2

Ok if you are in the mysql interface already type in:

\. /full path/to/file.sql

What I meant with full path is that you need to specify according to the current OS file system the location of the file. Just by putting data/ is not enough. For example if you are using MAC OS it would be: From the terminal and assuming is in Documents:

mysql < /Users/<yourusername>/Documents/file.sql

If you are already logged in mysql then type in:

\. /Users/<yourusername>/Documents/file.sql

If my tips don't help you, I will recommend you to go to this page:

http://dev.mysql.com/doc/refman/5.0/en/mysql-batch-commands.html To Find out more about this.

answered Jul 22, 2014 at 16:27

Abraham

3313 silver badges8 bronze badges

Comments

datavoredan · Accepted Answer · 2015-03-01 23:23:48Z

1

I think you just need to type 'source' before the path to the sql file. So you would have

mysql < source [fullpath]/MySQLFile.sql;

answered Mar 1, 2015 at 23:23

datavoredan

3,8169 gold badges36 silver badges49 bronze badges

Collectives™ on Stack Overflow

How to execute a SQL file using MySQL command line tool?

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related