Most Importantly : I need to backup all my Mysql dbs every 7 days. Currently I have just been manually ssh in and doing cp -r /var/lib/mysql /mysqlbacksupweekly , is there any way to automate this type of backup Im doing and also have it toss the old weeks backup thats in the /mysqlbacksupweekly directory. Im on cpanel, unix, Mysql all most recent versions.

2nd question :
I have 2 servers and I will eventually move to something where each server copies its /mysql dbs to the other server in case of a server explosion, when I do this in the future should I just run a command and have it ftp the data to each server from each other and would that use internal or my real bandwidth.

Copying MyISAM tables as you're doing is not safe, and will most probably result in corrupted data once you actually restore a backup.

Use my script... /usr/local/sbin/mysqlbackup:
Set your Backup location on `backupdir', edit /root/.my.cnf:
Create a link in /etc/cron.weekly:
And you end up with every single database backed up automagically in a nice, date-sorted format.

Honesly, I think `per week` backups of MySQL backups are useless. Why lose a week? I do them at least daily, and they gzip really small. This method ensures that your clients lose as little data as possible, and you have a dump file that can be edited should the need arise.

Restores are simple ...
No corruption. No "issues." No having to reload the database or flush permissions or blow-away and recreate databases or users.

Weekly has worked for about 5 or 6 years now , clients sometimes get issues on their sites like this week someone had a coder go in and wipe out a bunch of new items in a clients db on accident, it turned out the coder had been doing this for a week or longer , anyway long story short , they needed the furthest back old version of the db that I could find. luckily I had not done a manual mysql backup in a month so it was perfect for this situation.

Also its wierd that you say there would be issues or corruption for years I have been just literally dragging everything in /var/lib/mysql to others servers, , and backupfolders and with huge sites even like phpbb3 with thousands of users. Guess Ive just been lucky.

I really just need something to run every 7 days and literally just grab the entire folder and throw it in /mysqlweekly
. Should I just make a cron job to run my cp -r /var/lib etc.. once a week. Can I just use pico and do something close to this :

#!/bin/bash
cp -R /var/lib/mysql/* /mysqlweekly
done

and then have that run as a weekly or even daily cron
or not? If i'm close please let me know, thanks!

I hate to argue, but what you're doing is *wrong*.

Dragging MyISAM tables around like that is a sure fire way to lose two weeks worth of a data. Basically, you're copying direct ISAM files and if anything is open in that file at the time when you're copying it, you end up with an open table. Also, directly copying files back in like that causes changes in the way that MySQL handles memory and hashing, and it is absolutely impossible, due to design, to have it run efficiently. If you're doing that with "huge sites even like phpbb3 with thousands of users" (which I hope are being backed up more frequently than a week) I'm completely surprised that you've never had a stack overflow and don't have intimate knowlege of mysqlcheck & myisamchk --- if anyone's attempting to write to that file at the time you copy it back in *will* *absolutely* result in data corruption, and there is no way around that fact.

That you've done it like that for years just means you've been lucky. 99% of the people who do it that way haven't been. And pretty much anyone on here will agree.

To answer your question about cron, yes, that will work, however ... because of the stated reasons, *not* a good idea.

As for your second question, rsync is a better solution because it's more stable than relying on FTP and will work on your existing ssh layer. If you're running MySQL5, you should also look into the clustering options for a more complete failover.

As for whether or not it'll use internal or external bandwidth is kinda up in the air right now. Nobody at ThePlanet seems to be able to give a straight answer yet.

All data packets that pass through the switch port count towards your raw bandwidth provided by TP for any server.
The backup data sent from server one will use bandwidth on server one and the backup data received by server two will use bandwidth on server two.

ajz thanks for the bw info , good to know.

Catalyst ,

I want to try your script today and do it the right way, wasn't sure about this part,



I see where to change and it ... Im just wondering if this is correct for doing all users ,I need to backup all MYSQL not just for one user, will the way your faq/instructions showed make it so every mysql account including the mysql db with settings and permissions is copied. Im guessing I can just use pico and make the bin file at that location then from add the my.conf part (which i was kinda wondering about) and then adding it to cron. I could possibly use some light on the exact command to add that script to cron daily.

thanks cat, your feedback has already gotten me on a faster, more efficient track regarding data on my servers. People like you are an asset, to everyone.

Definitely the safe assumption, given last week's equally confusing thread. ;-) A better-safe-than-sorry approach seems the only senssible way to go.


Only install this script one time --- at the root level. The mysql root password goes in .my.cnf. That way it hits every database on your system into individual, timestamped files for each database. That way you get configuration information as well (the "mysql" db, which contains all your grants, perms and users).


`ln -s /etc/cron.daily/mysqldump /usr/local/sbin/mysqldump' would add it to cron.daily.


Welcome ... thanks.

It has to be; thats how my servers react.
If I transfer 20 GB from server one to server two, both owned by TP, I use 20 GB of bandwidth on both servers.
One server shows 20 GB of sent and one shows 20 GB of received.

catalyst,
Is there a way to have this backup script email root everytime it runs daily and/or email any errors it gets when running.

Other then that , hopefully I will never need to open any of these dbs for restore but I am very thankful for your time and your program to make my mysql backups! Merry Christmas~!

It already e-mails errors, so that's not a consideration. Just think: no output = no error. ;-) But yes, instead of creating a softlink, you could call it like a script like any of the other cron jobs.

Verbose mode on mysqldump is nasty.

Solved some issues today.

Having a root MYSQL pass with special characters such as $ or # in it will cause permission/access issues when cron.daily runs the script. I changed my root pass in Cpanel and it runs perfectly.

I added the echo line you suggested in pms to the script and its now emailing me everytime this runs regardless of errors. Done & done!

I also learned today that I needed to chmod these files im making to 700 and added a -u and -p'myrootpassword' to the :

for db in `mysqlshow | cut -d\ -f2 | grep -v \- | grep -v "information_schema"`

Line as I found that otherwise I get root@localhost access denied when cron runs that daily.

Just to confirm, you are correct. That is how the bandwidth usage works. Anything that passes through your public facing switch port is counted.

hey catalyst ,

#!/bin/sh
localpath="/backup/mysql"
numberofdays="7"
/usr/bin/find $localpath -mtime +$numberofdays | /usr/bin/xargs /bin/rm -Rf 2>&1

The only question I have really is would this script work to toss my 7 day old mysql backups (so i dont have to login and toss them every so often to save disk space). if i run this as a seperate cronjob daily. thanks!

Yes.