MySQL - Dump and Bin-Logs
Date: March 07, 2006 08:54AM
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.
I'm using MySQL version 5.0.18 on Windows 2003 Server. 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. (I'll be scheduling a Java Ant task to run & compress the results of mysqldump).
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)