Настройка удаленного подключения к MySQL

Материал статьи предназначен для начинающих, профессионалы Хабра в большинстве не нуждаются в изложении этого вопроса. Тем не менее, те, кто делает первые шаги в работе с MySQL часто задают подобные вопросы. Статья написан для тех, кто впервые столкнулся с необходимостью установить удаленное соединение с базой данных MySQL. В статье рассказывается о сложностях, которые могут возникнуть при настройке удаленного соединения и о методах их преодоления.

Как установить соединение с удаленной базой?

Для установления удаленного соединения нужно указать параметры, характеризующие устанавливаемое подключение. Это

• —host
• —protocol
• —port

Из четырех возможных протоколов удаленное подключение позволяет только TCP/IP, поэтому первым требованием является доступность компьютера из сети по TCP/IP. Далее необходимо добавить имя хоста (или ip адрес машины, на которой расположена база) в строке подключения: mysql —host=host_name Обратите внимание, что указание в качестве имени хоста значения localhost (или же отсутствие такого параметра, что то же самое, так как это дефолтное значение) приводит к обращению на локальную машину. Учтите, что параметры имеют две формы записи: длинную и короткую. В свою очередь, если параметр имеет значение (как например, в случае хоста необходимо указать имя хоста к которому нужно подключиться), то короткая форма записи может использоваться с пробелом или без (исключением из этого правила является пароль). Таким образом, следующие три записи являются эквивалентными:

• mysql —host=myhost.ru
• mysql -h myhost.ru
• mysql -hmyhost.ru

Как настроить соединение с удаленной базой?

В MySQL пользователь характеризуется двумя параметрами: именем и хостом, с которого он может обращаться. По умолчанию доступ разрешен только с локальной машины, т.е. для пользователя user@localhost. Права на доступ пользователям даются с помощью команды GRANT. Команда выполняется под рутом. Например, если я хочу создать юзера, который сможет коннектиться с любого хоста с полными правами, то следует выполнить следующую команду: GRANT ALL PRIVILEGES ON `имя_базы`.* TO myuser@% IDENTIFIED BY ‘пароль’;Примечание. Обратите внимание, что данная команда дает доступ пользователю myuser со всех IP кроме 127.0.0.1, соответсвующего localhost. Для пользователя myuser@localhost необходимо давать права отдельной командой GRANT. Если вы решите обратиться к локальной машине как к удаленной, используя протокол TCP/IP, то не забывайте, что myuser@localhost и myuser@ip_собственного_компа это разные пользователи и каждому из них права нужно давать отдельной командой. Второй пример показывает как дать право читать таблицу time_zone в базе mysql пользователю myuser с машины 192.168.0.76 с паролем mypassy: GRANT SELECT ON mysql.time_zone TO myuser@192.168.0.76 IDENTIFIED BY ‘mypassy’;

Возникающие сложности

