Администрирование Mysql из командной строки

Когда речь идет об администрировании Mysql сервера, каждый представляет что-то свое.
Программисту удобней работать и управляться с базами данных через
какое-нибудь веб приложение, например phpMyAdmin или через графический
Windows клиент, например Navicat. Администратору-же, зачастую приходится
обходиться командной строкой, консольным клиентом mysql и различными
утилитами командной строки, идущими в стандартной поставке Mysql
сервера.
В данном материале попытаюсь коротко рассказать о командах, которыми
пользуюсь сам, для решения тех или иных задач, возникающих в процессе
настройки сервера.

Все нижесказанное будет происходить в контексте операционной системы FreeBSD 8.0.

Небольшая ремарка насчет постоянного упоминания мной, FreeBSD.
В процессе написания, я стараюсь вживую проверять команды, которые пишу,
а поскольку Unix системы, расплодились нынче в большом количестве,
наборы команд, программ и утилит а так-же опций команд, могут
значительно различаться и то что работает во FreeBSD, может не работать в
каком-нибудь дистрибутиве Linux. Поэтому мне кажется не будет лишним
указать, в какой именно unix системе, это работает точно.
На самом деле адаптировать все это под любую другую Unix систему не
сложно, всегда можно найти аналог программы, или глянуть man
руководство, что-бы уточнить использование опций.
Для начала, как восстановить пароль Mysql пользователя root

Бывает, попадет в руки сервер, а пароль для пользователя root, уже никто не помнит.
Это решается довольно просто, причем вариантов решения несколько.
Во-первых, можно запустить демон Mysql сервера с опцией командной
строки, —skip-grant-tables или прописать параметр skip-grant-tables в
конфигурационный файл сервера, my.cnf. При таком запуске, mysql сервер,
проигнорирует таблицу привилегий, и все базы данных, в том числе и
системные, будут доступны для изменения, без каких-либо привилегий. В
этом случае оператор SET PASSWORD, можно выполнить любым пользователем,
для любого пользователя, в том числе root.
Выполняем:
mysql> SET PASSWORD FOR ‘root’@’localhost’ = PASSWORD(‘new_pass’)\g
Query OK, 0 rows affected (0.00 sec)

перезапускаем сервер без опции —skip-grant-tables и пробуем подключиться.
Второй вариант, это воспользоваться внешней утилитой из стандартного комплекта Mysql сервера, mysqladmin.
freebsd8 /# mysqladmin -u root password «new_pass» # установит новый пароль
freebsd8 /# mysqladmin -u root password «» # удалит старый пароль

Второй вариант вариант конечно проще.
Консольный клиент mysql

В поставке Mysql сервера, всегда присутствует одноименный консольный клиент — программа mysql.
Что она умеет. Да практически все, что необходимо для полноценной
настройки и обслуживания Mysql сервера. Опишу манипуляции и команды,
которые приходится делать чаще всего.
При запуске, программа-клиент подключается к серверу с текущим именем системного пользователя, например:
freebsd8 /# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 138
Server version: 5.5.2-m2-log FreeBSD port: mysql-server-5.5.2
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.
mysql>

такой вариант запуска возможен, если для пользователя mysql — root, не установлен пароль ( поведение по умолчанию ).

Не путайте пользователя root, Mysql сервера, с системным пользователем root.

Запуск клиента mysql с ключом —help, выведет довольно внушительный список возможных опций программы.
Итак, список команд, используемых наиболее часто с краткими пояснениями:

Далее в примерах, ключ \g ( go ), это указание Mysql выполнить
команду, пока он не указан, клиент будет переходить на новую строку,
ожидая дальнейшего ввода или ключа \g. Полный список ключей можно
посмотреть введя ключ \h ( help ).
SHOW [ GLOBAL ] VARIABLES
Вывести список системных переменных Mysql сервера. Переменные могут быть
глобальные ( GLOBAL ) и сессионные ( SESSION ). Если не указано, какого
именно типа, переменные нужно вывести, будут выведены сессионные
переменные
SHOW [ GLOBAL ] VARIABLES LIKE ‘%часть_имени_переменной%’
Более удобный вариант команды. Знак процента, является шаблоном и соответствует любым символам. Например:
mysql> SHOW VARIABLES LIKE ‘colla%’\g
+———————-+——————+
| Variable_name | Value |
+———————-+——————+
| collation_connection | utf8_general_ci |
| collation_database | utf8_general_ci |
| collation_server | utf8_general_ci |
+———————-+——————+
3 rows in set (0.00 sec)

