Setting up a Proxy for Google Cloud SQL (P5D71) - Live Coding with Jesse

hey everybody welcome to live coding with jesse i'm jesse and today i'm going to try to fix the problem that we encountered yesterday and there were a few reasons why that was happening and so i'm going to share with you a couple of the reasons and then we're going to try to fix it if you didn't see yesterday's video here's a quick recap of what we tried to do we started up a sql a mysql database on google's cloud platform and we took our wordpress mysql database that we've been using for this project and we made a copy and then we sent that copy over to the new database that we're running on google's cloud platform so the database is there the tables are there we verified that but we couldn't connect we were attempting to host wordpress with now from zeit and we couldn't get it to connect to the database and that's kind of where we left off yesterday so i thought a lot about it i couldn't even though i stopped working i did not stop thinking about that problem which i don't know if that happens to you all but that frequently happens to me like if there's a problem i didn't solve i i just keep thinking about it all the time i think maybe it'd be better if i just worked until i fixed it and then i could be at peace but in this case i think i've found the solution but i haven't had time to test it yet the solution is from what i understand in addition to a few other adjustments we're going to make a proxy because we authorize some ip addresses some networks unfortunately when you host the site with now you don't have an ip address it changes too often so they don't even give you the ip address uh of your your instance of whatever you're running with now so in cases where you can't give them an ip address google's cloud sql offers the option of running a proxy and accessing it like that there's also a further complication that harshit found and that was we were the example we were using to run wordpress with now was assuming that we were using aws and their security so we we may need to go in and disable some of that stuff too but first i definitely want to get this proxy set up so i haven't done this before we're going to go through it together i have the instructions up here somewhere i'm going to paste in a link to the instructions so you all can check that out with me and let's see hey hey everybody uh see there's a few you saying hi yeah let's let's get started so as soon as i find those instructions i'm gonna send them all to you and we're gonna get started so here they are there in the chat now if you have trouble clicking on that to and it doesn't open then try copying and pasting it and let's see all right i'm gonna make sure i have this up on one of my other screens and we can get started with that my computer if my fan is running too loud and that's bothering you let me know i tried closing down as much as i could and i can't figure out what is running uh and causing the fan to uh to run in overdrive like that all right so let me let's move over to the split screen there we are okay harshit is making a pull request right now to fix some of those issues so i'm gonna wait and pull down the latest changes before we do anything on this uh repo that you see here uh and what i'm gonna do instead is go through let's start this timer and let me pull this over here and let's go through some of these instructions try to get this working all right we can see here the proxy what it's going to do for us is it's going to let us access our server our database here without white listing ip addresses so that's what we we can't white list because we don't have you know a static ip address or even really a range that we could set so i guess we could y'all can read this if you want more info but uh the graphics are nice uh so this is how it's going to connect uh instead of going directly to uh cloud sql obviously the proxy lets us go to the proxy and then go to cloud sql so uh pretty simple concept uh i at least i think let me know if it's if it's not i have been reading up on this for a while so if you i may have a slight advantage at understanding it okay all right so we've enabled the api so we did that yesterday we need these gcp authentication credentials so i'm just going to open that another window for right now valid database user account password we set that up yesterday your instance must have an external ip address which we we have alright awesome so let's um let's try this out and try this out on our i'm using a mac right now so let's get to our command line and let me organize this a bit better all right so let's go over to our command line and yeah i'll just open up a new window here all right so we're going to download this great downloaded so quickly and then we're gonna make it executable great we need to provide it with this info so we should have all this info stay to come if you're application we sent the clouds go find credentials here's what's on the cage okay great so impressed okay i'm just scanning over this i think it's always good practice to read all the instructions before doing it um i don't know when i'm cooking it always helps with the recipe if i know what's going to happen ahead of time so i'm not going to read it all i'm just going to scan a bit hmm all right this is good we're gonna they have a github page just for this that we're using which is awesome all right so we are just testing this right now but eventually we would have to go to this step if we end up using this so let's go now i can't find where i left off all right we'll see how do we want to provide these credentials all right you can create a credential file hmm okay all right so we have a few more people joining us for those of you that are joining i just want to reiterate we're going through this process i've never done this before so that's why there's a bit of silence on my part from kind of reading over this i am going to check the chat because it looks like there's a lot in here um okay so it it does look like uh harshit made a pull request there so when the time comes we're gonna to put bringing his pool requests before we attempt to connect everything the wordpress instance to this database um let's see okay i'll go through if you have asked some questions i will uh answer those questions as soon as our pomodoro timer is up so we have about 17 minutes left here uh at this point let me let me paste this in the chat as well and then that way you can check out the tool that we were using that we just loaded that i just loaded locally and i guess for those of you that weren't here earlier here's the page for the instructions we're following okay so here's some example invocations so let's check this out hmm i guess we can try one of these all right so let me try let's move over to here just to change this up so we we do need to replace you need so i'm trying to figure out where they're expecting this to be run from hmm all right let's see all right so i'm not exactly sure let me tell you what i'm i'm thinking right now what i'm not sure about i may just end up copying some stuff and throwing it in the command line just to see if it works uh because we read up here a little earlier i guess up here first it was talking about loading this proxy on your local machine and then it was saying uh something about running it in the google cloud platform console which which i mean that's definitely two different things so i'm trying to to figure out what they want um let's go all right so we have that cloud sql proxy all right good it's not a directory let's see if we can just whenever i just do run run is not a command what's the equivalent command actually maybe i'll just look here okay of course all right so we definitely need default credentials which is what we're trying to do over here i just need to figure out what the credentials are and actually this this will help okay we have this info i'm just gonna grab it from okay and i wonder if this is going to be the database name let's see if this is what they want all right i'm not sure if they want like the main name for the google cloud account or the database username we'll find out all right so let's try this out now what's wordpress command i found all right so obviously that was not what they needed so let's try it again uh shoot it's treating it like two different commands let's see hmm all right just i'm checking back in the chat to see if see if i've lost all of you uh since i'm a bit lost myself all right so i mean at this point i'm just gonna try a few more variations on this uh on this command and see if i can get uh something different to come up uh even if it's a different error message that's going to be progress uh if it doesn't work after a few more tries then i'll just go back to the documentation make sure i didn't miss a step all right you know what let's let me double check this i think there was something yeah maybe since we're doing this locally right now that's equal to you using mph all right so anyway this is what i was looking for the enable the api i just want to double check that we have enabled the proper api shhh okay all right now that that's enabled i'm just gonna try to run this again all right nothing okay let's try these instructions for now just to get something working and um then we can we can move on i mean i think it it may at the very least help us to rule out like where where am i making this mistake at if you haven't already installed g click command line tool all right so there is a gcloud command line tool awesome uh let's see i guess we'll download that real quick um welcome to the cloud sdk yay yes yes great so we just installed this uh google cloud stuff would you like to log in sure take a clog project to use five one all right awesome okay looks like we're all set then to continue on with these instructions and i'm going to minimize these it looks like we're almost ready for question time so i'll hear a little beep soon we done that let's use this quick start all right we've already done this it's details connection since this is connection name we've already done that so you already have copied the instance connection name start the proxy in its own terminal she can modernize output we're going to replace instant connection name all right so let's copy that and replace instance connection name with this i cannot find credentials login i thought we already did this all right then all right let me run this and then we'll do questions hey great wonderful all right so we got down to this step for it to work cool all right so that's like not nearly where we need to be at but it's the first step so let's take a short break and i'm gonna answer some questions uh jeremy asks at a high level what's the value add slash purpose for using the proxy the proxy allows us let me back up so normally you would have an ip address white list and only connect incoming connections from those ip addresses would be allowed for that sql database but we we want to be able to connect to it without having to whitelist an ip address so that's what the proxy enables us to do is to still have incoming connections from whatever ip address and have that work for this particular project we need that because the place where i would like to host wordpress from wordpress needs to communicate with this database with that place is going to have variable ip addresses so that's about as high level as i could make it i guess stephen says so lost but i totally have a video to refer to if i ever need this yeah for sure um world says you have to give google your database passwords um well essentially we've we've created a database with google's cloud sql service so google is running our database so obviously they they have our passwords okay cool oh man i went to grab more coffee and my coffee mug is out of coffee i mean like in the grand scheme of things that's no big deal but really that's kind of like the worst when it happens all right so i don't have any more questions right now so i think i'm gonna go right back into another pomodoro session and i think i only want to do one more pomodoro session i have a lot of paperwork and to do from my last trip um for my last conference and somebody's gonna help me with that since i never remember how to do it right and i also have to pick up my son from school so i have to leave the office early so anyway i want to get some stuff done now as quickly as i can alright so we can test our connection by going replacing username with your mysql username okay cool i always like to edit these oops i guess i forgot to copy i always like to edit these in my code editor username ah shoot let's try this um let's try like that and then i want to put so i want to just go into that wordpress database uh so that's how i'm going to modify that just a bit let's see where am i where do i need to run this here no we're in another spot no i don't know all right let me run it from here ah do i not i'm thinking i thought i had this tab open but i have it open on my laptop right next to me so obviously it's not going to work for right now um let me i need ssh into somewhere else that's my sequel uh do all right i'm installing my sequel really quick on my other screen there we go all right let's see if i have it yet oops let me open up a new one no why is it not found still hmm all right that's not cool i just went through the install process for this and now it's not working hmm hmm all right well let me go back service account definitely skipped this part before hmm all right so we have this proxy running hmm hmm maybe there we go my sequel shell okay all right let's see let's see what i can do then now now that we have this started um all right let's try what oh other way all right cool so it looks like we can use this all right let's try it all right what was the unexpected identifier all right let's try it without this then nah still hmm hmm all right let me let me check this which robocop said if everything worked uh without warning that would be too good to be true yeah for sure i mean i expected that we would have some trouble since i've never attempted anything like this before i do wish that it was going slightly better but see hmm okay jeremy miller saying uh p without a pass without a password let's see still interesting i wonder let me try this one so hmm all right this is kind of a dead end right now hmm i need help troubleshooting it's worth a shot here hmm all right i don't think any of those apply mad duchess says i was saying i had it on unix sockets but it was supposed to be tcp yeah i tried the tcp already and it did not work um i mean maybe other than your presence hmm i mean right now i we just keep getting that error like a syntax error let's see all right cool so i just went there in my browser just to see what would happen so at least we're it's registering oh actually you know what i could use let me try this uh i'll let me open this uh sql operations studio and then i'll see if we can can do this so let's do this and we'll do password and see if this works um not looking good i'm not reading anything here nope server was not found they're not accessible okay shoot i mean i could try okay let me try running it like this hmm hmm uh let's see check stack overflow question all right i'm gonna check this on my other screen quickly uh okay cool i did check out that question so let's try it out all right so evidently that is not working let's go to yeah um wrong way cool there we go oh hmm it's not giving me anything right now let's see if this is showing anything no let's break this out so we can see what's going on here okay maybe i should put in that this might have to add oh jeremy thank you i always mess that up they're not connected um maybe i should put in put this into hmm you guys hmm all right jeremy i'm glad you're i was just reading that in the stack overflow thing uh to do it that way so uh so if it's mysql js we can go connect um and then at zero six yes no all right let me try it with and let me go into lastpass because that password is very long hmm hmm all right well my second pomodoro session just finished i just found someone else who's getting a similar error um stephen says this is a long way from me building front-end sites and client dashboards yeah it's a little different than what we're used to it's cool to do new things though i don't know it's fun definitely stretches the brain a bit hmm well we made some progress today right wasn't definitely not mission accomplished yet but made some progress interesting hmm i was just trying to think just trying to see if there are any flags that i could use hmm uh world says are you afraid that google will give you a bill for like 1k or something for using cloud uh no i'm not really afraid of that you don't want to see how much i've been build so far i don't know let's check it out billing um i guess nothing so far hmm so yeah i guess nothing nothing so far that's cool i mean i had i'd actually only plan on having this up just during yesterday's stream but we didn't get done what i wanted to get done so that's why it's still up uh john hanson says would it be worth trying out your commands in the google cloud show sorry within the google cloud platform console well the thing i was i wanted to do was i wanted to be able to connect from from outside the cons because we you know we already were able to connect using the console in google cloud platform all right i'm just scanning to see if there are any other questions all right uh hodor says i'm looking to make an electron uh and react uh app is you're setting up an electron project still to go to um yeah that's the latest that i've done with electron so check that out and then check out the project excuse me check out the project that we started in that video um the the github repo for that to see where we're at with it uh it's not you know up to date as much up to date as it could be uh but i think it should give you an idea of of how to get started though stephen says you need some coffee yeah i need i need something um all right cool i i need to i need to end the stream and go do my paperwork and stuff before i have to pick up my son so thank you all for watching uh sorry we didn't finish this again i don't know if it was painful watching at all it was a little bit painful for me going through this uh still i mean i still liked learning more about this and figuring some of this stuff out so you know maybe i'll work on a little bit later at home or something like that i kind of don't want to do a whole another stream with this stuff uh so we'll see what happens uh it's tomorrow tomorrow's friday yeah i'll try to do a stream tomorrow number whether i get this working or not i'll try to stream uh about something so uh oh robocop says the best stream by far i don't know if you're being sarcastic or what but uh i don't know maybe it it's better to watch me struggle on on that end um but uh let me see can you get those all right julio sending me something to check out so i will check that out i'm copying and pasting that right now to my browser and thank you let's see isn't it okay all right i'm gonna end the stream for real now thank you all so much for watching and for all your help uh hopefully i'll be back tomorrow and until then uh have a great day