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 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;