DISQUS

Tech-Recipes: MySQL - Daily database dumps, all nicely sorted. | MySQL | Tech-Recipes

  • bofh468 · 5 years ago
    I know that replying to oneself is bad karma... but I noticed a typo above. The backup files will be located under /home/backups/mysql/dbname-YYYY-MM-DD.tar.gz
  • Anonymous · 5 years ago
    This is exactly what I've been looking for. Will try it soon.

    Ellen
  • Anonymous · 5 years ago
    If you want to back up *every* database on the server, then replace this line

    for DATABASE in `cat /etc/db-backup/db-list.txt`


    with the following

    for DATABASE in `echo 'show databases' | mysql`
  • Anonymous · 5 years ago
    <ul id="quote">/usr/local/bin/mysqldump --user=root --opt $DATABASE | gzip </ul>

    this is for user root with no password, if the user root has a password add '--password=' followed by your password INSTEAD of just putting '-p'

    I've tried using '-p' but then I got the password prompt.
  • Anonymous · 5 years ago
    <ul id="quote"><h6>Anonymous wrote:</h6></ul><ul id="quote">/usr/local/bin/mysqldump --user=root --opt $DATABASE | gzip </ul>

    this is for user root with no password, if the user root has a password add '--password=' followed by your password INSTEAD of just putting '-p'

    I've tried using '-p' but then I got the password prompt.
    try using -ppassword
    instead of -p password.
  • Esquire · 4 years ago
    Just on the code
    for DATABASE in `echo 'show databases' | mysql -p`


    --column-names=false should be added so that "Database", which is the column name, will not be included in the list.

    Thus...
    for DATABASE in `echo 'show databases' | mysql --column-names=false -p`
    <ul id="quote"><h6>Anonymous wrote:</h6>If you want to back up *every* database on the server, then replace this line

    for DATABASE in `cat /etc/db-backup/db-list.txt`


    with the following

    for DATABASE in `echo 'show databases' | mysql`
    </ul>
  • Anonymous · 1 year ago
    Hi,

    I am also looking for a reliable and dependable solution to backups my databases on the server and want to keep each backup for atleast 15-30 days.

    I guess the solution mentioned here is more reliable than the ones available with php scripts.

    Can you tell me the command to be executed on cron tab to take daily backups at a specified time?

    Thanks.
  • Anonymous · 1 year ago
    I am little new to coding stuff and am bit confused to paths and user with or without password.

    Can you please tell me exact script with following parameters:

    1. Type of hosting: shared
    2. Objective: to backup every database and keep them for 15 days
    3. Backup folder: /home/myid/db-backups
    4. etc folder is located at /home/myid/etc
    5. UserID: hello, password: iamok

    Please also tell me the command to be executed on cron tab to run your sh script, thanks.
  • christian · 8 months ago
    thanks very much for this explanation
  • N GowriSankar · 4 months ago
    This looks great. I like the idea of gzipping it.

    For our requirement, we were taking two level backups = Daily backups and Monthly backups. The daily backups are recycled every week and monthly backups are stored for much longer duration [As a matter of fact, we have not yet deleted them for past 3 years]. Of course, our backups are only 200+ MB now.

    So we have ended up with:
    01-2008.dmp, 02-2008.dmp, 03-2008.dmp, ... 05-2009, 06-2009.dmp, etc and seven daily dump files = Mon.dmp, Tue.dmp, Wed.dmp, ... Sat.dmp, Sun.dmp

    We used `date +%e` to get the day of today. We used `date +%0m` to get month and `date +%0y` to get year.