Detailed Notes
We continue the series of tutorials as we build out a backup system that drops archive files into Amazon's S3 and related storage. This tutorial focuses on getting a database backup using a Python script.
You can learn more through our online classes at https://school.develpreneur.com and register for free. Registration will add you to our email list, and you will periodically receive coupons for courses and notifications of the latest releases.
Transcript Text
[Music] well hello and welcome back we are continuing with our little um backup anator orever we're going to call it our little backup script and we have uh currently we we worked our way up to if you look couple of the previous episodes uh we did a couple things where we were like we're storing something off and uh we've done storing and retrieving creating a bucket in Amazon's S3 we've done zipping up files this episode I want to focus on using python to back up a database now we're going to do the uh this is for my SQL and this is going to be the brw force kind of approach and basically what we're going to do is we are going to create a little function I'm just going to call it database dump and we're going to use uh from subprocess we're going to use P open and all we're going to do is we're going to actually use our normal uh dump type of command so normally if you don't know you can normally dump dump a database by doing MySQL I'm sorry my SQL dump and then you're going to do dasu and it's going to be username and then there's going to be- p means it's going to ask you for a password and then you tell it the database name so it' be DB name and if you normally what's going to do it's just going to kick out the SQL so what you probably want to do is send it to redirect it to somewhere like backup. SQL so if I do this it's going to say enter your password this is not anything real so it's going to say hey it doesn't exist and so this is something you may run into because when we're running our script we're not going to get that ability to ask for a password instead we need to give it one and so a way we do that is we create a thing called my.cnf and we're going to put that I'm sorry my.cnf and we're going to put it in our home folder and all that is is a text file and it's going to look like this is we're going to have a section and it's going to call it's called MySQL dump because we're going to say hey when we call MySQL dump for MySQL and this will work for Maria D Maria DB as well well then we're going to give it the username and the password that we want to use in order to do that now here um and so we've got a dash user I think we can actually get rid of this completely let me check that because I have this entry built for my database user in my uh my.cnf I think we can do it without that so if I do my SQL dump uh let's just run it uh because there's not a whole lot to whoops there's not a whole lot to this script and especially if I spell python right and don't add a typo in there um all we do is we're going to come in and we're going to give it a database name and we're going to give it an output file so if you come up here database name and output file and then here I'm just throwing those in so my database name and I'm going to go out to my output file and notice that it's uh so it'll be in the current folder I could put more in into this and uh I've just giving it a whole thing so I've got this or SQL extension I've got this data base so now if I run it let's see if I do it without a user cool and so if I do go look here uh let me actually remove what did I call it shorty. whoops shorty. SQL let me run that again so I don't even need to give it a user there we go and if I do wow if I catch shorty. SQL then here is my database and you see it's like a big old dump and so it starts with whole bunch of checks and stuff like that what's database type and then for each table it's going to drop it if exist and it's going to create the table and then it's going to enter all the data so and it's very straightforward so if I wanted to replace this I could go into my SQL and I could do use shorty and if I do Source uh what I call shorty. SQL then it's going to run through and now I've just replaced my database now this is the reason we're doing that we've got two things that we have values value in doing this now one of them is for this is for this specific uh script now one of the things you may be thinking is like well hey what if and this is a a business use that's got me thinking about this what what if I've got an app and I've got it out on a server somewhere and I want to grab I want to have the ability to allow somebody to grab a backup of the database and do something with it well in Jango we can actually make that pretty straight forward we've seen how we can do it in our scripts so we can just in a python script do something this simple it's really you know you can set this up in a couple of minutes and boom you have a database backup now if I want to do it in Jango what I would do is in the URLs I'm going to create a URL like for example dump and then I'm going to do I'm GNA give it an ID so I'm gonna have a URL that looks something like this actually it's going to be in well it's actually not be slash up but you know basically whatever my path is and I'm going to give it an ID because what I want to do is within the and let me just keep that there for now so then this is what it's going to look like in the The View code is I'm going to come in from the request I'm going to take that ID I want to make sure not anybody can do it so this is like the you know the simple very easy way to secure it and and that secures in quotes but it's basically it says hey if they don't give me the right ID then I'm not going to be able to I'm not going to do anything I'm just going to send them back to whatever the homepage is which is this I'm going to redirect otherwise I'm going to come in and basically the same thing that we've got here this little uh DB dump of change it just a little bit now in this case I'm going to have a hard code I'm going to have within my code my database name and my output file um I could pass those as parameters if I wanted to and then what I really need to do now since I don't need this is I'm going to do a MySQL dump and then it's the same thing here it's just the DB name the out file now in this case what I'm going to do is I'm going to turn right around I'm going to open that file I just create that SQL file I just created created and then I'm going to send it back to them so it's going to end up downloading the SQL file that we just told it to it so it's going to download the backup the whole database STP now obviously this could be a problem at times because it could be a huge file uh it's also you know obviously it's a a security risk uh the fact that you have in your website a way to do that but you can lock that down various different ways uh you can use a password you can have it so they have to be logged in that they have to be you know at an admin or something like that a lot of different ways you can do it so I'm giving you a shotgun be careful that you don't blow your you know your toes off or something like that now this is going to be very useful for us with our backup piece because we're going to have two things we're going to be looking at when we're doing the backup we want to you know we'll have like step one is back up and store the database and then we're going to have a step two which is going to be back up the files and store uh let's actually say zip and store them and so our next uh in the next session that's one of the things we're going to talk about is like all right now that we've we've got those pieces together we have in our backup uh we've got a file but what we're going to do is we're going to add something to scroll through a directory pull all those files put them into a zip file uh which is what we looked at before is basically zipping that file so there's one file one one big zip file that would be necessarily all of our backup folders and the files within them we're going to take that we're going to shove that out and we're also going to have the ability to do a a database if we want to or actually we could technically do multiple databases we can even do multiple databases zip those up and have a single file that we're going to store out in S3 so we're going to talk about that next time there's several ways we can go with this right now I just wanted to make sure that we're good with database dump dumping your database from python is super super simple and actually some of this like the database name once you've got yourm my.cnf file built you could actually pull this database from the settings in um in D Jango that setting the Jango settings file you know we use that database all over the place so you would have you could easily set this up so that it never actually has like I do here uh it never actually uses database name it instead would use the one uh whatever the settings database name is so and you could also do that so it could flip from uh like settings to set up so you could do it from production or database or or development or testing or whichever uh configuration you're running from keep this one sort of short there you go that is how you dump your mySQL database or Maria dat DB database out to a file so that you can you know copy that somewhere else back it up send it to a buddy whatever it is you want to do go out there and have yourself a great day a great week and we will talk to you next time well hello this is Rob from develop andur building better developers and we have a mastermind class that will be starting or actually several Mastermind classes starting in 2024 check us out at Tech technology Mastermind 2024 we've got all the information you need including some pricing early bird discounts up to 50% off but those are things that you're going to have to act soon there is an application process so go ahead and sign up today thanks a [Music] lot
Transcript Segments
[Music]
well hello and welcome back we are
continuing with our little um backup
anator orever we're going to call it our
little backup script and we have uh
currently we we worked our way up to if
you look couple of the previous episodes
uh we did a couple things where we were
like we're storing something off and uh
we've done storing and retrieving
creating a bucket in Amazon's S3 we've
done zipping up files this episode I
want to focus on using python to back up
a
database now we're going to do the uh
this is for my SQL and this is going to
be the brw force kind of approach and
basically what we're going to do is we
are going to create a little function
I'm just going to call it database dump
and we're going to use uh from
subprocess we're going to use P open and
all we're going to do is we're going to
actually use our normal uh dump type of
command so normally if you don't know
you can normally dump dump a database by
doing
MySQL I'm sorry my SQL dump and then
you're going to do dasu and it's going
to be username and then there's going to
be- p means it's going to ask you for a
password and then you tell it the
database name so it' be DB name and if
you normally what's going to do it's
just going to kick out the SQL so what
you probably want to do is send it to
redirect it to somewhere like backup.
SQL so if I do this it's going to say
enter your
password this is not anything real so
it's going to say hey it doesn't exist
and so this is something you may run
into because when we're running our
script we're not going to get that
ability to ask for a password instead we
need to give it one and so a way we do
that is we create a thing called
my.cnf and we're going to put that I'm
sorry
my.cnf and we're going to put it in our
home folder and all that is is a text
file and it's going to
look like this is we're going to have a
section and it's going to call it's
called MySQL dump because we're going to
say hey when we call MySQL dump for
MySQL and this will work for Maria D
Maria DB as well well then we're going
to give it the username and the password
that we want to use in order to do that
now here um and so we've got a dash user
I think we can actually get rid of this
completely let me check that because I
have this entry built for my database
user in my uh
my.cnf I think we can do it without that
so if I do my SQL
dump uh let's just run it uh
because there's not a whole lot to
whoops there's not a whole lot to this
script and especially if I spell python
right and don't add a typo in there um
all we do is we're going to come in and
we're going to give it a database name
and we're going to give it an output
file so if you come up here database
name and output file and then here I'm
just throwing those in so my database
name and I'm going to go out to my
output file and notice that it's uh so
it'll be in the current folder I could
put more in into this and uh I've just
giving it a whole thing so I've got this
or SQL extension I've got this data base
so now if I run it let's see if I do it
without a
user cool and so if I do go look here uh
let me actually
remove what did I call it shorty. whoops
shorty. SQL let me run that again so I
don't even need to give it a user there
we go and if I
do wow if I catch shorty. SQL then here
is my database and you see it's like a
big old dump and so it starts with whole
bunch of checks and stuff like that
what's database type and then for each
table it's going to drop it if exist and
it's going to create the table and then
it's going to enter all the data so and
it's very straightforward so if I wanted
to replace this I could go into my
SQL and I could do use
shorty and if I do Source uh what I call
shorty. SQL then it's going to run
through and now I've just replaced my
database now this is the reason we're
doing that we've got two things that we
have values value in doing this now one
of
them is for this is for this specific uh
script now one of the things you may be
thinking is like well hey what if and
this is a a business use that's got me
thinking about this what what if I've
got an app and I've got it out on a
server somewhere and I want to grab I
want to have the ability to allow
somebody to grab a backup of the
database and do something with it well
in Jango we can actually make that
pretty straight forward we've seen how
we can do it in our scripts so we can
just in a python script do something
this simple it's really you know you can
set this up in a couple of minutes and
boom you have a database backup now if I
want to do it in Jango what I would do
is in the URLs I'm going to create a URL
like for example dump and then I'm going
to do I'm GNA give it an ID so I'm gonna
have a URL that looks something like
this actually it's going to be in well
it's actually not be slash up but you
know basically whatever
my path is and I'm going to give it an
ID because what I want to do is within
the and let me just keep that there for
now so then this is what it's going to
look like in the The View code is I'm
going to come in from the request I'm
going to take that ID I want to make
sure not anybody can do it so this is
like the you know the simple very easy
way to secure it and and that secures in
quotes but it's basically it says hey if
they don't give me the right ID then I'm
not going to be able to I'm not going to
do anything I'm just going to send them
back to whatever the homepage is which
is this I'm going to redirect otherwise
I'm going to come in and basically the
same thing that we've got here this
little uh DB dump of change it just a
little bit now in this case I'm going to
have a hard code I'm going to have
within my code my database name and my
output file um I could pass those as
parameters if I wanted to and then what
I really need to do now since I don't
need this is I'm going to do a MySQL
dump and then it's the same thing here
it's just the DB name the out file now
in this case what I'm going to do is I'm
going to turn right
around I'm going to open that file I
just create that SQL file I just created
created and then I'm going to send it
back to them so it's going to end up
downloading the SQL
file that we just told it to it so it's
going to download the backup the whole
database STP now
obviously this could be a problem at
times because it could be a huge file uh
it's also you know obviously it's a a
security risk uh the fact that you have
in your website a way to do that but you
can lock that down various different
ways uh you can use a password you can
have it so they have to be logged in
that they have to be you know at an
admin or something like that a lot of
different ways you can do it so I'm
giving you a shotgun be careful that you
don't blow your you know your toes off
or something like that now this is going
to be very useful for us with our backup
piece because we're going to have two
things we're going to be looking at when
we're doing the backup we want to you
know we'll have like step
one is back up and store the
database and then we're going to have a
step
two which is going to be back up the
files and store uh let's actually
say zip and store them and so our next
uh in the next session that's one of the
things we're going to talk about is like
all right now that we've we've got those
pieces together we have in our backup uh
we've got a file but what we're going to
do is we're going to add something to
scroll through a directory pull all
those files put them into a zip file uh
which is what we looked at before is
basically zipping that file so there's
one file one one big zip file that would
be necessarily all of our backup folders
and the files within them we're going to
take that we're going to shove that out
and we're also going to have the ability
to do a a database if we want to or
actually we could technically do
multiple databases we can even do
multiple databases zip those up and have
a single file that we're going to store
out in S3 so we're going to talk about
that next time there's several ways we
can go with this right now I just wanted
to make sure that we're good with
database dump dumping your database from
python is super super simple and
actually some of this like the database
name once you've got
yourm
my.cnf file built you could actually
pull this database from the settings in
um in D Jango that setting the Jango
settings file you know we use that
database all over the place so you would
have you could easily set this up so
that it never actually has like I do
here uh it never actually uses database
name it instead would use the one uh
whatever the settings database name is
so and you could also do that so it
could flip from uh like settings to set
up so you could do it from production or
database or or development or testing or
whichever uh configuration you're
running
from keep this one sort of short there
you go that is how you dump your mySQL
database or Maria dat DB database out to
a file so that you can you know copy
that somewhere else back it up send it
to a buddy whatever it is you want to do
go out there and have yourself a great
day a great week and we will talk to you
next time well hello this is Rob from
develop andur building better developers
and we have a mastermind class that will
be starting or actually several
Mastermind classes starting in 2024
check us out at Tech technology
Mastermind
2024 we've got all the information you
need including some pricing early bird
discounts up to 50% off but those are
things that you're going to have to act
soon there is an application process so
go ahead and sign up today thanks a
[Music]
lot