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 unix_socket plugin aktiv ist (default). Ein User, der nur über die lokale mysql DB authentifiziert werden soll, muss in der plugin Spalte mysql_native_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

<font inherit/monospace;;inherit;;#000000background-color:#ffffff;>mysqlcheck -u root -p --all-databases</font>

<programlisting language-terminal> mysqlcheck [options] –databases db_name … </programlisting>