DE

↑ ↓ select, Enter open, Esc close

Show the privileges of a MySQL user

harmless rootMySQL and MariaDBdebian, ubuntu
Adjust the values, the command updates live
root@server
mysql -e "SHOW GRANTS FOR 'dbuser'@'localhost';"

Prints the user’s privileges as GRANT statements. Useful with the error message “Access denied” to check whether the user has access to the right database. User and host must exactly match an entry in mysql.user.

Also searched as

  • check mysql user privileges
  • access denied for user database
  • what permissions does database user have

Related one-liners

All in MySQL and MariaDB

Create a MySQL database with its own user

#mysql -e "CREATE DATABASE wp_example CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; CREATE USER 'wp_example_user'@'localhost' IDENTIFIED BY 'SecurePassword'; GRANT ALL PRIVILEGES ON wp_example.* TO 'wp_example_user'@'localhost';"
cautionMySQL and MariaDB

List all MySQL users with their host

#mysql -e 'SELECT user, host FROM mysql.user ORDER BY user;'
harmlessMySQL and MariaDB

Analyze the slow query log for the slowest queries

#mysqldumpslow -s t -t 10 /var/log/mysql/mariadb-slow.log
harmlessMySQL and MariaDB

Back up a single database consistently as gzip

$mysqldump --single-transaction --quick -u dbuser -p wp_example | gzip > /var/www/vhosts/example.com/wp_example.sql.gz
harmlessMySQL and MariaDB

Back up all databases into one gzipped file

#mysqldump --single-transaction --quick --all-databases --routines --events | gzip > /root/all-databases.sql.gz
harmlessMySQL and MariaDB

Back up each database into its own gzipped file

#for db in $(mysql -NBe 'SHOW DATABASES' | grep -Ev '^(information_schema|performance_schema|sys)$'); do mysqldump --single-transaction --quick "$db" | gzip > "/root/db-backup/$db.sql.gz"; done
harmlessMySQL and MariaDB

Read first, then run.

The commands on myline.de act directly on servers, files and databases. A wrong path or placeholder can delete data irreversibly or make a server unreachable.

  • All commands are provided without warranty and are not tested on every system.
  • Understand what a command does before running it, and check every placeholder.
  • Make a backup first and, if possible, try it on a test system.
  • You run commands at your own risk. Liability for damages is excluded to the extent permitted by law.