Skip to content

Latest commit

 

History

History
200 lines (156 loc) · 10.1 KB

10001.mysql.md

File metadata and controls

200 lines (156 loc) · 10.1 KB
Raw

mysql - Справочник запросов

Работа с БД

Посмотреть список всех БД:

SHOW DATABASES;

Выполнение с SQL файла:

SOURCE importtb.sql;

Создание и удаление БД:

CREATE DATABASE university CHARACTER SET utf8 COLLATE utf8_general_ci;;
USE university;
DROP DATABASE university;

Создание у удаление таблиц:

CREATE TABLE instructor (
  ID INT(5) NOT NULL AUTO_INCREMENT,
  name VARCHAR(20) NOT NULL,
  dept_name VARCHAR(20),
  salary NUMERIC(8,2),
  PRIMARY KEY (ID)
);
DROP TABLE instructor;

Информация о таблице:

DESCRIBE table_1;

Работа с записями:

SELECT studentID, FirstName, LastName, FirstName + ' ' + LastName AS FullName FROM student;
SELECT DISTINCT FirstName, LastName, sat_score FROM student ORDER BY LastName DESC LIMIT 10;
SELECT studentID, FirstName, LastName, sat_score, rcd_updated FROM student WHERE LastName LIKE 'Monique%' OR LastName NOT LIKE '%Greene';
SELECT * FROM course WHERE course_id LIKE 'CS-___';                  # _ - Один символ % - Любое поличество
SELECT * FROM instructor WHERE salary BETWEEN 50000 AND 100000;

INSERT table_1 (“column_1”, “column_2”, “column_3”) VALUES (“parameter_1”, “parameter_2”, “parameter_3”); 
INSERT INTO `table_one` (id, parent_id, text) SELECT id, parent_id, option FROM `table_two`;
UPDATE table_1 SET column1 = value1,column2 = value2 WHERE Id = 4;
DELETE FROM table_1

Работа со структурой таблиц:

ALTER TABLE table_1 RENAME parameter_1 TO parameter_2;  # изменить в таблице table_1 название поля parameter_1 на parameter_2.
ALTER TABLE table_1 ADD parameter_1 INT (3);            # добавить в таблице table_1 поле с названием parameter_1.
ALTER TABLE table_1 MODIFY parameter_1 INT (3);         # изменить в таблице table_1 поле с названием parameter_1.
ALTER TABLE table_1 DROP parameter_1;                   # удалить в таблице table_1 поле с названием parameter_1.

Групировка:

SELECT column_name, COUNT(*) FROM table_name GROUP BY column_name;
SELECT column_name , COUNT(*) FROM table_name GROUP BY column_name HAVING COUNT(*) > value;

Управление пользователями и выдача и выключение прав на БД

Список привиллегий:

  • ALL PRIVILEGES — как мы уже увидели ранее, данный набор прав предоставляет пользователю MySQL полный доступ к определенной базе данных (если не выбрана ни одна база данных, предоставляется глобальный доступ к системе)
  • CREATE — позволяет пользователю создавать новые таблицы или базы данных
  • DROP — позволяет пользователю удалять таблицы или базы данных
  • DELETE — позволяет пользователю удалять строки из таблиц
  • INSERT — позволяет пользователю вставлять строки в таблицы
  • SELECT — позволяет пользователю выполнять команду SELECT для чтения данных из базы
  • UPDATE — позволяет пользователю обновлять строки таблицы
  • GRANT OPTION — позволяет пользователю предоставлять или отзывать права других пользователей

Чтение и пользователей и привиллегий:

SELECT user,host,plugin,authentication_string,password_expired FROM mysql.user;
SELECT host, user, password FROM mysql.user\G;

SHOW GRANTS FOR 'username'@'localhost';

SELECT * FROM mysql.db;
SELECT * FROM mysql.tables_priv;
SELECT * FROM mysql.columns_priv;
SELECT * FROM information_schema.schema_privileges;
SELECT * FROM information_schema.table_privileges;
SELECT * FROM information_schema.column_privileges;
SELECT * FROM information_schema.column_privileges WHERE GRANTEE="'test_user'@'localhost'";

Создание пользователя и конфигурирование привиллегий:

CREATE USER 'username1'@'localhost' IDENTIFIED BY 'password';
CREATE USER 'username3'@'%'IDENTIFIED WITH mysql_native_password BY 'password';

