PHP Language - MySQL backup script

Author

MySQL backup script

Peter

2004-12-23, 3:58 am

Hello,

I'm trying to setup a cron job to backup MySQL database. My hosting provider
recommended my this script:

#!/bin/sh
date=`date '+%m-%d-%y'`
mysqldump -upeter_userid -ppeter_password >
/home/peter/mysql_backup/peter_test.$date.sql
chown peter:peter mysql_backup -R

where "peter" is a hosting account user name,
"test" is a database name,
"userid" is a database user name,
"password" is a database password

Something is wrong with this script. It produces 0 bytes file. I don't see
here the database name that should be backed up - peter_test. Also, should I
use the account user name with password as well: peter_password?

What would be the correct script?

I would appreciate your help.

Thank you,

--
Peter Afonin

Gordon Burditt

2004-12-23, 3:58 am

>I'm trying to setup a cron job to backup MySQL database. My hosting provider
>recommended my this script:
>
>#!/bin/sh
>date=`date '+%m-%d-%y'`
>mysqldump -upeter_userid -ppeter_password >
>/home/peter/mysql_backup/peter_test.$date.sql

The above two lines should be all on one line (among other problems).

>chown peter:peter mysql_backup -R
>
>where "peter" is a hosting account user name,
>"test" is a database name,
"peter_test" is a database name
>"userid" is a database user name,
"peter_userid" is a database user name
>"password" is a database password
"peter_password" is a database password
>
>Something is wrong with this script. It produces 0 bytes file. I don't see
>here the database name that should be backed up - peter_test. Also, should I
>use the account user name with password as well: peter_password?

mysqldump -upeter_userid -ppeter_password peter_test > /home/peter/mysql_backup/peter_test.$date.sql

The argument following -p is the password, the whole password, and
nothing but the password (No (say it out loud) pee eeh tee eeh are
underscore in front of it). The same applies to -u about the MySQL
userid.

You didn't specify the host, but if this is run ON the database
server itself, it will default properly.

If you have access to a sh shell, try typing this command manually.
Watch for error messages being output to stderr. Then check if you
got output into the file. If you have more than one database and
want to back up all of them, do it one at a time.

peter_userid and peter_password are the MySQL identification, not
the hosting login info (although they are likely the same).

I prefer dates that collate properly and have the correct year
(all 4 digits of it), like a MySQL date:
date=`date '+%Y-%m-%d'`
which produces something like: 2004-12-23 . You can name your files
anything you want inside directories you can write in.

"mysqldump --help" shows you how to invoke it. The output varies
somewhat with its version, and any path names are local to the
system it's running on. See below.

Gordon L. Burditt

mysqldump Ver 10.8 Distrib 4.1.7, for portbld-freebsd4.9 (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]

