MySQL - Remote Access to MySQL Server on Linux CentOS 6.3 Part 1

In this tutorial I talk you through installing and configuring a MySQL server to allow remote access on Linux using CentOS 6.3.

So first, download and install the mysql server: yum install mysql mysql-server

Next make it so as the mysql server with be running on server startup, issue the following command:

chkconfig mysqld on

Next start the mysqld service

service mysqld start

Next run the command /usr/bin/mysql_secure_installation in order to set the root password and to delete other users and ultimately to stop the root user from having remote access.

Then you can access the mysql server, to make a new user, which you can login in as rather than using the root user all of the time.

We then editted the /etc/my.cnf, to allow the server to be access remotely only on the address of 10.10.10.30, which was the static ip address of this machine which we install the mysql server on.

We add the following line to this file below the area which is [mysqld]
bind-address=10.10.10.30

Note: this ip address needs to be static ip address of this single machine.

Next we added a rule to the firewall by editting the /etc/sysconfig/iptables file, by adding the following line to it, above the ssh line:

-A INPUT -p tcp -m state --state NEW,ESTABLISHED -m tcp --dport 3306 -j ACCEPT

Once you have save and closed this file, restart or reload the iptables:

service iptables restart

Potentially, this is the server ready to be used remotely, although you are still required to add a user which has the correct privileges:

No more space available...best just watch the video!!!!

---

Closed Caption:

