CODEWITHSUNDEEP
mysqlbackupinnodb
BrownChiLD
BrownChiLD

Reputation: 3703

Copying MySQL InnoDB files

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.

Upvotes: 5

Views: 6732

Answers (3)

frozenfire
frozenfire

Reputation: 81

For the InnoDB tables there is one log file inside mysql>>data folder. Copy the log file "ibdata1" inside your data folder, the newly installed Phpmyadmin will show innodb tables too. Otherwise it'll only display MyISAM tables.

But have to take caution in one thing, if you already have ibdata1 file in your current data folder, replacing the ibdata1 file from backup may cause problem to current InnoDB tables, but if you are installing your backup database to newly installed Xampp/Wamp, it'll work.

Upvotes: 2

Ahmed M Farghali
Ahmed M Farghali

Reputation: 319

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

Upvotes: 8

Mats Kindahl
Mats Kindahl

Reputation: 2060

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.

Upvotes: 5

Related Questions