Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 319
  • Last Modified:

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

0
hankknight
Asked:
hankknight
1 Solution
 
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
 
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

Featured Post

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now