MySQL

Monitor queries

watch -n 1 mysqladmin --user=<user> --password=<password> processlist

List all databases

show databases;

List all tables

show tables;

Describe table contents

-- Column names
describe TABLE_NAME;
-- Schema
show create table TABLE_NAME;
-- Indexes
show index from TABLE_NAME;

Dump the database schema

mysqldump --all-databases --no-data
# --skip-add-drop-table
# --skip-comments

Server-side help

-- contents
help contents

Check if a server is up

mysqladmin ping

Create a database

create database DATABASE_NAME;

Import database

mysql -u username -p<password> database < filename.sql

Export database

mysqldump -u username -p<password> database > filename.sql

Delete database

drop database DATABASE_NAME;

User management

-- Create a user
CREATE USER 'example_user'@'localhost' IDENTIFIED BY 'example_pass';
-- Revoke permissions for user
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'example_user'@'localhost';
-- Delete a user
DROP USER 'example_user'@'localhost';

Show grants

-- show grants for current user
show grants;
-- show grants for particular user
show grants for 'user'@'example.com';

Variables

-- session variables
SHOW SESSION VARIABLES;
SET SESSION sort_buffer_size=1000000;
-- global variables
SHOW GLOBAL VARIABLES;
SET GLOBAL sort_buffer_size=1000000;

Resetting root password

/etc/init.d/mysql stop
/usr/bin/mysqld_safe --skip-grant-tables &
mysql --user=root mysql

update user set Password=PASSWORD('new-password-here') WHERE User='root';
flush privileges;

fg
# (ctrl-c to kill mysql)
service mysql start

Create prefix index

alter table TABLENAME.COLUMN
add key (COLUMN(n));

See what engine the table uses

show table status
like 'table_name' \G

See running processes

-- Quick glance
show processlist ;
-- sort by user
select * from information_schema.processlist where user='foobar';

Isolation Levels

Isolation level	dirty reads possible	nonrepeatable reads possible	phantom reads possible	locking reads
READ UNCOMMITTED	t	t	t	f
READ COMMITTED	f	t	t	f
REPEATABLE READ	f	f	t	f
SERIALIZABLE	f	f	f	t

SET SESSION TRANSACTION ISOLATION LEVEL [level];

Autocommit

SHOW VARIABLES LIKE 'AUTOCOMMIT';
SET AUTOCOMMIT=[0|1]

Repairing

check table [tablename]
repair table [tablename]

InnoDB engine

has high overhead, but row-level locking with multiversion concurrency control (MVCC)

Memory engine

uses table-locking, but is speedy

Archive engine

Only uses INSERT and SELECT
Compresses each new row with zlib
Low disk I/O
Ideal for logging

NDB Cluster Engine

Consists of data nodes, management nodes, and SQL nodes
Real-time performance with redunancy and load-balancing capabilities
Complex joins are slow, but single table lookups can be fast

Falcon Engine

Uses MVCC, tries to keep transactions in memory
(need to see where it’s development is at now)

soliddb engine

similar to InnoDB

PBXT (Primebase XT) engine

Has high write concurrency

Maria

(needs to be looked into)

Optimizing

Avoid NULL when possible
optimize table

indexing

Isolate the query column
Try to simplify any math, and use literals when possible
When indexing char columns, try using just a few letters
- good target is count(distinct name) / count(*)

Timezones

-- see what time zones are in use
SELECT @@global.time_zone, @@session.time_zone;
-- set global time zone
SET GLOBAL time_zone = <timezone>;
-- set session time zone
SET time_zone = <timezone>;

mysqladmin

mysqladmin COMMAND

command	desc
flush-logs	rotate logs
version	get version

Search for foreign key use

select * from KEY_COLUMN_USAGE where REFERENCED_TABLE_NAME = 'tbl_name';

Disable super_read_only

set global super_read_only=0

super_read_only https://www.percona.com/blog/2016/09/27/using-the-super_read_only-system-variable/

Good books

High Performance MySQL by Baron Schwartz, Perter Zaitsev, Vadim Tkachenko

MySQL - Configuration

Variables

-- session variables
SHOW SESSION VARIABLES;
SET SESSION sort_buffer_size=1000000;
-- global variables
SHOW GLOBAL VARIABLES;
SET GLOBAL sort_buffer_size=1000000;

Get timezone config

SELECT @@global.time_zone, @@session.time_zone;

network

Port 3306 Plaintext protocol SSL is negotiated in-stream

performance

indexing

Isolate the query column
Try to simplify any math, and use literals when possible
When indexing char columns, try using just a few letters
- good target is count(distinct name) / count(*)

Check the slow query log

Enable this for debugging, don’t leave it running if you don’t need it.

set global slow_query_log = ON|OFF
set global slow_query_log_file = file_name

