HostArmada Web Hosting Knowledge Base

Knowledge is power! Use that power and achieve total and unconditional control over the Web Hosting Services!

How to replace database entries using SSH?

This method is handy when there are a lot of matches, and you need to replace them all. It saves you a lot of time as the phpMyAdmin technique only allows you to change a single value at a time. Here is what you need to do:

  1. Log in via SSH.
  2. Use the commands provided in this article under the "EXPORT A MYSQL DATABASE OVER SSH" to export your database.
  3. When you have performed the exportation process, please type in the following command:
    sed -i 's/currentValue/newValue/g' file.sql 
    Here is an example:
    sed -i 's/' backup.sql 
    This command will go over the entire file and will replace any values equal to the "currentValue" parameter with the one inside the "newValue".
    It is time to replace the current database with the new one holding the replaced values. To do so, you need to drop all the tables in the current database. To do so, please execute the following command:
    for table in $(mysql -u"database_user" -p"database_password" "database_name" -e 'show tables' | egrep '^*_' | egrep -v '^Tables');do mysql -u"database_user" -p"database_password" "database_name" -e "drop table $table";done 
    Here is an example:
    for table in $(mysql -u"hostarm_user123" -p"mjksad9234" "hostarm_data123" -e 'show tables' | egrep '^*_' | egrep -v '^Tables');do mysql -u"hostarm_user123" -p"mjksad9234" "hostarm_data123" -e "drop table $table";done 
  4. Lastly, please import the database you edited by using the commands provided in this article under the "IMPORT A MYSQL DATABASE VIA SSH" heading.
  5. There you have it! You have successfully performed a search-replace option for your database.

If you do not feel comfortable enough to attempt this method yourself - do no worry! Our technical support team is 24/7 available at your disposal, and they will be happy to assist you. You can contact them by submitting a support ticket from your Client Area.

Was this article useful and on point?

Find out more about HostArmada entire range of optimized Web Hosting Services and take action today on improving your website Loading Speed, Security, and overall Stability!