Skip to content

Instantly share code, notes, and snippets.

@zmh

zmh/bowery-php-mysql.md

Last active August 29, 2015 14:02
Show Gist options
  • Star 3 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save zmh/0805fb01109fab54b244 to your computer and use it in GitHub Desktop.
Save zmh/0805fb01109fab54b244 to your computer and use it in GitHub Desktop.
Download ZIP
Using Bowery with PHP and MySQL
Raw
bowery-php-mysql.md

Using Bowery with PHP and MySQL

Bowery works well with PHP and MySQL, but there are a few steps you have to take before you can get started.

##Short version

  1. Run bowery pull 5397a3faabd3328a13000006 in an empty directory
  2. Run bowery connect and then disconnect with CTRL+C once you see the message "Service php upload complete. Syncing file changes."
  3. Run bowery ssh php, service apache2 restart, and then exit. Disregard any terminal output or warnings that are printed.
  4. Run bowery ssh mysql, mysql --user=root --password=your_password < /application/example.sql, and then exit. Disregard any terminal output or warnings that are printed.
  5. Run bowery connect and then open the last URL that is printed.

##Slightly more detailed version

#####Note: You can skip to step 4 by running bowery pull 5397a3faabd3328a13000006 in an empty directory.

1. Set up your directory structure.

Let's make a new directory and set up two subdirectories, one for PHP and the other for MySQL. This isn't strictly necessary, but makes it easier to determine where files and services are.

$ mkdir LAMPexample
$ cd LAMPexample/
$ mkdir php
$ mkdir mysql
$ ls
mysql php

2. Add the two services

Here, we tell Bowery that we want

  1. a PHP service that has source files in the ./php directory and uses the premade php55 Bowery image, and
  2. a MySQL service that requires port 3306 to be exposed and uses the mysql Bowery image.
$ bowery add php
Image: php55
Path: ./php
Ports:
Start Command:
Build Command:
Test Command:

$ bowery add mysql
Image: mysql
Path: ./mysql
Ports: 3306
Start Command:
Build Command:
Test Command:

3. Add in two example files

I've pre-written (1) a PHP file that prints out some data from MySQL, and (2) a SQL file that you can use to pre-populate a database so you can get started easily. Those files are here.

index.php

Comments are provided inline, but nothing special is happening here. The only thing to note is that root and your_password are the default credentials for the mysql image—you probably want to change those if you're using this in production. Also, notice how we're using getenv("MYSQL_PORT_3306_ADDR"); to access the MySQL hostname from within the PHP image.

<?php
	// Our login details
	$username = "root";
	$password = "your_password";

	// Bowery gives you a hostname in the form of ec2-12-345-678-90.compute-1.amazonaws.com:12345
	// The mysqli() command requires host and port to be provided as separate arguments, so we
	// split those here
	$hostname = getenv("MYSQL_PORT_3306_ADDR"); 
	$host = parse_url($hostname, PHP_URL_HOST);
	$port = (int)parse_url($hostname, PHP_URL_PORT);

	$db = new mysqli($host, $username, $password, "example", $port);

	if($db->connect_errno > 0){
	    die('Unable to connect to database [' . $db->connect_error . ']');
	}


	if(!$result = mysqli_query($db,"SELECT * FROM users")){
	    die('There was an error running the query [' . $db->error . ']');
	}

	while($row = $result->fetch_assoc()){
	  echo "---------------------------<br>";
	  foreach ($row as $field) {
	  	  echo $field . "<br>";
	  }
	}
?>
example.sql

Here, we create a database named example that has a users table with 3 fields: id, name, and email. We add two sample users so you have some data to work with.

-- MySQL dump 10.13  Distrib 5.5.22, for debian-linux-gnu (x86_64)
--
-- Host: localhost    Database: example
-- ------------------------------------------------------
-- Server version	5.5.22-0ubuntu1

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0
*/;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `users`
--
CREATE USER 'root'@'%' IDENTIFIED BY 'your_password';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;

