MySQL

Installation issues

Fail to start

On Ubuntu 12.04, we can use

sudo dpkg-reconfigure mysql-server-5.5

Under Shell

Create a new user account to grant access to a database

http://www.cyberciti.biz/faq/mysql-user-creation/

NOTE: please be mindful when creating a user name. There is no protection against creating an old user's name. If you accidentally create a same user name as say mediawiki DB user, then mediawiki will not be working.

$ mysql -u root -p
OR
$ mysql -u root -h myserver-sever.com -p

Create a new mysql database called demo;

mysql> CREATE DATABASE testdb;

Create a new user called user1 for database demo

mysql> GRANT ALL ON testdb.* TO user1@localhost IDENTIFIED BY 'mypassword';

User user1 can connect to mysql server demo database using following command:

$ mysql -u user1 -p testdb
OR
$ mysql -u user1 -h mysql.server.com -p testdb

MYSQL commands

http://www.pantz.org/software/mysql/mysqlcommands.html

Find out about your MySQL version and all installed plugins:

mysql> select version();
mysql> show plugins;

Default database location

On debian, it is /var/lib/mysql.

phpMyAdmin

http://localhost/phpmyadmin with default username: root

See http://tuxtweaks.com/2011/10/install-lamp-and-phpmyadmin-on-ubuntu-11-10/2/

Create a database 'demo' and a table 'employee'

http://www.thegeekstuff.com/2011/10/mysql-tutorial-basics/

mysql -u guest -p demo
show databases;
use demo;
show tables;
create table employee .............;
desc employee;
insert into employee .............;
select * from employee;

Access mysql using perl

http://www.cyberciti.biz/faq/how-to-access-mysql-database-using-perl

Use through R

Examples from r-bloggers

• http://www.r-bloggers.com/mysql-and-r/
• http://www.r-bloggers.com/accessing-mysql-through-r/
• http://gettinggeneticsdone.blogspot.com/2011/05/accessing-databases-from-r-rstats-sql.html
• useR Vignette from Jeffrey Breen
• Phil Spector

Installation

• http://cran.r-project.org/web/packages/RMySQL/INSTALL

Ubuntu/Debian

First, log in using root.

install.packages("DBI")

Go to shell and

apt-get install libdbd-mysql libmysqlclient16-dev       (not sure if this is necessary)
http://cran.r-project.org/src/contrib/RMySQL_0.9-3.tar.gz
R CMD INSTALL RMySQL*.tar.gz

Windows

Check my note with complete screenshots at File:Install MySQL on Windows.pdf.

Open and close connection: dbConnect/dbDisconnect

con <- dbConnect(MySQL(),
         user="me", password="nuts2u",
         dbname="my_db", host="localhost")
on.exit(dbDisconnect(con))

List tables and fields: dbListTables and dbListFields

dbListTables(mydb)
dbListFields(mydb, 'some_table')

Read and write entire tables: dbReadTable and dbWriteTable

We can create tables in the database using R dataframes.

df = dbReadTable(con, 'motortrend')
dbWriteTable(mydb, name='table_name', value=data.frame.name)

Query: dbGetQuery and dbSendQuery

You can process query results row by row, in blocks or all at once. The highly useful function dbGetQuery(con, sql) returns all query results as a data frame. With dbSendQuery, you can get all or partial results with fetch.

con <- dbConnect(MySQL(), user="network_portal", password="monkey2us", dbname=db.name, host="localhost")
rs <- dbSendQuery(con, "select name from genes limit 10;") # results in in mysql
data <- fetch(rs, n=10) # return result to R as a data frame; use n=-1 to retrieve all pending records.
huh <- dbHasCompleted(rs)
dbClearResult(rs)
dbDisconnect(con)

Aggregate and Sort

df = dbGetQuery(con, "SELECT mfg, avg(hp) AS meanHP FROM motortrend GROUP BY mfg ORDER BY meanHP DESC")
df = dbGetQuery(con, "SELECT cyl as cylinders, avg(hp) as meanHP FROM motortrend GROUP by cyl ORDER BY cyl")

dbApply()

sql = "SELECT cyl, hp FROM motortrend ORDER BY cyl"
rs = dbSendQuery(con, sql)
dbApply(rs, INDEX='cyl', FUN=function(x, grp) quantile(x$hp))

Retrieving AUTO_INCREMENT IDs

• http://www.w3schools.com/sql/sql_autoincrement.asp

