MySQL cheetsheet

mysql

MySQL is one of the most popular sql databases, it is a powerfull, free open-source database management system that has been around for years. MySQL powers 9 of the 10 top Web sites worldwide, as well as thousands of corporate web-based applications. MySQL was designed and optimized for Web applications, that’s why it became the favorite choice for web developers.

In this post I’m making a list with the most useful MySQL commands you should know.

Create a new mysql database

CREATE DATABASE dbname;

Drop an existing database

DROP DATABASE dbname;

Create a new user and give him rights on a specific database

CREATE USER 'user'@'localhost' IDENTIFIED BY 'password'; 
GRANT ALL PRIVILEGES ON `dbname`.* TO 'user'@'localhost';
GRANT ALL PRIVILEGES ON `dbname`.* TO 'user'@'127.2.1.%';
SELECT user, host FROM mysql.user;

Grant full user rights on all databases

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

Update a mysql user password

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

Delete a mysql user

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

Dump your database to a sql file

mysqldump -u root -p dbname > dbname-bk.sql
This will temporary block writes to your database
mysqldump --routines --triggers --events -u root dbname > dbname-bk.sql

Restore your backup

mysql -u root -p dbname < dbname.sql

Restore one single database from a dump with multiple databases

mysql -u root --force --one-database dbname < dump-dbs.sql

Update root password

sudo systemctl stop mysql

# or
sudo service mysql stop

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

use mysql;

UPDATE mysql.user SET authentication_string=PASSWORD('newpassword') WHERE user='root';
flush privileges;
quit

mysqladmin -uroot -p shutdown
sudo /etc/init.d/mysql start

Kill all processes of a specific user

SELECT concat('KILL ',id,';') FROM information_schema.processlist WHERE user='user';


If you liked this post, you can share it on Twitter. Also you can follow me on Github or endorse me on LinkedIn.