DO_SQL_COMMAND(1) User Contributed Perl Documentation DO_SQL_COMMAND(1) NAME do_sql_command.pl - a perl program to execute user requested SQL commands per each database and table SYNOPSIS do_sql_command.pl --options --arguments DESCRIPTION This program allows you to execute same command for any database.table specified on a command-line or listed in a file containing a list of databasenames. You can specify multiple commands which will be executed for each table in each database. You can omit specified tables or databases to work on. If you do not specify any --db option, a list of databases will be fetched from database host. If you do not specify any --table , list of tables will be fetched for each database from mysql host. The strong advantage of this program is to selectively include or exclude some databases or tables which you do or do not want to modify. Hence we offer --omit-(table|database) and --table and --db arguments. The program is clever enough to figure out what tables or databases are present on the system and operate on all of them, if no --db or --table was specified. Note that this program will take the advantage of ~/.my.cnf file, so you do not have to specify username, password, hostname, port on a commandline. Thus, this script you can use to: do command for 2 tables in all databases except databases foo and bar do command for all tables in all databases except databases foo and bar do command for all tables in databases foo and bar do command for all tables except table baz in databases foo and bar do command for all tables except table baz in all databases See example below. OPTIONS --------ddddbbbbssss----iiiinnnn----ffffiiiilllleeee====$$$$ffffiiiilllleeee Filename (might be specified with full path), which contains line by line list of databases, on which this script should perform (un)compression. If --dbs-in-file=- is pecified, list of databases is read from STDIN. --------hhhhoooosssstttt====$$$$hhhhoooossssttttnnnnaaaammmmeeee [default: 127.0.0.1] Hostname of the mysql host. Note that this program will take the advantage of ~/.my.cnf file. --------oooommmmiiiitttt----ddddaaaattttaaaabbbbaaaasssseeee Enumerate a list of databases we should not modify. --------oooommmmiiiitttt----ttttaaaabbbblllleeee Enumerate a list of tables we should not modify. This option is used to selectively exclude tables, which were specified using --table option or, more importantly, if --table was left out, the algorithm would perform on all tables present in a given database. --omit-table prevents this program from modifying such tables. --------ccccmmmmdddd====ssssoooommmmeeee ssssqqqqllll ccccoooommmmmmmmaaaannnndddd wwwwiiiitttthhhh $$$$vvvvaaaarrrr #### ccccoooommmmmmmmeeeennnntttt $$$$vvvvaaaarrrr Specify any SQL command you wish. After the `#' hash mark you can specify any comments, which might be logged in mysqld logfile along with your query and also will be visible in crash output from mysqld. Of course, your version of mysql must support them. I think it is since 4.0.x. What are those variables? They are internal variables in this perl program. If $user/$host/$port/$db/$table is present there, it will be replaced by do_sql_command.pl before it get's sent to mysqld. As you execute this program most probably from shell, you should use double quotes and protect the `$' sign with backslash. For example: --cmd="some sql command with \$var # comment \$var" --------ppppaaaasssssssswwwwoooorrrrdddd====$$$$ppppaaaasssssssswwwwoooorrrrdddd [default: ] Password to be used during sql connection. Note that this program will take the advantage of ~/.my.cnf file. --------ppppoooorrrrtttt====$$$$ppppoooorrrrtttt [default: 3306] Port number where mysqld is listening to our requests. --------ttttaaaabbbblllleeee====$$$$ttttaaaabbbblllleeeennnnaaaammmmeeee Enumerate a list of tables, for which we should perform specified SQL command. If no --table option is specified, the script will perform such command on all tables present in a given database. You can combine --table with --omit-table options. The script will ignore those tables specified via --omit-table although they were also specified using --table. --------uuuusssseeeerrrr====$$$$uuuusssseeeerrrr [default: ] Username to be used during sql connection. Note that this program will take the advantage of ~/.my.cnf file. --------vvvveeeerrrrbbbboooosssseeee Verbose output. Specifying this option more times increases verbosity. Maximum is currently 3, although you may specify it more times. --------ccccoooouuuunnnntttt====$$$$nnnnuuuummmmbbbbeeeerrrr [default: 1] How many time we should repeat every SQL command. Specify -1 if you want never ending loop. EXAMPLES do_sql_command.pl --db=db1 --db=db2 --table=table1 --table=table2 --cmd="show create table \$table" The command above will execute `SHOW CREATE TABLE $tablename' for table1 and table2 in databases db1 and db2. do_sql_command.pl --db=db1 --db=db2 --omit-table=table1 --cmd="show create table \$table" Execute `SHOW CREATE TABLE $tablename' for every table except table1 in databases db1 and db2. do_sql_command.pl --dbs-in-file=- --omit-table=table1 --cmd="show create table \$table #User: \$user Db: \$db Table: \$table" --no-show-sql-cmds < list.txt Execute same command, but read a list of databases from STDIN and do not show the SQL command we executed. You may then take advantage of this sql command being logged with the appended comment text. AUTHOR Martin Mokrejs http://www.natur.cuni.cz/~mmokrejs/mysql-tools