How to alter a table in all MySQL databases?

Posted on 2011-10-24
Last Modified: 2012-05-12

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?

Question by:jimmycdinata
    LVL 2

    Expert Comment

    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.

    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.
    LVL 19

    Accepted Solution

    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
    $sql="ALTER TABLE xxxx etc."
    for ($i=0; $i<sizeof($databases); $i++) {

    Open in new window

    LVL 19

    Expert Comment

    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.
    LVL 2

    Author Closing Comment

    Thanks for pointing the solutions.

    Featured Post

    What Is Threat Intelligence?

    Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

    Join & Write a Comment

    I use MySQL for many of my development projects in a Windows environment. To manage my databases (and perform queries) for years I used a tool called MySQL administrator.  This tool has since been replaced by MySQL Workbench. So I decided to m…
    Foreword This is an old article.  Instead of using the MySQL extension that was used in the original code examples, please choose one of the currently supported database extensions instead.  More information is available here: MySQLi / PDO (http://…
    In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    729 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now