OT: MySQL backup script

[Darrell Lee]

I am trying to create a php script to backup a MySQL database on my
hosting company. I am trying to use mysqldump to do this. Any help you
guys can give would be greatly appreciated.

--
Darrell Lee
Advanced Data Systems, Inc.
864-230-9626 | dl...@adsi-sc.com

[George Law]

My experience with some hosting companies is that they don't typically allow you to do any shell level execs for security purposes so making a exec call to mysqldump doesn't usually work

~George Law

--
You received this message because you are subscribed to the Google Groups "Upstate Carolina Linux Users Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to uclug+un...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/uclug/024210aaa5ab14d0f6ce53befa021b21%40adsi-sc.com.

[Darrell Lee]

George, here is the code I tried to run, sensitive info removed. Do you
see anything wrong with it?

<?php
$dbhost = 'localhost';
$dbuser = 'user';
$dbpass = '************';
$dbname = 'demo';

$backup_file =
'/home/<private>/public_html/<private>.com/demo/admin/backups/'.$dbname.'_'.date("Y-m-d-H-i-s").'.sql';

$command = "mysqldump --opt -h $dbhost -u $dbuser -p $dbpass $dbname
> '$backup_file";

system($command);
?>

---

Darrell Lee
Advanced Data Systems, Inc.
864-230-9626 | dl...@adsi-sc.com

> https://groups.google.com/d/msgid/uclug/CAKzfAU4ngwV%2BCyZ4CH1Cihg26%2BGGsaq-cfk6S-g4WAyOHfoz3w%40mail.gmail.com
> [1].
>
>
> Links:
> ------
> [1]
> https://groups.google.com/d/msgid/uclug/CAKzfAU4ngwV%2BCyZ4CH1Cihg26%2BGGsaq-cfk6S-g4WAyOHfoz3w%40mail.gmail.com?utm_medium=email&utm_source=footer

[George Law]

Darrell,

the code works with one tweak - no space between the -p and the value for the password

 $command = "mysqldump --opt -h $dbhost -u $dbuser -p$dbpass $dbname

with the space in there it was telling me 'no such database XXX' where XXX was my db password

you might be able to do it as --password=XXX as well for clarity

from the --help :

  -p, --password[=name]
                      Password to use when connecting to server. If password is
                      not given it's solicited on the tty.

I just ran this in a LAMP docker container and dumped my database.

however, in my experience, most web hosts that do virtual hosting, often disable many php functions, such

as exec and system - e.g. explained here :https://www.cyberciti.biz/faq/linux-unix-apache-lighttpd-phpini-disable-functions/

So your mileage may vary depending on your web host.

You may have to do something like stick the mysqldump command within a shell script and run via cron

~George Law

To view this discussion on the web visit https://groups.google.com/d/msgid/uclug/ab45174a55bac02839b0281a5798c9ef%40adsi-sc.com.

[George Law]

Sorry, this is the full $command with the redirect   - you also had an extra single quote in there

    $command = "mysqldump --opt -h $dbhost -u $dbuser -p$dbpass $dbname > $backup_file";

[Darrell Lee]

Thank you George !!!

Darrell Lee

Advanced Data Systems, Inc.

dl...@adsi-sc.com

864-230-9626

To view this discussion on the web visit https://groups.google.com/d/msgid/uclug/CAKzfAU4uYay61S7KTLEYhZWcR1epUwao0E0VX1J0jFpeLefNnw%40mail.gmail.com.

[Darrell Lee]

George I made the changes you pointed out and the script works, thank
you again for your help!

Also, I would like to learn more about this:

> You may have to do something like stick the mysqldump command within a
> shell script and run via cron

Where would you suggest I look to learn how to do this? I can get shell
access at my isp.

---
Darrell Lee
Advanced Data Systems, Inc.
864-230-9626 | dl...@adsi-sc.com

> https://groups.google.com/d/msgid/uclug/CAKzfAU6yC_MT0hGKjEkwYMJyRmoVpVi%3DGNajZzx%3DiQdxux_LWw%40mail.gmail.com

> [1].
>
>
> Links:
> ------
> [1]

> https://groups.google.com/d/msgid/uclug/CAKzfAU6yC_MT0hGKjEkwYMJyRmoVpVi%3DGNajZzx%3DiQdxux_LWw%40mail.gmail.com?utm_medium=email&utm_source=footer

[George Law]

Darrell

Writing from my phone so pardon the formatting ... Great to hear that it worked although it surprises me ... That link I sent explaining how to block certain php functions (like system() ) is usually a part of 'hardening' a web server ... Before I set up my own vps server with digital ocean (now moved to linode), I used several of the bigger named hosting companies like blue web, liquid web, etc ... system() was generally always blocked ... I think when I did the presentation a couple years ago on cwp (CentOS web panel) I might have touched on some of the settings it used , with the function blocks being one of them.

I would suggest a change to the backup_file ... Add .gz on the end and then insert a pipe to gzip to compress the backup 

$backup_file='/home/<private>/public_html/<private>.com/demo/admin/backups/'.$dbname.'_'.date("Y-m-d-H-i-s").'.sql.gz';

$command = "mysqldump --opt -h $dbhost -u $dbuser -p$dbpass $dbname |gzip >> $backup_file";

As far as where to learn this (bash scripting in general), I had a friend ask me this a while back ... A Google search for "bash 101" found what appeared to be a good set of starting points. Codecademy.com looks like they've got a bash tutorial but I haven't looked at it ... But I did go through their python tutorial a couple years ago and it was pretty good.

Minus the php starting and ending tags it looks like your script should translate to bash with minimal edits ... Variable assignments work the same way except you do not need the $ in the assignment, only when you use the variable. Also no spaces around the = 

system($command) becomes exec $command 

And I had to tweak the date ... You get some extra periods in the file name .. but basically it works:

I literally did a 'cp dbbackup.php dbbackup.sh' and then edited 

#!/bin/bash

dbhost='localhost';

dbuser='spotweb';

dbpass='xxx';

dbname='spotweb';

backup_file='/var/www/html/'.$dbname.'_'.$(date "+%m-%d-%Y-%H%M%S").'.sql';

command="/usr/bin/mysqldump --opt -h $dbhost -u $dbuser -p $dbpass $dbname > $backup_file";

echo $command;

exec $command

chmod +x dbbackup.sh

./dbbackup.sh

Boom 😂

I added the full path to mysqldump .. if you need to run this from cron using full paths is always recommended because they $PATH inside cron is limited. Likewise the gzip would also need /usr/bin

~George Law

To view this discussion on the web visit https://groups.google.com/d/msgid/uclug/7de11b7b35b669126678f7c003bc2de3%40adsi-sc.com.

[Darrell Lee]

That is awesome info George, thank you so much for the help!

Darrell Lee

Advanced Data Systems, Inc.

dl...@adsi-sc.com

864-230-9626

To view this discussion on the web visit https://groups.google.com/d/msgid/uclug/CAKzfAU4q7tkSD3Zqq9XT2Uw7ybLBarEBxSmjffqwj%3D-_pDBh-Q%40mail.gmail.com.