MySQL
Jump to navigation
Jump to search
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
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;
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
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
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
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>
Access mysql using perl
http://www.cyberciti.biz/faq/how-to-access-mysql-database-using-perl