backup mysql database

hi gus,

I am trying to backup mysql folder, i used copy (cp) command in linux.  the backup folder called mysql_backup.
as you can see below the total size of backup folder is smaller, do you know why is that? i even stop mysql service from running and backed up, still same size. even symantec backup couldn't backup some these folders.  does this gives me a problem when retriving example moodle database.  or you recommend using something else.
[root@localhost mysql]# du -h

66M      ./moodle

8.0K      ./test

1.3M      ./mbrs

1016K      ./mysql

88M      .

[root@ localhost mysql_backup]# du -h

63M      ./mysql/moodle

4.0K      ./mysql/test

1.2M      ./mysql/mbrs

808K      ./mysql/mysql

85M      ./mysql

85M      .

[root@auis-stud-intranet mysql_backup]#
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
unless you stopped the mysql database, you should NOT use cp to backup the database ...
instead, use the mysql backup tools (mysqldump, for example) to backup the mysql databases
As above you should use mysqldump or similar to backup your databases.  Below is a portion of the script I use to backup necessary files and databases. This works great, it backs up the web files, dumps the database with the necessary drop commands and then uploads them to an internal ftp server


#Script to backup websites and associated databases

FILE=/root/mysql_bkup/dealpower-`date +%Y%m%d%H`.sql
WEBFILE=dealpower-`date +%Y%m%d%H`.tar

if [ ! -f $LOCK ]; then
 touch $LOCK
 elif [ -f $LOCK ]; then
 echo "Lock file still in place on `hostname` investigate."|mail -s "`date +%Y%m%d-%H:%m` Lock file still in place, investigate" $EMAIL
 exit 0

#Backup the dealpower database
#mv *.sql *.tar $OLD
/usr/bin/mysqldump -uuser -ppassword  --databases --ignore-table=table to exclude --add-drop-table --triggers database -R > $FILE

#backup the website
/bin/tar czf $WEBFILE $WEB php.ini my.cnf versions

#upload the files to craig's pc
/bin/sh $UPLOAD
mv *.sql *.tar $OLD

rm -f $LOCK

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
rawandnetAuthor Commented:
I used (mysqldump --user=user --password=pass --opt cacti > cacti.sql), wasn't sure if every thing backs up properly, do i have to still stop mysql service if i use mysqldump?

and if i had to reinstall the server and reinstall mysql-server, just backing up lets say moodle database and restoring it, does it restore moodle database, or i have to backup /var/lib/mysql folder?

Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

No you don't have to stop the mysql service.  mysqldump can run while the database is active.

If you have triggers/procedures then add triggers and -R to your dump line as I have in my example.

The mysqldump file will dump everything that is needed to recreate the database up to that point in time.

Restoring is a simple matter of running.

mysql -uuser -p database < cacti.sql

The following article goes into some detail. I would suggest you read through it to make sure you include all the necessary switches.  --add-drop-table(database) is useful to add.
rawandnetAuthor Commented:
which articale?

and the command i use to backup is
(mysqldump --user=user --password=pass --opt cacti > cacti.sql), where should i put -R
Whoops sorry forgot the linkl.

change your line to the following.

mysqldump -uuser -ppassword  --add-drop-table --triggers database_name -R > cacti.sql.

If you read through the article you may find other settings that you want, basically they will go before the database name.
rawandnetAuthor Commented:
you mentioned triggers/procedures, what do you mean by that and could you tell me what does ( --add-drop-table --triggers ) do?
Triggers or stored procedures and bits of code within your database that act depending on their setting or "trigger".  Not all databases use them, but a lot do.

The --trigger and -R will also include the necessary info int eh dump to recreate triggers or stored procedures for that domain.

--add-drop-table tells mysqldump to add the necessary code to drop an existing table of the same name, so that it can re-create it cleanly.  This is useful if you are restoring over the same database/table.

There is also --add-drop-database which does the same thing but for the whole database.

This article has quite a lot of useful info that you can review.
rawandnetAuthor Commented:
I have read the article it was interesting, the only thing surprises me is, after restoring the databases it is always smaller than original, example,
my moodle databases is 66MB, after backing up and restore using mysqldump the size change to 63MB, any idea why is that.  i look at the sizes under /var/lib/mysql.
I believe the size difference is down to whitespace/unclaimed space.  In the original file, you are looking at the actual file which contains the database, plus any open space from deletes.

When you restore your database, the space being taken up by whitespace/unclaimed space is not included and as such is effectively reclaimed, and also the data is organised better as it is a fresh copy basically.

does that make sense?
rawandnetAuthor Commented:
Then do you recommend to use (-R and –triggers) options or not, as i don't know whether my database uses it.  Using it does it cause any problem if i didn't have those services on my DB?

Try this script, on /backup/ create a file called body.txt and write your body message
mysqldump -uYOURUSERNAME -pYOURPASSWORD --opt YOURDATABASE > /backup/mysql.$(date +"%F").sql 
cd /backup/
tar -zcvf mysql.$(date +"%F").tgz mysql.$(date +"%F").sql
ATTFILE=/backup/mysql.$(date +"%F").tgz
SUBJECT="Backup Mysql $(date +"%F")"
cat body.txt | mutt -s "$SUBJECT" -a /backup/mysql.$(date +"%F").tgz $MAILTO
cd /backup/
rm mysql.$(date +"%F").tgz

Open in new window

As you are unsure and they don't interfere with the export I do recommend using -R and --triggers.

That aside I think you should be able to script up something to do your backups safely.  
rawandnetAuthor Commented:
thanks for your greate support, Solved
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Linux Distributions

From novice to tech pro — start learning today.