Setting Up MySQL

mysql

#1

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 more

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-ctl command 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.


Tutorials - Table of Contents
Password for root user of MySQL?
mySQL - bash workspace FAIL
Using Cloud9 on a shared hosting site?
Connecting to Microsoft SQL Server with PHP
#2

Will this path be of a local database?


#3

This will be the path of a local .sql file. For more information, please see How to import an SQL file using the command line in MySQL or Import MySQL dumpfile.


#4

I really hope you can help me.

I have tried several times to connect mysql to django. I have tried to use the settings you show for PHP as the database settings for django but I cannot connect to the mysql server.

Is there any guidance you can give me.

Thanks,
John Zehnder


#5

How do you get the PATH to your workspace root?


#6

For me, it worked to use ~/workspace. As in:

mysql> source ~/workspace/db-dumps/my_db_dump.sql


#7

Thanks Keith quicker todo. I did find out full path for anyone interested which is

/home/ubuntu/workspace/


#8

worked for me too…Thank you


#9

Hi, great tutorial. But I’m having a problem with the PHP application mysqli. Do you know how do I use this?


#10

Great Tutorial!
Thanks for all


#11

and if I already have a ready database? what I do?


#12

I’m having trouble importing a csv file, using ‘load data’. Error was:

mysql> load data infile “/home/ubuntu/workspace/sql/ord_bcast_values.csv” into table ord_bcast_values columns terminated by ‘,’ lines terminated by ‘\n’;
ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

I know about the ‘source’ method, but can this be done too?

I am unable to put the csv file into: /var/lib/mysql-files/ as defined by:
mysql> SHOW VARIABLES LIKE “secure_file_priv”;

Thank you.


#13

I am running $ mysql-ctl install, getting “mysql-ctl: command not found”.??


#14

Hi. I’ve been working on a PHP/MySQL app in C9. It runs fine on C9.
But I am having trouble with the database connection when I try to deploy the app to Heroku.
I followed the steps in Heroku’s Dev Center:


but when I open the app in Heroku (after pushing to Heroku) the app does not show the data.

I changed my database connection file from C9’s setup:
<?php
$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);
}


to Heroku’s Migration marketplace add-on. (I added a ClearDB database), so now my connection file looks like this:

<?php $url = parse_url(getenv("CLEARDB_DATABASE_URL")); $server = $url["us-cdbr-iron-east-03.cleardb.net"]; $username = $url["b5458e0da66d5b"]; $password = $url["cba72f57"]; $db = substr($url["heroku_f131178ef9fabf6"], 1); $con = new mysqli($server, $username, $password, $db)or die(mysql_error()); ?>

Do I need to include $port = 3306;?

thanks,
Simon


#16

Hi. I am setting up my workspace for the first time for use with MySQLdb. I get the following error on executing "import"
import MySQLdb
ImportError: No module named MySQLdb

I tried the following based on a recommendation, but I don’t have root permission to complete.
pip install MySQL-python

Any recommendation? Thank you in advance.