If I archive htdocs dose that arches databases too

[Dbreh]

Hello
If I archive htdocs dose that arches databases too

Or do I need extra action to do for archiving MySql database

Note
My database is more than 100 Mb, so I need to archive it and leave it in the website before transferring

[BerksWebGuy]

Your web files (htdocs) and your database are on 2 different servers. You have to back them up separately.

[Dbreh]

OK,
My database is more than 100 Mb,
so I need to archive it
and leave it in the website (/htdocs/)

I try many scripts but they don't function well

Is their any way to archive that data and leave it in the website (/htdocs/)

[cc1030]

This script will run the mysqldump application which will output your database to an .sql file (it does not compress the output). You have to be careful, though, because of the 60-second script time limit. If the server spends more than 60 seconds trying to export your database, it won't finish, and you won't get a full backup. Anyway, here's the script:

database_backup.cgi
#!/bin/sh

# This NOW= line must be the first line of the file.
NOW=`date "+%Y%m%d_%H%M%S"`

# SAVE_PATH: The directory you want your backup and error files saved to
# (SAVE_PATH must include the trailing slash).
# DB_SERVER: The server your database is on (exmaple: mysql01.powweb.com).
# DB_NAME: The name of your database.
# DB_USERNAME: The username you use to log in to your database.
# DB_PASSWORD: The password you use to log in to your database.
# BACKUP_FILE: The name of the file you want to back up the database to
# (this will overwrite any existing file with the same name).
# ERROR_FILE: The name of the file you want errors reported to
# (this will overwrite any existing file with the same name).
# SEND_EMAIL: Set to YES if you want the script to send you an email when
# the backup is complete. Comment out the SEND_EMAIL= line
# if you do not want the script to send you an email.
# EMAIL_FROM: If SEND_EMAIL=YES, the mail is "From" this address.
# EMAIL_TO: If SEND_EMAIL=YES, the mail is sent "To" this address.
# EMAIL_SUBJECT: If SEND_EMAIL=YES, this is the subject line.

SAVE_PATH="/home/users/web/bNNN/pow.thetruthisav/"
DB_SERVER="mysql01.powweb.com"
DB_NAME="database_name"
DB_USERNAME="database_user"
DB_PASSWORD="database_password"
BACKUP_FILE="database_backup_${NOW}.sql"
ERROR_FILE="mysqldump.err"
SEND_EMAIL="YES"
EMAIL_FROM="Database Backup <db_backup@example.com>"
EMAIL_TO="user@example.com"
EMAIL_SUBJECT="Database backup ${NOW}"


################################################## #########################
# Do not touch anything below this line!
################################################## #########################

cd ${SAVE_PATH}

BACKUP_BEGIN=`date "+%A, %B %d %Y, at %l:%M:%S%P"`

/usr/bin/mysqldump \
--user=${DB_USERNAME} \
--password=${DB_PASSWORD} \
--host=${DB_SERVER} \
${DB_NAME} \
> ${BACKUP_FILE} \
2> ${ERROR_FILE}

BACKUP_COMPLETE=`date "+%A, %B %d %Y, at %l:%M:%S%P"`

BACKUP_SIZE=`ls -l ${BACKUP_FILE} | awk '{print $5}'`
BACKUP_ERR_FILE=`cat ${ERROR_FILE}`
[ -n "${BACKUP_ERR_FILE}" ] && {
BACKUP_ERR="`echo -e "Errors:\n${BACKUP_ERR_FILE}"`"
} || {
BACKUP_ERR="No errors"
}

MESSAGE=`echo -e "Backup of database ${DB_NAME} on server ${DB_SERVER}:\n\
Backup began on ${BACKUP_BEGIN}\n\
Backup completed on ${BACKUP_COMPLETE}\n\
\n\
Location of backup: ${SAVE_PATH}\n\
Name of backup: ${BACKUP_FILE}\n\
Size of backup: ${BACKUP_SIZE}\n\
\n\
${BACKUP_ERR}"`

[ -n "${SEND_EMAIL}" ] && {
echo -e "From: ${EMAIL_FROM}\n\
To: ${EMAIL_TO}\n\
Subject: ${EMAIL_SUBJECT}\n\
\n\
${MESSAGE}" \
| /usr/sbin/sendmail -t -i
}

echo -e "Content-Type: text/plain\n\
\n\
${MESSAGE}"

You'll need to change those settings to match your database and email settings. You can set this to run as a scheduled job (but there seem to be issues with scheduled jobs not running right now), or you can call it from your browser.

[Dbreh]

Thanks a lot sir, well great help
So far, that's very nice

Now, could I find another cgi script for restoring Database again to a new database
And what's the way to extend the time to be more than 60 second

[cc1030]

Restoring to a new database would be more complicated. First, you would need to create the database in OPS, then modify the database backup file to select the new database before executing the queries. The entire process cannot be automated. The restore part (once the database is created) probably could, but not the whole thing.

As for how you go about extending the script time limit beyond 60 seconds, you can't. That is a limit imposed by Endurance for all CGI scripts. There is no way around it.