MySQL — создание и управление базами данных

По умолчанию сервер MySQL разрешает только локальные подключения, то есть выполняемые с сервера, на котором он работает.

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

1. Внести изменения в конфигурационный файл MySQL.
2. Разрешить пользователю или пользователям MySQL удаленные подключения.

• Как подключиться к базе данных MySQL: инструкция

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

В этом материале мы рассмотрим, как подключиться к MySQL несколькими способами. В качестве тестовой базы можно применять облачные базы данных провайдера Timeweb Cloud. Запуск БД там займет пару минут.

• Как создать базу данных и нового пользователя в MySQL: инструкция

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

По умолчанию вам будет доступен пользователь root. Используйте его, чтобы выполнить первоначальную настройку: создать первую БД и других пользователей.

Приведенные команды подойдут для управления MySQL, развернутой на сервере. Если же вы пользуетесь облачной базой данных, пользователь и сама база будут созданы автоматически при создании БД в панели управления.

...
# phpMyAdmin Configuration
Include /etc/phpmyadmin/apache.conf

...
$i++;
$cfg['Servers'][$i]['host'] = 'your_database_cluster_hostname.b.db.'yourcloudserver.com';
$cfg['Servers'][$i]['port'] = '25060';
$cfg['Servers'][$i]['ssl'] = true;
$cfg['Servers'][$i]['ssl_ca'] = '/etc/phpmyadmin/ca-certificate.crt';

Содержание

• Общая информация для работы с MySQL
• Создание баз данных MySQL
• Подключение к серверу MySQL
• Как войти в PHPMyAdmin?
• Дамп базы MySQL
• Удаленный доступ к базе данных MySQL
• Изменение кодировки базы данных по умолчанию

PhpMyAdmin

Как зайти в phpMyAdmin на нашем хостинге

Как изменить язык в phpMyAdmin

Как импортировать базу данных через phpMyAdmin

Импорт базы данных происходит в одноименном разделе «Импорт»

Необходимо выбрать дамп импортируемой базы данных и его кодировку

Обращаем внимание на то, что файл дампа базы данных должен иметь одно из четырех расширений:

По нажатию кнопки «OK» импорт будет запущен.

Как экспортировать базу данных через phpMyAdmin

Экспортировать базу данных можно в одноимённом разделе.

Для запуска процедуры экспорта достаточно нажать на кнопку «OK». После создания дампа базы данных он загрузится через браузер.

Как оптимизировать/починить таблицу через phpMyAdmin

В первую очередь необходимо нажать на название базы данных в левом меню. Откроется список таблиц в базе данных.
Далее нужно отметить таблицы, с которыми необходимо произвести то или иное действие.

Из выпадающего списка «C отмеченными:» необходимо выбрать один из пунктов(Оптимизировать таблицу/Восстановить таблицу). SQL-запрос будет немедленно выполнен.

Ключевые команды, которые нужно знать

Как создать БД в MySQL:

Как удалить БД:

создать нового пользователя MySQL:

Как удалить пользователя:

Это минимальный набор запросов, который поможет в MySQL создать базу данных и пользователя. Особенности использования этих и других возможностей СУБД подробно описаны в

Создание базы данных

Подключитесь к MySQL под учётной записью root:

mysql -u root -p

Посмотрите, какие базы уже хранятся на сервере:

Даже если вы ещё ничего не делали, таблица не будет пустой. В ней отобразятся служебные БД. Удалять их не нужно.

создать базу данных MySQL

CREATE DATABASE my_timeweb;

Вместо my_timeweb укажите имя, которое необходимо задать новой БД.

Чтобы убедиться, что новая база появилась на сервере, ещё раз выполните:

В таблице отобразится название БД, которую вы только что создали.

Нельзя, чтобы у двух и более БД были одинаковые имена. Если ещё раз создать базу my_timeweb, то появится сообщение об ошибке с кодом 1007. 

Чтобы предотвратить появление ошибки, можно в MySQL создать новую базу с помощью расширенного синтаксиса:

CREATE DATABASE IF NOT EXISTS my_timeweb;

В этом случае СУБД проверит условие. Если БД с таким именем не существует, то MySQL создаст её. Если же такая база была создана ранее, то ничего не изменится.

Подключение к MySQL в phpMyAdmin

Должен запуститься браузер и в нём должна быть странаца, содержащая меню авторизации.

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

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

Существует 4 способа подключения к базе данных MySql:

Способ 1 — Через PhpMyAdmin

1. Далее выбираем нужную базу данных и нажимаем «Перейти».

1. Вводим название пользователя, пароль и нажимаем «Вперед».

Способ 2 — Через консоль

Для подключения к базе данных MySql через консоль, нужно набрать следующую команду:

• You_Server — хост с MySql, обычно это IP сервера. Если подключаемся с локального сервера, параметр можно опустить.
• User_Name — имя пользователя MySql.
• Password — пароль MySql.

Если вы все сделали правильно, должно быть так:

Способ 3 — Подключение с помощью PHP

Для подключения к базе данных через PHP, нужно выполнить следующий код:

$result = mysqli_query($conn, ‘SELECT id FROM table limit 1’);

где, функция mysql_connect состоит из:

• localhost — имя хостинга;
• admin — имя пользователя (для входа в MySql);
• pass — пароль (для входа в MySql);
• db — имя базы данных, к которой подключаемся.
• Функция mysql_query — нужна для выбора вашего запроса к базе данных. Где table пишем название таблицы, limit 1 указываем количество строк. В представленном примере выбираем столбец ID из базы данных книг (Ставим ID, если данный параметр указан в таблице).
• Функция mysql_fetch_row — нужна для извлечения строк из SQL-запроса, который мы только что создали.
• Функция mysql_close — нужна для того, чтобы закрыть соединение.

Способ 4 — Подключение к базе данных удаленно

Для того чтобы подключиться к базе данных MySql удаленно, нужно выполнить следующие действия:

1. С помощью панели управления хостингом, в разделе «Главное» заходим в «Базы Данных».

1. Далее выбираете нужную базу данных и нажимаете «Пользователи».

1. Затем выбираем нужного пользователя (в данном случае он один) и нажимаем «Изменить».

1. Ставим галочку напротив графы «Удаленный доступ» и нажимаем «Ок».
   Также нужно обратиться в поддержку хостинга и сообщить ip адрес, с которого будут происходить подключения к бд, иначе данная настройка работать не будет.

Готово! Теперь вы сможете подключаться к базе данных удаленно (чтобы подключиться удаленно через PHP, смотрите предыдущий способ, представленный выше).

Удаление базы данных

Для удаления выполните:

DROP DATABASE my_timeweb;

Вместо my_timeweb укажите имя БД, которую следует удалить.

У DROP DATABASE также есть расширенный синтаксис, который помогает избежать ошибок:

DROP DATABASE IF EXISTS my_timeweb;

Запрос будет выполнен только при наличии БД с таким именем.

DROP DATABASE удалит БД и связанные с ней данные. Интерфейс командной строки MySQL не предложит вам подтвердить действие. Поэтому применяйте DROP DATABASE с осторожностью.

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

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

MySQL создать пользователя, авторизуйтесь как root и выполните в консоли:

Если нужно указать не конкретный хост, а целый пул, используйте знак %. Например:

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

Способы, доступные пользователю

Существует несколько вариантов коннекта, какой именно выбрать, решать вам:

1. Командная строка MySQL. Входит в комплект сервера MySQL.

Во всех перечисленных способах возможно подключение по защищенному каналу благодаря SSL-сертификату.

Подключение через phpMyAdmin

Edit MySQL Config File

1.1 Access mysqld.cnf File

The location of the file may vary based on the distribution and version in use. If the MySQL configuration file is not it its default location try using the Linux find command to detect it.

1.2 Change Bind-Address IP

You now have access to the MySQL server configuration file. Scroll down to the bind-address line and change the IP address. The current default IP is set to 127.0.0.1. This IP limits MySQL connections to the local machine.

The new IP should match the address of the machine that needs to access the MySQL server remotely. For example, if you bind MySQL to 0.0.0.0, then any machine that reaches the MySQL server can also connect with it. For this website to access it you need to use IP address 212.47.237.65
Once you make the necessary changes, save and exit the configuration file.

1.3 Restart MySQL Service

Next, your current firewall settings need to be adjusted to allow traffic to the default MySQL port.

Сколько подключений к БД доступно

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

Подключение из разных операционных систем

