-12

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

Improve this question
3
  • 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, 2009 at 13:19
  • Yes, they are. I couldn't think of a good reason to close the question though.
    – David Pashley
    Oct 7, 2009 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, 2009 at 14:30

4 Answers 4

Reset to default
6

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.

Improve this answer
3

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

Improve this answer
3

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.

Improve this answer
2

...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.

Improve this answer

Not the answer you're looking for? Browse other questions tagged .