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

mysqldump -u root -p dbname > dbname.sql

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

Dump doar la schema:

mysqldump -u root -p --no-data dbname > dbname.sql

Dump doar anumite tabele:

mysqldump -u root -p dbname TABLE1 TABLE2 TABLE3 ... > dbname.sql

Dump fara anumite tabele:

mysqldump -u root -p dbname --ignore-table=DATABASE.TABLE --ignore-table=DATABASE.TABLE2 ... > dbname.sql

mysqldump -uroot -p dbname --ignore-table=dbname.users > dbname.sql  #dump fara tabela cu useri

Restore o singura baza de date din dump facut cu –all-databases

mysql -u root --force --one-database dbname < all_dbdump.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;