asked on

Batch: do query on 200+ DB's at once

I have 200+ mySQL databases, all with the same user privalages.

I want to perform two SQL queries on all of them at once either from a command line or using PHP.

I have a list of all DBs in a long list with one on each line.

db_sdfdsfd1
db_jhgdj34
db_xz45g
....

ALTER TABLE `xyz_Pages`
  DROP `showMenu`,
  DROP `showSitemap`,
  DROP `showSEO`;
 
ALTER TABLE `xyz_Pages`  
ADD  `showMenu` TINYINT NOT NULL DEFAULT '1',
ADD `showSitemap` TINYINT NOT NULL DEFAULT '1',
ADD `showSEO` TINYINT NOT NULL DEFAULT 1 ;

Open in new window

Tomas Helgi Johannsson

Hi!

You can use the TOAD for MySQL (http://www.toadsoft.com/toadmysql/Overview.htm) for that.
It has the feature to connect to N databases and issue the same SQL on every connection.

Regards,
Tomas Helgi

1eEurope

just use a readfile in php to go threw your databases in a foreach-loop. in the loop select the database (use myDatabase) and run the statements.

Ray Paseur

Back up all your data bases before you try anything!

Do you have the same user id and password and host for all the data bases?

Thanks, ~Ray

ASKER CERTIFIED SOLUTION

cr4ck3rj4ck

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Ray Paseur

@cr4ck3rj4ck: Just a thought, but when working with beginners, it's a good idea to remind them to test the return values from functions and print out error messages, etc. Otherwise you get a lot of "it didn't work" postings. ;-)

<?php // multiple db updates
 
// BASIC CONNECTION INFORMATION ASSUMES ALL DB ARE ON SAME SERVER
$db_host	= "localhost";
$db_user	= "your user id";
$db_word	= "your password";
 
// AN ARRAY OF DATA BASE NAMES - YOU MIGHT LOAD THIS BY READING IT FROM A FILE?
$db_names[]	= "db_sdfdsfd1";
$db_names[]	= "db_jhgdj34";
$db_names[]	= "db_xz45g";
 
// CONNECT TO THE DATA BASE SERVER
if (!$db_connection = mysql_connect("$db_host", "$db_user", "$db_word")) {
	$errmsg	= mysql_errno() . ' ' . mysql_error();
	echo "$errmsg";
	die();
}
 
// ITERATE OVER DATA BASE NAME LIST
foreach ($db_names as $db_name) {
 
// SELECT THE DATA BASE
	if (!$db_sel = mysql_select_db($db_name, $db_connection)) {
	 	$errmsg	= mysql_errno() . ' ' . mysql_error();
	 	echo "$errmsg";
	 	die();
	}
	
// EXECUTE THE QUERIES 
	$sql	= "ALTER TABLE `xyz_Pages` DROP `showMenu`, DROP `showSitemap`, DROP `showSEO` ";
	if (!$s	= mysql_query($sql)) { $error = mysql_error(); die("$error"); } 
	/* MORE QUERIES, AS REQUIRED */
}
?>

Open in new window