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/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:
