The most flexible way to get data into R is from (semi-)colon separated files. So I am having my vast and growing collection of .csv and .txt research data extracted from databases, spreadsheets and other statistical software.

I decided to start collecting them into a MySQL database.

My first encounter with MySQL documented on the fly, following the official tutorial.

The MySQL database on my machine was installed together with an apache web server and PHP (the so calles LAMP = Linux + Apache + MySQL + PHP) with the tasksel command:
sudo tasksel install lamp-server

In order to login to the database open a shell and
sudo mysql --password

then enter the root-password and you are presented with the database command line
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 47
Server version: 5.1.37-1ubuntu5 (Ubuntu)
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>

A first overview of what is there gives
SHOW DATABASES;
at the command line. In my case it gives
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
+--------------------+
2 rows in set (0.00 sec)

Basic SQL

SQL commands are printed in capitals for readability though it is not necessary to use capitals (“use” works as well as “USE”)

• USE mysql; connects to the database ‘mysql’ and
• SELECT DATABASE(); shows which database is used right now (in case you forgot)
• SHOW TABLES; lists all tables in the connected database
• DESCRIBE sometable; shows the structure of “sometable”
• SELECT * FROM sometable; prints the table
• CREATE DATABASE MyNewDatabase; creates a database calles MyNewDatabase – case sensitive
• QUIT ends the MySQL session
• mysql -h hostname -u username -p MyNewDatabase connects a specific user directly to a specific database without the need to use the otherwise obligatory use command.

Now that I created a new database I would like to avoid the fuzz while connecting to it. Up til now I have to use the root password to connect to the database.

The solution is to grant all user privileges to my user account without setting a password:
GRANT ALL ON MyNewDatabase.* TO 'rforge'@'localhost'; grants all rights on the new database MyNewDatabase to user rforge

In order to access the MyNewDatabase the only statement needed is:
mysql MyNewDatabase

In order to fill MyNewDatabase with content, MySQL demands two not very straightforward steps:

1. create an empty table with a list of all variables and their specific variable type (like date, numeric, character)
   CREATE TABLE MyNewTable (SomeDate DATE, SomeString VARCHAR(20), SomeNumber DOUBLE);
2. load data from a tab-separated textfile into the empty table
   LOAD DATA LOCAL INFILE 'MyPathTo/Textfile.txt' INTO TABLE MyNewTable