Daily Database Cron Job on Godaddy

[TheBrandon]

Hello all,

 

I'm sure this is an incredibly common request but I'm still learning the syntax of this. I'm trying to get a daily cron job running to backup my database on Godaddy.

 

So far I am using this code:

mysqldump --opt -Q -h [host] --user=[user] --password=[password] [database] | gzip -v9 - > [/home/content/a/l/m/almo10000/html/casting-call-db/_DailyBackups/MySQL-`/bin/date +\%a`.sql.gz]

 

It's giving me the error:

/bin/sh: line 1: [/home/content/a/l/m/almo10000/html/casting-call-db/_DailyBackups/MySQL-Fri.sql.gz]: No such file or directory

mysqldump: Got error: 2005: Unknown MySQL Server Host '[host]' (1) when trying to connect

 

For security reasons I changed the host, username, password and database in the above example to just be the label for what they are. I'm leaving the path though since paths on Godaddy can be annoying and confusing sometimes.

 

Any advice on getting this to work? Is my syntax right? I'm not sure why it's telling me the file doesn't exist since that directory most definitely exists. Is my path wrong?

[trq]

host, user, password & database and your path aren't actually surrounded by [] are they? They shouldn't be.

[TheBrandon]

Yes, they were surrounded by brackets.

 

So it should be:

mysqldump --opt -Q -h [host] --user=user --password=password database | gzip -v9 - > [/home/content/a/l/m/almo10000/html/casting-call-db/_DailyBackups/MySQL-`/bin/date +\%a`.sql.gz]

 

???

 

Is database in the correct place/formatted correctly?

[trq]

You still have [] around host and your path.

 

mysqldump --opt -Q -h host --user=user --password=password database | gzip -v9 - > /home/content/a/l/m/almo10000/html/casting-call-db/_DailyBackups/MySQL-`/bin/date +\%a`.sql.gz

[TheBrandon]

Ok I'll try that tonight. Thanks! I'll let you know if it works.

[TheBrandon]

Today it gave me this error:

 

mysqldump: File '/usr/share/mysql/charsets/?.conf' not found (Errcode: 2)

mysqldump: Character set '#33' is not a compiled character set and is not specified in the '/usr/share/mysql/charsets/Index' file

mysqldump: Got error: 1251: Client does not support authentication protocol requested by server; consider upgrading MySQL client when trying to connect

  0.0%

 

Any idea what that means?

[TheBrandon]

Okay so when I run this command:

mysqldump -A -ucasting354sds -pCast85496 > /home/content/a/l/m/almo10000/html/casting-call-db/_DailyBackups/backup.sql

 

I get this error:

mysqldump: Got error: 2002: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2) when trying to connect

 

But if I run:

mysqldump -ucasting354sds -pCast85496 > /home/content/a/l/m/almo10000/html/casting-call-db/_DailyBackups/backup.sql

 

It doesn't generate an error. However it fills the backup.sql with this:

mysqldump  Ver 8.23 Distrib 3.23.58, for redhat-linux-gnu (i386)

By Igor Romanenko, Monty, Jani & Sinisa

This software comes with ABSOLUTELY NO WARRANTY. This is free software,

and you are welcome to modify and redistribute it under the GPL license

 

Dumping definition and data mysql database or table

Usage: mysqldump [OPTIONS] database [tables]

OR    mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]

OR    mysqldump [OPTIONS] --all-databases [OPTIONS]

 

  -A, --all-databases  Dump all the databases. This will be same as

        --databases with all databases selected.

  -a, --all Include all MySQL specific create options.

  -#, --debug=...      Output debug log. Often this is 'd:t:o,filename`.

  --character-sets-dir=...

                        Directory where character sets are

  -?, --help Display this help message and exit.

  -B, --databases      To dump several databases. Note the difference in

usage; In this case no tables are given. All name

arguments are regarded as databasenames.

