{"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… <\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_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}]}}