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

LVL 16
hankknightAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Tomas Helgi JohannssonCommented:
    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
0
1eEuropeCommented:
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.
0
Ray PaseurCommented:
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
0
cr4ck3rj4ckCommented:
This will have a LONG execution time but essentially this should suit your needs.

Hope this helps.
	$dbConnect = mysql_pconnect($dbHost, $dbUser, $dbPass); 
	$dbList = file('DBList.txt');
	foreach ($dbList as $dbName)
	{
		mysql_select_db(trim($dbName), $dbConnect);
		$querySQL = "ALTER TABLE `xyz_Pages` DROP `showMenu`, DROP `showSitemap`, DROP `showSEO`";
		$executeSQL = mysql_query($querySQL);
		$querySQL = "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";
		$executeSQL = mysql_query($querySQL);
	}

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ray PaseurCommented:
@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

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.