MySQL Dump

narmi2
narmi2 used Ask the Experts™
on
Dear Experts,

We have a database with many tables in MySQL 3.23 and are upgrading to MySQL 5.0.  Is MySQL Dump the best way to get the database from server to server.  If it is the best way, could someone show me how to use it problem with example script?  If it is not the best way, please do let me know

Thanks.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Senior Principal Technical Support Engineer
Top Expert 2009
Commented:
Hi!

It depends upon the environment - lets say you have primaraily MyISAM tables then you can take the binary files using "mysqlhotcopy" or even just bringdown MySQL and copy the datadir which is the fastest and best way to backup. But if you have mixed stoareg (MyISAM, InnoDb etc) then its better to go with "mysqldump" utility.

Let me know..
Commented:
Yes, mysqldump is the best way as it dumps both table structure and data. However have you looked at the migration toolkit?

http://dev.mysql.com/downloads/gui-tools/5.0.html

Author

Commented:
If I decide to use mysqldump, what do I type in to get the full database, database structure, database data, table structure etc etc everything?

I have 3 databases, but I only want 1 of them.
Amazon Web Services

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

Commented:

Backup to file
mysqldump -opt -u username -p > backup.sql

Backup and zip
mysqldump -opt -u username -p | gzip > /mydir/mydatabase.gz



Or you could just use this script:
http://sourceforge.net/projects/automysqlbackup/
UmeshSenior Principal Technical Support Engineer
Top Expert 2009
Commented:

Open a command prompt window

cd to /path/to/mysql/bin

In My machine it looks like this..
Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.
 
C:\Documents and Settings\DBA>cd\
 
C:\>cd mysql/bin
 
## For all databases
 
C:\MySQL_Ent\bin>mysqldump -uroot -p --all-databases > all_dump.sql
Enter password: ******
 
## For Single database replace DBNAME1 with DB name
## Replace username with actual user to connect MySQL server
 
C:\MySQL_Ent\bin>mysqldump -uusername -p DBNAME1 > DBNAME1.sql
Enter password: ******

Open in new window

Author

Commented:
Once this is done, how do I restore the database on the new server?
UmeshSenior Principal Technical Support Engineer
Top Expert 2009
Commented:
Using "mysql" utlity you can restore


C:\MySQL_Ent\bin>mysql -uusername -p < DBNAME1.sql
Enter password: ******
 
More on "mysql" here 
 
http://dev.mysql.com/doc/refman/5.0/en/mysql.html

Open in new window

Commented:
or you can go into mysql and type ./source /dirtofile/backup.sql

Author

Commented:
Is the --opt switch important for my needs?
UmeshSenior Principal Technical Support Engineer
Top Expert 2009
Commented:
--opt is enabled by default in MySQL =>4.1 and higher versions but prior to that you need mention it.

This option is shorthand; it is the same as specifying --add-drop-table --add-locks --create-options --disable-keys --extended-insert --lock-tables --quick --set-charset. It should give you a fast dump operation and produce a dump file that can be reloaded into a MySQL server quickly.
The --opt option is enabled by default. Use --skip-opt to disable it. See the discussion at the beginning of this section for information about selectively enabling or disabling certain of the options affected by --opt.
UmeshSenior Principal Technical Support Engineer
Top Expert 2009
Commented:
This may help you for more details

MySQL 3.23/4.0/4.1 Manual

http://dev.mysql.com/doc/refman/4.1/en/mysqldump.html

MySQL 5.0 Manual

http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial