Copying MySQL InnoDB files

Question

I know copying mySQL InnoDB Files (frm, etc) is asking for trouble. But what if i copy the whole MSYSQL directory as is? bin, data, docs, etc.. will that work? I believe it should work. Am i right?

I don't think MySQL does anything outside the MySQL folder unless explicitly set.

To those wondering why I need to do this, I build intranet applications and I usually make simple 1 click WINDOWS BASED INSTALLERS for these things. This means preparing the WAMP/XAMPP SERVER state, configs and all on a dummy machine, then taking a snapshot of that.

Answer 1

From command line, Back-up to SQL file:

mysqldump -u username -p --lock-tables DB1 > database-backup.sql

for multiple databases

mysqldump -u username -p --lock-tables --databases DB1 DB2 DB3 ... > database-backup.sql

for all databases

mysqldump -u username -p --lock-tables --all-databases > database-backup.sql

use of "--lock-tables" to prevent database access while dumping.

to import a database :

mysql -u username -p DB1 < database-backup.sql

for multiple databases:

mysql -u username -p < database-backup.sql

Answer 2

Yes, you can create a backup of the database using this approach, but you need to stop the server before doing that or run the risk of having inconsistent data. If you have only MyISAM table, you can usually get away with doing a FLUSH TABLES WITH READ LOCK, copy the directory, and the UNLOCK TABLES.

See http://dev.mysql.com/doc/mysql-backup-excerpt/5.0/en/backup-methods.html for more info.