MySQL: Difference between revisions

Revision as of 15:12, 25 December 2017

Installation issues

Fail to start

On Ubuntu 12.04, we can use

sudo dpkg-reconfigure mysql-server-5.5

InnoDB vs MyISAM

The default is InnoDB.

Under Shell

Manual/Documentation

https://dev.mysql.com/doc/refman

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';

If we quit mysql and log in again using the new account, the 'SHOW DATABASES' will only show databases that the new account can access

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

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

Show all users

SELECT User, Host, Password FROM mysql.user; 

SELECT DISTINCT User FROM mysql.user;

Show the privileges of a user

mysql> SHOW GRANTS FOR 'user1'@'localhost';
+--------------------------------------------------------------------------------------------------------------+
| Grants for user1@localhost                                                                                   |
+--------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'user1'@'localhost' IDENTIFIED BY PASSWORD '*FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF' |
| GRANT ALL PRIVILEGES ON `testdb`.* TO 'user1'@'localhost'                                                    |
+--------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

Another way is through phpMyAdmin. Click on 'Databases' and in the Action column click 'Check Privileges' in the corresponding row of the desired database.

Remove an account

DROP USER 'jeffrey'@'localhost';

Remove a database

mysql> DROP DATABASE db_name;
mysql> SHOW DATABASES;

Show the current user

mysql> SELECT USER();

Show the current database

mysql> SELECT DATABASE();

MYSQL commands

Find out about your MySQL version and all installed plugins:

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

Executing SQL Statements from a Text File

https://dev.mysql.com/doc/refman/5.7/en/mysql-batch-commands.html

$ mysql db_name

$ mysql -h host -u user -p db_name # db_name is not a password !

$ mysql -h host -u user -pPASSWORD db_name # no space after "-p"

$ mysql db_name < text_file # text_file that contains the statements you wish to execute/Batch mode
$ mysql db_name < text_file > output.txt
mysql> source file_name

Create a table

Note that a database have multiple tables.

mysql> CREATE DATABASE testdb;
mysql> USE testdb
mysql> CREATE TABLE pet (name VARCHAR(20), owner VARCHAR(20),
    -> species VARCHAR(20), sex CHAR(1), birth DATE, death DATE);

OR put it in a text file

mysql> CREATE TABLE pet
{
    name VARCHAR(20),
    owner VARCHAR(20),
    species VARCHAR(20), 
    sex CHAR(1), 
    birth DATE, 
    death DATE
}

Show all tables.

mysql> SHOW TABLES;

Verify your table

mysql> DESCRIBE pet;

Loading data into a table

https://dev.mysql.com/doc/refman/5.7/en/loading-tables.html

mysql> LOAD DATA LOCAL INFILE '/path/pet.txt' INTO TABLE pet;

OR using the mysqlimport utility from the shell

$ mysqlimport --local pet /path/pet.txt

You could add a new record using an INSERT statement like this:

mysql> INSERT INTO pet
    -> VALUES ('Puffball','Diane','hamster','f','1999-03-30',NULL);

Retrieve data

https://dev.mysql.com/doc/refman/5.7/en/retrieving-data.html General form

SELECT what_to_select
FROM which_table
WHERE conditions_to_satisfy;

Some examples

mysql> SELECT * FROM pet;

mysql> UPDATE pet SET birth = '1989-08-31' WHERE name = 'Bowser';

mysql> SELECT * FROM pet WHERE name = 'Bowser';

mysql> SELECT * FROM pet WHERE birth >= '1998-1-1';

mysql> SELECT * FROM pet WHERE species = 'dog' AND sex = 'f';

mysql> SELECT * FROM pet WHERE (species = 'cat' AND sex = 'm')
    -> OR (species = 'dog' AND sex = 'f');

mysql> SELECT name, birth FROM pet;

mysql> SELECT DISTINCT owner FROM pet;

mysql> SELECT name, birth FROM pet ORDER BY birth;

mysql> SELECT name, birth FROM pet ORDER BY birth DESC;  # To sort in reverse (descending) order

mysql> SELECT name, birth FROM pet WHERE MONTH(birth) = 5;

Pattern match

mysql> SELECT * FROM pet WHERE name LIKE 'b%'; # find names beginning with b, case-insensitive

