Backup MYSQL by data folder

Tea_J

Now i know there are lots of ways to backup your precious database.. however i've not heard or read a single comment regarding this backup method.

I have a program that uses a mysql database. I wanted the best, and easiest way to backup mysql databases everyday and I figured I'd just have my program copy the mysql>data folder to a safe location..

This seems to work but i want to hear about your comments on this.. is my way ok?

i could use dump, and export stuffs.. but i really find the above method the easiest for my particular task

Tea

bastien

What is the table type? For standard MyISAM tables, this is acceptable.

For InnoDB or MaxDB tables...this will hose you...you will need to do proper backups as described in the manaul

Tea_J

im using standard myISAM.. 🙂 so it's ok? cool.. hey i forgot the difference between these tables.. can ya give me a refresher? what's the difference between these types of tables again? what do you use? and why?

thanks

tea

bastien

from the manual

The original storage engine was ISAM, which managed non-transactional tables. This engine has been replaced by MyISAM and should no longer be used. It is deprecated in MySQL 4.1, and will be removed in MySQL 5.0.

In MySQL 3.23.0, the MyISAM and HEAP storage engines were introduced. MyISAM is an improved replacement for ISAM. The HEAP storage engine provides in-memory tables. The MERGE storage engine was added in MySQL 3.23.25. It allows a collection of identical MyISAM tables to be handled as a single table. All three of these storage engines handle non-transactional tables, and all are included in MySQL by default. Note that the HEAP storage engine now is known as the MEMORY engine.

The InnoDB and BDB storage engines that handle transaction-safe tables were introduced in later versions of MySQL 3.23. Both are available in source distributions as of MySQL 3.23.34a. BDB is included in MySQL-Max binary distributions on those operating systems that support it. InnoDB also is included in MySQL-Max binary distributions for MySQL 3.23. Beginning with MySQL 4.0, InnoDB is included by default in all MySQL binary distributions. In source distributions, you can enable or disable either engine by configuring MySQL as you like.

NDBCluster is the storage engine used by MySQL Cluster to implement tables that are partitioned over many computers. It is available in source code distributions as of MySQL 4.1.2.

Tea_J

transactional tables means tables that can use foreign keys right? Gosh, i cant believe i forgot all these stuffs

bastien

Nah, transactional tableshave tha ability to use transations

begin transaction
do some sql (can be 1 or more statements)
end transaction

Using transactions says that if any of the sql statements fail, the entire set of sql statements is rolled back and nothing is changed. This way, you get around the ACID nature of the MyISAM engine, where partial DML is allowed (ie some fields are updated but not all). In many operations, like ecommerce or banking, you want an all or nothing set of operations.

foreign keys are there for referential integrity, where a child record can not exist without the parent.

Tea_J

ahhhhhhh.... so that's the advantage of InnoDB! Now i get it.

thanks man! awesome help.

Tea