I recently had a task where I needed to export a specific table that was in a few hundred different databases. However, mysqldump does not have a way to specify that a specific table should be dumped out of every database. See the supported formats below:

mysqldump [options] db_name [tbl_name ...]
mysqldump [options] --databases db_name ...
mysqldump [options] --all-databases

I was hoping for a command like: mysqldump --all-databases 'table_name'.

mysqldump does have an --ignore-table option but in my case there were too many different tables to list and I didn’t want to go there.

My next thought was to build a quick PHP script that would loop through every database, check if the desired table exists and then mysqldump it. Before I had the chance to start on this approach I realized I could accomplish this with a one line shell command. The approach I took was the following:

mysql -s -N -e "select TABLE_SCHEMA from information_schema.tables where TABLE_NAME='users'" | xargs -I % sh -c 'mysqldump % users | mysql -uUSERNAME -pPASSWORD -hHOST %'

In the example above, I got a list of all databases (TABLE_SCHEMA) that contained a “users” table. I piped that output to xargs which runs mysqldump on the specific database and users table. Last I piped mysqldump to send the output to another server so that it could be imported in the same step.