(My) SQL Cheat Sheat

Lokale User Konfiguration

User für Remote Zugriff hinzufügen

GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.1.%' IDENTIFIED BY 'password';

User und Passwort anzeigen

select user, host, password from user;

+------+---------------+-------------------------------------------+
| user | host          | password                                  |
+------+---------------+-------------------------------------------+
| root | localhost     |                                           |
| root | 192.168.1.% | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B |
+------+---------------+-------------------------------------------+

Passwort setzten

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

Authentifizierung prüfen

SELECT user,authentication_string,plugin,host FROM mysql.user;

+------+-------------------------------------------+-------------+---------------+
| user | authentication_string                     | plugin      | host          |
+------+-------------------------------------------+-------------+---------------+
| root |                                           | unix_socket | localhost     |
| root |                                           |             | 192.168.1.%   |
+------+-------------------------------------------+-------------+---------------+

Anmelde Plugin auf Passwort ändern

UPDATE mysql.user SET plugin = 'mysql_native_password', authentication_string = PASSWORD('root') WHERE User = 'root';

+------+-------------------------------------------+-----------------------+---------------+
| user | authentication_string                     | plugin                | host          |
+------+-------------------------------------------+-----------------------+---------------+
| root | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B | mysql_native_password | localhost     |
| root | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B | mysql_native_password | 192.168.1.% |
+------+-------------------------------------------+-----------------------+---------------+

Backup und Dump

mysqldump -h host -u mysql_user -ppassword database_name > file.sql