sudo aptitude install mysql-server
sudo vi /etc/mysql/my.cnf sudo /etc/init.d/mysql restart
If you want to access your database remotely you need to do some configuring.
bind-address = 127.0.0.1
change that to the IP-address you want mysql to bind to.
GRANT ALL ON *.* TO USER@'10.101.10.128' IDENTIFIED BY 'my_secret_password';
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.
SELECT TIMESTAMP, FLOOR(HOUR(TIMESTAMP)*4 + MINUTE(TIMESTAMP)/15) FROM lane_measurements;
SELECT TIMESTAMP, DATE_ADD(DATE(TIMESTAMP), INTERVAL 30 MINUTE ) FROM lane_measurements;
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;
LOAD DATA LOCAL INFILE 'road_frc.csv' INTO TABLE road_2_frc FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' (roadid,frc);
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
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.
mysqldump databasename > dumpfile.dump
make sure that the database exists before the restauration or recreate it.
mysql databasename < dumpfile.dump