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

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

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)
альтернативная последовательность команд:

mysql> use mysql;
mysql> update user set password=PASSWORD(«newpassword») where user=’root’ and host=’localhost’;
mysql> flush privileges;
перезапускаем сервер без опции —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 ( как альтернативу можно использовать символ ; – точка с запятой ). Еще один полезный вариант \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)

mysql> SHOW VARIABLES LIKE ‘colla%tabase’\g
+———————+——————+
| Variable_name | Value |
+———————+——————+
| collation_database | utf8_general_ci |
+———————+——————+
1 row in set (0.00 sec)

mysql> SHOW VARIABLES LIKE ‘collation_database’\g
+———————+——————+
| Variable_name | Value |
+———————+——————+
| collation_database | utf8_general_ci |
+———————+——————+
1 row 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> SHOW VARIABLES LIKE ‘max_join_size’\g
+—————+———————+
| Variable_name | Value |
+—————+———————+
| max_join_size | 111111111111111111 |
+—————+———————+
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.01 sec)
# Глобальное значение осталось неизменным, в то время как локальное соответствует тому, которое мы задали.

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

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.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 vas@% |
+———————————+
| 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 vas@% |
+————————————————+
| 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 в полном смысле.. то есть я не имею дела со структурой таблиц и связей, если конечно не занимаюсь этим для себя. В большинстве случаев работа в консольном клиенте сводится к «создать юзера, создать базу, выставить права».