MySQL : Running Batch Scripts

From the OS Command Line
From the MySQL Prompt
User-Defined Variables (Parameters)

From the OS Command Line

Create a script called "get_databases.sql" with the following contents.

SHOW DATABASES;

To run the script from the OS, simply redirect the script to the mysql client at the command line.

$ mysql --user=root --password -s < get_databases.sql
Enter password:
information_schema
mysql
performance_schema
$

To push the output to a file, use a redirect to the desired output file.

$ mysql --user=root --password -s < get_databases.sql > /tmp/output.txt
Enter password:
information_schema
mysql
performance_schema
$

As expected, the "output.txt" file contains the output from the script.

$ cat /tmp/output.txt
information_schema
mysql
performance_schema
$

From the MySQL Prompt

From the mysql prompt, you can run a script using the source command.

mysql> source get_databases.sql
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
+--------------------+
3 rows in set (0.00 sec)
mysql>

User-Defined Variables (Parameters)

Scripts can contain parameters, which must be set in a calling script or at the command line. Create a script called "get_tables.sql" with the following contents.

SELECT table_name
FROM information_schema.tables
WHERE table_schema = @schema;

From the mysql prompt, we can set the parameter value before running the script.

set @schema = 'mysql';
source get_tables.sql

The following output shows this in action.

mysql> set @schema = 'mysql';
Query OK, 0 rows affected (0.00 sec)
mysql> source get_tables.sql
+---------------------------+
| table_name |
+---------------------------+
| columns_priv |
| db |
| event |
| func |
| general_log |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| innodb_index_stats |
| innodb_table_stats |
| ndb_binlog_index |
| plugin |
| proc |
| procs_priv |
| proxies_priv |
| servers |
| slave_master_info |
| slave_relay_log_info |
| slave_worker_info |
| slow_log |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
+---------------------------+
28 rows in set (0.00 sec)
mysql>

For more information see:

Hope this helps. Regards Tim...

Back to the Top.

(追記) (追記ここまで)