Setting up Mysql in c9

MySQL is the world's most widely used open source relational database management system (RDBMS) that runs as a server providing multi-user access to a number of databases.

MySQL's most common features

• A broad subset of ANSI SQL 99, as well as extensions


• Cross-platform support


• Stored procedures


• Triggers


• Cursors


• Updatable Views


• Information schema


• Many more192

Using MySQL with Cloud9

This article explains our first iteration of MySQL support in Cloud9. It makes it super easy to install, start and stop a MySQL instance right in your workspace. The nice thing is that every workspace will run a separate database so your projects will never interfere with each other. You can control MySQL with the mysql-ctlcommand line tool run from the terminal:

# start MySQL. Will create an empty database on first start
$ mysql-ctl start

# stop MySQL
$ mysql-ctl stop

# run the MySQL interactive shell
$ mysql-ctl cli

You can then connect to the database with following parameters:

• Hostname - $IP (The same local IP as the application you run on Cloud9)


• Port - 3306 (The default MySQL port number)


• User - $C9_USER (Your Cloud9 user name)


• Password - "" (No password since you can only access the DB from within the workspace)


• Database - c9 (The database username)

To verify your hostname, you can connect to the mysql cli and show the host by running the following commands:

mysql-ctl cli

Once connected to the mysql shell, run the following:

select @@hostname;

Importing data into your database

To import existing data into your database run following commands:

mysql-ctl cli

You are now in the MySQL environment and can start the import:

mysql> use c9
mysql> source PATH_TO_SQL_FILE.sql

To verify that everything got imported run:

mysql> show tables;

Connecting from PHP

So now you know how to create a database, start the DB server, and access it via a
command line tool. It's time for the real deal: accessing it from your code.

In this example, we will connect from PHP:

1. Create a new file and call it connect.php


2. Copy/paste the following code in there and save the file:

<?php
    // A simple PHP script demonstrating how to connect to MySQL.
    // Press the 'Run' button on the top to start the web server,
    // then click the URL that is emitted to the Output tab of the console.

    $servername = getenv('IP');
    $username = getenv('C9_USER');
    $password = "";
    $database = "c9";
    $dbport = 3306;

    // Create connection
    $db = new mysqli($servername, $username, $password, $database, $dbport);

    // Check connection
    if ($db->connect_error) {
        die("Connection failed: " . $db->connect_error);
    } 
    echo "Connected successfully (".$db->host_info.")";

After creating the file:
3. Run the code by right-clicking on the file name from the navigation pane then clicking 'Run'
4. The output pane (usually on the bottom of your screen) shows 'Starting Apache httpd...'
5. Click the link that is displayed after that (https://workspacename-username.c9.io/connect.php)
6. A preview pane will open, showing 'Connected successfully (0.0.0.0 via TCP/IP)'.

MySQL socket file can be found in ~/lib/mysql/socket/mysql.sock

Can I use my own MySQL credentials?

Yes, this is possible. You can log into MySQL by entering 'mysql-ctl cli' then run the following lines to add a new user:

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

Simply replace username / password in the code above with the username / password you want.