all-schemas: Tool to run multiple SQL queries on a MySQL database server

Category:

Tutorials > Database Management Systems > MySQL > Installation

You have to run the job server first (click here for more info), then the worker and the client. Here is how you start the worker:

$ python worker_allschemas.py -g localhost:4730 -d

Make sure the worker is running by executing the following command:

ps aux | grep worker_allschemas.py

Here is the output of worker_allschemas.py with -h option for help.

  -h, --help            show this help message and exit
  -g GEARMAN_SERVERS [GEARMAN_SERVERS ...], --gearman-servers GEARMAN_SERVERS [GEARMAN_SERVERS ...]
                        Gearman servers list. Default: ['localhost:4730']
  -d, --daemonize       Daemonizes this worker to run as a deamon in the
                        background and perform tasks for clients. Default:
                        False
  --version             show program's version number and exit

Now, you can run SQL statements on databases of your choice on a MySQL server. Here are a few examples:

$ python client_allschemas.py --user=<db username> --password=<db user password> --host=<db host or IP address> \
-e "SHOW tables" -i mysql information_schema

Put the actual database username, password, and hostname/ip in the command above. The command will output a list of all tables in all databases except mysql and information_schema databases. The output on my machine looks something like below.

------------------t1 - Returned/Affected = 2/2 ------------------
Tables_in_t1
agg_sitelink_day
dahi

------------------test - Returned/Affected = 3/3 ------------------
Tables_in_test
agg_sitelink_day
dahi
t

Basically, there are two databases on MySQL server (except the ignored ones) ie. t1 and test. There are two tables agg_sitelink_day and dahi in t1 and three tables agg_sitelink_day, dahi, and t in database test. The Returned/Affected values display the rows returned (in case of SELECT and SHOW) and affected (in case of SELECT, UPDATE, DELETE) commands. You might see errors in the form of exception under some or all databases if the tables on which you run the query doest not exist in a particular database. For example, look at the output of the following command.

python2.7 client_allschemas.py --user=<db username> --password=<db user password> --host=<db host or IP address> \
 -e "SELECT * FROM dahi" "SELECT * FROM t" -i mysql information_schema t1 t2 t3 t4

Output:

------------------t5 - Returned/Affected = 1/1 ------------------
id
1

------------------test - Returned/Affected = 0/0 ------------------

------------------- t5 ------------------
<class '_mysql_exceptions.ProgrammingError'>

------------------test - Returned/Affected = 4/4 ------------------
id
None
None
None
shahryar

Notice the exception error in t5 database, that is because table t in second SQL does not exist in t5 database. all-schemas is a powerful tool and it will require more tutorials like this one to fully cover all its features and capabilities.

Did this tutorial help a little? How about buy me a cup of coffee?

Buy me a coffee at ko-fi.com

Please feel free to use the comments form below if you have any questions or need more explanation on anything. I do not guarantee a response.

IMPORTANT: You must thoroughy test any instructions on a production-like test environment first before trying anything on production systems. And, make sure it is tested for security, privacy, and safety. See our terms here.

Comments (write a comment):

0 comments so far. Be the first one to leave a comment on this article.

leave a comment on tutorial leave a comment