Selectively restoring WordPress posts from old SQL dump
I have a bunch of WordPress blogs sharing a database, which seemed like a good idea at the time. Now I have a bunch of posts that were overwritten at *some* time in the past by bad data, but my backups are good. My backup testing works perfectly well for complete replacement, but I don't want to do that - I want to cherry-pick for one blog of several, and only replace posts that existed in November with the same IDs in today's database, but leave everything else alone. Does anyone have any thoughts? Right now I am hoping not to have a couple of terminals open and try to manually run UPDATEs with copy-n-paste, because that seems rather error-prone. Thanks! William
What i have done, reload backup to a new mysql/postgres db (assuming a binary quick load backup) (delete what you don't want - optional) then export db using text insert ability (not a binary blob).[can't remember args for mysql/postgres off top of my head, but they are there] then run it as a script based import to DB (which takes longer, but....) no copy and paste, just direct the insert script to the db. You may have an issue if you attempt to over-write a key. (auto index key). You may want to also state the db you are using, hey maybe its msSQL :) -tl On Wed, Mar 29, 2017 at 11:24 PM, William Witteman via talk <talk@gtalug.org
wrote:
I have a bunch of WordPress blogs sharing a database, which seemed like a good idea at the time.
Now I have a bunch of posts that were overwritten at *some* time in the past by bad data, but my backups are good.
My backup testing works perfectly well for complete replacement, but I don't want to do that - I want to cherry-pick for one blog of several, and only replace posts that existed in November with the same IDs in today's database, but leave everything else alone.
Does anyone have any thoughts?
Right now I am hoping not to have a couple of terminals open and try to manually run UPDATEs with copy-n-paste, because that seems rather error-prone.
Thanks!
William --- Talk Mailing List talk@gtalug.org https://gtalug.org/mailman/listinfo/talk
William Witteman via talk wrote:
I have a bunch of WordPress blogs sharing a database, which seemed like a good idea at the time.
Now I have a bunch of posts that were overwritten at *some* time in the past by bad data, but my backups are good.
My backup testing works perfectly well for complete replacement, but I don't want to do that - I want to cherry-pick for one blog of several, and only replace posts that existed in November with the same IDs in today's database, but leave everything else alone.
Does anyone have any thoughts?
When I have to do a similar process (moving a WordPress site from staging to production), I use the plugin [WP Migrate DB][0]. The free version does exactly what it says. If you don't want to use that plugin. How is your data stored in the MySQL database? My assumption is it's a bunch of tables with different prefixes (i.e. site1_posts, site2_posts, etc). You can use these two commands: $ mysql -N information_schema -e "select table_name from tables where table_schema = 'change-me-to-database-name' and table_name like 'site1_%'" > site1_tables.txt $ mysqldump change-me-to-database-name `cat site`_tables.txt` > site1_dump.sql [0]: <https://en-ca.wordpress.org/plugins/wp-migrate-db/>
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 I would: 1) restore from a known good backup into a temporary DB and create a new WordPress instance for your target blog 2) replace the November posts, pages, comments and media files in the temporary instance with ones from the current instance (and also any posts, pages, comments and media added since the backup was taken) 3) swap the temporary instance for the current instance. That'll give you an independent blog with a separate database, leaving the others in the one database. That database will still contain the table with the corrupt data, but nothing will be using that. You may want to consider splitting your one database into multiples for the other blogs too. Each table name in the database should have a prefix that identifies the blog it belongs to. Make a .sql text-only backup, then you can split the single .sql file into individual .sql files based on table name prefixes, fix up the headers so they're a valid .sql files for importing, then use them to create individual DBs for each blog. - --Bob. On 2017-03-29 11:24 PM, William Witteman via talk wrote:
I have a bunch of WordPress blogs sharing a database, which seemed like a good idea at the time.
Now I have a bunch of posts that were overwritten at *some* time in the past by bad data, but my backups are good.
My backup testing works perfectly well for complete replacement, but I don't want to do that - I want to cherry-pick for one blog of several, and only replace posts that existed in November with the same IDs in today's database, but leave everything else alone.
Does anyone have any thoughts?
Right now I am hoping not to have a couple of terminals open and try to manually run UPDATEs with copy-n-paste, because that seems rather error-prone.
Thanks!
William --- Talk Mailing List talk@gtalug.org https://gtalug.org/mailman/listinfo/talk
- -- Bob Jonkman <bjonkman@sobac.com> Phone: +1-519-635-9413 SOBAC Microcomputer Services http://sobac.com/sobac/ Software --- Office & Business Automation --- Consulting GnuPG Fngrprnt:04F7 742B 8F54 C40A E115 26C2 B912 89B0 D2CC E5EA -----BEGIN PGP SIGNATURE----- Version: GnuPG v2 Comment: Ensure confidentiality, authenticity, non-repudiability iEYEARECAAYFAljdb+YACgkQuRKJsNLM5eo0cgCgis4Cc6MVLBWXiW0iKQL9MfLT 2SMAoMQsItuJwiVDLIqUzYxAmk6kCW4k =dcEo -----END PGP SIGNATURE-----
participants (4)
-
Bob Jonkman -
Myles Braithwaite -
ted leslie -
William Witteman