Backing up a mySQL database

Easy as pie.

Log into your MySQL server with your telnet client. From the command line, type:

mysqldump databasename > /path/to/output/file

That will create a text file with all the database info saved, including the appropriate CREATE TABLE and INSERT INTO statements so that the tables can be recreated merely by running that single file via the command line on the new server.

You can also back up parts of the DB if it's too large to conveniently back up into a single file. If it's got 5 big tables, each table can be saved to a different file like:

mysqldump databasename tablename > /path/to/output/file

or with 2 tables per output file like:

mysqldump databasename tablename1 tablename2 > /path/to/output/file

So, if your files are in /usr/www/username/ on your server and you wanted to back up tables called test1 and test 2 out of a DB named mydatabase, the final syntax for saving it would be:

mysqldump mydatabase test1 test2 > /usr/www/username/saveddatabase.sql

That didn't work, I followed your directions, but it created no files or anything.

If anybody else knows how to do it, please ICQ me at 73902680. Thanks.

ROFL! Try to help somebody and when they fail due to their own ineptitude they go looking for a 2nd opinion rathering than trying to learn from their mistakes. I wish you lots of luck trying to do it a different way.

Doesn't it depend if you are logged into the mySQL server vs having it remote?

For example, you can't do a "SELECT INTO OUTFILE" in mySQL if you're not logged into the database machine. Nor can you do a "LOAD DATA FROM INFILE" in earlier versions if you're not on the machine -- you can now do a "LOAD DATA FROM LOCAL INFILE".

Also, don't you need special permissions to run the mysqldump program?

How about writing a perl script that reads through your database catalog and exports all tables in .csv format? Then just use the LOAD DATA to re-load the tables.

Ralph

------------------
http://www.hockeydb.com

[This message has been edited by Ralph Slate (edited 14 June 2000).]

If you have appropriate permissions and MySQL is on the server, then the origionaly posted solution is correct:

mysqldump databasename > /path/to/output/file.sql

------------------
We're Here Forums!

So, I would type in something like:

mysqldump databasename > /home/webdevhq/webdevhq-www/file.sql and that would work?

Well if you are on the same server with the mysql, and you have permissions to do so, yes it should work just fine.

------------------
We're Here Forums!

if
mysqldump <database>
works I'm not amazed about all the stupid security problems we have at all.

it should be at least
mysqldump -u <dbuser> -p <database>
And <b>don't</b> put the password on the command-line !!

If you have written the whole application yourself it could pay off to write the import/export tool yourself, too.

Exporting the data and importing the "core" is always a good oportunity to sanitize the whole database.
Since all your db-maintainance functions are in a lib/module anyway that's usually straight forward.