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