Pre flight check

Putty: https://www.chiark.greenend.org.uk/~sgtatham/putty/latest.html

Installation

Load test database

apt install git
git clone https://github.com/datacharmer/test_db
cd test_db
mysql < employees.sql

Configuration

Dynamic variables:

Storage Engines

Innodb

How to calculate innodb log file size: https://www.percona.com/blog/2008/11/21/how-to-calculate-a-good-innodb-log-file-size/
ERRATA: innodb_strict_mode - set to 1 to change warnings to errors (to ensure MySQL will stop you from inserting bad data to database).

SQL Modes

Mysql: https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html
MariaDB: https://mariadb.com/kb/en/mariadb/sql-mode/

SHOW commands

https://dev.mysql.com/doc/refman/5.7/en/show.html

Transactions

Isolation levels

https://dev.mysql.com/doc/refman/5.7/en/innodb-transaction-isolation-levels.html

Deadlocks

MySQL problems

FK on myISAM

CREATE TABLE parent (
id INT NOT NULL,
name varchar(128),
PRIMARY KEY (id)
) ENGINE=MyISAM;

CREATE TABLE child (
id INT,
parent_id INT,
INDEX par_ind (parent_id),
FOREIGN KEY (parent_id)
REFERENCES parent(id)
ON DELETE CASCADE
) ENGINE=MyISAM;

SHOW CREATE TABLE child;

SQL MODES

SELECT @@GLOBAL.sql_mode;
INSERT INTO parent VALUES('123test', 'Foo Bar');
SELECT * FROM parent;

SET SESSION sql_mode='';
INSERT INTO parent VALUES('123test');
SHOW WARNINGS;
SELECT * FROM parent;

User Management

Row level permissions HOWTO: https://mariadb.com/resources/blog/protect-your-data-row-level-security-mariadb-100
Howto install percona toolkit: https://www.percona.com/doc/percona-toolkit/3.0/installation.html

Logging

Percona slow log rate limiting: https://www.percona.com/doc/percona-server/LATEST/diagnostics/slow_extended.html
general log

SHOW VARIABLES LIKE "%general%";
SET GLOBAL general_log=ON;

slow log

SHOW VARIABLES LIKE "%slow%";
SET GLOBAL slow_query_log=ON;
SHOW VARIABLES LIKE "%time%";
SET GLOBAL long_query_time=0.1;

UPDATE salaries SET salary=salary*2;
UPDATE salaries SET salary=salary+2;

pt-query-digest /var/lib/mysql/localhost-slow.log

binary log

log_bin=localhost-binary

systemctl restart mysql

# do updates/inserts

mysqlbinlog /var/lib/mysql/localhost-binary.000001

Security

Root user security

my.cnf: Older way, works, not so secure (saving root pwd to the file).

vim /root/.my.cnf

[client]
password=YOURSECRETPASSWORD

chmod 600 /root/.my.cnf

unix auth plugin

Secure non password auth. Only root user can log to root account.

Application level trivia

Poland SPZ: https://hackaday.com/2014/04/04/sql-injection-fools-speed-traps-and-clears-your-record/
SQL injection trivia:

SELECT * FROM table WHERE id=$variable;
$variable="15; UPDATE TABLE users;"
https://app.com/show.php?variable=15; UPDATE TABLE

db: SELECT * FROM table WHERE id=15; UPDATE TABLE users;

$sql =prepare SELECT * FROM table WHERE id=?
query $sql, $variable

db: SELECT * FROM table WHERE id="15; UPDATE TABLE users"