Table of Contents
Introduction
Welcome to the MySQL Cheat Sheet, a valuable resource for anyone looking to enhance their command line skills with MySQL. This comprehensive guide covers a range of commands, from basic operations like database creation and deletion to more complex tasks such as backing up and restoring databases.
In this tutorial, I will write a MySQL Cheat sheet by command line. How to Reset Root Password in MySQL? How to backup and restore databases, and so forth in MySQL databases.
- MySQL Database Commands Cheat Sheet:
- A comprehensive list of commands for database creation, modification, and management.
- MySQL Command Line Client Cheat Sheet:
- Essential commands for initiating and interacting with MySQL through the command line.
- Create Table MySQL Cheat Sheet:
- Syntax and options for creating tables, defining columns, setting data types, and applying constraints.
- MySQL Connect Cheat Sheet:
- Commands for connecting to a MySQL database using various authentication methods.
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
This MySQL Cheat Sheet is designed to serve as a quick reference for frequently used MySQL commands. Whether you’re a beginner or an experienced developer, these commands are essential tools to efficiently manage your databases. Keep this guide handy to streamline your database operations. I hope will this your helpful. Thank you for reading the DevopsRoles page!
