MySQL cheat sheet

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

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.

,

About HuuPV

My name is Huu. I love technology and especially Devops Skill such as Docker, vagrant, git so forth. I likes open-sources. so I created DevopsRoles.com site to share the knowledge that I have learned. My Job: IT system administrator. Hobbies: summoners war game, gossip.
View all posts by HuuPV →

Leave a Reply

Your email address will not be published. Required fields are marked *

Comment moderation is enabled. Your comment may take some time to appear.

This site uses Akismet to reduce spam. Learn how your comment data is processed.