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

Date and Time conversion

select userid,from_unixtime(datefield) from table1;

+-----------------------+----------------------------+
| userid                | from_unixtime(datefield)   |
+-----------------------+----------------------------+
| user1                 | 2016-05-01 19:30:00        |
| user2                 | 2016-05-02 12:55:00        |
| user3                 | 2016-05-03 21:03:00        |
| user4                 | 2016-05-04 23:27:00        |
+-----------------------+----------------------------+