mysqldump

Dump a Single Database

 mysqldump -u root -p --databases [db-name] > db_$(date +%F).sql

Dump ALL Databases

mariadb-dump \
 --all-databases \
 --single-transaction \
 --routines \
 --events \
 --hex-blob \
 --default-character-set=utf8mb4 \
 -p \
 | gzip > /mnt/web-data/www-mysql-full_$(date +%Y%m%d%H%M%S).sql.gz

Users and Permissions

MySQL 8.4

Show All Users

SELECT user, host, plugin FROM mysql.user ORDER BY user, host;

Show Grants for One User

SHOW GRANTS FOR 'username'@'host';

Show Grants for All Users

First, prepare the Grant statements:

SELECT CONCAT('SHOW GRANTS FOR ''', user, '''@''', host, ''';') FROM mysql.user;

Then copy and paste those commands back into the mysql CLI to execute them.

Older Stuff for Posterity

Show MySQL users, their passwords and hosts

 SELECT User,Host,authentication_string FROM mysql.user ORDER BY User ASC, Host ASC;

Show privileges for a user from a given host

 SHOW GRANTS FOR 'user_name'@'host';

Create a user that can login from localhost

 CREATE USER '[newuser]'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';

Create permissions for above user

 GRANT ALL PRIVILEGES ON *.* TO '[newuser]'@'localhost';
 GRANT ALL PRIVILEGES ON [database].* TO '[newuser]'@'localhost';

Export a User

tbd