rawandnet
asked on
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]#
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]#
SOLUTION
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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)
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)
ASKER
which articale?
and the command i use to backup is
(mysqldump --user=user --password=pass --opt cacti > cacti.sql), where should i put -R
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.
http://www.devshed.com/c/a/MySQL/Backing-up-and-restoring-your-MySQL-Database/
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.
http://www.devshed.com/c/a/MySQL/Backing-up-and-restoring-your-MySQL-Database/
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.
ASKER
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.
http://www.devshed.com/c/a/MySQL/Backing-up-and-restoring-your-MySQL-Database/
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.
http://www.devshed.com/c/a/MySQL/Backing-up-and-restoring-your-MySQL-Database/
ASKER
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.
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?
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?
ASKER
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?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
That aside I think you should be able to script up something to do your backups safely.
ASKER
thanks for your greate support, Solved
ASKER
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?