Update a MySQL table from the command line (find and replace)

I thought this might be helpful to some of you out there, as I routinely have to do this myself. Need to update a table, or a lot of rows of data in MySQL? Here is a handy Find and Replace SQL script to run at the command line.

mysql> update TABLENAME set FIELDNAME  = replace(FIELDNAME, 'X','Y');

In the above example, X is the existing text and Y is the new text you want to replace with. Have fun!

How to change timezone on a Linux server (CentOS)

We run CentOS for many of our server builds and this question often comes up. Hope this helps.

Here are the steps:

  1. Make a backup copy of the default localtime file.
  2. [code]
    mv /etc/localtime /etc/localtime.bak
    [/code]

  3. Create a symbolic link to a new localtime file
  4. [code]
    ln -s /usr/share/zoneinfo/America/Los_Angeles /etc/localtime
    [/code]

Be sure to replace “America/Los_Angeles” with the directory (if your zone has one) and filename of the timezone you want to use. Then test your change by running the command “date” from the command line.

Use rsync to transfer files between Linux servers

We use rsync to transfer files across servers and to assist with a variety of tasks associated with backup and restore. We found this great tutorial over at Nixcraft. Check it out. They do a great job of explaining advanced uses of rsync.

In a nutshell, this is one of the most useful commands we use and thought we would share with you.

[code]
# copy files from server1(local) to server2

rsync -avz /var/www/html/* user@server2.com:/var/www/html/

[/code]

How to backup to Amazon S3

## UPDATE 2017 ##
The method described below was in use 5 years ago. It may still work on your host, but there are more elegant ways to do this now. I keep it here for archive purposes only.
##############

Need to backup your Linux server files and databases to Amazon S3? Here is a great tool – http://s3tools.org/download

Follow the install instructions.

Once you set up your S3 bucket, you can use this bash script to copy files to the S3 bucket:


#!/bin/sh
#
## Lanexa.net - file name = BACKUP_s3.sh
## backup script to push archive files to Amazon S3 bucket
## 2012-01-04
#
# define where your backup files are stored locally on your server
FILEDIR=/BACKUP/files/
DBDIR=/BACKUP/mysql/
#
cd $FILEDIR
#
# use s3cmd to upload files to S3 instance
s3cmd put FILENAME s3://BUCKET-NAME/
#
cd $DBDIR
s3cmd put DATABASES s3://BUCKET-NAME/
#
# email a note to yourself that backup is complete
mail -s "backup files and databases copied to S3" youremail@address.com