8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Articles » Mysql » Here

MySQL : Running Batch Scripts

Related articles.

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.