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 für Nextcloud konfigurieren
Für Nextcloud sollte folgende Koniguration vorliegen.
Diese kann z.B. in folgender Datei abgelegt werden:
/etc/mysql/conf.d/99-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:
