Search and replace on entire database?

Have a website where we have just changed domain names, but am having a problem because content has been submitted that occasionally uses absolute URL’s which has resulted in lots of links through the site pointing to the old domain.

What I want to be able to do is do a search and replace in MySQL that replaces all use of the old domain name with the new one. I’ve found several search and replace guides around the internet, but they all seem to do a search and replace on a specific field on a specific table like this on;

UPDATE tablename SET tablefield = replace(tablefield,"findstring","replacestring");

Can anybody tell me how to do a search and replace on an entire database in all fields?

1.
Dump the database with mysqldump to a text file.

2.
Then do a search replace on this dump file. Since it is a text file
you can just use any search replace tool that you want.

3.
Then import the dump file to the DB again and you should be set.

If your DB is so big that you can’t open the dump file in a normal editor you will probably have to use some search replace tool like sed or PERL.
Since I’m a PERL man I would do:

perl -p -i.bak -e 's/oldstring/newstring/g' yourDumpFile

That will substitute all occurances of oldstring to newstring in yourDumpFile and also save a backup file callec yourDumpFile.bak. If you don’t want a backup file you can omit the .bak in the argument.

how to extract a "tar.bz2" file?

Open a terminal and type:

tar xvjf filename.tar.bz2

Which will untar it to the current directory. Normally (99% of the time) it will create it’s own subdirectory so you don’t need to worry about that.
Just so you know:
tar – Tape ARchiver
And the options:
x – extract
v – verbose output (lists all files as they are extracted)
j – deal with bzipped file
f – read from a file, rather than a tape device

“tar –help” will give you more options and info

How to find out the Linux distro release and the version number of your server?

To check the version date of your OS (Linux distros):

uname -v

To find out the release:

uname -r

To display and show the complete kernel signature of your hosting server:

uname -a

Something like this will output similar information concerning your Linux kernel:

cat /proc/version

If you haven’t had the uname utility installed, you can get the kernel release version by:

cat /proc/sys/kernel/osrelease

You may also be interested in some other useful things to see within /proc.

Simple SSH (Linux) Command to Display the Number of CPUs (Processors) of Your Hosting Server

One of the first things that may concern you is that whether your web hosting company has equipped enough CPUs or Processors on your server as they have allegedly done. Or you are on shared plans and are simply curious whether your web hosting provider is overselling by overloading your server a lot.

First make sure you have SSH access to your hosting server which majority of hosting businesses are now providing. Then create an SSH account and log it in to the server.

A rather simple linux bash command will help you determine how many CPUs your host has on your server:

cat /proc/cpuinfo | grep processor | wc -l

This combination of pipeline command extracts server processors information from /proc/cpuinfo that contains CPU details each per line, returning a plain number which will usually be 1, 2, 4 or even more.

There you go. Now find out whether your host is cheating on you with this tip in addition to checking your server load!

Extra Tips

You can find plenty of other useful and interesting information about your hosting server and OS release at /proc. For example, for some RAM stats:

cat /proc/meminfo

For total seconds since the last reboot:

cat /proc/uptime

For Linux release and versions:

cat /proc/version

And much more. Just ‘ls /proc’ and try for yourself.

Check for Hosting Server Load with Linux (SSH) Command ‘uptime’

Some of the hosting providers out there are infamous for overselling who try their best to stuff in as many users (websites) as possible into a single web hosting server. High server load is an indicator of how your server is performing and whether it is laboring too much thus jeopardizing the performance of your websites. You can get to know the average load in the last 15 minutes of your server by the simple Linux command below (via SSH):

uptime

Which will typically return a line of data similar to this:

21:39:33 up 10:45, 3 users, load average: 4.46, 3.92, 3.64

That says there are currently 3 users logged on and the load average of this server in the last minute, last 5 minutes and last 15 minutes are 4.46, 3.92 and 3.64. These figures represent the number of runnable processes at the same time on average for the CPUs (processor) to process. Combined with number of processors of the server, you may know how many processes are being processed by any single CPU.

Considering the fact that any CPU can only take on one process at any given time, there will possibly be processes waiting in the queue – meaning server is overloaded. Therefore, if the number of processors of your hosting server is 4, in the last minute, it is overloaded by ( 4.46 / 4 ) – 100% = 11.5%.

Extracting and creating archive files

Sometimes you would need to extract or create an archive file (i.e to install a script, you would usually download an archive and extract it to continue the installation). The very first step in the process would be to identify the exact archive type by looking at the file extension. The most common archive types are zip (ending with .zip), tar (.tar), Tar+Gunzip (.tar.gz), Bzip (.bz2) and Rar (.rar).

Each archive type has its own command for compressing/extracting as listed below.

To extract a ZIP file, please use:

unzip archive.zip

To extract a Tar file, please use:

tar -xvf archive.tar

To extract a Tar.Gz file, please use:

tar -zxvf archive.tar.gz

To extract a Rar file, please use:

rar x archive.rar

Each archive type has its own mechanism to create a new archive file. The most commonly used however is the tar.gz format. An example of creating such a file can be seen below:

tar -zcf archive-name.tar.gz foldername/

Source: http://www.siteground.com/tutorials/ssh/ssh_extracting.htm

SSH Commands

Login to SSH using terminal

ssh username@domain.com -p22456

Extract a tar.bz2 file

tar jxf file.tar.bz2

Backing up Database: The command for backing up your database is the following:

mysqldump -u username -p database_name -hServerIP > /path/backup.sql

Type the above command in your command line with your username, database_name, and storage_path for dump to be saved in, press Enter and you will be prompted for your database user’s password.

Restoring your Database: The command for restoring your database is the following:

mysql -u username -pPassword -hServerIP database_name < path/db.sql
&#91;/shell&#93;

Note the lack of a space between -p and Password. Also, the password is for the MySQL database.

<strong>Compressed dumps/restores</strong>: In order to restore a compressed database, you will have to uncompress the file first. The following command combines both the steps of uncompressing your file and transferring its contents to a database:

[shell]
gunzip < backup.sql.gz | mysql -u blarg -pPassword db_name
&#91;/shell&#93;

If you want to directly compress data from a mySQL database without having to gzip separately, enter the following command:

&#91;shell&#93;
mysqldump -u username -p -hServerIP db_name | gzip > backup.sql.gz

After you type the above and press enter, you should be prompted for your password. Enter your password, and you should be all set.

Compress/Uncompress: If you want to compress your existing .sql file, you will need to use the following command:

gzip -X path/to/backup.sql

In the above command, X is a number between 1 and 9 that specifies the level of compression used. The higher your number, the more compressed (smaller) your file will be.

For example, this is the command I use to compress my data:

gzip -9 forum_backup.sql

Likewise, to uncompress a compressed file, you will use the following command:

gunzip backup.sql.gz
Page 2 of 212

Pin It on Pinterest