Podręczny przewodnik po MySQL

Ustawienia

Lista parametrów konfiguracyjnych i ich wartości

mysqld --verbose --help

Zarządzanie użytkownikami

Lista użytkowników

mysql> SELECT User,Host FROM mysql.user;

Lista uprawnień użytkownika root@localhost

mysql> SHOW GRANTS FOR root@localhost;

Tworzenie nowego użytkownika

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

Dodaj wybrane uprawnienia dla wszystkich tabel bazy danych dbname użytkownikowi 'user'@'localhost'

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

Dodaj wszystkie uprawnienia dla wszystkich tabel bazy danych dbname użytkownikowi 'user'@'localhost'

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

Usunięcie uprawnień użytkownika 'user'@'localhost' dla bazy danych dbname:

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

Usunięcie użytkownika

mysql> DROP USER user@localhost;

Przeładowanie uprawnień

mysql> FLUSH PRIVILEGES;

Nowe hasło dla root

$ mysqladmin -uroot password 'secret'

Zarządzanie bazą danych

Tworzenie bazy danych

mysql> CREATE DATABASE dbname COLLATE utf8_general_ci;

Tworzenie bazy danych z konsoli

$ mysqladmin -u root -p create dbname

Usuwanie bazy danych z konsoli

mysqladmin -u root -p drop dbname

Maatkit - rozszerzone możliwości MySQL

Adminer - wygodna alternatywa dla phpMyAdmin, tylko jeden plik php.

Inne komendy SQL

Zamiana określonego podciągu w polu na inny

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

Wstawianie danych z jednej tabeli do drugiej

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

Podgląd struktury tabeli blog_posts

mysql> DESCRIBE blog_posts;

Podgląd zapytania SQL tworzącego strukturę tabeli blog_posts

mysql> SHOW CREATE TABLE  blog_posts;

Monitorowanie i statystyki

Lista wszystkich baz danych

mysql> SHOW DATABASES;

Lista wszystkich tabel w wybranej bazie danych

mysql> SHOW TABLES;

Statystyki pracy serwera

mysql> SHOW GLOBAL STATUS;

• Mytop - proste narzędzie do monitorowania 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

Optymalizacja baz danych

Poprzez komendę mysqlcheck można przeprowadzać sprawdzanie, optymalizację i naprawę błędów.

Sprawdzanie błędów bazy danych dbname

$ mysqlcheck -p dbname

Odzyskiwanie i optymalizacja wszystkich baz danych

$ mysqlcheck -Aor -p

Opis argumentów

-p – użyj hasła

-A – sprawdzaj wszystkie bazy danych

-r – naprawiaj bazę danych

-o – optymalizuj bazę danych

Skrypt prostej optymalizacji bazy danych, można dodać do crona do wykonania raz dziennie

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

Zainstaluj mysqltuner, który pokazuje statystyki działającego serwera mysql i daje przydatne porady dotyczące optymalizacji.

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

Uruchom

/usr/local/bin/mysqltuner.pl

Podczas uruchamiania skrypt zapyta o login i hasło dla root'a. Po poprawnej autoryzacji zostanie wyświetlona statystyka i różne porady dotyczące edycji pliku konfiguracyjnego MySQL.

Zmiana wiersza zachęty (prompt)

W pliku ~/.my.cnf dodajemy

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

Te ustawienia zostały znalezione w sieci, ale niestety nie pamiętam autora. Ustawienia te wykonują następujące czynności

• ustawiają domyślną kodowanie na utf8
• używają less do wyświetlania wyników zapytań
• zmieniają wiersz zachęty, \u - użytkownik, \h - host, \d - baza danych
• podają nazwę użytkownika i hasło dla konsoli mysql, co jest wygodne przy pracy na lokalnym serwerze

Dump (kopia zapasowa)

Dump bazy danych

$ mysqldump -uroot -p dbname > dump.sql

Dump wybranych baz

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

Dump wszystkich baz

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

Dump tylko struktury, bez danych

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

Inne opcje

--add-drop-table - dodaje polecenie DROP TABLE przed każdą komendą CREATE TABLE

--add-locks - dodaje polecenie LOCK TABLES przed wykonaniem i UNLOCK TABLE po wykonaniu każdego dumpa tabeli

--no-create-db, -n - nie dodawać polecenia CREATE DATABASE, które jest dodawane przy użyciu parametrów --databases i --all-databases

--no-data, -d - dumpować tylko strukturę tabel

--no-create-info, -t - nie tworzyć polecenia CREATE TABLE

--skip-comments - nie wyświetlać komentarzy.

--compact - używać formatu kompaktowego

--create-options - dodaje dodatkowe informacje o tabeli do polecenia CREATE TABLE: typ, wartość AUTO_INCREMENT itp. Niepotrzebne opcje można usunąć za pomocą sed.

--extended-insert, -e - stosowanie polecenia INSERT z wielowierszowym składnią (zwiększa zwięzłość i szybkość operacji wejścia)

--tables - dumpować tylko tabele z listy następującej po tym parametrze, separator - spacja

Zastosowanie dumpa

$ mysql -uroot -p dbname1 < dump.sql

Począwszy od wersji 5.5.30 podczas dumpowania możliwe jest uzyskanie następujących komunikatów

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)

Aby to naprawić, należy zaktualizować bazy danych i zrestartować MySQL

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

Zmiana kodowania dla pola tekstowego

Lista pól dla tabeli table z informacjami o polu, w tym kodowanie pola

SHOW FULL COLUMNS FROM table;

Zmieniamy charset dla pola field

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

Zmiana kodowania przy imporcie z dumpa

Określenie kodowania pliku

file --mime-encoding dump.sql

Konwersja z kodowania latin1 na utf8

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

Odzyskiwanie hasła roota

$ 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