Unterschiede

Hier werden die Unterschiede zwischen zwei Versionen angezeigt.

--- mariadb_mysql [04/11/2023 - 14:15] – thommie4
+++ mariadb_mysql [17/08/2024 - 07:06] (aktuell) – Externe Bearbeitung 127.0.0.1
@@ Zeile 1: / Zeile 1: @@
-xxxx
+====== 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.
+10:45:37 mysqld_safe Logging to '/var/log/mysql/error.log'.
+10:45:37 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
+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>