MySQL

@databases, @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


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

indexing


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