Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

the database has million of records, and will need to be backed up every data entry from the user.

share|improve this question
3  
Aren't you that guy who asks basically the same thing the fifth time now? I could imagine that people get annoyed by that. ("how to use mylvmbackup", "backing up database with large amount of data...", "mysqlhotcopy — A Database Backup Program", "How can I back up and replicate a large MySQL database?", "what is the best way to backup mysql database") –  PEra Oct 7 '09 at 13:19
    
Yes, they are. I couldn't think of a good reason to close the question though. –  David Pashley Oct 7 '09 at 13:35
    
Too generic of a question. The "best" way to back up a database involves various factors such as: How large of a dataset? Are offline backups acceptable? Is point-in-time restoration a requirement? What storage engine? etc –  Charles Hooper Oct 7 '09 at 14:30
add comment

closed as off-topic by Falcon Momot, Ward, mdpc, Michael Hampton Sep 27 '13 at 23:43

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Questions must demonstrate a minimal understanding of the problem being solved. Try including attempted solutions, why they didn't work, and the expected results. See How can I ask better questions on Server Fault? for further guidance." – Falcon Momot, Ward, Michael Hampton
If this question can be reworded to fit the rules in the help center, please edit the question.

4 Answers

up vote 6 down vote accepted

For small databases I'm personally use phpMyAdmin. For bigger databases (SQL dump is, let's say, more than 5MB) I prefer backing up from command line using standard MySQL tool mysqldump:

mysqldump -h localhost -u username -p database_name > backup_db.sql

Such way it is easy to get compressed database backup:

mysqldump -u username -h localhost -p database_name | gzip -9 > backup_db.sql.gz

NOTE:

I hope I understand your question correctly, though I'm a little fuzzed with this: and will need to be backed up every data entry from the user. Please explain more in details, so I can update my question.

share|improve this answer
add comment

We're doing it the following way:

We're using master-slave replication to replicate the database to a second server. Then we take an offline backup from the slave. To keep the downtime of the slave to a minimun we shut down the mysql server on the slave, create an lvm snapshot, restart the mysql server and then create a copy from the lvm snapshot.

We did use innodb hotbackup to directly create a backup from the master (we didn't have a slave by then) but this can take some time when you got a big database.

Best regards,

Stefan

share|improve this answer
add comment

There's not a lot to go on, but assuming this is an OLTP database with new data being added continuously I suggest you look into setting up a replicated instance, using mysqldump or mysqlhotcopy to periodically backup the replica.

This will ensure continuous mirroring of your data to the replicated instance while minimizing the strain on your production instance. Note that the replica in itself does not replace the need for a traditional backup, so you still want to backup the replica to tape or external storage.

share|improve this answer
add comment

...will need to be backed up every data entry from the user.

Really? How often will "data entry" (more precisely SQL INSERT, UPDATE, DELETE queries) happen? If it's very frequently, your requirement is likely impossible to fulfill.

Personally, I use the automysqlbackup script to manage versioned backups of my MySQL databases.

share|improve this answer
add comment

Not the answer you're looking for? Browse other questions tagged or ask your own question.