Терминал и MySQL

Обращение к MySQL из Unix Shell

Для обращения к MySQL служит команда mysql

mysql --host=хост --port=порт --user=логин -p имя_базы_данных
При этом программа спросит пароль. Если вам нужно автоматизировать это действие, вместо ключа -p можно указать —password=ваш_пароль.

имя_базы_данных указывать необязательно

создание базы данных

CREATE DATABASE mybase;

создание аккаунта, уровни доступа MySQL

Для упрощения создания пользователя можно разрешить ему все операции с базой. При этом mybase — название БД, mybaseuser — имя пользователя, localhost — хост(ы) с которых разрешено обращение данного пользователя к БД.

GRANT ALL ON mybase.* TO mybaseuser@localhost IDENTIFIED BY 'password';
FLUSH PRIVILEGES;
insert into tables_priv (Host,Db,User,Table_name,Table_priv) values ('%','dbname','userlogin','TableName','Select');

Если необходима более тонкая настройка доступа, то вместо ALL необходимо через запятую указать права, например разрешить пользователю только читать или только записывать данные, или ограничить возможность удаления базы данных (что иногда бывает очень неприятно), полный список прав доступа MySQL здесь.

Смена пароля пользователя
Метод 1
:

mysql -u root
mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('newpass');

Метод 2:

mysqladmin -u root password "newpass"

или если пароль для пользователя root уже задан:

mysqladmin -u root password oldpass "newpass"

Метод 3:

mysql -u root
mysql> use mysql;
mysql> UPDATE user SET Password = PASSWORD('newpass') WHERE User = 'root';
mysql> FLUSH PRIVILEGES;

список баз данных MySQL

СУБД MySQL может хранить несколько баз данных. Чтобы посмотреть их список:

SHOW DATABASES;

работа с базой MySQL

Чтобы приступить к работе с нужной базой, её нужно выбрать:

USE mybase;

удаление базы данных MySQL

Удалить базу данных так же просто как и создать:

DROP DATABASE mybase;

список таблиц MySQL

Внутри базы данных находятся… …таблицы. 🙂 Чем-то это они похожи на обычные электронные таблицы. После выбора базы данных (USE) просмотреть список таблиц в этой базе можно так:

SHOW TABLES;

просмотр структуры таблицы MySQL

Каждая таблица имеет собственную структуру (заданный набор столбцов). Просмотреть структуру таблицы можно так:

DESCRIBE tablename;
SHOW TABLE STATUS;

создание структуры таблицы MySQL

