I'm trying to download my main site's MySQL database so I can experiment with it on my development box. Unfortunately I don't know exactly how to do what I need. I've heard transferring databases like this can be tricky, so I want to make sure I know what I'm doing before I start.

I found some instructions, but they said to dump the database from the old server first, then import the dump into the database on the new server. Unless I've missed something, dumping a database simply removes the data from the original database, right? If that's the case I'll need to do something else; I need the original database to remain fully functional.

I thought "dumping" was creating a backup? Do you not have PHPMyAdmin available? If so, you can create a backup through that. If not, and dumping does "clean" the db, you could just upload the dump back to the original as well as the new db, could you not?

I just looked into this matter because I wanted to automate phpBB's backup system.

In the case of phpBB, you're better off using theirs, since the output file, which is pretty much just a dump of the database, but it has one extra line per table, if exists drop table x, which is crucial for a live rebuild of an existing database.

The mysqldump command puts out a text file, with sql statements, that when imported to a non-filled database will then rebuild the structure, or rather, build the structure, then populate that structure with the data.

When you create your local database you either made it with username and password or not:

if not:

:: Code ::

%mysql <database name> < <path to file on server>

is all you need to populate it assuming the database is already in existence, but empty.
If you have a user name and password on your local machine, it would look like this:

In other words, the command is 'mysql', the db is the second item, the '<' means put the file into the db, if it was '>' it would be put the db into the file, and the last thing is the file.

You can run this in the windows command line box, first of course you have to navigate to the mysql folder to issue the above command, usually mysql installs itself on:
c:\mysql\bin

so in the command line just
c:>cd c:\mysql\bin

then run the above command, works like a charm. By the way, the uncompressed mysql backups seem to all be in this format, the ones I get off my web servers using the server mysql backup gui produces the same mysql dump file as just running a manual command line mysqldump.

However, keep in mind that for sychonizing databases you'd have to fully delete your old one on your development box each time before rebuilding it, or you will get a 'table already exists' error, that's what the 'if table x exists, drop table x' is for in the phpBB db backup.

techAdminStatus: Site AdminJoined: 26 Sep 2003Posts: 3581Location: East Coast, West Coast? I know it's one of them.