Skip to content

Instantly share code, notes, and snippets.

@karlpokus
Last active Mar 2, 2017
Embed
What would you like to do?
mysql - api, node, security for dummies
// 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);
});
}
# 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_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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment