Backuping SQL

Problems with the Windows version of XAMPP, questions, comments, and anything related.

Backuping SQL

Postby Killerfrog » 16. March 2005 21:15

Hello,

May someone explain me how backuping a full mysql database step by step and how to use backup if anything fails please?

I've tried with phpmyadmin, but found that when I export my entire database, i'm just unable to re-upload it! (In fact, I have to export each database than recreate each one and replug data which seems to be a little bit anoying... i'm sure there is a faster and better way to do backup of mysql.

Thx
Killerfrog
 
Posts: 75
Joined: 18. December 2004 21:16

Postby joannew » 16. March 2005 21:36

to reload the data from the command line

mysql> source filepath.txt
User avatar
joannew
 
Posts: 3
Joined: 16. March 2005 21:23
Location: Wales

Postby taustin » 17. March 2005 00:11

There is a command line program in the mysql\bin\ directory called mysqldump.exe that will do the backup.

mysqldump -A --user root --password=password >backup.sql

will dump the entire server in to a file called "backup.sql" in the current directory. (-A means "dump all databases, --user defines the user, root should work, but any user with adequate priviliges will do, too, --password= sets the password, and whatever you put after the ">" is the file name it will dump to.)

You can resort by starting up the mysql from the command line, by typing in "mysql --user root" (or whatever user you're using). It should prompt you for a password if one is needed. Once you're in, you can run the backup.sql file (or whatever you called it) by typing in "source backup.sql" (or "source path\to\backup\file\backup.sql", if it's not in the bin directory).

If there's much there, it will take a *long* time to restore.
taustin
 
Posts: 150
Joined: 24. September 2004 00:23
Location: Huntington Beach CA

Postby Killerfrog » 17. March 2005 02:38

Thx

Do I have to be local to the computer to do that?

Is there no programe that can do that even more easier?
Killerfrog
 
Posts: 75
Joined: 18. December 2004 21:16

Postby dobbelina » 17. March 2005 07:28

If you have a large database phpmyadmin usually like to "time out"
when restoring large databases.
If you gonna restore the whole database (and it's not to large) you
have to click the "sql query window" up in the left corner.
After that (in the pop-up window) click import files.
That's the only way to import databases that has the
"CREATE DATABASE" statement.
It would be a better idea though to backup each database separately.
You do that by clicking the database name in the left drop down, then
choose export. (Be sure to select all tables).
Restoring of large databases are best done with bigdump.
Bigdump is a restore script that uses javascript to restore the database
3000 lines at a time. (doesn't time out).
You simply upload your database (uncompressed) and run the script
on the server.

Bigdump can be found here:
http://www.ozerov.de/bigdump.php
dobbelina
 
Posts: 17
Joined: 14. March 2005 09:33

Postby Killerfrog » 17. March 2005 15:12

Thx,

I prefer this method.

I just tried and it worked perfecly to backup then rewrite database!
Killerfrog
 
Posts: 75
Joined: 18. December 2004 21:16

Postby taustin » 17. March 2005 17:53

Killerfrog wrote:Thx

Do I have to be local to the computer to do that?

Is there no programe that can do that even more easier?


To use the command line tool, yes, I believe you have to be local. So long as the file isn't too big, the phpmyadmin stuff is easier to use.
taustin
 
Posts: 150
Joined: 24. September 2004 00:23
Location: Huntington Beach CA

Postby Killerfrog » 17. March 2005 19:58

k thx
Killerfrog
 
Posts: 75
Joined: 18. December 2004 21:16


Return to XAMPP for Windows

Who is online

Users browsing this forum: No registered users and 96 guests