MySQL - Creating multiple tables in multiple databases

I was recently asked to help create multiple MySQL tables on a MySQL 4 server using a MySQL 5 client.

This post will show you how you can create multiple tables in multiple databases using our database of choice, MySQL.

Setup a shell script to execute the query. Assume that the first argument passed is the database, indicated by $db.

# CODE FOR synch_db.sh
db=$1;
mysql -uuser -ppass --host=192.168.0.1 -e 'CREATE TABLE IF NOT EXISTS '$db'.table_name ( id int(11) NOT NULL auto_increment, alias varchar(80) default NULL, tag varchar(80) default NULL, mapping int(1) default 0, PRIMARY KEY (id)) TYPE=MyISAM;'

mysql -uuser -ppass --host=192.168.0.1 -e 'CREATE TABLE IF NOT EXISTS '$db'.table_name2 ( id int(11) NOT NULL auto_increment, alias varchar(80) default NULL, tag varchar(80) default NULL, mapping int(1) default 0, PRIMARY KEY (id)) TYPE=MyISAM;'

Create a CSV file containing database names to which you want changes to be applied. Following is how our CSV looks like. In our case the CSV is semi-colon separated and the 8th field containing the database name.

domain.com;field1;field2;....field7;database_name

And finally we can query the CSV file, extract the database name and run our shell script to create tables across all databases.

cat merged.csv | cut -d ";" -f 8 | while read i ; do sh synch_db.sh $i; done;

Please, add your questions and comments.