Архивы рубрик:MySQL

Выполнял однажды SQL запрос:

GRANT REPLICATION SLAVE ON TESTDATABASE.* TO "replication"@"192.168.1.9" IDENTIFIED BY "password";

И обнаружил следующую ошибку:

ERROR 1221 (HY000): Incorrect usage of DB GRANT and GLOBAL PRIVILEGES

Так как привилегии REPLICATION SLAVE являются глобальными и не могут быть назначены конкретной базе данных, то и в запросе их нужно указывать глобально, поэтому SQL запрос должен выглядеть так:

GRANT REPLICATION SLAVE ON *.* TO "replication"@"192.168.1.9" IDENTIFIED BY "password";

После этого запрос выполнился успешно:

Query OK, 0 rows affected, 1 warning (0,01 sec)

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

mysqlcheck -u root -p -A

Где опция -u определяет имя пользователя под которым выполняется подключение к MySQL, -p для запроса пароля, -A для проверки таблиц всех баз данных.

Пример восстановления конкретной базы данных:

mysqlcheck -u root -p -r db_name

Пример восстановления конкретной таблицы в указанной базе данных:

mysqlcheck -u root -p -r db_name table_name

Опишу возможные опции запуска.

Проверка всех баз данных и их таблиц, кроме INFORMATION_SCHEMA и performace_schema:

--all-databases, -A

Указать перечень баз можно используя опцию:

--databases, -B

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

--all-in-1, -1

Анализировать таблицы:

--analyze, -a

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

--auto-repair

Указание адреса подключения к MySQL серверу (например если их несколько):

--bind-address=ip_address

Подключение к MySQL серверу на указанном узле:

--host=host_name, -h host_name

Директория с установками символов:

--character-sets-dir=dir_name

Проверка таблиц на наличие ошибок (выполняется по умолчанию):

--check, -c

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

--check-only-changed, -C

Проверка таблиц на совместимость с текущей версией сервера:

--check-upgrade, -g

Сжатие данных передаваемым между клиентом и сервером если оба это поддерживают:

--compress

Запись и отображение отладочной информации (на тесте стандартный mysqlcheck в Ubuntu 16.04 был скомпилирован без поддержки debug):

--debug=debug_options
--debug-check
--debug-info

Указание набора символов по умолчанию:

--default-character-set=charset_name

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

--defaults-file=file_name

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

--extended, -e

Проверить только таблицы которые не были закрыты должным образом:

--fast, -F

Продолжать даже в случае SQL ошибки:

--force, -f

Быстрее чем extended, находится 99.99% ошибок:

--medium-check, -m

Не читать файлы опций, однако файл .mylogin.cnf будет читаться:

--no-defaults

Смотрите также мою статью — Подключение к MySQL из localhost без ввода пароля.

Оптимизировать таблицы:

--optimize, -o

Указание пароля при подключении:

--password[=password], -p[password]

Номер порта TCP/IP для подключения:

--port=port_num, -P port_num

Отображение имени программы и всех параметров, которые она получает из файлов параметров:

--print-defaults

Указание протокола подключения:

--protocol={TCP|SOCKET|PIPE|MEMORY}

Быстрый метод проверки таблиц, предотвращает сканирование строк для проверки неправильных ссылок:

--quick, -q

Выполнение ремонта:

--repair, -r

Тихий режим, отображаются только ошибки:

--silent, -s

Пропустить указанную базу:

--skip-database=db_name

Указание сокета при подключении:

--socket=path, -S path

Подключение с использованием SSL:

--ssl*

Переопределить -databases или -B, все аргументы имени, следующие за опцией, рассматриваются как имена таблиц:

--tables

Указание перечня протоколов через запятую, которые разрешено использовать при зашифрованных соединениях:

--tls-version=protocol_list

При восстановление получить структуру таблицы из файла .frm:

--use-frm

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

--user=user_name, -u user_name

Подробный режим, отображается больше информации:

--verbose, -v

Отображение версии mysqlcheck и выход:

--version, -V

