Access denied for user ‘debian-sys-maint’@’localhost’

С проблемой можно столкнуться в результате некорректной установки MySQL. Полностью ошибка выглядит следующим образом:

mysql_upgrade: Got error: 1045: Access denied for user 'debian-sys-maint'@'localhost' (using password: YES) while connecting to the MySQL server

Суть проблемы состоит в том, что mysql_upgrade не может получить доступ к нашей базе под указанным в файле debian.cnf паролем. Если выполнить команду:

cat /etc/mysql/debian.cnf

Мы получить результат следующего вида:

# Automatically generated for Debian scripts. DO NOT TOUCH!
[client]
host     = localhost
user     = debian-sys-maint
password = n4aSHUP04s1J32X5
socket   = /var/run/mysqld/mysqld.sock
[mysql_upgrade]
user     = debian-sys-maint
password = n4aSHUP04s1J32X5
socket   = /var/run/mysqld/mysqld.sock
basedir  = /usr

Нас интересует параметр password. Копируем это пароль, после чего в MySQL необходимо выполнить запрос:

mysql> GRANT ALL PRIVILEGES ON *.* TO 'debian-sys-maint'@'localhost' IDENTIFIED BY 'n4aSHUP04s1J32X5';

С первого раза не сработало, MySQL ругался на то, что пароль не соответствует правилам безопасности. Тогда просто можно добавить к паролю любой символ и повторить команду. При этом не забудьте указать новый пароль в фале debian.cnf.

Для того чтобы имения вступили в силу, необходимо перезапустить MySQL.

Сброс пароля MySQL

В интернете достаточно много материалов про сброс пароля root в MySQL 5.7. Процесс достаточно простой и уже много раз описывался в сети, но связи с тем, что метод опробован на практике, решил записать последовательность своих действий. Все действия выполнялись в Ubuntu 16.04 и MySQL 5.7.

Проверим версию MySQL:

mysql --version
mysql  Ver 14.14 Distrib 5.7.16, for Linux (x86_64) using  EditLine wrapper

От версии пакета MySQL будет зависть SQL-запрос, который необходимо выполнить для изменения пароля. Далее останавливаем MySQL:

service mysql stop

Создаем каталог, устанавливаем на него права:

mkdir -p /var/run/mysqld
chown -R mysql /var/run/mysqld

Запускаем MySQL без загрузки grant tables, в целях безопасности отключаем сеть:

mysqld_safe --skip-grant-tables --skip-networking &

Теперь мы можем подключится к MySQL без использования пароля:

mysql -u root

Перезагрузим таблицы привилегий:

mysql> FLUSH PRIVILEGES;

Теперь осталось только изменить пароль root. Если у вас MySQL 5.7.6 или новее, MariaDB 10.1.20 или новее, используем следующую команду:

ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password';

Для версий MySQL 5.7.5 или старее, MariaDB 10.1.20 или старее, используем команду:

SET PASSWORD FOR 'root'@'localhost' = PASSWORD('new_password');

Результат выполнения команды должен выглядеть следующим образом:

Output
Query OK, 0 rows affected (0.00 sec)

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

mysql -u root -p

Таким образом, мы спросили пароль root и получили доступ к MySQL с правами администратора.

Удалить все таблицы из базы MySQL

Дата: 24.06.2016Метки:

Во время работы с MySQL может возникнуть необходимость удалить сразу все таблицы из базы. С такой задачей приходится сталкиваться достаточно редко. Поэтому чаще всего для удаления таблицы используют команду drop table, а затем через запятую перечисляют список всех таблиц. Когда нужно удалить не один десяток таблиц, данный метод будет не самым лучшим выбором.

drop table table1, table2, table3;

Поэтому если таблиц много, то удобнее всего удалить сразу всю базу целиком. Проблема заключается в том, что затем вам придется снова создать эту базу. А с этим могут возникнуть трудности, если у вас нет доступа к учетной записи пользователя root.

drop database database_name;

Как быть и что делать в этой ситуации? Самый простой способ сразу удалить все таблицы из базы MySQL — использовать утилиту mysqldump:

mysqldump -u[USERNAME] -p[PASSWORD] --add-drop-table --no-data [DATABASE] | grep ^DROP | mysql -u[USERNAME] -p[PASSWORD] [DATABASE]

