Backing up *ALL* MySQL databases on a dedicated server

[WildWayz]

Hi ya all - this applies to all you dedicated server people out there.


First of all, telnet into your dedicated server with root access, then in /root (or where ever) create a new file called

mysqlbackup.sh

Enter this into that file

echo =========================
echo + MySQL Backup Utility +
echo =========================

echo REMEMBER TO CHMOD THIS TO 755
echo or 'chmod u+x mysqlbackup.sh

echo
echo
echo ===============================
echo Removing all .TAR and .GZ files
echo ===============================
rm -r /root/mysqlbackup.tar.gz
echo
echo ... ALL DONE!

echo
echo
echo ====================
echo Backing up databases
echo ====================
/usr/local/bin/mysqldump -uROOT -pPASSWORD -A > /root/mysqlbackup.sql

echo
echo ... ALL DONE BACKING UP!

echo
echo
echo =====================
echo Compressing the files
echo =====================
tar cvf /root/mysqlbackup.tar /root/*.sql
gzip /root/mysqlbackup.tar
echo
echo ... ALL DONE

echo
echo
echo =======================
echo Removing all .SQL files
echo =======================
rm -f /root/*.sql
echo
echo ... ALL DONE

echo
echo
echo ====================
echo + BACKUP COMPLETE! +
echo ====================

Save it then type

chmod u+x mysqlbackup.sh

Next up is to make an entry into the CRONTAB with these details

0 3 * * * /root/mysqlbackup.sh


This will make it run the backup script every day at 3:00am (The previous day's one will be deleted though).

If you want it to email you the backup, then you can do this * THIS ONLY WORKS IF PHP IS NOT INSTALLED AS AN APACHE MODULE *

#!/usr/local/php
<?
require("mailclass.php");
$subject = "MESSAGE SUBJECT";
$sendto = "EMAIL ADDRESS TO SEND TO";
$replyto = "EMAIL ADDRESS TO REPLY TO";
$message = "This is the daily backup for ".date("jS F Y",time()).". Mysql Dump attached";
$filetoattach = "NAME OF FILE TO SEND";
$newmail = new CMailFile($subject,$sendto,$replyto,$message,$file toattach,$mimetype);
$newmail->sendfile();
?>

Save it as emailbackup.php and once more do

chmod u+x emailbackup.php

That will then email the backup... well it will do, but I cannot fine my mailclass.php file - so can anyone help there?

Hope this helps a little.

James

[eva2000]

nice but just curious why the email backup on your version will not work if php is installed as an apache module ?

chris's script seems to work with email backups - http://vbulletin.com/forum/showthread.php?s=&threadid=4256

[DVD Plaza]

Why go to the effort of making MySQL create files to reconstruct your databases with?

On a dedicated server you can obviously do what you want, with access to what you want, so I copy the actual raw MySQL datafiles. Thus I instantly have two copies of every database, the nightly backup runs, and then I delete the second copies.

Why do I do this? Depending what is happening at the time of the backup (amazing how many people still cruise through during the early hours of the morning) the database files may or may not be in use at the time, and they won't get backed up. This way at least one copy, maybe two, is guaranteed to be on the backup.

[MattLight]

You should just backup /var/lib/mysql if you have root access to the server.

You can do something like:
cp -af /var/lib/mysql /backup/todays-mysql
or to a remote system:
scp -r /var/lib/mysql newserver.com:/backup/drive/todays-mysql
or if you're really clever, rsync it:
rsync -avzr /var/lib/mysql newserver.com:/backup/drive/mysql
or tar it up...
tar -zcf /var/lib/mysql /backup/drive/todays-mysql.tar.gz

As you can see, there are a number of ways to backup your mysql data without dumping the data for each one. It also saves disk space because the databases don't have to be in the "INSERT INTO" format.

Hope that helps!

Best Regards,
Matt Lightner
mlightner@site5.com

[shashi]

and here is another one from an earlier post.
http://www.vbulletin.com/forum/showthread.php?s=&postid=36917#post36917

and i think the reason for dumping individual databases separately is so that you can recover them individually when needed.

and the reason for using mysql dump utility is that if you copy raw databases while the db is open and mysql running, you may get incorrect backup. not many databases flush all data to disk right away, they all work in buffered output mode, so not only that someone might be doing an insert at the time of copying, the database may have some data in the memory that it has not yet written at all!

[MattLight]

Originally posted by shashi
and i think the reason for dumping individual databases separately is so that you can recover them individually when needed.
Each mysql database has it's own folder in /var/lib/mysql (or wherever your databases are stored). I.E., the "mysql" database's files are located in "/var/lib/mysql/mysql". To restore a single databse, you only need to overwrite the current database folder with the old one and refresh the mySQL server.

If you are referring to individual table records, then those are best recovered with a mysql data dump (although you could restore the afflicted db folder with a new name and dump the data to get the individual records out).

and the reason for using mysql dump utility is that if you copy raw databases while the db is open and mysql running, you may get incorrect backup. not many databases flush all data to disk right away, they all work in buffered output mode, so not only that someone might be doing an insert at the time of copying, the database may have some data in the memory that it has not yet written at all!
This can be avoided by running "mysqladmin refresh" before backing up the data. That will cause mysql to flush all tables and write the data to disk.

Best Regards,
Matt Lightner
mlightner@site5.com

[shashi]

Originally posted by MattLight

This can be avoided by running "mysqladmin refresh" before backing up the data. That will cause mysql to flush all tables and write the data to disk.


if the database is open, there is no good way to backup unless a "hot backup" is used, something that the database itself has to provide. e.g. like what oracle provides. luckily, it seems mysql also provides a hot backup, even tough in alpha, called mysqlhotcopy. without that, all copies are approximate, SPECIALLY operating system copies. no amount of flushing via OS can gaurantee anything, since if the db is large it may take a minute, and of course there can be updates during that one minute! so the only time an OS backup is any good is if the database is down, else always use the tools provided by the database.

here is an excerpt from the manual:

Because MySQL tables are stored as files, it is easy to do a backup. To get a consistent backup, do a LOCK TABLES
on the relevant tables. See section 7.32 LOCK TABLES/UNLOCK TABLES syntax. You only need a read lock; this
allows other threads to continue to query the tables while you are making a copy of the files in the database directory. If
you want to make an SQL level backup of a table, you can use SELECT INTO OUTFILE.

Another way to backup a database is to use the mysqldump program: See section 14.5 Dumping the structure and data from MySQL databases and tables.

1.Do a full backup of your databases:

shell> mysqldump --tab=/path/to/some/dir --opt --full

You can also simply copy all table files (`*.frm', `*.MYD' and `*.MYI' files), as long as the server isn't
updating anything. The script mysqlhotcopy does use this method.


i checked the perl script that comes with mysql (mysqlhotcopy), it also essentially does exact same thing:
lock tables andmysqldump

and here is the excerpt from "perldoc /usr/local/mysql/bin/mysqlhotcopy" on my server:


mysqlhotcopy is designed to make stable copies of live
MySQL databases.

Here "live" means that the database server is running and
the database may be in active use. And "stable" means that
the copy will not have any corruptions that could occur if
the table files were simply copied without first being
locked and flushed from within the server.

WARNING: THIS IS VERY MUCH A FIRST-CUT ALPHA.

[MattLight]

shashi,

Thanks for the excellent info! I'll give that a try sometime.

Best Regards,
Matt Lightner
mlightner@site5.com

[eva2000]

interesting reading all this info :D

right now i use chris' script to selectively dump databases i set in the script and run it as a cronjob every day at a certain time.

i have 9 databases in mysql occupying 450mb of space in /var/lib/mysql

i have my vb beta 3 non live back up at 152mb in size in /var/lib/mysql

and my vb beta 4 database which is 160mb in size in /var/lib/mysql

the only other important database is the one for my marticle powered site at http://i4net.tv/marticle/

so i set my cronjob to backup only

beta4 and marticle mysql databases via mysqldump


Question is for the experts :)

When my databases start approaching enormous sizes like say 500mb - 1GB in size if they eventually get to such a size, which method of backup on a daily cronjob basis would be better ?

mysqldump or shutting mysql down for an actual backup of the /var/lib/mysql ? you can't do the second method via cron though can you ?

You should just backup /var/lib/mysql if you have root access to the server.

You can do something like:
cp -af /var/lib/mysql /backup/todays-mysql
or to a remote system:
scp -r /var/lib/mysql newserver.com:/backup/drive/todays-mysql
or if you're really clever, rsync it:
rsync -avzr /var/lib/mysql newserver.com:/backup/drive/mysql
or tar it up...
tar -zcf /var/lib/mysql /backup/drive/todays-mysql.tar.gz

[MattLight]

Eva,

I don't see why not. All you need is a simple bash script...

#!/bin/bash
mysqladmin shutdown
sleep 5
# do the actual backup here
safe_mysqld &
exit

Then just run that script via crontab with an & after it (so it runs in the BG).

Anyone have a reason as to why something like that wouldn't work?

Regards,
Matt Lightner
mlightner@site5.com