Are there any mysql scripts for taking a database backup by excluding specified tables?

Also, what is the best way to take backup of a running mysql system?

  • What OS? Community or Enterprise? InnoDB or MyISAM or ... ? – gbn Nov 30 '11 at 9:54
  • If you are not sure, connect to MySQL, type use information_schema; and then type: select table_schema, table_name, engine from tables where table_schema='database_name'; – Craig Efrein Nov 30 '11 at 9:59
  • 1
    possible duplicate of How do to mysqldump specific table(s)? – Gaius Dec 16 '11 at 22:45

You can backup databases and tables using the mysqldump command. There are parameters for excluding certain tables.

mysqldump -u username -ppasswword -h servername --ignore-table=dbname.tabletoskip dbname

If you need to get fast backups of MyISAM or ARCHIVE tables, you can also use mysqlhotcopy. That has more restrictions though.

  • 1
    This is as StraightForward an answer as possible. +1 !!! – RolandoMySQLDBA Nov 30 '11 at 18:20
  • Yeah, it is best to start with the simple solutions, and only work up to the complex home-brew scripts when you know you need it. – Cylindric Dec 1 '11 at 9:25
  • Thanks Cylindric..It worked for me for excluding one table..How to exclude more than one table – Praveen Prasannan Dec 5 '11 at 5:17
  • Can we use this method for backing up a running DB – Praveen Prasannan Dec 5 '11 at 5:31
  • 1
    Like it says on the page I linked, "To ignore multiple tables, use this option multiple times." – Cylindric Dec 5 '11 at 9:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.