Nastavení
Seznam nastavení a jejich hodnot
mysqld --verbose --help
Správa uživatelů
Seznam uživatelů
mysql> SELECT User,Host FROM mysql.user;
Seznam oprávnění uživatele root@localhost
mysql> SHOW GRANTS FOR root@localhost;
Vytvoření nového uživatele
mysql> CREATE USER 'user'@'localhost' IDENTIFIED BY 'secret';
Přidáme vybraná oprávnění pro všechny tabulky DB dbname uživateli 'user'@'localhost'
mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,ALTER,INDEX
ON dbname.* TO 'user'@'localhost';
Přidáme všechna oprávnění pro všechny tabulky DB dbname uživateli 'user'@'localhost'
mysql> GRANT ALL PRIVILEGES ON dbname.* TO 'user'@'localhost';
Odebrání oprávnění uživatele 'user'@'localhost' pro DB dbname:
mysql> REVOKE ALL ON dbname.* FROM 'user'@'localhost';
Odebrání uživatele
mysql> DROP USER user@localhost;
Znovu načtení oprávnění
mysql> FLUSH PRIVILEGES;
Nové heslo pro root
$ mysqladmin -uroot password 'secret'
Správa databáze
Vytvoření databáze
mysql> CREATE DATABASE dbname COLLATE utf8_general_ci;
Vytvoření databáze z konzole
$ mysqladmin -u root -p create dbname
Smazání databáze z konzole
mysqladmin -u root -p drop dbname
Maatkit - rozšířené možnosti MySQL
Adminer - pohodlná náhrada za phpMyAdmin, pouze jeden soubor php.
Další sql příkazy
Nahrazení určitého podřetězce v poli za jiný
mysql> UPDATE table SET field=replace(field, 'original string', 'new string');
Vložení dat z jedné tabulky do druhé
mysql> INSERT INTO table1 (fld1, fld2) SELECT table2.fld1, table2.fld2 FROM table2 WHERE table2.fld2 > 7;
Zobrazení struktury tabulky blog_posts
mysql> DESCRIBE blog_posts;
Zobrazení sql dotazu na vytvoření struktury tabulky blog_posts
mysql> SHOW CREATE TABLE blog_posts;
Monitorování a statistika
Seznam všech databází
mysql> SHOW DATABASES;
Seznam všech tabulek ve vybrané databázi
mysql> SHOW TABLES;
Statistika práce serveru
mysql> SHOW GLOBAL STATUS;
- Mytop - jednoduchý nástroj pro monitorování Mysql
- Vzdálené monitorování výkonu a dotazů MySQL
- Instalace Innotop pro monitorování výkonu MySQL serveru
- Instalace Mtop (Monitorování databázového serveru MySQL)
- Zkontrolujte počet otevřených databázových připojení MySQL na Linuxu nebo Unix-like serveru
Optimalizace databází
Pomocí příkazu mysqlcheck lze provádět kontrolu, optimalizaci a opravu chyb.
Kontrola chyb DB dbname
$ mysqlcheck -p dbname
Oprava a optimalizace všech DB
$ mysqlcheck -Aor -p
Popis argumentů
-p – použít heslo
-A – zkontrolovat všechny databáze
-r – opravit DB
-o – optimalizovat DB
Skript pro jednoduchou optimalizaci DB, lze přidat do cronu pro denní spuštění
mysqlcheck --repair --analyze --optimize --all-databases --auto-repair -u root -pSECRET
Nainstalujeme mysqltuner, který zobrazuje statistiku běžícího mysql serveru a poskytuje užitečné rady pro optimalizaci.
cd /usr/local/bin wget http://mysqltuner.pl/mysqltuner.pl chmod +x mysqltuner.pl
Spuštění
/usr/local/bin/mysqltuner.pl
Při spuštění skript požádá o přihlašovací jméno a heslo pro root. Po správné autorizaci bude zobrazena statistika a různé rady pro úpravu konfiguračního souboru MySQL.
Změna řádku výzvy (prompt)
V ~/.my.cnf přidáme
[client] default-character-set = 'utf8' pager = 'less -n -i -S' prompt = '\u@\h [\d] > ' user = 'root' password = 'secret'
Tato nastavení byla nalezena na internetu, ale autora bohužel neznám. Tato nastavení provádí následující
- nastaví výchozí kódování na utf8
- použije less pro výstup výsledků dotazu
- změní řádek výzvy, \u - uživatel, \h - hostitel, \d - databáze
- zadá přihlašovací jméno a heslo pro mysql-konzoli, což je pohodlné při vývoji na lokálním serveru
Demp (záloha)
Demp databáze
$ mysqldump -uroot -p dbname > dump.sql
Demp vybraných databází
$ mysqldump -uroot -p -B dbname1 dbname2 > dump.sql
Demp všech databází
$ mysqldump -uroot -p -A > dump.sql
Demp pouze struktury, bez dat
$ mysqldump -uroot -p --no-data dbname > database.sql
Jiné možnosti
--add-drop-table - přidá příkaz DROP TABLE před každý příkaz CREATE TABLE
--add-locks - přidá příkaz LOCK TABLES před provedením a UNLOCK TABLE po provedení každého dempu tabulky
--no-create-db, -n - nepřidávat příkaz CREATE DATABASE, který se přidává při použití parametrů --databases a --all-databases
--no-data, -d - dumpovat pouze strukturu tabulek
--no-create-info, -t - nevytvářet příkaz CREATE TABLE
--skip-comments - nevypisovat komentáře.
--compact - použít kompaktní formát
--create-options - přidá další informace o tabulce do příkazu CREATE TABLE: typ, hodnota AUTO_INCREMENT atd. Nepotřebné možnosti lze odstranit pomocí sed.
--extended-insert, -e - použití příkazu INSERT s víceřádkovým syntaxí (zvyšuje kompaktnost a rychlost vstupních operátorů)
--tables - dumpovat pouze tabulky ze seznamu následujícího za tímto parametrem, oddělovač - mezera
Použití dempu
$ mysql -uroot -p dbname1 < dump.sql
Od verze 5.5.30 je možné při dumpu získat následující zprávy
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)
Pro opravu tohoto problému je třeba aktualizovat databáze a restartovat MySQL
sudo mysql_upgrade -u root -h localhost -p --verbose sudo service mysql restart
Změna kódování pro textové pole
Seznam polí pro tabulku table s informacemi o poli, včetně kódování pole
SHOW FULL COLUMNS FROM table;
Změníme charset pro pole field
ALTER TABLE table MODIFY field VARCHAR(255) CHARACTER SET utf8;
Změna kódování při importu z dempu
Určení kódování souboru
file --mime-encoding dump.sql
Konverze z kódování latin1 do utf8
mysqldump --add-drop-table -uroot -p dbname | replace CHARSET=latin1 CHARSET=utf8 | iconv -f latin1 -t utf8 | mysql -uroot -p dbname
Obnovení root-hesla
$ 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