MySQL cheat sheet

In this tutorial, I will write MySQL Cheat sheet by command line. How to Reset Root Password in MySQL? How to backup and restore Database, so forth in MySQL databases.

MySQL cheat sheet

At the command line, log in to MySQL as the root user:

mysql -u root -p

How to create and delete MySQL DATABASE

CREATE DATABASE DBName
CREATE DATABASE DBName CHARACTER SET utf8
DROP DATABASE DBName

Backup DATABASE to SQL File in MySQL

mysqldump -u Username -p DBName > sampledatabasename_backup.sql

Restore from backup SQL File in MySQL

mysql - u Username -p DBName < sampledatabasename_backup.sql

MySQL select query

SELECT * FROM table
SELECT * FROM table1, table2, ...
SELECT field1, field2, ... FROM table1, table2, ...
SELECT ... FROM ... WHERE condition
SELECT ... FROM ... WHERE condition GROUPBY field
SELECT ... FROM ... WHERE condition GROUPBY field HAVING condition2
SELECT ... FROM ... WHERE condition ORDER BY field1, field2
SELECT ... FROM ... WHERE condition ORDER BY field1, field2 DESC
SELECT ... FROM ... WHERE condition LIMIT 10
SELECT DISTINCT field1 FROM ...
SELECT DISTINCT field1, field2 FROM ...

Users and Privileges in MySQL

GRANT ALL PRIVILEGES ON sampledatabase.* TO 'user'@'localhost' IDENTIFIED BY 'password';
GRANT SELECT, INSERT, DELETE ON sampledatabase.* TO 'user'@'localhost' IDENTIFIED BY 'password';
REVOKE ALL PRIVILEGES ON sampledatabase.* FROM 'user'@'hostname'; -- one permission only
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'user'@'hostname'; -- To revoke all privileges, which drops all global, database, table, column, and routine privileges for the named user or users

SET PASSWORD = PASSWORD('new_pass')
SET PASSWORD FOR 'user'@'hostname' = PASSWORD('new_pass')
SET PASSWORD = OLD_PASSWORD('new_pass')

DROP USER 'user'@'hostname' -- DROP to delete a user

Reset Root Password in MySQL

$ /etc/init.d/mysql stop
$ mysqld_safe --skip-grant-tables

Open another terminal

$ mysql 
mysql> UPDATE mysql.user SET password=PASSWORD('new_pass') WHERE user='root';

Switch back to the mysqld_safe terminal and kill the process using Control

$ /etc/init.d/mysql start

Conclusion

Thought the article, You can use the command line in “MySQL cheat sheet” as above. I hope will this your helpful. Thank you for reading the DevopsRoles page!