'USE db_name;' will be included in the output

  -c, --complete-insert Use complete insert statements.

  -C, --compress        Use compression in server/client protocol.

  --default-character-set=...

                        Set the default character set

  -e, --extended-insert Allows utilization of the new, much faster

                        INSERT syntax.

  --add-drop-table Add a 'drop table' before each create.

  --add-locks Add locks around insert statements.

  --allow-keywords Allow creation of column names that are keywords.

  --delayed-insert      Insert rows with INSERT DELAYED.

  --delete-master-logs  Issue RESET MASTER on the master just after taking

                        the dump, and before unlocking tables.

                        This will automatically enable --first-slave.

  --master-data        This will cause the master position and filename to

                        be appended to your output, before unlocking tables.

                        This will automatically enable --first-slave.

  -F, --flush-logs Flush logs file in server before starting dump.

  -f, --force Continue even if we get an sql-error.

  -h, --host=... Connect to host.

  -l, --lock-tables    Lock all tables for read.

  --no-autocommit      Wrap tables with autocommit/commit statements.

  -K, --disable-keys  '/*!40000 ALTER TABLE tb_name DISABLE KEYS */;

                        and '/*!40000 ALTER TABLE tb_name ENABLE KEYS */;

                        will be put in the output.

  -n, --no-create-db    'CREATE DATABASE /*!32312 IF NOT EXISTS*/ db_name;'

                        will not be put in the output. The above line will

                        be added otherwise, if --databases or

                        --all-databases option was given.

  -t, --no-create-info Don't write table creation info.

  -d, --no-data No row information.

  -O, --set-variable var=option

                        give a variable a value. --help lists variables

  --opt Same as --add-drop-table --add-locks --all --quick

                        --extended-insert --lock-tables --disable-keys

  -p, --password[=...] Password to use when connecting to server.

                        If password is not given it's solicited on the tty.

 

  -P, --port=... Port number to use for connection.

  -q, --quick Don't buffer query, dump directly to stdout.

  -Q, --quote-names Quote table and column names with `

  -r, --result-file=... Direct output to a given file. This option should be

                        used in MSDOS, because it prevents new line '\n'

                        from being converted to '\n\r' (newline + carriage

                        return).

  -S, --socket=... Socket file to use for connection.

  --tables              Overrides option --databases (-B).

  -T, --tab=...        Creates tab separated textfile for each table to

                        given path. (creates .sql and .txt files).

                        NOTE: This only works if mysqldump is run on

                              the same machine as the mysqld daemon.

  -u, --user=# User for login if not current user.

  -v, --verbose Print info about the various stages.

  -V, --version Output version information and exit.

  -w, --where= dump only selected records; QUOTES mandatory!

  -X, --xml            dump a database as well formed XML

  -x, --first-slave    Locks all tables across all databases.

  EXAMPLES: "--where=user='jimf'" "-wuserid>1" "-wuserid<1"

  Use -T (--tab=...) with --fields-...

  --fields-terminated-by=...

                        Fields in the textfile are terminated by ...

  --fields-enclosed-by=...

                        Fields in the importfile are enclosed by ...

  --fields-optionally-enclosed-by=...

                        Fields in the i.file are opt. enclosed by ...

  --fields-escaped-by=...

                        Fields in the i.file are escaped by ...

  --lines-terminated-by=...

                        Lines in the i.file are terminated by ...

 

Default options are read from the following files in the given order:

/etc/my.cnf /var/lib/mysql/my.cnf ~/.my.cnf

The following groups are read: mysqldump client

The following options may be given as the first argument:

--print-defaults Print the program argument list and exit

--no-defaults Don't read default options from any options file

--defaults-file=# Only read default options from the given file #

--defaults-extra-file=# Read this file after the global files are read

 

Possible variables for option --set-variable (-O) are:

max_allowed_packet    current value: 25165824

net_buffer_length    current value: 1047551

 

I really need to get this working. Does anyone have any ideas or suggestions? I'm at a complete loss as to why this isn't functioning.

 

[trq]

I would contact your host.

[TheBrandon]

I have. They insist it's a scripting flaw and refuse to help.

[trq]

Then leave. The socket error is there responsibility as far as I'm concerned.

 

Have you tried specifying the hostname or ip address for the database server? If you go by this route you should be able to connect to the database via the network and get around the socket error.

[TheBrandon]

Yeah didn't I have the hostname in my first example? Unless it was coded wrong?

 

I don't know how to do this stuff very well. I've always had cron jobs I just copy/pasted so troubleshooting it is very hard for me. Can you show me an example of one using the hostname if my initial example wasn't correct?

[Pikachu2000]

For what it's worth, I've used GoDaddy hosting for several sites, and I never was able to connect to a MySQL server without specifying a hostname.

[TheBrandon]

Well my first example had the host name:

mysqldump --opt -Q -h [host] --user=[user] --password=[password] [database] | gzip -v9 - > [/home/content/a/l/m/almo10000/html/casting-call-db/_DailyBackups/MySQL-`/bin/date +\%a`.sql.gz]

Unless something else was wrong with that code?

 

Pikachu, can you post one of your godaddy crons?

[trq]

Your first example says that the hostname is [host], that is incorrect.

 

mysqldump -h ipaddress(or)hostname -A -ucasting354sds -pCast85496 > /home/content/a/l/m/almo10000/html/casting-call-db/_DailyBackups/backup.sql

[TheBrandon]

Just tried that (with the correct hostname of course) and I got this:

mysqldump: File '/usr/share/mysql/charsets/?.conf' not found (Errcode: 2)
mysqldump: Character set '#33' is not a compiled character set and is not specified in the '/usr/share/mysql/charsets/Index' file
mysqldump: Got error: 1251: Client does not support authentication protocol requested by server; consider upgrading MySQL client when trying to connect

 

[trq]

You need to take this up with your host.

[TheBrandon]

I'll contact them again but I have. Twice.

Dear Brandon,

 

Thank you for your response. 

 

I have reviewed your account and it appears that your hosting account is functioning correctly at this time and Cron Jobs are able to run without issue. This error appears to be an issue with your scripting. Unfortunately, we do not provide technical support with third-party scripting issues. We are very limited in the support we can offer for customer-written code or scripts. You may wish to consult with a community forum online or do a search on your favorite search engine as other users may have encountered a similar problem in the past and may offer helpful solutions. If there is evidence that our system is responsible for the error, please forward it to us, and we would be happy to look into the matter further. But at the moment this does not appear to be the case.

 

Please let us know if we can assist you in any other way.

 

Sincerely,

 

Micah G.

Online Support Team

 

 

Dear Brandon,

 

Thank you for your reply.  We have reviewed your account and could not find any issues with the hosting account or database. The type of error you are experiencing is most likely caused by coding or scripting. Unfortunately, because we do not provide coding support, we are unable to determine what specifically in your code may be causing the error. However, the following may be of some assistance in diagnosing the problem: 

 

The error code that you are receiving can indicate a corrupted table in your database or corrupted database code in general.  However, this can also indicate additional issues, all related to scripting.

 

You may attempt to repair your database and/or review your code to determine if there are any issues, because it appears that is the most likely cause of the error. You may also wish to enable detailed error messages as a temporary diagnostic measure. The procedure for this will vary, depending on the scripting language being used. We also recommend consulting both the vendor's online documentation, as well as the large number of online forums dedicated to coding and scripting.

 

If you determine there is no issue with the code itself and believe the issue is server related, please reply with specific evidence of this and we will investigate the matter further.

 

Please let us know if we can help you in any other way.

 

Sincerely,

 

Christian P.

Online Support Team

 

In fact they pointed me to post on a forum online...

 

I'll email them the link to this thread though. I'll let you know how it goes.

[TheBrandon]

So I got into the office this morning and had an email from Godaddy telling me that, on Godaddy servers, you cannot run cron scripts that do backups. Here is the email:

 

Dear Sir/Madam,

 

Thank you for contacting Hosting Support in regards to almoproductions.com.

 

At this time we have tested Cron on your hosting account and found that it is working. This can be verified through the file on your hosting account named /cron_test.txt. Every hour, the system will write the date and time to your hosting account to prove that Cron is working on the server. As this Cron script is working, it tells us that Cron is functioning on the server, and that there is an issue in the script that you are trying to run.

 

From your email it appears that you are trying to create a backup of your database. In the GoDaddy system, our database servers and web servers are separate machines, and therefore you will not be able to run a Cron script on the database server. You are however able to execute a PHP script that will create a backup of your database.

 

We have uploaded an example script to your hosting in the /GD-Temp directory named, mysql_dump.php. Be aware that this script is provided as an example only. No guarantee or warranty is implied by it's use, and we do not recommend that it be left permanently on your hosting account.

 

Please contact us if you have any further issues.

 

Regards,

 

Bryan W.

Advanced Hosting Support

 

For anyone curious, this is the file he uploaded:

<?
function containsInfo($value){
	if( isset($value) && ($value != '') )
		return true;
	else
		return false;
}
if( containsInfo($_REQUEST['server']) && containsInfo($_REQUEST['database']) && containsInfo($_REQUEST['username']) && containsInfo($_REQUEST['password']) ){
	if( mysql_connect(trim($_REQUEST['server']), trim($_REQUEST['username']), trim($_REQUEST['password'])) ){
		$backupFile="MySQLExport.sql";
		$sqlfile=$_SERVER['DOCUMENT_ROOT']."/".$backupFile;
		system("/usr/bin/mysqldump --opt -u" . trim($_REQUEST['username']) . " -p" . trim($_REQUEST['password']) .  " -h" . trim($_REQUEST['server']) . " " . trim($_REQUEST['database']) . " > $sqlfile");
		echo "The MySQL export has been created to your account. The file <a href='http://" . $_SERVER['HTTP_HOST'] . "/" . $backupFile . "'>" . $backupFile . "</a> was created in your root directory.";
	}
}
else{
?>
This tool is intended to create a MySQL export of your database. Simply fill in the following boxes with the MySQL settings specified on your hosting control panel and it will take care of the rest.<br/><br/>
<form action="<?=$_SERVER['PHP_SELF'];?>" method="post">
	<table border="1" cellpadding="3">
		<tr>
			<td>
				Server Location:
			</td>
			<td>
				<input type="text" name="server">
			</td>
		</tr>
		<tr>
			<td>
				Database Name:
			</td>
			<td>
				<input type="text" name="database">
			</td>
		</tr>
		<tr>
			<td>
				Username:
			</td>
			<td>
				<input type="text" name="username">
			</td>
		</tr>
		<tr>
			<td>
				Password:
			</td>
			<td>
				<input type="password" name="password">
			</td>
		</tr>
		<tr>
			<td colspan="2">
				<input type="submit" value="Start Database Dump">
			</td>
		</tr>
	</table>
</form>
<?
}
?>

 

So from here I'll either try to modify this to run without having data in the form or just find a PHP script to export and save a backup.

 

It's just frustrating. I wish Godaddy would have told me this on my first email. It's annoying that it took so many emails to get a simple "you can't do that."

[trq]

You can still do it (I'm not sure anyone at Godaddy know what there doing). You just need to use the host name that they expect to be passed to this script.

 

Seriously, I've heard nothing but bad stuff about Godaddy, if (when) you get the chance, change hosting.

[TheBrandon]

I have though. I plugged the same connection info into a PHP script and now I've got PHP doing it. So all the variables were right. I had it in the cron job at one point but it still broke it. Was my syntax wrong?

 

I dunno. I've got PHP handling it now. It's just retarded that basic things like this are complicated or impossible on Godaddy.

 

 

[Pikachu2000]

I've never had a problem with the actual service from them, but yeah, their support staff is just about as useless as tits on a snake.

[trotirider]

Hi, i tried some commands, and the sql file is always set to 0ko, so empty...

Do we really have to enable ssh to make a mysqldump cron ?

[trotirider]

Finally, seems to work fine with : http://www.phpmybackuppro.net/

and godaddy cron with this command :  /web/cgi-bin/php5 "$HOME/html/yourfolder/yourfile.php"

 

Tested and approved