Find and Replace text in all tables in a database using Mysql query?

Dopani

Well-known member
Registered
Joined
Mar 11, 2014
Messages
233
Points
18
I have a custom database and there is a word http:// that I want to change to https:// with a query in phpmyadmin or using CLI. What update command can I use to find and replace this text in all tables of my current database?
 

Allen_Jolynn0

New member
Registered
Joined
Apr 26, 2018
Messages
1
Points
3
If you want to edit from all tables, best way is to take the dump and then find/replace and upload it back.
 

Dopani

Well-known member
Registered
Joined
Mar 11, 2014
Messages
233
Points
18
Dopani
Honestly I used this way in the past and I got error after I restored it back Phpmyadmin, I don't know why but I would run updates via Mysql query, that seems safer and I used succeeded with running queries.

I used this command

Code:
UPDATE Table_name SET field = replace(field, "old_data", "new_data");
but It will only work on a table, I wanted to run queries on all tables.
 

KnownHost-DanielP

Well-known member
Hosting Provider
Registered
Joined
Mar 1, 2018
Messages
121
Points
28
Best answer
I agree with Allen, perform a database dump, and then do the search/replace on that dump and re-import.

So to dump MySQL (assuming linux) lets go ahead and make two, one we're going to mess with, the other is a backup incase something goes wrong.
Code:
mysqldump -u username -P database_name -> /path/to/dump/file.sql
mysqldump -u username -P database_name -> /path/to/dump/full_backup.sql
Next you can use sed to search and replace.

Code:
sed -i 's/http:\/\//https:\/\//g' /path/to/dump/file.sql
Finally, re-import the database.

Code:
mysql -u username -P databae_name < /path/to/dump/file.sql
That autta do the trick. I you run into any issues lemme know but always take backups first :)
 

Dopani

Well-known member
Registered
Joined
Mar 11, 2014
Messages
233
Points
18
Dopani
I afraid I have not got sed installed on my VPS. what is it?

Code:
sed -i 's/http:\/\//https:\/\//g' /path/to/dump/file.sql
What is -i, s, g parameter in this command?

Is it easier for me to download the db file to Windows and replace http by https via a text editor as notepad++?

Thanks
 

VirtuBox

Well-known member
Registered
Joined
May 3, 2016
Messages
1,622
Points
83
I have a custom database and there is a word http:// that I want to change to https:// with a query in phpmyadmin or using CLI. What update command can I use to find and replace this text in all tables of my current database?
Hello,
You can use the search and replace feature of phpmyadmin. At first une search to find tables with the word http://, and then go into the table and use search and replace.

Screenshot_265.png
 
Older Threads
Replies
0
Views
1,350
Replies
1
Views
1,976
Replies
6
Views
4,758
Replies
14
Views
3,263

Latest Hosting OffersNew Reviews

Sponsors

Tag Cloud

You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.

Top