Moving a MySQL installation?

[richb at pioneer.ci.net (Rich Braun)]

Mark Richards <mark.richards at massmicro.com> suggested:
> My preference would be your option 2 which can be done in a single
> statement to export each database and a single statement to import each
> database:
>
> mysqldump -u USER -p  DATABASE > wholedb.sql

In fact that's how I do my backups.  Just replace DATABASE from the command
line above with the option "-A" and you get all databases.  The "--opt"
command line option is also useful for backing up whole databases.  Here is my
3-line script for doing the backups:

  F=/var/adm/mysql.backup/backup.`/bin/date "+%Y%m%d-%H.%M"`
  /usr/bin/mysqldump --opt -A -uroot -prootpassword >$F
  bzip2 $F

I run that in a crontab once or twice a day; you could use logrotate to remove
older files if you want.

In theory you could restore your databases into a newer version of MySQL but
in practice I'd bet there are some migration issues.  I will need to do an
upgrade sometime myself because I'm still on 4.0; 4.1 and 5.0 have come out
since.

-rich


-- 
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.