Backup MySQL

OK, thanks Mattail for the really cool script. I'm having a bit of a problem, being new to these types of scripts.

The script is located here: http://wiki.dreamhost.com/index.php/Automatic_Backup

The first line, #!/bin/bash, is giving me an error when I type it in. "event not found" What am I doing wrong? I'm typing this at the putty prompt.

As an alternative, can I just put all that text into a text file and upload it, and then automate it?

Onwards,

Matt

Go for the alternative. Put the text of the script in a file, upload it and make it executable (using chmod, as described on the page you referenced). To automate the backup, again follow the instructions on the wiki.

Cheers,
Ian

Putting the database password in plain text in the script has always disturbed me. Until recently, one could view a listing of everybody's processes on the server, which would include commands with password parameters.

If you're sharing your hosting plan with other users, they do have read access to all of your files, so beware of storing plaintext passwords.

-Scott

Well, for this particular domain I'm the only person admin'ing it.

But that does bring up an interesting point.

Is there a way to do a "quickie" MySQL backup without all the scripting and so forth?

Onwards,

Matt

In phpMyAdmin, select the database and table you want to use. Along the top of the screen should be an "Export" link - select this and choose the format of your database dump. If you select "SQL" and ensure the "Structure" box is ticked, you will get a .sql file which you can use to recreate the database from scratch.

OK, I go to phpMyAdmin, I select 'Export'. I make sure SQL is selected. I select all the tables, 'Select All'. Structure and Data are checked along with the defaults. I select 'gzipped' and hit the go button.

I get a prompt to ask me where to save the file. I save the file. It's an invalid archive, and it took 2 seconds to download.

My database is 50 MB in size. What did I do wrong?



**edit** never mind. I found the instructions on doing a backup from the command line here at http://wiki.dreamhost.com/index.php/KB_/_Account_Control_Panel_/_Goodies_::_MySQL. I'll just use that. I'd still like to automate it. Maybe someday. ;)
Onwards,

MattEdited by MattG on 06/27/06 09:24 PM (server time).

In reply to:

---

My database is 50 MB in size. What did I do wrong?

---

I think you will find the problem is caused by the 7MB limit of the default DreamHost PHP install.

As you already discovered, the solution is to use the command line tools to do the backup.

Mark

#!/bin/bash is supposed to be aprt of the shell script. That's a file that you create and put on your server (or create through ssh on the server). Then you have crontab excute that.

Let me know if you need more help with this. My inlaws have just been visiting and are leaving tomorrow morning so I'll be around more to help.

In reply to:

---

putting the database password in plain text in the script has always disturbed me. Until recently, one could view a listing of everybody's processes on the server, which would include commands with password parameters.

If you're sharing your hosting plan with other users, they do have read access to all of your files, so beware of storing plaintext passwords.

---

Wouldn't the process for this just show up as the cron job, and not the individual parts of the shell script?

And enless you've given out your password for that user, you're the only person who has access to those files. If you're not comfortable giving out the informaiton that the user can access, don't give out it's password.

With the way dreamhost is set up (groups) you don't have to use the same user who has the domain. Just becuase johnny has the FTP/ssh access to example.com, bobby who is an other user under your account can read the files for example.com and of course access the database regardless.




--Matttail
art.googlies.net - personal website

I've tried setting this up, and I'm sure I'm setting something up incorrectly, but I'm not sure what!

The e-mail that gets sent to me by the cron job states:
/bin/sh: /home/.dewdrop/finalfurlongftp/backups/finalfurlong.sh: /bin/bash
: bad interpreter: No such file or directory

I've also tried it with the directory /home/finalfurlongftp/backups/finalfurlong.sh, with the same result.

The first time the command ran (using the non-dewdrop version), I got a Permission Denied error, which went away once I remembered to chmod the script so that it's executable.

My cron job looks like:
00 16 * * * /home/.dewdrop/finalfurlongftp/backups/finalfurlong.sh

Am I missing something?

Seems like you're missing somethig. Would you paste in your shell script plase? that's the finalfurlong.sh file. Go ahead and chage our your user and pass for dummy info. I think your problem is there, and hopefully I'll be able to see what's wrong.



--Matttail
art.googlies.net - personal website

(To clarify, the username/password for the domain/mysql scripts should be the ones that have shell access?)

The scripts I am running are:
#!/bin/bash
suffix=$(date +%y%m%d)
cd /home/ftpusername/.snapshot/nightly.0/
tar -cf /home/ftpusername/backups/archives/finalfurlong.$suffix.tar finalfurlong.org/

#!/bin/bash
suffix=$(date +%y%m%d)
cd /home/ftpusername/.snapshot/nightly.0/
tar -cf /home/ftpusername/backups/archives/tohorse.$suffix.tar tohorse.com/

#!/bin/bash
mkdir /home/ftpusername/backups/mysql
suffix=$(date +%y%m%d)

mysqldump --opt -u dbusername -p dbpass -h db.url.org finalfurlong > backups/mysql/finalfurlong.$suffix.sql

tar -cf /home/ftpusername/backups/archives/mysql_backup.$suffix.tar backups/mysql/*
rm -r backups/mysql/

I have no idea. As far as I can see things should be working.

You shouldn't have needed to change the permissions (chmod) the script you uploaded - default permissions should work just find.

The user name you use to backup your website files should be the user who owns that domin. IE the user name you use to log in via FTP and manage the domain. For the mysql database backup you need to be using the user/pass that you set up in correlation with the database, the same one you'd use to log into phpmyadmin with.

Even if you have these wrong, I don't believe that relates to the error message you posted.

The error message seems to be saying that it can't find bash - your 'command line interperter'. The first line of each of your scripts points to it's location. What happens if you just try running one of your scripts from the command line (not with cron)? If the first backup script is called final.sh then you would type this at the command line:
./final.sh

(that's dot forward slash filename) Post back with what happened.




--Matttail
art.googlies.net - personal website

If I try it from the root directory, it detects bash.
-bash: ./finalfurlong.sh: No such file or directory

(Which makes sense, because the file's in the /backups folder)


If I try it from the directory the script is actually in, I get
: bad interpreter: No such file or directory

I'm really at a loss. I still think everythign should be working. The best thing I can sigguest it to carefully go back over things and see if you can find a type somwhere. I'm sorry I don't have any better sigguestions.

If you want I'd be willing to try and fiugre this out for you. PM me if you're intersted and I could SSH into your account and check things out. Perhaps I can figure out what's going wrong. You'd have to give me your user/pass, so if you're not comfortable with that I totaly understand. Anyways, if you're intersted PM me.



--Matttail
art.googlies.net - personal website