Смотрите также:
Установка и настройка MySQL сервера на Ubuntu

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

mysql -u root

Или так, если необходимо ввести пароль:

mysql -u root -p

Посмотрим список существующих баз данных:

SHOW DATABASES;

Выбрать базу данных можно так:

USE dbname;

Посмотреть какая в данный момент выбрана база:

SELECT DATABASE();

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

SHOW TABLES;

Пример просмотра содержимого указанной таблицы:

SELECT * FROM tablename LIMIT 25;

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

DESCRIBE tablename;

Где Field — это имя столбца, Type — тип данных, Null — определяет, может ли столбец содержать значения NULL, Key — является ли столбец индексным, Default — определяет значение по умолчанию для столбца.

Посмотреть информацию о индексах таблицы можно так:

SHOW INDEX FROM tablename;

Посмотреть информацию о таблице, кодировке, её типе (ENGINES) и т.д. можно командой:

SHOW CREATE TABLE tablename;

Посмотреть какие типы поддерживает сервер можно так:

SHOW ENGINES;

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

Читать далее «Подключение к MySQL без ввода пароля»

connect_timeout — количество секунд, в течение которых сервер mysql ожидает пакет подключения, прежде чем прервать соединение.

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

mysql -u USER -p
show variables like "connect_timeout";
quit;

Значение connect_timeout можно указать в файле /etc/mysql/my.cnf, например:

[mysqld]
connect_timeout=10

В реальном времени можно изменить выполнив SQL запрос (после перезапуска MySQL оно сбросится на стандартное или указанное в файле конфигурации):

SET GLOBAL connect_timeout=10;

Стандартное значение равно 10, минимальное — 2, максимальное 31536000.

wait_timeout — Количество секунд, в течение которых сервер ждет активности в неинтерактивном соединении, прежде чем закрыть его.
В момент соединения значение wait_timeout берется из глобального значения wait_timeout или interactive_timeout в зависимости от типа клиента (как определено опцией CLIENT_INTERACTIVE connect для mysql_real_connect ())

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

mysql -u USER -p
show variables like "wait_timeout";
show variables like "interactive_timeout";
quit;

По умолчанию значения wait_timeout и interactive_timeout равняются 28800 секунд = 8 часов.
Минимально можно установить 1, максимум — 31536000, максимум (для Windows) — 2147483.

Можно изменить значение wait_timeout выполнив SQL запрос, например:

set global wait_timeout = 28800;
set global interactive_timeout = 28800;

Чтобы установленное значение не сбросилось, его нужно указать в файле /etc/mysql/my.cnf, в блоке mysqld:

[mysqld]
wait_timeout = 28800
interactive_timeout = 28800

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

mysql

Посмотрим какие есть пользователи:

select * from mysql.user;
select user,host from mysql.user;

Создадим пользователя (там где localhost указывается откуда пользователь может подключатся, можно указать IP-адрес, localhost — с локальной машины где сам MySQL сервер, либо % с любых адресов):

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

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

#bind-address = 127.0.0.1

И перезапустить MySQL сервер:

sudo service mysql restart

После этого рекомендую ограничить доступ к MySQL средствами IPTables.
Смотрите также — Настройка IPTables

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

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

Если нужно на все базы:

GRANT ALL PRIVILEGES ON *.* TO 'user'@'localhost';

Можно указать конкретные права доступа:

GRANT SELECT ON database_name.* TO 'user'@'localhost';
GRANT SELECT, INSERT ON database_name.table_name TO user@192.168.1.5;

Если нужно создать новую базу:

CREATE DATABASE database_name;

Чтобы изменения вступили в силу выполним:

FLUSH PRIVILEGES;

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

DROP USER 'user'@'localhost';

Пример просмотра привилегий:

SHOW GRANTS FOR 'user'@'localhost';
SHOW GRANTS;
SELECT * FROM information_schema.user_privileges;

Ниже приведу примеры импорта и экспорта MySQL баз данных из терминала Linux.

Экспортировать базу данных в файл можно так:

Читать далее «Импорт и экспорт MySQL баз данных»