Quelltext der Seite 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 <code>~/.mysql_history</code>.

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 <code>CREATE DATABASE</code> oder <code>USE [database]</code> enthalten ist, wird das Dumpfile in die Datenbank geschrieben, statt in das o.g. [database_name].

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

== Date and Time conversion ==

 select userid,from_unixtime(datefield) from table1;
 <br>
 +-----------------------+----------------------------+
 | 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 <code>-p</code> 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;
'''Hint:''' Replace "database_name" with the name of the database for which table sizes should be displayed.

=== 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 <code>~/.mytop</code>:

 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

[[Category:Linux]]
[[Category:Terminal]]