Execute MySQL statements from a text file

Posted July 8th, 2009 in MySql (Updated July 14th, 2009)

It's possible to execute statements in MySQL from a text file from the command line or from the MySQL command line shell. This can be useful, for example, if you've saved data using mysqldump and need to load it back into the database.

Example SQL file

The text file must contain SQL statements for this to work, e.g.:

INSERT INTO mytable (name, value) VALUES ('foo', 'bar');

The example above inserts a single record, but the SQL file could have multiple SQL statements, separated by a semi-colon. These SQL statements can do any valid SQL: inserting, deleting, or updating records; creating, modifying or dropping tables; etc.

Executing the file from the command line

If the file is at e.g/ /tmp/myfile.sql you can load it into the "test" database using the "chris" username like so:

mysql -u chris -p test < /tmp/myfile.sql

The -p flag says you want to specify the password and it will ask for the password before the SQL file is executed.

Executing the file from within the MySQL command line shell

If you are already working from within the MySQL command line shell, you can execute the SQL file without exiting from the MySQL shell like so (note that the mysql> bit is the MySQL shell command prompt and you don't need to type that part in):

mysql> source /tmp/myfile.sql

You can also do it like this:

mysql> \. /tmp/myfile.sql

Note that this will only work from within the MySQL command line shell and not another client such as phpMyAdmin.