docs.daveops.net

Snippets for yer computer needs

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

NDB Cluster Engine

Falcon Engine

soliddb engine

PBXT (Primebase XT) engine

Maria

Optimizing

indexing

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

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

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

# Plain-jane MySQL mysqldumpslow /var/log/mysql/mysql-slow.log # Percona 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

mysqlslap

Emulates client load

Run profiling

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

Resources

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

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

Further reading

Engines

MariaDB - Aria Engine

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

MySQL - Blackhole Engine

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

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