Основы работы с MySQL

Внимание! После ручной правки конфигураций MySQL — my.cnf требуется перезапустить сервер MySQL. 

ISPmanager5 — Службы — MySQL/MariaDB — Рестарт или в консоли сервера:

systemctl restart mysql
systemctl restart mariadb

Внимание! После ручной правки конфигураций MySQL — my.cnf требуется перезапустить сервер MySQL. 

ISPmanager5 — Службы — MySQL/MariaDB — Рестарт или в консоли сервера:

systemctl restart mysql
systemctl restart mariadb

Что такое MySQL

Подробнее о MySQL мы рассказали в статье.

• полностью бесплатная СУБД;
• поддерживается большинством CMS;
• неограниченный многопользовательский режим;
• множество плагинов, облегчающих работу с данной СУБД;
• поддерживает различные типы таблиц (MyISAM, InnoDB, HEAP, MERGE);
• позволяет добавлять до 50 миллионов строк в таблицы.

• ограниченный функционал (не реализованы все возможности SQL);
• не подходит для масштабных проектов.

Базы данных на хостинге REG.RU доступны на всех тарифах, кроме Host-Lite и Win-Lite. Также базы данных доступны во всех панелях управления веб-хостингом. Если у вас один из этих тарифов, для использования баз данных повысьте тариф.

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

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

Правила, определяющие возможность удалённой работы с MySQL, формируются в Панели управления услугой виртуального хостинга в разделе «Базы данных». После входа в раздел следует выбрать базу к которой будет настраиваться доступ и нажать на ссылку «Настроить удалённый доступ». Ссылка находиться в меню , напротив имени базы данных. 

• следует полностью указать IP-адрес, с которого будет осуществляться работа с MySQL-сервером. Пример записи — 127.0.0.1. 

Правило создается путём нажатия на кнопку «Создать доступ», указания нужного правила в открывшемся окне и нажатия на кнопку «Создать». Для каждой базы допускается создание до 30 правил для удалённого доступа. 

• Имя сервера для удалённого подключения указывается в панели управления на странице «Открытие удалённого доступа».
• Порт для подключения — 3306 .

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

. . .
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
. . .

. . .
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
. . .

$ sudo systemctl restart mysql

$ mysql -u user_name -h db_host -p

Many websites and applications start off with their web server and database backend hosted on the same machine. With time, though, a setup like this can become cumbersome and difficult to scale. A common solution is to separate these functions by setting up a remote database, allowing the server and database to grow at their own pace on their own machines.

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

Navigate to the line that begins with the bind-address directive. It will look like this:

. . .
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
. . .

By default, this value is set to 127.0.0.1, meaning that the server will only look for local connections. You will need to change this directive to reference an external IP address. For the purposes of troubleshooting, you could set this directive to a wildcard IP address, either *, ::, or 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
. . .

. . .
[mysqld]
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
datadir         = /var/lib/mysql
log-error       = /var/log/mysql/error.log
bind-address            = 0.0.0.0

After changing this line, save and close the file (CTRL + X, Y, then ENTER if you edited it with nano).

Then restart the MySQL service to put the changes you made to mysqld.cnf into effect:

 systemctl restart mysql

 mysql

mysql  root 

">RENAME  @ TO @

">CREATE  @ IDENTIFIED BY 

">CREATE  @ IDENTIFIED WITH mysql_native_password BY 

">ALTER  @ IDENTIFIED WITH mysql_native_password BY 

">GRANT CREATE, ALTER, DROP, INSERT, UPDATE, DELETE, SELECT, REFERENCES, RELOAD on *.* TO @ WITH GRANT OPTION

">FLUSH PRIVILEGES

Then you can exit the MySQL client:

">

Lastly, assuming you’ve configured a firewall on your database server, you will also need to open port 3306 — MySQL’s default port — to allow traffic to MySQL.

 ufw allow from remote_IP_address to any port 

If you need to access the database from other machines in the future, you can grant them access on an ad hoc basis with this command. Just remember to include their respective IP addresses.

Warning: This command will enable anyone to access your MySQL database. Do not run it if your database holds any sensitive data.

 ufw allow 

Note: If you added a firewall rule to only allow connections from a specific IP address, you must try to access the database with the machine associated with that address.