Таблицы создаются с помощью (этот абстрактный пример работать не будет, он здесь просто для упрощения понимания процесса создания таблицы:

CREATE TABLE tablename (переменные тип(размер));

А вот это уже настоящий работающий пример:

CREATE TABLE test_innodb (
id int(11) NOT NULL auto_increment,
PRIMARY KEY (id) -- основной ключ
) TYPE=InnoDB;

И этот тоже:

CREATE TABLE categories (
catid INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
category VARCHAR(100) NOT NULL
);
CREATE TABLE items (
itemid INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
catid INT,
FOREIGN KEY (catid) REFERENCES categories (catid), -- внешний ключ
itemname VARCHAR(100) NOT NULL, -- не может быть не заданно
itemdesc VARCHAR(255) NOT NULL,
imageurl VARCHAR(100),
itemcounter INT DEFAULT '1', -- значение по умолчанию '1'
access INT
);

Здесь нужно обратить внимание на понятия основного ключа (PRIMARY KEY), определяющего однозначно каждую строку таблицы и внешнего ключа (FOREIGN KEY), определяющего зависимость данных в одной таблице от данных в другой таблице.

типы таблиц MyISAM и innoDB в MySQL

На Unix системах по умолчанию создаются типы таблиц MyISAM. Они вполне подходят для баз данных с малой нагрузкой, таких, как веб-проекты. Однако для крупных информационных систем предпочтительнее использовать тип innoDB, преодалевающий множество ограничений, например позволяющий создавать базы данных, по размеру большие, чем максимальный размер файла на файловой системе. Так же innoDB позволяет блокировать (LOCK) не всю таблиу, а отдельные строки, что бывает просто необходимо в случае когда система работает в режиме 24/7/365, например для того чтобы база не «подвисала» при создании резервной копии. Выбор типа хранения для таблиц происходит на этапе создания таблицы:

CREATE TABLE table_name (...) ENGINE=MyISAM или innoDB

типы данных MySQL

BOOL истина или ложь, TRUE/FALSE или 1/0.

TINYINT очень маленькие целые числа от -128 до 127 или от 0 до 255.

SMALLINT маленькие целые числа от -32768 до 32767 или от 0 до 65535.

INT целые числа от -2147483648 до 2147483647 или от 0 до 4294967295.

BIGINT большие целые числа от -9223372036854775808 до 9223372036854775807 или от 0 до 18446744073709551615.

DOUBLE числа с плавающей запятой от -1.7976931348623157E+308 до -2.2250738585072014E-308, 0, от 2.2250738585072014E-308 до 1.7976931348623157E+308.

TIME время от ‘-838:59:59’ до ‘838:59:59’.

DATE дата от ‘1000-01-01’ до ‘9999-12-31’.

TIMESTAMP метка времени (время и дата), в секундах с 1970-01-01. Удобно для INSERT и UPDATE. По умолчанию первая колонка TIMESTAMP в таблице автоматически записывает время и дату операции, если в самой операции не указано иначе. В любую колонку TIMESTAMP можно записать текущие дату и время, присвоив NULL.

VARCHAR(n) строка из n символов.

полный список типов данных MySQL здесь

добавление записей MySQL

Добавлять записи (они же строки, кортежи) очень просто:

INSERT INTO mytable (a,b,c) VALUES ('1','2','3');

Вот примеры к таблицам, созданным в примере выше:

INSERT INTO test_innodb (id) VALUES ('1');
INSERT INTO categories (catid,category) VALUES ('1','Компьютеры');
INSERT INTO items (itemid, catid, itemname,
itemdesc, imageurl, access) VALUES
('1','1','Надпись 1','Коменn к надписи 1',
'http://hostlip.ru/gallery/images/0061024.jpg',
'1');

выборка записей MySQL

Для выборок из баз данных используется самый часто используемый оператор в MySQL. В простейшем виде для выбора всех данных из таблицы он выглядит так:

SELECT * FROM items;

Однако в работе обычно таблицы достаточно большие, и выборка всех данных занимает достаточное время, поэтому выбранные данные ограничивают нужными условиями, например так:

SELECT * FROM items WHERE itemname = 'Apple Mac Book Pro';

Из найденных и выдаваемых данных можно исключить всё лишнее, например если суть запроса в том чтобы спросить у базы данных только название и описание, без указания всех остальных деталей, запрос будет такой:

SELECT itemname,itemdesc FROM items WHERE itemname = 'Apple Mac Book Pro';

Количество записей, показанных в ответе можно ограничить (например 10 записями):

SELECT * FROM items LIMIT 10;

Когда в базе находится много записей, вывод можно так же упорядочивать (сортировать) в различных порядках по разным колонкам.

При перекрестной выборке из нескольких таблиц пишем условие соответствия записей по ключу:

SELECT category,itemname,itemdesc FROM items,categories WHERE categories.catid=items.catid;

При выборке поля типа TIMESTAMP конвертируются в строковое значение. Чтобы этого избежать, используем UNIX_TIMESTAMP():

SELECT UNIX_TIMESTAMP(date) FROM table;

удаление записей MySQL

Как бы это странно не звучало, чтобы удалить записи, их нужно выбрать с помощью WHERE. Делается это так:

DELETE FROM users WHERE user = 'coolhacker';

изменение записей MySQL

Для изменения тоже нужно с помощью WHERE выбрать строки(у), которые(ую) нужно изменить. Полезный пример (работает от root), для изменения пароля пользователя:

UPDATE mysql.user SET Password=password('paSSwoRD') WHERE User='username';

Еще один пример, с таблицей, описанной выше:

UPDATE items SET itemdesc='Клёвый ноутбук', itemname='Mac Book Pro'
WHERE itemname='Apple Mac Book Pro';

изменение структуры таблицы MySQL

Иногда структуру созданную с помощью CREATE TABLE нужно изменить. Проще всего это сделать на пустой таблице, иначе нужно смотреть чтобы в итоге преобразования не потерялись какие-то нужные данные. В любом случае, если вы делаете это первый раз, создайте заранее резервную копию базы. Изменение структуры:

Переименовать таблицу:

ALTER TABLE myfirsttable RENAME mysecondtable;

Переименовать столбец:

ALTER TABLE mytable CHANGE a b INTEGER;

Добавить новый столбец TIMESTAMP с именем mytimestamp:

ALTER TABLE mytable ADD mytimestamp TIMESTAMP;

Удалить столбец:

ALTER TABLE mytable DROP COLUMN notneeded;

Изменить тип столбца a INTEGER на TINYINT NOT NULL (оставляя имя прежним) и изменить тип столбца b с CHAR(10) на CHAR(20) с переименованием его с b на c:

ALTER TABLE mytable MODIFY a TINYINT NOT NULL, CHANGE b c CHAR(20);

дамп базы данных MySQL

Дамп базы созраняется утилитой коммандной строки mysqldump (в Unix Shell):

mysqldump --quick -u<логин> -p -h<хост> -P<порт> <база> > database.sql

Дамп всех баз данных одновременно (с блокировкой таблиц на время дампа):

mysqldump -h<хост> -uroot -P<порт> -p<пароль> --all-databases --quick --lock-tables --extended-insert > backupalldb.sql

При ошибке ‘Out Of Memory’, возникающей как правило из за ограниченности ресурсов , нужно добавить ключик —quick

восстановление базы данных MySQL из дампа

Восстановление базы из дампа происходит через Unix Shell, с использованием утилиты mysql:

mysql -u<логин> -p -h<хост> -P<порт> -D <база> < database.sql

Бывает при восстановлении дампа получаются ошибки, например такие:

/usr/local/mysql/bin/mysql -D db_test --password=secret_passowrd --user=user < /root/test.sql ERROR 1064 at line 161: You have an error in your SQL syntax near 'option varchar(50) NOT NULL default , ordering int(11) NOT NULL default '0',' at line 9

Обычно это означает что какое-то имя столбца MySQL принял за ключевое слово, в этом случае нужно отредактировать дамп на указанной строчке (161) например с помощью Vi: имя столбца (в данном примере option) нужно заключить в `обратные кавычки`. Чтобы этого не происходило можно было при создании дампа запускать mysqldump с ключем --quote-name

просмотр процессов в MySQL

Иногда бывает непонятно что делает база данных, повисла ли она, или там проходят какие-то операции, посмотреть не превышен ли лимит на количество соединений с базой:

SHOW FULL PROCESSLIST;

просмотр текущей конфигурации MySQL сервера

SHOW VARIABLES;

Оптимизация таблиц и запросов

http://host.ru/support/articles/mysql-optimization.html

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

Визуализация структур данных MySQL

http://dev.mysql.com/downloads/workbench/

unset LANG
unset LC_ALL
mysql-workbench
INSERT из SELECT

INSERT NewTable (nrwdata) SELECT olddata FROM OldTable;

Подробная инструкция по синтаксису MySQL доступна на сайте для разработчиков MySQL

Оставьте комментарий

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

Этот сайт использует Akismet для борьбы со спамом. Узнайте, как обрабатываются ваши данные комментариев.