====== MYSQL und MARIADB ======

===== Reset root Passwort =====

Version ermitteln

<code>
 dpkg -l 'mysql-server*'

</code>

**Variante 1: Mysql mit Schalter starten**
<code>
 /usr/sbin/mysqld --skip-grant-tables --skip-networking &

</code>

Client starten

<code>
 mysql -u root

 FLUSH PRIVILEGES;

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

 FLUSH PRIVILEGES;

</code>

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

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

</code>

MYSQL Server mit der Datei initialisieren:

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

</code>

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

</code>

===== MySQL per shell =====

Login 

<code>
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
</code>

Remote Login 

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

</code>


===== MySQL Replikation =====

[[https://www.thomas-krenn.com/de/wiki/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|https://www.thomas-krenn.com/de/wiki/MySQL_Verbindungen_mit_SSL_verschl%C3%BCsseln]]

Einschränkung auf einige DBs:<code>

 replicate-wild-do-table=dbname1.%

</code>

<code>
 replicate-wild-do-table=dbname2.%

</code>

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

<code>
CREATE USER foo2@test IDENTIFIED BY 'mariadb';

</code>

Datenbank anlegen

<code>
CREATE DATABASE passbolt;
</code>

User volle rechte auf die DB geben

<code>
GRANT ALL privileges ON `passbolt`.* TO 'passbolt';:
</code>

Kontrolle

<code>
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'@'%'                                                  |
+---------------------------------------------------------------------------------------------------------+

</code>

Alle Spalten einer Tabelle zeigen

<code>
SHOW COLUMNS FROM user FROM mysql;
</code>

Inhalt von Spalten zeigen

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

Privilegien eines Users zeigen:

<code>
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'@'%'                                             |
+-------------------------------------------------------------------------------------------------------+
</code>

===== Dump von Datenbanken =====

<code>
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
</code>

===== Shell-Import von Datenbanken =====

<code>
root@maria1:/home/thommie# mysql -u root -p

mysql -u root -p rcube < rcube.sql
</code>