Hi all,

I have one doubt, is copying /var/lib/mysql is a good alterntive to mysqldump?.

Because i use rsync to copy /var/lib/mysql for back up without dumping the database. I use rsync to do differential backup up so that it copies /var/lib/mysql to /var/tmp every one minute.

Does this idea worths or will it create any other problems.

Your suggestions please.

Thanks&regards,
Dinesh.

Hi Dinesh,

I have used BackupPC and rsync in the past to backup (and restore) a live MySQL database. Granted, when I restored, I stopped the process so further "live" changes weren't made while I was trying to restore.

1 members found this post helpful.

Hi cyberdeath,

Thanks for reply,

So that point is you did it by rsync and while restoring we need to stop the mysqld service.

Have you faced any other problems because of copying mysql using rsync instead of mysqldump.

Kindly share your suggestions, it would be very helpful.

Hello again Dinesh,

No, I actually did not experience any problems doing the data restore. Basically, to put it simply, someone decided to delete the entire MySQL database (don't ask me how) and I was able to restore the entire database using the files that were backed up from rsync. We did not experience any database or other problems as a result of this if that is what you are asking. I have also restored a database after some wrong data was entered, which also worked fine.

Overall, I personally found that rsync did and does a better job at doing the data backup compared to MySQL dump. However, that is not to say they don't both have their place. I have personally used mysqldump when doing upgrades or changes to the system to add a bit of "redundancy" in there in case rsync didn't work during these times. I also used to use mysqldump monthly but I stopped doing it.

Hopefully this helps clarify things. Please let me know if I can be of further assistance.

1 members found this post helpful.

Hi cyberdeath,

Thanks,

In my case i use to rsync the mysql data for every minute. so whenever mysql crashes or assume that some unwanted thing happens.

The next minute the same changes will be written to the destination(Back up)folder.Then is it possible to restore my database.

That is a duplicate of the posting you made in your earlier thread -- http://www.linuxquestions.org/questi...4/#post4231260.

See my comments on that thread.

Basically, you may get away with simply backing up /var/lib/mysql, but you run the risk of having inconsistencies in the database when you recover. It may work many times, and then you may encounter the time when it doesn't. You will end up having to get deeper into MySQL at that point to repair things.

1 members found this post helpful.

Simply copying files won't work with all storage engines. It will work with MyISAM; but I'd recommend you to use mysqlhotcopy or mysqldump to avoid problems with data inconsistencies.

1 members found this post helpful.

Hi,

Thanks for your replies.

But kindly provide me how to take differential backups in mysql.I googled for it,and found that mysqld service has to be started --log--bin option.But i edited /etc/my.cnf file but mysqld didn't started.I use mysql 5.0 version.

So can you just provide me any link or tip for takilng differential backups using mysqldump.

And also can you please provide me the command used for differential backup using tar. i googled and found many for incremental backup but not for differential.

Two points here, since your post is going in two different directions now.

Differential is just a particular type of incremental that some vendors talk about. See the definitions here: http://wiki.zmanda.com/index.php/FAQ...da_use_them%3F . Thus, if you can do incremental with tar, you can do differential. They just aren't going to make a specific case for that and call it by that name.

Back to MySQL. Things like mysqldump do full dumps. If you want incremental, you need the binary log: http://dev.mysql.com/doc/refman/5.1/en/binary-log.html. You don't want to be messing with file system backups (like tar) trying to do incremental on your database files.

If you changed your my.cnf and are having trouble starting mysql, tell us exactly what you changed and what errors you see on startup.

1 members found this post helpful.

Hi choogendyk,

Thanks for your suggestion,my doubt regarding the tar has been solved.I got a way for that.

Now i enabled Binary logging in my mysql db by editing the my.cnf file like this.

[mysqld]
datadir=/var/lib/mysql
log-bin=/var/lib/mysql/
socket=/var/lib/mysql/mysql.sock
user=mysql

Now my mysql server runs with binary logging enabled.

But how to use mysqldump now.How mysqldump command reads the log and dump only the changes in database, how this can be acheived.

Since i am very new to this,I am finding this pretty much difficult.

You are pretty much there.

You don't do incremental dumps. The binary log is the incremental. You use mysqldump to dump everything and the binary log to recover to a particular point in time (incremental).

See http://download.oracle.com/docs/cd/E...-recovery.html for details.

You can manage all this yourself, or you could implement Zmanda Recovery Manager for MySQL. It's best if you understand doing it yourself first, though.

1 members found this post helpful.

Hi choogendyk,

I have another question.we have seperate databases for each client in our mysql.

For example assume we have x,y,z databases, each belongs to different clients.

if "y" database has to be restored with half an hour before data through the bin log file.

Then it will affect "x" and "z" databases also, is there a way to overcome this problem.

Is there a way to restore only a particular database from the binlog file.

I believe the trick is to use the mysqlbinlog utility to select from the binlog file only those entries pertaining to the database you want to recover -- http://dev.mysql.com/doc/refman/5.0/en/mysqlbinlog.html.

This is probably what Zmanda Recovery Manger for MySQL is doing when you do a selective recovery. See the example near the end of this page -- http://wiki.zmanda.com/index.php/Mysql-zrm-restore.

Of course, you need to do the full recovery of that database and then do the binlog incremental to bring it up to where you want.

1 members found this post helpful.

Hi choogendyk,

Once again thanks, this looks cool and great.it works fine in my local environment.

Soon it will be applied on production.

But Whenever i restore my database with mysql full dump and binary logs,do i need to clear all my binary logs.

I believe it's described in the documentation links in my previous posts. Typically, I think, you would synchronize full backups and binary logging.

Suppose you have a full backup and binary logging is enabled. Now you are going to do another full backup. Assuming you are going to be keeping your previous full backup. As part of the procedure for stopping database activity (locking tables), you also stop logging. Then copy the binary log to a backup location associated with the previous full backup. Now, when you do the mysqldump, use the --flush-logs option. Then start up the logging again and unlock the tables. This way, you have the full dumps and the binary logs that correspond to the incremental sequences from one full dump to the next as well as a clean start to logging for the new full dump. Of course, you then have a policy for aging those out and tossing them.

I'm not sure exactly how the sequence goes in doing a full and then incremental recovery. If you are bringing it all the way back up, then you should leave the binary log as is at the end. Then start things back up with logging again. But, how to manage a recovery to some particular point in time. Perhaps the binary log gets copied out to the backup location, and then you edit the log to include only the portion you want to use for the recovery. Then proceed as in the case where you recovered the whole binary log. I haven't done that before, so I'm not sure. It sounds logical, but it could get messy if you are partially recovering only one of several databases. The documentation should give the details. In particular, this tutorial might be helpful: http://www.learn-mysql-tutorial.com/BackupRestore.cfm

1 members found this post helpful.