Default options are read from the following files in the given order:
/etc/my.cnf /var/db/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
-a, --all Deprecated. Use --create-options instead.
-A, --all-databases Dump all the databases. This will be same as --databases
with all databases selected.
--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.
--character-sets-dir=name
Directory where character sets are.
--compatible=name Change the dump to be compatible with a given mode. By
default tables are dumped in a format optimized for
MySQL. Legal modes are: ansi, mysql323, mysql40,
postgresql, oracle, mssql, db2, maxdb, no_key_options,
no_table_options, no_field_options. One can use several
modes separated by commas. Note: Requires MySQL server
version 4.1.0 or higher. This option is ignored with
earlier server versions.
--compact Give less verbose output (useful for debugging). Disables
structure comments and header/footer constructs. Enables
options --skip-add-drop-table --no-set-names
--skip-disable-keys --skip-lock-tables
-c, --complete-insert
Use complete insert statements.
-C, --compress Use compression in server/client protocol.
--create-options Include all MySQL specific create options.
-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.
-#, --debug[=#] This is a non-debug version. Catch this and exit
--default-character-set=name
Set the default character set.
--delayed-insert Insert rows with INSERT DELAYED.
--delete-master-logs
Delete logs on master after backup. This automatically
enables --first-slave.
-K, --disable-keys '/*!40000 ALTER TABLE tb_name DISABLE KEYS */; and
'/*!40000 ALTER TABLE tb_name ENABLE KEYS */; will be put
in the output.
-e, --extended-insert
Allows utilization of the new, much faster INSERT syntax.
--fields-terminated-by=name
Fields in the textfile are terminated by ...
--fields-enclosed-by=name
Fields in the importfile are enclosed by ...
--fields-optionally-enclosed-by=name
Fields in the i.file are opt. enclosed by ...
--fields-escaped-by=name
Fields in the i.file are escaped by ...
-x, --first-slave Locks all tables across all databases.
-F, --flush-logs Flush logs file in server before starting dump. Note that
if you dump many databases at once (using the option
--databases= or --all-databases), the logs will be
flushed for each database dumped.
-f, --force Continue even if we get an sql-error.
-?, --help Display this help message and exit.
-h, --host=name Connect to host.
--lines-terminated-by=name
Lines in the i.file are terminated by ...
-l, --lock-tables Lock all tables for read.
--master-data This causes the master position and filename to be
appended to your output. This automatically enables
--first-slave.
--no-autocommit Wrap tables with autocommit/commit statements.
--single-transaction
Dump all tables in single transaction to get consistent
snapshot. Mutually exclusive with --lock-tables.
-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.
-N, --no-set-names Deprecated. Use --skip-set-charset instead.
--set-charset Add 'SET NAMES default_character_set' to the output.
Enabled by default; suppress with --skip-set-charset.
-O, --set-variable=name
Change the value of a variable. Please note that this
option is deprecated; you can set variables directly with
--variable-name=value.
--opt Same as --add-drop-table, --add-locks, --create-options,
--quick, --extended-insert, --lock-tables, --set-charset,
and --disable-keys. Enabled by default, disable with
--skip-opt.
-p, --password[=name]
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.
--protocol=name The protocol of connection (tcp,socket,pipe,memory).
-q, --quick Don't buffer query, dump directly to stdout.
-Q, --quote-names Quote table and column names with backticks (`).
-r, --result-file=name
Direct output to a given file. This option should be used
in MSDOS, because it prevents new line '\n' from being
converted to '\r\n' (carriage return + line feed).
--skip-opt Disable --opt. Disables --add-drop-table, --add-locks,
--create-options, --quick, --extended-insert,
--lock-tables, --set-charset, and --disable-keys.
-S, --socket=name Socket file to use for connection.
--ssl Enable SSL for connection (automatically enabled with
other flags). Disable with --skip-ssl.
--ssl-key=name X509 key in PEM format (implies --ssl).
--ssl-cert=name X509 cert in PEM format (implies --ssl).
--ssl-ca=name CA file in PEM format (check OpenSSL docs, implies
--ssl).
--ssl-capath=name CA directory (check OpenSSL docs, implies --ssl).
--ssl-cipher=name SSL cipher to use (implies --ssl).
-T, --tab=name 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.
--tables Overrides option --databases (-B).
-u, --user=name User for login if not current user.
-v, --verbose Print info about the various stages.
-V, --version Output version information and exit.
-w, --where=name Dump only selected records; QUOTES mandatory!
-X, --xml Dump a database as well formed XML.
--max_allowed_packet=#
--net_buffer_length=#
-i, --comments Write additional information.
--hex-blob Dump BLOBs in HEX. this mode does not work with
extended-insert

Variables (--variable-name=value)
and boolean options {FALSE|TRUE} Value (after reading options)
--------------------------------- -----------------------------
all TRUE
all-databases FALSE
add-drop-table TRUE
add-locks TRUE
allow-keywords FALSE
character-sets-dir (No default value)
compatible (No default value)
compact FALSE
complete-insert FALSE
compress FALSE
create-options TRUE
databases FALSE
default-character-set utf8
delayed-insert FALSE
disable-keys TRUE
extended-insert TRUE
fields-terminated-by (No default value)
fields-enclosed-by (No default value)
fields-optionally-enclosed-by (No default value)
fields-escaped-by (No default value)
first-slave FALSE
flush-logs FALSE
force FALSE
host mysql.domain.com
lines-terminated-by (No default value)
lock-tables TRUE
no-autocommit FALSE
single-transaction FALSE
no-create-db FALSE
no-create-info FALSE
no-data FALSE
set-charset TRUE
port 3306
quick TRUE
quote-names TRUE
socket (No default value)
ssl FALSE
ssl-key (No default value)
ssl-cert (No default value)
ssl-ca (No default value)
ssl-capath (No default value)
ssl-cipher (No default value)
tab (No default value)
user (No default value)
verbose FALSE
where (No default value)
max_allowed_packet 25165824
net_buffer_length 1047551
comments TRUE
hex-blob FALSE

skadmin

2004-12-23, 3:58 am

This is the PHP script I use
Replace the X's with your information.

<?php
$emailaddress = "you@yourdomain.com";
$host="XXXXX.XXXXX.com"; // database host address
$dbuser="XXXXXX"; // database user name
$dbpswd="XXXXXX"; // database password
$mysqldb="XXXXXX"; // name of database
$day = (date("d"));
$filename="/XXXX/XXXXXX/backup$day.sql";
if( file_exists($filename) ) {
unlink("$filename");
}
system(
"/usr/local/bin/mysqldump --opt --no-create-db --user=$dbuser --password=$dbpswd
--host=$host $mysqldb > $filename",$result);
$size = filesize("$filename");
$runtime = (date(" F d h:ia"));
$message .= "The backup has been run.\n\n";
$message .= "The return code was: $result\n\n";
$message .= "Size of the backup: $size bytes\n\n";
$message .= "Server time of the backup: $runtime\n\n";
mail($emailaddress, "Backup Message" , $message, "From: Website <>");
?>

Then I add this line to my Cron file:
0 0 * * * /usr/local/bin/php /xxxx/xxxxxx/etc/sqlbackup.php

This script backs up my database every night at midnight.

"Peter" <pva@speakeasy.net> wrote in message
news:74idnWmF8-_D1FfcRVn-jg@speakeasy.net...[color=darkred]
> Hello,
>
> I'm trying to setup a cron job to backup MySQL database. My hosting
> provider
> recommended my this script:
>
> #!/bin/sh
> date=`date '+%m-%d-%y'`
> mysqldump -upeter_userid -ppeter_password >
> /home/peter/mysql_backup/peter_test.$date.sql
> chown peter:peter mysql_backup -R
>
> where "peter" is a hosting account user name,
> "test" is a database name,
> "userid" is a database user name,
> "password" is a database password
>
> Something is wrong with this script. It produces 0 bytes file. I don't see
> here the database name that should be backed up - peter_test. Also, should
> I
> use the account user name with password as well: peter_password?
>
> What would be the correct script?
>
> I would appreciate your help.
>
> Thank you,
>
>
> --
> Peter Afonin
>
>

Peter

2004-12-23, 3:58 am

Thank you very much everyone, I'll try.

Peter

"skadmin" <Someplace@Nowhere.com> wrote in message
news:XVsyd.244129$V41.190565@attbi_s52...
> This is the PHP script I use
> Replace the X's with your information.
>
>
> <?php
> $emailaddress = "you@yourdomain.com";
> $host="XXXXX.XXXXX.com"; // database host address
> $dbuser="XXXXXX"; // database user name
> $dbpswd="XXXXXX"; // database password
> $mysqldb="XXXXXX"; // name of database
> $day = (date("d"));
> $filename="/XXXX/XXXXXX/backup$day.sql";
> if( file_exists($filename) ) {
> unlink("$filename");
> }
> system(
>
"/usr/local/bin/mysqldump --opt --no-create-db --user=$dbuser --password=$db
pswd
> --host=$host $mysqldb > $filename",$result);
> $size = filesize("$filename");
> $runtime = (date(" F d h:ia"));
> $message .= "The backup has been run.\n\n";
> $message .= "The return code was: $result\n\n";
> $message .= "Size of the backup: $size bytes\n\n";
> $message .= "Server time of the backup: $runtime\n\n";
> mail($emailaddress, "Backup Message" , $message, "From: Website <>");
> ?>
>
>
> Then I add this line to my Cron file:
> 0 0 * * * /usr/local/bin/php /xxxx/xxxxxx/etc/sqlbackup.php
>
> This script backs up my database every night at midnight.
>
>
>
>
>
>
> "Peter" <pva@speakeasy.net> wrote in message
> news:74idnWmF8-_D1FfcRVn-jg@speakeasy.net...
see[color=darkred]
should[color=darkred]
>
>

Peter

2004-12-23, 8:56 am

Thank you, Gordon.

Tried to follow your recommendations.

Created a user test with password test, gave all rights, database ddt2_dmf.
Now my file looks like this:

#!/bin/sh
date=`date '+%m-%d-%y'`
mysqldump -utest -ptest ddt2_dmf >
/home/ddt2/mysql_backup/ddt2_test.$date.sql
chown ddt2:ddt2 mysql_backup -R

Getting error:

mysqldump: Got error: 1045: Access denied for user: 'test@localhost' (Using
password: YES) when trying to connect

What am I still doing wrong?

Thank you,

Peter

"Gordon Burditt" <gordonb.wnyv4@burditt.org> wrote in message
news:cqdkt9$7kj@library1.airnews.net...
provider[color=darkred]
>
> The above two lines should be all on one line (among other problems).
>
> "peter_test" is a database name
> "peter_userid" is a database user name
> "peter_password" is a database password
see[color=darkred]
should I[color=darkred]
>
> mysqldump -upeter_userid -ppeter_password peter_test >
/home/peter/mysql_backup/peter_test.$date.sql
>
> The argument following -p is the password, the whole password, and
> nothing but the password (No (say it out loud) pee eeh tee eeh are
> underscore in front of it). The same applies to -u about the MySQL
> userid.
>
> You didn't specify the host, but if this is run ON the database
> server itself, it will default properly.
>
> If you have access to a sh shell, try typing this command manually.
> Watch for error messages being output to stderr. Then check if you
> got output into the file. If you have more than one database and
> want to back up all of them, do it one at a time.
>
> peter_userid and peter_password are the MySQL identification, not
> the hosting login info (although they are likely the same).
>
> I prefer dates that collate properly and have the correct year
> (all 4 digits of it), like a MySQL date:
> date=`date '+%Y-%m-%d'`
> which produces something like: 2004-12-23 . You can name your files
> anything you want inside directories you can write in.
>
> "mysqldump --help" shows you how to invoke it. The output varies
> somewhat with its version, and any path names are local to the
> system it's running on. See below.
>
> Gordon L. Burditt
>
> mysqldump Ver 10.8 Distrib 4.1.7, for portbld-freebsd4.9 (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]
>
> Default options are read from the following files in the given order:
> /etc/my.cnf /var/db/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
> -a, --all Deprecated. Use --create-options instead.
> -A, --all-databases Dump all the databases. This will be same
as --databases
> with all databases selected.
> --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.
> --character-sets-dir=name
> Directory where character sets are.
> --compatible=name Change the dump to be compatible with a given mode.
By
> default tables are dumped in a format optimized for
> MySQL. Legal modes are: ansi, mysql323, mysql40,
> postgresql, oracle, mssql, db2, maxdb,
no_key_options,
> no_table_options, no_field_options. One can use
several
> modes separated by commas. Note: Requires MySQL
server
> version 4.1.0 or higher. This option is ignored with
> earlier server versions.
> --compact Give less verbose output (useful for debugging).
Disables
> structure comments and header/footer constructs.
Enables
> options --skip-add-drop-table --no-set-names
> --skip-disable-keys --skip-lock-tables
> -c, --complete-insert
> Use complete insert statements.
> -C, --compress Use compression in server/client protocol.
> --create-options Include all MySQL specific create options.
> -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.
> -#, --debug[=#] This is a non-debug version. Catch this and exit
> --default-character-set=name
> Set the default character set.
> --delayed-insert Insert rows with INSERT DELAYED.
> --delete-master-logs
> Delete logs on master after backup. This
automatically
> enables --first-slave.
> -K, --disable-keys '/*!40000 ALTER TABLE tb_name DISABLE KEYS */; and
> '/*!40000 ALTER TABLE tb_name ENABLE KEYS */; will
be put
> in the output.
> -e, --extended-insert
> Allows utilization of the new, much faster INSERT
syntax.
> --fields-terminated-by=name
> Fields in the textfile are terminated by ...
> --fields-enclosed-by=name
> Fields in the importfile are enclosed by ...
> --fields-optionally-enclosed-by=name
> Fields in the i.file are opt. enclosed by ...
> --fields-escaped-by=name
> Fields in the i.file are escaped by ...
> -x, --first-slave Locks all tables across all databases.
> -F, --flush-logs Flush logs file in server before starting dump. Note
that
> if you dump many databases at once (using the option
> --databases= or --all-databases), the logs will be
> flushed for each database dumped.
> -f, --force Continue even if we get an sql-error.
> -?, --help Display this help message and exit.
> -h, --host=name Connect to host.
> --lines-terminated-by=name
> Lines in the i.file are terminated by ...
> -l, --lock-tables Lock all tables for read.
> --master-data This causes the master position and filename to be
> appended to your output. This automatically enables
> --first-slave.
> --no-autocommit Wrap tables with autocommit/commit statements.
> --single-transaction
> Dump all tables in single transaction to get
consistent
> snapshot. Mutually exclusive with --lock-tables.
> -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.
> -N, --no-set-names Deprecated. Use --skip-set-charset instead.
> --set-charset Add 'SET NAMES default_character_set' to the output.
> Enabled by default; suppress
with --skip-set-charset.
> -O, --set-variable=name
> Change the value of a variable. Please note that
this
> option is deprecated; you can set variables directly
with
> --variable-name=value.
> --opt Same
as --add-drop-table, --add-locks, --create-options,
> --quick, --extended-insert, --lock-tables, --set-cha
rset,
> and --disable-keys. Enabled by default, disable with
> --skip-opt.
> -p, --password[=name]
> 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.
> --protocol=name The protocol of connection (tcp,socket,pipe,memory).
> -q, --quick Don't buffer query, dump directly to stdout.
> -Q, --quote-names Quote table and column names with backticks (`).
> -r, --result-file=name
> Direct output to a given file. This option should be
used
> in MSDOS, because it prevents new line '\n' from
being
> converted to '\r\n' (carriage return + line feed).
> --skip-opt Disable --opt.
Disables --add-drop-table, --add-locks,
> --create-options, --quick, --extended-insert,
> --lock-tables, --set-charset, and --disable-keys.
> -S, --socket=name Socket file to use for connection.
> --ssl Enable SSL for connection (automatically enabled
with
> other flags). Disable with --skip-ssl.
> --ssl-key=name X509 key in PEM format (implies --ssl).
> --ssl-cert=name X509 cert in PEM format (implies --ssl).
> --ssl-ca=name CA file in PEM format (check OpenSSL docs, implies
> --ssl).
> --ssl-capath=name CA directory (check OpenSSL docs, implies --ssl).
> --ssl-cipher=name SSL cipher to use (implies --ssl).
> -T, --tab=name 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.
> --tables Overrides option --databases (-B).
> -u, --user=name User for login if not current user.
> -v, --verbose Print info about the various stages.
> -V, --version Output version information and exit.
> -w, --where=name Dump only selected records; QUOTES mandatory!
> -X, --xml Dump a database as well formed XML.
> --max_allowed_packet=#
> --net_buffer_length=#
> -i, --comments Write additional information.
> --hex-blob Dump BLOBs in HEX. this mode does not work with
> extended-insert
>
> Variables (--variable-name=value)
> and boolean options {FALSE|TRUE} Value (after reading options)
> --------------------------------- -----------------------------
> all TRUE
> all-databases FALSE
> add-drop-table TRUE
> add-locks TRUE
> allow-keywords FALSE
> character-sets-dir (No default value)
> compatible (No default value)
> compact FALSE
> complete-insert FALSE
> compress FALSE
> create-options TRUE
> databases FALSE
> default-character-set utf8
> delayed-insert FALSE
> disable-keys TRUE
> extended-insert TRUE
> fields-terminated-by (No default value)
> fields-enclosed-by (No default value)
> fields-optionally-enclosed-by (No default value)
> fields-escaped-by (No default value)
> first-slave FALSE
> flush-logs FALSE
> force FALSE
> host mysql.domain.com
> lines-terminated-by (No default value)
> lock-tables TRUE
> no-autocommit FALSE
> single-transaction FALSE
> no-create-db FALSE
> no-create-info FALSE
> no-data FALSE
> set-charset TRUE
> port 3306
> quick TRUE
> quote-names TRUE
> socket (No default value)
> ssl FALSE
> ssl-key (No default value)
> ssl-cert (No default value)
> ssl-ca (No default value)
> ssl-capath (No default value)
> ssl-cipher (No default value)
> tab (No default value)
> user (No default value)
> verbose FALSE
> where (No default value)
> max_allowed_packet 25165824
> net_buffer_length 1047551
> comments TRUE
> hex-blob FALSE