Вместо переменных, которые указаны в квадратных скобках, вам необходимо указать свои данные.

Изменить пароль пользователя MySQL

Дата: 24.06.2016Метки:

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

mysql -uroot -p

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

SET PASSWORD = PASSWORD('пароль')

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

SET PASSWORD FOR 'mysqluser'@'localhost' = PASSWORD('пароль');
SET PASSWORD FOR 'mysqluser'@'%' = PASSWORD('пароль');

Первая команда отличается от второй тем, что пользователю mysqluser будет разрешена авторизация только с localhost. В плане безопасности это более предпочтительный вариант.

Аналогичное действие можно выполнить с помощью запроса SQL:

UPDATE mysql.user SET Password=PASSWORD('пароль') WHERE User='mysqluser' AND Host='localhost';
FLUSH PRIVILEGES;

Установка MariaDB 10.1 в Debian 8

Я решил отказаться от использования MySQL, а точнее полностью перевести все свои сервера на ее форк — MariaDB. Пользуясь случаем, хочу рассказать о процессе установки MariaDB 10.1 в Debian 8. Следует отметить, что краткое описание установки MariaDB есть на официальной странице проекта. Я решил выделить этому вопросу отдельный пост, в котором хочу описать необходимые действия после установки MariaDB на сервере.

Перед началом установки MariaDB необходимо добавить ее репозиторий. На сайте MariaDB рекомендуют для этого установить пакет software-properties-common. Я не вижу в этом никакого смысла и предпочитаю все делать вручную.

Регистрируем GPG-ключ репозитория в системе:

apt-key adv --recv-keys --keyserver keyserver.ubuntu.com 0xcbcb082a1bb943db

Добавляем описание репозитория в файл sources.list. Открываем файл в редакторе nano:

nano /etc/apt/sources.list

Копируем в конец следующие строки:

deb [arch=amd64,i386] http://lon1.mirrors.digitalocean.com/mariadb/repo/10.1/debian jessie main
deb-src http://lon1.mirrors.digitalocean.com/mariadb/repo/10.1/debian jessie main

Обновляем список доступных пакетов:

apt-get update

Запускаем процесс установки MariaDB 10.1:

apt-get install mariadb-server

Во время установки нас попросят ввести пароль для пользователя root. На этом процесс установки MariaDB в Debian 8 закончен. А теперь переходим к настройке сервера.

Чтобы повысить надежность нашего сервера, нужно выполнить минимальные требования безопасности. Запретить авторизацию под пользователем root с удаленных хостов. При наличии базы test и пользователя anonymous, нужно удалить их с сервера. Чтобы облегчить задачу используйте скрипт:

mysql_secure_installation

Тип хранения данных по умолчанию

Если необходимо изменить тип хранения данных по умолчанию, добавьте в файл my.cnf следующие строки:

[mysqld]
default-storage-engine = innodb

Убедитесь, что MariaDB использует таблицы InnoDB по умолчанию. Для этого выполните команду:

SHOW ENGINES;

Создать пользователя и базу MariaDB

Чтобы создать пользователя в MariaDB используйте команду ниже:

CREATE USER 'USER_NAME'@'localhost' IDENTIFIED BY 'PASSWORD';

Создаем новую базу:

CREATE DATABASE database_name;

Даем полные права пользователю USER_NAME на базу databasename:

GRANT ALL PRIVILEGES ON database_name.* TO 'USER_NAME'@'localhost';

Теперь нужно обновить все привилегии:

FLUSH PRIVILEGES

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

SHOW GRANTS FOR 'USER_NAME'@'localhost';

Бинарные логи

MariaDB записывает все изменения БД в бинарный журнал, он необходим для работы механизма репликации. Если вы не делали бэкапов или они устарели, бинарные логи можно использовать для восстановления данных. Впрочем, нет никакой гарантии, что данные будут полностью или частично восстановлены. Успех будет зависеть от размера, времени хранения бинарных логов, частоты выполнения бэкапа.

Для отключения бинарных логов, комментируем строки в файле my.cnf:

#log_bin                 = /var/log/mysql/mariadb-bin
#log_bin_index           = /var/log/mysql/mariadb-bin.index

Отключить комментарии в WordPress

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

Как вариант, можно быстро отключить комментарии в WordPress используя MySQL запрос:

use wordpressdb;
UPDATE wp_posts SET comment_status='closed' WHERE post_status = 'publish' AND post_type = 'post';

Чтобы включить комментарии для всех записей в WordPress, выполните запрос:

UPDATE wp_posts SET comment_status='open' WHERE post_status = 'publish' AND post_type = 'post';

Пользователь для бэкапа данных MySQL

Дата: 04.12.2015Метки:

Достаточно часто можно встретить ситуацию, когда для создания дампа базы MySQL используют учетную запись root. Это не есть правильно с точки зрения безопасности. Поэтому, лучше будет создать отдельного пользователя MySQL, учетные данные которого можно использовать в скриптах.

Для начала создадим нового пользователя MySQL:

CREATE USER 'backup'@'localhost' IDENTIFIED BY 'password';

Установим ему права для бэкапа таблиц. В зависимости от используемого типа хранилища, выполните команды ниже.

Права для бэкапа данных хранилища INNODB:

GRANT SELECT ON *.* TO 'backup'@'localhost';

Права для бэкапа данных хранилища MyISAM:

GRANT SELECT, LOCK TABLES ON *.* TO 'backup'@'localhost';

Создать пользователя и базу MySQL

Дата: 16.11.2015Метки:

Короткая заметка на тему как создать пользователя и базу MySQl для дальнейшей установки WordPress или любого другого движка. Все действия будем выполнять через консоль. В процессе понадобится создать безопасный пароль, я рекомендую сгенерировать случайную комбинацию с помощью сервиса Password Generator.

Чтобы создать базу MySQL заходим в mysql клиент под root. Параметры databasename, user и password нужно заменить на свои.

mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 11455

Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.

mysql> create database databasename;
Query OK, 1 row affected (0.00 sec)

mysql> grant all privileges ON databasename.* TO "user"@"localhost"
    -> identified by "password";
Query OK, 0 rows affected (0.00 sec)
  
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

mysql> exit
Bye

Удалить пользователя и базу MySQL:

drop user 'user'@'localhost';
drop database databasename;

Настройка работы MySQL Query Cache

Дата: 18.09.2015Метки:

Использование кэширования запросов, является одним из ключевых факторов влияющих на производительность работы MySQL. Функционал Query Cache открывает дополнительные возможности для оптимизации базы данных и позволяет снизить время обработки запросов в несколько раз. Наилучшую эффективность работы Query Cache показывает на веб-серверах, у которых таблицы не обновляются слишком часто и присутствует много идентичных запросов.

Для подходящего запроса типа SELECT, MySQL автоматически сохраняет текст запроса и данные выборки в кэше. Все идентичные запросы в дальнейшем, будут обрабатываться в обход БД с помощью функции MySQL Query Cache. Таким образом, кэшированные запросы не выполняется вовсе.

Для работы Query Cache в значении переменной query_cache_type должно быть установлено ON или DEMAND, а query_cache_size быть отличной от нуля.

В противном случае, необходимо добавить соответствующие настройки в секцию [mysqld] конфигурационного файла MySQL:

nano /etc/mysql/my.cnf

[mysqld]
query_cache_type        = ON
query_cache_limit       = 1M
query_cache_size        = 16M

За настройку работы функции Query Cache отвечают системные переменные начинающиеся с 'query_cache_'.

mysql> SHOW VARIABLES LIKE 'query_cache_%';

+------------------------------+----------+
| Variable_name                | Value    |
+------------------------------+----------+
| query_cache_limit            | 1048576  |
| query_cache_min_res_unit     | 4096     |
| query_cache_size             | 16777216 |
| query_cache_type             | ON       |
| query_cache_wlock_invalidate | OFF      |
+------------------------------+----------+
5 rows in set (0.00 sec)

query_cache_limit — размер максимальной выборки, которая будет записана в кэш. В качестве значения необходимо указать максимальный размер самого тяжелого запроса, но не стоит чрезмерно завышать значение данного параметра.
query_cache_min_res_unit — минимальный размер выделяемого блока памяти для хранения результатов кэшированного запроса. Для записи данных в кэш MySQL разбивает выборку на отдельные блоки с минимальным размером query_cache_min_res_unit. Последний такой блок обрезается до размера данных, а оставшаяся память освобождается. Для записи данных в кэш, MySQL по мере необходимости выделяет блоки размером query_cache_min_res_unit. В качестве значения необходимо указать среднее значение размера выборки от всех запросов. Примерное значение query_cache_min_res_unit можно вычислить по формуле query_cache_min_res_unit = (query_cache_size – Qcache_free_memory) / Qcache_queries_in_cache. Слишком большое значение будет способствовать фрагментации кэша, слишком маленькое может стать причиной снижения производительности.
query_cache_size — размер памяти выделяемый для хранения кэша запросов. Значение равное 0 отключает работу MySQL Query Cache. Устанавливаем значение исходя из количества свободной оперативной памяти в системе. Для выбора оптимального значения, в идеале переменная Qcache_lowmem_prunes должна равняться нулю. В противном случае, рекомендуется чтобы в процессе работы MySQL это значение увеличивалось незначительно.
query_cache_type — параметр отвечающий за работу кэша. Может принимать значения: ON, DEMAND и OFF. Опция включает или отключает работу MySQL Query Cache, если значение query_cache_type установлено равным DEMAND, MySQL будет кэшировать только запросы с директивой SQL_CACHE.
query_cache_wlock_invalidate — определяет будут ли данные браться из кэша, если таблица, к которым они относятся заблокирована на чтение. Если значение параметра query_cache_wlock_invalidate принимает значение OFF, то будет доступно получение данных заблокированной таблицы из Query Cache.

Для мониторинга MySQL Query Cache используется команда:

mysql> SHOW GLOBAL STATUS LIKE 'Qcache%';

+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 158      |
| Qcache_free_memory      | 16420704 |
| Qcache_hits             | 143791   |
| Qcache_inserts          | 21851    |
| Qcache_lowmem_prunes    | 0        |
| Qcache_not_cached       | 12506    |
| Qcache_queries_in_cache | 215      |
| Qcache_total_blocks     | 598      |
+-------------------------+----------+
8 rows in set (0.00 sec)

Qcache_free_blocks — количество свободных блоков в кэше. Чем больше незадействованных блоков, тем больше степень фрагментации кэша. Если результат большинства запросов имеет небольшой объем данных выборки, необходимо уменьшить значение параметра query_cache_min_res_unit.
Qcache_total_blocks — количество занятых блоков.
Qcache_free_memory — объем свободной памяти, отведенной под кэш.
Qcache_hits — количество запросов отработанных из кэша.
Qcache_inserts — количество запросов записанных в кэш.
Qcache_lowmem_prunes — количество запросов, которые были удалены из-за переполнения кэша.
Qcache_not_cached — количество запросов не подлежащих кэшированию.
Qcache_queries_in_cache — количество запросов находящихся в кэше.

Кратко механизм работы Query Cache выглядит следующим образом. Под кэширование запросов MySQL выделяет в памяти область размером query_cache_size. Для записи результатов запроса сервер создает в кэше свободный блок размером query_cache_min_res_unit. После заполнения блока, сервер создает новый пустой блок и так до тех пор, пока все данные выборки не будут записаны в кэш. После чего свободная область памяти последнего блока выделяется в новый свободный блок. В случае если размер выборки превышает установленное значение query_cache_limit, то запись прекращается, а занятое память освобождается.

Фрагментация кэша возникает при удалении выборки из кэша, когда для записи результатов новых запросов количества освободившихся блоков недостаточно. Для того что бы определить степень фрагментации, необходимо обратить внимание на значение переменной Qcache_free_blocks. В идеале значение должно быть равно единице, в случае фрагментации — Qcache_total_blocks / 2. Так же можно определить, что ваш кэш запросов сильно фрагментируется, если значение Qcache_lowmem_prunes постоянно возрастает при том, что значение Qcache_free_memory далеко от нуля.

Для дефрагментации кэша используется команда:

mysql> FLUSH QUERY CACHE;

Для оценки эффективности работы кэша используется формула Qcache_hits / (Qcache_hits + Com_select).