Re: Re : Backing up mysql driven website on REMOTE SERVER with SSH

From: Annonymous (nomail_at_nodomain.org)
Date: 12/09/04 

Date: Thu, 09 Dec 2004 10:18:21 -0500

NeilPaton wrote:

> Can anyone give me general and then specific advice for backing up a
> website on a remote server. I have root access via SSH. So far...
>
> I can log on to ssh rootserver@...
>
> I can then change directory using the cd command,
> I think the syntax is :
>
> cd /home/httpd
>
> That is about all I know at this stage. For doing this.
> I just want to be able to write the website on the remote server (my own
> server) to a local linux PC.
>
> Neil Paton
>
Neil, I run many web sites which use MySQL as their engines. Once you log into
the server via ssh, you will need to "dump" all databases associated with the
web site. This assumes you have access to the databases and MySQL tools.

 From your home directory make a directory for your backup

mkdir webbackup

then change to that directory with

cd webbackup

now dump the databases to ascii files with

mysqldump --add-drop-table databasename >databasebackup.dmp

(be sure to include the --add-drop-table to prevent errors if/when you restore)

that step is repeated for any databases your web site uses, of course changing
the file name you are dumping to.

If you are moving the dumped ascii files off to a server over the Internet you
would want to compress them with gzip or take all the dumped files in your
webbackup directory and put them into a compressed tarball with the command

tar -zcvf webbackup.tar *.dmp

Someone else suggested using rsync. I don't know how well that would work with
live databases. I do use rsync with other files. There is an excellent how-to
on using rsync via cron to copy files with SSH public keys at this URL:
http://www.jdmz.net/ssh/

If you're copying the files from the remote server to a local one you should
verify the integrity of your efforts. Untar the files to a webrestore directory
on your local machine

cd webrestore
tar -zxvf webbackup.tar

mysql -e "create database databasename"
mysql -e "grant all privileges on databasename.* to username@localhost
identified by 'password_if_you_use_one'"
mysql -e "flush privileges"

mysql databasename <databasebackup.dmp

The first 3 mysql -e commands are only done once to create the database. The
last mysql piping the data in from dababasebackup.dmp would be done any time you
want to restore the data.

Backing up the database is part of the effort, you have to make sure you back up
your graphics (pictures, icons etc) and any documents or PDFs you have available
on the web site. This is where rsync shines.

Hope this helps.

Relevant Pages

  • RE: [PHP] Microsoft .NET arguement
    ... You can't interface MySQL with ODBC? ... I've found MS SQL (and MS Access databases) to be extremely fast ...
    (php.general)
  • Re: [PHP] Because you guys/gals/girls/women/insert pc term here are a smart lot
    ... thinking about the storage requirements a little more. ... Maybe not applicable to little website databases, ... mysql on the other hand is no only more powerful ...
    (php.general)
  • Re: [PHP] SQL Source Control
    ... etc), which is all well and fine, but how do you manage source control ... on your databases? ... Say you've got an upgrade to a site, all of the new PHP files are ... Though perhaps a MySQL list would have more insight... ...
    (php.general)
  • Re: Questions to discover a php guru
    ... world and not too much familiar with the platform. ... Proficient with MySQL ... I'm by no means a php "developer", but I have built a site "from ... How do you backup mysql databases? ...
    (comp.lang.php)
  • Re: Large Scale PHP Application Design Questions
    ... Erwin Moller ... IMHO it is ALWAYS a good idea to use autoincrement PRIMARY KEYS on every table when designing relational databases. ... MySQL doesn't really have the behind-the-scenes ability to put it's storage in alternate locations. ...
    (comp.lang.php)
Loading