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-serverEdit
/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