[TriLUG] MySql backup

[Michael Alan Dorman]

Michael Thompson <thompson at easternrad.com> writes:
> I am fairly new to mysql myself but I beleive if you back up the
> directories in /var/lib/mysql you will get all of the databases.  The
> databases are stored in their own directory (named after the db) and can
> be copied from one server to another and be accesible.  If you do not
> backup the /var/lib/mysql/mysql with the other databases, you will have
> to re-create users and permissions.  If this is NOT correct, would
> someone please correct me, as this is my current plan of action for my
> databases (unless I find something better...).

This will *ONLY* work is you stop mysqld beforehand.  Otherwise, you
have no guarantees that the files will not be updated while you are in
the process of copying.

An alternative to 'mysqldump -a' is a shell script that uses a
for-loop to dump all the databases individually.  Something like
(untested, but it's cobbled together from scripts I actually use):

for i in `mysql -B -e "show databases" | tail +2`; do
    mysqldump $i | gzip -9 > $i.`date --iso-8601=date`.gz
    ls -t $i.* | tail +7 | xargs rm
done

This will actually date the dumps so you can keep more than one, and
remove any that are more than seven days old.

As a bonus, I'll even spare you a rant as to why MySQL is a great ISAM
database but a poor SQL one, and why you should be using PostgreSQL
for any serious database usage.

Mike
-- 
Speak in extremes, it will save you time -- Tin Machine