mysql  user  database_server_ip 

Как изменить версию MySQL?

На виртуальном хостинге доступны следующие версии MySQL: — MySQL Version 5.7.23(mysql Ver 14.14 Distrib 5.7.23-24, for Linux (x86_64) using 6.0).

Полный список установленного ПО на серверах REG.RU

Как обновить mysql на хостинге? Изменить версию MySQL на виртуальном хостинге невозможно.

Создание удалённого подключения в консоли

В конфигурационном файле /etc/my.cnf (/etc/mysql/my.cnf для Debian) должна отсутствовать строка skip-networking, если она присутствует — следует её закомментировать:

#skip-networking

Также следует добавить/изменить:

bind-address = IP_сервера

Подключитесь к сервер MySQL суперпользователем:

mysql -u root -p

create user 'dbuser'@'%' identified by 'password'

Можно проверить, что мы создали, командой, выводящей всех пользователей с их хостом и паролем:

select user,host,password from mysql.user;

Создайте базу, например, testdb:

create database testdb;

Предоставление прав пользователю

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

Наиболее часто назначаемые права:

• ALL PRIVILEGES — пользователю предоставляется полный доступ к базе данных.
• CREATE — пользователю разрешено создавать базы данных и таблицы.
• DROP — пользователю разрешено удалять базы данных и таблицы.
• DELETE — пользователю разрешено удалять строки из определенной таблицы.
• INSERT — пользователю разрешено вставлять строки в определенную таблицы.
• SELECT — пользователю разрешено читать базу данных.
• UPDATE — пользователю разрешено обновлять строки таблицы.
• GRANT OPTION — пользователю разрешено изменять права других пользователей.

Назначить привилегии пользователю можно, используя синтаксис:

GRANT permission ON database.table TO  'username'@'%';

где permission — вид предоставляемой привилегии.

GRANT ALL PRIVILEGES ON testdb.* TO 'dbuser'@'%';

Предоставить права на вставку:

GRANT INSERT ON *.* TO 'dbuser'@'%';

Предоставить несколько привилегий пользователю:

GRANT SELECT, INSERT, DELETE ON testdb.* TO 'dbuser'@'%';

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

GRANT INSERT *database_name.table_name* TO 'dbuser'@'%';

Отозвать все привилегии пользователя:

REVOKE ALL PRIVILEGES ON testdb.* FROM 'dbuser'@'%';

Посмотреть текущие привилегии пользователя:

SHOW GRANTS FOR 'dbuser'@'%';

DROP USER 'dbuser'@'%';

После завершения настройки прав необходимо перезагрузить привилегии:

FLUSH PRIVILEGES;

Этот материал был полезен?

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

Создание базы данных происходит в панели управления хостингом. Войдите в вашу панель управления хостингом и следуйте соответствующей инструкции ниже:

1. Перейдите в раздел базы данных и нажмите создать базу данных:

3. Сгенерируйте пароль пользователя и нажмите ок.

Готово, новая база данных создана.

Ошибка при создании бд в ispmanager

При создании базы данных к названию базы и к имени пользователя автоматически добавляется префикс вида u1234567_ (итого 9 символов), максимальное количество символов в имени — 16. таким образом, вводимое вами имя базы и имя пользователя не должно превышать 7 символов (16 минус префикс).

Обратите внимание: если вид вашей панели управления отличается от представленного в статье, в разделе «основная информация» переключите тему с paper_lantern на jupiter.

1. В разделе «базы данных» выберите пункт мастер баз данных mysql:

2. На открывшейся странице укажите название базы данных и нажмите следующий шаг. к названию базы данных автоматически добавляется префикс вида u1234567_.

3. Укажите имя пользователя базы данных, пароль и повторите пароль. затем нажмите создать пользователя:

4. Укажите права пользователя по отношению к базе данных (обычно необходимы все права) и нажмите Следующий шаг:

Готово, новая база данных создана.

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

2. В открывшемся окне заполните следующие поля:

   • имя базы данных (к названию автоматически добавляется префикс вида u1234567_);
   • выберите сайт, на котором будет использоваться база данных;
   • имя нового пользователя базы данных (к имени автоматически добавляется префикс вида u1234_);
   • пароль и подтверждение пароля.

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

