User Tools

Site Tools


mysql

Setup

Installing

  sudo aptitude install mysql-server

Configuration

sudo vi /etc/mysql/my.cnf   
sudo /etc/init.d/mysql restart

Remote Access

If you want to access your database remotely you need to do some configuring.

  • First tell mysql to bind to an external ip address (the default is to bind to 127.0.0.1). Edit my.cnf and find the line
bind-address = 127.0.0.1

change that to the IP-address you want mysql to bind to.

  • Then you need to tell mysql which users are granted remote access. This can be done via the “Privileges” Page in phpmyadmin (if installed) or via the console using something like
GRANT ALL ON *.* TO USER@'10.101.10.128' IDENTIFIED BY 'my_secret_password';
  • Some Notes
  • Remember that its not always very wise to GRANT ALL. Instead you can grant USAGE or any subset of SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,…
  • *.* grants the rights to all Database Objects in the System. To grant rights only to a specific DB you can use something like GRANT ALL ON Tolldata.* TO …
  • The IDENTIFIED BY 'password' clause is optional. If you choose to remove it, remember that you must not provide a password when you try to log into the database, or else the login will fail!
  • see http://dev.mysql.com/doc/refman/5.0/en/adding-users.html for more information on user privileges

User Management

To create a user

CONNECT db_name ;
CREATE USER 'gosmart'@'localhost' IDENTIFIED BY 'gosmart';
GRANT SELECT,INSERT,UPDATE,DELETE ON *.* TO 'gosmart'@'localhost';

see http://dev.mysql.com/doc/refman/5.0/en/adding-users.html for more information.

SQL

Working with Dates

Resources

Calculate Time Interval from Date

SELECT TIMESTAMP, FLOOR(HOUR(TIMESTAMP)*4 + MINUTE(TIMESTAMP)/15) FROM lane_measurements; 

Add Time Period to a Date

SELECT TIMESTAMP, DATE_ADD(DATE(TIMESTAMP), INTERVAL 30 MINUTE ) FROM lane_measurements;

Export to CSV File

SELECT * 
INTO OUTFILE "/home/hkoller/prototype_complete.csv" 
FIELDS TERMINATED BY ';' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n'
FROM `prototype_complete` 
ORDER BY clusterid,roadid,laneid,`interval` ASC;

If you require headers in your CSV, you have to use a workaround (see http://dev.mysql.com/doc/refman/4.1/en/select.html here):

SELECT 'company','contact_name' -- writing the header
UNION 
SELECT company , contact_name 
INTO OUTFILE '/tmp/test.csv' FIELDS TERMINATED BY ','  
FROM answers;

Import from CSV File

LOAD DATA LOCAL INFILE 'road_frc.csv'
INTO TABLE road_2_frc
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
(roadid,frc);

Hints

Table Types

MySQL supports different table types with individual strengths and weaknesses. The default table type is MyISAM which is fast but lacks some essential DB features. In order to get a more complete featureset (Foreign Keys, Transactions,..) use InnoDB. eg.

CREATE TABLE test (
    id INTEGER PRIMARY KEY NOT NULL,
    name VARCHAR(20)
) **TYPE InnoDB**;

see http://www.devshed.com/c/a/MySQL/Storage-Engine-Table-Types/ for a detailed discussion of the table types

GROUP BY weirdness

It seems that MySQL allows SELECT statements with a GROUP BY clause to contain normal fields (fields which are not part of an aggregate function or the grouping). Here is an example query to illustrate the problem:

  mysql> SELECT   species
    -> ,        MIN(birth)  -- birthdate of oldest pet per species
    -> ,        MAX(birth)  -- birthdate of youngest pet per species
    -> ,        birth       -- birthdate of ... uh oh...!
    -> FROM     pet_table
    -> GROUP BY species;

The result for column “birth” in the above example is a more or less random value. Modern MySQL versions provide the config parameter

sql_mode=ONLY_FULL_GROUP_BY (put in /etc/mysql/my.cnf)

to disable this behavior (and have MySQL reject such queries like any sane DBMS should).

See http://rpbouman.blogspot.com/2007/05/debunking-group-by-myths.html (the Section about “Running into trouble with GROUP BY” ) for details.

Backup

creating a dumpfile

 mysqldump databasename > dumpfile.dump

restoring the database

make sure that the database exists before the restauration or recreate it.

 mysql databasename < dumpfile.dump
mysql.txt · Last modified: 2013/01/09 10:36 by mantis