MySQL

Create new user

grant all privileges on db.* to user@'%' identified by 'passwd';

Show extended table information

show table status;

Show table structure

describe <table>

Show indices

show index from <table>

Show all running processes

show full processlist;

Kill a process

kill <pid>;

Repair a table

repair table <table>;

Backup whole database

mysqldump <db> > backup.sql

Selective backup

select * into outfile "backup.sql" from table where foo="bar";

Create database with utf-8 charset

create database <db_name> default character set utf8;
  • Or edit my.cnf

[mysqld]
default-character-set = utf8

[mysql]
default-character-set = utf8

Use InnoDB tables instead of MyISAM

  • Edit my.cnf

default-storage-engine=INNODB

Change db charset

alter database <db_name> character set utf8;

Add Foreign Key Contraint

alter table add constraint <contraint_name> foreign key <column> references <table> <column> on delete cascade;

Add check constraint

alter table add constraint <name> CHECK (some_column > 0 and other_column != "");

Add index

create index <name> on <table> (<column>);

Change output format

  • To display column names as keys in a list append G at the end of the query

Delete entries older than 30 days

DELETE FROM <table> WHERE DATE_SUB(CURDATE(),INTERVAL 30 DAY) <= <column>;

Temporary tables

CREATE TEMPORARY TABLE table2 AS (SELECT * FROM table1)

Reset root password

  • Restart db with `` –skip-grant-tables –skip-networking``

Active active cluster

  • Install MariaDB-Galera-server instead of mysql-server

  • Edit /etc/my.cnf on all nodes

wsrep_cluster_address=gcomm://master-node
wsrep_node_address='<ip_of_this_node>'
wsrep_node_name='<name_of_this_node>'
  • On all other nodes than master node init a base db

mysql_install_db --user=mysql --ldata=/var/lib/mysql
  • On master node start

service mysql bootstrap
  • On all other nodes

service mysql start