3. Нажмите ОК внизу страницы.

Готово, новая база данных создана.

Внимание!

Удалённый доступ к базе данных MySQL

Для активации удалённого соединения с базой данных войдите в вашу панель управления хостингом, и следуйте соответствующей инструкции ниже:

1. Чтобы активировать удаленный доступ MySQL, выберите пункт «Базы данных». Кликните по базе данных и нажмите Пользователи:

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

Обратите внимание: если вид вашей панели управления отличается от представленного в статье, в разделе «Основная информация» переключите тему с paper_lantern на jupiter.

1. В разделе «Базы данных» выберите пункт Удаленный MySQL:

В панели управления Plesk возможность удалённого соединения включена по умолчанию.

Какие данные необходимо использовать для удалённого подключения?

Для удалённого соединения с базой данных (БД) и доступа к MySQL необходимо указывать следующие данные:

• Server/Hostname (сервер базы данных): в качестве сервера необходимо указывать

  • имя сервера, на котором располагается ваша услуга хостинга (например, serverX.hosting.reg.ru, точное имя сервера вы можете уточнить в информационном письме),
  • либо IP-адрес сервера
  • либо доменное имя сайта (убедитесь, что домен припаркован к хостингу);
• Port (порт): 3306 (стандартный порт для подключения к БД);
• DataBase (база данных): имя БД, к которой производится подключение;
• User (пользователь): имя пользователя БД. Обычно имя пользователя имеет вид u1234567_username (не путайте с логином от хостинга вида u1234567);
• Password (пароль): пароль пользователя БД (может отличаться от пароля к услуге хостинга). Как узнать пароль базы данных MySQL?

Какие программы использовать для удалённого подключения MySQL

Подключиться к базе данных вы можете с помощью программы «mysql». Пример удалённого подключения к базе данных на сервере «server90.hosting.reg.ru» под пользователем «u0015955_default»:

mysql -p3306 -hserver90.hosting.reg.ru -uu0015955_default -p

PuTTY

Из соображений безопасности на виртуальном хостинге не предоставляется возможности настройки SSH-туннелирования для соединения с базой данных. Для этого мы рекомендуем приобрести VPS или выделенный сервер.

Создание удалённого подключения в ISPmanager 6

В панели ISPmanager — раздел Настройки — Серверы баз данных выделите сервер MySQL и нажмите кнопку Изменить.

В панелях с версией ниже 5.95 опции Удаленный доступ нет. Необходимо выполнить настройки вручную. В конфигурационном файле /etc/my.cnf (/etc/mysql/my.cnf для Debian) должна отсутствовать строка skip-networking, если она присутствует — следует её закомментировать:

#skip-networking

Также следует добавить/изменить:

bind-address = IP_сервера

Для удалённого подключения к базам данных требуется при создании базы отметить Удалённый доступ.

Если при создании базы вы не выбрали данную опцию, её можно указать в Управлении пользователями баз данных. Для этого перейдите в раздел Инструменты — Базы данных — выделите нужную базу — нажмите кнопку Пользователи — затем кнопку Изменить.

Как изменить пароль базы данных

Важно: в ISPmanager подраздел «Базы данных» недоступен, если вы используете тариф «Host-Lite».

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

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

2. Выберите пользователя БД, пароль которого необходимо изменить, и нажмите Изменить:

3. В открывшемся окне введите новый пароль и нажмите Ok.

Обратите внимание: если вид вашей панели управления отличается от представленного в статье, в разделе «Основная информация» переключите тему с paper_lantern на jupiter.

1. В блоке «Базы данных» выберите пункт Базы данных MySQL:

2. Пролистайте страницу вниз до раздела «Текущие пользователи» и кликните по ссылке Изменить пароль для нужного пользователя:

3. Дважды введите новый пароль (если нужно, используйте генератор паролей). Нажмите кнопку Изменить пароль.

1. Перейдите в раздел «базы данных» и на открывшейся странице нажмите управление пользователями:

2. Кликните по имени пользователя, пароль которого необходимо изменить.

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

Готово, пароль базы данных изменён.

Измените пароль в конфигурационном файле сайта

