Update query acros 50 mysqls?

Discussion in 'MySQL' started by postcd, May 19, 2014.

0
  1. #1
    Hello,

    i need to update one field acros 50 mysqls, please can anyone advice me on how to do this?

    Is it wise to do all at once? The field content is like one sentence long.

    Thank you
     
    postcd, May 19, 2014 IP
  2. PoPSiCLe

    PoPSiCLe Illustrious Member

    Messages:
    4,623
    Likes Received:
    725
    Best Answers:
    152
    Trophy Points:
    470
    #2
    WTF is "50 mysqls" - databases? Tables?
     
    PoPSiCLe, May 19, 2014 IP
  3. postcd

    postcd Well-Known Member

    Messages:
    1,043
    Likes Received:
    9
    Best Answers:
    1
    Trophy Points:
    190
    #3
    They all are wordpress databases, so have same structure. so data i need to update are same.
     
    postcd, May 19, 2014 IP
  4. PoPSiCLe

    PoPSiCLe Illustrious Member

    Messages:
    4,623
    Likes Received:
    725
    Best Answers:
    152
    Trophy Points:
    470
    #4
    If the tables in the different databases are the same, why not just create a short php-file which loops through the databases and exchanges the database for each iteration?
     
    PoPSiCLe, May 19, 2014 IP
  5. sarahk

    sarahk iTamer Staff

    Messages:
    28,802
    Likes Received:
    4,534
    Best Answers:
    123
    Trophy Points:
    665
    #5
    Expanding on @PoPSiCLe's answer - when using MySQL you need to connect with a database before you can run your query (in this case the update). So you'd need a script that had the 50 database hosts, names, usernames and passwords and you'd need to connect to them one by one.
     
    sarahk, May 19, 2014 IP
  6. Nikolai Manek

    Nikolai Manek Peon

    Messages:
    3
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    1
    #6
    Are you somewhat familiar with a programming/scripting language? If you can create a Ruby script it would be very simple. Create an array with your database connection strings, loop over it and open new connections, execute the query, close it...repeat.. I just typed it up real quick. Did not test it as I don't have mysql but it should work...

    require "rubygems"
    require "mysql"


    mydbs = Hash.new{|h,k| h[k]=Hash.new(&h.default_proc) }

    mydbs[0] = ["hostname_here","username_here","password_here","database_name_here"]
    mydbs[1] = ["hostname_here","username_here","password_here","database_name_here"]
    mydbs[2] = ["hostname_here","username_here","password_here","database_name_here"]
    mydbs[3] = ["hostname_here","username_here","password_here","database_name_here"]
    mydbs[4] = ["hostname_here","username_here","password_here","database_name_here"]
    # add more with the correct values, all the way to 50...

    mydbs.each do |k,v|
    @db_host = v[0]
    @db_user = v[1]
    @db_pass = v[2]
    @db_name = v[3]

    client = Mysql::Client.new:)host => @db_host, :username => @db_user, :password => @db_pass, :database => @db_name)
    result = client.query("YOUR UPDATE QUERY HERE")
    puts "updated #{@db_name}" # so you can see that its still doing something..
    client.close
    end
     
    Nikolai Manek, May 19, 2014 IP
  7. Nikolai Manek

    Nikolai Manek Peon

    Messages:
    3
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    1
    #7
    oh sorry.. hash of hashes in this case (not array)
     
    Nikolai Manek, May 19, 2014 IP
  8. pmf123

    pmf123 Notable Member

    Messages:
    1,449
    Likes Received:
    81
    Best Answers:
    0
    Trophy Points:
    215
    #8
    are these databases all on the same server? if so, you can login once using the "admin" or "root" login to the mysql and make all the changes...

    you didn't specify what type of change.. is it adding a new table or field?
     
    pmf123, Aug 31, 2014 IP
  9. postcd

    postcd Well-Known Member

    Messages:
    1,043
    Likes Received:
    9
    Best Answers:
    1
    Trophy Points:
    190
    #9
    Yes, these mysqls are in one account and i have total root access (mysql root too) to the physical linux server...
     
    postcd, Sep 1, 2014 IP