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?

[Webinar] Streamline your web hosting managementRegister Today

Kruger_monkeyConnect With a Mentor Commented:
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
Guy Hengel [angelIII / a3]Connect With a Mentor 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
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?

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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?

abolinhasConnect With a Mentor Commented:
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
All Courses

From novice to tech pro — start learning today.