MySQL Forums
Forum List  »  Backup

MySQL - Dump and Bin-Logs
Posted by: masga 2000
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)


Options: ReplyQuote

Written By
MySQL - Dump and Bin-Logs
March 07, 2006 08:54AM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.