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'] = 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.

Show and Configure Server Variables

Set Transaction Isolation Level und Binary Logging

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 Transaction Isolation Level und Binary Logging

Current Configuration:

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

Alternative:

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

select variable_name,global_value from 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 anzeigen

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

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