# MySQL Innodb backups/restore?



## klabacita (Jul 15, 2014)

Hi.

How are you dealing with the innodb backup process? My doubt is, if I follow the cool backups, I copy the files the manual say, suppose that we have 2 databases innodb engine.


```
db1
db2
```

Later I need to restore the data from yesterday for db1. How I can just recover the data for this database? 

Any info will be appreciated.


----------



## SirDice (Jul 16, 2014)

With InnoDB you can't simply copy the files. Use mysqldump(1) to back up your databases.


----------



## klabacita (Jul 16, 2014)

Hi.

The manual say u you can use mysqldump but is not a warranty that will work for InnoDB, u you can lose data. 

I use that command for MyISAM.


----------



## SirDice (Jul 16, 2014)

I've used mysqldump on some pretty big InnoDB databases for many years, we've never had an issue with it.


----------



## klabacita (Jul 16, 2014)

@SirDice, what parameters are u you using?


----------



## SirDice (Jul 17, 2014)

I can't remember we used any specific settings, just the plain mysqldump(1).


----------



## gkontos (Jul 17, 2014)

It is always a good idea to lock the tables when performing `mysqldump`.


----------



## usdmatt (Jul 17, 2014)

The information here seems to make reasonable sense.
http://dba.stackexchange.com/questions/ ... and-writes

(This sort of question seems more suited to DBA StackExchange than FreeBSD forums)

Regarding restoring just one DB, I tend to backup each database separately to dbname.sql, which makes this easy. If you only have a few databases that don't change often it's easy enough to just hard code your backup script to run `mysqldump` for each one. It's also possible to automatically get a list of databases. A few ways I've done this in the past is to either get the script to connect to MySQL and run `show databases`, or use the system `ls` command to get the contents of /var/db/mysql.


----------



## gkontos (Jul 20, 2014)

You might also want to check http://sourceforge.net/projects/automysqlbackup/ AutoMySQLBackup. It has some very nice features.


----------

