MariaDB (Debian)

Connect to Server

$ mysql -u user1 -p                     # Mit Passwortabfrage
$ mysql -u user1 -ppassword1
exit                                    # Close connection

Database Management

show databases;                         # Datenbanken anzeigen
show status;                            # Status of MySQL Server
create database [database];             # Create new database
use [database];                         # Select Database

Table Management

show tables;
show table status;                      # Stats of all tables
describe [table];

User Management

show grants;                            # Show grants of current user
show grants for user1                   # Show grants for user1

List users:

select user,host from mysql.user;

Create user:

create user 'username'@'localhost' identified by 'password';

Remarks:

• localhost: User can connect only from (?) localhost
• %: User can connect on multiple hosts

Grant all access to user1@localhost for * tables in database1:

grant all on database1.* to user1@localhost;

Backup Database

$ mysqldump -u root -p[root_password] [database_name] > dumpfilename.sql

Restore Database

$ mysql -u root -p[root_password] [database_name] < dumpfilename.sql