Если после всех действий у вас возникает ошибка (Can’t connect to Mysql Server on ‘ваш IP'(10061)), значит соединение блокирует firewall (windows firewall, outpost, антивирус или еще что-то). Если возникает ошибка (Client does not support autentification protocol request by server; consider upgrading MySQL client), клиент (то есть ваша программа) не поддерживает требуемый сервером протокол. Эту ошибку можно исправить, установив пароль старого формата: SET PASSWORD FOR user@host = OLD_PASSWORD(‘password’); Кросспост Настройка удаленного подключения к MySQL с Webew.

Если Вы хотели поработать с MySQL в операционной системе Linux Mint 18, например, для того чтобы изучить язык SQL или просто познакомиться с СУБД (а может быть задействовать ее у себя на работе), но Вы не знаете, как установить сервер MySQL, какие первоначальные настройки необходимо внести, каким клиентом подключаться, для того чтобы писать запросы на языке SQL, то данный материал для Вас, так как в нем мы рассмотрим процесс установки MySQL и клиентского приложения MySQL Workbench.

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

MySQL – это бесплатная реляционная система управления базами данных. Разработку и поддержку СУБД MySQL осуществляет компания Oracle. MySQL является одной из самых популярных в мире баз данных для веб-приложений (сайтов).

MySQL реализована для большинства современных операционных систем, например: Windows, Linux, FreeBSD, Mac OS X, Solaris.

Если кому интересно как выглядит процесс установки MySQL в Windows, то можете ознакомиться с материалом «Установка MySQL на Windows 7», так как в нем мы подробно рассматривали данный процесс.

Для того чтобы работать с сервером MySQL (создавать таблицы, писать запросы на получение данных и так далее) необходимо специальное клиентское приложение. Таких приложений много, как платных, так и бесплатных, в данном материале мы рассмотрим две клиентских программы, это стандартную консольную утилиту MySql и графический инструмент MySQL Workbench.

Утилита MySql – это стандартная консольная программа для работы с сервером MySQL. Начинающим пользователям, наверное, будет не удобно ее использовать, так как она не имеет графического интерфейса, все команды пишутся вручную в командной строке (терминале).

MySQL Workbench – это программа для работы с сервером MySQL, которая имеет графический интерфейс. Она позволяет осуществлять разработку баз данных и администрировать сервер.

Переходим к рассмотрению процесса установки MySQL и MySQL Workbench в операционной системе Linux Mint 18.2, мы с Вами рассмотрим два способа установки: первый – это с помощью графического инструмента (менеджера программ), и второй, более продвинутый способ, — с помощью терминала Linux.

Содержание

Описание установки MySQL и MySQL Workbench на Linux Mint 18.2

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

На заметку! Если Вы обычный пользователь домашнего компьютера и хотите познакомиться с Linux поближе, рекомендую почитать мою книгу – «Linux для обычных пользователей», в ней я подробно рассказываю про основы операционной системы Linux

Установка MySQL с помощью менеджера программ

MySQL в Linux Mint устанавливается также как и любая другая программа, о том, как устанавливать программы с помощью менеджера программ, мы с Вами говорили в материале «Установка и удаление программ в Linux Mint».

Для наглядности давайте все равно разберем процесс установки MySQL. Запускаем менеджер программ Меню->Менеджер программ или Меню->Администрирование->Менеджер программ.

Вводим пароль администратора компьютера.

В поиск вводим MySQL и нажимам Enter (ввод), затем щелкаем двойным кликом по нужной программе. В нашем случае начинаем с MySQL-Server.

В открывшемся окне щелкаем «Установить».

В процессе установки менеджер программ попросит нас придумать и ввести пароль для суперпользователя root в MySQL, мы вводим и нажимаем «Вперед».

Затем еще раз вводим его для подтверждения.

После того как изменится статус на «Установлена» переходим к установке следующей программы, для этого щелкаем на вкладку «Результаты поиска».

Затем точно таким же способом устанавливаем Mysql-client и Mysql-workbench (для установки MySQL Workbench потребуется около 200 мегабайт).

Все, на этом процесс базовой установки MySQL с помощью менеджера программ закончен.

Установка MySQL с помощью терминала

Запускаем терминал Linux. Например, щелкаем по ссылке на панели или запускаем из меню Mint.

Устанавливать MySQL необходимо от имени суперпользователя root, поэтому давайте сразу переключимся на этого пользователя. Для этого пишем sudo -i (или sudo su) и жмем Enter (ввод), затем вводим пароль пользователя.

Далее сначала давайте обновим список пакетов, для этого пишем команду.

            apt-get update    

Затем для установки: MySQL Server, MySQL Client и MySQL Workbench пишем следующую команду.

            apt-get -y install mysql-server mysql-client mysql-workbench    

В процессе установки нам предложат придумать и ввести пароль для администратора сервера MySQL (пользователя root), мы вводим пароль и в следующем окне подтверждаем его.

Установка будет завершена, как распакуются и настроятся все пакеты.

Настройка MySQL

После установки первое, что нам необходимо сделать, это осуществить базовую настройку сервера MySQL. Даже если он у Вас будет только локально на домашнем компьютере.

Базовая настройка безопасности MySQL

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

В процессе Вам будут задавать вопросы, практически на все отвечаем «Да», т.е. пишем «Y». Исключение может составлять вопрос о смене пароля пользователя root (Change the password for root?), т.е. его можно не менять и ответить «N», если в процессе установки MySQL мы задали хороший пароль для root. Сразу после запуска скрипта вводим пароль администратора сервера MySQL (как раз тот самый пароль от пользователя root).

            mysql_secure_installation    

Потом будет вопрос о том, хотим ли мы активировать плагин проверки сложности пароля, отвечаем «Y», т.е. «Да».

В случае если мы активируем данный плагин, у нас спросят уровень сложности пароля, давайте ответим 2, т.е. средней сложности (цифры, большие/маленькие буквы и спец. символы).

Далее будет вопрос как раз о смене пароля пользователя root, как я уже сказал, я отвечаю «N», т.е. «Нет». Если на этапе установки MySQL Вы придумали для root простой пароль, то в этом случае лучше конечно его сменить.

Затем мы отвечаем «Y», для того чтобы удалить анонимных пользователей. Также отвечаем «Y», для того чтобы запретить пользователю root удаленное подключение.

Потом снова отвечаем «Y», для удаления тестовой базы данных и на последний вопрос также отвечаем «Y» для перезагрузки таблицы привилегий.

На этом базовая настройка безопасности завершена.

Настройка кодировки в MySQL

По умолчанию в MySQL установлена кодировка latin1, для того чтобы мы могли вносить, хранить и читать данные на кириллице, давайте изменим эту кодировку на utf8, в данном случае все созданные нами объекты на сервере по умолчанию будут иметь кодировку utf8. Также мы укажем настройки, при которых все подключения к серверу принудительно будут иметь также кодировку utf8. Для этого давайте откорректируем конфигурационный файл /etc/mysql/my.cnf. Это мы будем делать в терминале, поэтому открываем его и вводим следующую команду (также от имени root или с использованием команды sudo).

            nano /etc/mysql/my.cnf    

После того как Вы откроете этот файл, опуститесь чуть ниже и укажите следующие параметры.

             [mysqld]          character-set-server=utf8          collation-server=utf8_general_ci          init_connect='SET collation_connection = utf8_general_ci'          init_connect='SET NAMES utf8'          skip-character-set-client-handshake            [client]          default-character-set=utf8            [mysqldump]          default-character-set=utf8    

Сохраняем файл сочетанием клавиш Ctrl+O и жмем Enter, затем закрываем его также сочетанием клавиш только Ctrl+X.

После внесения изменений в конфигурационный файл, сервер MySQL необходимо перезагрузить, например вот так.

            systemctl restart mysql    

Настройка удаленного доступа к серверу MySQL

Если вдруг Вам понадобилось, чтобы к Вашему серверу можно было подключаться с других компьютеров, то для этого необходимо изменить одну настройку в файле mysqld.cnf, а именно параметр bind-address, по умолчанию в нем указано что подключаться к серверу можно только с локального компьютера. Для того чтобы разрешить подключение с любых компьютеров укажите bind-address=0.0.0.0. Запускаем также с правами суперпользователя.

            nano /etc/mysql/mysql.conf.d/mysqld.cnf    

После внесения изменений также сохраняем/закрываем (Ctrl+O, Ctrl+X) файл и перезагружаем сервер MySQL.

            systemctl restart mysql    

Подключение к серверу MySQL с помощью MySQL Workbench

После установки пакета MySQL-Workbench у Вас в меню Mint появился пункт «Программирование», в котором есть программа «MySQL Workbench». Для подключения к серверу MySQL запускаем данную программу.

После запуска в MySQL Workbench у нас уже будет по умолчанию настроено локальное подключение от имени пользователя root, но работать от имени этого пользователя крайне не рекомендуется. Поэтому мы только сейчас подключимся от имени root, создадим БД и пользователя, а потом уже будем подключаться от имени созданного нами пользователя. Сейчас я сразу покажу Вам, как можно создавать новое подключение, для этого нажимаем на иконку плюсик «+».

Вводим необходимые параметры (имя подключения, хост, порт, учетную запись), я указываю их на примере пользователя root (для проверки доступа можете нажать на кнопку «Test Connection») и нажимаем «OK».

После этого щелкаем на созданное подключение.

Система спросит пароль, мы вводим пароль и жмем «OK». После чего мы подключимся к серверу MySQL.

Создание базы данных и таблицы в MySQL, а также вставка данных в таблицу

Теперь давайте для примера создадим базу данных с названием TestBase. Для этого пишем инструкцию CREATE DATABASE.

А потом давайте создадим таблицу TestTable и вставим в нее одну строку (предварительно перейдя в нужную БД).

             #Создание базы данных          CREATE DATABASE TestBase;          #Подключаемся к нужной базе данных          USE TestBase;          #Создаем таблицу          CREATE TABLE TestTable (id INT, comment VARCHAR (100));          #Вставляем данные в таблицу          INSERT INTO TestTable (id, comment) VALUES (1, 'Текст');          #Осуществляем выборку данных          SELECT * FROM TestTable    

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

Сейчас, как я и говорил, давайте создадим нового пользователя, для того чтобы потом работать от его имени. Я для примера создам пользователя TestUser и разрешу ему подключаться с любого хоста, а также дам ему полные права на управление базой TestBase. Для этого пишем следующую инструкцию.

             #Создаем пользователя          CREATE USER 'TestUser'@'%' IDENTIFIED BY 'Pa$$w0rd';          #Назначаем права          GRANT ALL PRIVILEGES ON TestBase.* TO 'TestUser'@'%';          #Применяем изменения          FLUSH PRIVILEGES;    

Где,

• TestUser – это имя учетной записи, Вы указываете свою;
• % — это означает, что пользователь может подключаться с любого хоста, в случае если Вы хотите, чтобы пользователь подключался только с локального компьютера, то вместо этого символа можете указать localhost;
• Pa$$w0rd – это пароль от учетной записи (Вы указываете свой придуманный пароль);
• ALL PRIVILEGES – означает, что мы даем пользователю полные права, в случае необходимости Вы можете указать только нужную привилегию;
• TestBase.* — означает, что права мы даем только на определенную базу данных, в нашем случае TestBase. Для того чтобы дать права на все БД напишите *.*;
• FLUSH PRIVILEGES – сохраняем все изменения, которые мы внесли.

Подключение к серверу MySQL с помощью консольной утилиты MySQL

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

            mysql -u TestUser -p    

Затем давайте для примера сделаем выборку из нашей тестовой таблицы, для этого подключимся к БД и напишем запрос SELECT (не забываем писать точку с запятой (;) в конце инструкции).

            USE TestBase;       SELECT * FROM TestTable;    

Как видим, мы подключились и успешно выполнили запрос.

Удаление MySQL и MySQL Workbench в Linux Mint 18.2

Если вдруг Вам необходимо удалить сервер MySQL и клиентскую программу MySQL Workbench, то для этого Вы также можете использовать менеджер программ, т.е. найти нужную программу и нажать «Удалить».

Это нужно сделать для каждой программы, которую Вы хотите удалить, т.е. для MySQL-Server, MySQL-Client и для MySQL-Workbench.

Удалить MySQL также можно и в терминале Linux. Для этого открываем терминал и пишем следующую команду от имени суперпользователя.

            apt-get -y remove --purge mysql-server mysql-client mysql-workbench    

Ключ —purge означает, что мы хотим удалить пакеты вместе с их файлами настройки. В данном случае мы удалили сразу все 3 программы, за исключением связанных пакетов, которые установились автоматически. Для удаления связанных пакетов необходимо использовать команду apt-get -y autoremove, тем самым Вы можете высвободить место на диске.

На этом моя статья, посвященная установке MySQL на ОС Linux Mint, закончена, надеюсь, она была Вам полезна, удачи!

Нередко, удалённый доступ к серверу MySQL — это единственный способ управления своими базами данных (БД). Для хостинг-провайдеров подобная техническая возможность является обязательной для качественного предоставления услуг клиентам. Для веб-разработчиков (вообще для разработчиков) удалённый доступ позволяет использовать один общий ресурс для обслуживания БД. В любом случае, просто так по-умолчанию сервер MySQL удалённые подключения не допускает. Для включения удалённого доступа к MySQL необходимо конфигурирование определённых параметров как самого сервера, так и обслуживаемых БД и их пользователей. В данной статье будут рассмотрены основные принципы предоставления удалённого доступа к серверу MySQL.

Содержание

Что для этого нужно?

Сервер MySQL по-умолчанию в целях безопасности сконфигурирован таким образом, что может принимать только локальные соединения, поступающие через локальный сокет с той же самой машины, на которой работает и сам сервер. Эта настройка обслуживается опцией «bind-address» в конфигурационном файле MySQL-сервера

/etc/mysql/mysql.conf.d/mysqld.cnf. Изменив значение указанной опции и перезапустив сервер MySQL можно предоставить доступ для удалённых клиентов. Кроме самой возможности удалённого подключения к серверу MySQL необходимо также и позаботиться о предоставлении соответствующих привилегий удалённым пользователям. Это также очень важно как для комфортной работы, так и для безопасности. Привилегии пользователей MySQL предоставляются как для подключений, так и для работы

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

Таким образом, существует возможность задания привилегий, т. е. управления доступом не только для самих пользователей и групп. Но и для характера их подключения к серверу — удалённого или локального. Это необходимо учитывать при организации и управлении удалённым доступом к MySQL-серверу.

Настройка сервера MySQL

По-умолчанию для опции «bind-address» в файле /etc/mysql/mysql.conf.d/mysqld.cnf задано значение 127.0.0.1. И подключиться к серверу можно только локально:

. . .  lc-messages-dir = /usr/share/mysql  skip-external-locking  #  # Instead of skip-networking the default is now to listen only on  # localhost which is more compatible and is not less secure.  bind-address = 127.0.0.1  . . .

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

. . .  lc-messages-dir = /usr/share/mysql  skip-external-locking  #  # Instead of skip-networking the default is now to listen only on  # localhost which is more compatible and is not less secure.  bind-address = 0.0.0.0  . . .

Сделав изменения в и сохранив файл нужно перезапустить сам сервер MySQL:

$ sudo systemctl restart mysql

Далее можно проверить, что удалённое подключение возможно, выполнив его с другого компьютера в сети:

$ mysql -u user_name -h db_host -p

Здесь «db_host» – это адрес (IP или имя хоста) компьютера, на котором работает сервер MySQL. При удачном подключении пользователь будет переведён в интерфейс командной консоли MySQL.

Настройка привилегий БД

На сервере MySQL может быть несколько пользователей с одинаковым именем, но разным режимом доступа. Например, пользователь с именем john, которому доступно только локальные подключения. На самом деле распознается сервером MySQL как пользователь с именем «john@localhost». Для того, чтобы пользователь john мог подключаться ещё и удалённо с любого IP. На сервере должна быть создана ещё одна учётная запись с именем «john@%». Вот как выглядит создание такой учётной записи в веб-интерфейсе phpMyAdmin:

Как можно видеть, в системе управления доступом MySQL для учётной записи пользователя существует отдельный атрибут «имя хоста», регламентирующий режим подключения. Это сделано как для повышения самой безопасности, так и для более гибкого управления и администрирования. Тот случай, когда одно другому не только не мешает. Но в совокупности поднимает эффективность и безопасность всего сервиса.

Поскольку учётные записи john@localhost и john@% являются разными (хотя и принадлежат одному пользователю), то для них можно отдельно настраивать привилегии. Например, для учётной записи john@% в phpMyAdmin это выглядит следующим образом:

Как можно видеть, администратор запретил пользователю john удалять БД через удалённое подключение. Таким же образом можно устанавливать (или убирать) ограничения на уровне БД. Т. е. при работе с таблицами, а также на уровне таблиц — при работе непосредственно с данными.

Заключение

В заключение стоит ещё раз отметить, что предоставление удалённого доступа к БД путём изменения параметра bind-address в значение 0.0.0.0 должно проводиться с учётом принятых мер по обеспечению дополнительной безопасности сервера.

Если вы нашли ошибку, пожалуйста, выделите фрагмент текста и нажмите Ctrl+Enter.

Используемые источники:

• https://habr.com/post/25554/
• https://info-comp.ru/sisadminst/589-install-mysql-and-workbench-on-linux-mint.html
• https://itproffi.ru/upravlenie-udalyonnym-dostupom-k-serveru-mysql/