Professional Web Applications Themes

backups - MySQL

Hello, I would like to make daily automatic full backups of my databases. I've read a few articles/books about that topic in several doents without finding any acceptable solution. I'm able to backup my Myisam and innodb tables by hand (with mysqldum) while keeping a consistent state for each db. Is it possible to automate that, ie to 1) make a list of all tables 2) define for each table if it's an Innodb or Myisam one 3) backup each table with mysqldump with the options needed by the format of the table (myisam or innodb) ? Thanks a lot ...

  1. #1

    Default backups

    Hello,

    I would like to make daily automatic full backups of my
    databases. I've read a few articles/books about that topic in several
    doents without finding any acceptable solution. I'm able to backup
    my Myisam and innodb tables by hand (with mysqldum) while keeping a
    consistent state for each db.

    Is it possible to automate that, ie to 1) make a list of all tables 2)
    define for each table if it's an Innodb or Myisam one 3) backup each
    table with mysqldump with the options needed by the format of the
    table (myisam or innodb) ?

    Thanks a lot for yor answer
    -AJ
    Antoine Junod Guest

  2. #2

    Default Re: backups

    "Peter H. Coffin" <hellsopninehells.com> writes:
    > On 21 Jul 2006 14:49:17 +0200, Antoine Junod wrote:
    > > I would like to make daily automatic full backups of my
    > > databases. I've read a few articles/books about that topic in several
    > > doents without finding any acceptable solution. I'm able to backup
    > > my Myisam and innodb tables by hand (with mysqldum) while keeping a
    > > consistent state for each db.
    > >
    > > Is it possible to automate that, ie to 1) make a list of all tables 2)
    > > define for each table if it's an Innodb or Myisam one 3) backup each
    > > table with mysqldump with the options needed by the format of the
    > > table (myisam or innodb) ?
    >
    > Which of the above does mysqldump *not* do for you?
    mysqldump -u root --all-databases > backup.sql locks table after table
    and thus permits to change a table within a db while another table of
    that db is backuped. That implies that a record of the first table
    point onto a modified (inexistant) record on the second
    table. Consistency is not guarenteed.

    The solution is the option --lock-all-tables. But I read in "The
    definitive guide to MySQL 5, Apress", page 354, that this options
    solves nothing for innodb tables (why?). In the same place, they
    propose to use the following set of commands for backuping InnoDB
    tables;

    mysqldump -u root -p --skip-opt --single-transaction \
    --add-drop-table --create-options --quick --extended-insert \
    —set-cht —disable-keys databasename > backup.sql

    That is what i'm using now. It's mean i've two different commands, one
    for the myisam tables and one for the innodb tables. My current backup
    script has a list of myisam tables and another list for innodb
    tables. These lists are hand made. I would be able to build them
    automaticaly. Is there a way to do that?

    Or, more simply, is there a set of mysqldump options that are doing
    what I want and that is common to both tables type?

    Thans for your reply
    -AJ
    Antoine Junod Guest

  3. #3

    Default Re: backups

    Antoine Junod wrote:
    > The solution is the option --lock-all-tables. But I read in "The
    > definitive guide to MySQL 5, Apress", page 354, that this options
    > solves nothing for innodb tables (why?).
    One doesn't need to lock tables to get a consistent read, if the tables
    support transaction isolation.

    Read about REPEATABLE READ here:
    [url]http://dev.mysql.com/doc/refman/5.0/en/innodb-transaction-isolation.html[/url]
    > My current backup
    > script has a list of myisam tables and another list for innodb
    > tables. These lists are hand made. I would be able to build them
    > automaticaly. Is there a way to do that?
    The following query should get you the information you need (assuming
    you using MySQL 5.0):

    SELECT t.table_schema, t.table_name, t.table_type, t.engine
    FROM INFORMATION_SCHEMA.tables t;

    The `engine` column has values 'InnoDB', 'MyISAM', 'MEMORY'. For views,
    the column is NULL.

    See [url]http://dev.mysql.com/doc/refman/5.0/en/tables-table.html[/url]

    Regards,
    Bill K.
    Bill Karwin Guest

  4. #4

    Default Re: backups

    Bill Karwin <billkarwin.com> writes:
    > Antoine Junod wrote:
    > > The solution is the option --lock-all-tables. But I read in "The
    > > definitive guide to MySQL 5, Apress", page 354, that this options
    > > solves nothing for innodb tables (why?).
    >
    > One doesn't need to lock tables to get a consistent read, if the
    > tables support transaction isolation.
    >
    > Read about REPEATABLE READ here:
    > [url]http://dev.mysql.com/doc/refman/5.0/en/innodb-transaction-isolation.html[/url]
    >
    > > My current backup
    > > script has a list of myisam tables and another list for innodb
    > > tables. These lists are hand made. I would be able to build them
    > > automaticaly. Is there a way to do that?
    >
    > The following query should get you the information you need (assuming
    > you using MySQL 5.0):
    >
    > SELECT t.table_schema, t.table_name, t.table_type, t.engine
    > FROM INFORMATION_SCHEMA.tables t;
    >
    > The `engine` column has values 'InnoDB', 'MyISAM', 'MEMORY'. For
    > views, the column is NULL.
    >
    > See [url]http://dev.mysql.com/doc/refman/5.0/en/tables-table.html[/url]
    Thanks for your reply and the links. That's helped a lot.

    -AJ
    Antoine Junod Guest

Similar Threads

  1. Coldfusion MX7 Backups
    By C_A_Braun in forum Coldfusion Server Administration
    Replies: 0
    Last Post: March 28th, 12:15 AM
  2. Backups not Staring...
    By Gawie Marais in forum Windows Server
    Replies: 3
    Last Post: June 28th, 07:28 AM
  3. HDR & backups
    By Dirk Moolman in forum Informix
    Replies: 2
    Last Post: October 1st, 09:11 AM
  4. RAC, CFS and online backups
    By Connor McDonald in forum Oracle Server
    Replies: 5
    Last Post: December 9th, 07:10 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139