MySQL

Installation

macOS

$ brew install mysql
$ brew services start mysql

Not sure how to set it up to be able to connect via Sequel Pro

Usage

Creating a database and user

CREATE DATABASE mydatabase;
GRANT ALL ON mydatabase.* TO myuser@localhost IDENTIFIED BY 'mypassword';

Export and import

$ mysqldump -u user -ppassword databasename > outfile.sql
$ mysql -u user -ppassword databasename < outfile.sql

Save query results to file

SELECT id, name, email INTO OUTFILE '/tmp/result.csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
ESCAPED BY '\\'
LINES TERMINATED BY '\n'
FROM users WHERE 1

Truncating tables with foreign keys

SET FOREIGN_KEY_CHECKS=0;
DELETE FROM …;
SET FOREIGN_KEY_CHECKS=1;

Recreating foreign key constraints

ALTER TABLE mytable DROP FOREIGN KEY myfk;
ALTER TABLE mytable ADD CONSTRAINT {constraintdef};