MALE SPEAKER: Google Cloud SQL allows you to easilycreate, run, and manage MySQL databaseson Google’s infrastructure. I’m going to show you how to create a Cloud SQLdatabase, then demo how to connectto it from standard tools, and from applications runningon Compute Engine and App Engine. To get started, let’s jump over to the Cloud Console. I’ll start with selecting the Cloud SQL service,and then clicking the New Instancebutton to create a new instance. The first thing to specify is the instance ID. We’ll call this instance sample demo. We’ll leave the region set to the United States,but we could also create instances that live in the EU. Since we’re only using this instance for a demo,we’ll choose the smallest instance, d0, for our tier. And for the billing plan, we’ll go with the per useoption to be charged by the hour. This way, we’ll only pay for the databasewhen we’re actually using it. Under the options, we have the abilityto specify a specific backup window,and choose a type of replication that should be used. I’ll just go with the defaults. We’ll need an IP address for instance to connect to it,so we’ll select be Assign IP Address option. Finally, there’s the setting for specifying authorized AppEngine applications. The default App Engine app associated with this projectis currently the only authorized application. We’ll leave this as it is for now. On the right side of the page, there’sa nice summary of the current instance settings. That looks good. Let’s click the Confirm button to create the instance. We can see here that the instance is being created. Once the process of creating the instance completes,we’ll be able to check out its details,and start using it right away. Now the instance is created. Let’s select the instance, and take a look at the details. The status is running. Great. Notice that you click the Edit button to changethe settings of your database instance on the fly. For example, you could change the tier sizeif you determine that you need more or less RAM. Now that we’ve got our instance running, let’s connect to it. We can connect from any MySQL tool or application,such as Toad or MySQL Workbench. But we’ll start by using the standard MySQL command lineclient. It’s part of the MySQL community server software bundle,so once you’ve got that installed,you can start using the MySQL client. And I’ve already got it installed. At the moment, nothing has been authorizedto connect to our instance. That’s the default for maximum security. I’ll select Access Control to allow accessfrom my client machine. The first thing we’ll need is a MySQL user and password. For this demo, we’ll be connecting as the root user,so we’ll give the root user a password,and click Set to save it. Next, we’ll need to add the IP addressof the machine I’m connecting with. On a Mac or Linux machine, you can type the terminal commandifconfig to find your machine’s IP address. On a Windows machine, it’s ipconfig. Or, you can just do a Google searchfor “what is my IP address. “I’ll click on Add authorized network,enter the IP address of my machine,and click Add to authorize it. I won’t bother setting up an encrypted connection for now,but you should use it for all production databases. Now let’s jump to a terminal window to test our connection. Here’s the command I’ll run. We’ll type mysql, followed by the IP address of our Cloud SQLinstance for the host. And then the user flag set to root. And finally, the password flag without a value. When we run that, we’ll be prompted for our password. After entering the password, we are nowconnected to our Cloud SQL instance. Let’s confirm that by running a show databases query. And we get back the list the default databases includedwith MySQL. Now let’s write a script to create a databasewith a single table containing a small sample of datafrom Wikipedia. Here’s the script we’ll run. It’s just a standard MySQL script. For example, it could be a dump of an existing databaseyou want to move to the cloud. Here’s where we create the database. And here’s our use statement to select the new database. This statement creates our table with two columns specified. The remaining statements will insert datainto our newly created table. OK, let’s jump back to MySQL prompt, and run the script. The command to run the script is source,so we’ll type “source,” followed by the location of the script,and hit Enter to run it. There, our database has been created. We should now be able to run a queryto test that all our data is what we expect. We’ll run the query. Select name, size from stars, and hit Enter. And there’s our table with the data that we just inserted. Now that we’ve got some sample datain Cloud SQL, let’s look at how we can use a Google ComputeEngine instance to query Cloud SQL from a PHP page. Here’s the Cloud Console, where youcan see that I’ve already createda VM within Compute Engine. It’s already got PHP and Apache installed on it. I’ve also created a firewall within Compute Engineto allow internet requests on port 80. We could see that by going to the Network Section,and selecting our default network. Here under the firewall section, youcan see that I’ve already created a firewall named HTTP1,and it’s set up to allow traffic on port 80. Now let’s SSH into our VM, and test runninga PHP page that connects to Cloud SQL. I’ll click on my instance PHP demo to get the SSH link,and we’ll use that to SSH into the VM. And we’re on our VM. Again, we need to get the external IPaddress of this Compute Engine VMso that we can allow access to it from Cloud SWL. Back in the Cloud Console, we can copy the VM’s external IPaddress here. Then we’ll go back to the Cloud SQL’s Access Control Section,and click Add New under the authorized network section. Finally, we’ll paste in the VM’s IP address, and click Add. Now let’s go back to the terminal windowthat’s SSH’ed into our Compute Engine VM. I’ll create a new PHP page called index. php in the var/wwwdirectory where Apache is serving pages from. I’ve got the PHP code in an editor on my local machine. Here’s the connection to Cloud SQL,and here’s where we select our database. Here’s the query that will be run,and here’s where the code sends the query to Cloud SQL,and outputs the results to the page. The remainder the code is some CSS styling for the HTML,along with some JavaScript to format our numbers with commas. I’ll select all of that, and copy it, and paste it backin our index. php page, and save the file. Now we should be able to open a new browser tab,and paste in the IP address of our VM,and the file name of our new index. php page. And there is our PHP page displaying our datafrom Cloud SQL. Now what about using a web-based database managementtool, like PHP MyAdmin?Using the same Compute Engine VM,we can try this out quickly. The VM already has phpMyAdmin installed,so the only remaining thing we needto do to try out our phpMyAdmin setupis to specify the IP address of our Cloud SQL instanceas the default server. We can do that by editing the config. inc PHP configurationfile with the following command. We’ll type sudo nano, and the location of the fileto edit it. Then we’ll jump down where you can optionallyspecify the server host. Here, we can uncomment this line,and change local host to be the IP address of our Cloud SQLinstance. And we’ll close the file, and save the changes. Now we should be able to open up a new browser tab,and enter the external IP address of our VM plusphpMyAdmin. And there’s the phpMyAdmin login screen. We’ll enter root as the user name,and specify the password that we set up previouslyfor Cloud SQL in the Cloud Console. For the server choice, we’ll choose the IP addressof our Cloud SQL instance, and click Go. And we’re in. We can select our bigstars database,and then view the data in the stars table. That looks good. We can now administer our entire database from phpMyAdmin. We can see the databases, we can run SQL queries,monitor statistics, and so on, all through a web front end. Great. Now let’s take that same PHP page that we previouslyran on Compute Engine, and run it on Google App Engine. I’ve already created an App Engineapplication called Cloud SQL Sample. The next thing we’ll need to do isto authorize access to Cloud SQL from our App Engine app. Here in the Cloud SQL Access Control Settings,under the section titled Authorize App EngineApplications, we’ll click the Add New link,and then enter the app ID of our,app, and then click the Add buttonto authorize our App Engine applicationto connect to our Cloud SQL instance. Now let’s jump back to our terminal window. Here’s the PHP page that we ran on Google Compute Engine. The only modification that we haveto make for it to run on App Engineis to update the connection string. We’ll replace the Cloud SQL instance IP address withthe string :/cloudsql/ followed by our project ID,and then colon, and then the name of our Cloud SQL instance. Then I’ll set the password to be an empty string since we’vealready authorized our App Engine application’sconnection to Cloud SQL and the Cloud Console. I’ve already deployed this updated PHP page out to our AppEngine application, so now we shouldbe able to open a new browser tab,and then go to our App Engine app. And there’s our PHP page with data from Cloud SQL. Excellent. I’ve just showed you a very simple demonstrationof creating a classical database along with different waysthat you can connect to Cloud SQLto manage your databases, from standard tools,from applications running on Compute Engine,and from applications on App Engine. I’ve also show you how to use Cloud SQL as the data sourcefor your web pages. Here are a few resources that you can go to learn more. Now that you’ve seen how quickly you can get started, try it outfor yourself, and start using Cloud SQL right now. Thank you for watching.
The post Getting Started with Google Cloud SQL appeared first on Open Source.
from http://bit.ly/1DUtzoT
No comments:
Post a Comment