CPU limit exceede during MySQL backup

[VMS]

Today I got CPU limit exceeded during MySQL database backup. The database size is 27Mb. I just clicked in the Control Panel Backup->Download a MySQL Database Backup. After a minute the site was operational again. I tried to reproduce this (repeated backup) - and oops... CPU limit exceeded again. So, the question - how can I back up my database?
Below is the relevant part of cpu_exceeded_logs directory:

Wed Dec 6 16:46:53 2006: used 7.00 seconds of cpu time for [[mysql query]]
Wed Dec 6 16:46:56 2006: used 2.00 seconds of cpu time for [[mysql query]]
Wed Dec 6 16:47:07 2006: used 11.00 seconds of cpu time for [[mysql query]]
Wed Dec 6 16:47:11 2006: used 4.00 seconds of cpu time for [[mysql query]]
Wed Dec 6 16:47:14 2006: used 2.00 seconds of cpu time for [[mysql query]]
Wed Dec 6 16:47:17 2006: used 2.00 seconds of cpu time for [[mysql query]]
Wed Dec 6 16:47:23 2006: used 4.00 seconds of cpu time for [[mysql query]]
Wed Dec 6 16:47:29 2006: used 6.00 seconds of cpu time for [[mysql query]]
Wed Dec 6 16:47:32 2006: used 2.00 seconds of cpu time for [[mysql query]]

[VMS]

Add-on:
Below is the corresponding fragment from mysql_slow_queries log:

# Wed Dec 6 16:46:53 2006
# Query_time: 7 Lock_time: 0 Rows_sent: 36230 Rows_examined: 36230
SELECT /*!40001 SQL_NO_CACHE */ * FROM `accesslog`

# Wed Dec 6 16:46:56 2006
# Query_time: 2 Lock_time: 0 Rows_sent: 1881 Rows_examined: 1881
SELECT /*!40001 SQL_NO_CACHE */ * FROM `aggregator_item`

# Wed Dec 6 16:47:07 2006
# Query_time: 11 Lock_time: 0 Rows_sent: 83 Rows_examined: 83
SELECT /*!40001 SQL_NO_CACHE */ * FROM `cache`

# Wed Dec 6 16:47:11 2006
# Query_time: 4 Lock_time: 0 Rows_sent: 1738 Rows_examined: 1738
SELECT /*!40001 SQL_NO_CACHE */ * FROM `comments`

# Wed Dec 6 16:47:14 2006
# Query_time: 2 Lock_time: 0 Rows_sent: 5989 Rows_examined: 5989
SELECT /*!40001 SQL_NO_CACHE */ * FROM `locales_source`

# Wed Dec 6 16:47:17 2006
# Query_time: 2 Lock_time: 0 Rows_sent: 638 Rows_examined: 638
SELECT /*!40001 SQL_NO_CACHE */ * FROM `node_revisions`

# Wed Dec 6 16:47:23 2006
# Query_time: 4 Lock_time: 0 Rows_sent: 313 Rows_examined: 313
SELECT /*!40001 SQL_NO_CACHE */ * FROM `search_dataset`

# Wed Dec 6 16:47:29 2006
# Query_time: 6 Lock_time: 0 Rows_sent: 50900 Rows_examined: 50900
SELECT /*!40001 SQL_NO_CACHE */ * FROM `search_index`

# Wed Dec 6 16:47:32 2006
# Query_time: 2 Lock_time: 0 Rows_sent: 25112 Rows_examined: 25112
SELECT /*!40001 SQL_NO_CACHE */ * FROM `search_total`

[DaHiA]

to avoid hitting the cpu limit ceiling, you will have to use phpMyAdmin
or better to do it using SSH << im using it to backup a 140MB MySQL database

SSH command line:
#mysqldump -u username -pPassword database_name > file_name.sql

where username and password are the login info of your plan
and database name is the name of database you want to backup
file_name.sql is the name of backup file name

[VMS]

Thank you! I'll give it a try.

[Hercules]

I just ran a MySQL Backup of 3,7MB with PHPMyAdmin and also got the grey screen of death.
My question is: did the backup get corrupted, or did only the website get suspended for a few mins?
This was the only content in the cpu log file:
CPU Exceeded Log For Wed Dec 27 15:39:55 2006
Wed Dec 27 15:39:51 2006: used 60.00 seconds of cpu time for [[mysql query]]

[Gixxer]