последний и предпоследний варианты, выводят одно и то-же.
SET [ GLOBAL ] имя_переменной=значение | имя_переменной=DEFAULT
Оператор SET, устанавливает значение переменной. Имейте в виду, не все
переменные можно изменить через консольный клиент, многие
инициализируются при запуске Mysql сервера, с помощью соответствующих
опций командной строки или через установку в файле конфигурации my.cnf.
При попытке изменить значение такой переменной, будет выведено сообщение
об ошибке.
Что-бы переменная приняла значение по умолчанию, можно в качестве значения использовать ключевое слово DEFAULT.
Как и в случае с командой SHOW, если не указан тип переменной, значение
будет установлено для сессионной ( локальной ) переменной. Следующий
пример, демонстрирует это поведение более наглядно:
# Выводим значение локальной переменной
mysql> SHOW VARIABLES LIKE ‘max_join_size’\g
+—————+———————-+
| Variable_name | Value |
+—————+———————-+
| max_join_size | 18446744073709551615 |
+—————+———————-+
1 row in set (0.00 sec)
# Выводим значение глобальной переменной
mysql> SHOW GLOBAL VARIABLES LIKE ‘max_join_size’\g
+—————+———————-+
| Variable_name | Value |
+—————+———————-+
| max_join_size | 18446744073709551615 |
+—————+———————-+
1 row in set (0.00 sec)
# Как видите в данный момент значения идентичны

# Теперь установим новое значение:
mysql> SET max_join_size=111111111111111111\g
Query OK, 0 rows affected (0.00 sec)

# Ну и вернем значение по умолчанию:
mysql> SET max_join_size=DEFAULT\g
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW GLOBAL VARIABLES LIKE ‘max_join_size’\g
+—————+———————-+
| Variable_name | Value |
+—————+———————-+
| max_join_size | 18446744073709551615 |
+—————+———————-+
1 row in set (0.01 sec)
SHOW [ GLOBAL ] STATUS [ LIKE ‘%имя_переменной%’ ]
Выведет статусные переменные ( счетчики ) с их значениями.
Поведение с ключевым словом GLOBAL и без него, аналогично команде SHOW VARIABLES.
SHOW DATABASES
Вывести список существующих баз данных
USE имя_базы_данных
Перейти в базу данных имя_базы_данных, сделав ее текущей
SHOW TABLES [ FROM имя_базы_данных ]
Если не указана конструкция FROM, выведет список таблиц в текущей базе данных.
CHECK TABLE имя_таблицы [ FOR UPGRADE | QUICK | FAST | MEDIUM | EXTENDED | CHANGED ]
Проверка таблицы на предмет ошибок в различных режимах.
OPTIMIZE TABLE имя_таблицы
Оптимизация таблиц.
REPAIR TABLE имя_таблицы
Попробовать отремонтировать таблицу.
Этот оператор тоже имеет несколько опций, но в обычном случае достаточно
простого REPAIR TABLE. Если для ремонта таблиц, вдруг надумаете
использовать дополнительные опции, советую сначала ознакомиться с
документацией по их применению и возможным последствиям.)
SELECT user, host FROM mysql.user
Это обычный SQL запрос к таблице user системной базы данных mysql, в
результате которого будет выведен список всех существующих пользователей
и хостов.
CREATE USER ‘user’@’localhost’ IDENTIFIED BY ‘password’
Создать пользователя mysql с именем user для хоста localhost, то есть только для локального подключения, с паролем password.
В Mysql можно ограничить пользователя, разрешив ему подключаться только с
определенного хоста, это может быть как IP адрес, так и доменное имя.
Если при создании, указывается только имя пользователя, то есть такой
вариант команды — CREATE USER boom , в качестве хоста назначается шаблон
%, означающий, что данный пользователь может соединяться с Mysql
сервером откуда угодно. Кроме того, для данного пользователя будет задан
пустой пароль.
Если есть такая возможность, в целях безопасности, лучше ограничивать пользователей по IP адресам.
RENAME USER ‘user’@’host’ TO ‘user2’@’host’
Переименовать пользователя. Не работает в старых версиях Mysql ( не помню точно с какой версии появилась ).
Если в имени назначения опустить имя host, будет присвоен шаблон %, то есть любой.
DROP USER имя_пользователя
Удалить пользователя.
SET PASSWORD FOR ‘user’@’host’ = PASSWORD(‘password’)
Назначить или изменить пароль пользователя. То-же самое можно сделать с
помощью SQL оператора UPDATE , изменив поле Password в системной таблице
User, для определенного пользователя.
mysql> UPDATE mysql.user SET password= PASSWORD(‘secret’) WHERE user=’user’ AND host=’localhost’\g
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> FLUSH PRIVILEGES\g
Query OK, 0 rows affected (0.02 sec)

