|
Moving MySQL Database
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. Thanks, Matthew Back to top |
|||||
|
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?
Back to top |
|||||
|
No, the mysqldump command simply outputs a full script of your database structure, tables etc, and the data contained. This thread has more information.
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: :: Code ::
%mysql -u <username> --password=<password> -h <server name> <database name> < <path to file on server> /* so for example db is called db1 and path is c:\web-stuff\mysql-bu\db1-12-31-04.sql //the above command would be */ c:\mysql\bin>mysql db1 < c:\web-stuff\mysql-bu\db1-12-31-04.sql 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. Back to top |
|||||
|
Don't forget, you can add this when creating the mysqldump of the database:
:: Code :: mysqldump --add-drop-table .... and so on....This will add the if table exists drop table, and lets you recreate the db without having to first delete it. It's hard reading the full documentation on all this stuff, easy to miss simple things like this. That will solve the one issue. It' pays to spend some time reading the documentation, as they say, RTFM... easy to say, hard to do. Back to top |
|||||
|
All times are GMT - 8 Hours
|
|||||