We help IT Professionals succeed at work.

MySQL backups - is a dump required for backup software to pick up the database data?

We have MySQL newly installed on a server (CentOS) at my workplace.  There is a backup that is setup to run to pickup any files that are new or have changed anywhere on the server (IBM Tivoli Storage Manager).  My question is this:  Will TSM be able to backup all the MySQL databases just as they are?  Or will they not get picked up on backup (are they considered "in use" and not accessible to grab a backup copy of)?  I wasn't clear if we really need to be doing a data dump to disk first, then have the backup pick that dump up, or if it will already be grabbing a complete copy of the databases already.
Watch Question

MySQL uses the local filesystem and relies on the OS to provide protection for read/write locks on its files, so TSM should be able to back up all the files. You should check the backup drive to make sure, however.

Another option, a backup-backup if you will, is to run mysqldump on your database on a regular basis as well. That will produce a text file which might be easier to back up.

You can write your own bash script to backup MySQL DBs as they are. As mankowitz suggested, you can use mysqldump.

Here is bash code that I use to backup the DBs. I save them to a path and then I use rsync to move them to a remote host.

DATABASES=$(find /var/lib/mysql/ -maxdepth 1 -mindepth 1 -type d -exec basename {} \;)
for database in $DATABASES
mysqldump --add-drop-table -u root -pYourPassword --database $database | gzip >          \ 
/path/"$database"-`date +%Y%m%d`.sql.gz

Open in new window

Explore More ContentExplore courses, solutions, and other research materials related to this topic.