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;
Code language: PHP (php)

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;Code language: CSS (css)

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';Code language: JavaScript (javascript)

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
Code language: JavaScript (javascript)

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)"
Code language: JavaScript (javascript)

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;
Code language: JavaScript (javascript)

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 -pCode language: PHP (php)

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;Code language: JavaScript (javascript)

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;Code language: PHP (php)

mysql command to import database

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

Rajesh Kumar

I’m a DevOps/SRE/DevSecOps/Cloud Expert passionate about sharing knowledge and experiences. I have worked at Cotocus. I share tech blog at DevOps School, travel stories at Holiday Landmark, stock market tips at Stocks Mantra, health and fitness guidance at My Medic Plus, product reviews at TrueReviewNow , and SEO strategies at Wizbrand.

Do you want to learn Quantum Computing?

Please find my social handles as below;

Rajesh Kumar Personal Website
Rajesh Kumar at YOUTUBE
Rajesh Kumar at INSTAGRAM
Rajesh Kumar at X
Rajesh Kumar at FACEBOOK
Rajesh Kumar at LINKEDIN
Rajesh Kumar at WIZBRAND