MySQL Commands & Query Cheatsheet Example

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:

> mysql -u root -p
Enter password: ***********
mysql> use mysql;
Database changed
mysql> SELECT user FROM user;

Show current user

To get the information on the current user, you use the user() function as shown in the following statement:

mysql> SELECT user();

Show current logged users

To list all users that are currently logged in the MySQL database server, you execute the following statement:

SELECT 
    user, 
    host, 
    db, 
    command 
FROM 
    information_schema.processlist;

How to change a mysql user password?

$ mysql -u root -p
mysql> use mysql;

- The syntax is as follows for mysql database server version 5.7.5 or older:
mysql> SET PASSWORD FOR 'user-name-here'@'hostname' = PASSWORD('new-password');

- For mysql database server version 5.7.6 or newer use the following syntax:
mysql> ALTER USER 'user'@'hostname' IDENTIFIED BY 'newPass';

- You can also use the following sql syntax:
mysql> UPDATE mysql.user SET Password=PASSWORD('new-password-here') WHERE USER='user-name-here' AND Host='host-name-here';

How To Enable & Disable Remote Access to MySQL

Locate mysql config file such as my.cnf and Add "bind-address = 0.0.0.0" if you want to mysql should be accessible from localhost

[mysqld]
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
datadir         = /var/lib/mysql
log-error       = /var/log/mysql/error.log
bind-address            = 127.0.0.1

- Restart mysql to make it effective


Locate mysql config file such as my.cnf and Add "bind-address = 0.0.0.0" if you want to mysql should be accessible from localhost

[mysqld]
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
datadir         = /var/lib/mysql
log-error       = /var/log/mysql/error.log
bind-address            = 0.0.0.0

- Restart mysql to make it effective

Mysql backup a Database using mysqldump

Generate the backup of a single database
$ mysqldump -u root -p sakila > file_20200424.sql

Generate the backup of multiple databases or all the databases
$ mysqldump -u root -p --databases sakila employees > file.sql

Generate the backup of database structure
$ mysqldump -u root -p --no-data sakila > file.sql

Generate the backup of a specific table
$ mysqldump -u root -p sakila actor payment > file.sql

How to run mysql command without login using shell?

mysql -u root --password='Gshnd$26#12' -e "create database testdb"
mysql -u root --password='Gshnd$26#12' -e "use testdb"
mysql -u datadog --password=Gsh534shs -e "show slave status"
mysql -u root --password='Gshnd$26#12' -e "use testdb" && "create table raju (customer_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, first_name TEXT, last_name TEXT)"

mySql Query basic Example

$ mysql -u root -p

create database testdb

use testdb

CREATE TABLE shop (
    article INT UNSIGNED  DEFAULT '0000' NOT NULL,
    dealer  CHAR(20)      DEFAULT ''     NOT NULL,
    price   DECIMAL(16,2) DEFAULT '0.00' NOT NULL,
    PRIMARY KEY(article, dealer));
INSERT INTO shop VALUES
    (1,'A',3.45),(1,'B',3.99),(2,'A',10.99),(3,'B',1.45),
    (3,'C',1.69),(3,'D',1.25),(4,'D',19.95);

SELECT * FROM shop ORDER BY article;

How to create user in mysql

$ CREATE USER 'sammy'@'localhost' IDENTIFIED BY 'DevOpsSchool$123';
$ GRANT ALL PRIVILEGES ON *.* TO 'sammy'@'localhost' WITH GRANT OPTION;
$ FLUSH PRIVILEGES;
$ exit
$ mysql -u sammy -p

GRANT with database name wildcard & Pattern in MySQL?

If I use back-tics instead of single quotes in the syntax, it appears to work just fine:

mysql > grant all on `projectA\_%`.* to `projectA`@`%`;
mysql > GRANT ALL PRIVILEGES ON `my%`.* TO rajesh@'localhost' WITH GRANT OPTION;

How to change root password?

Option #1 
$ mysql -u root
mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('password');
mysql> flush privileges;
mysql> exit;

Option #2
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'Rajesh$123sa332' USING 'mysql_native_password';
mysql> flush privileges;

Option #3
mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('Rajesh$123sa332');
mysql> flush privileges;

Option #4
mysql > ALTER USER 'root'@'localhost' IDENTIFIED BY 'Rajesh$123sa332';
mysql> flush privileges;

mysql command to import database

$ mysql -u galaxy_dbuser -p galaxy_database < file.sql

• Author
• Recent Posts

Follow me

Rajesh Kumar

Mentor for DevOps - DevSecOps - SRE - Cloud - Container & Micorservices at Software AG

Join my following certification courses...
- DevOps Certified Professionals (DCP)
- Site Reliability Engineering Certified Professionals (SRECP)
- Master in DevOps Engineering (MDE)
- DevSecOps Certified Professionals (DSOCP)
URL - https://www.devopsschool.com/certification/

My Linkedin - https://www.linkedin.com/in/rajeshkumarin

Follow me

Latest posts by Rajesh Kumar (see all)

• An Introduction of GitLab Duo - December 22, 2024
• Best Hospitals for affordable surgery for medical tourism - December 20, 2024
• Top Global Medical Tourism Companies in the World - December 20, 2024