MariaDB Cheat Sheet (Debian)

Aus Matts Wiki

Following notes are tested with:

  • MySQL 5.5
  • MariaDB 10.x

Connect to Server

$ mysql -u user1 -p                     # Mit Passwortabfrage
$ mysql -u user1 -ppassword1
exit                                    # Close connection

Management Tasks

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

Maintenance Tasks

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');

Though its unclear when the apostrophes around the user name are necessary. Within mysql cli client this seems to work too: 

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

Perhaps it is more relevant to specify the hostname.

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.

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.

Reset AUTO_INCREMENT Sequence

To reset the sequence for a column with AUTO_INCREMENT use: 

ALTER TABLE <tablename> AUTO_INCREMENT = <next_number>;

Set <next_number> to the desired number which will be used next time.

As a table can only have one column with AUTO_INCREMENT, there is no need to specify the column.

Further reading: https://www.techonthenet.com/mariadb/auto_increment.php

Backup and Restore

Backup Database

$ mysqldump -u root -p[root_password] [database_name] > dumpfile.sql

Exclude one or more tables: 

$ mysqldump -u root -p[root_password] [database_name] --ignore-table=database_name.table1 > dumpfile.sql

$ mysqldump -u root -p[root_password] [database_name] --ignore-table=database_name.table1 --ignore-table=database_name.table2 > dumpfile.sql

Restore Database

$ mysql -u root -p[root_password] [database_name] < dumpfile.sql

Attention! Be careful when restoring:

If dumpfile.sql contains a CREATE DATABASE [database] or USE [database] statement, the dumpfile.sql will be imported into a new Database [database] regardless of the [database_name] specified above.

Workaround: In dumpfile.sql change the statement CREATE DATABASE [database] and USE [database] so that both contain the [database_name] specified in the statement above. As a consequence, the [database_name] specified above can be omitted resulting in the following restore command: 

$ mysql -u root -p[root_password] < dumpfile.sql

Import from CSV File to Database Table

See How to Import CSV in MariaDB


Server Configuration

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

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

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.

Running Processes

Show running processes: 

show processlist;
show full processlist;


Further reading: https://www.theairtips.com/post/how-to-find-long-running-queries-in-mysql-mariadb

Abgerufen von „https://otremba.net/w/index.php?title=MariaDB_Cheat_Sheet_(Debian)&oldid=3729“