and welcome to this video tutorial and
I'm stable thorn and today we're going
to be talking about accessing your mask
or server remotely now basically in the
previous tutorials of install mysql
server added and granted users and stuff
like that but that for the sake of this
tutorial i wanted to go through
everything from the beginning so anyone
else watching it knows the exact steps
that we want to do is isn't going to get
confused by the stuff that we've done
already
ok so let's get started now the
beginning bit of installing my school
i'm just going to run through really
quickly I'm going to leave a little link
up right now too so you can go and see
the slow version
ok but this is just going to fly for it
and get to a stage of actually adding
users and then changing the
configuration so without further ado
let's go on so we need to be rude first
of all and then say yum install my
squirrel and microserver that's just
going to fly fruit and find the packages
that we need to download say yes to
download them it's gonna go off
download the four packages and install
them
it's running the checks now installing
and still in very fine and complete ok
let's just clear the screen by pressing
ctrl L now that its installed let's just
say it so that my scroll d is going to
run on server startup ok if it crashes
then the magical server will start up on
the event that has crashed
ok next thing we want to do is actually
starts the Moscow service itself or the
daemon case it's initialized everything
is giving us all the instructions of
what we got to do setting the root
password
ok that's the next thing that we're
going to do so once a user being my
squirrel secure groups installation
ok we haven't got a root password set
removed my squad completely case it's
not on there so there's no databases or
anything so we just say yes to set the
root password
we're going to set it with password i
would advise of season strong password
remove an anonymous users disallow root
login remotely yes remove test database
in access to it
yes ok reload privileges table now yes
ok that's that now done ok so now we
know that Moscow is up and running it
will start on service start at once
again route can't access this one my
school server remotely therefore we can
only access it on this one this one
computer itself and that's it
ok so let's now login to the Master
server just say mask well dash p enter
our password password is just press ctrl
L to clear the screen now let's just
look at the host at the use table so
select everything from mysql database in
the user table and as you can see here
we've got two uses both of roots and and
different host the first host of 127 .
zero . zero . one and then the other
local house so that 1271 is then the
loopback address and the second one is
localhost so it means that we can access
it from this one computer one of the
local host and then any other processes
can act
is it through then the the loopback
address and so on
ok that's not good so now what we'll do
is add a user for myself
David kk so he's got the exact same
privileges as the roots because I don't
potentially want to login as root with
it
okay so we're gonna say first of all is
create user and say David at first of
all 127 . 0 . one
ok and then identified by then password
once again
ok and then we're going to do another
one of then local hosts want to be able
to mirror what the roots got basically
ok we're not going to delete the route
we're just gonna leave the root way the
way it is but we're going to have my
user to be in full control of it
ok so now that we've got this rich need
to grant privileges to David place we're
going to grant all to give me every
single privilege race say privileges
after this and then we say on we say
every single database gave every single
table every single bait database then we
say to so which user we're giving these
two and we're giving this to David and
at and then localhost we need to do is
twice and then we can say with up with
grant option
ok so it means that I've got the ability
to grant uses to other people and say
enter now we just add 14 1270 dr 0 to 1
and this place that in there
ok that's basically it done selects now
say flush privileges to make sure things
loaded again
ok we're going to say flash host as well
just to know that both things
whoops by things have been flushed just
press ctrl R to clear the screen and now
we can say select a host user from my
squirrel user and as you can see now
we've got a duplicate and user of reach
but with the name David this time and
actually the same password but you want
to obviously have different passwords
and strong passwords but for the sake of
clarity
in here we're just using the password
off textual form password
ok so now that we've done this let's log
out as read shall we
and it's just say quit and it's locked
back in as David case let's say use it
David and then password
let's go back in just press ctrl out to
clear let's say select user first of all
as you can see it's david at localhost
is locked in now for the sake of
purposes let's let's quit out this game
ok control over to clear and it's now
look in this time using user David but
host of 127 . 0 . one and then switch
piece for the password and just control
out to clear the screen and then select
user once again and we're still in as
the localhost because the IP address has
been resolved to this it's not such a
thing to really to worry about okay I
said now that we've done this we want to
know that we can access it remotely
ok now for the sake of purposes of this
we're going to put a Yuuzhan here of
james as well and we're going to make a
database for James just to show the
difference of what a person can access
ok so let's just say we stop doing this
capital letters because it's annoying
create user i'm going to call James
that's localhost ok actually nice James
is going to be on on the other machine
okay yeah it's going to be on the other
machine which i'm going to show you in a
minute which is which is so which we
want which is 10 . ten . ten . 10
ok so we won't allow access on 10 . ten
. ten . 10 right we're going to use the
IP address and then identified
by i said i'm going to stop username and
password case that's now done and now
let's make a database for James case
let's say create create database and
it's just prefix it with a DB and save
James I just say James James to do
okay so we got the database to created
create databases so we create database
DB underscore James now that's created
now let's create privileges for James
just on this one database on every
single table that they make on this one
database so let's say the grants ok and
will say all to it and on this time we
won't say star which is a DB James .
start so on this data based on every
single table and we're giving all
privileges to it so potentially can do
everything you want to this one database
ok let me say two then James at ten .
ten . ten . 10 so this one host this one
computer can access this one database
okay but we're not going to give him the
grant option
alright so let's just reload that now
let's just say flush privileges and
let's just flip for you see if we can
find it there is a flash house as well
that's now done
ok so now we can say show databases
first of all okay and as you can see
we've got MDB James now let's just see
who's locked in first of all here so let
user it's me locked in
so now let's log out as David just to
show you that what happens when we try
to login as James so that's a user James
with password type in password access
denied because it's not on localhost ok
so it's now just look back in as David
everything's fine control L to clear the
screen
okay so now we've got James can't even
log in on this one machine which is what
we want as well because he wanted to be
able to login on the other machine now
in order for us to be able to lock in on
the other machine we have to edit
something I have to edit something on
the actual linux server rather than my
school machine itself so let's just say
quit on here
ok controller to clear the screen and
now what we do is we need to edit the my
cock confirm scala can file rather than
comp file their my . conf file and
inside of here okay what there is you
have to be rich obviously to access this
and just basically with inside of this
mysqld rocky area so beneath it
we need to say bind address ok now the
address that we're going to bind to by
default is zero . zero . zero . zero ok
so it means that every single IP address
can access it actually no I believe it
changed so it's only locally that you
can access it to we could add zero .
zero . zero . zero then everyone can
access it but we're going to put in the
IP address of this one machine which is
then blind dash address equal this
machine
ok this as you can see down here are
wrote the IP address of this machine so
i didn't forget which machine i'm using
ok so that's basically that bit there so
we just press escape codon WQ to quit
and exit now after these making these
changes we actually need to restart the
mysql service so let's just say service
mysqld restarts that's potentially the
most need to do you know if you've only
got those settings with inside of your
my . conf file then everything's going
to be fine now if you've got a huge
amount a list of other things you're
going to have to research some other
items within it because they might stop
you should be enough to do this but
potentially on sentence when you install
mysql server that's the default and my .
conf file that you can get so now that
the micro service
and has been restarted we should now be
able to access this machine as such this
muscular server on another machine so
for the second purposes of this what I'm
gonna do is an open up another terminal
ok and I'm going to ssh to my other
machine
ok and we want David there that is fine
okay now now i'm on my other machine
which is actually wear on sat as well
because this is a virtual machine which
i'm running in the background
ok so now what I want to do is I want to
say mysql user on James
ok and and this machine just to show you
very quickly obviously is then got the
IP address of 10 . ten . ten . 10 case
this is one that we want to work with
alright so let's say my squirrel user
James P password and we still get access
denied for user James at localhost ok
now there's a reason for this so it's
just first of all ping . 30 and we get
the the answer back which is no problem
now let's just move this down and come
back over to here now we've got the my
squirrel service running ok so this one
him
sure we get this right that once that
one there
okay we've got the Moscow service
running on here at the moment and this
is then my other machine
ok oh sorry been stupid thing that's why
we're localhost so we want at 30 ok so
let's just start this again for clarity
i forgot to do the heist thing that ok
so we didn't get this just do this once
again
mysql dash you James is the user we're
going to login with and we're going to
supply the HTM which and say then the IP
address of the machine which we want to
connect to and then dash p so start to
say password they cannot connect to
mysql server on this IP address this is
the next thing that we need to do
ok because although we've set out the
mysql server we've set up the users we
set it up a database for James if it
can't get through the IP the the
firewall in the iptables then we're
never going to get the connection
therefore it's saying that they can't
connect to the microserver so we have to
open up port 3306 on the input and
output chain of our iptables day of our
firewall so let's just place control L
to clear and we need to open up our
iptables file now we could just do it
from the command line here but I don't
want to wait so let's open this up now
this is the default settings of your
sentence machine on your firewall when
you install it now what we need to do is
beneath the port 22 so the ssh ml of
element of it
just press site insert then dash a and
then input and we want then the protocol
of TCP and we want to say the M to match
and we're going to match so well-matched
States ok and the state that we're
matching case new and a stablished ok
and then we're gonna match also then tcp
and the destination port is going to be
3306 ok and they want to see Jay for the
action and the action is going to be
except
ok that's pretty much it now we can see
that on our output chain the default
policies except anyway so we don't need
to add a role to the output chain case
of all okay and we're not running any
network address translation therefore we
don't have to worry about the forward
chain either
ok so this will do perfectly fine so
escape codon w to quit because we've
updated our IP tables where they need to
restart our IP tables or use the restore
function and so on but we just say
iptables restarts everything's been fine
now to come back over to our other
machine now gate which is the one which
of sh2 now this is at ten . ten . ten .
10 ok we can now run this one again
supply a password and as you can see
we're now in ok so how did this work
this is now the question
alright why did I give the IP address in
the host table and why did I give the IP
address of the host rather than off the
microserver i'm gonna explain this in
the next tutorial in part two
ok so for the time being we've got
everything done now in the second
tutorial I'm going to slow down and i'm
going to explain everything ok so don't
worry about it all right just come to
the next tutorial and we can slow down
and we can talk through what is exactly
we've done and slow time
ok so i look forward to seeing it you in
the next tutorial right bye-bye