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

User anlegen

MariaDB [(none)]> CREATE USER 'root'@10.10.10.19 IDENTIFIED BY 'xxxxxxxxx';

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

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

Beispiel

 <font inherit/monospace;;inherit;;#000000background-color:#ffffff;>mariadb-dump -u root -p -d sqlmail>> sqlmail.sql</font> 

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

mysql -u root -p rcube <rcube.sql

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

mysqlcheck -u root -p --all-databases

mysqlcheck [options] –databases db_name