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.

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

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]

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

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

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

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

mysql -u root -p rcube < rcube.sql