MySQL Advanced - Backup & Restoring Databases

http://www.killersites.com In this important video tutorial from our Advanced MySQL course, we show you how to do backups and restore your data with the help of the MySQL Dump function!

We've only made part of our content available for free. For the rest, check out our Killer Video Store or our Video Tutorial Library at Killersites.com!

http://www.killervideostore.com/
http://www.killersites.com/university/
http://www.killersites.com/community/
http://www.killerphp.com/

---

Closed Caption:

hi there this is John 11 sold for killer
PHP calm and this is part 8 of a
nine-part series or looks to be a 9 part
series on advanced mysql usage and today
what I'm going to be showing you is how
you can do backups and restore your data
with the help of the mysql dump function
that comes with pretty much every
distribution of mysql so let's just get
started here if I start by showing you
the tables from our last video you can
very easily see that we've got author
post and we've got post count post with
authors and published posts which are
all views that we created in the last
two videos so if I want to do any kind
of backups of my data the most effective
way of doing this is actually from the
command line now granted you can also do
this and i'm just going to show you
quickly how if I just create a new tab
here so you can see if you do have a map
configuration then it's actually very
easy for you to just use the PHP my
admin toolset that usually comes with
some kind of lamp stack whatever it may
be and from PHP my admin if you remember
it from the introductory tutorial that
defended you can see basically your
whole database schema and you can see
how many records you have and everything
else but you can also do an export of
your data so if I were to check all and
I said export and at the bottom here you
can actually save it as a file or you
can just hit go and when you hit go
you'll see that mysql will actually
generate all the necessary statements
for creating the AH the the constraints
that we looked at and even the first or
the second video to creating the views
that
he described in the last video and so on
and so forth and even all the tables and
all the the rose and everything else so
this is a great way of doing your
backups if you don't have access to this
or if you're for example using ssh to
log into a server and you don't have PHP
my admin at your disposal then fear not
there's actually a very simple way of
doing this too so if i go back to my
terminal and I just quit mysql you'll
notice that in my my particular mysql
binaries are stored in the the bin
folder here so i have MySQL client test
mysql config mysql find rose install DB
and so on and so forth mysqladmin but
there's also my SQL dump and mysql dump
is a really simple way of just dumping
out all the information in a particular
database and it's great if you want to
do that turn it into an SQL file similar
to the scripts that we've been creating
overtime accept that this is going to be
something that is automatically
generated for us so i'm just going to
create a minus I'm just gonna run mysql
dumped from here and see what happens
so you're going to notice that it's
going to give me a selection of options
now in this case my first option is
going to be that when I access my
database using route and my password for
my database is actually going to be root
not very high security and if I just
specify the database name then what's
going to happen is it's just going to
dump everything out straight to my
command line which isn't very helpful i
mean yeah you can copy and paste it but
that's not a very elegant solution with
a bit of shell scripting we can actually
easily move this into a file so if i
wanted to push all of the data that the
mysql dump function is going to return
then it would just be a matter of using
the greater than symbol which will pipe
that output into a file and then I just
need to specify a file so in this case
it's going to be
k PHP SQL and i'm going to call it back
up . sq L hit enter and nothing should
happen or seem to happen however if I go
to this particular folder using CD and
then using the tilde which takes me home
to my desktop 2k PHP SQL which is the
folder that i have this particular
backup file sitting in and i were to or
even I can just go into my finder here
so it's nice and easy for you to see if
i go to my desktop i'm going to have in
my kph psql folder a backup . SQL that
was just created so if I open this in my
editor you can see that it has all the
necessary information for defining the
the website that I or the the database
schema for my web site so it's got you
know the inserts it's got you know a
whole bunch of extra junk which might
not necessarily be super user friendly
but at least we know that it's all it's
all there
so with that in place we can also see
that there's the views and everything
else so if i just select all of this and
i were to go back here into mysql and
for the sake of argument i was going to
say okay so databases I've got a whole
bunch of databases here if I wanted to
create a new database called k PHP back
up with the collation of utf-8
underscore general underscore CI and a
encoding utf-8 I believe that's how you
would do it
ah here we go it's actually not collate
its character set
yeah so Kool Aid is fine but it's
actually character set
so if i create this k PHP backup and I
decide I want to use kph be back up then
if I go back here and I take my backup
that SQL file select all and then paste
it in then it's going to say dump
completed everything looks good if I say
show tables i'm going to get all of the
tables from mike a PHP table database
including all the views so if i say
define post with underscore authors oops
i mean described post underscore with
underscore authors i'm going to get a
description of that if i say select star
from published posts i should get a nice
description of that and everything is in
place so with that one simple mysqldump
command if you wanted to and you had to
log into mysql this would be a very
simple way of doing it another way of
doing it is actually without even
accessing mysql at all so to illustrate
this example what I'm going to do is I'm
just going to drop the database of
$OPERAND kph be underscore backup show
databases and then i'm going to recreate
the create database
k PHP underscore back up and then
collate to utf-8 underscore general
underscore CI it's very important that
you make sure that your defaults are
properly setup or else you'll end up
having like a Swedish character set for
some of your columns and then utf-8 14
other columns which if you have any kind
of funny characters or you know if
you're trying to store multiple
languages you're going to set yourself
up for a lot of trouble and we're going
to the character set set to utf-8 so
I've created this k PHP backup database
except that now if i say use k PHP
underscore backup and I show tables
it's going to be absolutely empty now if
i quit my SQL
instead of just running my legs regular
mysql query and specifying a PHP
underscore backup as my database i can
then pipe the backup SQL script back
into mysql through the client and that
means instead of using the greater than
i would put to a file i'm just going to
use the less then and then I'm going to
find that backup . SQL file so in this
case it's inkay PHP SQL backup . SQL if
i hit enter
it'll take a moment and then it'll just
pump all that information back into the
SQL server if i run the client and I say
show tables then they're going to be
back where they belong
so with a couple of very simple commands
at your disposal
it's actually not difficult to do your
backups and restores and to be able to
easily manage your database if you're
moving from a development environment
with staging environment if you need to
create a test script to show that there
are certain integrity that that is
maintained within your application you
know these little simple queries are
going to save you a ton of time i'm john
lennon sold and thanks for listening