moving MySQL database

sovtek

I'm moving everything onto a new server running FC3 from a server running RH9.
It's running MySQL as a backend for a website and I've never backed up and migrated a MySQL database. Are there any good tutorials or references that someone could point out to me.
Sorry i don't have MySQL version information.

RobertFoster

Have you got PHPMyAdmin installed by any chance? There's the option to export the data to a *.sql file (just like a text file) which can be imported to another DB.

If you don't, then try locating the *\mysql\data folder. Each database is stored in a subdirectory of this directory and each table in that database are stored as a set of 3 files within this subdirectory (*.FRM, *.MYI, *.MYD). Just copy them and put them in the same place on the second database.

sovtek

i am using phpmyadmin. Cheers I will give that a bash.

K!LL!@N

RobertFoster wrote:

Have you got PHPMyAdmin installed by any chance? There's the option to export the data to a *.sql file (just like a text file) which can be imported to another DB.

This is probably the easiest way, but don't forget to tick the option for full inserts or you might end up with just the structure of the database and none of the data.

Killian

ssh

Use mysqldump:

mysqldump -u username -p databasename > databasebackup.sql

and then to restore, first use mysqladmin or phpmyadmin to create the database. Then do:

mysql -u username -p database < databasebackup.sql

handy thing to know for when you don't have access to phpmyadmin.

ressem

Vote 2 for mysqldump
creates a human readable SQL file with all your data +Db + table layout

You're going to want to learn about it if you ever need to run automated backups, or perhaps have to modify data tables due to changes in business logic.

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

niallb

This tool is brilliant - written in perl.
http://partisans.spurious.biz/~pkremer/projects/scripting/mysqlblasy.php

It'll back them up safely and you can restore as in ssh's post.

NiallB

rsynnott

RobertFoster wrote:

If you don't, then try locating the *\mysql\data folder. Each database is stored in a subdirectory of this directory and each table in that database are stored as a set of 3 files within this subdirectory (*.FRM, *.MYI, *.MYD). Just copy them and put them in the same place on the second database.

Never do this; it'll potentially break the database in slow, non-obvious ways, if you're using a different version. (I also get the impression that those are in the system's native -endian order, so Horrible Things will happen if you move from PC to Mac or Sparc, for example)