----- Original Message -----
> From: "Adarsh Sharma" <adarsh.sharma@stripped>
> Not getting U'r point marked as bold & Underline
I checked up in the mean time, and it does not make a truly consistent backup of MyISAM -
it locks all tables - yes, ALL tables - and then copies the files. Given that MyISAM
doesn't support transactions, that means that any "transactions" (that is, sets of related
queries) in progress will be copied in the state they were, and the backup may contain
inconsistent data like unreferenced rows.
> I am not using xtrabackup but I think --single-transaction & -q
> options may solved this issue
> I know this is valid only for Innodb Tables but anyway's I have
> both MyISAM & Innodb tables but only Innodb tables size is increasing in
> seconds and MyISAM table size increased after hours.
-q is good, but not relevant to your problem. It simply prevents buffering of the output,
which speeds up the dump a bit.
--single-transaction, as you say, only affects InnoDB tables.
--lock-all-tables will give you the same behaviour for MyISAM as xtrabackup, but is likely
to be incompatible with --single-transaction.
Neither of those solve your consistency issue.
> Can U Please explain me what happened when I issue the mysqldump
> command with options --single-transaction & -q option on Innodb tables of
> size> greater than 100 GB & on the other hand my application continuously
> insert data in the tables.
The size isn't particularly relevant; --single-transaction basically "snapshots" the
InnoDB tables so you copy all tables from the same point-in-time. I'm not 100% sure, but I
think this is a pure read lock, so the inserts continue to happen; your backup process
will not see them, but other processes will. This ensures a consistent InnoDB backup.
> Please note that I don't have my bin-log enabled.
> I can enable it if required.
It is ovbiously necessary if you choose to do binlog backups :-p
> Yet I am not able to find the finalize the answer of the original
The answer to "is there a way to take consistent backups of MyISAM tables without stopping
the application" is "no, there is not". The binlog backup strategy I roughly outlined
earlier can dramatically decrease your application's downtime, however.
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel