Running a Query on Multiple Databases

Our company uses a CMS that we developed in-house, and there are around 60 sites running the most current version of it. That means about 60 sites with mostly identical database structures.

When I make upgrades or bug fixes to the CMS, I commit them to a core repository which is inherited by all the sites. Very efficient, write once, use over and over and over again. Then if you need custom code for a single site, you can override it locally. So that’s great for the code, but the databases, though all pretty similar, are nonetheless each their own unique entity, running on our MySql server.

When I need to change the databases, I have to change all of them individually. What a chore. Shouldn’t there be a programmatic way to do that? PHP5, Cake framework, MySql, hit me up, let me know what I don’t know. But in the meanwhile, I needed to add a single record to the Permissions table of each and every database.

The query looks like this;

Ok, cool. It checks whether the permission exists in the permissions table, gets the id if it does, and adds a record to grant that permission to the admin (#2) group.

But I got to do this 60 times in a row. No way Jose. I asked my pal Matt to help out, he being more advanced in his career than I am. Matt kindly wrote me a nice syntactically-correct statement using the sp_msforeachdb command – then I pointed out that I’m using MySql and sp_msforeachdb doesn’t exists, nor does it have an equivalent that I know of.

These problems are so basic, I’m almost ashamed to even write about them. Does everyone make such elementary mistakes? I’m positive they do, but I must be the only one who blogs about it. Anyway, Matt’s advice was to burn our 13 years of legacy code and start again on Microsoft Server. Okay pal.

Here’s what I ended up doing:

script.php:


Then I ran the contents of output.txt against my database in PHP MyAdmin, and no I couldn’t use mysql_connect or any of the other excellent PHP MySql functions that exists, because some of the databases were on my list incorrectly, didn’t have the right tables and therefore caused the script to fail gracelessly.

Is there a better way? Will you tell me?

Would you like to receive updates by email?

Published by

Shannon Graham

Shannon has been writing brief essays and occasional how-to articles at Rocketships, Unaffiliated (.ca) whenever inspiration strikes, since 2012.

She is interested in your opinion.

8 thoughts on “Running a Query on Multiple Databases”

  1. Well there are systems out there that have migrations for databases that can be run with a little update script.
    If you don’t have these things, you might easily add it yourself.

    Just create a table that holds the current version number of the current database, and depending on this number the script might call migrating update scripts and incrementing the local version number.
    This way the script checks itself for the current database version, and manually updates everything needed.

    You can add it to the main software and run it on an update, therefore having a clean way for self updating software.

  2. Do it from the command line with bash:
    for i in $( cat file_with_databases_one_per_line.txt )
    do
    cat statement.sql | mysql -u user –password=password -h host $i
    done

    The statement.sql would not include the “Use database” part.

  3. Here is a python program that should do what you want.

    import MySQLdb, _mysql_exceptions

    #format of dblist items: sqlhost, sqluser, sqlpass, sqldbname)
    #sqlhost = ‘eg.localhost or ip’
    #sqluser = ‘username’
    #sqlpass = ‘password’
    #sqldb = ‘mydatabase’

    dblist = [(‘host1’, ‘user1’, ‘pass1’, ‘dbname1’),
    (‘host2’, ‘user2’, ‘pass2’, ‘dbname2’),
    (‘host3’, ‘user3’, ‘pass3’, ‘dbname3’),
    (‘host4’, ‘user4’, ‘pass4’, ‘dbname4’),
    ]

    for i in dblist:
    print(‘database being processed: %s’ % (i[3]) )
    #example connection: db = MySQLdb.connect(host=sqlhost, user=sqluser, passwd=sqlpass, db=sqldb)
    db = MySQLdb.connect(host=i[0], user=i[1], passwd=i[2], db=i[3]
    mysqlc = db.cursor()
    sqlstmt = “your sql stmt here that will be run for every database”
    mysqlc.execute(sqlstmt)

    #note, I don’t do anything with the _mysql_exceptions but it can be used to code for errors
    #mysql errors will cause this program to halt, the print message will tell you which db it halted on.
    #alternatively you could store your db data in mysql itself, or in a csv file and access the data from an external source instead of storing it in the program itself.

  4. here is the same thing using a csv file:

    import MySQLdb, _mysql_exceptions, csv

    #format of dblist/csv file items: sqlhost, sqluser, sqlpass, sqldbname)
    #sqlhost = ‘eg.localhost or ip’
    #sqluser = ‘username’
    #sqlpass = ‘password’
    #sqldb = ‘mydatabase’

    reader = csv.reader(file(‘multidb.csv’))
    dblist = []
    for l in reader:
    dblist.append(l) #read the data into a list

    for i in dblist:
    print(‘database being processed: %s’ % (i[3]) )
    #example connection: db = MySQLdb.connect(host=sqlhost, user=sqluser, passwd=sqlpass, db=sqldb)
    db = MySQLdb.connect(host=i[0], user=i[1], passwd=i[2], db=i[3]
    mysqlc = db.cursor()
    sqlstmt = “your sql stmt here that will be run for every database”
    mysqlc.execute(sqlstmt)

    #note, I do not do anything with the _mysql_exceptions but it can be used to code for errors
    #mysql errors will cause this program to halt, the print message will tell you which db it halted on.

  5. Here is an all MySQL way to do it via stored procedure. This is a bit simplified, but you should get the general idea of what you can do. In the long run I would suggest working to move all the common user auth and permission groups to a single database, especially if they are all for the same basic app.

    DROP PROCEDURE IF EXISTS fondle_databases;

    DELIMITER //

    CREATE PROCEDURE fondle_databases()
    BEGIN

    DECLARE vcDbName VARCHAR(100);

    — get all db names except for system schema
    CREATE TEMPORARY TABLE IF NOT EXISTS dbs AS (SELECT SCHEMA_NAME AS DbName FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME NOT IN (‘information_schema’, ‘mysql’, ‘performance_schema’));
    — or get just ones you want to target
    — CREATE TEMPORARY TABLE IF NOT EXISTS dbs AS (SELECT SCHEMA_NAME AS DbName FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME IN (‘list_target_dbnames’));

    — loop through each db name and do something

    — get first database name
    SET vcDbName := null;
    SELECT DbName INTO vcDbName FROM dbs LIMIT 1;

    WHILE vcDbName IS NOT NULL DO
    — do whatever needs to be done within the db as a prepared statement
    — you could also write as second stored proc passing db name as a param

    SET @vcQry := CONCAT(‘SELECT * FROM ‘, vcDbName, ‘.table_name;’);

    PREPARE stmt FROM @vcQry;

    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

    — get next database name
    DELETE FROM dbs WHERE DbName = vcDbName;
    SET vcDbName := NULL;
    SELECT DbName INTO vcDbName FROM dbs LIMIT 1;

    END WHILE;

    DROP TABLE dbs;

    END//

Leave a Reply

Your email address will not be published. Required fields are marked *