ALTER USER 'root'@'localhost' IDENTIFIED BY 'Passw0rdC5';
GRANT ALL PRIVILEGES ON *.* TO 'test'@'localhost'
GRANT SELECT, INSERT, UPDATE, DELETE ON `some_db`.* TO 'some_user'@'somehost' WITH GRANT OPTION;
GRANT SELECT (id, user_name), UPDATE (user_name) ON `test_db`.`table_users` TO 'test_user'@'localhost';
REVOKE SELECT ON `somedb`.* FROM 'someuser'@'somehost';
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'someuser'@'somehost';
FLUSH PRIVILEGES;

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

DROP USER 'some_user'@'%';

Бэкапирование

mysqldump -u root -p db_name > /db_backup.sql                             # Бэкап отдельной БД
mysqldump -u root -p --no-data db_name > /db_backup.sql                   # Бэкап отдельной БД без данных (Только структура)
mysqldump -u root -p --databases db_1 db_2 > dbs1_backup.sql              # Бэкап нескольких БД
mysqldump -u root -p --all-databases > db_all_backup.sql                  # Бэкап всех БД
mysqldump -u root -p db_name table_name > /db_table_backup.sql            # Бэкап отдельной таблицы БД

mysqldump -u root -p -v db_name | gzip --best > db_name.sql.gz            # Бэкап с архивированием.

Основные параметры:

  • --add-locks - Добавить команды LOCK TABLES перед выполнением и UNLOCK TABLE после выполнения каждого дампа таблицы (для ускорения доступа к MySQL).
  • --add-drop-table (--add-drop-database) - Добавить команду DROP TABLE (DROP DATABASE) перед каждой командой CREATE TABLE (CREATE DATABASE).
  • --all-databases - Произвести дамп всех баз данных. Аналогично опции --databases с указанием всех баз данных.
  • --compress - Использовать сжатие всей информации между клиентом и сервером, если они оба поддерживают сжатие.
  • --databases - Выполнить дамп нескольких баз данных. Обратите внимание на разницу в использовании: в этом случае таблицы не указываются. Все имена аргументов рассматриваются как имена баз данных. Оператор USE db_name; включается в вывод перед каждой новой базой данных.
  • --extended-insert - Использовать команду INSERT с новым многострочным синтаксисом (повышает компактность и быстродействие операторов ввода).
  • --force - Продолжать даже при получении ошибки SQL при выполнении дампа таблицы.
  • --no-create-db - не создавать БД (Не будет CREATE DATABASE в дампе)
  • --no-data - Не записывать информацию из строк таблицы.
  • --routines - Добавить к дампу импорт функций и процедур.
  • --verbose (-v) - Расширенный режим вывода.

Восстановление

mysql -u root -p db_name < /db_backup.sql                                 # Восстановить с дампа
mysql -u root -p --add-drop-table  db_name < /db_backup.sql               # Восстановить с дампа c удалением таблиц БД перед разворачиванием.

Выбор самых больших таблиц из MySQL

SELECT CONCAT(table_schema, '.', table_name),
       CONCAT(ROUND(table_rows / 1000000, 2), 'M')                                    rows,
       CONCAT(ROUND(data_length / ( 1024 * 1024 * 1024 ), 2), 'G')                    data,
       CONCAT(ROUND(index_length / ( 1024 * 1024 * 1024 ), 2), 'G')                   idx,
       CONCAT(ROUND(( data_length + index_length ) / ( 1024 * 1024 * 1024 ), 2), 'G') total_size,
       ROUND(index_length / data_length, 2)                                           idxfrac
FROM   information_schema.TABLES
ORDER  BY data_length + index_length DESC
LIMIT  50;
rows — количество записей в таблице
data — размер данных таблицы
idx — размер индекса таблицы
total_size — суммарные размер таблицы
idxfrac — часть размера индекса от общего размера таблицы

Выгрузка всех прав всех пользователей в виде SQL дампов

MYSQL_CONN="-uroot -ppassword"
mysql ${MYSQL_CONN} --skip-column-names -A -e"SELECT CONCAT('SHOW GRANTS FOR ''',user,'''@''',host,''';') FROM mysql.user WHERE user<>''" | mysql ${MYSQL_CONN} --skip-column-names -A | sed 's/$/;/g' > MySQLUserGrants.sql

-- Вытянуть все пароли пользователей:
mysqldump -u root mysql user > user_table_dump.sql

Готовые скрипты

Создание пользователя и БД под проект

CREATE USER 'project'@'%' IDENTIFIED BY '<your_secret_password>';
CREATE DATABASE projectdb CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_bin';
GRANT ALL PRIVILEGES ON projectdb.* TO 'project'@'%';
FLUSH PRIVILEGES;

Источник: https://ruhighload.com/Выбор+самых+больших+таблиц+из+mysql