RE: [PHP] backing up a database
-Original Message- From: James Tu [mailto:[EMAIL PROTECTED] Sent: Tuesday, November 28, 2006 5:03 PM To: PHP General List Subject: Re: [PHP] backing up a database Thanks Brad: I'm just surprised that when people mention mysqldump, most of the time they don't talk about locking the tables at all. I'm curious why this is the case. -James Unless you tell mysqldump not to lock tables, or change the defaults, it automatically locks: For example, use of --opt is the same as specifying --add-drop-table --add-locks --create-options --disable-keys --extended-insert --lock-tables --quick --set-charset. Note that as of MySQL 4.1, all of the options that --opt stands for also are on by default because --opt is on by default. http://dev.mysql.com/doc/refman/4.1/en/mysqldump.html -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] backing up a database
Does one need to lock(?) MySQL before running mysqldump? Or will the mysqldump command wait for any pending operations to finish, lock the tables for dumping and once finished release the lock? -James On Nov 27, 2006, at 2:21 AM, David Robley wrote: Sumeet wrote: Brad Fuller wrote: $command = mysqldump -u $dbuser -p$dbpass $dbname | gzip $backupFile; system($command); what if system() has been disabled on the server? SELECT INTO OUTFILE 'file_name' export_options seems a useful alternative. Cheers -- David Robley I haven't caught a fish all day! Tom said, without debate. Today is Sweetmorn, the 39th day of The Aftermath in the YOLD 3172. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] backing up a database
-Original Message- From: James Tu [mailto:[EMAIL PROTECTED] Sent: Tuesday, November 28, 2006 1:21 PM To: PHP General List Subject: Re: [PHP] backing up a database Does one need to lock(?) MySQL before running mysqldump? Or will the mysqldump command wait for any pending operations to finish, lock the tables for dumping and once finished release the lock? -James It is my understanding that mysql will not do this unless specified. http://dev.mysql.com/doc/refman/4.1/en/mysqldump.html --lock-all-tables, -x Lock all tables across all databases. This is achieved by acquiring a global read lock for the duration of the whole dump. This option automatically turns off --single-transaction and --lock-tables. Added in MySQL 4.1.8. --lock-tables, -l Lock all tables before dumping them. The tables are locked with READ LOCAL to allow concurrent inserts in the case of MyISAM tables. For transactional tables such as InnoDB and BDB, --single-transaction is a much better option, because it does not need to lock the tables at all. Please note that when dumping multiple databases, --lock-tables locks tables for each database separately. Therefore, this option does not guarantee that the tables in the dump file are logically consistent between databases. Tables in different databases may be dumped in completely different states. -B -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] backing up a database
Thanks Brad: I'm just surprised that when people mention mysqldump, most of the time they don't talk about locking the tables at all. I'm curious why this is the case. -James Does one need to lock(?) MySQL before running mysqldump? Or will the mysqldump command wait for any pending operations to finish, lock the tables for dumping and once finished release the lock? -James It is my understanding that mysql will not do this unless specified. http://dev.mysql.com/doc/refman/4.1/en/mysqldump.html --lock-all-tables, -x Lock all tables across all databases. This is achieved by acquiring a global read lock for the duration of the whole dump. This option automatically turns off --single-transaction and --lock-tables. Added in MySQL 4.1.8. --lock-tables, -l Lock all tables before dumping them. The tables are locked with READ LOCAL to allow concurrent inserts in the case of MyISAM tables. For transactional tables such as InnoDB and BDB, --single-transaction is a much better option, because it does not need to lock the tables at all. Please note that when dumping multiple databases, --lock-tables locks tables for each database separately. Therefore, this option does not guarantee that the tables in the dump file are logically consistent between databases. Tables in different databases may be dumped in completely different states. -B -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] backing up a database
James Tu wrote: Thanks Brad: I'm just surprised that when people mention mysqldump, most of the time they don't talk about locking the tables at all. I'm curious why this is the case. Because you don't need to. More of a mysql-mailing list question to be honest. -- Postgresql php tutorials http://www.designmagick.com/ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] backing up a database
I think it becomes an issue only if the database structure is complex with multiple related tables, and have lots of update activity. I think most people design applications that don't require anything so complex, and most queries are of the select variety. Warren Vail -Original Message- From: Chris [mailto:[EMAIL PROTECTED] Sent: Tuesday, November 28, 2006 2:50 PM To: James Tu Cc: PHP General List Subject: Re: [PHP] backing up a database James Tu wrote: Thanks Brad: I'm just surprised that when people mention mysqldump, most of the time they don't talk about locking the tables at all. I'm curious why this is the case. Because you don't need to. More of a mysql-mailing list question to be honest. -- Postgresql php tutorials http://www.designmagick.com/ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] backing up a database
Sumeet wrote: Brad Fuller wrote: $command = mysqldump -u $dbuser -p$dbpass $dbname | gzip $backupFile; system($command); what if system() has been disabled on the server? SELECT INTO OUTFILE 'file_name' export_options seems a useful alternative. Cheers -- David Robley I haven't caught a fish all day! Tom said, without debate. Today is Sweetmorn, the 39th day of The Aftermath in the YOLD 3172. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] backing up a database
Brad Fuller wrote: $command = mysqldump -u $dbuser -p$dbpass $dbname | gzip $backupFile; system($command); what if system() has been disabled on the server? -- Thanking You Sumeet Shroff http://www.prateeksha.com Web Designers and PHP / Mysql Ecommerce Development, Mumbai India -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] backing up a database
Ross wrote: I have a database and it needs to get backed up on a daily basis. Is there a class that allows me to create a backup and then save it as a .sql or excel or both to a folder of her choice? cron and mysqldump or pg_dump -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] backing up a database
Ross wrote: I have a database and it needs to get backed up on a daily basis. Is there a class that allows me to create a backup and then save it as a .sql or excel or both to a folder of her choice? I'm only helping cos there's a chick involved. ;-) the easiest solution would be to use a combination of mysqldump and cron. no-doubt there is php code out there capable of doing what you want ... if nothing else you could probably rip out the relevant bits from phpMyAdmin. I have a strong suspicion google can provide lots of potential winners for 'php mysql backup' like this one: http://www.0php.com/MySQL-Backup.php (found in 0.5 seconds) R. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] backing up a database
If you're using MySQL, set up a cron job/scheduled task to call mysqldump and save the output somewhere. Other DBMSs will likely have similar tools. Edward -Original Message- From: Ross [mailto:[EMAIL PROTECTED] Sent: 22 November 2006 09:54 To: php-general@lists.php.net Subject: [PHP] backing up a database I have a database and it needs to get backed up on a daily basis. Is there a class that allows me to create a backup and then save it as a .sql or excel or both to a folder of her choice? R. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] backing up a database
Here is a script that I found that has been working well for me. I set up a
cron job to run this file every night just after midnight. It e-mails a
gzipped sql dump file as an attachment to the specified e-mail address.
Right now the database this is backing up is not very big. I'm not sure how
scalable it is (probably not very).
Suggestions/improvements are welcome.
?php
set_time_limit(0);
ini_set(memory_limit, 500M);
$dbhost = 'localhost';
$dbuser = 'username';
$dbpass = 'password';
$dbname = 'database';
$backupDir = '/backups/';
$backupFileName = $dbname . date(Y-m-d-H-i-s) . '.sql.gz';
$backupFile = $backupDir . $backupFileName;
$command = mysqldump -u $dbuser -p$dbpass $dbname | gzip
$backupFile;
system($command);
$email_from = [EMAIL PROTECTED]; // Who the email is from
$email_subject = MySQL Backup .date(m/d/Y, time()); // The
Subject of the email
$email_txt = Your file is attached.; // Message that the email has
in it
$email_to = [EMAIL PROTECTED]; // Who the email is to
$headers = From: .$email_from;
$file = fopen($backupFile,'rb');
$data = fread($file,filesize($backupFile));
fclose($file);
$semi_rand = md5(time());
$mime_boundary = ==Multipart_Boundary_x{$semi_rand}x;
$headers .= \nMIME-Version: 1.0\n .
Content-Type: multipart/mixed;\n .
boundary=\{$mime_boundary}\;
$email_message .= This is a multi-part message in MIME format.\n\n
.
--{$mime_boundary}\n .
Content-Type:text/html;
charset=\iso-8859-1\\n .
Content-Transfer-Encoding: 7bit\n\n .
$email_txt . \n\n;
$data = chunk_split(base64_encode($data));
$email_message .= --{$mime_boundary}\n .
Content-Type:
application/octet-stream;\n .
name=\{$backupFileName}\\n .
Content-Transfer-Encoding:
base64\n\n .
$data . \n\n .
--{$mime_boundary}--\n;
$ok = @mail($email_to, $email_subject, $email_message, $headers);
if($ok) {
echo date(Y-m-d H:i:s) . Operation completed.\n;
} else {
die(date(Y-m-d H:i:s) . Operation failed - The e-mail could not
be sent.\n);
}
?
-Original Message-
From: Edward Kay [mailto:[EMAIL PROTECTED]
Sent: Wednesday, November 22, 2006 5:25 AM
To: PHP General List
Subject: RE: [PHP] backing up a database
If you're using MySQL, set up a cron job/scheduled task to call mysqldump
and save the output somewhere. Other DBMSs will likely have similar tools.
Edward
-Original Message-
From: Ross [mailto:[EMAIL PROTECTED]
Sent: 22 November 2006 09:54
To: php-general@lists.php.net
Subject: [PHP] backing up a database
I have a database and it needs to get backed up on a daily basis.
Is there a
class that allows me to create a backup and then save it as a
.sql or excel
or both to a folder of her choice?
R.
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] backing up a database
This is similar to mine, except I use a shell script.
Also, instead of emailing the file, my script FTPs it to a remote server. I
expect this approach would scale better as the file size increases.
Another point to note: my email is hosted on the same server as the DB.
Emailing it to myself wouldn't offer any more protection than just saving it
locally. (My remote FTP location is in a different continent!)
Edward
-Original Message-
From: Brad Fuller [mailto:[EMAIL PROTECTED]
Sent: 22 November 2006 14:28
To: 'PHP General List'
Subject: RE: [PHP] backing up a database
Here is a script that I found that has been working well for me.
I set up a
cron job to run this file every night just after midnight. It e-mails a
gzipped sql dump file as an attachment to the specified e-mail address.
Right now the database this is backing up is not very big. I'm
not sure how
scalable it is (probably not very).
Suggestions/improvements are welcome.
?php
set_time_limit(0);
ini_set(memory_limit, 500M);
$dbhost = 'localhost';
$dbuser = 'username';
$dbpass = 'password';
$dbname = 'database';
$backupDir = '/backups/';
$backupFileName = $dbname . date(Y-m-d-H-i-s) . '.sql.gz';
$backupFile = $backupDir . $backupFileName;
$command = mysqldump -u $dbuser -p$dbpass $dbname | gzip
$backupFile;
system($command);
$email_from = [EMAIL PROTECTED]; // Who the email is from
$email_subject = MySQL Backup .date(m/d/Y, time()); // The
Subject of the email
$email_txt = Your file is attached.; // Message that the email has
in it
$email_to = [EMAIL PROTECTED]; // Who the email is to
$headers = From: .$email_from;
$file = fopen($backupFile,'rb');
$data = fread($file,filesize($backupFile));
fclose($file);
$semi_rand = md5(time());
$mime_boundary = ==Multipart_Boundary_x{$semi_rand}x;
$headers .= \nMIME-Version: 1.0\n .
Content-Type: multipart/mixed;\n .
boundary=\{$mime_boundary}\;
$email_message .= This is a multi-part message in MIME format.\n\n
.
--{$mime_boundary}\n .
Content-Type:text/html;
charset=\iso-8859-1\\n .
Content-Transfer-Encoding: 7bit\n\n .
$email_txt . \n\n;
$data = chunk_split(base64_encode($data));
$email_message .= --{$mime_boundary}\n .
Content-Type:
application/octet-stream;\n .
name=\{$backupFileName}\\n .
Content-Transfer-Encoding:
base64\n\n .
$data . \n\n .
--{$mime_boundary}--\n;
$ok = @mail($email_to, $email_subject, $email_message, $headers);
if($ok) {
echo date(Y-m-d H:i:s) . Operation completed.\n;
} else {
die(date(Y-m-d H:i:s) . Operation failed - The e-mail could not
be sent.\n);
}
?
-Original Message-
From: Edward Kay [mailto:[EMAIL PROTECTED]
Sent: Wednesday, November 22, 2006 5:25 AM
To: PHP General List
Subject: RE: [PHP] backing up a database
If you're using MySQL, set up a cron job/scheduled task to call
mysqldump
and save the output somewhere. Other DBMSs will likely have
similar tools.
Edward
-Original Message-
From: Ross [mailto:[EMAIL PROTECTED]
Sent: 22 November 2006 09:54
To: php-general@lists.php.net
Subject: [PHP] backing up a database
I have a database and it needs to get backed up on a daily basis.
Is there a
class that allows me to create a backup and then save it as a
.sql or excel
or both to a folder of her choice?
R.
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] backing up a database
On Wednesday 22 November 2006 15:49, Edward Kay wrote:
This is similar to mine, except I use a shell script.
Also, instead of emailing the file, my script FTPs it to a remote server. I
expect this approach would scale better as the file size increases.
Another point to note: my email is hosted on the same server as the DB.
Emailing it to myself wouldn't offer any more protection than just saving
it locally. (My remote FTP location is in a different continent!)
Edward
Shell script with ftp works pretty decent for me 2.
-Original Message-
From: Brad Fuller [mailto:[EMAIL PROTECTED]
Sent: 22 November 2006 14:28
To: 'PHP General List'
Subject: RE: [PHP] backing up a database
Here is a script that I found that has been working well for me.
I set up a
cron job to run this file every night just after midnight. It e-mails a
gzipped sql dump file as an attachment to the specified e-mail address.
Right now the database this is backing up is not very big. I'm
not sure how
scalable it is (probably not very).
Suggestions/improvements are welcome.
?php
set_time_limit(0);
ini_set(memory_limit, 500M);
$dbhost = 'localhost';
$dbuser = 'username';
$dbpass = 'password';
$dbname = 'database';
$backupDir = '/backups/';
$backupFileName = $dbname . date(Y-m-d-H-i-s) . '.sql.gz';
$backupFile = $backupDir . $backupFileName;
$command = mysqldump -u $dbuser -p$dbpass $dbname | gzip
$backupFile;
system($command);
$email_from = [EMAIL PROTECTED]; // Who the email is from
$email_subject = MySQL Backup .date(m/d/Y, time()); // The
Subject of the email
$email_txt = Your file is attached.; // Message that the email has
in it
$email_to = [EMAIL PROTECTED]; // Who the email is to
$headers = From: .$email_from;
$file = fopen($backupFile,'rb');
$data = fread($file,filesize($backupFile));
fclose($file);
$semi_rand = md5(time());
$mime_boundary = ==Multipart_Boundary_x{$semi_rand}x;
$headers .= \nMIME-Version: 1.0\n .
Content-Type: multipart/mixed;\n .
boundary=\{$mime_boundary}\;
$email_message .= This is a multi-part message in MIME format.\n\n
.
--{$mime_boundary}\n .
Content-Type:text/html;
charset=\iso-8859-1\\n .
Content-Transfer-Encoding: 7bit\n\n .
$email_txt . \n\n;
$data = chunk_split(base64_encode($data));
$email_message .= --{$mime_boundary}\n .
Content-Type:
application/octet-stream;\n .
name=\{$backupFileName}\\n .
Content-Transfer-Encoding:
base64\n\n .
$data . \n\n .
--{$mime_boundary}--\n;
$ok = @mail($email_to, $email_subject, $email_message, $headers);
if($ok) {
echo date(Y-m-d H:i:s) . Operation completed.\n;
} else {
die(date(Y-m-d H:i:s) . Operation failed - The e-mail could not
be sent.\n);
}
?
-Original Message-
From: Edward Kay [mailto:[EMAIL PROTECTED]
Sent: Wednesday, November 22, 2006 5:25 AM
To: PHP General List
Subject: RE: [PHP] backing up a database
If you're using MySQL, set up a cron job/scheduled task to call
mysqldump
and save the output somewhere. Other DBMSs will likely have
similar tools.
Edward
-Original Message-
From: Ross [mailto:[EMAIL PROTECTED]
Sent: 22 November 2006 09:54
To: php-general@lists.php.net
Subject: [PHP] backing up a database
I have a database and it needs to get backed up on a daily basis.
Is there a
class that allows me to create a backup and then save it as a
.sql or excel
or both to a folder of her choice?
R.
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
--
---
Børge
Kennel Arivene
http://www.arivene.net
---
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] backing up a database
On Wed, November 22, 2006 3:53 am, Ross wrote: I have a database and it needs to get backed up on a daily basis. Is there a class that allows me to create a backup and then save it as a .sql or excel or both to a folder of her choice? I personally would not involve PHP in this process, in general, as the existing database backup software for almost any database will be more efficient and have less overhead than piping any of it through PHP... So just create a cron job in a shell to do: mysql_dump backup_path_here Of course, if one has no shell access, you're kinda stuck using PHP to run the http://php.net/exec to do that, and then some kind of hinky way to pretend to run cron... Finding a new webhost is my usual solution to that :-) -- Some people have a gift link here. Know what I want? I want you to buy a CD from some starving artist. http://cdbaby.com/browse/from/lynch Yeah, I get a buck. So? -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
