mysql dump to convert values

eodchop · 05-28-2009, 08:40 AM

We noticed that during that during a recent migration process that some of the databases still have the full URLs. For example, in the 'example' database there are several rows with:

http://upsstore.rtcolor.com/LWServle...9613.large.gif

We need to do a dump of all the 1.0 converted databases and convert these values to

previews/saved-layouts/29613.large.gif

Otherwise if the client ever has us do an FQDN change all the previews will be missing.

I then need to import these converted values back into mysql.

I am a complete mysql newb. Can someone point me in the right direction please?

Thanks,

Ryan

TB0ne · 05-28-2009, 11:59 AM

Quote:

Originally Posted by eodchop

We noticed that during that during a recent migration process that some of the databases still have the full URLs. For example, in the 'example' database there are several rows with:

http://upsstore.rtcolor.com/LWServle...9613.large.gif

We need to do a dump of all the 1.0 converted databases and convert these values to

previews/saved-layouts/29613.large.gif

Otherwise if the client ever has us do an FQDN change all the previews will be missing.

I then need to import these converted values back into mysql.

I am a complete mysql newb. Can someone point me in the right direction please?

Well, if it's stored in the database, a mysqldump is going to just pull it out. The good news is, that the file is just (essentially), a text file. There may be other ways to do it, but you could try a quick-and-dirty method using sed, like this:

Code:

cat <mysql dump file name> | sed 's/http\:\/\/upsstore.rtcolor.com\/LWservlet\/prod_data\///g' > <output file name>

That'll just chop off the http://..... stuff, leaving you with just 'previews/gif-file-name.gif'. Might have to tweak the syntax of the backslashes, though. They 'escape' a character, so it treats it as such, instead of an operand. Lots of info on the sed command on the net, too. That may work for a quick-and-dirty solution, but there may be more elegant ways to do it.

You could even pipe the mysqldump through a working sed statement, so it'll strip them out on the fly, whenever you run the command...

jlinkels · 05-28-2009, 08:48 PM

Execute this SQL statement in MySQL:

Code:

UPDATE tablename SET url=REPLACE(url,"http://upsstore.rtcolor.com/LWServlet/prod_data/","");

Assuming the table name is tablename and the column which stores the URL's is url. No need for mysqldump etc.

BE SURE TO HAVE A GOOD & SOLID BACKUP. One typo and you loose all data. Yes, you make the backup with mysqldump.

jlinkels

eodchop · 05-29-2009, 07:49 AM

Quote:

Originally Posted by jlinkels

Execute this SQL statement in MySQL:

Code:

UPDATE tablename SET url=REPLACE(url,"http://upsstore.rtcolor.com/LWServlet/prod_data/","");

Assuming the table name is tablename and the column which stores the URL's is url. No need for mysqldump etc.

BE SURE TO HAVE A GOOD & SOLID BACKUP. One typo and you loose all data. Yes, you make the backup with mysqldump.

jlinkels

Is there anyway to use your method and not have to go line by line in Mysql? I have 15 db's that need attention. Several of them contain 10,000+images.

Thanks,

Ryan

jlinkels · 05-29-2009, 08:07 AM

This is one statement PER TABLE, not one statement per line.

If you have many tables and many databases to process, prepare a text file with lines like this:

Code:

UPDATE dbname1.tablename1 SET url=REPLACE(url,"http://upsstore.rtcolor.com/LWServlet/prod_data/","");
UPDATE dbname1.tablename2 SET url=REPLACE(url,"http://upsstore.rtcolor.com/LWServlet/prod_data/","");
...
UPDATE dbname2.tablename1 SET url=REPLACE(url,"http://upsstore.rtcolor.com/LWServlet/prod_data/","");
...

Which is done easily using copy & paste. Save the file, and execute:

Code:

mysql -u username -ppassword < file_you_just_created

Not the absence of space in -ppassword.

Again, this file only need to contain a line for each table you are processing, not for each URL.

jlinkels

TB0ne · 05-29-2009, 11:21 AM

Quote:

Originally Posted by jlinkels

This is one statement PER TABLE, not one statement per line.

If you have many tables and many databases to process, prepare a text file with lines like this:

Code:

UPDATE dbname1.tablename1 SET url=REPLACE(url,"http://upsstore.rtcolor.com/LWServlet/prod_data/","");
UPDATE dbname1.tablename2 SET url=REPLACE(url,"http://upsstore.rtcolor.com/LWServlet/prod_data/","");
...
UPDATE dbname2.tablename1 SET url=REPLACE(url,"http://upsstore.rtcolor.com/LWServlet/prod_data/","");
...

jlinkels

Very nice solution, jlinkels. Better than mine, but I'm no MySQL expert...thanks for sharing.