Backing up/restoring MySQL databases?

[Thorin]

Is it just a case of copying the data folder?

[Mike]

I think that's all we do...

[Whitesupraboy2]

I dont know specifically Mysql. But in SQL the best and easiest way if disk space isnt a problem is to backup the database and then restore it. Of course you can move the data folder but you then need to attach the databases to the new server.

 

I know mysql is slightly different but must be a similar concept.

[Thorin]

Well there's no backup option that I could see, unless there's some other utility to do it then I'll just copy the folder off elsewhere and hope it works when I set the new server up.

[Pete]

I suggest you bookmark - http://dev.mysql.com/doc

and read - http://dev.mysql.com/doc/refman/5.1/en/disaster-prevention.html

[Mike]

Not sure if it has a backup option but we use Navicat for MySQL stuff... very useful if you don't use it already.

[Keith C]

You can either do a mysqldump, or a mysqlhotcopy, or shut down the mysql instance and copy the datafiles. Copying running datafiles is hazardous and not to be recommended.

[Thorin]

You can either do a mysqldump, or a mysqlhotcopy, or shut down the mysql instance and copy the datafiles. Copying running datafiles is hazardous and not to be recommended.

 

Ok I'll stop that then

 

Cheers for the link Pete, I was lazy and thought I'd get a quick response on here. I'll have a go with this mysqldump thing...

[Mike]

I'll have a go with this mysqldump thing...

 

Me too...

[sdavies]

mysqldump is quickest and easiest I believe (well it is what I do here anyway).

 

mysqldump -u user -h host database > databasedump.sql

 

lots of funky options with that too, but to restore you simply have to either drop your db and recreate from the mysql instance, then from the command line something like:

 

mysql -u user -h host

 

(depending on specifics of your setup)

[Thorin]

balls to mysqldump, there's loads of databases on there. Shut it down, copied the data folder and will see if it works

[jeremyh]

To back up MySQL you usually have the option of backing up the table structures, the data itself, or both.

 

You can have the structure dumped into a text file and run it as a form of INSERT query when you need to.

 

I wanted to add it's probably not a good idea just copying the data folder, you will have the data, but you will probably lose the table structures and you would end up having to recreate these manually (!) if you don't already have a backup.

[Guru]

I use a program called EMS MySql Manager 2005 Lite. Has an option to backup everything including data

[Pete]

I use a program called EMS MySql Manager 2005 Lite. Has an option to backup everything including data

 

I use that too. It's a bit clunky, but get the job done.