Dear all,

Let me know how can i take mysql server backup without stopping it. Also if some scripts are available for same. what are the thing should remember while taking a backup.

thanks


Ramchandra Dhupkar

I think mysql comes with its own backup tool but I forget what it is. Anyway, you could always simply backup the mysql database directory which is probably /var/lib/mysql.

Hi,

No! you cannot just backup the mysql dir... If you do you will end up with a non working database...


But mysql comes with two ways of doing the backups...

mysqldump -creates a full backup that you pipe into a file

example:

mysqldump -a -B test > backup_of_test.sql
creates a full backup of "test" database and puts it all into a file called "backup_of_test.sql".
To restore the file just do "mysql -p test < backup_of_test.sql"...

The other way is to do a mysqlhotcopy...

If you are new to mysql I suggest you use mysqldump, look at the man page for more extensive information...

Regards

Jonas

man i love it when threads i don't start apply to me and make my life easier.

I beg to differ. Although using mysqldump is the preferred way to backup a database you can simply copy the .MYI,.MYD, and .FRM files as well. It has worked for me numerous times in the past.

That is true only if your database in down, otherwise your database will (or can) be corrupt when you do a restore...

But you might be lucky and nothing at all happens whith your database and then it will work, but I wouldn't trust that backup... ;o)

//jonas

I've now searched about 25 threads on incremental backups, and there is usually a dead silence when it comes to the topic. Does anyone know of any way to back up directories incrementally, especially Lotus Notes databases?

Thanks in advance,
Sam

Notes database files are regular files! (if you are running a new version of Notes...)
So any tool that can do a incremental will do! (tar, cpiol,amanda)

The only thing you need to think about is to shut down notes before the backup to make sure no writes are done while doing the backup...

Regards

Jonas

Jonas,

No! you cannot just backup the mysql dir... If you do you will end up with a non working database...

How right you are!

I have linux on a desktop and on a laptop. I thought copy the directory overwrite the laptop directory /var/lib/mysql finish.

No way.

mysqldump -a -B test > backup_of_test.sql
creates a full backup of "test" database and puts it all into a file called "backup_of_test.sql".
To restore the file just do "mysql -p test < backup_of_test.sql"...

Could I run on my desktop mysqldump as above copy the sql file to floppy and do the restore on a seperate machine, like a laptop??

Yes,

That is "the right way" to do it. It will work great, the only thing you might have to adjust is the user. (if you had some other user on your laptop...)

Good luck

Jonas

Recently, we are talking like 2 days ago, I had to restore a database that wasn't working very well. I tried restoring from another backup solution, and only ended up screwing myself bigtime. Eventually, what worked for me was pulling out a full backup of /var and grabbing the files from /var/lib/mysql/DATABASE and copying them over, file by file (the imssing ones that the mysqldump seemed to have problems remembering to grab?) and got the pup restored.

So, it may be the "right way" but in my sad situation, if I had solely followed the "right way" I'd have been screwed. It took a combo of both "the right way" and apparently "the wrong way" to get things restored and in working order.



Cool

Hi MasterC,

Just to put the record straight...

I never said you couldn't restore from /var/lib/mysql (or where ever you put the files...) if you shut down mysql first...

The discussion was about a "hot" backup, i.e. taking a backup while the database is up. (in use) The problem you might get is that some table is in the progress of updating it's data and in that instance you copy the files -> thus getting a mixture of old and new data...

I say "might" because it is not sure that will happen. You might be able to take a backup that way 99 times of 100. The problem is the 100th time... ;o)

Regards

Jonas

Right on I meant nothing from that other than that you were both correct, especially since it took a combo of both of your ways to get mine back up when it jumped ship I understand and can completely appreciate "the correct" way to do things, and thank you for the information as such

Cool