MySQL — FAQ

Автор: | 11.09.2014

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

Links

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

Обязательно добавить в 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

Команда Описание Синтаксис
SHOW BINARY LOGS Lists the binary log files on the server SHOW BINARY LOGS;
SHOW BINLOG EVENTS Shows the events in the binary log SHOW BINLOG EVENTS [IN ‘log_name’] [FROM pos] [LIMIT [offset,] row_count];
SHOW CHARACTER SET Shows all available character sets SHOW CHARACTER SET [LIKE ‘pattern’ | WHERE expr];
SHOW COLLATION Lists collations supported by the server SHOW COLLATION [LIKE ‘pattern’ | WHERE expr];
SHOW COLUMNS Displays information about the columns in a given table SHOW [FULL] COLUMNS {FROM | IN} tbl_name [{FROM | IN} db_name] [LIKE ‘pattern’ | WHERE expr];
SHOW CREATE DATABASE Shows the CREATE DATABASE statement that creates the given database SHOW CREATE {DATABASE | SCHEMA} db_name;
SHOW CREATE FUNCTION This statement is similar to SHOW CREATE PROCEDURE but for stored functions SHOW CREATE FUNCTION func_name;
SHOW CREATE PROCEDURE Создает процедуру и показывает статус выполнения SHOW CREATE PROCEDURE proc_name;
SHOW CREATE TABLE Shows the CREATE TABLE statement that creates the given table SHOW CREATE TABLE tbl_name;
SHOW CREATE VIEW This statement shows a CREATE VIEW statement that creates the given view SHOW CREATE VIEW view_name;
SHOW DATABASES Lists the databases on the MySQL SHOW {DATABASES | SCHEMAS} [LIKE ‘pattern’ | WHERE expr];
SHOW ENGINE Displays log or status information about a storage engine SHOW ENGINE engine_name {LOGS | STATUS };
SHOW ENGINES Displays status information about the server’s storage engines SHOW [STORAGE] ENGINES;
SHOW ERRORS Similar to SHOW WARNINGS, except that instead of displaying errors, warnings, and notes, it displays only errors SHOW ERRORS [LIMIT [offset,] row_count];
SHOW FUNCTION CODE Similar to SHOW PROCEDURE CODE but for stored functions SHOW FUNCTION CODE func_name;
SHOW FUNCTION STATUS Similar to SHOW PROCEDURE STATUS but for stored functions SHOW FUNCTION STATUS [LIKE ‘pattern’ | WHERE expr];
SHOW GRANTS Lists the GRANT statement or statements that must be issued to duplicate the privileges that are granted to a MySQL user account SHOW GRANTS [FOR user];
SHOW INDEX Returns table index information SHOW {INDEX | INDEXES | KEYS} {FROM | IN} tbl_name [{FROM | IN} db_name] [WHERE expr];
SHOW INNODB STATUS Synonym for SHOW ENGINE INNODB STATUS SHOW INNODB STATUS;
SHOW LOGS Synonym for SHOW ENGINE BDB LOGS SHOW [BDB] LOGS;
SHOW MASTER STATUS Provides status information about the binary log files of the master SHOW MASTER STATUS;
SHOW MUTEX STATUS Displays InnoDB mutex statistics SHOW MUTEX STATUS;
SHOW OPEN TABLES Lists the non-TEMPORARY tables that are currently open in the table cache SHOW OPEN TABLES [{FROM | IN} db_name] [LIKE ‘pattern’ | WHERE expr];
SHOW PRIVILEGES Shows the list of system privileges that the MySQL server supports SHOW PRIVILEGES;
SHOW PROCEDURE CODE Displays a representation of the internal implementation of the named stored procedure SHOW PROCEDURE CODE proc_name
SHOW PROCEDURE STATUS Returns characteristics of a stored procedure, such as the database, name, type, creator, creation and modification dates, and character set information SHOW PROCEDURE STATUS [LIKE ‘pattern’ | WHERE expr];
SHOW PROCESSLIST Shows you which threads are running SHOW [FULL] PROCESSLIST;
SHOW PROFILE Display profiling information SHOW PROFILE;
SHOW PROFILES Display profiling information SHOW PROFILE;
SHOW SLAVE HOSTS Displays a list of replication slaves currently registered with the master SHOW SLAVE HOSTS;
SHOW SLAVE STATUS Provides status information on essential parameters of the slave threads SHOW SLAVE STATUS;
SHOW STATUS Provides server status information SHOW [GLOBAL | SESSION] STATUS [LIKE ‘pattern’ | WHERE expr];
SHOW TABLE STATUS Provides a lot of information about each non-TEMPORARY table SHOW TABLE STATUS [{FROM | IN} db_name] [LIKE ‘pattern’ | WHERE expr];
SHOW TABLES Lists the non-TEMPORARY tables in a given database SHOW [FULL] TABLES [{FROM | IN} db_name] [LIKE ‘pattern’ | WHERE expr];
SHOW TRIGGERS Lists the triggers currently defined for tables in a database SHOW TRIGGERS [{FROM | IN} db_name] [LIKE ‘pattern’ | WHERE expr];
SHOW VARIABLES Shows the values of MySQL system variables SHOW [GLOBAL | SESSION] VARIABLES [LIKE ‘pattern’ | WHERE expr];
SHOW WARNINGS Shows the error, warning, and note messages that resulted from the last statement that generated messages in the current session SHOW WARNINGS [LIMIT [offset,] row_count];

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

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

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

Добавить комментарий

Ваш e-mail не будет опубликован. Обязательные поля помечены *