Find and replace a string in entire mysql database

Most of the time when you want to copy a website for another use or even when you are changing your URL, you face this problem that you should search for the old URL among all records of database and then replace it with new one.

There are many ways and many scripts designed for this issue and each has a disadvantage.

I faced this problem and tested few of these ways but they are mostly causing more problem, specially when you have too many tables and too many columns. So I found the following way and it worked like charm:

You can follow these steps to do it, but the idea is simple, replacing string in text file:

  1. Exporting database in propper encoding
  2. Finding and replacing the string within the database data

 

1. Exporting database in proper encoding

This step is depend on your database management software, but since PhpMyAdmin is so common, I’m explaining it:

  • Login to your PhpMyAdmin and go to the target database
  • From the top menu, click on export
  • In the new page, select UTF-8 encoding and then click on “Go” button.

 

You can also use mysqldump command if you have terminal acccess:

mysqldump -u [DB_USER] -p[DB_PASSWORD] [DB_NAME] > file.sql

 

2. Finding and replacing the string within the database data

There are many ways you can do this, you can open the file with a text editor (I used Notepad++) :

  • Open the file
  • Press Ctrl+H
  • Type the text you are searching for
  • Type the text you want to change previous string to
  • Click on “Replace All” button

 

You can also use Nano or Vim to do this in text environment:

nano file.sql

and then press Ctrl+\ for search and replace

or

vi file.sql

and then:

:%s/foo/bar/g

The command above change each ‘foo’ to ‘bar’ in all lines.

Leave a Reply

Your email address will not be published. Required fields are marked *

This is new type of CAPTCHA, make the puzzle please !WordPress CAPTCHA