CODEWITHSUNDEEP
mysqlsqlwordpressreplace
user1883682
user1883682

Reputation: 31

How can I run an sql command on all databases at once?

I have circa 80 wordpress blogs, which write about my various sites. I want to update all posts in all databases for a specific string of text, in this instance, a domain name.

The script works perfectly fine, on one database, but I will need to make several changes, to ALL databases which will simply take far too long.

I need to be able to run these commands across every database at once, rather than one database at a time.

My script, as it works currently on a single database:

UPDATE wp_posts SET post_content = REPLACE(post_content, 'http://www.old-domain.com', 'http://mixudo.com');

Many thanks

Upvotes: 3

Views: 3313

Answers (3)

joseantgv
joseantgv

Reputation: 2023

Try executing this query:

SELECT CONCAT('UPDATE ', 
schema_name, '.\'wp_posts\' SET \'post_content\' = REPLACE(post_content, \'http://www.old-domain.com\', \'http://mixudo.com\');')
FROM information_schema.schemata
WHERE schema_name NOT IN ('information_schema','mysql','performance_schema','phpmyadmin', 'webauth');

and then reexecuting the result.

Source: https://dba.stackexchange.com/a/20251

Upvotes: 1

gview
gview

Reputation: 15411

You can get a list of databases on a mysql server using the SHOW DATABASES command. Using that as input you can write a shell script in whatever language you are capable that will foreach() through the list of databases, and running the UPDATE statement on each, by executing 

use DBNAME;

Where DBNAME is the variable in the foreach. The update statement should work the same on all the databases, as I assume each db has the same set of tables with the same tablenames in each.

To Ed Heal's point, this also assumes you're using the root/admin user for the server so that it will have rights to update all the tables.

Upvotes: 0

Ed Heal
Ed Heal

Reputation: 60037

  1. Create a list of database names with usernames/passwords that you need the script to be executed on
  2. For each database in this list do:
  3. Log into that database and select it
  4. Run the script on that database

That is the only way to do your task safely.

Upvotes: 0

Related Questions