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;

Delete user from all grants tables:

drop user <username>;

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'@<hostname>] = password('newpassword');

Example

set password for 'user1'@localhost = password('helloworld12345');

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

Vorsicht beim Restore: Wenn in dumpfilename.sql die Codezeile CREATE DATABASE oder USE [database] enthalten ist, wird das Dumpfile in die Datenbank geschrieben, statt in das o.g. [database_name].

Lösung: In dumpfilename.sql die Anweisung CREATE DATABASE entfernen und USE [database] so anpassen, dass stattdessen nach [database_name] wiederhergestellt wird.

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.

Show and Configure Server Variables

MySQL should be configured for Nextcloud as follows. Storage configuration in /etc/mysql/conf.d/nextcloud.cnf:

[mysqld]
transaction_isolation = READ-COMMITTED
binlog_format = ROW
innodb_file_format=barracuda
innodb_file_per_table=1
innodb_buffer_pool_size=1G
innodb_io_capacity=4000
innodb_io_capacity_max=12000

Show Current Configuration of Transaction Isolation Level and Binary Logging

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

Alternative:

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

Alternative 2:

SELECT variable_name,global_value FROM information_schema.system_variables WHERE variable_name LIKE "innodb%" ORDER_BY variable_name;

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/

Character Set und Collation einer Datenbank anzeigen

USE [database];
SELECT @@character_set_database, @@collation_database;

In order to have 4-Byte support the result should look like this:

+--------------------------+----------------------+
| @@character_set_database | @@collation_database |
+--------------------------+----------------------+
| utf8mb4                  | utf8mb4_general_ci   |
+--------------------------+----------------------+

Default values are defined in /etc/mysql/mariadb.conf.d/50-server.cnf

Monitoring

Database Size

SELECT table_schema AS "Database", 
ROUND(SUM(data_length + index_length)/1024/1024,2) AS "Size (MB)" 
FROM information_schema.TABLES 
GROUP BY table_schema;

Table Size

SELECT table_name AS "Table",
ROUND(((data_length + index_length)/1024/1024),2) AS "Size (MB)"
FROM information_schema.TABLES
WHERE table_schema = "database_name"
ORDER BY (data_length + index_length) DESC;

MyTOP

In order to prevent entering database password on the command-line a config file can be used. Make sure permissions are as restrictive as expected.

Create config file ~/.mytop:

user=root
pass=
host=localhost
db=test
delay=5
port=3306
socket=
batchmode=0
header=1
color=1
idle=1

Source: Manpage: https://linux.die.net/man/1/mytop