Automating Mysql Db Backup

[section31]

Whats the best way to automate a mySQL DB BACKUP? I tried using a crontab with this

>mysqldump -udbusername -pdbpassword dbname > mybackup.sql

but when i try to restore via phpmyadmin I get this error

>MySQL said: 


You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '--------------------------------------------------------- 
--

 

I'm almost sure whats causing the error. Its some of the lines that start in -------, instead they need to have ###. Is there a way I can have the mysqldump use #'s instead of -'s.

[section31]

One last bump.

 

TCH Stones

[borfast]

Give this one a try.

 

http://www.phpfreaks.com/script/view/11.php

[section31]

UH, thanks for the nice script, but this still doesn't solve my problem with the - not being friendly to phpmyadmin. The mysqldump from the commandline seems to always use - instead of #. In the meantime, I've been using a search and replace which works fine but its more work for me. Its not like i'm going to be restoring my db anytime soon, its just in case. ya know

 

I decided to try this script for s***s and giggles anyway. But for some reason my db is being corrupt during the ftp transfer to my computer. The gzip file is perfect on the server itself, but during the ftp transfer it gets corrupted. Any idea whats causing this.

 

Must be something to do with this syntax.

 

>$ftpconnect = "ncftpput -u $ftp_user_name -p $ftp_user_pass -d debsender_ftplog.log -e dbsender_ftplog2.log -a -E -V $ftp_server $ftp_path $filename2";

shell_exec($ftpconnect);

 

Any Ideas?

Edited May 25, 2003 by lstover

[borfast]

Well, if you actually made it to the part where you have a gzip file with an actual database backup in it, you've gone farther than I did. The script says everything is ok but the file is actually empty.

So I tried executing the mysqldump command directly and got this:

 

mysqldump: Got error: 1044: Access denied for user: 'best-alm@localhost' to database 'bestalm_ipb' when using LOCK TABLES

 

It's not the same problem you have but I don't know how to fix any of them

[borfast]

OK, after taking a look at the mysqldump man page, I found that the --opt argument that the script uses is what's causing the LOCK TABLES error (it implicitly adds the --lock-tables option to the command line).

 

So I opened up the script and changed this line (67):

 

passthru("mysqldump --opt -h$dbhost -u$dbuser -p$dbpass $dbname >$filename");

 

into this:

 

passthru("mysqldump -a -e -h$dbhost -u$dbuser -p$dbpass $dbname >$filename");

 

and now it works like a charm.

I now have a cron job with the following command:

 

php -q /path/to/script/dbsender.php

 

and it works just fine.

I received the file in my e-mail and it looks OK but I didn't try it with the FTP option, though.

 

 

But anyway, now I understand your problem with the '---'

I wasn't understanding your problem before but once I opened the .sql file, I realized that I wouldn't be able to import that data with phpMyAdmin.

 

mysqldump's man page says we can read the backup back into mysql with one of the following commands:

 

mysql database < backup-file.sql

or

mysql -e ’source /patch-to-backup/backup-file.sql’ database

 

I tried both commands and none of them worked. The second even has something wrong in its syntax, since MySQL output the whole 'how-to-use' stuff.

So right now I'm trying to get a way to either have mysqldump output ## instead of -- or a way to import the file with -- into MySQL.

If I find something, I'll post it here.

[borfast]

Straight from the phpMyAdmin FAQ:

 

[3.4]I am unable to import dumps I created with the mysqldump tool bundled with the MySQL server distribution.

 

The problem is that mysqldump creates invalid comments like this:

 

-- MySQL dump 8.22

--

-- Host: localhost Database: database

---------------------------------------------------------

-- Server version 3.23.54

 

The invalid part of the code is the horizontal line made of dashes that appears once in every dump created with mysqldump. If you want to run your dump you have to turn it into valid MySQL. This means, you have to add a whitespace after the first to dashes of the line or add a # before it:

-- -------------------------------------------------------

or

#---------------------------------------------------------

 

So now we just have to find a way correct all the lines with more than two followed dashes.

This shouldn't be hard and since I don't suppose we'll need to import backups everyday, it should't be a bother

[section31]

Yup, like i said. Shouldn't be a serious problem, I'll just do it manually. And yes, i hate to make a few modifications to the script for it to work, but overall a descent script.