Перейти к содержанию

mysql

Дополнительный конфигурационный файл

  • --defaults-extra-file — в дополнение к стандартным конфигам прочитать ещё и указанный файл
  • --default-file — вместо чтения стандартных конфигов читать только указанный файл

С помощью параметра --defaults-extra-file можно указать дополнительный файл конфигураций. В таком файле можно например разместить логин и пароль. Разные утилиты (mysql, mysqldump) читают настройки из соответствующего раздела. Эти утилиты считаются клиентами, поэтому читают секцию [client] 

[client]
user = database_user
password = database_pass

[mysql]
database = my_database

[mysqldump]
single-transaction
skip-lock-tables
no-tablespaces

[mysqldump-testing]
no-create-info

[mysqldump-compact]
compact     # компактный режим - оставляет только INSERT
extended-insert      # генерировать многострочный вариант для INSERT
skip-extended-insert # отключить extended-insert


mysql --defaults-extra-file=/path/to/mysql.extra.ini database

С помощью --defaults-group-suffix=str можно указать суффикс для дополнительной читаемой группы. 

[mysqldump]

[mysqldump_other]
no-tablespaces


# применит настройки из секции [mysqldump]
mysqldump  --defaults-extra-file=my.cnf  database

# применит настройки из секции [mysqldump] и дополнительно из [mysqldump_other]
mysqldump  --defaults-extra-file=my.cnf  --defaults-group-suffix=_other  database

Вызов sql и вывод результата в cli

mysql  -u user  -p database  --execute="SELECT * from users;"
mysql  --defaults-extra-file=mysql.extra.ini  database  --execute="SELECT * from users;"

Создание БД

CREATE DATABASE mydatabase;
# создание БД с указанием кодировки и алгоритма сравнения
CREATE DATABASE mydatabase CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
# прописать права к созданной таблице для пользователя myuser
GRANT ALL PRIVILEGES ON `mydatabase`.* TO 'myuser'@'localhost';
# узнать кодировку и алгоритм сравнения
USE my_database;
SELECT @@character_set_database, @@collation_database;

CHARSET - кодировка для хранения данных в таблице. COLLATION определяет способ хранения и сравнения данных в БД

Полезные ссылки:

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

CREATE USER 'db_user'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';
GRANT ALL PRIVILEGES ON database.* TO db_user@localhost;

-- Узнать текущие привилегии
SHOW GRANTS FOR db_user@localhost;

Настройка pager

Позволяет менять тип вывода данных. Например, для показа результата SELECT ... в удобном виде вызвать: 

# S - не переносить строки
# X - не очищать экран при отрисовке
# F - автоматически закрыть если содержимое влезает на экран
mysql> pager less -SXF

Полезные параметры для mysqldump

  • --compact компактный режим
  • --no-create-info не добавлять CREATE TABLE ...
  • --insert-ignore в файл вместо INSERT INTO будет прописано INSERT IGNORE INTO
  • --extended-insert для вставки множества значений использовать синтаксис множественной вставки 
    INSERT INTO t VALUES (v1), (v2), (v3);
  • --skip-extended-insert - отключение режима --extended-insert

Получение информации из sql дампа

Если есть огромный sql файл, то полностью восстанавливать его не хочется. Данные можно достать используя grep: 

# символ ` надо экранировать, так как он спец символ для bash
grep "INSERT INTO \`b_option\`" dump.sql > b_option_dump.sql"

Оператор CREATE TABLE обычно занимает несколько строк, так что надо указать grep выводить дополнительно строки после найденного результата (параметр -A, --after-context): 

grep -A 15 "CREATE TABLE \`b_uts_tasks_task\`" dump.sql

Разворачивание бекапа

mysql в docker

docker exec -i mysql sh -c "exec mysql -uroot -p'Пароль_c_cимволами_$' DATABASE" < file-on-host.sql

Размер таблиц

  • Размер конкретной таблицы 
    SELECT ROUND(((data_length + index_length) / 1024 / 1024), 2) AS `Size in MB`
FROM information_schema.TABLES
WHERE table_schema = 'database_name' AND table_name = 'table_name';
  • Список таблиц, отсортированных по размеру 
    SELECT
    table_name AS `Table`,
    ROUND(((data_length + index_length) / 1024 / 1024), 2) AS `Size in MB`
FROM information_schema.TABLES
WHERE table_schema = 'database_name'
ORDER BY (data_length + index_length) DESC;

Настройки

Некоторые настройки mysql можно менять во время работы без перезагрузки сервера с помощью команды SET. Это настройки сбросятся при перезагрузке сервера если они не прописаны в конфигурационых файлах.

Использование SET PERSIST изменяет глобальную переменную и записывает значение в файл mysqld-auto.

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

Для получения/установки значений глобальных параметров можно использовать @@ (документация): 

SET GLOBAL max_connections = 1000;
SET @@GLOBAL.max_connections = 1000;

Сессионные переменные работают как пользовательские, для работы с ними можно использовать @:

  • SHOW VARIABLES показ всех переменных
  • @@variable - это синоним для @@GLOBAL.varaible