male speaker: google cloudsql allows you to easily create, run, andmanage mysql databases on google's infrastructure. i'm going to show youhow to create a cloud sql database, thendemo how to connect
Android cloud, to it from standard tools,and from applications running on compute engineand app engine. to get started, let's jumpover to the cloud console. i'll start with selectingthe cloud sql service,
and then clickingthe new instance button to create a new instance. the first thing to specifyis the instance id. we'll call thisinstance sample demo. we'll leave the regionset to the united states, but we could also createinstances that live in the eu. since we're only usingthis instance for a demo, we'll choose the smallestinstance, d0, for our tier. and for the billing plan,we'll go with the per use
option to becharged by the hour. this way, we'll onlypay for the database when we're actually using it. under the options,we have the ability to specify a specificbackup window, and choose a type ofreplication that should be used. i'll just go with the defaults. we'll need an ip address forinstance to connect to it, so we'll select beassign ip address option.
finally, there's the settingfor specifying authorized app engine applications. the default app engine appassociated with this project is currently the onlyauthorized application. we'll leave thisas it is for now. on the right sideof the page, there's a nice summary of thecurrent instance settings. that looks good. let's click the confirmbutton to create the instance.
we can see here that theinstance is being created. once the process of creatingthe instance completes, we'll be able tocheck out its details, and start using it right away. now the instance is created. let's select the instance, andtake a look at the details. the status is running. great. notice that you clickthe edit button to change
the settings of yourdatabase instance on the fly. for example, you couldchange the tier size if you determine that youneed more or less ram. now that we've got our instancerunning, let's connect to it. we can connect from anymysql tool or application, such as toad or mysql workbench. but we'll start by using thestandard mysql command line client. it's part of the mysql communityserver software bundle,
so once you've gotthat installed, you can start usingthe mysql client. and i've alreadygot it installed. at the moment, nothinghas been authorized to connect to our instance. that's the defaultfor maximum security. i'll select accesscontrol to allow access from my client machine. the first thing we'll needis a mysql user and password.
for this demo, we'll beconnecting as the root user, so we'll give theroot user a password, and click set to save it. next, we'll need toadd the ip address of the machine i'mconnecting with. on a mac or linux machine, youcan type the terminal command ifconfig to find yourmachine's ip address. on a windows machine,it's ipconfig. or, you can justdo a google search
for "what is my ip address." i'll click on addauthorized network, enter the ip addressof my machine, and click add to authorize it. i won't bother setting up anencrypted connection for now, but you should use it forall production databases. now let's jump to a terminalwindow to test our connection. here's the command i'll run. we'll type mysql, followed bythe ip address of our cloud sql
instance for the host. and then the userflag set to root. and finally, the passwordflag without a value. when we run that, we'll beprompted for our password. after entering thepassword, we are now connected to ourcloud sql instance. let's confirm that by runninga show databases query. and we get back the list thedefault databases included with mysql.
now let's write a scriptto create a database with a single table containinga small sample of data from wikipedia. here's the script we'll run. it's just a standardmysql script. for example, it could be adump of an existing database you want to move to the cloud. here's where wecreate the database. and here's our use statementto select the new database.
this statement creates our tablewith two columns specified. the remaining statementswill insert data into our newly created table. ok, let's jump back to mysqlprompt, and run the script. the command to runthe script is source, so we'll type "source," followedby the location of the script, and hit enter to run it. there, our databasehas been created. we should now beable to run a query
to test that all ourdata is what we expect. we'll run the query. select name, size fromstars, and hit enter. and there's our table with thedata that we just inserted. now that we've gotsome sample data in cloud sql, let's look athow we can use a google compute engine instance to querycloud sql from a php page. here's the cloudconsole, where you can see that i'vealready created
a vm within compute engine. it's already got php andapache installed on it. i've also created a firewallwithin compute engine to allow internetrequests on port 80. we could see that by goingto the network section, and selecting ourdefault network. here under thefirewall section, you can see that i've alreadycreated a firewall named http1, and it's set up to allowtraffic on port 80.
now let's ssh into ourvm, and test running a php page thatconnects to cloud sql. i'll click on my instancephp demo to get the ssh link, and we'll use thatto ssh into the vm. and we're on our vm. again, we need toget the external ip address of thiscompute engine vm so that we can allow accessto it from cloud swl. back in the cloud console, wecan copy the vm's external ip
address here. then we'll go back to the cloudsql's access control section, and click add new under theauthorized network section. finally, we'll paste in thevm's ip address, and click add. now let's go back tothe terminal window that's ssh'ed intoour compute engine vm. i'll create a new php pagecalled index.php in the var/www directory where apacheis serving pages from. i've got the php code in aneditor on my local machine.
here's the connectionto cloud sql, and here's where weselect our database. here's the querythat will be run, and here's where the codesends the query to cloud sql, and outputs theresults to the page. the remainder the code issome css styling for the html, along with some javascript toformat our numbers with commas. i'll select all of that, andcopy it, and paste it back in our index.php page,and save the file.
now we should be able toopen a new browser tab, and paste in the ipaddress of our vm, and the file name ofour new index.php page. and there is our phppage displaying our data from cloud sql. now what about using aweb-based database management tool, like php myadmin? using the samecompute engine vm, we can try this out quickly.
the vm already hasphpmyadmin installed, so the only remainingthing we need to do to try outour phpmyadmin setup is to specify the ip addressof our cloud sql instance as the default server. we can do that by editing theconfig.inc php configuration file with the following command. we'll type sudo nano, andthe location of the file to edit it.
then we'll jump downwhere you can optionally specify the server host. here, we canuncomment this line, and change local host to bethe ip address of our cloud sql instance. and we'll close the file,and save the changes. now we should be able toopen up a new browser tab, and enter the externalip address of our vm plus phpmyadmin.
and there's thephpmyadmin login screen. we'll enter rootas the user name, and specify the passwordthat we set up previously for cloud sql inthe cloud console. for the server choice,we'll choose the ip address of our cloud sqlinstance, and click go. and we're in. we can select ourbigstars database, and then view the datain the stars table.
we can now administer ourentire database from phpmyadmin. we can see the databases,we can run sql queries, monitor statistics, and so on,all through a web front end. now let's take that samephp page that we previously ran on compute engine, andrun it on google app engine. i've already createdan app engine application calledcloud sql sample. the next thingwe'll need to do is to authorize access to cloudsql from our app engine app.
here in the cloud sqlaccess control settings, under the section titledauthorize app engine applications, we'llclick the add new link, and then enterthe app id of our, app, and then clickthe add button to authorize our appengine application to connect to ourcloud sql instance. now let's jump back toour terminal window. here's the php page that weran on google compute engine.
the only modificationthat we have to make for it torun on app engine is to update theconnection string. we'll replace the cloud sqlinstance ip address with the string :/cloudsql/followed by our project id, and then colon, and then thename of our cloud sql instance. then i'll set the password tobe an empty string since we've already authorized ourapp engine application's connection to cloud sqland the cloud console.
i've already deployed thisupdated php page out to our app engine application,so now we should be able to open anew browser tab, and then go to ourapp engine app. and there's our php pagewith data from cloud sql. excellent. i've just showed you avery simple demonstration of creating a classical databasealong with different ways that you canconnect to cloud sql
to manage your databases,from standard tools, from applications runningon compute engine, and from applicationson app engine. i've also show you how to usecloud sql as the data source for your web pages.
here are a few resources thatyou can go to learn more. now that you've seen how quicklyyou can get started, try it out for yourself, and startusing cloud sql right now. thank you for watching.