I just ran a MySQL Backup of 3,7MB with PHPMyAdmin and also got the grey screen of death.
My question is: did the backup get corrupted, or did only the website get suspended for a few mins?
This was the only content in the cpu log file:
CPU Exceeded Log For Wed Dec 27 15:39:55 2006
Wed Dec 27 15:39:51 2006: used 60.00 seconds of cpu time for [[mysql query]]
When you exceed the limitation for any reason your site will shutdown for a brief
period of time. It does not corrupt your DB.

When backing up your mySQL DB's the only way to not exceed the limitation is
to use SSH. phpmyadmin will hit the limit every time if you have a large DB.

[duesouth]

Do it late..0100hrs CST

[Hercules]

I already learned how to backup in PHPMyAdmin, any more stuff will likely kill my brain.
Well next time I'll do it at 2am local time so not much visitors will notice.

[cubbie866]

This happened to me yesterday as well using backup. I may have to breakdown and get SSH permission and learn how to do this. I've been apprehensive since I have to send Bluehost a copy of my driver's license to get permission. It's seems there's a bigger learning curve to SSH.

Is there an easy way to upload a backed up database using SSH? I have a backup test site that I've been using phpmyadmin to do this with.

Thanks.

[espybro]

This is an old problem:

http://www.bluehostforum.com/showthread.php?t=298&highlight=backup

[dkinzer]

