• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 297
  • Last Modified:

How to alter a table in all MySQL databases?

Experts,

I have multiple databases in my environment. Every user have one database with multiple tables.

1. Is this an effective solutions?
2. How to alter a table in all MySQL databases, if I need some changes?

Thanks.
0
jimmycdinata
Asked:
jimmycdinata
  • 2
1 Solution
 
wiredpeaCommented:
It can be effective. In my company we use it too and it works great because we can buy chepers hardware that one really big server.

http://dev.mysql.com/doc/refman/5.0/en/replication.html

But remember that you make one server a 'master' and do all updates / inserts / deletes there, you can do selects one any of them. So you actually need to do a lot more selects then other queries.
0
 
xtermCommented:
It's pretty much a no-brainer if you have PHP installed on your server.  The code below is for the CLI of PHP, however just remove the first line if you want to create a web page and run it from a browser instead.

Remember to fix the $sql= line to be the exact SQL you want to run in each database.
#!/usr/bin/php -q
<?php

$databases=array("database1","database2","databaseN");
$sql="ALTER TABLE xxxx etc."

$dbh=mysql_connect("database_host","username","password");
for ($i=0; $i<sizeof($databases); $i++) {
        mysql_select_db("$databases[$i]");
        mysql_query($sql,$dbh);
}

mysql_close($dbh);
exit;

?>

Open in new window

0
 
xtermCommented:
Sorry, I left the closing semicolon out on line #5.  It should read:

$sql="ALTER TABLE xxx etc.";

Obviously, your own query goes in there, not mine.
0
 
jimmycdinataAuthor Commented:
Thanks for pointing the solutions.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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