Backing up

[Ike]

I am running off of a MySQL database on a remote server. I am truly
concerned about backup. I have phpMyAdmin running on the remote server, and
I see I can "Export" to various formatsd from there, but nothing to "Import
if I need to reinstate my data.

How do you typically backup a MySQL database? Cant I simply copy the files?
How does one do this if I want, say, to backup the files from the remote
server, to my local harddrive? Thanks, Ike

[Boomerang]

Ike wrote:

I am running off of a MySQL database on a remote server. I am truly
concerned about backup. I have phpMyAdmin running on the remote server, and
I see I can "Export" to various formatsd from there, but nothing to "Import
if I need to reinstate my data.

How do you typically backup a MySQL database? Cant I simply copy the files?
How does one do this if I want, say, to backup the files from the remote
server, to my local harddrive? Thanks, Ike

You can either export via phpMyAdmin or via the mysql-dump command.
(See: http://www.mysql.com/doc/en/Backup.html for more details).

Then you can recover it by using 'mysql your_database_n ame <
dumpfile.sql' (without the quotes) Or via PHPMyAdmin, but you have to
read the docs for that.

HTH,
Boomerang

[Jim Moseby]

"Ike" <rx*@hotmail.co m> wrote in message
news:_J******** *********@newsr ead1.news.atl.e arthlink.net...

I am running off of a MySQL database on a remote server. I am truly
concerned about backup. I have phpMyAdmin running on the remote server, and I see I can "Export" to various formatsd from there, but nothing to "Import if I need to reinstate my data.

How do you typically backup a MySQL database? Cant I simply copy the files? How does one do this if I want, say, to backup the files from the remote
server, to my local harddrive? Thanks, Ike

Hi Ike. I answered this question in another group a while back. I'll
repost it here.

First, have a look at mysqldump, a utility for dumping the databases for
backup that comes with MySQL. Also, depending on your situation, you may
be able to back it up like so:
1. Stop the MySQL server ('/etc/init.d/mysql stop' on my servers)
2. Copy, zip, tar, whatever... the actual database files. There will be a
directory for each database, containing a.frm, a .MYD and a .MYI file for
each table. Simply make a copy of the entire directory structure.
3. Restart the MySQL server ('/etc/init.d/mysql start' on my servers)

Easy as 1-2-3!

I can do it this way because my servers are not in use except during the
day, so I can shut them down in the evenings for backup. Restoring a
crashed server is as simple as setting up a new Red Hat (or your flavor of
choice) box with MySQL and copying the directory structure back into it. It
also makes it very easy to restore a single table within a database (just
stop the server and copy the three files associated with the table you
want.) You have to mind the file ownerships and permissions, but otherwise
it works like a charm.

I use a simple script, kicked off automatically each night by a cron job:
#
# Backup the databases
#
/etc/init.d/mysql stop
prefix=`eval date +%d`
suffix=tar.gz
backupfilename= $prefix.$suffix
tar -cvzf /backups/$backupfilename /data/databases /data/scripts
/data/shared /data/wwwroot/htdocs
/etc/init.d/mysql start

This script creates a file for each day of the month, overwriting the one
from the previous month. So, you will have a maximum of 31 files named
01.tar.gz ... 31.tar.gz that each contain the entire MySQL database
including all the users, etc and anything else you care to add to the
backup. Obviously this method has some limitations, and would not work for
everyone, but it has worked well for me.
HTH - JM