Copying MySQL InnoDB files

backup, innodb, mysql

Solution

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

Problem

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.

Original source