MySQL script files how-to

Uncategorized

What is a MySQL script file?.. Why use it?

A MySQL script file (aka MySQL batch file) is a regular text file containing MySQL statements separeted by terminantors, statements in a MySQL script file can be run from the MySQL client, this is very useful, many PHP script available online come with MySQL script files that need to be run as part of the PHP script installation process, when you have PHPMyAdmin (or some other tool) available, you can copy the contents of the MySQL script file then paste those statements into PHPMyAdmin and run the SQL queries, this works fine on local servers and/or for small MySQL script files, but if you have 20000 MySQL queries (maybe you are trying to restore your forum database), using copy/paste and PHPMyAdmin is a very bad idea.

Run MySQL script files from MySQL client

You can run MySQL script files from the MySQL client like this

mysql> SOURCE path_to_script_file;

or

mysql> . path_to_script_file;

path_to_script_file is an absolute or relative path (relative to the directory from which you invoked the MySQL client), examples:

mysql> SOURCE queries.sql;
mysql> SOURCE ./../sql/queries.sql;
mysql> SOURCE C:wampsqlqueries.sql;

— exactly the same as :

mysql> . queries.sql;
mysql> . ./../sql/queries.sql;
mysql> . C:wampsqlqueries.sql;

Run MySQL script files from the shell

You can execute a MySQL script file directly from the shell with the mysql command like this:


shell> mysql database_name < path_to_script_file
or
shell> mysql -f database_name < path_to_script_file
or
shell> mysql --force database_name < path_to_script_file

The -f (or –force) option tells mysql to continue the execution of upcoming queries even if it encounters errors, some examples:


shell> mysql database_name < script.sql
shell> mysql -f database_name < ../sql/script_with_errors.sql

Leave a Reply

Your email address will not be published. Required fields are marked *