как подключиться к базе данных MySQL из разных операционных систем. Выглядеть команды будут так:

Шаг 2. Создание пользователя MySQL

Через консоль

Подключитесь к MySQL:

С помощью команд ниже создайте пользователя и выдайте ему необходимые привилегии. В примере используется ALL PRIVILEGES, но в зависимости от ваших задач вы можете выдавать только отдельные права доступа (например, CREATE, INSERT, UPDATE и т.д.).

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

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

С помощью следующей команды можно разрешить пользователю подключение ко всем базам вместо одной конкретной:

Через phpMyAdmin

В пункте «Имя хоста» выберите «Любой хост» и укажите либо %, чтобы разрешить подключение с любого адреса, либо конкретный IP.

На следующем шаге задайте нужные привилегии для пользователя. Это может быть полный набор прав или с определенными ограничениями (например, вы можете запретить новой учетной записи удалять таблицы базы данных; таким образом удаление будет возможно только при локальном подключении).

Шаг 1. Редактирование конфигурационного файла

В Ubuntu файл размещается, как правило, по пути /etc/mysql/mysql.conf.d/mysqld.cnf

В CentOS это могут быть /etc/mysql/my.cnf/etc/my.cnf

Откройте файл для редактирования, указав нужный путь:

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

Найдите в нем следующие строки:

# 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

Параметр bind-address определяет, с каких адресов MySQL разрешает подключения. В данном случае оно возможно только с локального сервера (localhost, 127.0.0.1).

Укажите здесь нужные вам настройки:

После перезапустите MySQL:

sudo systemctl restart mysql

Выдача привилегий

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

В этой таблице — привилегии, которые часто выдают пользователям. Более подробный список смотрите в

Для выдачи прав нужно выполнить команду GRANT. Например:

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

Set up Firewall to Allow Remote MySQL Connection

While editing the configuration file, you probably observed that the default MySQL port is 3306. This is default MySQL port number but can be changed in the config file.

Option 1: UFW (Uncomplicated Firewall)

The system confirms that the rules were successfully updated. Firewall rule added to firewall deamon.

Option 2: FirewallD

The firewalld management tool in CentOS uses zones to dictate what traffic is to be allowed.
Create a new zone to set the rules for the MySQL server traffic. The name of the zone in our example is mysqlrule, and we used the IP address from our previous example 212.47.237.65:

You have successfully opened port 3306 on your firewall.

Option 3: Open Port 3306 with iptables

Ограничения файрвола

Удаленное подключение к MySQL может ограничиваться настройками файрвола. Например, на сервере с Bitrix 7 такое подключение вероятнее всего будет недоступно из-за ограничений iptables. Потребуется дополнительно внести изменения в правила — открыть порт 3306.

Можно открыть доступ к нему с любого адреса, но такая настройка является небезопасной. Рекомендуется разрешать доступ для конкретного IP.

iptables это можно сделать командой:

sudo iptables -A INPUT -s 123.45.67.8 -p tcp --destination-port 3306 -j ACCEPT

Замените адрес из примера на нужный вам.

Если на сервере используется UFW, настройку можно выполнить с помощью:

sudo ufw allow from 123.45.67.8 to any port 3306

После завершения настройки проверьте, что пользователь может подключиться к MySQL удаленно. 

mysql -u имя_пользователя -h адрес_хоста_mysql -p

Добро пожаловать в Web phpMyAdmin

Make sure your MySQL server allows remote connections and that port 3306 is open. If you need to enable your MySQL server to allow for a remote connection then the instructions can be found below.

Удаление учётной записи

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

Connect to Remote MySQL Server

How to Grant Remote Access to New MySQL Database?

Выводы

Работать с удаленной базой данных можно с криптографической защитой данных или без нее. Это зависит от задачи, которую предстоит решать. Соединение возможно как вручную из операционок Linux и Windows, так и автоматически при наличии в приложении соответствующего кода. Вопрос, как подключиться к локальному серверу MySQL, имеет то же решение. Отличие составляет лишь адрес, который понадобится указать в командах.

Отзыв привилегий

Чтобы отозвать привилегии, используйте команду REVOKE. У неё такой же синтаксис, как у GRANT. Но результат будет противоположным.

Если учётная запись не имела никаких прав в этой БД, то ничего не изменится.