Does anybody know any good software for backing up MySQL databases?

My database should be backed up but MySQL control centre does not allow you do this. Does anybody know of any free software which would allow a good backup/restore option? I know such a program exists but can't remember the name.

After the recent 34SP.COM I need to back up my forum, gig listings and band profiles as the only place there are stored is a database on their servers.

Thanks.

 

AT, I came across these:

http://freshmeat.net/projects/phpmysqlbackupro/?branch_id=49350&release_id=170049

http://members.lycos.co.uk/wipe_out/automysqlbackup/

Don't know if they would be of any use to you.

 

Just run a batch file to xcopy or cp the data files to a separate location periodically. It's an easy job to reinstate the data from a copy, have a look at the documentation at www.mysql.com.

Alternatively, you can use the mysqldump program that is installed with mysql, on a windows box you need something like:

c:\mysql\bin\mysqldump --opt -u username --password=password databasename > c:\somebackuplocation\backup.sql

(Obviously, change the paths appropriately.) Stick that in a batch file and either schedule it or run it manually every so often. On a *nix box, the syntax is exactly the same apart from the path specification, which is more like:

/usr/bin/mysql/bin/mysqldump --opt -u username --password=password databasename > /home/foo/mysqlbackup/backup.sql

Again, read the mysqldump docs for the procedure to restore.

-- Ian

 

I use mysqldump. In fact, I have a cron job setup that, every day at 3am does the mysqldump, gzips up the dump file and then FTP's it to a different server I have - all in one command line...don't you just love Unix pipes

Michael.

 

Thanks I will install MySQL tonight as I only have the version on the host server at the moment

 

You only need the version on the host server. The way that 34sp.com (and many other hosting providers) configures their MySQL databases means you can't "connect" to them from an external (external to 34sp.com) host so tools like MySQL-Front won't work.

mysqldump comes with MySQL and you can run it on the host server. When the 34sp.com forums come back up, do a search there and you should find what you need.

Michael.

 

Thanks, hopefully the forums will be back up soon unless their hard drive has crashed. Normaly I would have asked the question there but it has been down since Monday I think.

 

you can use plesk on 34sp to get into phpmysqladmin, and u can backup from there

 

Thanks I have just spotted the backup button now. I didn't realise they would make it that easy. I have a dump file so I assume that will restore the database as well?

 

Yes, forgot to mention that you can run the phpMySqlAdmin backup but that tends not to work very well for large databases (it wouldn't work for the ZG database) - the same applies to all PHP backup scripts. The ZG database is around 100Mb btw incase you wondered what I meant by large.

Michael.