MariaDB (Debian)

Diese Anleitungen gelten für:

• MySQL 5.5
• MariaDB 10.0

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
drop database [database];               # Drop 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
• Using apostrophes helps to make sure all characters will be interpreted as expected

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

grant all on database1.* to user1@localhost;

Change Password

Important Note for Debian >= 9.x: When logged in as root no password is needed.

If password is known and you can log in:

set password [for 'user'] = password('newpassword');

Alternative approach by updating user table for compatibility across versions:

# mysql -u root -p
MariaDB [(none)]> USE mysql;
MariaDB [(mysql)]> UPDATE user SET password=PASSWORD('YourPasswordHere') WHERE User='root' AND Host = 'localhost';
MariaDB [(mysql)]> FLUSH PRIVILEGES;

To validate, exit your current MariaDB session by typing.

MariaDB [(mysql)]> exit;

and then press Enter. You should now be able to connect to the server using the new password.

Note: Replace YourPasswordHere with the new password.

Note: Remember to clear command line history.

Clear Command Line History

Command Line is stored in ~/.mysql_history.

Remove file and restart MariaDB.

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        |
+-----------------------+----------------------------+

Upgrade Database

# mysql_upgrade -u root -p

Durch Parameter -p ohne Angabe eines Passworts wird ein Passwort abgefragt.

Transaction Isolation Level und Binary Logging konfigurieren

Für Nextcloud sollte folgende Koniguration vorliegen.

Diese kann z.B. in folgender Datei abgelegt werden:

/etc/mysql/conf.d/nextcloud.cnf

[mysqld]
transaction_isolation = READ-COMMITTED
binlog_format = ROW

Aktuellen Konfiguration:

SELECT @@GLOBAL.tx_isolation, @@tx_isolation, @@GLOBAL.binlog_format, @@binlog_format;

Alternative:

SHOW VARIABLES LIKE 'tx_isolation';
SHOW VARIABLES LIKE 'binlog_format';

Quellen:

https://docs.nextcloud.com/server/19/admin_manual/configuration_database/linux_database_configuration.html#db-transaction-label

https://mariadb.com/kb/en/set-transaction/