Использование MySQL

Оглавление

Логин и пароль в консольном клиенте mysql

Явное указание логина и пароля

Клиент MySQL может подключаться с использованием явно указанного пароля:

$ mysql -u username -p pass

Использование файла с логином и паролем

Можно указать используемые по умолчанию настройки клиента в произвольном файле в следующем виде:

[client]
user = "whatever"
password = "whatever"
host = "whatever"

Для использования настроек по умолчанию из этого файла достаточно будет указать путь нему следующим образом:

$ mysql --defaults-file=файл.cnf

Использование файла ~/.mylogin.cnf

Можно настроить иcпользумый по умолчанию пароль в файле ~/.mylogin.cnf при помощи команды следующего вида:

$ mysql_config_editor set --login-path=client --host=localhost --user=root --password

Где client - имя секции. Секция client используется клиентом mysql по умолчанию.

Посмотреть пароли из файла ~/.mylogin.cnf в расшифрованном виде можно следующим образом:

$ my_print_defaults -s client

Для подключения клиентом MySQL с использованием другой секции, имя секции можно указать следующим образом:

$ mysql --login-path=client

Аутентификация через Unix-сокет

Имеется также возможность аутентификации без пароля при подключении через Unix-сокет. Для этого в настройках у клиента вместо традиционного плагина mysql_native_password должен быть выставлен плагин auth_socket. Для изменения способа аутентификации определённого пользователя можно воспользоваться одним из соотвествующих запросов:

ALTER USER user@localhost IDENTIFIED WITH mysql_native_password BY 'password';
ALTER USER user@localhost IDENTIFIED WITH auth_socket;

Просмотр списка активных транзакций

Просмотреть список активных транзакций можно выполнив в базе данных information_schema следующий запрос:

SELECT innodb_trx.trx_started,
       innodb_trx.trx_mysql_thread_id,
       processlist.host,
       processlist.db,
       processlist.user
FROM innodb_trx
JOIN processlist ON processlist.id = innodb_trx.trx_mysql_thread_id
ORDER BY innodb_trx.trx_started;

Просмотр количества записей в журнале откатов

Просмотреть количество элементов можно выполнив в базе данных information_schema следующий запрос:

SELECT count
FROM innodb_metrics
WHERE name = 'trx_rseg_history_len';

Или при помощи такого запроса:

SHOW ENGINE InnoDB STATUS;

В строке следующего вида:

History list length 2759

Просмотр объёма сегментов отката транзакций

Узнать объём сегментов отката транзакций в мегабайтах можно из базы данных information_schema при помощи запроса:

SELECT SUM(curr_size) * 16 / 1024 AS undo_space_mb
FROM xtradb_rseg;

Объём сегментов отката в буферном пуле в мегабайтах можно узнать из той же базы данных с помощью такого запроса:

SELECT COUNT(*) * 16 / 1024 AS size_mb
FROM innodb_buffer_page
WHERE page_type = 'UNDO_LOG';

Более полную информацию по остальным страницам можно получить следующим образом:

SELECT COUNT(*) cnt,
       COUNT(*) * 16 / 1024 size_mb,
       page_type
FROM innodb_buffer_page
GROUP BY page_type;

Типы страниц:

  • BLOB - несжатая страница BLOB,
  • EXTENT_DESCRIPTOR - страница дескриптора экстентов,
  • FILE_SPACE_HEADER - заголовок пространства файла,
  • IBUF_BITMAP - битовая карта буфера вставки,
  • IBUF_INDEX - индекс буфера вставки,
  • INDEX - узел двоичного дерева,
  • INODE - узел индекса,
  • SYSTEM - системная страница,
  • TRX_SYSTEM - данные транзакционной системы,
  • UNDO_LOG - страница журнала отмены,
  • UNKNOWN - неизвестно.

Источник:

Просмотр неактивных пользователей

Запрос для извлечения пользователей, которые никогда не подключались и не определили представление, подпрограмму, триггер или запись в планировщике задач:

SELECT DISTINCT m.user,
                m.host
FROM mysql.user m
LEFT JOIN performance_schema.accounts p ON m.user = p.user
  AND p.host LIKE m.host
LEFT JOIN information_schema.views is_v ON is_v.security_type = 'DEFINER'
  AND is_v.definer LIKE CONCAT(m.user, '@', m.host)
LEFT JOIN information_schema.routines is_r ON is_r.security_type = 'DEFINER'
  AND is_r.definer LIKE CONCAT(m.user, '@', m.host)
LEFT JOIN information_schema.events is_e ON is_e.definer LIKE CONCAT(m.user, '@', m.host)
LEFT JOIN information_schema.triggers is_t ON is_t.definer LIKE CONCAT(m.user, '@', m.host)
WHERE p.user IS NULL
  AND is_v.DEFINER IS NULL
  AND is_r.DEFINER IS NULL
  AND is_e.DEFINER IS NULL
  AND is_t.DEFINER IS NULL
ORDER BY user, host;

Источник:

Просмотр размеров таблиц и индексов

Для просмотра 10 самых крупных таблиц (вместе с индексами) можно воспользоваться запросом следующего вида, выполнив его в базе данных information_schema:

SELECT table_name, data_length + index_length AS s
FROM tables
GROUP BY table_name
ORDER BY s DESC
LIMIT 10;

Поиск таблиц без первичного ключа

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

SELECT tables.table_schema,
       tables.table_name
FROM tables
LEFT JOIN table_constraints ON tables.table_schema = table_constraints.table_schema
  AND tables.table_name = table_constraints.table_name
  AND table_constraints.constraint_type = 'PRIMARY KEY'
WHERE table_constraints.constraint_type IS NULL
  AND tables.table_schema NOT IN ('mysql', 'information_schema', 'performance_schema', 'sys')
  AND tables.table_type = 'BASE TABLE'
ORDER BY tables.table_schema,
         tables.table_name;

Источник: Bart Gawrych. Find tables without primary keys (PKs) in MySQL database

Просмотр необычных движков таблиц

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

SELECT engine,
       table_schema,
       table_name
FROM information_schema.tables
WHERE engine <> 'InnoDB'
  AND table_schema NOT IN ('mysql', 'performance_schema', 'information_schema');

Преобразование всех таблиц из MyISAM в InnoDB

$ mysql information_schema -BNe "SELECT CONCAT('ALTER TABLE \`', table_schema, '\`.\`', table_name, '\` ENGINE=InnoDB;') FROM tables WHERE engine = 'MyISAM' AND table_schema NOT IN ('mysql', 'performance_schema', 'information_schema');" | mysql

Поиск таблиц с секциями

Для вывода списка таблиц, поделённых на секции или подсекции, можно выполнить в базе данных information_schema следующий запрос:

SELECT DISTINCT table_schema, table_name
FROM partitions
WHERE partition_name IS NOT NULL
  OR subpartition_name IS NOT NULL;

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

ALTER TABLE <таблица> REMOVE PARTITIONING;

Выгрузка схемы базы данных

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

$ mysqldump --single-transaction --skip-comments --skip-add-drop-table --no-data db > db_schema.sql

Дополнительные материалы