Konstantin, You can take a cold backup of your data, that is shutdown MySQL and do a file copy. This is a static recovery. You still need your binary logs, or if a slave, appropriate replication configuration for this copy to become upto date with your production data, this is the point in time recovery.
The best backup approach does not involve stopping MySQL, because you loose many benefits like a hot cache for example. You can perform hot/warm backups using OS snapshot technology either from SAN or LVM, preferably on a replicated slave which enables you to control writing and does not affect your primary system. mylvmbackup is a good tool to use http://www.lenzg.net/mylvmbackup/ The amount of automatic recovery time necessary to restore your DB depends on the state of your data made during the backup snapshot. Ronald On Mon, Feb 22, 2010 at 2:42 AM, Konstantin K <[email protected]> wrote: > Guys, > > Thanks for the tips and info and links. > > As far as sharding goes, I have about 1000 shards (identical schemas) > on each machine. I have 4 physical machines to start. Eventually > when i get more machines, I will spread the shards (and load) to > include the newer machines. > > I've read some opinions of the innodb_file_per_table option and it > seems most mysql experts tend to not recommend it for reasons that > Ronald mentioned and others. So I probably will stick with the > default single global tablespace. > > Some more questions, > > 1. In my MySQL setup, /db is my data directory with all of the shards > in it, including the global tablespace for innodb. If I shutdown > mysql, cp the entire directory to a backup medium, will that work as a > raw backup? Will I be able to recover from these files later if I > need to? > > 2. Ronald, you mentioned that mysqldump is only good for smaller dbs, > what tools do you recommend for dbs (having only INNODB tables)? > > Thanks again! > Konstantin > > > > > On Sat, Feb 20, 2010 at 10:20 PM, Ronald Bradford > <[email protected]> wrote: > > Konstantin, > > > > Let me try to address some of your points, and also correct some of the > > inaccurate information in the response. > > > > mysqldump is a single stream export of data. With a mysql backup the most > > critical action is producing a "consistent" snapshot, that enables you to > > ensure a possible recovery. You can use --lock-tables to get a > consistent > > version for MyISAM, this is a blocking statement, for ALL InnoDB tables, > you > > can use --single-transaction which is non-blocking, however depending on > > your DB size, buffer pool size and log file size you may experience > > problems. mk-parallel-dump does not provide the "consistency" on a > running > > DB. Such consistency can only occur on a DB when no client access is > > available for the entire backup. mysqldump is also really only practical > > for small databases 5-10GB. > > > > innodb_file_per_table is not a good idea for default, there are plenty of > > arguments for not using it when you have a large number of tables because > of > > the increased data disk syncing. innodb_file_per_table does have a > number > > of benefits, some more significant then others, You talk about 1000 > shards, > > what is this? Is this schema's in your instance, or 1000 servers? > > > > There are several approaches to backups, they depend on your MySQL > topology, > > your storage engines, your required uptime, you application design for > read > > and read/write access etc. > > > > DRBD is not a backup solution, is a synchronous disk replication approach > to > > ensuring a consistent and high availability for fail-over. While in > theory > > you can break the DRBD replication to perform a consistent disk backup, > > that's a bad idea because you just broke your failover strategy. > > > > Disaster is enviable. It is critical you have a backup and recovery plan, > > you actually test it, you time it and you document it. Many organizations > > fail to do this, here is a checklist you need to ensure you follow -- > > > http://ronaldbradford.com/blog/checked-your-mysql-recovery-process-recently-2010-02-15/ > > > > Regards > > > > Ronald > > > > > > > > 01110010 01101111 01101110 01100001 01101100 01100100 00100000 01100010 > > 01110010 01100001 01100100 01100110 01101111 01110010 01100100 > > Ronald Bradford > > MySQL Expert specializing in Performance Tuning, Scalability and High > > Availability > > MySQL Community Member of the Year 2009 > > Co author of Expert PHP and MySQL. http://expertphpandmysql.com > > > > Web Site: http://ronaldbradford.com > > Linked In: http://www.linkedin.com/in/ronaldbradford > > Twitter: @RonaldBradford, @MySQLExpert > > > > > > > > > > On Sat, Feb 20, 2010 at 8:36 PM, Rob Marscher < > [email protected]> > > wrote: > >> > >> I think innodb_file_per_table is a good idea. I haven't seen any > arguments > >> for not using it. I don't have experience with the parallel dump > solution. > >> I've used replication to keep a live backup. I don't exactly remember > off > >> the top of my head but there's an option for mysqldump to not lock > innodb > >> tables during the dump. I think there's some caveats with that method > but > >> they were acceptable for my situation. I think details are in the > mysqldump > >> online documentation. > >> I believe there's a technique with DRBD too for quick backups. I seem to > >> remember some mysql experts talking about stopping the server using a > super > >> quick method to copy the mysql data dir. > >> The most important thing is to make sure you test recovering your system > >> from a backup. There are some horror stories of people not realizing > there > >> was a problem with their backups until they actually had a crash and > needed > >> to recover from it. > >> > >> On Feb 20, 2010, at 7:42 PM, Konstantin K <[email protected]> wrote: > >> > >> Hi guys, > >> > >> I've been reading Maatkit documentation, High Performance MySQL 2nd > >> Edition, and googling to try to learn what my options are in terms of > >> backing up and recovery of MySQL. > >> > >> For our database layer, we will be launching with several physical > >> machines, each of which will have MySQL 5.1, RAID-10 (hardware), NO > >> LVM, and will have about 1000 database shards, with all tables using > >> the INNODB engine. > >> > >> In the documentation for mk-parallel-dump (http://www.maatkit.org/doc/ > >> mk-parallel-dump.html), it says: > >> "...mk-parallel-dump is not a backup program! It is only designed for > >> fast data exports, for purposes such as quickly loading data into test > >> systems. Do not use mk-parallel-dump for backups..." > >> > >> But in the book, it seems to point that mk-parallel-dump can be used > >> as a backup solution. > >> > >> So my questions are: > >> 1. Can mk-parallel-dump be used to backup the innodb databases? > >> 2. Is it recommended to use innodb_file_per_table option? > >> 3. What do you recommend for specific documentation, methodologies, > >> techniques for raw and logical backups? > >> > >> Thanks, > >> Konstantin > >> > >> _______________________________________________ > >> New York PHP Community MySQL SIG > >> http://lists.nyphp.org/mailman/listinfo/mysql > >> > >> NYPHPCon 2006 Presentations Online > >> http://www.nyphpcon.com > >> > >> Show Your Participation in New York PHP > >> http://www.nyphp.org/show_participation.php > >> > >> _______________________________________________ > >> New York PHP Community MySQL SIG > >> http://lists.nyphp.org/mailman/listinfo/mysql > >> > >> NYPHPCon 2006 Presentations Online > >> http://www.nyphpcon.com > >> > >> Show Your Participation in New York PHP > >> http://www.nyphp.org/show_participation.php > > > > > > _______________________________________________ > > New York PHP Community MySQL SIG > > http://lists.nyphp.org/mailman/listinfo/mysql > > > > NYPHPCon 2006 Presentations Online > > http://www.nyphpcon.com > > > > Show Your Participation in New York PHP > > http://www.nyphp.org/show_participation.php > > > _______________________________________________ > New York PHP Community MySQL SIG > http://lists.nyphp.org/mailman/listinfo/mysql > > NYPHPCon 2006 Presentations Online > http://www.nyphpcon.com > > Show Your Participation in New York PHP > http://www.nyphp.org/show_participation.php >
_______________________________________________ New York PHP Community MySQL SIG http://lists.nyphp.org/mailman/listinfo/mysql NYPHPCon 2006 Presentations Online http://www.nyphpcon.com Show Your Participation in New York PHP http://www.nyphp.org/show_participation.php
