[darkscout]

So with the explosion of everyone having fun with MySQL. I just propose one change: Always keep "$database" as the current version. Before upgrade archive off the previous version to "$database$version".

Some of us have backup scripts, mysql slave servers, tools and other fun stuff that gets broken every time the version number changes.

For example I run a nightly script to backup my database in case anything happens:
"mysqldump xbmc_video -u xbmc -p xbmc | gzip > xbmc_video.sql.gz"

Now every time the database number changes, this script is just going to be archiving off old data. However, in my proposed change. You save a backup to xbmc_video50 before moving to xbmc_video51. The "xbmc_video" is version 51, version 50 is safe and backed up, and everything works as intended.

[Memphiz]

+10 for this - as of the exact same backup reason

[darkscout]

Ticketed:
http://trac.xbmc.org/ticket/12209

[gabbott]

I think this is a great idea as well

[darkscout]

+10 for this - as of the exact same backup reason

Memphiz or anyone else: if you're running anything that can run 'logrotate' which automates the whole thing more or less.

Inside of /etc/logrotate.d I created a file "xbmc_video"

Code:

/var/backups/xbmc_video.sql.gz {
    daily
    rotate 14
    nocompress
    create 640 root adm
    postrotate
        mysqldump xbmc_video57 -u xbmc -pxbmc > /var/backups/xbmc_video.sql
        mysql -h [offsite server] -u [user] -ppropradr [pass] < /var/backups/xbmc_video.sql
        gzip -9f /var/backups/xbmc_video.sql    
         #rsync -az /var/backups/xbmc_video.sql –password-file /root/rsync.password remote@server::backup
    endscript
}

Run it for the first time with:

Code:

touch /var/backups/xbmc_video.sql.gz
logrotate -f /etc/logrotate.d/xbmc_video

You should now have an automatic rolling backup.

[samzstein]

please do this, im a newb to the mySQL stuff and was only able to set it up through a lifehacker tutorial so im stuck using a nightly from early October due to my own ineptitude.

[darkscout]

sam: this won't help you. It'll only help the people that backup their databases with an automated script.

Someone needs to contact life hacker and get them to edit or remove their article.