Take the 2-minute tour ×
Ask Ubuntu is a question and answer site for Ubuntu users and developers. It's 100% free, no registration required.

I am backing up my files using deja-dup. But how do I do the same with MySQL? I see some cron scripts, but that will keep backups forever. I thought the feature of deja-dup that allows backups to be kept up to a month or while theres space. Is there something similar with MySQL?

share|improve this question
    
Do you want to use MySQL to back up your data, or do you want to use Déjà-Dup to back up your MySQL database? –  Stefano Palazzo Jun 6 '11 at 6:17
    
@Stefano Palazzo, either way will be fine. 1 advantage with Deja-Dup is I will then control all backups from it. Instead of moving over to MySQL. However I'd expect the backups to work? I understand that MySQL stores tables of different engines (eg. InnoDB vs MyISAM) differently? –  JM at Work Jun 6 '11 at 9:33
add comment

2 Answers

  • Export your MySQL database to a text file and put it somewhere Deja-Dup picks it up.

mysqldump -u {your_DB_username} -p {your_database} > {/home/{user}/textfile.sql} will, after asking for your MySQL user password create a file textfile.sql. Assuming you backup your home directory placing this will back it up.

Before backing up you could also compress it to save some space with gzip /home/{user}/textfile.sql

share|improve this answer
    
Sounds like a good idea. How do I do it before deja-dup runs? I configured Deja-Dup scheduled backups from the GUI. Do I have to do it with CRON instead? –  JM at Work Jun 6 '11 at 9:35
    
You should be aware, that the binary files in /var/lib/mysql/ may not be in a usable state (i.e. out of sync with each other) while mysql is running. To backup these files you will have to stop the database first, thereby syncing them. Also, using these files with another version of mysql might not be possible! –  Carsten Thiel Jun 6 '11 at 11:18
    
good call @Carsten while searching I did forgot that :-D –  Rinzwind Jun 6 '11 at 11:20
add comment

I would recommend automysqlbackup Install automysqlbackup. This is a script run from cron that makes daily, weekly & monthly backups of your MySQL databases using mysqldump.

Of course that means it has the same disadvantage as mysqldump: it has to lock the database during the "dump", but for small databases (when a dump finishes quickly) or when it only happens while nobody wants to use the database (e.g. a desktop that you back up at night) that shouldn't be a problem. If you use mysqldump manually, you should get an idea of how long it blocks in your case (of course your database will probably grow over time).

Also, daily backups are rotated weekly, and weekly backups are rotated on a 5 week cycle (only monthly backups are kept forever) and the SQL dumps are automatically compressed, so in most cases the total diskspace usage isn't too big.

share|improve this answer
    
Thanks for that answer. Running mysqldump for each database is not an option when you have about 50 databases. In case anyone is looking for config file it is in /etc/default/automysqlbackup and it's quite well commented (I needed to change just backup destination dir) –  user2041318 Feb 13 at 12:42
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.