Replace string in big database or large text file

Submitted by Anonymous (not verified) on Sun, 05/02/2010 - 22:28

You have a cms and a big database with over 100 tables and 100.000 entries. And you need to replace all entries contain "example.com" with "www.example.com". How you do that? Editing all entries manually with phpmyadmin? It could takes weeks to complete. I have found a solution and want to share with you.

Steps:

1. Export your whole database in a text file : db.sql
2. Replace string using Linux sed command
3. Empty your db and import new sql file

Export your mysql database into a text file: db.sql

mysqldump -u root -p your_database_name > db.sql

Replate old string text with new one


sed 's/http:\/\/example.com\//http:\/\/www.example.com\//g' db.sql > db.sql.replaced

Empty DB with drop all tables with phpmyadmin. (Do not drop database self)

Import replaced new .sql to database

mysql -u root -p your_database_name < db.sql.replaced

Add new comment