MySQL MariaDB cheat sheet

14.Jul.2022

database / MariaDB / MySQL / Postgress / DB, General / Allgemein

su - root;

list all available databases:

# login & send command to mysql & logout
mysql -u root -e 'show databases' -s --skip-column-names

# or login
mysql -u root -p;

# enter command
mysql> show databases;
+----------------------+
| Database             |
+----------------------+
| information_schema   |
| DatabaseXY           |
| DatabaseXYZ          |
| mysql                |
| phpmyadmin           |
+----------------------+
7 rows in set (0.00 sec)

information_schema, mysql are system “internal” databases (where the MySQL users etc.) are stored.

They are created per default during installation.

create new mysql user called “user” and a database called “databasename” @ localhost and grant the user all access rights

CREATE USER 'user'@'localhost' IDENTIFIED VIA mysql_native_password USING '***';

GRANT USAGE ON *.* TO 'user'@'localhost' REQUIRE NONE WITH MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0;

CREATE DATABASE IF NOT EXISTS `databasename`;

GRANT ALL PRIVILEGES ON `databasename`.* TO 'user'@'localhost';

GRANT ALL PRIVILEGES ON `databasename\_%`.* TO 'user'@'localhost';