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!
