mysql - api, node, security for dummies

mysql.js

// pkg: mysql
// https://github.com/mysqljs/mysql#performing-queries

var mysql = require('mysql'),
    connection = mysql.createConnection({
      host:'localhost',
      user:'bixa',
      password:'pwd',
      database:'foo'
    });

connection.connect();

connection.query('SELECT * FROM potluck;', function (err, res, fields) {
  if (err) throw err;  
  display(res);
});

connection.end();

function display(data) {
  data.forEach(function(o){
    console.log(o.name + ' likes ' + o.food);
  });
}

mysql.sh

# service
sudo service mysql [status|start|stop]
brew services [start|run|stop|restart] mysql

# shell
mysql -u [user] -p

# BASICS
SHOW DATABASES;
SHOW tables;
USE [db];
DESCRIBE [table];

# count
SELECT COUNT(*) FROM [table];

# current db, user, version
SELECT DATABASE();
SELECT USER();
SELECT VERSION();

# edit
CREATE DATABASE [name];
DROP DATABASE [name];

# list users
# https://www.digitalocean.com/community/tutorials/how-to-create-a-new-user-and-grant-permissions-in-mysql
SELECT Host, User, Password FROM mysql.user;
# create new user
CREATE USER 'bixa'@'localhost' IDENTIFIED BY 'pwd'
# set priviliges
GRANT [type of permission] ON [database name].[table name] TO ‘[username]’@'localhost’;
GRANT ALL PRIVILEGES ON * . * TO 'bixa'@'localhost';
# enable
FLUSH PRIVILEGES;

# CREATE table
CREATE TABLE potluck (id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20),
food VARCHAR(30),
confirmed ENUM("Y","N") DEFAULT 'N'
signup_date DATE);

# INSERT DATA
INSERT INTO `potluck` (`id`,`name`,`food`,`confirmed`,`signup_date`) VALUES (NULL, "John", "Casserole","Y", '2012-04-11');
INSERT INTO `potluck` (`name`,`food`) VALUES ("Lucy", "really long name for a dish I dont even know");
# show all
SELECT * FROM potluck;
SELECT table AS table2 WHERE col LIKE '%jay*';
# update
UPDATE `potluck` SET `confirmed` = 'Y' WHERE `potluck`.`name` ='Sandy';
UPDATE potluck SET confirmed='N';
# add col
ALTER TABLE potluck ADD email VARCHAR(40);
# add col at position
ALTER TABLE potluck ADD sanity VARCHAR(40) AFTER name;
# remove col
ALTER TABLE potluck DROP email;
# delete row (works even without back-ticks and citations)
DELETE from potluck where food="BBQ" [AND|OR] human=true;

# filter by date
SELECT * FROM potluck WHERE signup_date>"2012-04-11";
# order or sort
SELECT * FROM potluck ORDER BY signup_date [ASC|DESC]

# DUMPS
# dump potluck from foo to potluck.tsv
$ echo 'SELECT * FROM potluck' | mysql -B -u bixa -p foo > potluck.tsv
# dump everything to .sql
mysqldump --all-databases > dump.sql
# dump dbs
mysqldump --databases db1 db2 db3 > dump.sql
# dump only tables from one db
mysqldump test t1 t3 t7 > dump.sql
# restore from dump
mysql < dump.sql
# in shell
source foo.sql
# csv - no header, craps all over the place
SELECT * FROM potluck INTO OUTFILE '/var/lib/mysql-files/foo.csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n';
# restorable backup from cli flag: --databases is important
mysqldump -u bixa -p --databases foo > foo.sql

# logs
SHOW VARIABLES LIKE '%log%'; # log_error, general_log_file, slow_query_log_file

# regex
# _ any one single char
# % 0+ any chars

mysql_sec.md

mysql_secure_installation

• Start mysql
• $ mysql_secure_installation
• validate existing pwds
• set pwd for root
• remove anonymous users
• deny remote login for root
• remove test db
• reload privileges