MySQL — FAQ

В этой заметке собраны наиболее часто используемые команды.

Links

• http://www.mysql.ru/docs/man/ — Справочное руководство по MySQL

Конфигурация

Обязательно добавить в my.cnf:

# Обязательно добавить в my.cnf:
character-set-server	= utf8
language		= /usr/share/mysql/english
 
 
# Если хочется протрейсить все запросы:
general_log_file        = /var/log/mysql/mysql.log
general_log             = 1
# Потом: tail -f /var/log/mysql/mysql.log

Работа с базой

Создание базы

create database basename;
# Правильнее указать кодировку
create database basename CHARACTER SET utf8 COLLATE utf8_unicode_ci;

Заливка базы из файла

mysql basename -u user -p < ./file.sql
mysql basename -u user --password="password" < ./file.sql

Выгрузка базы в файл

mysqldump databasename -u username -p > file.sql
mysqldump databasename -u username --password="password" > file.sql

Show

http://dev.mysql.com/doc/refman/5.0/en/show.html

Пользователи

Добавление нового пользователя

CREATE USER 'username'@'localhost' IDENTIFIED BY 'pspass';
FLUSH PRIVILEGES;
GRANT ALL PRIVILEGES ON databasename.* TO 'username'@'localhost';
FLUSH PRIVILEGES;

Если требуется вход со всех хостов, то заменить localhost на %.

Смена пароля пользователю

SET PASSWORD FOR 'username'@'localhost' = PASSWORD('newpass');

Удаление пользователя

REVOKE ALL ON databasename.* FROM 'username'@'localhost';
drop user 'username'@'localhost';
use mysql;
DELETE FROM user WHERE user = 'username';

Сброс пароля root

1. Останавливаем сервис
2. Запускаем сервер с ключом mysqld --skip-grant-tables
3. Заходим в mysql как root mysql -u root
4. Меняем пароль и перезапускаем сервис

use mysql;
update user set password=PASSWORD("NEW-ROOT-PASSWORD") where User='root';
flush privileges;
quit;

Изменение данных

Вставка строки

INSERT INTO city VALUES (1,'Munising',1);
INSERT INTO таблица (поле,поле,поле) VALUES (значение,значение,значение);
если на месте значения DEFAULT, то вставлять значение по умолчанию.

Вставка в подзапросы

INSERT INTO таблица (поле,поле,поле) (SELECT поле,поле,поле FROM таблица_источник WHERE условие)
типы данных по полям источника и приемника должны совпадать.

Удаление данных

DELETE FROM user WHERE user = 'username';

Выбор данных

 SELECT список_выражений(поля)
 FROM источник данных
 WHERE предикаты (условия)
 GROUP BY список_выражений
 HAVING предикаты 
 ORDER BY список_выражений;
 
 
SELECT song.name, artist.* FROM song, artist WHERE song.artist_id=artist.id;

Можно применять сложные математические изменения

SELECT title, ROUND (price * .90,2) FROM cd;

уменьшается до двух знаков с учетом 10% скидки.

Имена полей (псевдонимы полей)

При выводе можно изменять имена полей. Для этого необходимо указать новое значение через пробел от истинного.

SELECT title 'CD Title', price * .90 '90% of price' FROM cd;
+---------------------------------+--------------+
| CD Title                        | 90% of price |
+---------------------------------+--------------+
| Legends of the Great Lakes      |      16.1550 |
| Nothing Less                    |       9.0000 |
| More Legends of the Great Lakes |      16.1550 |
| The Ballad of Seul Choix        |      16.1550 |
| Seeing the Unseen               |       9.0000 |
+---------------------------------+--------------+
5 rows in set (0.01 sec)

Использование подзапросов

SELECT title, price * .90 price, 
(SELECT COUNT(*) FROM song WHERE song.cd_id=cd.cd_id) songs 
FROM cd;

ALL и DISTINCT

DISTINCT в Oracle имеет синоним UNIQUE
ALL (по умолчанию) выводит все строки
SELECT DISTINCT список_выражений(поля) ....
Выбор уникальных 

FROM

Источник данных. Через пробел можно указать псевдоним

SELECT a.title, a.price * .90,2 FROM cd a;

WHERE

Задает условие ограничения вывода результата.

SELECT attraction_name FROM attraction
   WHERE goverment_owned='Y'
   AND citi_id IN (SELECT citi_id FROM city WHERE citi_name='Munising');

ORDER BY

Сортировка.

• ASCENDING (ASC) по возростанию
• DESCENDING (DESC) по убыванию

SELECT выражение FROM источник ORDER BY поле, поле;
 
select DeviceReportedTime,Message 
   from SystemEvents 
   where Facility=9 and Priority=7 
   ORDER BY DeviceReportedTime DESC;

Работа с CACHE

# Посмотреть состояние
SHOW STATUS LIKE 'Qcache%';
 
# Сбросить CACHE
FLUSH QUERY CACHE;

Обслуживание сервера

Кодировки

Бывает такое, что на исходном сервере кодировка одна (например latin1), а на конечном мы хотим другую (UTF8).

Мы по глупости сдампили и загругрузили все на новый сервер и программы вроде как работают, но вот сама панелька mysql работает неверно. Данные программами уже изменены и казалось-бы все потеряно, но:

# Делаем дамп с нашей работающей базы (она в utf, но мы обманем)
mysqldump --default-character-set=latin1 -u root -p base>base.sql
# лезем в base.sql и видим, что теперь там все ок,
# только надо изменить все настройки latin1 в utf8
 
# Создаем новую базу и грузим туда файло
echo "create database base2 CHARACTER SET utf8 COLLATE utf8_unicode_ci;"|mysql -u root -p
mysql -u root -p < base.sql

mysqlcheck

mysqlcheck — утилита для проверки и восстановления базы.

Начиная с версии MySQL 3.23.38 можно применять новый инструмент для проверки и восстановления MyISAM-таблиц. Отличие mysqlcheck от myisamchk состоит в том, что утилита mysqlcheck должна использоваться при работающем сервере mysqld, в то время как myisamchk — при остановленном. Преимущество же заключается в том, что теперь не нужно останавливать сервер для проверки или восстановления таблиц.

Утилита mysqlcheck использует соответствующие команды: CHECK, REPAIR, ANALYZE и OPTIMIZE удобным для пользователя образом.

Существует три альтернативных способа запуска mysqlcheck:

mysqlcheck [OPTIONS] database [tables]
mysqlcheck [OPTIONS] --databases DB1 [DB2 DB3...]
mysqlcheck [OPTIONS] --all-databases

Таким образом, утилита может использоваться подобно mysqldump по отношению к выбранным базам данных и таблицам.

Оптимизация (vacuum)

# Почистить "дырки" (дефрагментация), обновить статистику и отсортировать индексы:
OPTIMIZE TABLE имя_таблицы;
# Апдейт статистики оптимизатора:
ANALYZE TABLE имя_таблицы;

Инструменты командной строки:

#дефрагментация
# При отключенной базе
myisamchk --quick --check-only-changed --sort-index --analyze
# При работающей
mysqlcheck --repair --analyze --optimize --all-databases --auto-repair
 
# Рекомендуется регулярно выполнять:
isamchk -r --silent --sort-index -O sort_buffer_size=16M db_dir/*.ISM
myisamchk -r --silent --sort-index  -O sort_buffer_size=16M db_dir/*.MYI