MySQL-Schnellreferenz

Einstellungen

Liste der Einstellungsparameter und deren Werte

mysqld --verbose --help

Benutzerverwaltung

Benutzerliste

mysql> SELECT User,Host FROM mysql.user;

Liste der Berechtigungen für den Benutzer root@localhost

mysql> SHOW GRANTS FOR root@localhost;

Erstellung eines neuen Benutzers

mysql> CREATE USER 'user'@'localhost' IDENTIFIED BY 'secret';

Wir fügen dem Benutzer 'user'@'localhost' die ausgewählten Privilegien für alle Tabellen der Datenbank dbname hinzu

mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,ALTER,INDEX 
ON dbname.* TO 'user'@'localhost';

Wir fügen dem Benutzer 'user'@'localhost' alle Privilegien für alle Tabellen der Datenbank dbname hinzu

mysql> GRANT ALL PRIVILEGES ON dbname.* TO 'user'@'localhost';

Entfernen der Benutzerrechte für 'user'@'localhost' für die Datenbank dbname:

mysql> REVOKE ALL ON dbname.* FROM 'user'@'localhost';

Benutzer löschen

mysql> DROP USER user@localhost;

Privilegien neu laden

mysql> FLUSH PRIVILEGES;

Neues Passwort für root

$ mysqladmin -uroot password 'secret'

Datenbankverwaltung

Erstellung einer Datenbank

mysql> CREATE DATABASE dbname COLLATE utf8_general_ci;

Erstellung einer Datenbank über die Konsole

$ mysqladmin -u root -p create dbname

Löschen einer Datenbank über die Konsole

mysqladmin -u root -p drop dbname

Maatkit - erweiterte MySQL-Funktionen

Adminer – eine praktische Alternative zu phpMyAdmin, bestehend aus nur einer PHP-Datei.

Weitere SQL-Befehle

Ersetzen eines bestimmten Teilstrings in einem Feld durch einen anderen

mysql> UPDATE table SET field=replace(field, 'original string', 'new string');

Einfügen von Daten aus einer Tabelle in eine andere

mysql> INSERT INTO table1 (fld1, fld2) SELECT table2.fld1, table2.fld2 FROM table2 WHERE table2.fld2 > 7;

Anzeigen der Struktur der Tabelle blog_posts

mysql> DESCRIBE blog_posts;

Anzeigen des SQL-Abfragecodes zur Erstellung der Struktur der Tabelle blog_posts

mysql> SHOW CREATE TABLE  blog_posts;

Monitoring und Statistik

Liste aller Datenbanken

mysql> SHOW DATABASES;

Liste aller Tabellen in der ausgewählten Datenbank

mysql> SHOW TABLES;

Statistik zur Serverleistung

mysql> SHOW GLOBAL STATUS;

• Mytop – einfaches Überwachungstool für MySQL
• Remote MySQL Performance And Query Monitoring
• Install Innotop to Monitor MySQL Server Performance
• Install Mtop (MySQL Database Server Monitoring)
• Check The Number Of MySQL Open Database Connections on Linux Or Unix-like Server

Datenbankoptimierung

Mit dem Befehl mysqlcheck können Sie Prüfungen, Optimierungen und Fehlerkorrekturen durchführen.

Prüfung der Datenbank dbname auf Fehler

$ mysqlcheck -p dbname

Wiederherstellung und Optimierung aller Datenbanken

$ mysqlcheck -Aor -p

Beschreibung der Argumente

-p – Passwort verwenden

-A – alle Datenbanken prüfen

-r – Datenbank reparieren

-o – Datenbank optimieren

Skript zur einfachen Datenbankoptimierung, kann in den Cron-Dienst aufgenommen werden, um einmal täglich ausgeführt zu werden

mysqlcheck --repair --analyze --optimize --all-databases --auto-repair -u root -pSECRET

Installieren Sie mysqltuner, das Statistiken zum laufenden MySQL-Server anzeigt und nützliche Optimierungsvorschläge liefert.

cd /usr/local/bin
wget http://mysqltuner.pl/mysqltuner.pl
chmod +x mysqltuner.pl

Ausführen

/usr/local/bin/mysqltuner.pl

Beim Start fragt das Skript nach dem Benutzernamen und Passwort für root. Nach erfolgreicher Authentifizierung werden Statistiken und verschiedene Ratschläge zur Anpassung der MySQL-Konfigurationsdatei angezeigt.

Ändern der Eingabeaufforderung (Prompt)

In ~/.my.cnf add

[client]
default-character-set = 'utf8'
pager = 'less -n -i -S'
prompt = '\u@\h [\d] > '
user = 'root'
password = 'secret'

These settings were found on the internet, but unfortunately I don’t remember the author. These settings do the following

• set the default encoding to utf8
• use less for displaying query results
• change the prompt string, \u - user, \h - host, \d - database
• specify the login and password for the mysql console, convenient when developing on a local server

Dump (backup)

Database dump

$ mysqldump -uroot -p dbname > dump.sql

Dump of selected databases

$ mysqldump -uroot -p -B dbname1 dbname2 > dump.sql

Dump of all databases

$ mysqldump -uroot -p -A > dump.sql

Dump of structure only, without data

$ mysqldump -uroot -p --no-data dbname > database.sql

Other options

--add-drop-table - adds the command DROP TABLE before each command CREATE TABLE

--add-locks - adds the command LOCK TABLES before execution and UNLOCK TABLE after execution of each table dump

--no-create-db, -n - do not add the command CREATE DATABASE, which is added when using parameters --databases and --all-databases

--no-data, -d - dump only table structures

--no-create-info, -t - do not create the command CREATE TABLE

--skip-comments - do not output comments.

--compact - use compact format

--create-options - adds additional information about the table to the command CREATE TABLE: type, AUTO_INCREMENT value, etc. Unnecessary options can be removed using sed.

--extended-insert, -e - apply command INSERT with multi-line syntax (increases compactness and performance of input operators)

--tables - dump only tables from the list following this parameter, separator is space

Applying the dump

$ mysql -uroot -p dbname1 < dump.sql

Starting with version 5.5.30, during dumping you may receive the following message

Warning: Skipping the data of table mysql.event. Specify the --events option explicitly.
или
mysqldump: Couldn't execute 'show events': Cannot proceed because system tables used by Event Scheduler were found damaged at server start (1577)

To fix this, you need to update the databases and restart MySQL

sudo mysql_upgrade -u root -h localhost -p --verbose
sudo service mysql restart

Changing encoding for a text field

List of fields for table table with information about the field, including the field encoding

SHOW FULL COLUMNS FROM table;

Change charset for field field

ALTER TABLE table MODIFY field VARCHAR(255) CHARACTER SET utf8;

Changing encoding when importing from a dump

Determining the file encoding

file --mime-encoding dump.sql

Converting from encoding latin1 to utf8

mysqldump --add-drop-table -uroot -p dbname | replace CHARSET=latin1 CHARSET=utf8 | iconv -f latin1 -t utf8 | mysql -uroot -p dbname

Restoring root password

$ service mysqld stop
$ mysqld_safe --skip-grant-tables &
$ mysql

mysql> UPDATE mysql.user SET Password=PASSWORD('secret') WHERE User='root';
mysql> FLUSH PRIVILEGES;

$ service mysqld restart