create.network <- function(species.id, network.name, data.source, description) {
  
  con <- dbConnect(MySQL(),
           user="super_schmuck", password="nuts2u",
           dbname="my_db", host="localhost")
  on.exit(dbDisconnect(con))

  sql <- sprintf("insert into networks
                  (species_id, name, data_source, description, created_at)
                  values (%d, '%s', '%s', '%s', NOW());",
                 species.id, network.name, data.source, description)
  rs <- dbSendQuery(con, sql)
  dbClearResult(rs)

  id <- dbGetQuery(con, "select last_insert_id();")[1,1]

  return(id)
}

Full example from Jeffrey Breen

http://www.r-bloggers.com/slides-%E2%80%9Caccessing-databases-from-r%E2%80%9D-rstats/

First, create new database & user in MySQL:

mysql> create database testrdb;
mysql> grant all privileges on testrdb.* to 'testuser'@'localhost' identified by 'testpass';
mysql> flush privileges;

In R, load the “mtcars” data.frame, clean it up, and write it to a new “motortrend” table:

library(stringr)
library(RMySQL)
 
data(mtcars)
 
# car name is data.frame's rownames. Let's split into manufacturer and model columns:
mtcars$mfg = str_split_fixed(rownames(mtcars), ' ', 2)[,1]
mtcars$mfg[mtcars$mfg=='Merc'] = 'Mercedes'
mtcars$model = str_split_fixed(rownames(mtcars), ' ', 2)[,2]
 
# connect to local MySQL database (host='localhost' by default)
con = dbConnect("MySQL", "testrdb", username="testuser", password="testpass")
 
dbWriteTable(con, 'motortrend', mtcars)
 
dbDisconnect(con)

And check the database from mysql

mli@PhenomIIx6:~$ mysql -u testuser -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 153
Server version: 5.1.63-0ubuntu0.11.10.1 (Ubuntu)

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| testrdb            |
+--------------------+
2 rows in set (0.00 sec)

mysql> use testrdb;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+-------------------+
| Tables_in_testrdb |
+-------------------+
| motortrend        |
+-------------------+
1 row in set (0.00 sec)

mysql> describe motortrend;
+-----------+--------+------+-----+---------+-------+
| Field     | Type   | Null | Key | Default | Extra |
+-----------+--------+------+-----+---------+-------+
| row_names | text   | YES  |     | NULL    |       |
| mpg       | double | YES  |     | NULL    |       |
| cyl       | double | YES  |     | NULL    |       |
| disp      | double | YES  |     | NULL    |       |
| hp        | double | YES  |     | NULL    |       |
| drat      | double | YES  |     | NULL    |       |
| wt        | double | YES  |     | NULL    |       |
| qsec      | double | YES  |     | NULL    |       |
| vs        | double | YES  |     | NULL    |       |
| am        | double | YES  |     | NULL    |       |
| gear      | double | YES  |     | NULL    |       |
| carb      | double | YES  |     | NULL    |       |
| mfg       | text   | YES  |     | NULL    |       |
| model     | text   | YES  |     | NULL    |       |
+-----------+--------+------+-----+---------+-------+
14 rows in set (0.01 sec)

mysql> SELECT * FROM motortrend WHERE row_names like "Mazda%" order by mpg;
+---------------+------+------+------+------+------+-------+-------+------+------+------+------+-------+---------+
| row_names     | mpg  | cyl  | disp | hp   | drat | wt    | qsec  | vs   | am   | gear | carb | mfg   | model   |
+---------------+------+------+------+------+------+-------+-------+------+------+------+------+-------+---------+
| Mazda RX4     |   21 |    6 |  160 |  110 |  3.9 |  2.62 | 16.46 |    0 |    1 |    4 |    4 | Mazda | RX4     |
| Mazda RX4 Wag |   21 |    6 |  160 |  110 |  3.9 | 2.875 | 17.02 |    0 |    1 |    4 |    4 | Mazda | RX4 Wag |
+---------------+------+------+------+------+------+-------+-------+------+------+------+------+-------+---------+
2 rows in set (0.00 sec)
mysql> SELECT row_names, wt, model  FROM motortrend WHERE row_names like "Mazda%" order by wt;
+---------------+-------+---------+
| row_names     | wt    | model   |
+---------------+-------+---------+
| Mazda RX4     |  2.62 | RX4     |
| Mazda RX4 Wag | 2.875 | RX4 Wag |
+---------------+-------+---------+
2 rows in set (0.00 sec)

mysql>

Find database online

• http://www.inside-r.org/howto/finding-data-internet
• http://www.r-bloggers.com/list-of-public-databases-from-the-washington-post/