Iterate over multiple MySQL tables, export 1 table from each

Question

I have around 150 MySQL databases, I need to export 1 table from each of the databases.

Is this possible, username and password are identical for each DB.

Answer 1

I think in this case, iteration would be more appropriate (rather than recursion).

Answer 2

If you are on Linux, I'd suggest writing a simple bash script that cycles the 150 DB URLs and calls mysqldump on each one.

Answer 3

See link text, it generates meta data for all databases and all tables, you may be able to adapt it to export data for you. However this is in PHP and I am not certain of the language you wish to use..

Answer 4

I'm sure there's a more compact way to do it but this should work.

#!/bin/bash

mysql -B -e "show databases" | egrep -v "Database|information_schema" | while read db; 
do 
  echo "$db"; 
  mysqldump $db TableName > $db.sql
done

You may need to tweak the mysql and mysqldump calls depending on your connection information.