----- Original Message ----- > From: "Adarsh Sharma" <adarsh.sha...@orkash.com> > > 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 > question. 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
