mysqldump command creat only empty files?

[viewnepal]

I want to backup my mysql Database so for that i have try to put my following code in cron job as well in php file. i am able to create the file but file is 0kb. why it is happening. for your convenience the code is as followes.

code that i have used in cron job is this.

mysqldump -hlocalhost -uusername -ppassword dbname > /home/user/public_html/myfolder/mysql_backup.sql

this codes creat a file mysql_backup.sql but it is empty. please help me.

similarly the code i have try in php file is using exec command.

$file = "backup-".date("m-d-y-H-i-s");
@exec("mysqldump -h localhost -u username -ppassword dbname ¦ gzip > /home/user/public_html/myfolder/$file.txt.gz");

Similary this code is also work to create the gip file but here also this is empty.

Please help me how i can be able this. i need to do it in php and mysql normally.

[military-world.net]

To back up your database, open your phpMyAdmin page in your web browser and select your database from the dropdown on the left. From the the main database page, click on "Export" in the top button bar.

Set up the options as follows:

Export section
This section determines from which tables data should be exported and the format of the file itself. You should "Select All" tables and choose SQL as the data type.

SQL section
This section gives you various SQL syntax options that you can choose for your export. You should check both the Structure and Data checkboxes.

Structure - Check the following SQL structure options:
Add DROP TABLE
Add AUTO_INCREMENT value
Enclose table and fieldnames with backquotes
Data - Do not check any of SQL insert syntax options (e.g. Complete, Extended, Delayed) but make sure that the "Export type" is set to "INSERT".
"Save as file" section
This section lets you choose options about the exported file itself. If you want to save your export to a file (always helpful when you want to actually back up your data), check the "Save as file" button.

In Filename template, you can set the name of the file to be saved to your desktop. The default is DB which yields the database name plus .sql file extension. This is fine for a one time backup, but I tend to prefer %Y%m%d%H%M%S-DB which yields a time-stamped filename (e.g. 20040510120000-mt.sql).

By default, the export will be downloaded in a plain text file format. If, however, your database is very large, you may want to choose some form of compression to shorten your download and save on bandwidth. Note that due to a bug in the software at the time of writing, if you choose a compression format, the .gz and .zip extensions may no be added on and so you may want to add them yourself after the download is complete.

Go!
Once you hit the "Go" button, the download of your export file will proceed and phpMyAdmin will remember your settings for the next time.

Restoring via phpMyAdmin
To restore from a backup, click on the SQL tab in the top menu bar. You will see a button labelled "Choose file". Click it and select the MySQL export file from your computer in the resulting dialog box. Leave the "Compression" option at "Autodetect" unless you experience problems. Click on Go and your database will be restored!

Backing up via the command line
To backup from the command line of your shell account, log in and type the following at the prompt replacing USERNAME and DATABASE as described previously:

mysqldump -a -u USERNAME -p DATABASE > FILENAME.mysql

You will be prompted for your database password and then the DATABASE will be dumped to a plain-text file called FILENAME.mysql.

The resulting file, FILENAME.mysql, is a full backup with which you can fully restore your database in case of problems.

Restoring via the command line
Restoring from FILENAME.mysql is a three step process:

Drop the database

mysqladmin -u USERNAME -p drop DATABASE

Recreate the database

mysqladmin -u USERNAME -p create DATABASE

Import the backup data

mysql -u USERNAME -p DATABASE < FILENAME.mysql

[military-world.net]

After you have set up a working MySQL replication from the master to the slave, I assume that you want to do automatic backups of the slave database to the directory /home/sqlbackup. First, we must create that directory:

mkdir /home/sqlbackup

Next we create the shell script /usr/local/sbin/mysqlbackup.sh that stops the slave, makes an SQL dump of the whole MySQL database in /home/sqlbackup (the file name of the SQL dump will look like this: backup-20070423-18.sql; this is a dump taken on April 23, 2007, at 18.00h), restarts the slave afterwards (the slave will then catch up on everything that has happened on the master in the meantime so that no data is lost), and deletes all SQL dumps in /home/sqlbackup that are older than two days:

vi /usr/local/sbin/mysqlbackup.sh

#!/bin/sh

datum=`/bin/date +%Y%m%d-%H`

/usr/bin/mysqladmin --user=root --password=yourrootsqlpassword stop-slave

/usr/bin/mysqldump --user=root --password=yourrootsqlpassword --lock-all-tables \
--all-databases > /home/sqlbackup/backup-${datum}.sql

/usr/bin/mysqladmin --user=root --password=yourrootsqlpassword start-slave

for file in "$( /usr/bin/find /home/sqlbackup -type f -mtime +2 )"
do
/bin/rm -f $file
done

exit 0

(Please make sure that you replace yourrootsqlpassword with the password of the root MySQL user on the slave!)

Now we must make the script executable:

chmod 755 /usr/local/sbin/mysqlbackup.sh

Of course, we don't want to run the /usr/local/sbin/mysqlbackup.sh manually; instead, we create a cron job that runs the script automatically every three hours:

crontab -e

0 */3 * * * /usr/local/sbin/mysqlbackup.sh &> /dev/null

Of course, you are free to modify the cron job to run as often as you need it.

That's it, using this method you can now back up your MySQL database without interrupting the MySQL service on the master server.