Installation einer Datenbank auf der Shell
$ mysql -u adminusername -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \\g.
Your MySQL connection id is 5340 to server version: 3.23.54
Type 'help;' or '\\h' for help. Type '\\c' to clear the buffer.
mysql> CREATE DATABASE databasename;
Query OK, 1 row affected (0.00 sec)
mysql> CREATE USER "wordpressusername"@"hostname" IDENTIFIED BY "password";
mysql> GRANT ALL PRIVILEGES ON databasename.* TO "wordpressusername"@"hostname";
Query OK, 0 rows affected (0.00 sec)
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.01 sec)
mysql> EXIT
Bye
Reset des root Passworts
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 beim Start
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 shell Kommandos
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]
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 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'@'%' |
+-------------------------------------------------------------------------------------------------------+
Backup: Dump von Datenbanken
Dump aller Datenbanken
mariadb-dump --user=admin_backup --password --lock-tables --all-databases > /data/backup/dbs_alldatabases.sql
Dump einer Datenbank
mariadb-dump --user=admin_backup --password --lock-tables --extended-insert --databases your_database_name > /data/backup/your_database_name.sql
Restore: 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
mysqlcheck -u root -p --all-databases
mysqlcheck [options] –databases db_name