CREATE DATABASE example;
USE example;
DROP TABLE IF EXISTS `users`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `users` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(20) DEFAULT NULL,
  `email` varchar(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `users`
--

LOCK TABLES `users` WRITE;
/*!40000 ALTER TABLE `users` DISABLE KEYS */;
INSERT INTO `users` VALUES (1,'Bob','bob@gmail.com'),(2,'Steve','steve@gmail.com
');
/*!40000 ALTER TABLE `users` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2014-06-10 18:37:51

The only lines in the above that should give you pause are

CREATE USER 'root'@'%' IDENTIFIED BY 'your_password';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;

We need to do that because the default user is only assigned to localhost.

4. Run an initial connect

$ bowery connect
Hey there Zachary. Connecting you to Bowery now...
Services are availble in the forms:
  <name>.<id>.boweryapps.com
  <port>.<name>.<id>.boweryapps.com
Uploading file changes to mysql, check "bowery logs" for details.
Service mysql is available at:
  mysql.53977977961dd91b1b000002.boweryapps.com
  3306.mysql.53977977961dd91b1b000002.boweryapps.com
Uploading file changes to php, check "bowery logs" for details.
Service php is available at:
  php.53977977961dd91b1b000002.boweryapps.com
Service mysql upload complete. Syncing file changes.
Service php upload complete. Syncing file changes.

Once you see that last line, disconnect by typing CTRL+C.

5. Start Apache

We're working on eliminating the need for this step, but for the time being you need to manually start Apache. To do that, ssh into your PHP service with bowery ssh php and then run service apache2 restart. Disregard any warnings or terminal output you encounter. You can type exit after that's done to come back to your local machine.

$ bowery ssh php
Welcome to Bowery Services.
---------------------------------------------
Name: php
Application: 53975543d8fc19392c000003
Time: 2014-06-10 15:02:13.167972841 -0400 EDT
---------------------------------------------
Welcome to Ubuntu 12.04 LTS (GNU/Linux 3.8.0-42-generic x86_64)

 * Documentation:  https://help.ubuntu.com/
Last login: Wed Jun  4 01:58:38 2014 from 113.234.148.103
root@e5390acda62c:~# service apache2 restart
 * Restarting web server apache2                                                AH00558: apache2: Could not reliably determine the server's fully qualified domain name, using 172.17.0.209. Set the 'ServerName' directive globally to suppress this message
                                                                         [ OK ]
root@e5390acda62c:~# exit

6. Prepopulate your database

We'll need to import that SQL file into MySQL. To do that, ssh into your MySQL service by running bowery ssh mysql and then run mysql --user=root --password=your_password < /application/example.sql. Disregard any warnings or terminal output you encounter. You can type exit after that's done to come back to your local machine.

$ bowery ssh mysql
Welcome to Bowery Services.
---------------------------------------------
Name: mysql
Application: 5397a3faabd3328a13000006
Time: 2014-06-11 10:46:54.303015606 -0400 EDT
---------------------------------------------
Welcome to Ubuntu 12.04 LTS (GNU/Linux 3.8.0-42-generic x86_64)

 * Documentation:  https://help.ubuntu.com/
Last login: Wed Jun 11 14:38:33 2014 from 66.192.207.22
root@263e5248de15:~# mysql --user=root --password=your_password < /application/example.sql
ERROR 1396 (HY000) at line 22: Operation CREATE USER failed for 'root'@'%'
root@263e5248de15:~# exit
logout

7. Run bowery connect to complete everything!

One more command to run: bowery connect. When you do that, you'll see an output like this:

$ bowery connect
Hey there Zachary. Connecting you to Bowery now...
Services are availble in the forms:
  <name>.<id>.boweryapps.com
  <port>.<name>.<id>.boweryapps.com
Uploading file changes to mysql, check "bowery logs" for details.
Service mysql is available at:
  mysql.53977977961dd91b1b000002.boweryapps.com
  3306.mysql.53977977961dd91b1b000002.boweryapps.com
Uploading file changes to php, check "bowery logs" for details.
Service php is available at:
  php.53977977961dd91b1b000002.boweryapps.com
Service mysql upload complete. Syncing file changes.
Service php upload complete. Syncing file changes.

That last URL—the one for the PHP service—is the one you want to visit. When you open that in your browser, you should see something like the following:

---------------------------
1
Bob
bob@gmail.com
---------------------------
2
Steve
steve@gmail.com

That data is being pulled from MySQL and displayed using PHP. Once you see that, you're all set!

Final thoughts

Getting separate images to talk to each other

In this tutorial, we created two Bowery services: one named php and the other named mysql. The php service could access the mysql service using the variable MYSQL_PORT_3306_ADDR, which we accessed in PHP by calling getenv("MYSQL_PORT_3306_ADDR").

Note that the name of this variable follows a convention that you can use with any service and any port. To access the address of a particular port (say, 4000) on a particular service (say, randomservice), you can use the variable of the form RANDOMSERVICE_PORT_4000_ADDR. If you're looking to access the service on the default port (80), you can just use the variable of the form RANDOMSERVICE_ADDR.

Using your own MySQL Data

You can access MySQL by the command line and/or by importing a preconfigured SQL file. Once you've run bowery ssh mysql to get into your MySQL service, you can access the MySQL command line by running mysql --user=root --password=your_password. You can also put files into the mysql/ directory on your computer, and they'll be synced to the /application/ directory on your server—we used this to sync a SQL file from ./mysql/example.sql to /application/example.sql on the server, which made it much easier to mass-import data.

Workarounds in this tutorial

The only two work-arounds we needed to make here were

  1. Starting Apache manually on the PHP service with service apache2 restart and
  2. Creating a user in MySQL that could access our data by running CREATE USER 'root'@'%' IDENTIFIED BY 'your_password';GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;.

Everything else was standard to what you would do on any LAMP stack.

Questions? Comments?

Hopefully this was helpful! If you have any questions, email us at support@bowery.io.

@obouchari
Copy link

Hi, The bowery idea is great and it will be very helpful for developers to provide a sample of a project or work remotely, but it seems like I'm having an issue with the server, when visiting the link provided by bowery connect I see nothing but a blank page! I followed all the steps. I don't know if I'm missing something here? Any idea what is exactly the problem? Thank you.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment