 MySQL

 New to topics? Read the documentation here!

 Top articles  Latest articles + New article in topic

Ciro Santilli 34  Updated 2024-12-15  +Created 1970-01-01

sudo mysql

works on Ubuntu 20.10.

Create user for further logins without sudo askubuntu.com/questions/915585/how-to-login-mysql-shell-when-mysql-have-no-password/1325689#1325689:

sudo mysql -e "CREATE USER $USER"

Run command from CLI stackoverflow.com/questions/1602904/how-do-you-run-a-single-query-through-mysql-from-the-command-line

mysql -e 'SHOW DATABASES'

Create test user with password:

sudo mysql -e 'CREATE USER user0 IDENTIFIED WITH mysql_native_password BY "a"'
sudo mysql -e 'GRANT ALL PRIVILEGES ON database_name.* TO "user0"'

and login as that user:

mysql -u user0 -p

mysql -u user0 -pmypassword

The IDENTIFIED WITH mysql_native_password part is to overcome "Client does not support authentication protocol requested by server" when connecting from Node.js.

List users:

sudo mysql -e 'SELECT * FROM mysql.user'

View permissions for each user on each DB: serverfault.com/questions/263868/how-to-know-all-the-users-that-can-access-a-database-mysql

sudo mysql -e 'SELECT * FROM mysql.db'

List databases:

sudo mysql -e 'SHOW DATABASES'

Create database:

sudo mysql -e 'CREATE DATABASE mydb0'

Destroy database:

sudo mysql -e 'DROP DATABASE mydb0'

Show tables in database:

sudo mysql -e 'SHOW TABLES' mydb0

or:

sudo mysql -e 'SHOW TABLES FROM mydb0'

 Read the full article