Multi Server MySQL Client - ccql

A post from dbpandit

Another fruit from Github's basket!

ccql - What is it?

Answer: ccql is an abbreviation for "Concurrent client for MySQL". It connects to multiple MySQL servers at a time based on the provided list of hosts and execute given SQL query concurrently. Provides the consolidated result which saves quite a time if one has hundreds of MySQL servers and enable DBAs to take further actions without any pain of jumping onto each server.

How ccql helps?

Answer: Sometimes we look for a faster way of running SQL query/queries on the whole MySQL Cluster or just on some random nodes without wasting time jumping onto each one of them one by one in order to gather the output of some status or variable or just to verify record count of a table.
In my organization, We sometimes use Ansible in order to serve such purposes, however, ccql is even better if we stay specific to MySQL.

How does ccql work?

Answer: It connects to multiple MySQL servers at the same time with provided options & credentials (Obviously MySQL's credentials must be same for all) and runs given query. This saves quite a time and provides the consolidated result which can be further for other operations.

Download ccql binary from the following weblink:

Options can be used with ccql. Below mentioned options can also be found using ccql --help:

Usage of ccql:  
  -C string
        Credentials file, expecting [client] scope, with 'user', 'password' fields. Overrides -u and -p
  -H string
        Hosts file, hostname[:port] comma or space or newline delimited format. If not given, hosts read from stdin
  -Q string
        Query/queries input file
  -d string
        Default schema to use (default "information_schema")
  -h string
        Comma or space delimited list of hosts in hostname[:port] format. If not given, hosts read from stdin
        Display usage
  -m uint
        Max concurrent connections (default 32)
  -p string
        MySQL password
  -q string
        Query/queries to execute
  -t float
        Connect timeout seconds
  -u string
        MySQL username

It offers accurately required set of parameters which can be leveraged in many ways. Let's see a few examples here:
Illustration 1: A DBA wants to check what all of mysql slaves having read-only mode on.
Answer: If one plan to use ccql:

ccql -C /tmp/.mysql_credentials.cnf -h "dbhost04,dbhost05,dbhost06" -q "select @@read_only"  

In the above command:
-C takes mysql credentials file as input. That file must have section: [client] as mentioned below :


-h takes a list of hostnames separated by comma or space. Btw, the output of the above query will be like:

dbhost05:3306    1  
dbhost06:3306    0  
dbhost04:3306    0  

It signifies that we have read-only mode on at dbhost05 while disabled at other slave nodes.

Now, the question arises that what else can be done here. So what if we mix ccql with awk, voila!
Check this out, We can use above mentioned output for enabling read-only mode on dbhost06 and dbhost04, How? Have a look at the below command:

ccql -C /tmp/.mysql_credentials.cnf -h "dbhost04,dbhost05,dbhost06" -q "select @@read_only" | awk '$2==0 {print $1}' | ccql -C /tmp/.mysql_credentials.cnf -q "set global read_only=1"  

Note: Above command will enable read_only mode on slaves where it's not enabled already. There can be various scenarios where ccql can be used to save time :)

For more details refer below mentioned webpage: