[Mysql] Mysql cheatsheet #persistance

README.md

Installation

https://dev.mysql.com/downloads/mysql/ Download TAR option

Setup

1. Unpack TAR
2. Create data directory in exploded root
3. Initialize - mysqld --initialize
4. Check for line and make note of root password

A temporary password is generated for root@localhost:

Start

1. Start server - sudo mysqld --user=$CURRENT_USER

Stop

MAC - /usr/local/mysql/bin/mysqladmin -u root shutdown -p

Post startup

1. Login as root - mysql -u root -p (use saved password above)
2. Reset root password - ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass';

Create user

• Create User - CREATE USER 'dbuser'@'localhost' IDENTIFIED BY 'password';
• Grant permissions - GRANT ALL ON *.* TO 'dbuser'@'localhost' WITH GRANT OPTION;
• Reset password REQUIRED - ALTER USER 'dbuser'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';
• flush privileges;

Create database

• Create database - create database dbname;
• Grant permissions to 'dbname' - GRANT ALL PRIVILEGES ON database_name.* TO 'username'@'localhost';

Connecting to database

• JDBC = mysql://username:password@host:port/db_name
• terminal = mysql -u root -p -D <DB-name>

Alter

• Set default for boolean column - ALTER TABLE api_audit MODIFY COLUMN customer_visible boolean NOT NULL DEFAULT 0;

Tips

1. Display
   1. To use less to scroll horizontally and vertically - pager less -SFX. To reset - nopager
   2. Or execute query as select * from table\G , replace ; with \G
   3. Run queries inline without logging in mysql -hlocalhost -P 3306 -udbuser --protocol=tcp -D db --execute="drop table test"

Debug - Production

1. Get DDL statement from live table SHOW CREATE TABLE

Queries

Show all tables filtered by prefix users

SHOW TABLES LIKE 'user%'

Show all foreign keys constraint on table

SELECT TABLE_NAME, COLUMN_NAME,  CONSTRAINT_NAME, REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE REFERENCED_TABLE_NAME = 'table' group by constraint_name;

Truncate table with foreign key

SET FOREIGN_KEY_CHECKS = 0;
TRUNCATE table1;
SET FOREIGN_KEY_CHECKS = 1;

Docker Mysql

Connecting

mysql -h localhost -P 3306 --protocol=tcp -u dbuser   -D db

(on mac replace localhost with 172.17.0.1 the default ip of docker desktop)

Backup

docker exec CONTAINER /usr/bin/mysqldump -u root --password=root DATABASE > backup.sql

Restore

cat backup.sql | docker exec -i CONTAINER /usr/bin/mysql -u root --password=root DATABASE