You might consider using a cron job to perform an automatic periodic backup. I'm using Wolf's db_backup script (http://restkultur.ch/personal/wolf/scripts/db_backup) with a small modification (SMTP authorization). You can configure it to email the backup to you or to FTP it offsite for extra protection, in addition to leaving it on your BlueHost server.

In case you need to do an impromptu backup, the same script can accomplish that, too, all without needing SSH access (although that's a good thing to have).

[cubbie866]

You might consider using a cron job to perform an automatic periodic backup. I'm using Wolf's db_backup script (http://restkultur.ch/personal/wolf/scripts/db_backup) with a small modification (SMTP authorization). You can configure it to email the backup to you or to FTP it offsite for extra protection, in addition to leaving it on your BlueHost server.

In case you need to do an impromptu backup, the same script can accomplish that, too, all without needing SSH access (although that's a good thing to have).


This looks interesting. I'm a newbie to cron jobs as well. I'm assuming I need the linux version to install on the server?

Thank you.

[dkinzer]

I'm assuming I need the linux version to install on the server?
The Wolf db_backup script is a PHP script. I don't think that there is anything that is Linux-specific about it (but there could be). If there is a choice, always select the Linux version of something to run on BH since they run Linux servers.

Regarding cron jobs generally, check out this cPanel cron job tutorial (http://www.siteground.com/tutorials/cpanel/cron_jobs.htm). It might be helpful.

[cubbie866]

I've managed to get the wolf db_backup up and running successfuly. It seemed it didn't matter what version I used, windows or linux. Now to automate it with cron job. I appreciate the link you provided.

Edit: Unfortunately that tutorial didn't help.

Do I insert this line with quotes into command?

"0 0 * * * wget --http-user=webuser --http-passwd=webpassword --html-extension --directory-prefix=public_html/admin http://www.example.com/db_backup/db_backup.php"

replacing www.example.com with my site as well as webuser and webpassword? It says to test the command before putting it in. But I don't see where I can test the command?

[dkinzer]

"0 0 * * * wget --http-user=webuser --http-passwd=webpassword --html-extension --directory-prefix=public_html/admin http://www.example.com/db_backup/db_backup.php"

replacing www.example.com with my site as well as webuser and webpassword? It says to test the command before putting it in. But I don't see where I can test the command?
Although you can put the backup script in your public_html directory tree, I wouldn't recommend it because it will be accessible to anyone with a browser. That is OK, of course, for short term testing, however.

I put my scripts such as this in a directory that is off of my home directory, something like /home/myusername/script_directory. Then, I use the following command in the cron entry:

(cd $HOME/script_directory/db_backup; /usr/bin/php db_backup.php > /dev/null)In your example above, the "0 0 * * *" portion specifies the time at which the command will run. That particular specification says to run the command at midnight (zero minute, zero hour, every day). You can't specify the time directly like that on BlueHost because you can't directly edit the crontab file. Rather, you use the cron interface in cPanel. I use the "Advanced Unix Style" because it is closer to actually editing the crontab file like I'm used to but you may prefer to use the "Standard" cron interface. In either case, you specify the times to run a command and specify the command to run. That's it in a nutshell.

The command that I've shown is a little more complex than normal. The parentheses around the entirety arrange for all of the contained commands to be run in sequence, the commands are separated by a semicolon. In this case there are two commands. The first specifies a directory change to my script directory and the second invokes the PHP interpreter giving it the name of the script to run. In this particular case, specifying the directory change doesn't afford much advantage but often, a script takes parameters that may need filenames. Changing the current directory helps reduce length of those filenames if they happen to be in the same directory. A variation of my command that will have largely the same effect is:

/usr/bin/php $HOME/script_directory/db_backup/db_backup.php

For testing, you can temporarily modify the cron tab entry so that it runs at the bottom of the hour, at the top of the next hour, or five minutes from now. You'll probably want to enable log generation in the db_backup configuration. That way, you'll be able to tell when it runs and if it doesn't run properly you might be able to find out why.

This biggest problem is that if there is something wrong with the way that you specify the cron command it won't run and you'll have no indication as to why. This is another instance where having SSH access is useful. If you had SSH access you could simply run the command from the SSH command prompt. It is true that the environment in which the command runs is slightly different between cron and SSH but it is fairly close.

You can get SSH access by requesting it from support. You'll have to provide some sort of identification verification (e.g. passport) but that's not much of a burden.

[cubbie866]

Thank you so much for all your detailed help, dkinzer! Your explanation and suggestions were used and all worked perfectly. :D

I am stunned with how helpful you've been.

[shae marks]

Today I got CPU limit exceeded during MySQL database backup. The database size is 27Mb. I just clicked in the Control Panel Backup->Download a MySQL Database Backup. After a minute the site was operational again. I tried to reproduce this (repeated backup) - and oops... CPU limit exceeded again. So, the question - how can I back up my database?
Below is the relevant part of cpu_exceeded_logs directory:

Wed Dec 6 16:46:53 2006: used 7.00 seconds of cpu time for [[mysql query]]
Wed Dec 6 16:46:56 2006: used 2.00 seconds of cpu time for [[mysql query]]
Wed Dec 6 16:47:07 2006: used 11.00 seconds of cpu time for [[mysql query]]
Wed Dec 6 16:47:11 2006: used 4.00 seconds of cpu time for [[mysql query]]
Wed Dec 6 16:47:14 2006: used 2.00 seconds of cpu time for [[mysql query]]
Wed Dec 6 16:47:17 2006: used 2.00 seconds of cpu time for [[mysql query]]
Wed Dec 6 16:47:23 2006: used 4.00 seconds of cpu time for [[mysql query]]
Wed Dec 6 16:47:29 2006: used 6.00 seconds of cpu time for [[mysql query]]
Wed Dec 6 16:47:32 2006: used 2.00 seconds of cpu time for [[mysql query]]Below is the corresponding fragment from mysql_slow_queries log:

# Wed Dec 6 16:46:53 2006
# Query_time: 7 Lock_time: 0 Rows_sent: 36230 Rows_examined: 36230
SELECT /*!40001 SQL_NO_CACHE */ * FROM `accesslog`

# Wed Dec 6 16:46:56 2006
# Query_time: 2 Lock_time: 0 Rows_sent: 1881 Rows_examined: 1881
SELECT /*!40001 SQL_NO_CACHE */ * FROM `aggregator_item`

# Wed Dec 6 16:47:07 2006
# Query_time: 11 Lock_time: 0 Rows_sent: 83 Rows_examined: 83
SELECT /*!40001 SQL_NO_CACHE */ * FROM `cache`

# Wed Dec 6 16:47:11 2006
# Query_time: 4 Lock_time: 0 Rows_sent: 1738 Rows_examined: 1738
SELECT /*!40001 SQL_NO_CACHE */ * FROM `comments`

# Wed Dec 6 16:47:14 2006
# Query_time: 2 Lock_time: 0 Rows_sent: 5989 Rows_examined: 5989
SELECT /*!40001 SQL_NO_CACHE */ * FROM `locales_source`

# Wed Dec 6 16:47:17 2006
# Query_time: 2 Lock_time: 0 Rows_sent: 638 Rows_examined: 638
SELECT /*!40001 SQL_NO_CACHE */ * FROM `node_revisions`

# Wed Dec 6 16:47:23 2006
# Query_time: 4 Lock_time: 0 Rows_sent: 313 Rows_examined: 313
SELECT /*!40001 SQL_NO_CACHE */ * FROM `search_dataset`

# Wed Dec 6 16:47:29 2006
# Query_time: 6 Lock_time: 0 Rows_sent: 50900 Rows_examined: 50900
SELECT /*!40001 SQL_NO_CACHE */ * FROM `search_index`

# Wed Dec 6 16:47:32 2006
# Query_time: 2 Lock_time: 0 Rows_sent: 25112 Rows_examined: 25112
SELECT /*!40001 SQL_NO_CACHE */ * FROM `search_total` shae marks (http://mielofon.com/model/shae_marks)