mysql backup/restore

Anon

hello everyone...

i'm a little new to mysql and wanted to know if there is a way to backup and restore a specified table and/or entire database.

If so, what version do I need to be running and if there isn't such a thing or I can't do it, I"m pretty sure I can back it into a textfile, but how will I restore it.

Also can you explain how to backup and restore using mysql,
thanks!

PHP Version: 4.0.4
MySQL Version: not known

Anon

see mysql manual (www.mysql.com) for more details, but to back up a database use at the shell command line:
$ mysqldump -ppassword [database_name] >/root/to/file_name/to/dump/to

this will dump the whole database structure and data.

To restore:
add to the start of the dumped file:
DROP DATABASE DATABASE_NAME;
CREATE DATABASE DATABASE_NAME;
USE DATABASE_NAME;

and at the end of the file add a GRANT statement e.g.

GRANT INSERT,UPDATE,DELETE,SELECT
ON DATABASE_NAME.*
TO username@localhost
IDENTIFIED BY 'pword';

save file, then go back to the command line:

$ mysql -ppassword </root/to/edited/file

This should restore the database to the state at which you dumped it.
There are other ways, too - check out the manual.

Anon

Is it possible to resore a backup using a query? My host doesn't provide telnet access.

TIA

Dave Patterson.

Anon

If the backup was made from a mysqldump, it is made up of line after line of SQL that can be used to rebuild the database and populate it with the dumped data.
Use fopen to open the file, read each line in turn, and while there are (uncommented) lines of the file to read, do:

$sql="line_whatever_of_dump_file";
mysql_query($sql);

You may have to DROP and then CREATE the database first, again using SQL calls, and add a GRANT statement at the end - see my previous reply.

If your backup is different to this, I am sure you could manipulate it in some way to achieve the same result.

Anon

Hi, Phil/Mark/Dave,

$ mysqldump -ppassword [database_name] >/root/to/file_name/to/dump/to

This seems not to work, it gives a message the
/root/to/file_name/to/dump/to "is a directory". I want to back up the whole database, can any one help.

Thank.

Anon

is /root/to/file_name/to/dump/to/ a directory that exists?
if so you need to add a file name to the end - eg:
/path_to_file/file_name.sql