Кроме того, пароль пользователя можно задать при назначении привилегий, в операторе GRANT ( см. ниже ).
GRANT ALL ON data\_base.* TO ‘user’@’host’ [ IDENTIFIED BY ‘secret’ ]
Назначает права пользователю.
В Mysql достаточно гибкая система привилегий, имеющая несколько уровней,
глобальный, уровень базы данных,уровень таблицы и уровень столбца.
Для меня, приведенный выше вариант, является самым распространенным. Мы
назначаем все права ( кроме GRANT OPTION — право управлять правами
других пользователей ), на все таблицы базы данных data_base ( обратите
внимание, если в имени базы данных присутствует знак подчеркивания, его
нужно экранировать обратным слешем ), для пользователя user с хостом
host.
При указании конструкции IDENTIFIED BY ‘secret’, пользователю будет
назначен пароль ‘secret’, если у пользователя уже был установлен пароль,
он будет заменен на ‘secret’.
Еще один типичный пример, назначение прав пользователю, для создания резервных копий данных:
mysql> GRANT SELECT, LOCK TABLES ON *.* to ‘backup’@’localhost’\g

Тема привилегий Mysql, довольно объемна и ее освещение не входило в
мои планы. Если вам понадобится более плотно работать с привилегиями,
рекомендую почитать официальную документацию.
REVOKE ALL ON *.* FROM ‘user’@’host’
Противоположность предыдущей команды, отнимает права пользователя.
SHOW GRANTS FOR ‘user’@’host’
Просмотр существующих привилегий пользователя mysql. Например, у только что созданного пользователя, будут такие привилегии:
mysql> SHOW GRANTS FOR ‘vasya’@’%’\g
+———————————+
| Grants for [email protected]% |
+———————————+
| GRANT USAGE ON *.* TO ‘vasya’@’%’ |
+———————————+
1 row in set (0.00 sec)

USAGE -это привилегии по умолчанию, означает отсутствие каких-либо привилегий.
# выдадим пользователю некоторые права
mysql> GRANT SELECT, LOCK TABLES ON *.* to ‘vasya’@’%’\g
Query OK, 0 rows affected (0.00 sec)

# Проверяем
mysql> SHOW GRANTS FOR ‘vas’@’%’\g
+————————————————+
| Grants for [email protected]% |
+————————————————+
| GRANT SELECT, LOCK TABLES ON *.* TO ‘vas’@’%’ |
+————————————————+
1 row in set (0.00 sec)
DESCRIBE имя_таблицы
Выводит информацию по указанной таблице, имена колонок, типы данных и т.д.
Это укороченный вариант команды SHOW COLUMNS FROM. Можно укоротить вообще до DESC.
CREATE DATABASE имя_базы_данных
Создать базу данных.
При необходимости можно указать кодировку, например, CHARACTER SET utf8 и
сравнение COLLATE utf8_bin, в данном случае речь идет о кодировке UTF8.
DROP DATABASE имя_базы_данных
Удалить базу данных.
ALTER TABLE имя_таблицы ENGINE=InnoDB
У оператора ALTER масса различных ключей, но в процессе
администрирования сервера ( то есть не администрирования какого-то
конкретного веб приложения ), я его применяю для смены движка MyISAM на
InnoDB для некоторых таблиц.
DROP TABLE имя_таблицы
Удалить таблицу.
FLUSH PRIVILEGES
Перезагрузить системную таблицу привилегий.
Mysql сервер кэширует в память результат выполнения таких операторов
как: CREATE USER, GRANT, CREATE SERVER, INSTALL PLUGIN, и не освобождает
память при выполнении обратных вариантов команд: DROP USER, REVOKE,
DROP SERVER, UNINSTALL PLUGIN.
FLUSH HOSTS
Очищает кэш хостов, например если у хоста сменился IP адрес или если вдруг появилось сообщение: Host ‘имя_хоста’ is blocked.

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