toolkit (https://www.percona.com/software/database-tools/percona-toolkit) pt-query-digest /var/log/mysql/mysql-slow.log

Dealing with fragmentation

-- size in MB
select ENGINE, TABLE_NAME, Round(DATA_LENGTH/1024/1024) as data_length, round(INDEX_LENGTH/1024/1024) as index_length, round(DATA_FREE/1024/1024) as data_free, (data_free/(index_length+data_length)) as frag_ratio from information_schema.tables where DATA_FREE > 0 order by frag_ratio desc;

Optimize table

optimize table <tbl>;

https://dev.mysql.com/doc/refman/5.7/en/optimize-table.html

Good ways to benchmark

Use a query log to come up with a realistic workload that covers peek time and when batch jobs are run
Use fresh snapshots between benchmarks
Full stack tools:
- ab
- http_load
  - http_load -rate [requests_per_sec] -parallel [num_processes] -seconds [time] [url_file]
- JMeter
Single component tools:
- Database Test Suite (made by OSDL)
- sql-bench
- Super Smack

mysqlslap

Emulates client load

Run profiling

set profiling = 1;
* run query *
show profile;

Resources

High Performance MySQL by Baron Schwartz, Perter Zaitsev, Vadim Tkachenko

Production Ready

Remove all anonymous accounts

DROP USER ''@'localhost';
DROP USER ''@'hostname';

Remove all non-localhost root users

Remove test databases

DELETE FROM mysql.db WHERE Db LIKE 'test%';
FLUSH PRIVILEGES;
DROP DATABASE test;

Set timezone to UTC

For safe measure, don’t just set the time zone in MySQL, but also set the system time zone to UTC

default-time-zone=UTC

Change the default prompt

[mysql]
prompt="(\U) [\d] >"

https://dev.mysql.com/doc/refman/5.6/en/mysql-commands.html

replication

Set the replica binlog coordinates

CHANGE MASTER TO
  MASTER_HOST='example.com',
  MASTER_LOG_FILE='mysqld-bin.123456',
  MASTER_LOG_POS=123456;

Check the binlog in a human readable way

mysqlbinlog $BINLOG_FILE

Links

https://dev.mysql.com/doc/refman/5.6/en/replication-gtids-concepts.html

security

Don’t place DB on same instance as application

If the application is vulnerable to a file disclosure attack, it could allow attacker to download the DB files directly. This can be mitigated by proper file permissions, but a locked down ACL on a networked MySQL server is just as good (and makes the application more scalable as well).

Upgrading

If using InnoDB:

set global innodb_fast_shutdown=0

With a slow shutdown, InnoDB performs a full purge and change buffer merge before shutting down, which ensures that data files are fully prepared in case of file format differences between releases.

Run mysql_upgrade

mysql_upgrade should not be used when the server is running with –gtid-mode=ON. See GTID mode and mysql_upgrade for more information.

ZFS

Set the ZFS recordsizes to match InnoDB page size

zfs set recordsize=16k  tank/db
zfs set recordsize=128k tank/log

Engines

MariaDB - Aria Engine

https://en.wikipedia.org/wiki/Aria_%28storage_engine%29

MySQL - Blackhole Engine

No storage mechanism at all
Useful for replication setups and audit logging

MySQL - CSV Engine

Reads, writes to CSV files

MySQL - Federated Tables

http://archive.oreilly.com/pub/a/databases/2006/08/10/mysql-federated-tables.html

MySQL - InnoDB Engine

innochecksum - offline file checksum utility

MySQL - MyISAM engine

uses table-level locking and lacks transactions, but has low overhead and is platform neutral
excellent for read-only tables

Tools

myisam_ftdump A utility that displays information about full-text indexes in MyISAM tables. See Section 4.6.2, “myisam_ftdump — Display Full-Text Index information”.

myisamchk A utility to describe, check, optimize, and repair MyISAM tables. See Section 4.6.3, “myisamchk — MyISAM Table-Maintenance Utility”.

myisamlog A utility that processes the contents of a MyISAM log file. See Section 4.6.4, “myisamlog — Display MyISAM Log File Contents”.

myisampack A utility that compresses MyISAM tables to produce smaller read-only tables. See Section 4.6.5, “myisampack — Generate Compressed, Read-Only MyISAM Tables”

mysqlhotcopy A utility that quickly makes backups of MyISAM tables while the server is running. See Section 4.6.10, “mysqlhotcopy — A Database Backup Program”..

MySQL - XtraDB Engine

drop-in replacement for InnoDB binary compatibility with InnoDB database files

MySQL

Monitor queries

List all databases

List all tables

Describe table contents

Dump the database schema

Server-side help

Check if a server is up

Create a database

Import database

Export database

Delete database

User management

Show grants

Variables

Resetting root password

Create prefix index

See what engine the table uses

See running processes

Isolation Levels

Autocommit

Repairing

InnoDB engine

Memory engine

Archive engine

NDB Cluster Engine

Falcon Engine

soliddb engine

PBXT (Primebase XT) engine

Maria

Optimizing

indexing

Timezones

mysqladmin

Search for foreign key use

Disable superreadonly

Good books

Links

MySQL - Configuration

Variables

Get timezone config

network

performance

indexing

Check the slow query log

Dealing with fragmentation

Optimize table

Good ways to benchmark

mysqlslap

Run profiling

Resources

Production Ready

Remove all anonymous accounts

Remove all non-localhost root users

Remove test databases

Set timezone to UTC

Change the default prompt

replication

Set the replica binlog coordinates

Check the binlog in a human readable way

Links

security

Don’t place DB on same instance as application

Upgrading

ZFS

Set the ZFS recordsizes to match InnoDB page size

Further reading

Engines

MariaDB - Aria Engine

MySQL - Blackhole Engine

MySQL - CSV Engine

MySQL - Federated Tables

MySQL - InnoDB Engine

MySQL - MyISAM engine

Tools

MySQL - XtraDB Engine

Disable super_read_only