Шпаргалка MySQL

Изучение настоящей шпаргалки не сделает вас мастером SQL, но позволит получить общее представление об этом языке программирования и возможностях, которые он предоставляет. Рассматриваемые в шпаргалке возможности являются общими для всех или большинства диалектов SQL.

Создание дампа
mysqldump --all-databases > dump.sql - дамп всех баз
mysqldump --databases db1 db2 db3 > dump.sql - дамп нескольких указанных бд
mysqldump -u USER -pPASSWORD DATABASENAME > dump.sql - дамп
mysqldump -u USER -pPASSWORD DATABASENAME | gzip > /path/to/outputfile.sql.gz - дамп с упаковкой в gzip
mysqldump -u USER -pPASSWORD DATABASENAME | gzip > 'date+/path/to/outputfile.sql.%Y%m%d.%H%M%S.gz' - дамп с упаковкой в gzip и датой создания
mysqldump --no-data - u USER -pPASSWORD DATABASENAME > /path/to/file/schema.sql - копируем структуру БД без данных
mysqldump -u USER -pPASSWORD DATABASENAME TABLE1 TABLE2 TABLE3 > /path/to/file/dump_table.sql - если нужно сделать дамп только одной или нескольких таблиц

Пример бекапа в кроне:

0 0 * * * /usr/bin/mysqldump -u root -pPASSWORD DBNAME | gzip > /home/user/backup_mysql/DBNAME_`date "+\%d-\%m-\%Y_\%H:\%M:\%S"`.sql

Восстановление из дампа
mysql -u USER -pPASSWORD DATABASENAME < /path/to/dump.sql - восстановление БД из дампа
gunzip < /path/to/outputfile.sql.gz | mysql -u USER -pPASSWORD DATABASENAME - восстановление БД из дампа в архиве .gz
mysql -u USER -pPASSWORD < /path/to/dump.sql - восстановление нескольких БД из дампа

Восстановление из дампа в консоли MySQL

create database mydb;
use mydb;
source /path/to/db_backup.dump;

Консоль MySQL CLI
mysql -u root -p - залогиниться в консоль mysql
show databases; - показать все БД
use [database]; - выбрать БД
show tables; - показать таблицы БД
show index from [table]; - показать все индексы таблицы
describe [table]; - структура таблицы
create database mydb; - создать БД mydb
show status;
show processlist;
CREATE TABLE [table] ([column] VARCHAR(120), [another-column] DATETIME); - создать таблицу
Adding a column: ALTER TABLE [table] ADD COLUMN [column] VARCHAR(120); - добавить столбец в таблицу
ALTER TABLE [table] ADD COLUMN [column] int NOT NULL AUTO_INCREMENT PRIMARY KEY; - добавить столбец в таблицу с уникальным автоинкрементным ID
INSERT INTO [table] ([column], [column]) VALUES ('[value]', '[value]'); - вставить значение в столбец таблицы
SELECT * FROM [table]; - вывести значение из таблицы
SELECT * FROM [table] ORDER BY ID DESC LIMIT 10 - вывести 10 последних значений из таблицы
EXPLAIN SELECT * FROM [table];
SELECT [column], [another-column] FROM [table]; - вывести несколько столбцов из таблицы
SELECT COUNT([column]) FROM [table]; - посчитать записи
SELECT *, (SELECT COUNT([column]) FROM [table]) AS count FROM [table] GROUP BY [column]; - Посчитать и выбрать сгруппированные записи
SELECT * FROM [table] WHERE [column] = [value]; (Selectors: <, >, !=; combine multiple selectors with AND, OR) - выбрать определенные записи
SELECT * FROM [table] WHERE [column] LIKE '%[value]%'; - Выбрать записи с определенным значением
SELECT * FROM [table] WHERE [column] LIKE '[value]%'; - Выбрать записи с начинающиеся с определенного значения
SELECT * FROM [table] WHERE [column] LIKE '[val_ue]'; - Выбрать записи начинающиеся с val и заканчивающиеся на ue
SELECT * FROM [table] WHERE [column] BETWEEN [value1] and [value2]; - Выбрать записи между значениями
SELECT * FROM [table] WHERE [column] ORDER BY [column] ASC LIMIT [value]; (Order: DESC, ASC) - Выбрать с сортировкой по столбцу по возрастанию или убыванию
UPDATE [table] SET [column] = '[updated-value]' WHERE [column] = [value]; - Обновление записей
DELETE FROM [table] WHERE [column] = [value]; - Удаление заспией
DELETE FROM [table]; - Удалить все записи из таблицы, не дропая талицу, при этом каунтер сбрасывается
truncate table [table]; - Удалить все записи из таблицы
ALTER TABLE [table] DROP COLUMN [column]; - Удалить столбцы из таблицы
DROP TABLE [table]; - Уничтожить таблицу
DROP DATABASE [database]; - Уничтожить БД

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

CREATE USER 'USERNAME'@'localhost' IDENTIFIED BY 'USERPASSWORD';
GRANT ALL PRIVILEGES ON DBNAME . * TO 'USERNAME'@'localhost';
FLUSH PRIVILEGES;
SELECT User FROM mysql.user; - посмотреть всех пользователей

Общие
mysqlshow -u USER -pPASSWORD - просмотр списка всех БД
mysqladmin -u USER -pPASSWORD create NEWDATABASE - создать БД NEWDATABASE
mysqlshow -u USER -pPASSWORD DATABASENAME - список всех таблиц БД DATABASENAME

Остальное полезное
du -sh /var/lib/mysql | sort -gr - узнать размер всех БД
Запрос выведет размер указанной БД:

SELECT table_schema AS "DATABASE_NAME", ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS "Размер в Мб" FROM information_schema.TABLES GROUP BY table_schema

mytop - удобный диспетчер задач для MySQL, как htop, в нем можно убивать процессы в MySQL

В крон добавить 2 задания:

20 1 * * * /usr/bin/mysqlcheck -u root --auto-repair --check --all-databases >/dev/null 2>&1

 

40 1 * * * /usr/bin/mysqlcheck -u root --auto-repair --optimize --all-databases >/dev/null 2>&1

 

Для таблиц в InnoDB при создании дампа надо добавлять --single-transaction, это гарантирует целостность данных бекапа. Для таблиц MyISAM это не актуально, ибо они не поддерживают транзакционность.

В большинстве случаев лучше использовать движок InnoDB.

	MyISAM	InnoDB
Полнотекстовый поиск	+	> 5.6.4
Транзакции	-	+
Блокировка записи	Таблицы	Строки

MyISAM стоит использовать, если нужен полнотекстовый поиск до версии 5.6. Также этот движок подойдет, когда в таблице очень мало записей и большое количество чтений.

Во всех остальных случаях нужно использовать InnoDB.

Важно под каждую базу на боевом сервере создавать своего пользователя.

Кодировка базы может быть любой, если она UTF8.

В php лучше забыть про сильно устаревшее расширение mysql и по-возможности использовать pdo или mysqli.

Без особой нужды не стоит открывать MySQL наружу. Вместо этого можно сделать проброс портов:

ssh -fNL LOCAL_PORT:localhost:3306 REMOTE_USER@REMOTE_HOST