2

My server details:

OS: Windows Server 2003
IIS6
Plesk 8.xx installed (currently using Plesk to set the cronjob)

I need your advice. I have 2 methods:

Method 1:

Using php + mysqldump, create databases backup files into gzip, and then send email with attachment (each databases has around about 25mb)

Method 2:

Using batch + mysqldump, create databases backup files into gzip, and then send email with attachment (same, each databases has around about 25mb)

My questions:

  1. Whats the difference of using php file and batch file for cronjob?

  2. Which method is better in term of backup speed and send email, and (maybe)safety (e.g., lesser file corrupt occurance)?

  3. If i set the cronjob hourly, will it effect my web performances? I mean, lets say my website has 100++ users online now, and each user making transaction to MySQL, when I perform backup at my web peak hour, will it decrease the performances, like the loading speed, prone to errors etc??

(sorry for my bad english)

P.S: If you need my php and batch file code, please ask me to post it here. I didnt post it now is because, its very simple and standard code.

1
  • You can set up a serverfault account and link it to your stackoverflow. You'll get +100 rep points as well just for linking them. It's the stackoverflow equivalent for "system administrators and IT professionals, people who manage or maintain computers..."
    – jason
    Jan 15, 2010 at 3:42

6 Answers 6

1
  1. If you are just doing dumping, gzipping and then sending email, there is no difference. BUT if you are doing more complicated stuffs, using PHP will have advantage, example maybe you want to set timestamps, or parse the output before sending. Then you can make use of PHP's vast libraries and methods for these kind of stuff that batch can't provide (at least easily)

  2. This one you will have to test out yourself.

  3. You might want to set up incremental backup or save just the transactions.(not the whole database)

0
  1. For doing some database backup you can choose whatever language you want PHP,bash,python, it won't change much anyway.

  2. No method is better, it only depends on your script and what tools you are using.

  3. Yes mysqldump has some impact performance, you might want to setup a slave server using replication and actually make your backup there if you want to save your database so often.

2
  • 1. Got it. Probably, I will change to php, as i am more familiar with it; 2. Understood. Mine is very simple backup; 3. This is new to me, I might need to do more research on how to do this; Thanks for your answer
    – mysqllearner
    Jan 15, 2010 at 3:20
  • @mysqllearner: you are welcome!
    – RageZ
    Jan 15, 2010 at 4:13
0

Code it in whatever language you are most skilled. For a simple task like this, speed differences in languages wont be noticeable.

As your databases are quite small it probably wont impact performance too much. But mysqldump tends to lock tables while they are being dumped preventing anything else from writing to them.

0

While you can use whatever language you like for this kind of work some are distinctly better than others. Batch files have the limitation that they require external programs for pretty much everything for this kind of work. PHP is generally not generally most people's first choice as a command line program, although if that's what you're most comfortable with then go for it. Just bear in mind that PHP and Windows are not always the best of friends and what works well on other platforms may not work as well under Windows, often because external programs are different or absent.

0

Using php/batch + mysqldump, create databases backup files into gzip, and then send email with attachment (each databases has around about 25mb)

Whether you're using PHP or a batch file, it's only really being used for milliseconds. All the heavy processing is happening in MySQL and Gzip, and that's all happening outside your scripts.

Essentially, it's like caring about the color of the car you ride to the Space Shuttle in.

0

Which method is better in term of backup speed and send email, and (maybe) safety (e.g., lesser file corrupt occurance)?

The execution time for such a small script in bash or PHP will be negligible. You are concerned with the MySQL execution speed, which is the same regardless of which language calls the MySQL command. After bash/PHP makes the command to MySQL, all of the heavy lifting will be done in the DB.

Check your tables to see if you are using MyISAM or InnoDb. Mysqldump will lock MyISAM tables, but InnoDB tables can be backed up while the application continues to access the table. This is because of row level locking.

You must log in to answer this question.