the database has million of records, and will need to be backed up every data entry from the user.
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
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.
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.
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.
...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.