MySQL: Difference between revisions

From 太極
Jump to navigation Jump to search
(Replaced content with "= Installation issues = == Fail to start == On Ubuntu 12.04, we can use <pre> sudo dpkg-reconfigure mysql-server-5.5 </pre> == InnoDB vs MyISAM == The default is InnoDB....")
Tag: Replaced
Line 1: Line 1:
== Installation issues ==
= Installation issues =
=== Fail to start ===
== Fail to start ==
On Ubuntu 12.04, we can use  
On Ubuntu 12.04, we can use  
<pre>
<pre>
Line 6: Line 6:
</pre>
</pre>


=== InnoDB vs MyISAM ===
== InnoDB vs MyISAM ==
The default is InnoDB.
The default is InnoDB.


== Under Shell ==  
= Under Shell =  
=== Manual/Documentation ===
== Manual/Documentation ==
https://dev.mysql.com/doc/refman
https://dev.mysql.com/doc/refman


=== Installation and setup root password ===
== Installation and setup root password ==
[https://itsfoss.com/install-mysql-ubuntu/ How to Install MySQL in Ubuntu Linux] (18.04)
[https://itsfoss.com/install-mysql-ubuntu/ How to Install MySQL in Ubuntu Linux] (18.04)


Line 35: Line 35:
+--------------------+
+--------------------+
| information_schema |
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
3 rows in set (0.02 sec)
MariaDB [(none)]> exit
Bye
</pre>
==== Reset root password ====
https://www.digitalocean.com/community/tutorials/how-to-reset-your-mysql-or-mariadb-root-password
=== How do I turn off the mysql password validation? ===
Normally a password as short as 3 characters is enough for the root user.
* https://stackoverflow.com/questions/36301100/how-do-i-turn-off-the-mysql-password-validation
=== 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.
<pre>
$ mysql -u root -p
OR
$ mysql -u root -h myserver-sever.com -p
</pre>
Create a new mysql database called demo;
<pre>
mysql> CREATE DATABASE testdb;
</pre>
Create a new user called user1 for database demo
<pre>
mysql> GRANT ALL ON testdb.* TO user1@localhost IDENTIFIED BY 'mypassword';
</pre>
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
<pre>
mysql> show databases;
+--------------------+
| Database          |
+--------------------+
| information_schema |
| testdb            |
+--------------------+
2 rows in set (0.00 sec)
</pre>
User user1 can connect to mysql server demo database using following command:
<pre>
$ mysql -u user1 -p testdb
OR
$ mysql -u user1 -h mysql.server.com -p testdb
</pre>
=== Show all users ===
<pre>
SELECT User, Host, Password FROM mysql.user;
SELECT DISTINCT User FROM mysql.user;
</pre>
=== [https://dev.mysql.com/doc/refman/5.7/en/show-grants.html Show the privileges of a user] ===
<pre>
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)
</pre>
Another way is through '''phpMyAdmin'''. Click on 'Databases' and in the Action column click 'Check Privileges' in the corresponding row of the desired database.
=== [https://dev.mysql.com/doc/refman/5.7/en/drop-user.html Remove an account] ===
<pre>
DROP USER 'jeffrey'@'localhost';
</pre>
=== [https://dev.mysql.com/doc/refman/5.7/en/drop-database.html Remove a database] ===
<syntaxhighlight lang='sql'>
mysql> DROP DATABASE db_name;
mysql> SHOW DATABASES;
</syntaxhighlight>
=== Show the current user ===
<syntaxhighlight lang='sql'>
mysql> SELECT USER();
</syntaxhighlight>
=== Show the current database ===
<syntaxhighlight lang='sql'>
mysql> SELECT DATABASE();
</syntaxhighlight>
Note on MariaDB it shows the current database.
<syntaxhighlight lang='sql'>
MariaDB [(none)]> use mysql
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
MariaDB [mysql]>
</syntaxhighlight>
=== How do you deselect MySQL database? ===
https://stackoverflow.com/questions/27895328/how-do-you-deselect-mysql-database. Ans: You just select another database and USE it.
=== Version vs Distrib number of MySQL ===
https://stackoverflow.com/questions/8645184/version-vs-distrib-number-of-mysql
* '''Ver''' refers to the version of the mysql command line client - what you are envoking by typing 'mysql'
* '''Distrib''' refers to the mysql server version your client was built with. This is not to be confused with the mysql server you are connected to, which can be obtained with SELECT VERSION();
On Ubuntu 14.04
<pre>
$ mysql --version
mysql  Ver 14.14 Distrib 5.5.58, for debian-linux-gnu (x86_64) using readline 6.3
$ mysql -u root -p -e 'select version()'
Enter password:
+-------------------------+
| version()              |
+-------------------------+
| 5.5.58-0ubuntu0.14.04.1 |
+-------------------------+
</pre>
On Docker's MySQL
<pre>
# mysql --version
mysql  Ver 14.14 Distrib 5.7.20, for Linux (x86_64) using  EditLine wrapper
# mysql --user=root --password=$MYSQL_ROOT_PASSWORD -e 'select version()'
mysql: [Warning] Using a password on the command line interface can be insecure.
+-----------+
| version() |
+-----------+
| 5.7.20    |
+-----------+
</pre>
=== MYSQL commands ===
* [https://dev.mysql.com/doc/refman/5.7/en/mysql-command-options.html mysql command line options]
* http://www.pantz.org/software/mysql/mysqlcommands.html
Find out about your MySQL version and all installed [http://www.howtoforge.com/how-to-install-mysql-5.6-on-ubuntu-12.10-including-memcached-plugin-p2 plugins]:
<pre>
mysql> select version();
mysql> show plugins;
</pre>
=== Executing SQL Statements from a Text File ===
https://dev.mysql.com/doc/refman/5.7/en/mysql-batch-commands.html
<pre>
$ 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
</pre>
Note when I test it on raspbian, I get errors ''Access denied for user 'testuser'@'localhost' (using password: YES)''.
It works after I follow these 2 suggestions
* https://stackoverflow.com/questions/6091427/mysql-into-outfile-access-denied-but-my-user-has-all-access-and-the-fold '''GRANT FILE ON *.* TO 'asdfsdf'@'localhost';'''
* https://www.raspberrypi.org/forums/viewtopic.php?p=1326803 Creating a new directory and change directory attributes '''mkdir /tmp/data; chmod 777 /tmp/data'''
Following the exercise [[#How_does_importing_a_database_affect_the_current_database.3F|here]],
<syntaxhighlight lang='bash'>
$ mysql -u testuser -ptestpass -e \
  "Use testrdb; SELECT * FROM motortrend WHERE model = 'RX4' INTO OUTFILE '/tmp/data/rx4.csv'"
$ cat /tmp/data/rx4.csv
Mazda RX4 21 6 160 110 3.9 2.62 16.46 0 1 4 4 Mazda RX4
newrow 21 \N \N \N \N \N \N \N \N \N \N \N RX4
</syntaxhighlight>
=== Create a table ===
Note that a database have multiple tables.
* https://dev.mysql.com/doc/refman/5.7/en/creating-database.html
* https://dev.mysql.com/doc/refman/5.7/en/creating-tables.html
<syntaxhighlight lang='sql'>
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);
</syntaxhighlight>
OR put it in a text file
<syntaxhighlight lang='sql'>
mysql> CREATE TABLE pet
{
    name VARCHAR(20),
    owner VARCHAR(20),
    species VARCHAR(20),
    sex CHAR(1),
    birth DATE,
    death DATE
}
</syntaxhighlight>
Show all tables.
<syntaxhighlight lang='sql'>
mysql> SHOW TABLES;
</syntaxhighlight>
Verify your table
<syntaxhighlight lang='sql'>
mysql> DESCRIBE pet;
</syntaxhighlight>
=== Loading data into a table ===
https://dev.mysql.com/doc/refman/5.7/en/loading-tables.html
<syntaxhighlight lang='sql'>
mysql> LOAD DATA LOCAL INFILE '/path/pet.txt' INTO TABLE pet;
</syntaxhighlight>
OR using the '''mysqlimport''' utility from the shell
<syntaxhighlight lang='bash'>
$ mysqlimport --local pet /path/pet.txt
</syntaxhighlight>
You could add a new record using an '''INSERT''' statement like this:
<syntaxhighlight lang='sql'>
mysql> INSERT INTO pet
    -> VALUES ('Puffball','Diane','hamster','f','1999-03-30',NULL);
</syntaxhighlight>
=== Retrieve data ===
https://dev.mysql.com/doc/refman/5.7/en/retrieving-data.html
General form
<syntaxhighlight lang='sql'>
SELECT what_to_select
FROM which_table
WHERE conditions_to_satisfy;
</syntaxhighlight>
Some examples
<syntaxhighlight lang='sql'>
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;
</syntaxhighlight>
[https://dev.mysql.com/doc/refman/5.7/en/pattern-matching.html Pattern match]
<syntaxhighlight lang='sql'>
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:
</syntaxhighlight>
Count rows
<syntaxhighlight lang='sql'>
mysql> SELECT COUNT(*) FROM pet;
mysql> SELECT COUNT(DISTINCT owner) FROM pet;
mysql> SELECT owner, COUNT(*) FROM pet GROUP BY owner;
</syntaxhighlight>
Retrieve information from multiple tables
https://dev.mysql.com/doc/refman/5.7/en/multiple-tables.html
<syntaxhighlight lang='sql'>
# 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;
</syntaxhighlight>
=== Mysql list tables and sizes - order by size ===
https://stackoverflow.com/questions/14569940/mysql-list-tables-and-sizes-order-by-size
<syntaxhighlight lang='sql'>
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;
</syntaxhighlight>
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
<syntaxhighlight lang='sql'>
# 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;
</syntaxhighlight>
=== Write/save a table/query to a text file ===
* http://zetcode.com/databases/mysqltutorial/exportimport/
* https://stackoverflow.com/questions/21253704/how-to-save-mysql-query-output-to-excel-or-txt-file
* https://stackoverflow.com/questions/6091427/mysql-into-outfile-access-denied-but-my-user-has-all-access-and-the-fold
<syntaxhighlight lang='bash'>
# 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
</syntaxhighlight>
=== Create a database 'demo' and a table 'employee' ===
http://www.thegeekstuff.com/2011/10/mysql-tutorial-basics/
<pre>
mysql -u guest -p demo
show databases;
use demo;
show tables;
create table employee .............;
desc employee;
insert into employee .............;
select * from employee;
</pre>
=== Access mysql using perl ===
http://www.cyberciti.biz/faq/how-to-access-mysql-database-using-perl
=== How to See Which MySQL Tables are Taking the Most Space ===
https://www.howtogeekpro.com/166/how-to-see-which-mysql-tables-are-taking-the-most-space/
== Administration ==
=== Default port ===
3306
=== phpMyAdmin ===
http://localhost/phpmyadmin with default username: root and the admin password you have chosen during the installation of phpMyAdmin.
* https://www.digitalocean.com/community/tutorials/how-to-install-and-secure-phpmyadmin-on-ubuntu-16-04
* http://tuxtweaks.com/2011/10/install-lamp-and-phpmyadmin-on-ubuntu-11-10/2/
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 [http://paynedigital.com/articles/2011/09/setting-up-and-securing-a-phpmyadmin-install-on-ubuntu-10-04 Setting up and securing a phpMyAdmin install on Ubuntu 10.04]
<pre>
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>
</pre>
If we want to change the URL or the port number of phpMyAdmin, follow [https://askubuntu.com/questions/969417/change-phpmyadmin-url-through-etc-apache2-conf-available-phpmyadmin-conf this].
=== Configuration file ===
[https://severalnines.com/blog/mysql-docker-containers-understanding-basics MySQL Docker Containers: Understanding the basics]
This can be found by using '''mysql --help''' or '''mysqld --help --verbose'''. On Ubuntu 16.04, it shows
<syntaxhighlight lang='bash'>
$ mysql --help
...
Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf ~/.my.cnf
...
$ tree /etc/mysql/
/etc/mysql/
├── conf.d
│   ├── mysql.cnf
│   └── mysqldump.cnf
├── debian.cnf
├── debian-start
├── my.cnf -> /etc/alternatives/my.cnf
├── my.cnf.fallback
├── mysql.cnf
└── mysql.conf.d
    ├── mysqld.cnf
    └── mysqld_safe_syslog.cnf
2 directories, 9 files
</syntaxhighlight>
=== Default database location ===
On Debian/Ubuntu, it is '''/var/lib/mysql'''.
<syntaxhighlight lang='bash'>
$ mysql -uroot -p -e 'SHOW VARIABLES WHERE Variable_Name = "datadir"'
</syntaxhighlight>
OR
<syntaxhighlight lang='mysql'>
mysql> select @@datadir;
</syntaxhighlight>
=== Change data location ===
https://stackoverflow.com/questions/1795176/how-to-change-mysql-data-directory
# sudo /etc/init.d/mysql stop
# sudo cp -R -p /var/lib/mysql /newpath
# sudo gedit /etc/mysql/my.cnf
# ''Look for the entry for datadir, and change the path (which should be /var/lib/mysql) to the new data directory.''
# sudo gedit /etc/apparmor.d/usr.sbin.mysqld
# ''Look for lines beginning with /var/lib/mysql. Change /var/lib/mysql in the lines with the new path.''
# sudo /etc/init.d/apparmor reload
# sudo /etc/init.d/mysql restart
=== How to backup/export and load/import a single database and/or table from a MySQL database ===
* https://dev.mysql.com/doc/refman/5.7/en/mysqldump.html
* https://www.howtogeekpro.com/157/how-to-backup-export-a-single-table-from-a-mysql-database/
* https://stackoverflow.com/questions/4546778/how-can-i-import-a-database-with-mysql-from-terminal
* [https://www.howtogeekpro.com/160/how-to-exclude-tables-from-mysql-exports-using-mysqldump/ How to Exclude Tables from MySQL Exports Using mysqldump]
<syntaxhighlight lang='bash'>
# Export
# Method 1: directly
$ mysqldump -u USERNAME --password="PW" DB_NAME > backup.sql
# Method 2: gzip
$ mysqldump -u USERNAME --password="PW" DB_NAME | gzip --best --verbose > backup.sql.gz
# Import
# Method 1
$ mysql -u UserName -p Password -h Hostname DB_NAME < backup.sql
# Method 2
$ mysqlimport -u UserName -p Password backup.sql
</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>
=== Move the MySQL data directory ===
* https://askubuntu.com/questions/137424/how-do-i-move-the-mysql-data-directory
* [https://www.digitalocean.com/community/tutorials/how-to-move-a-mysql-data-directory-to-a-new-location-on-ubuntu-16-04 How To Move a MySQL Data Directory to a New Location on Ubuntu 16.04]
==  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
* [http://files.meetup.com/1781511/useR%20Vignette%20-%20Accessing%20Databases%20from%20R%20-%2020110504.pdf useR Vignette from Jeffrey Breen]
* [http://www.stat.berkeley.edu/~spector/sql.pdf Phil Spector]
=== Installation ===
* http://cran.r-project.org/web/packages/RMySQL/
* https://db.rstudio.com/my-sql/ (more instructions)
==== Ubuntu/Debian ====
First, log in using root.
<pre>
install.packages("DBI")
</pre>
Go to shell and
<syntaxhighlight lang='rsplus'>
# sudo apt-get install libdbd-mysql (this line seems not necessary)
# sudo apt-get install libmysqlclient-dev (Ubuntu 14)     
sudo apt-get install -y libmariadb-dev (Debian Stretch)
# R
# install.packages("RMySQL")
</syntaxhighlight>
==== Windows ====
Check my note with complete screenshots at [[File:Install MySQL on Windows.pdf]].
=== Open and close connection: dbConnect/dbDisconnect ===
<syntaxhighlight lang='rsplus'>
con <- dbConnect(MySQL(), user="me", password="nuts2u", dbname="my_db", host="localhost")
on.exit(dbDisconnect(con))
dbListTables(con)
head(dbReadTable(con, "recentchanges"))
</syntaxhighlight>
=== List tables and fields: dbListTables and dbListFields ===
<pre>
dbListTables(mydb)
dbListFields(mydb, 'some_table')
</pre>
=== Read and write entire tables: dbReadTable and dbWriteTable ===
We can create tables in the database using R dataframes.
<pre>
df = dbReadTable(con, 'motortrend')
dbWriteTable(mydb, name='table_name', value=data.frame.name)
</pre>
=== 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.
<pre>
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)
</pre>
=== Aggregate and Sort ===
<pre>
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")
</pre>
=== dbApply() ===
<pre>
sql = "SELECT cyl, hp FROM motortrend ORDER BY cyl"
rs = dbSendQuery(con, sql)
dbApply(rs, INDEX='cyl', FUN=function(x, grp) quantile(x$hp))
</pre>
=== Retrieving AUTO_INCREMENT IDs ===
* http://www.w3schools.com/sql/sql_autoincrement.asp
<pre>
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)
}
</pre>
=== 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:
<syntaxhighlight lang='mysql'>
mysql> create database testrdb;
mysql> grant all privileges on testrdb.* to 'testuser'@'localhost' identified by 'testpass';
mysql> flush privileges;
</syntaxhighlight>
In R, load the “mtcars” data.frame, clean it up, and write it to a new “motortrend” table:
<syntaxhighlight lang='rsplus'>
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]
> dim(mtcars)
[1] 32 13
> mtcars[1:3,]
              mpg cyl disp  hp drat    wt  qsec vs am gear carb    mfg  model
Mazda RX4    21.0  6  160 110 3.90 2.620 16.46  0  1    4    4  Mazda    RX4
Mazda RX4 Wag 21.0  6  160 110 3.90 2.875 17.02  0  1    4    4  Mazda RX4 Wag
Datsun 710    22.8  4  108  93 3.85 2.320 18.61  1  1    4    1 Datsun    710
# connect to local MySQL database (host='localhost' by default)
con = dbConnect(MySQL(), "testrdb", username="testuser", password="testpass")
dbWriteTable(con, 'motortrend', mtcars)
dbDisconnect(con)
</syntaxhighlight>
And check the database from mysql
<syntaxhighlight lang='bash'>
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>
</syntaxhighlight>
=== Setting Up Raspberry Pi Temperature/Humidity Sensors for Data Analysis in R ===
[https://roh.engineering/post/setting-up-raspberry-pi-temperature-humidity-sensors-for-data-analysis-in-r/ Setting Up Raspberry Pi Temperature/Humidity Sensors for Data Analysis in R]
=== How does importing a database affect the current database? ===
Continue from the database (testrdb) from the previous section.
<syntaxhighlight lang='bash'>
$ mysql -u testuser -ptestpass -e "use testrdb; select count(*) from motortrend"
+----------+
| count(*) |
+----------+
|      32 |
+----------+
$ mysql -u testuser -ptestpass -e "use testrdb; select * FROM motortrend WHERE model = 'RX4'"
+-----------+------+------+------+------+------+------+-------+------+------+------+------+-------+-------+
| 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  |
+-----------+------+------+------+------+------+------+-------+------+------+------+------+-------+-------+
# Modify a record
$ mysql -u testuser -ptestpass -e "use testrdb; update motortrend SET mpg=22 WHERE model = 'RX4'"
$ mysql -u testuser -ptestpass -e "use testrdb; update motortrend SET mfg='toyota' WHERE model = 'RX4'"
# Remove a record. Use 'Limit' if necessary
# https://stackoverflow.com/questions/18378190/how-to-delete-a-certain-row-from-mysql-table-with-same-column-values
$ mysql -u testuser -ptestpass -e "use testrdb; DELETE FROM motortrend WHERE model = 'Corona'";
# Insert a record
$ mysql -u testuser -ptestpass -e \
"use testrdb; INSERT INTO motortrend VALUES ('newrow',21, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'RX4')";
$ mysql -u testuser -ptestpass -e "use testrdb; select * FROM motortrend WHERE model = 'RX4'"
+-----------+------+------+------+------+------+------+-------+------+------+------+------+--------+-------+
| row_names | mpg  | cyl  | disp | hp  | drat | wt  | qsec  | vs  | am  | gear | carb | mfg    | model |
+-----------+------+------+------+------+------+------+-------+------+------+------+------+--------+-------+
| Mazda RX4 |  22 |    6 |  160 |  110 |  3.9 | 2.62 | 16.46 |    0 |    1 |    4 |    4 | toyota | RX4  |
| newrow    |  21 | NULL | NULL | NULL | NULL | NULL |  NULL | NULL | NULL | NULL | NULL | NULL  | RX4  |
+-----------+------+------+------+------+------+------+-------+------+------+------+------+--------+-------+
# Import.
$ mysql -u testuser -ptestpass testrdb < testrdb.sql
$ mysql -u testuser -ptestpass -e "use testrdb; select * FROM motortrend WHERE model = 'RX4'"
+-----------+------+------+------+------+------+------+-------+------+------+------+------+-------+-------+
| 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  |
+-----------+------+------+------+------+------+------+-------+------+------+------+------+-------+-------+
$ mysql -u testuser -ptestpass -e "use testrdb; select count(*) from motortrend"
+----------+
| count(*) |
+----------+
|      32 |
+----------+
</syntaxhighlight>
== [https://db.rstudio.com/dplyr/ dbplyr] ==
== RODBC ==
== SQLite ==
* [https://www.digitalocean.com/community/tutorials/how-and-when-to-use-sqlite How and When to Use Sqlite]
* [https://cran.r-project.org/web/packages/RSQLite/index.html RSQLite] package
== PostgreSQL ==
* [http://www.thegeekstuff.com/2017/02/postgresql-basics/ 8 PostgreSQL Examples to Install, Create DB & Table, Insert & Select Records]
* [https://opensource.com/article/17/10/set-postgres-database-your-raspberry-pi How to set up a Postgres database on a Raspberry Pi]
* [http://freerangestats.info/blog/2018/07/07/twitter-monitor Setting up RStudio Server, Shiny Server and PostgreSQL]
=== pgcli ===
[https://www.pgcli.com/ Pgcli]: a command line interface for Postgres with auto-completion and syntax highlighting.
== CouchDB ==
[https://www.howtoforge.com/tutorial/ubuntu-couchdb/ How to Install Apache CouchDB on Ubuntu 18.04 LTS]
== [https://www.influxdata.com/ InFluxDB] ==
* Used in [http://www.linuxuk.org/post/monitoring-plants-with-ubuntu/ Monitoring a Greenhouse with Ubuntu]
* [https://grafana.com/ Grafana]
* [https://www.codementor.io/ashish1dev/influxdb-to-grafana-visualizing-time-series-data-in-real-time-5hxhaq0uj InfluxDB To Grafana: Visualizing Time Series Data in Real Time]
* [https://snapcraft.io/gravit-designer Gravit designer] snap app
== [https://www.mongodb.com/ MongoDB] ==
== 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/

Revision as of 20:15, 23 June 2019

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

Installation and setup root password

How to Install MySQL in Ubuntu Linux (18.04)

Debian 9 (Stretch) package now ships with the UNIX_SOCKET authentication plugin enabled and you are no longer asked to set a root password when installing the package. Even when setting a root password via the mysql_secure_installation script you are still denied. So we need to run 'update'; see the command below.

See a solution at Debian 9 “Stretch” and MySQL/MariaDB root password. 

$ sudo apt-get install mysql-server
$ sudo mysql_secure_installation
$ sudo mysql -u root mysql -e "update user set plugin= where user='root'; flush privileges;"
$ mysql -u root -p
...
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 12
Server version: 10.1.23-MariaDB-9+deb9u1 Raspbian 9.0
...
MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
Retrieved from "https://wiki.taichimd.us/index.php?title=MySQL&oldid=20966"