mysql> SELECT * FROM pet WHERE name LIKE '%fy'; # find names ending with fy:

mysql> SELECT * FROM pet WHERE name LIKE '%w%'; # find names containing a w:

Count rows

mysql> SELECT COUNT(*) FROM pet;

mysql> SELECT COUNT(DISTINCT owner) FROM pet;

mysql> SELECT owner, COUNT(*) FROM pet GROUP BY owner;

Retrieve information from multiple tables https://dev.mysql.com/doc/refman/5.7/en/multiple-tables.html

# suppose we have two tables event & score; each has a column called event_id
# score table has columns: name, event_id, score
# event table has columns: event_id, date, type
mysql> SELECT student_id, date, score, type 
    -> FROM event, score
    -> WHERE date = "1999-09-20"
    -> AND event.event_id = score.event_id;

Mysql list tables and sizes - order by size

https://stackoverflow.com/questions/14569940/mysql-list-tables-and-sizes-order-by-size

SELECT TABLE_NAME, table_rows, data_length, index_length, 
    round(((data_length + index_length) / 1024 / 1024),2) "Size in MB"
    FROM information_schema.TABLES WHERE table_schema = "schema_name"
    ORDER BY (data_length + index_length) DESC;

Note: replace "schema_name" with your database name.

Find first and last record from mysql table

https://stackoverflow.com/questions/2735395/how-to-find-first-and-last-record-from-mysql-table

# To get the first record:
select col1 from tab1 order by col1 asc limit 1;   

# To get the last record:
select col1 from tab1 order by col1 desc  limit 1;

Write/save a table/query to a text file

# Method 1. mysql. Get a permission denied error 
$ mysql -u root -p
> SELECT * FROM tbl_name INTO OUTFILE '/tmp/orders.txt';

# Method 2. shell. Simple solution; does not need to change FILE privileges.
# https://stackoverflow.com/questions/9462416/shell-one-line-query
mysql -u root -pmy_password -h ip_add --port=13306 -D DATABASENAME -e "SELECT * FROM tbl_name;" > output.txt

Default database location

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

How to Backup/export and load/import a Single Table from a MySQL Database

# Export
$ mysqldump -u USERNAME --password="PW" DB_NAME > sql_backup_file

# Import
$ mysql -u UserName -p Password -h Hostname DB_NAME < sql_backup_file

Note that

the back up file is a text file.
the user information will not be retained in the backup file.
the original database name is saved in the backup file
the database name DB_NAME in importing is required. The database name does not to be the same as the original. However the database DB_NAME has to be created beforehand (mysql> CREATE DATABASE DB_NAME2). If the database has not existed or we omit the DB_NAME in importing, we will get an error

ERROR 1046 (3D000) at line 22: No database selected

# or
ERROR 1049 (42000): Unknown database 'testdb'

phpMyAdmin

http://localhost/phpmyadmin with default username: root and the admin password you have chosen during the installation of phpMyAdmin.

phpMyAdmin is installed under /usr/share/phpmyadmin.

Note that index.php file does not appear in /var/www/html directory. Why? A standard Apache Alias pointing every request starting with /phpmyadmin to the phpMyAdmin installation directory. See Setting up and securing a phpMyAdmin install on Ubuntu 10.04

udooer@udoo:~$ ls -l /var/www/html
total 12
-rw-r--r-- 1 root root 11510 Dec 24 13:44 index.html
lrwxrwxrwx 1 root root    18 Dec 24 14:00 mediawiki -> /var/lib/mediawiki

udooer@udoo:~$ ls -lah /etc/apache2/
total 88K
drwxr-xr-x   8 root root 4.0K Dec 24 13:44 .
drwxr-xr-x 121 root root 4.0K Dec 25 08:15 ..
-rw-r--r--   1 root root 7.0K Jan  7  2014 apache2.conf
drwxr-xr-x   2 root root 4.0K Dec 25 08:15 conf-available
drwxr-xr-x   2 root root 4.0K Dec 25 08:15 conf-enabled
-rw-r--r--   1 root root 1.8K Jan  3  2014 envvars
-rw-r--r--   1 root root  31K Jan  3  2014 magic
drwxr-xr-x   2 root root  12K Dec 24 13:45 mods-available
drwxr-xr-x   2 root root 4.0K Dec 24 13:45 mods-enabled
-rw-r--r--   1 root root  320 Jan  7  2014 ports.conf
drwxr-xr-x   2 root root 4.0K Dec 24 13:44 sites-available
drwxr-xr-x   2 root root 4.0K Dec 24 13:44 sites-enabled

udooer@udoo:~$ ls -laH /etc/apache2/sites-available/
total 20
drwxr-xr-x 2 root root 4096 Dec 24 13:44 .
drwxr-xr-x 8 root root 4096 Dec 24 13:44 ..
-rw-r--r-- 1 root root 1332 Jan  7  2014 000-default.conf
-rw-r--r-- 1 root root 6437 Jan  7  2014 default-ssl.conf

udooer@udoo:~$ ls -lah /etc/apache2/conf-enabled/
total 8.0K
drwxr-xr-x 2 root root 4.0K Dec 25 08:15 .
drwxr-xr-x 8 root root 4.0K Dec 24 13:44 ..
lrwxrwxrwx 1 root root   30 Dec 24 13:44 charset.conf -> ../conf-available/charset.conf
lrwxrwxrwx 1 root root   40 Dec 25 08:13 javascript-common.conf -> ../conf-available/javascript-common.conf
lrwxrwxrwx 1 root root   44 Dec 24 13:44 localized-error-pages.conf -> ../conf-available/localized-error-pages.conf
lrwxrwxrwx 1 root root   46 Dec 24 13:44 other-vhosts-access-log.conf -> ../conf-available/other-vhosts-access-log.conf
lrwxrwxrwx 1 root root   33 Dec 25 08:15 phpmyadmin.conf -> ../conf-available/phpmyadmin.conf
lrwxrwxrwx 1 root root   31 Dec 24 13:44 security.conf -> ../conf-available/security.conf
lrwxrwxrwx 1 root root   36 Dec 24 13:44 serve-cgi-bin.conf -> ../conf-available/serve-cgi-bin.conf

udooer@udoo:~$ cat /etc/apache2/conf-available/phpmyadmin.conf 
# phpMyAdmin default Apache configuration

Alias /phpmyadmin /usr/share/phpmyadmin

<Directory /usr/share/phpmyadmin>
	Options FollowSymLinks
	DirectoryIndex index.php

	<IfModule mod_php5.c>
		AddType application/x-httpd-php .php

		php_flag magic_quotes_gpc Off
		php_flag track_vars On
		php_flag register_globals Off
		php_admin_flag allow_url_fopen Off
		php_value include_path .
		php_admin_value upload_tmp_dir /var/lib/phpmyadmin/tmp
		php_admin_value open_basedir /usr/share/phpmyadmin/:/etc/phpmyadmin/:/var/lib/phpmyadmin/:/usr/share/php/php-gettext/:/usr/share/javascript/
	</IfModule>

</Directory>

If we want to change the URL or the port number of phpMyAdmin, follow this.

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

Installation

http://cran.r-project.org/web/packages/RMySQL/
https://db.rstudio.com/my-sql/ (more instructions)

Ubuntu/Debian

First, log in using root.

install.packages("DBI")

Go to shell and

# sudo apt-get install libdbd-mysql (this line seems not necessary)
sudo apt-get install libmysqlclient-dev       
# R
# install.packages("RMySQL")

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))

dbListTables(con)
head(dbReadTable(con, "recentchanges"))

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>

@@ Line 278: / Line 278: @@
 <syntaxhighlight lang='bash'>
+# Export
 $ mysqldump -u USERNAME --password="PW" DB_NAME > sql_backup_file
+# Import
 $ mysql -u UserName -p Password -h Hostname DB_NAME < sql_backup_file
 </syntaxhighlight>
+Note that
+# the back up file is a text file.
+# the user information will not be retained in the backup file.
+# the original database name is saved in the backup file
+# the database name ''DB_NAME'' in importing is required. The database name does not to be the same as the original. However the database ''DB_NAME'' has to be created beforehand (mysql> CREATE DATABASE DB_NAME2). If the database has not existed or we omit the DB_NAME in importing, we will get an error
+<pre>
+ERROR 1046 (3D000) at line 22: No database selected
+# or
+ERROR 1049 (42000): Unknown database 'testdb'
+</pre>
 === phpMyAdmin ===