RotterdamStudents

2004-12-23, 3:58 pm

> <?php
> $emailaddress = "you@yourdomain.com";
> $host="XXXXX.XXXXX.com"; // database host address
> $dbuser="XXXXXX"; // database user name
> $dbpswd="XXXXXX"; // database password
> $mysqldb="XXXXXX"; // name of database
> $day = (date("d"));
> $filename="/XXXX/XXXXXX/backup$day.sql";
> if( file_exists($filename) ) {
> unlink("$filename");
> }
> system(
>
"/usr/local/bin/mysqldump --opt --no-create-db --user=$dbuser --password=$db
pswd
> --host=$host $mysqldb > $filename",$result);
> $size = filesize("$filename");
> $runtime = (date(" F d h:ia"));
> $message .= "The backup has been run.\n\n";
> $message .= "The return code was: $result\n\n";
> $message .= "Size of the backup: $size bytes\n\n";
> $message .= "Server time of the backup: $runtime\n\n";
> mail($emailaddress, "Backup Message" , $message, "From: Website <>");
> ?>
>
>
> Then I add this line to my Cron file:
> 0 0 * * * /usr/local/bin/php /xxxx/xxxxxx/etc/sqlbackup.php
>
> This script backs up my database every night at midnight.

Hi,

i'm trying to use your script, but get the next message:
Warning: filesize(): SAFE MODE Restriction in effect. The script whose uid
is 10210 is not allowed to access /root owned by uid 0 in
/usr/local/psa/home/vhosts/mydomain.com/httpdocs/backup.php on line 15

What's wrong with it ??

Martijn

Ike

2004-12-23, 3:58 pm

Any possibility you migh also post your corresponding "restore," script here
as well? Thanks, Ike

"skadmin" <Someplace@Nowhere.com> wrote in message
news:XVsyd.244129$V41.190565@attbi_s52...
> This is the PHP script I use
> Replace the X's with your information.
>
>
> <?php
> $emailaddress = "you@yourdomain.com";
> $host="XXXXX.XXXXX.com"; // database host address
> $dbuser="XXXXXX"; // database user name
> $dbpswd="XXXXXX"; // database password
> $mysqldb="XXXXXX"; // name of database
> $day = (date("d"));
> $filename="/XXXX/XXXXXX/backup$day.sql";
> if( file_exists($filename) ) {
> unlink("$filename");
> }
> system(
>
"/usr/local/bin/mysqldump --opt --no-create-db --user=$dbuser --password=$db
pswd
> --host=$host $mysqldb > $filename",$result);
> $size = filesize("$filename");
> $runtime = (date(" F d h:ia"));
> $message .= "The backup has been run.\n\n";
> $message .= "The return code was: $result\n\n";
> $message .= "Size of the backup: $size bytes\n\n";
> $message .= "Server time of the backup: $runtime\n\n";
> mail($emailaddress, "Backup Message" , $message, "From: Website <>");
> ?>
>
>
> Then I add this line to my Cron file:
> 0 0 * * * /usr/local/bin/php /xxxx/xxxxxx/etc/sqlbackup.php
>
> This script backs up my database every night at midnight.
>
>
>
>
>
>
> "Peter" <pva@speakeasy.net> wrote in message
> news:74idnWmF8-_D1FfcRVn-jg@speakeasy.net...
see[color=darkred]
should[color=darkred]
>
>

skadmin

2004-12-23, 3:58 pm

To Restore replace this line:
system(
"/usr/local/bin/mysqldump --opt --no-create-db --user=$dbuser --password=$dbpswd
--host=$host $mysqldb > $filename",$result);

With This line:
system( "/usr/local/bin/mysql --user=$dbuser --password=$dbpswd --host=$host
$mysqldb < $filename",$result);

"Ike" <rxv@hotmail.com> wrote in message
news:diAyd.7904$RH4.5124@newsread1.news.pas.earthlink.net...
> Any possibility you migh also post your corresponding "restore," script
> here
> as well? Thanks, Ike
>
> "skadmin" <Someplace@Nowhere.com> wrote in message
> news:XVsyd.244129$V41.190565@attbi_s52...
> "/usr/local/bin/mysqldump --opt --no-create-db --user=$dbuser --password=$db
> pswd
> see
> should
>
>

Gordon Burditt

2004-12-23, 3:58 pm

In article <C_adnQM9S9ej6lfcRVn-2g@speakeasy.net>,
Peter <pva@speakeasy.net> wrote:
>Thank you, Gordon.
>
>Tried to follow your recommendations.
>
>Created a user test with password test, gave all rights, database ddt2_dmf.
>Now my file looks like this:
>
>#!/bin/sh
>date=`date '+%m-%d-%y'`
>mysqldump -utest -ptest ddt2_dmf >
>/home/ddt2/mysql_backup/ddt2_test.$date.sql
>chown ddt2:ddt2 mysql_backup -R
>
>Getting error:
>
>mysqldump: Got error: 1045: Access denied for user: 'test@localhost' (Using
>password: YES) when trying to connect
>
>What am I still doing wrong?

You have the user/password set up incorrectly.
Can you log in with the command-line client ('mysql') using that
user name, password, and database?

Gordon L. Burditt

Peter Afonin

2004-12-23, 8:56 pm

Thank you, Gordon.

Sorry, I'm not that Unix-aware to work with ssh. I can use commands in
Windows, but not Unix. Could you please just tell me what is incorrect? Or
tell me what command should I use to login to mysql database - I can try.

My account login is pva,

My database login is test, so it should be pva_test.

My database password is test.

Thank you,

Peter

"Gordon Burditt" <gordon@hammy.burditt.org> wrote in message
news:cqer85$ujc@library1.airnews.net...
> In article <C_adnQM9S9ej6lfcRVn-2g@speakeasy.net>,
> Peter <pva@speakeasy.net> wrote:
ddt2_dmf.[color=darkred]
(Using[color=darkred]
>
> You have the user/password set up incorrectly.
> Can you log in with the command-line client ('mysql') using that
> user name, password, and database?
>
>
>
> Gordon L. Burditt

Gordon Burditt

2004-12-23, 8:56 pm

>Sorry, I'm not that Unix-aware to work with ssh. I can use commands in
>Windows, but not Unix. Could you please just tell me what is incorrect? Or

No. You'd need to post a dump of your 'mysql' database, containing all the
privilege tables, for me to be able to do that (and for the bad guys to
hack your database - so DON'T post it). And if you can't log in to MySQL,
you can't get such a dump anyway. How did you create the account?

>tell me what command should I use to login to mysql database - I can try.

The command-line client is called 'mysql' on Unix. (It may be the
same on Windows, but I have no idea where it's installed). You
type a query and get an answer. In your case, just successfully
connecting is an indication you got things right. (The way out is
'quit'). Whether or not you have enabled database access from your
Windows machine is not necessarily related to whether you can access
it from your server which apparently can run /bin/sh scripts. If
you're not running from the server, you need the -hhostname option.

>My account login is pva,
>
>My database login is test, so it should be pva_test.

No, if your database login is test, then your database login is
test, *NOT* pva_test. On the other hand, if your database login
is pva_test, then your database login is pva_test, *NOT* test. Do
not gratuitously stick underscores in all over the place. That may
be some kind of local naming convention, but it has nothing to do
with MySQL.

>
>My database password is test.
>
>Thank you,
>
>Peter
>
>"Gordon Burditt" <gordon@hammy.burditt.org> wrote in message
>news:cqer85$ujc@library1.airnews.net...
>ddt2_dmf.
>(Using
>
>

Peter Afonin

2004-12-23, 8:56 pm

Thank you, Gordon.

I'll try all this. In terms of the user name and password - I create a login
test and password test, but cPanel adds automatically my user name to it,
like this: pva_test, which makes sense. I don't think it does it for
password, this wouldn't make any sense. My provider gave me an example with
underscore, and this

me.

Anyway, I'll try your suggestions.

Thanks,

Peter

"Gordon Burditt" <gordonb.5o4tp@burditt.org> wrote in message
news:cqfbq0$vsi@library1.airnews.net...
Or[color=darkred]
>
> No. You'd need to post a dump of your 'mysql' database, containing all
the
> privilege tables, for me to be able to do that (and for the bad guys to
> hack your database - so DON'T post it). And if you can't log in to MySQL,
> you can't get such a dump anyway. How did you create the account?
>
>
> The command-line client is called 'mysql' on Unix. (It may be the
> same on Windows, but I have no idea where it's installed). You
> type a query and get an answer. In your case, just successfully
> connecting is an indication you got things right. (The way out is
> 'quit'). Whether or not you have enabled database access from your
> Windows machine is not necessarily related to whether you can access
> it from your server which apparently can run /bin/sh scripts. If
> you're not running from the server, you need the -hhostname option.
>
>
> No, if your database login is test, then your database login is
> test, *NOT* pva_test. On the other hand, if your database login
> is pva_test, then your database login is pva_test, *NOT* test. Do
> not gratuitously stick underscores in all over the place. That may
> be some kind of local naming convention, but it has nothing to do
> with MySQL.
>
>
>
>

CJ Llewellyn

2004-12-23, 8:56 pm

"RotterdamStudents" <NOSPAMnewsNOSPAM@MAPSONcistron.nederland> wrote in
message news:cqeeqb$pc0$1@news.cistron.nl...
>
"/usr/local/bin/mysqldump --opt --no-create-db --user=$dbuser --password=$db
> pswd
>
> Hi,
>
> i'm trying to use your script, but get the next message:
> Warning: filesize(): SAFE MODE Restriction in effect. The script whose uid
> is 10210 is not allowed to access /root owned by uid 0 in
> /usr/local/psa/home/vhosts/mydomain.com/httpdocs/backup.php on line 15
>
> What's wrong with it ??

php is running in safe mode and is trying to save the backup data outside of
the web server document root path. More specifically in the /root user's
folder which for a web initiated action is a big NO NO.

Further more passing php variables to a shell command is very dangerous, see
the NoSanity threads for why it is dangerous.

Peter Afonin

2004-12-24, 4:00 am

Finally made it work. Thanks, Gordon.

Peter

"Gordon Burditt" <gordonb.5o4tp@burditt.org> wrote in message
news:cqfbq0$vsi@library1.airnews.net...
Or[color=darkred]
>
> No. You'd need to post a dump of your 'mysql' database, containing all
the
> privilege tables, for me to be able to do that (and for the bad guys to
> hack your database - so DON'T post it). And if you can't log in to MySQL,
> you can't get such a dump anyway. How did you create the account?
>
>
> The command-line client is called 'mysql' on Unix. (It may be the
> same on Windows, but I have no idea where it's installed). You
> type a query and get an answer. In your case, just successfully
> connecting is an indication you got things right. (The way out is
> 'quit'). Whether or not you have enabled database access from your
> Windows machine is not necessarily related to whether you can access
> it from your server which apparently can run /bin/sh scripts. If
> you're not running from the server, you need the -hhostname option.
>
>
> No, if your database login is test, then your database login is
> test, *NOT* pva_test. On the other hand, if your database login
> is pva_test, then your database login is pva_test, *NOT* test. Do
> not gratuitously stick underscores in all over the place. That may
> be some kind of local naming convention, but it has nothing to do
> with MySQL.
>
>
>
>

Ike

2004-12-28, 3:56 pm

RotterdamStudents

2004-12-28, 3:56 pm

skadmin

2004-12-28, 3:56 pm

Peter Afonin

2004-12-28, 3:56 pm

CJ Llewellyn

2004-12-28, 3:56 pm

Peter Afonin

2004-12-28, 3:56 pm

skadmin

2004-12-28, 3:56 pm

Peter

2004-12-28, 8:56 pm

Peter

2004-12-28, 8:56 pm

Sponsored Links