LinuxQuestions.org
Download your favorite Linux distribution at LQ ISO.
Go Back   LinuxQuestions.org > Forums > Linux Forums > Linux - Software
User Name
Password
Linux - Software This forum is for Software issues.
Having a problem installing a new program? Want to know which application is best for the job? Post your question in this forum.

Notices

Reply
 
Search this Thread
Old 05-28-2009, 08:40 AM   #1
eodchop
LQ Newbie
 
Registered: Nov 2008
Posts: 14

Rep: Reputation: 0
mysql dump to convert values


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
 
Old 05-28-2009, 11:59 AM   #2
TB0ne
Guru
 
Registered: Jul 2003
Location: Birmingham, Alabama
Distribution: SuSE, RedHat, Slack,CentOS
Posts: 14,759

Rep: Reputation: 2612Reputation: 2612Reputation: 2612Reputation: 2612Reputation: 2612Reputation: 2612Reputation: 2612Reputation: 2612Reputation: 2612Reputation: 2612Reputation: 2612
Quote:
Originally Posted by eodchop View Post
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...
 
Old 05-28-2009, 08:48 PM   #3
jlinkels
Senior Member
 
Registered: Oct 2003
Location: Bonaire
Distribution: Debian Lenny/Squeeze/Wheezy/Sid
Posts: 4,161

Rep: Reputation: 504Reputation: 504Reputation: 504Reputation: 504Reputation: 504Reputation: 504
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
 
Old 05-29-2009, 07:49 AM   #4
eodchop
LQ Newbie
 
Registered: Nov 2008
Posts: 14

Original Poster
Rep: Reputation: 0
Quote:
Originally Posted by jlinkels View Post
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
 
Old 05-29-2009, 08:07 AM   #5
jlinkels
Senior Member
 
Registered: Oct 2003
Location: Bonaire
Distribution: Debian Lenny/Squeeze/Wheezy/Sid
Posts: 4,161

Rep: Reputation: 504Reputation: 504Reputation: 504Reputation: 504Reputation: 504Reputation: 504
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
 
Old 05-29-2009, 11:21 AM   #6
TB0ne
Guru
 
Registered: Jul 2003
Location: Birmingham, Alabama
Distribution: SuSE, RedHat, Slack,CentOS
Posts: 14,759

Rep: Reputation: 2612Reputation: 2612Reputation: 2612Reputation: 2612Reputation: 2612Reputation: 2612Reputation: 2612Reputation: 2612Reputation: 2612Reputation: 2612Reputation: 2612
Quote:
Originally Posted by jlinkels View Post
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.
 
  


Reply

Tags
dump, mysql, values


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to convert the values displayed by sar -r with top rajaniyer123 Solaris / OpenSolaris 9 07-05-2010 02:48 PM
mysql dump mahmoud Linux - Server 7 02-27-2008 02:43 AM
Convert characters to hex values in perl pjz Programming 3 12-08-2005 08:35 AM
Mysql with 10.2 dump barrythai Mandriva 2 04-11-2005 07:18 PM
storing multiple values within one field in mysql antken Programming 8 12-15-2002 10:08 PM


All times are GMT -5. The time now is 05:55 AM.

Main Menu
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
identi.ca: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration