# automatic backup mysql databases



## almobd3 (Jun 8, 2011)

As you can see, I want a script that I can automatically back up my databases. For example I want to back up db1, db3, db4, not backing up the db2. How to do it?

What I mean: not to dump it to .sql but to .gzip or .tar.gz only, without dumping. I want to do it every six hours.


----------



## UNIXgod (Jun 8, 2011)

Look into the mysqldump man page, read the tar man page, as well as the cron man page.


----------



## SirDice (Jun 8, 2011)

`# mysqldump -u user -p password database | gzip > mybackup.gz`


----------



## almobd3 (Jun 8, 2011)

What I want is compressing the database folder as it is 

```
.frm
.MYD
.MYI
```
Compressing it as it is without dumping. I want a script to do it. I used AutoMySQLBackup but it saved it as .sql file.
Hope you have an answer.


----------



## almobd3 (Jun 11, 2011)

DutchDaemon would you please answer me? Or anyone?


----------



## DutchDaemon (Jun 12, 2011)

Don't mistake me for your personal helpdesk.


----------



## mix_room (Jun 16, 2011)

Do you just want to copy the files where the database is stored? Why do you want to do this? The files might not be consistent, there might be problems with restoring etc etc. 

What is the real problem that you want to solve? Why are the .sql-files not ok?


----------



## gkontos (Jun 16, 2011)

Store your /mysql directory in a ZFS pool and use snapshots for the backups.
Send the snapshots to a different location compressed or native.

Hope that helps.


----------



## SirDice (Jun 17, 2011)

Keep in mind that you can indeed just backup the files from a MyISAM database. However, it does NOT work when dealing with InnoDB.

In short, mysqldump is the only way to back up properly.


----------



## almobd3 (Jun 17, 2011)

I need a backup of the files as it is. So please if anyone has a script that can do such a thing I'll be grateful.

Thank you all for your help.


----------



## SirDice (Jun 17, 2011)

How hard can it be?

`# tar -zcvf my_db_backup.tgz /var/mysql/`


----------



## almobd3 (Jun 18, 2011)

Thanks SirDice, but what I want is a function that generate the folder and .tar.gz name.

Thanks in advance.


----------



## mix_room (Jun 20, 2011)

almobd3 said:
			
		

> Thanks SirDice, but what I want is a function that generate the folder and .tar.gz name.



How much are you willing to pay for him to do the job?


----------



## SirDice (Jun 20, 2011)

Yeah, you're supposed to learn how it works then do it yourself. We're not the spoon-feeding types.


----------



## almobd3 (Jun 21, 2011)

> how much are you willing to pay for him to do the job?



 

10-50$


----------



## SirDice (Jun 21, 2011)

$50 per hour sounds about right..


----------



## mix_room (Jun 22, 2011)

SirDice said:
			
		

> $50 per hour sounds about right..



For something like this, I would say yes.


----------



## UNIXgod (Jun 22, 2011)

mix_room said:
			
		

> For something like this, I would say yes.



Wouldn't you rather be self sufficient? Seriously.


----------



## mix_room (Jun 23, 2011)

UNIXgod said:
			
		

> Wouldn't you rather be self sufficient? Seriously.



For 50 USD per hour, as a relatively simple one time task: I would probably ask to have it done. 

On that note, since I wouldn't expect it to be a one off problem, I would learn how to do it. But since it wasn't me who asked the question in the first place, the question is moot.


----------



## DutchDaemon (Jun 23, 2011)

Can we come to some sort of conclusion here, gentlemen?


----------



## UNIXgod (Jun 23, 2011)

mix_room said:
			
		

> But since it wasn't me who asked the question in the first place, the question is moot.



I see I mis quoted. 



			
				almobd3 said:
			
		

> 10-50$



almobd3 if you are interested in scripting your own policies I am with most certainty that many here, including myself, would be more than happy to teach you.


----------



## pauloperes (Jun 28, 2011)

Hi,

Did you see this: http://dev.mysql.com/doc/refman/5.1/en/mysqlhotcopy.html?


----------

