List:General Discussion« Previous MessageNext Message »
From:Johan De Meersman Date:June 7 2011 8:27am
Subject:Re: Doubt regarding Mysqlsump
View as plain text  
----- 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
> 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
Doubt regarding MysqlsumpAdarsh Sharma7 Jun
  • Re: Doubt regarding MysqlsumpClaudio Nanni7 Jun
    • Re: Doubt regarding MysqlsumpJohan De Meersman7 Jun
      • Re: Doubt regarding MysqlsumpAdarsh Sharma7 Jun
        • Re: Doubt regarding MysqlsumpJohan De Meersman7 Jun
          • Re: Doubt regarding MysqlsumpKaren Abgarian9 Jun
            • Re: Doubt regarding MysqlsumpJohan De Meersman9 Jun
              • Re: Doubt regarding MysqlsumpKaren Abgarian9 Jun
              • RE: Doubt regarding MysqlsumpJerry Schwartz9 Jun
                • RE: Doubt regarding MysqlsumpWm Mussatto9 Jun
                  • MySQL loses password?Tim Thorburn12 Jun
                    • Re: MySQL loses password?Johan De Meersman12 Jun
                      • Re: MySQL loses password?Tim Thorburn12 Jun
                        • Re: MySQL loses password?Claudio Nanni12 Jun
                          • Re: MySQL loses password?Tim Thorburn13 Jun
                            • Re: MySQL loses password?Claudio Nanni13 Jun
                            • Re: MySQL loses password?Andrew Moore13 Jun
  • Re: Doubt regarding MysqlsumpNilnandan Joshi7 Jun
    • Re: Doubt regarding MysqlsumpAdarsh Sharma7 Jun
  • Re: Doubt regarding Mysqlsumpmark carson7 Jun