MySQL

Comenzi uzuale MySQL

CREATE DATABASE dbname;

GRANT ALL PRIVILEGES ON *.* TO 'user'@'localhost';

CREATE USER 'user'@'localhost' IDENTIFIED BY 'somepass'; 

GRANT ALL PRIVILEGES ON dbname.* TO 'user'@'localhost';

GRANT ALL PRIVILEGES ON `dbname`.* TO 'user'@'127.2.1.%';

UPDATE mysql.user SET PASSWORD=PASSWORD('somepass') WHERE USER='user';

SELECT user, host FROM mysql.user;

SELECT * FROM `table` ORDER BY id DESC LIMIT 10;

DELETE FROM mysql.user WHERE USER='user' AND HOST='localhost';

mysqldump -u root -p dbname > dbname.sql

mysqldump --routines --triggers --events -u root dbname > dbname.sql #blocheaza scrierile in db temporar

mysql -u root -p dbname < dbname.sql

Restore din backup

mysqldump dbname > dbname-today.sql
mysql
drop database dbname;
create database dbname;
exit
cd /backup/path
mysql dbname < dbname.create 
mysql dbname < dbname.sql

Recuperare parola root

#Pentru sisteme de operare mai vechi (CentOS 6, Ubuntu 14 sau mai vechi)
/etc/init.d/mysql stop 

#Pentru sisteme de operare mai noi (CentOS 7, Ubuntu 16 sau mai noi)
systemctl stop mysql / mariadb / mysqld

sudo mysqld_safe --skip-grant-tables &
mysql -u root
use mysql;

#MySQL 5.6 sau mai vechi
update user set password=PASSWORD("parolanoua") where User='root';

#MySQL 5.7 sau mai nou
update mysql.user set authentication_string=PASSWORD('parolanoua') where user='root';

flush privileges;
quit
mysqladmin -uroot -p shutdown
sudo /etc/init.d/mysql start

Kill toate procesele unui user

select concat('KILL ',id,';') from information_schema.processlist where user='username';

Sau:

select concat('KILL ',id,';') from information_schema.processlist where user='username' into outfile '/tmp/a.txt';
source /tmp/a.txt;

Acelasi lucru se poate face pentru un DB specific:

select concat('KILL ',id,';') from information_schema.processlist where db='dbname' into outfile '/tmp/a.txt';
source /tmp/a.txt;