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

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

sudo /etc/init.d/mysql stop
sudo mysqld_safe --skip-grant-tables &
mysql -u root
use mysql;
update user set password=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;