{"id":29534,"date":"2022-09-27T09:25:37","date_gmt":"2022-09-27T09:25:37","guid":{"rendered":"https:\/\/www.devopsschool.com\/blog\/?p=29534"},"modified":"2024-05-07T01:56:47","modified_gmt":"2024-05-07T01:56:47","slug":"mysql-commands-cheatsheet-and-reference","status":"publish","type":"post","link":"https:\/\/www.devopsschool.com\/blog\/mysql-commands-cheatsheet-and-reference\/","title":{"rendered":"MySQL Commands & Query Cheatsheet Example"},"content":{"rendered":"\n\n\n

List All Users in a MySQL Database Server<\/h2>\n\n\n\n

Are you looking for the MySQL SHOW USERS command? Unfortunately, MySQL does not have the SHOW USERS command like SHOW DATABASES, SHOW TABLES, etc., therefore to list all users in a MySQL database server, you use the following query:<\/p>\n\n\n

> mysql -u root -p\nEnter password: ***********\nmysql> use<\/span> mysql<\/span>;\nDatabase changed\nmysql> SELECT user FROM user;\n<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
Show current user<\/h2>\n\n\n\n
To get the information on the current user, you use the user() function as shown in the following statement:<\/p>\n\n\n
mysql> SELECT user();\n<\/code><\/span><\/pre>\n\n\n
Show current logged users<\/h2>\n\n\n\n
To list all users that are currently logged in the MySQL database server, you execute the following statement:<\/p>\n\n\n
SELECT<\/span> \n    user<\/span>, \n    host<\/span>, \n    db<\/span>, \n    command<\/span> \nFROM<\/span> \n    information_schema<\/span>.processlist<\/span>;<\/code><\/span>Code language:<\/span> CSS<\/span> (<\/span>css<\/span>)<\/span><\/small><\/pre>\n\n\n
How to change a mysql user password?<\/h2>\n\n\n
$ mysql -u root -p\nmysql> use mysql;\n\n- The syntax is as<\/span> follows for<\/span> mysql database server version 5.7<\/span>.5<\/span> or older:\nmysql> SET PASSWORD FOR 'user-name-here'<\/span>@'hostname'<\/span> = PASSWORD('new-password'<\/span>);\n\n- For mysql database server version 5.7<\/span>.6<\/span> or newer use the following syntax:\nmysql> ALTER USER 'user'<\/span>@'hostname'<\/span> IDENTIFIED BY 'newPass'<\/span>;\n\n- You can also use the following sql syntax:\nmysql> UPDATE mysql.user SET Password=PASSWORD('new-password-here'<\/span>) WHERE USER='user-name-here'<\/span> AND Host='host-name-here'<\/span>;<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
How To Enable & Disable Remote Access to MySQL<\/h2>\n\n\n
Locate mysql config file such as<\/span> my.cnf and Add \"bind-address = 0.0.0.0\"<\/span> if<\/span> you want to mysql should be accessible from<\/span> localhost\n\n[mysqld]\npid-file        = \/var\/<\/span>run\/mysqld\/mysqld.pid\nsocket          = \/var\/<\/span>run\/mysqld\/mysqld.sock\ndatadir         = \/var\/<\/span>lib\/mysql\nlog-error       = \/var\/<\/span>log\/mysql\/error.log\nbind-address            = 127.0<\/span>.0<\/span>.1<\/span>\n\n- Restart mysql to make it effective\n\n\nLocate mysql config file such as<\/span> my.cnf and Add \"bind-address = 0.0.0.0\"<\/span> if<\/span> you want to mysql should be accessible from<\/span> localhost\n\n[mysqld]\npid-file        = \/var\/<\/span>run\/mysqld\/mysqld.pid\nsocket          = \/var\/<\/span>run\/mysqld\/mysqld.sock\ndatadir         = \/var\/<\/span>lib\/mysql\nlog-error       = \/var\/<\/span>log\/mysql\/error.log\nbind-address            = 0.0<\/span>.0<\/span>.0<\/span>\n\n- Restart mysql to make it effective\n<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
Mysql backup a Database using mysqldump<\/h2>\n\n\n
Generate the backup of a single database\n$ mysqldump -u root -p sakila > file_20200424.sql\n\nGenerate the backup of multiple databases or all the databases\n$ mysqldump -u root -p --databases sakila employees > file.sql\n\nGenerate the backup of database structure\n$ mysqldump -u root -p --no-data sakila > file.sql\n\nGenerate the backup of a specific table\n$ mysqldump -u root -p sakila actor payment > file.sql\n<\/code><\/span><\/pre>\n\n\n
How to run mysql command without login using shell?<\/h2>\n\n\n\n
\n\n\n
mysql -u root --password='Gshnd$26#12'<\/span> -e \"create database testdb\"<\/span>\nmysql -u root --password='Gshnd$26#12'<\/span> -e \"use testdb\"<\/span>\nmysql -u datadog --password=Gsh534shs -e \"show slave status\"<\/span>\nmysql -u root --password='Gshnd$26#12'<\/span> -e \"use testdb\"<\/span> && \"create table raju (customer_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, first_name TEXT, last_name TEXT)\"<\/span>\n<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
\n\n\n\n
mySql Query basic Example<\/h2>\n\n\n\n
\n\n\n
$ mysql -u root -p\n\ncreate database testdb\n\nuse testdb\n\nCREATE TABLE shop (\n    article INT UNSIGNED  DEFAULT '0000'<\/span> NOT NULL,\n    dealer  CHAR(20<\/span>)      DEFAULT ''<\/span>     NOT NULL,\n    price   DECIMAL(16<\/span>,2<\/span>) DEFAULT '0.00'<\/span> NOT NULL,\n    PRIMARY KEY(article, dealer));\nINSERT INTO shop VALUES\n    (1<\/span>,'A'<\/span>,3.45<\/span>),(1<\/span>,'B'<\/span>,3.99<\/span>),(2<\/span>,'A'<\/span>,10.99<\/span>),(3<\/span>,'B'<\/span>,1.45<\/span>),\n    (3<\/span>,'C'<\/span>,1.69<\/span>),(3<\/span>,'D'<\/span>,1.25<\/span>),(4<\/span>,'D'<\/span>,19.95<\/span>);\n\nSELECT * FROM shop ORDER BY article;\n<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
\n\n\n\n
How to create user in mysql<\/h2>\n\n\n\n
\n\n\n
$ CREATE USER 'sammy'<\/span>@'localhost'<\/span> IDENTIFIED BY 'DevOpsSchool$123'<\/span>;\n$ GRANT ALL PRIVILEGES ON *.* TO 'sammy'<\/span>@'localhost'<\/span> WITH GRANT OPTION;\n$ FLUSH PRIVILEGES;\n$ exit<\/span>\n$ mysql -u sammy -p<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
\n\n\n\n
GRANT with database name wildcard & Pattern in MySQL?<\/h2>\n\n\n\n
\n\n\n
If I use back-tics instead of<\/span> single quotes in<\/span> the syntax, it appears to work just fine:\n\nmysql > grant all on `projectA\\_%`<\/span>.* to `projectA`<\/span>@`%`<\/span>;\nmysql > GRANT ALL PRIVILEGES ON `my%`<\/span>.* TO rajesh@'localhost'<\/span> WITH GRANT OPTION;<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
How to change root password?<\/h2>\n\n\n
Option #1 <\/span>\n$ mysql -u root\nmysql> SET PASSWORD FOR<\/span> 'root'<\/span>@'localhost'<\/span> = PASSWORD('password'<\/span>);\nmysql> flush privileges;\nmysql> exit<\/span>;\n\nOption #2<\/span>\nmysql> ALTER USER 'root'<\/span>@'localhost'<\/span> IDENTIFIED BY 'Rajesh$123sa332'<\/span> USING 'mysql_native_password'<\/span>;\nmysql> flush privileges;\n\nOption #3<\/span>\nmysql> SET PASSWORD FOR<\/span> 'root'<\/span>@'localhost'<\/span> = PASSWORD('Rajesh$123sa332'<\/span>);\nmysql> flush privileges;\n\nOption #4<\/span>\nmysql > ALTER USER 'root'<\/span>@'localhost'<\/span> IDENTIFIED BY 'Rajesh$123sa332'<\/span>;\nmysql> flush privileges;<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
mysql command to import database<\/h2>\n\n\n\n
$ mysql -u galaxy_dbuser -p galaxy_database < file.sql<\/p>\n","protected":false},"excerpt":{"rendered":"
List All Users in a MySQL Database Server Are you looking for the MySQL SHOW USERS command? Unfortunately, MySQL does not have the SHOW USERS command like SHOW DATABASES, SHOW TABLES, etc., therefore to list all users in a MySQL database server, you use the following query: Show current user To get the information on…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_kad_post_transparent":"","_kad_post_title":"","_kad_post_layout":"","_kad_post_sidebar_id":"","_kad_post_content_style":"","_kad_post_vertical_padding":"","_kad_post_feature":"","_kad_post_feature_position":"","_kad_post_header":false,"_kad_post_footer":false,"_kad_post_classname":"","_joinchat":[],"footnotes":""},"categories":[2],"tags":[],"class_list":["post-29534","post","type-post","status-publish","format-standard","hentry","category-uncategorised"],"_links":{"self":[{"href":"https:\/\/www.devopsschool.com\/blog\/wp-json\/wp\/v2\/posts\/29534","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.devopsschool.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.devopsschool.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.devopsschool.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.devopsschool.com\/blog\/wp-json\/wp\/v2\/comments?post=29534"}],"version-history":[{"count":14,"href":"https:\/\/www.devopsschool.com\/blog\/wp-json\/wp\/v2\/posts\/29534\/revisions"}],"predecessor-version":[{"id":46165,"href":"https:\/\/www.devopsschool.com\/blog\/wp-json\/wp\/v2\/posts\/29534\/revisions\/46165"}],"wp:attachment":[{"href":"https:\/\/www.devopsschool.com\/blog\/wp-json\/wp\/v2\/media?parent=29534"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.devopsschool.com\/blog\/wp-json\/wp\/v2\/categories?post=29534"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.devopsschool.com\/blog\/wp-json\/wp\/v2\/tags?post=29534"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}