Problem with mysqldump (no data, only the database structure).
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.
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.
ASKER
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
-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_b
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 --------------------------
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_b
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 --------------------------
ASKER
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?
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?
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.
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.
ASKER
Thanks gr....appreciate it mate.
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