MySQL - Dump and Bin-Logs

[b4u]

Hi,

The objective will be to make daily backups and, in case of failure, restore the dumps AND the data after the dump (which is stored on the bin-logs).

I'm trying to understand the MySQL backup, restore and binary logs, and have come into some questions.

In my.ini config file, I have the option:

This makes mysql create the following files:
02-02-2006 11:17 29.777 mysql-bin.000001
02-02-2006 11:34 117 mysql-bin.000002
02-02-2006 11:38 117 mysql-bin.000003
02-02-2006 15:44 117 mysql-bin.000004
08-02-2006 15:33 59.274 mysql-bin.000005
10-02-2006 12:38 1.775 mysql-bin.000006
11-02-2006 16:48 15.923 mysql-bin.000007
19-02-2006 15:56 341 mysql-bin.000008
19-02-2006 16:22 117 mysql-bin.000009
20-02-2006 12:30 3.990 mysql-bin.000010
21-02-2006 12:30 152.742 mysql-bin.000011
21-02-2006 15:23 8.471.198 mysql-bin.000012
22-02-2006 11:02 174.025.435 mysql-bin.000013
22-02-2006 11:09 141 mysql-bin.000014
22-02-2006 11:10 117 mysql-bin.000015
22-02-2006 12:22 11.577.176 mysql-bin.000016
22-02-2006 12:22 117 mysql-bin.000017
22-02-2006 12:23 141 mysql-bin.000018
22-02-2006 12:23 98 mysql-bin.000019
22-02-2006 12:23 361 mysql-bin.index

So as I undestand, everytime I startup the server, a new file is created and added to the index file (the next restart will create mysql-bin.000020).

Now I'm trying to get rid of the files, and first of all, I do a "FLUSH LOGS" on the console. At the point, it just creates a new file (a new bin-log), but it doesn't remove any data ... am I safe to delete the files? And the index one?

I'm going to schedule a daily backup on a windows system. So I'm thinking about taking the following steps:
1. Do a mysqldump into a text file, with a timestamp on it's name;
2. Zip the txt file for compact the data, and move it to another hard disk (and delete the txt file);
3. Flush the bin-log, since I will have lattest data in the dump file.

Now that just makes me think:
Between the dump start and the flush, how can I assure that no data is changed on the database? Is someone changes data, it will be logged into the bin-log, but then I flush/delete the bin-logs, so I lose the data.


Thanks

 

[Cheetah8799]

I'm not sure what MySQL version you are using, but in the older 4.x versions you could simply zip up the database files and save them somewhere. Then copy them back if you need to re-build everything from scratch. That doesn't help with re-building bad data inbetween archives, but it's a way to do it.

As for ensuring that no data changes between dump/flush. I think you can lock the database(s) during the process, then unlock again when the script is done. I don't know off hand what the commands are, but I vaguely recall reading about it long ago...

 

[b4u]

Hi,

I'm using MySQL version 5.0.18. I personally would prefer using mysqldump to generate plain sql text file with all data from the database, the problem is assuring I lose no data.

Does the mysqldump automatically locks the database while dumping the data?

Then, I would I assure that between mysqldump and deleting of bin-log files I will loose no info? Or how would be the best practice to do it without loosing data? (if for instance the database is backing a 24/7 website)

Thanks

 

[b4u]

* bump of luck *