?
Solved

Problem with mysqldump (no data, only the database structure).

Posted on 2007-10-18
8
Medium Priority
?
1,499 Views
Last Modified: 2013-12-13
Hi guys hope you all are well :>)
Guys hope you can help me out on this one :>)

I have a problem trying to do a full backup of a mysql database.
The problem I have is not in the backing up of the STRUCTURE of the database, but in trying to backup BOTH the STRUCTURE and the DATA contained within the database.
I must be doing something wrong, so Im going to show you exactly what Im doing to see if you can pinpoint any mistakes im doing.
Just to reiterate, the following DOES successfully back up the STRUCTURE ONLY.
What I require is both the STRUCTURE and DATA.
Version of MySQL I am running is MySQL 5

=====================================================
# mysqldump -u root -p vbForum > vbForum.sql

When I run the above command, it prompts me for a password to authenticate as a user against the vbForum database.
I enter my password, and it dumps a 6MB file called vbForum.sql.
The problem is that there is only create table statements in here, and no data.
The file should be easily over 10MB, so Im not sure what other switches I need ot use in my mysqldump statement.

Any other help greatly appreciated.

Guys also, I want to be able to run this by script....that is, not have to log in to mysql and run a backup command through mysql, because I want to be able to schedule a script to run a mysqldump command to do this automatically every day.

Thank you.


0
Comment
Question by:Simon336697
  • 4
  • 4
8 Comments
 
LVL 36

Expert Comment

by:grblades
ID: 20100850
Hello Simon336697,

I am not sure why it isn't working. The default behaviour is to backup the data aswell. Does the forum log in via the root account?
I am thinging perhaps the root account does not have permissions to read the actual data.

If you are wanting to run it from a script then you will want to put the password into the commandline. For example if the password is 'apple' then you would use :-
# mysqldump -u root -papple vbForum > vbForum.sql

Regards,
grblades
0
 
LVL 1

Author Comment

by:Simon336697
ID: 20100912
Hi gr!
Thanks for your help.
I tried your suggestion, and I created another user with full (grant all) on privileges to this database.
In both cases, I get the same result...just structure.
0
 
LVL 36

Accepted Solution

by:
grblades earned 2000 total points
ID: 20100958
What happens if you try something like this :-
It should dump all databases

# mysqldump -A -uroot -papple > vbForum.sql
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 1

Author Comment

by:Simon336697
ID: 20101033
Youve done it gr!!!!
Thank you gr!
What does the -A switch do?
I imagine it means 'ALL' but does that mean all data?
I cant find any doco on it.
0
 
LVL 36

Expert Comment

by:grblades
ID: 20101098
-A means all databases
In theory it should not have made a difference. The benefit of using -A is that if you add more databases later they will be backed up automatically.

Below is a script that I normally use to backup my mysql databases. It creates a backup file with todays date in it and then compresses it to save space. Backups over 7 days old are automatically deleted. Its good to have backups going back a few days just incase you make a mistake and dont notice immediatly.

#!/bin/bash
DATE=`date +%F`
SQLFILE=/var/spool/mysql_backups/mysql-$DATE.sql

echo
echo --------------------------------------------
echo - BACKUP REPORT                                    -
echo --------------------------------------------

echo
echo
date +%T
echo "######## Deleting Database Backups Over 1 Week Old"
find /var/spool/mysql_backups -ctime +7 -exec rm {} \; -print

echo
echo
date +%T
echo "######## Dumping Mysql Database"
mysqldump -A -uroot -pmypassword > $SQLFILE

echo
echo
date +%T
echo "######## Compressing Database Archive"
bzip2 -9 $SQLFILE

echo
echo
date +%T
echo ---------------------------------------------
echo - END OF BACKUP REPORT                      -
echo ---------------------------------------------
0
 
LVL 1

Author Comment

by:Simon336697
ID: 20101436
Hi gr!
Thanks so much for this.
The only problem I see backing up ALL databases like this, is what if you just want to restore ONE database.
How do you do this from the one file?
0
 
LVL 36

Expert Comment

by:grblades
ID: 20101533
That is the issue with creating a single backup file.
Normally what I do is make a copy and edit the backup file in VI. You can search for the 'create database' lines and delete the two large chunks of lines which refer to other databases. This was yo can restore individual tables aswell so it is worth knowing how to do it.
0
 
LVL 1

Author Comment

by:Simon336697
ID: 20101612
Thanks gr....appreciate it mate.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
How much do you know about the future of data centers? If you're like 50% of organizations, then it's probably not enough. Read on to get up to speed on this emerging field.
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

621 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question