phpMyAdmin - possible to find/replace throughout database?

Support
,
1

NethServer Version: 7.4
Module: phpMyAdmin

I’ve mentioned a few times that I’m looking into migrating from SME 9.2 to Neth 7.4. One of the things that will need to be migrated is a Nextcloud installation, and I have the bulk of the procedure worked out. But it involves doing a good bit of find/replace through the old database, and doing it manually would be both tedious and error-prone. The are several tables in which the operation needs to be run, and there will be several iterations of the replace to run (each UUID needs to be replaced). Which leads to two questions:

  • Is it possible, using phpMyAdmin, to run a find/replace through an entire database rather than one table at a time? Bonus points if it will show the tables in which replacements were made. Failing that, is it possible to do it at the mySQL/mariaDB shell?
  • Is it possible to automate doing several such find/replace operations? IOW, throughout database owncloud, in all tables, replace oldval1 with newval1, then replace oldval2 with newval2, etc.

Edit: This post is looking promising, but the link to mj7.co.uk isn’t working at the moment. However, the link to interconnect/it is live–looks like some experimentation is in order.

Edit 2: The script from interconnect/it looks like it will do quite well for the first bullet. It will also do a dry run to let you know what, if any, changes will be made, as well as show the tables affected once it completes. Since that script can also be called from the command line, it would no doubt be possible to combine that with another script and an input file containing the appropriate data. One of the comments on the blog post I linked links to a page from w8.se which apparently (as of 2009) had a version of the script that would satisfy my second bullet point as well, but it isn’t there any more.

2

For extinct/inaccessible webpages sometimes you can find a copy in archive.org (wayback machine).

Apart from mysql command line, on phpMyAdmin it’s possible to create sql queries for multiple tables (SQL tab, Query tab):


For those options some mysql syntax knowledge comes in handy.
Other database management tools (Adminer, DBeaver, etc.) might have similar features.

Another option is to export a database dump and use the find/replace feature of code editors (SublimeText, Notepad++, Atom…) against it. Some can show a list of what will be replaced before confirmation, and some support regular expressions. Then import the modified database.

As always, it’s advisable to have a database backup before proceeding with any massive change.

4 Likes
3

Thanks for the suggestions, and yes, I’d definitely have a backup. And yes, I expect learning some mysql syntax would be helpful as well.

4

mate, sql dumps are just text files… you can always use the good sed command (google has zillions of examples about replacing a string inside mysql dumps)

HTH

1 Like
5

Now that’s simple enough that I’m kind of embarrassed I didn’t think of it. Thanks.