Hello guys. Does anyone know any good backup script for mySQL databases? I want to backup only the databases, nothing else. Anyone? Thank you.

Originally posted by Franki
Hello guys. Does anyone know any good backup script for mySQL databases? I want to backup only the databases, nothing else. Anyone? Thank you.

Just use mysqlhotcopy - it's probably the easiest and best for you.

(Another method would be to setup a second server, and let it replicate the databases off the master)

Jks,
Do you know where I could find this script? Thank you.

Originally posted by Franki
Jks,
Do you know where I could find this script? Thank you.

It comes with MySQL :-)

mysqldump --all-databases -p > dump.sql

works for me. what exactly are you trying to do, backup mysql to another server or just have a file with all of your mysql data in it?

If you already have PhpMyAdmin installed then there is a cool ( not so well know backup feature )

1. Select the database
2. Scroll down to View dump (schema) of database
3. Select all tables and then 'Structure and Data'
4. Click on 'Send' and 'Gzipped'
5. Press go

Now the server will dump the database, zip it and return it to you for download so you could save it on your local harddisk.

Originally posted by driverdave
mysqldump --all-databases -p > dump.sql

works for me. what exactly are you trying to do, backup mysql to another server or just have a file with all of your mysql data in it?

Yes, the commands works - but it's not so good an idea to use it when you have a MySQL server that is loaded and must be available.

mysqlhotcopy works better, as it doesn't lock up the MySQL server for as long time as mysqldump does. In addition the backup files doesn't take up as much space as with mysqldump. and in addition, mysqldump can have problems with certain kinds of BLOB columns, that mysqlhotcopy hasn't got problems with.

Originally posted by ASPCode.net
If you already have PhpMyAdmin installed then there is a cool ( not so well know backup feature )

1. Select the database
2. Scroll down to View dump (schema) of database
3. Select all tables and then 'Structure and Data'
4. Click on 'Send' and 'Gzipped'
5. Press go

Now the server will dump the database, zip it and return it to you for download so you could save it on your local harddisk.

This scheme has the same locking problem as mysqldump - however it is very nice for the occasional copying of a database.

Using mysqlhotcopy or mysqldump is a bit more "fun", as it can be done automatically from crontab. Not having to sit there and perform the backups yourself is quite nice :-)

How mysqlhotcopy works? What's the command for backing up a database called "franki"? What about the restoring? Thanx!

Originally posted by Franki
How mysqlhotcopy works? What's the command for backing up a database called "franki"? What about the restoring? Thanx!

Typing

man mysqlhotcopy

And/or reading the MySQL manual will answer your questions.

yes! i love phpmyadmin. the end.


Originally posted by ASPCode.net
If you already have PhpMyAdmin installed then there is a cool ( not so well know backup feature )

1. Select the database
2. Scroll down to View dump (schema) of database
3. Select all tables and then 'Structure and Data'
4. Click on 'Send' and 'Gzipped'
5. Press go

Now the server will dump the database, zip it and return it to you for download so you could save it on your local harddisk.

Well, I tried mysqldump and works perfect! One question: If I set up another server with mySQL installed (the same version) and copy the generated .sql file, will I have my database run?

Originally posted by Franki
Well, I tried mysqldump and works perfect! One question: If I set up another server with mySQL installed (the same version) and copy the generated .sql file, will I have my database run?

Yes, you can import the file this way:

mysql -uroot -pPASSWORD < mysqldumpfile.sql

Please be sure that you understand the consequences of using mysqldump instead of mysqlhotcopy.

Hi Jens

I know you love mysqlhotcopy ;-)

I'd like to implement this to my backup-script which uses mysqldump today. But I don't know how to backup all databases. i think i have to use a regular expression to do this but unfortunately I don't know very much about regular expression. I tried this and it does not work:

/usr/bin/mysqlhotcopy -u=root -p=password --regexp=* /tmp

Thanks
Daniel

Originally posted by dnid
i think i have to use a regular expression to do this but unfortunately I don't know very much about regular expression. I tried this and it does not work:
/usr/bin/mysqlhotcopy -u=root -p=password --regexp=* /tmp


Try using a dot instead of the asterix - that will give you regexp that always matches a databasename.

Ok, I know this issue is not about Cobalt RaQs but it's important. I want to change the default-character-set in mySQL. How can this be done from phpMyAdmin (or with a shell command)? Thanx!

anyone know of a good perl based script to back up a sql db, archive it and email it to you?

I tired the php one on here, but cant get it to run in cron

05 14 * * * php /xxxx/backupphp.php database

There is one that a lot of people use at :

http://www.vbulletin.com/forum/showthread.php?postid=94320

I havn't used it yet myself, but am looking into it. I'd actually like to modify it to use FTP instead of email. I don't want to receive nightly emails with 70MB database dumps. (If anyone has it running with FTP, I'd love to hear about it.)

Hope it helps.