Scheduled backup date-named

[jlandes]

Hello,

As floyed mentioned, here is a guide to tell you how:
http://help.directadmin.com/item.php?id=146

Basically, you create multiple cron backups to use ftp for 127.0.0.1.
Point each cront to a different path, and you can have as many backups as you want.

Also, in the Admin Level Backup/Transfer, you can customize the path, without the need for ftp transfering.

John

Thanks for the information. This is a big help.

 

[livingtale]

backup only database

I read your instructions about the cronjob.
Is there a way of making a scheduled backup is this way only for the database?
The database is only small, and the site quite huge, so it would cost a lot of wasted bandwith.

 

[floyd]

Google 'mysql backup'

You can use mysqldump to create a simple backup of your database using the following syntax.

mysqldump -u [username] -p [password] [databasename] > [backupfile.sql]

o [username] - this is your database username
o [password] - this is the password for your database
o [databasename] - the name of your database
o [backupfile.sql] - the file to which the backup should be written.


The resultant dump file will contain all the SQL statements needed to create the table and populate the table in a new database server. To backup your database 'Customers' with the username 'sadmin' and password 'pass21' to a file custback.sql, you would issue the command:

mysqldump -u sadmin -p pass21 Customers > custback.sql

You would create a cron job to do this.

 

[livingtale]

backup database as cronjob

I tried to follow the following instruction

It can be done using the API and run as cron job.

http://www.directadmin.com/features.php?id=512

I produced the following cron command:

action=backup domain=mydomain.com select0=<b>database</b>

and tried because the HTML tags seemed inappropriate also:

action=backup domain=mydomain.com select0=database

where mydomain.com is the domain where I want to make backup from (not the resellers domain).

Both didn't work: couldn't find a beackupfile

I didn't understand the following line:

