MYSQL und MARIADB

Reset root Passwort

Version ermitteln

dpkg -l 'mysql-server*'

Variante 1: Mysql mit Schalter starten

/usr/sbin/mysqld --skip-grant-tables --skip-networking &

Client starten

mysql -u root
FLUSH PRIVILEGES;
SET PASSWORD FOR root@'localhost' = PASSWORD('password');
FLUSH PRIVILEGES;

Danach mysql stoppen und wieder normal starten.

Variante 2: Password mit initialer Textdatei setzen

Create a text file containing the password-assignment statement on a single line. Replace the password with the password that you want to use.

Für 5.5.x

SET PASSWORD FOR 'root'@'localhost' = PASSWORD('MyNewPass');

MYSQL Server mit der Datei initialisieren:

shell> mysqld_safe --init-file=/home/me/mysql-init &

root@server6:/home/thommie# mysqld_safe --init-file=/home/thommie/mysql_reset.txt &
[1] 120598
root@server6:/home/thommie# 160725 10:45:37 mysqld_safe Can't log to error log and syslog at the same time. Remove all --log-error configuration options for --syslog to take effect.
160725 10:45:37 mysqld_safe Logging to '/var/log/mysql/error.log'.
160725 10:45:37 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
160725 10:45:40 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended

MySQL per shell

Login

mysql -u root root Login
show databases; liste alle DBs
use phpmyadmin; benutze diese DB
MariaDB [phpmyadmin]> DELETE FROM `phpmyadmin`.`pma__userconfig` WHERE username='root3';
Query OK, 1 row affected (0.128 sec) löscht Userconfig (2FA Setting) für diesen User

Remote Login

mysql -u username -p password -h [host] -D [database]

MySQL Replikation

https://www.thomas-krenn.com/de/wiki/MySQL_Replikation

Mit ssl: https://www.thomas-krenn.com/de/wiki/MySQL_Verbindungen_mit_SSL_verschl%C3%BCsseln

Einschränkung auf einige DBs:

replicate-wild-do-table=dbname1.%

replicate-wild-do-table=dbname2.%

Siehe auch http://dev.mysql.com/doc/refman/5.7/en/replication-options-slave.html

MARIADB Besonderheiten

Login direkt aus der Shell mit root möglich, wenn das unixsocket plugin aktiv ist (default). Ein User, der nur über die lokale mysql DB authentifiziert werden soll, muss in der plugin Spalte mysqlnative_password und ein Passwort gesetzt haben.

User anlegen

CREATE USER foo2@test IDENTIFIED BY 'mariadb';

Datenbank anlegen

CREATE DATABASE passbolt;

User volle rechte auf die DB geben

GRANT ALL privileges ON `passbolt`.* TO 'passbolt';:

Kontrolle

MariaDB [(none)]> show grants for passbolt;
+---------------------------------------------------------------------------------------------------------+
| Grants for passbolt@% |
+---------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'passbolt'@'%' IDENTIFIED BY PASSWORD '*44BF0BD050389323145AA5E7F8D7549C194AE951' |
| GRANT ALL PRIVILEGES ON `passbolt`.* TO 'passbolt'@'%' |
+---------------------------------------------------------------------------------------------------------+

Alle Spalten einer Tabelle zeigen

SHOW COLUMNS FROM user FROM mysql;

Inhalt von Spalten zeigen

MariaDB [mysql]> SELECT host, user, password, plugin FROM user;
+-----------+--------+-------------------------------------------+-----------------------+
| Host | User | Password | plugin |
+-----------+--------+-------------------------------------------+-----------------------+
| localhost | root | invalid | mysql_native_password |
| localhost | mysql | invalid | mysql_native_password |
| % | joomla | *7CFBEFC5A73BBC76917C58894D45FB09D7E3F59B | mysql_native_password |
+-----------+--------+-------------------------------------------+-----------------------+

Privilegien eines Users zeigen:

MariaDB [mysql]> show grants for joomla;
+-------------------------------------------------------------------------------------------------------+
| Grants for joomla@% |
+-------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'joomla'@'%' IDENTIFIED BY PASSWORD '*7CFBEFC5A73BBC76917C58894D45FB09D7E3F59B' |
| GRANT ALL PRIVILEGES ON `joomla2`.* TO 'joomla'@'%' |
| GRANT ALL PRIVILEGES ON `joomla`.* TO 'joomla'@'%' |
| GRANT ALL PRIVILEGES ON `joomla`.`joomla` TO 'joomla'@'%' |
+-------------------------------------------------------------------------------------------------------+

Dump von Datenbanken

root@db1b:~# mariadb-dump
Usage: mariadb-dump [OPTIONS] database [tables]
OR mariadb-dump [OPTIONS] --databases DB1 [DB2 DB3...]
OR mariadb-dump [OPTIONS] --all-databases
OR mariadb-dump [OPTIONS] --system=[SYSTEMOPTIONS]]
For more options, use mariadb-dump --help

Shell-Import von Datenbanken

root@maria1:/home/thommie# mysql -u root -p
mysql -u root -p rcube <rcube.sql

Reparatur von Tabellen

https://dev.mysql.com/doc/refman/8.4/en/mysqlcheck.html

mysqlcheck -u root -p --all-databases
mysqlcheck [options] –databases db_name