Scripts to download all media from a multisite wordpress install including different sizes of images generated by wordpress.

readme.md

Step1: First, update urls.sql.rb based on how many multisites you have. This generates a SQL to execute on your database. pbcopy copies the generated sql from terminal on mac.

ruby urls.sql.rb | pbcopy

Step2: Execute the sql on phpmyadmin or mysql command line, download or save ther result as urls.txt.

Then run ruby all_files.rb which generates the urls.txt. The reason we need all_files.rb is because it finds the variants of images, eg: for image.jpeg, the sql has image-150x150.jpeg, image-320x320.jpeg, etc which are individually printed in each line by the all_files.rb.

all_files.rb

require 'csv'

File.open "urls.txt", "w" do |outfile|
  CSV.foreach("sql.csv", headers: true) do |row|
    # row is an array representing the fields in the line
    php_str = row[1]

    base_url = row[0]
    filename = File.basename(row[0]).strip
    dir = base_url.sub(%r{[^/]+\z}, '')
    files = php_str.scan(/s:\d+:"file";s:\d+:"([^"]+)"/).flatten.each(&:strip!)

    urls = files.reject { |name| File.basename(name) == filename }.map { |name| dir + name }

    outfile.puts(row[0])
    urls.each { outfile.puts(it) }
  end
end

urls.sql.rb

sqls = []

sqls << <<~SITE1.strip
  -- Main site (ID 1)
  SELECT
    CONCAT(
      (SELECT option_value FROM wp_options WHERE option_name = 'siteurl'),
      '/wp-content/uploads/',
      pm.meta_value
    ) AS file_url,
    pm2.meta_value as metadata
  FROM wp_posts p
  JOIN wp_postmeta pm ON pm.post_id = p.ID AND pm.meta_key = '_wp_attached_file'
  JOIN wp_postmeta pm2 ON pm2.post_id = p.ID AND pm2.meta_key = '_wp_attachment_metadata'
  WHERE p.post_type = 'attachment'
SITE1

[2, 3, 4, 5, 6, 8].each do |i|
  sqls << <<~SITEI.strip
    -- Site #{i}
    SELECT
      CONCAT(
        (SELECT option_value FROM wp_#{i}_options WHERE option_name = 'siteurl'),
        '/wp-content/uploads/sites/#{i}/',
        pm.meta_value
      ) AS file_url,
      pm2.meta_value as metadata
    FROM wp_#{i}_posts p
    JOIN wp_#{i}_postmeta pm ON pm.post_id = p.ID AND pm.meta_key = '_wp_attached_file'
    JOIN wp_#{i}_postmeta pm2 ON pm2.post_id = p.ID AND pm2.meta_key = '_wp_attachment_metadata'
    WHERE p.post_type = 'attachment'
  SITEI
end

puts sqls.join("\n\nUNION ALL\n\n")