Налаштування
Список параметрів налаштування та їх значення
mysqld --verbose --help
Керування користувачами
Список користувачів
mysql> SELECT User,Host FROM mysql.user;
Список прав у користувача root@localhost
mysql> SHOW GRANTS FOR root@localhost;
Створення нового користувача
mysql> CREATE USER 'user'@'localhost' IDENTIFIED BY 'secret';
Додамо вибрані привілеї для всіх таблиць БД dbname користувачу 'user'@'localhost'
mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,ALTER,INDEX
ON dbname.* TO 'user'@'localhost';
Додамо всі привілеї для всіх таблиць БД dbname користувачу 'user'@'localhost'
mysql> GRANT ALL PRIVILEGES ON dbname.* TO 'user'@'localhost';
Видалення прав користувача 'user'@'localhost' для БД dbname:
mysql> REVOKE ALL ON dbname.* FROM 'user'@'localhost';
Видалення користувача
mysql> DROP USER user@localhost;
Перезавантаження привілеїв
mysql> FLUSH PRIVILEGES;
Новий пароль для root
$ mysqladmin -uroot password 'secret'
Керування базою даних
Створення бази даних
mysql> CREATE DATABASE dbname COLLATE utf8_general_ci;
Створення бази даних з консолі
$ mysqladmin -u root -p create dbname
Видалення бази даних з консолі
mysqladmin -u root -p drop dbname
Maatkit - розширені можливості MySQL
Adminer - зручна заміна phpMyAdmin, всього один php-файл.
Інші sql-команди
Заміна в полі певного підрядка на інший
mysql> UPDATE table SET field=replace(field, 'original string', 'new string');
Вставка даних з однієї таблиці в іншу
mysql> INSERT INTO table1 (fld1, fld2) SELECT table2.fld1, table2.fld2 FROM table2 WHERE table2.fld2 > 7;
Перегляд структури таблиці blog_posts
mysql> DESCRIBE blog_posts;
Перегляд sql-запиту на створення структури таблиці blog_posts
mysql> SHOW CREATE TABLE blog_posts;
Моніторинг і статистика
Список всіх баз даних
mysql> SHOW DATABASES;
Список всіх таблиць у вибраній базі даних
mysql> SHOW TABLES;
Статистика по роботі сервера
mysql> SHOW GLOBAL STATUS;
- Mytop - простий засіб моніторингу 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
Оптимізація баз даних
За допомогою команди mysqlcheck можна виконувати перевірку, оптимізацію та виправлення помилок.
Перевірка на помилки БД dbname
$ mysqlcheck -p dbname
Відновлення та оптимізація всіх БД
$ mysqlcheck -Aor -p
Опис аргументів
-p – використовувати пароль
-A – перевіряти всі бази даних
-r – ремонтувати БД
-o – оптимізувати БД
Скрипт простої оптимізації БД, можна додати в крон для виконання раз на добу
mysqlcheck --repair --analyze --optimize --all-databases --auto-repair -u root -pSECRET
Встановимо mysqltuner, який показує статистику по працюючому mysql-серверу і дає корисні поради щодо оптимізації.
cd /usr/local/bin wget http://mysqltuner.pl/mysqltuner.pl chmod +x mysqltuner.pl
Запустимо
/usr/local/bin/mysqltuner.pl
При запуску скрипт запитає логін і пароль для root'a. Після коректної авторизації буде виведена статистика та різні поради щодо правки конфігураційного файлу MySQL.
Зміна рядка запрошення (prompt)
У ~/.my.cnf додамо
[client] default-character-set = 'utf8' pager = 'less -n -i -S' prompt = '\u@\h [\d] > ' user = 'root' password = 'secret'
Ці налаштування були знайдені на просторах інтернету, але автора, на жаль, не пам'ятаю. Роблять ці налаштування таке
- встановлюємо кодування за замовчуванням у utf8
- використовуємо less для виведення результатів запиту
- змінюємо рядок запрошення, \u - користувач, \h - хост, \d - база даних
- вказуємо логін і пароль для mysql-консолі, зручно при розробці на локальному сервері
Дамп (резервна копія)
Дамп бази даних
$ mysqldump -uroot -p dbname > dump.sql
Дамп вибраних баз
$ mysqldump -uroot -p -B dbname1 dbname2 > dump.sql
Дамп усіх баз
$ mysqldump -uroot -p -A > dump.sql
Дамп лише структури, без даних
$ mysqldump -uroot -p --no-data dbname > database.sql
Інші опції
--add-drop-table - додає команду DROP TABLE перед кожною командою CREATE TABLE
--add-locks - додає команду LOCK TABLES перед виконанням і UNLOCK TABLE після виконання кожного дампу таблиці
--no-create-db, -n - не додавати команду CREATE DATABASE, яка додається при використанні параметрів --databases і --all-databases
--no-data, -d - дампити лише структуру таблиць
--no-create-info, -t - не створювати команду CREATE TABLE
--skip-comments - не виводити коментарі.
--compact - використовувати компактний формат
--create-options - додає додаткову інформацію про таблицю в команду CREATE TABLE: тип, значення AUTO_INCREMENT тощо. Не потрібні опції можна вирізати за допомогою sed.
--extended-insert, -e - застосування команди INSERT з багаторядковим синтаксисом (підвищує компактність і швидкодію операторів введення)
--tables - дампити лише таблиці зі списку, що йде за цим параметром, роздільник - пробіл
Застосування дампу
$ mysql -uroot -p dbname1 < dump.sql
Починаючи з версії 5.5.30 при дампі можливо отримати таке повідомлення
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)
Для виправлення цього треба оновити бази і перезапустити MySQL
sudo mysql_upgrade -u root -h localhost -p --verbose sudo service mysql restart
Зміна кодування для текстового поля
Список полів для таблиці table з інформацією про поле, у тому числі й кодування поля
SHOW FULL COLUMNS FROM table;
Змінюємо charset для поля field
ALTER TABLE table MODIFY field VARCHAR(255) CHARACTER SET utf8;
Зміна кодування при імпорті з дампу
Визначення кодування файлу
file --mime-encoding dump.sql
Конвертування з кодування latin1 в utf8
mysqldump --add-drop-table -uroot -p dbname | replace CHARSET=latin1 CHARSET=utf8 | iconv -f latin1 -t utf8 | mysql -uroot -p dbname
Відновлення root-пароля
$ 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