(hidden value, doesn't affect the outcome of the backup, but is required)

Who can help?

 

[floyd]

That looks nothing like my recommendation for backing up a database only.

If you are going to use cron with the api commands you need to write a script of some sort that includes the url and the api commands.

But my suggestion for backing up the database is to not use api commands at all and just use the mysqldump command.

 

[livingtale]

empty file

problem is that

mysqldump -u [username] -p [password] [databasename] > [backupfile.sql]

produces an empty file

 

[livingtale]

correction of the mysqldump code

With trial and error I found that the following code produces a file that is not empty:

\usr\bin\mysqldump -u $[username] -p $[password] $[databasename] > [backupfile.sql]

Where [password] for instance needs to be replaced by the password itself.
But that leaves another problem:
It's not a .tar.gz file so it can't be uploaded with the backup system.
How do you restore with a .sql file?

 

[floyd]

You can tar.gz it yourself or as part of the command to create the sql file.

mysqldump -u[username] -p[password] [databasename] > [backupfile.sql]; tar -czf backup.sql.tar.gz backup.sql

I don't think

\usr\bin\mysqldump -u $[username] -p $[password] $[databasename] > [backupfile.sql]

is correct especially with the backslashes.

Restore just an sql file

mysql -u[username] -p[password] [database_to_restore] < [backupfile.sql]

Everything above I found by Googling just now. I don't have the commands memorized.

I did find that taking out the space between the -u and the username and the -p and the password works. I don't know why.

 

[livingtale]

correct code

sorry, wrong slash
But this code does work:

/usr/bin/mysqldump -u $[username] -p $[password] $[databasename] > [backupfile.sql]

the

; tar -czf [backup].sql

does not, alas.

But, even if it would work, the database file which is needed to restore the database is a .giz file, not a .tar file. You can easely (I think) make the backupfile.sq file on the site into a .tar.gz file (with the file manager - botomlines) but I don't think that uploading this file in the backup manager (that is: not the database manager facility - which needs a gz file) is save.

 

[livingtale]

disappointment

Sorry:
the code can't be wright.
The file it's produces is too small (not zero, but who cares)

don't know what to do next.......

 

[floyd]

Sorry meant to say

tar -czf backup.sql.tar.gz backup.sql

have edited the original

the database file which is needed to restore the database is a .giz file, not a .tar file.

I think you mean .gz

You are the one who asked about a .tar.gz file so I gave you the command to do that.

It's not a .tar.gz file so it can't be uploaded with the backup system.

If you want just a .gz file you have to use the command

gzip backup.sql

You may have to use the full path to the gzip program.

At this point I don't know what you want anymore. Your posts keep changing as to what you want. So please tells us again clearly what you want. And keep in mind that there is more than one way to do things.

It's not a .tar.gz file so it can't be uploaded with the backup system.

And which backup system are you talking about?

 

[livingtale]

sorry

I really want the same all along, but nothing seems to work!
I need a scheduled backup of only the database, which I can use to repair the database when it's broken. The need for ONLY the database-backup is that important because the site itself is quite large, but in no need for a scheduled backup.

Problem is that I'm muddling through in trying to get a cron command that works.

The first attempts got me empty files. And after that I THOUGHT I got it right, but I was wrong: the file was not empty, but not large enough: it had to be corrupt too.
And believe me: I tried all ways I could think of to get a correct command.
I even compared the code from MediaWiki (that's the database about) with yours and found out that they used /usr/bin/ and the $.... But that code was a much more complex command -making a zipped file with a date in the name- which didn't work either (no reaction on that site till now)

I even think of getting a domain-name just for the database only to avoid the problems. But think that's stupid....

 

[floyd]

I need a scheduled backup of only the database

I already gave it to you.

/usr/bin/mysqldump -u[username] -p[password] [databasename] < [backupfile.sql]

No []

 

[livingtale]

code does not work

Sorry, but think you really are wrong.
That way it makes an empty file.

Maybe the point is what the username is:
I think it should be the name of the account.
You have a reseller status when you use the cron job. The user should be the account (website) where the database is on.
But the username of the database doesn't work either.....

 

[floyd]

The user name and password are for the database not the control panel.

Its ok if you think I am wrong. I am using the command every single day to backup several databases and not only that but they are also being emailed to me. But I am the one that is wrong so I am done here.

 

[livingtale]

Sorry if I offended you, but it was a small yoke regarding your own bottomline. Besides, I did'nt get the message that you tried the command yourself (no /usr/bin/ in the command).
And I'm really desperate....

You state that the line works with you, I notice that is doesn't work with me.
Fact is that I thought that the username was the name of the user/account/domain. Quite confusing that user has several meanings here.
Could it be that it I'm not getting it right is because what I stated: that the command should in some way refer to the user/account/domain because it is not the same as the reseller?
That is: you're command works because you're status of reseller and the database are the same (it's the database of the reseller, not another domain).
What should I do to get it work for a specific user/domain/account?

In former days you could make a cronjob on the user-level, but that's gone in this version of DA.

 

[floyd]

Fact is that I thought that the username was the name of the user/account/domain

I don't know why you would think that. That is not the username your scripts use to access the database. Its not the username you use to access it in phpmyadmin. Its not the username you used when you set up the database to begin with. It must be the username and password you gave the database when you set it up. That is the only choice.

That is: you're command works because you're status of reseller and the database are the same (it's the database of the reseller, not another domain).

I have no idea what that means.

In former days you could make a cronjob on the user-level, but that's gone in this version of DA.

Yes it is there. In fact that is the only place you can set up any cron job except for using the backup system from the reseller level or admin level.

If you are not trying to run the command from the user level cron job set up then I have no idea what you are doing.

Maybe you need to hire a system administrator.

 

[livingtale]

corect code at last

First the misunderstanding about reseller and user:
in this discussion is stated that producing scheduled backups are only for resellers. And because my host didn't gave my users cronjob-rights I assumed you had to be a reseller to use the cronjob which you implied.

I understand that your command works well on your site, but on mine it surainly doesn't. But by combining several sources I came with the correct command:

/usr/bin/mysqldump -u [username] --password=[password] [databasename]
 | /bin/gzip > [databasename].gz

o [username] - this is your database username
o [password] - this is the password for your database
o [databasename] - the name of your database

You can use other names for the file xxx.gz and/or put a number before it.
If you use different numbers for each cronjob, you can schedule it (for instance: a cronjob for each day of the week and name it 1[databasename], 2[databasename] etc. For uploading the .gz file with the database manager the name of the file is not critical, but it has to be a .gz file.

 

[floyd]

Glad you found a command that would work for you. That one works too. Both in fact work:

mysqldump man page:

--password[=password], -p[password]

The password to use when connecting to the server. If you use the
short option form (-p), you cannot have a space between the option
and the password. If you omit the password value following the
--password or -p option on the command line, you are prompted for
one.

But also the ONLY place you can enter this command is from a User Level cron job. This command cannot be entered at the Reseller Level. Even if you are a reseller you have to click User Level to get to the right place.

Don't confuse people by telling them you are doing this as a Reseller. Even if you are a Reseller you are still doing it from the User Level. You cannot have cron execute your own commands from the Reseller Level.

This is very similar to what you came up with:

/usr/bin/mysqldump -u[username] -p[password] [databasename] < [backupfile.sql];/bin/gzip backupfile.sql

The only real different is that are using --password=[password] instead of using -p[password] I have a feeling you were putting a space between the -p and the password.

You are also outputing the command directly to gzip which is fine. The one I use outputed the command to the file first and then gziped it.

If you install mutt you can also have the backup file emailed to you by adding to the end after a ; separator:

;mutt -s "mysql backup" -a backupfile.gz [email protected]

Of course you will have to know the full path to mutt as well.

You also never stated what your operating system was so there was no way we could know the exact path to mysqldump and gzip so we could not give you the exact path to use.

And because my host didn't gave my users cronjob-rights

This is the first clue I had that you did not have full control over your server. If you are not the owner or admin of this server and do not have full control over it then you probably should have contacted your host first. We can only offer only limited help to users and resellers because we do not have any idea how the host has configured your server. Always contact your host first if you do not have full control of the server since they are in the best position to help you.

 

[livingtale]

p plus space

If I understand you correctly it went wrong with the space between p and password. In your line there was a space. And because there is a space between -u and username, it's quite confusing.
Please correct it in your messages as well for other readers.
It's really nasty that such a command is that easily written wrong....

I think the rare combination of the discussion (see my other message) and not having the correct rights made that I didn't know I should have those rights. If you don't know, what can you ask?
Besides: the first DA skin which I used didn't have the cronjob as user on show at all (maybe because I did not have the rigts?). How could I have known it was normal to have cron-rights as user?
And I do have the reseller status.
Maybe an idea for the help-manual?