Не забудьте изменить пароль базы данных в настройках сайта: Где cms хранит настройки подключения к базе данных.

Как удалить базу данных MySQL

Удалить базу данных можно в одной из хостинг панелей. Войдите в вашу панель управления хостингом и следуйте соответствующей инструкции ниже:

Перейдите в раздел «Базы данных». Выделите базу данных, которая вам больше не нужна, и нажмите Удалить:

1. В блоке «Базы данных» выберите пункт Базы данных MySQL:

2. Пролистайте страницу вниз до раздела «Текущие базы данных» и кликните Удалить напротив нужной базы:

   Обратите внимание: удаление пользователя базы данных при этом не происходит, его необходимо удалять отдельно. Процесс аналогичен.

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

Полезные статьи при работе с базами данных MySQL:

Создание удалённого подключения в ISPmanager 6

В панели ISPmanager — раздел Настройки — Серверы баз данных выделите сервер MySQL и нажмите кнопку Изменить.

В панелях с версией ниже 5.95 опции Удаленный доступ нет. Необходимо выполнить настройки вручную. В конфигурационном файле /etc/my.cnf (/etc/mysql/my.cnf для Debian) должна отсутствовать строка skip-networking, если она присутствует — следует её закомментировать:

#skip-networking

Также следует добавить/изменить:

bind-address = IP_сервера

Для удалённого подключения к базам данных требуется при создании базы отметить Удалённый доступ.

Если при создании базы вы не выбрали данную опцию, её можно указать в Управлении пользователями баз данных. Для этого перейдите в раздел Инструменты — Базы данных — выделите нужную базу — нажмите кнопку Пользователи — затем кнопку Изменить.

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

Логин и пароль

Или вы можете создать новую базу данных. В этом случае имя базы, имя пользователя и пароль вы зададите самостоятельно. Если у вас уже есть созданный сайт на CMS, узнать пароль базы данных можно в конфигурационном файле сайта: Где CMS хранит настройки подключения к базе данных.

Имя сервера

В качестве сервера базы данных необходимо указывать «localhost».

Создание удалённого подключения в консоли

В конфигурационном файле /etc/my.cnf (/etc/mysql/my.cnf для Debian) должна отсутствовать строка skip-networking, если она присутствует — следует её закомментировать:

#skip-networking

Также следует добавить/изменить:

bind-address = IP_сервера

Подключитесь к сервер MySQL суперпользователем:

mysql -u root -p

create user 'dbuser'@'%' identified by 'password'

Можно проверить, что мы создали, командой, выводящей всех пользователей с их хостом и паролем:

select user,host,password from mysql.user;

Создайте базу, например, testdb:

create database testdb;

Предоставление прав пользователю

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

Наиболее часто назначаемые права:

• ALL PRIVILEGES — пользователю предоставляется полный доступ к базе данных.
• CREATE — пользователю разрешено создавать базы данных и таблицы.
• DROP — пользователю разрешено удалять базы данных и таблицы.
• DELETE — пользователю разрешено удалять строки из определенной таблицы.
• INSERT — пользователю разрешено вставлять строки в определенную таблицы.
• SELECT — пользователю разрешено читать базу данных.
• UPDATE — пользователю разрешено обновлять строки таблицы.
• GRANT OPTION — пользователю разрешено изменять права других пользователей.

Назначить привилегии пользователю можно, используя синтаксис:

GRANT permission ON database.table TO  'username'@'%';

где permission — вид предоставляемой привилегии.

GRANT ALL PRIVILEGES ON testdb.* TO 'dbuser'@'%';

Предоставить права на вставку:

GRANT INSERT ON *.* TO 'dbuser'@'%';

Предоставить несколько привилегий пользователю:

GRANT SELECT, INSERT, DELETE ON testdb.* TO 'dbuser'@'%';

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

GRANT INSERT *database_name.table_name* TO 'dbuser'@'%';

Отозвать все привилегии пользователя:

REVOKE ALL PRIVILEGES ON testdb.* FROM 'dbuser'@'%';

Посмотреть текущие привилегии пользователя:

SHOW GRANTS FOR 'dbuser'@'%';

DROP USER 'dbuser'@'%';

После завершения настройки прав необходимо перезагрузить привилегии:

FLUSH PRIVILEGES;