Удалить из нескольких таблиц с использованием INNER JOIN
Во всех предыдущих примерах мы использовали команду удаления, чтобы удалить только записи из одной таблицы.
Есть два способа. когда можно указать удаление записей из нескольких таблиц.
В первом способе, можно указать таблицы, из которых записи будут удалены перед ключевым словом “FROM”, как показано ниже. Ниже будут удалены записи из worker и benefits таблиц, которые соответствуют критериям, указанным в условии where.
DELETE worker, benefits FROM worker INNER JOIN benefits INNER JOIN contractor WHERE worker.id=benefits.id AND benefits.id=contractor.id;
Во втором способе, можно указать таблицы, где записи будут удалены, после ключевого слова “FROM”, как показано ниже. Ниже будет удалять записи из worker и benefits, которые соответствуют критериям, указанным в условии where.
DELETE FROM worker, benefits USING worker INNER JOIN benefits INNER JOIN contractor WHERE worker.id=benefits.id AND benefits.id=contractor.id;
Запуск базы данных MariaDB
Как вы видите ниже, модуль сервера базы данных MariaDB загружен, но еще не запущен.
# systemctl status mariadb ? mariadb.service - MariaDB database server Loaded: loaded (/usr/lib/systemd/system/mariadb.service; disabled; vendor preset: disabled) Active: inactive (dead)
Запустите сервер MySQL с помощью systemctl, как показано ниже.
# systemctl start mariadb
Проверьте состояние systemctl, чтобы убедиться, что сервер базы данных MariaDB запущен успешно.
# systemctl status mariadb
● mariadb.service - MariaDB database server
Loaded: loaded (/usr/lib/systemd/system/mariadb.service; enabled; vendor preset: disabled)
Drop-In: /etc/systemd/system/mariadb.service.d
└─nofile.conf
Active: active (running) since Tue 2017-10-03 13:56:53 MSK; 1 months 7 days ago
Main PID: 840 (mysqld_safe)
CGroup: /system.slice/mariadb.service
├─ 840 /bin/sh /usr/bin/mysqld_safe --basedir=/usr
└─1121 /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --log-error=/var/log/mariadb/mariadb.log...
Если вы программист, и используете PHP, вы найдете это полезным: 3 метода подключения к MySQL с помощью PHP с примерами кода
Удалить строки с опцией IGNORE
Вы можете использовать ключевое слово IGNORE вместе с командой DELETE (т.е. DELETE IGNORE), когда вы хотите игнорировать реальное действительное сообщение об ошибке, если вы знаете, ваша конкретная команда DELETE будет сбрасывать.
Таким образом, когда вы используете DELETE IGNORE, если есть любое действительное сообщение об ошибке, он будет рассматриваться только как предупреждение. Вы должны быть очень осторожны, когда вы используете DELETE IGNORE, особенно в таблице, где у вас есть внешний ключ, так как DELETE IGNORE просто удалит записи, не беспокоясь о каких-либо внешних зависимостей внешнего ключа.
MariaDB > DELETE IGNORE FROM contractorbenefits WHERE id > 200; Query OK, 3 rows affected, 1 warning (0.01 sec)
Как видно из приведенного выше вывода, DELETE IGNORE удалит записи из таблицы, даже если существует зависимость внешнего ключа. Он просто выведет те реальные сообщение об ошибке как предупреждения.
Скорость работы баз данных
Чтобы оптимизация СУБД MySQL дала результат, нужно начать с анализа работы баз данных. Настройки сервиса содержатся в файле /etc/my.cnf.
С помощью настроек можно проверить, какие запросы выполняются медленно и что можно ускорить. Для этого в раздел добавляется следующий запрос:
log-slow-queries=/var/log/mariadb/slow_queries.log long_query_time=5
Информация указана в строчках:
log-slow-queries=/var/log/mariadb//slow_queries.log long_query_time=2
Во второй обозначено минимальное время внесения запроса в лог — 2 секунды.
Чтобы увидеть актуальные данные, сервер перезапускается. Сведения находятся в логе:
# systemctl restart mariadb # tail -f /var/log/mariadb/slow-queries.log
В полученном списке будут представлены все запросы, время выполнения которых превышает указанный показатель. К примеру, больше 10 секунд может выполняться запрос:
SELECT option_name, option_value FROM wp_options WHERE autoload = 'yes'
Если при его выполнении в MySQL операция происходит более 3 секунд, запрос может считаться медленным.
Сайт будет работать корректно при условии, что таких запросов немного. Но если у него постоянная нагрузка, количество необработанных запросов будет постепенно расти. Пропорционально им скорость ответа возрастет до нескольких минут.
Решить эту проблему может оптимизация таблиц или кода. В последнем случае из него убираются все сложные запросы. Это длительный процесс, описать который в рамках одной статьи крайне затруднительно. Поэтому здесь мы подробно рассмотрим именно первый вариант.
INNER JOIN (простое соединение)
Скорее всего, вы уже писали запросы, которое используют MariaDB INNER JOIN. Это самый распространенный тип соединения. MariaDB INNER JOINS возвращает все строки из нескольких таблиц, где выполняется условие соединения.
Синтаксис INNER JOIN в MariaDB:
SELECT columns
FROM table1
INNER JOIN table2
ON table1.column = table2.column;
Визуальная иллюстрация
На этой визуальной схеме MariaDB INNER JOIN возвращает затененную область:
MariaDB INNER JOIN будет возвращать записи, в которых пересекаются table1 и table2.
Пример
Вот пример MariaDB INNER JOIN:
PgSQL
SELECT sites.site_id, sites.site_name, pages.page_title
FROM sites
INNER JOIN pages
ON sites.site_id = pages.site_id;
|
1 |
SELECTsites.site_id,sites.site_name,pages.page_title FROMsites INNER JOINpages ONsites.site_id=pages.site_id; |
Этот пример MariaDB INNER JOIN вернул бы все строки из таблиц sites и pages, где в обоих таблицах sites и pages есть совпадающее значение site_id.
Рассмотрим данные, чтобы объяснить, как работают внутренние соединения:
У нас есть таблица sites с двумя полями (site_id и site_name). Она содержит следующие данные:
| site_id | site_name |
|---|---|
| 1000 | Yahoo.com |
| 2000 | Bing.com |
| 3000 | Yandex.com |
| 4000 | Google.com |
У нас есть еще одна таблица под названием страницы с тремя полями (page_id, site_id и file_size). Она содержит следующие данные:
| page_id | site_id | page_title |
|---|---|---|
| 1 | 1000 | Sports |
| 2 | 1000 | Finance |
| 3 | 2000 | MSN |
| 4 | 3000 | Metrika |
| 5 | 5000 | Gmail |
Если мы запустим оператор SELECT (который содержит INNER JOIN) ниже:
PgSQL
SELECT sites.site_id, sites.site_name, pages.page_id, pages.page_title
FROM sites
INNER JOIN pages
ON sites.site_id = pages.site_id;
|
1 |
SELECTsites.site_id,sites.site_name,pages.page_id,pages.page_title FROMsites INNER JOINpages ONsites.site_id=pages.site_id; |
Наш набор результатов будет выглядеть так:
| site_id | site_name | page_id | page_title |
|---|---|---|---|
| 1000 | Yahoo.com | 1 | Sports |
| 1000 | Yahoo.com | 2 | Finance |
| 2000 | Bing.com | 3 | MSN |
| 3000 | Yandex.com | 4 | Metrika |
Строка для ‘Google.com’ из таблицы sites будет опущена, так как site_id 5000 не существует в обеих таблицах. Строка для page_id — 4 из таблицы pages будет опущена, так как site_id — 5000 не существует в таблице sites.
Против
- Повышение нагрузки на сервер баз данных в связи с тем, что большая часть работы выполняется на серверной части, а меньшая — на клиентской.
- Придется много чего подучить. Вам понадобится выучить синтаксис MySQL выражений для написания своих хранимых процедур.
- Вы дублируете логику своего приложения в двух местах: серверный код и код для хранимых процедур, тем самым усложняя процесс манипулирования данными.
- Миграция с одной СУБД на другую (DB2, SQL Server и др.) может привести к проблемам.
Инструмент, в котором я работаю, называется MySQL Query Browser, он достаточно стандартен для взаимодействия с базами данных. Инструмент командной строки MySQL — это еще один превосходный выбор. Я рассказываю вам об этом по той причине, что всеми любимый phpMyAdmin не поддерживает выполнение хранимых процедур.
Кстати, я использую элементарную структуру таблиц, чтобы вам было легче разобраться в этой теме. Я ведь рассказываю о хранимых процедурах, а они достаточно сложны, чтобы вникать еще и в громоздкую структуру таблиц.
1. Процесс разработки
В отличие от многих других проектов с открытым исходным кодом полученных от Sun Microsystems, Oracle до сих пор развивает MySQL. После того как много разработчиков подали в отставку, были наняты новые люди. Но разработка новых версий MySQL ведется закрыто. Исходный код доступен только команде разработчиков и выгружается в публичный репозиторий только после завершения работы. Все решения обсуждаются внутри компании
Из-за раскрученности бренда у MySQL все еще есть большое сообщество, но все больше и больше проектов переходят на MariaDB. Такие известные корпоративные дистрибутивы, как REHL 7 и SLES 12 уже используют MariaDB, а это значит, что в сражении MySQL или MariaDB победит последняя.
Пример выбор столбцов из нескольких таблиц
Оператор SELECT в MariaDB также может выбирать столбцы из нескольких таблиц.
Например:
PgSQL
SELECT pages.page_id, sites.site_name
FROM sites
INNER JOIN pages
ON sites.site_id = pages.site_id
WHERE sites.site_name = ‘Google.com’
ORDER BY pages.page_id;
|
1 |
SELECTpages.page_id,sites.site_name FROMsites INNER JOINpages ONsites.site_id=pages.site_id WHEREsites.site_name=’Google.com’ ORDERBYpages.page_id; |
Этот пример оператора SELECT объединяет две таблицы, чтобы вернуть результирующий набор, который включает поля page_id и site_name. Результаты оператора SELECT фильтруются, когда site_name равно «Google.com», а значение site_id совпадает как в sites, так и в таблице pages. Результаты сортируются по page_id в порядке возрастания.
Option File Syntax
The syntax of the MariaDB option files are:
- Lines starting with # are comments.
- Empty lines are ignored.
- Option groups use the syntax . See the section below for more information on available option groups.
- The same option group can appear multiple times.
- The directive can be used to include other option files. See the section below for more information on this syntax.
- The directive can be used to include all files (and potentially files) in a given directory. The option files within the directory are read in alphabetical order. See the section below for more information on this syntax.
- Dashes () and underscores () in options are interchangeable.
- Double quotes can be used to quote values
- , , , , , , , and are recognized as character escapes for new line, carriage return, tab, backspace, space, double quote, single quote, and backslash respectively.
- Certain option prefixes are supported. See the section below for information about available option prefixes.
- See the section below for information about available options.
Выбор, вставка, обновление и удаление рядов
Начнём с заполнения таблица AuthorsTBL. Почему? Нам нужно иметь значения AuthorID перед вставкой записей в BooksTBL.
Выполните следующий запрос к MariaDB:
INSERT INTO AuthorsTBL (AuthorName) VALUES ('Agatha Christie'), ('Stephen King'), ('Paulo Coelho');
Команда вставки строк в таблицу называется INSERT и имеет общий вид:
INSERT INTO имя_таблицы (имя_поля1, имя_поля2) VALUES ('значение_первого_поля', 'значение_второго_поля');
В результате этой команды была бы вставлена одна новая строка. За один раз можно вставить сразу несколько строк. Команда вставки нескольких строк имеет вид:
INSERT INTO имя_таблицы (имя_поля1, имя_поля2) VALUES ('значение первого поля в первой строке', 'значение второго поля в первой строке'), ('значение первого поля во второй строке', 'значение второго поля во второй строке');
Т.е. если бы мы хотели вставить трёх авторов каждому из которых присвоен уникальный номер, то мы могли ввести бы следующую команду:
INSERT INTO AuthorsTBL (AuthorID, AuthorName) VALUES ('1', 'Agatha Christie'), ('2', 'Stephen King'), ('3', 'Paulo Coelho');
Но поскольку в таблице AuthorsTBL при характеристике типа AuthorID был установлен флаг AUTO_INCREMENT, который означает автоматическую установку уникального номера, то мы смогли из нашей команды убрать информацию, относящуюся к AuthorID. СУБД всё сделала сама: сама заполнила эти поля значениями 1, 2, 3.
Чтобы в этом убедиться, давайте посмотрим на нашу таблицу командой SELECT:
SELECT * FROM AuthorsTBL;
Команда SELECT используется для получения (выбора) записей из таблицы.
Общий синтаксис команды:
SELECT поле1, поле2 FROM таблица WHERE условие;
Как мы могли убедиться, часть WHERE условие; является опциональной. Если она не определена, то выбираются все строки. После SELECT можно указать название полей, которые вас интересуют, например:
SELECT AuthorID, AuthorName FROM AuthorsTBL;
Символ звёздочки (*) означает сразу все поля, т.е. вывод последней команды полностью идентичен SELECT * FROM AuthorsTBL;
Можно указать (через запятую) любой набор полей по желанию:
SELECT AuthorName FROM AuthorsTBL;
После WHERE можно указывать различные условия. Например, я хочу выбрать все поля, в строках которых автором является Agatha Christie, команда для этого:
SELECT * FROM AuthorsTBL WHERE AuthorName='Agatha Christie';
Теперь сделаем вставку (INSERT) записей в таблицу BooksTBL, мы будем использовать соответствующий AuthorID автора для каждой книги. Значение 1 в BookIsAvailable говорит о наличии книги, а 0 – об её отсутствии:
INSERT INTO BooksTBL (BookName, AuthorID, BookPrice, BookIsAvailable)
VALUES ('And Then There Were None', 1, 14.95, 1),
('The Man in the Brown Suit', 1, 23.99, 1),
('The Stand', 2, 35.99, 1),
('Pet Sematary', 2, 17.95, 0),
('The Green Mile', 2, 29.99, 1),
('The Alchemist', 3, 25, 1),
('By the River Piedra I Sat Down and Wept', 3, 18.95, 0);
Посмотрим содержимое таблицы BooksTBL:
SELECT * FROM BooksTBL;
Мы допустили ошибку, у книги The Alchemist должна быть цена 22.75. Для изменения данных в таблице используется команда UPDATE. Её общий синтаксис:
UPDATE таблица SET столбец=новое значение WHERE условие;
Для того, чтобы книги, у которой BookID равен 6 присвоить столбцу BookPrice новое значение 22.75 выполним следующую команду:
UPDATE BooksTBL SET BookPrice=22.75 WHERE BookID=6;
Проверим:
SELECT * FROM BooksTBL WHERE BookID=6;
Как можно убедиться, данные изменились:
При желании удалить запись (строку), можно воспользоваться командой DELETE. Синтаксис этой команды:
DELETE FROM таблица WHERE условие
Например, команда для удаления из таблицы BooksTBL строки, у которой значение поля столбца равно 6:
DELETE FROM BooksTBL WHERE BookID=6;
Не забывайте с командами UPDATE и DELETE использовать условие WHERE, поскольку без него можно удалить все строки в таблице или изменить значение столбца сразу для всех записей.
Удаление нескольких строк с использованием столбца типа String Matching
Вы можете удалять записи из таблицы путем сопоставления столбца строки с определенными критериями.
Для строки, вы можете использовать ключевое слово “like”, который будет делать частичное соответствие. Для частичного соответствия используется % в столбце значений.
В следующем примере будем удалять записи из таблицы сотрудников, где значение в столбце DEPT начинается с “Tech”. Таким образом, это удалит все записи, где отдел “IT”.
Приведенный выше вывод указывает на то, что он удалил 3 записи. Как мы видим из следующего выхода, мы не видим больше записей отдела “IT”.
Мы обсуждали много о различных практических условиях команды WHERE в нашем MySQL учебнике. Это очень полезно, чтобы понять, как эффективно использовать предложение WHERE во время удаления: 25 Основных примеров команды WHERE в MySQL.
Установка
Установите MariaDB:
emerge -a dev-db/mariadb
Выполните базовую настройку, задав пароль пользователю root:
emerge —config dev-db/mariadb
Configuring pkg...
* Trying to get password for mysql 'root' user from 'mysql' section ...
* Trying to get password for mysql 'root' user from 'client' section ...
* Please provide a password for the mysql 'root' user now
* or through the /root/.my.cnf file.
* Avoid characters in the password
>
* Retype the password
>
* Creating the mysql database and setting proper permissions on it ...
...
В выводе указана вся основная информация.
Запустите MariaDB:
/etc/init.d/mysql start
Добавьте SQL-сервер в автозагрузку:
rc-update add mysql
Example Option Files
Most MariaDB installations include a sample MariaDB option file called . On older releases, you would have also found the following option files:
However, these option files are now very dated for modern servers, so they were removed in MariaDB 10.3.1.
In source distributions, the sample option files are usually found in the directory, and in other distributions, the option files are usually found in the directory that is relative to the MariaDB base installation directory.
You can copy one of these sample MariaDB option files and use it as the basis for building your server’s primary MariaDB option file.
Example Minimal Option File
The following is a minimal my.cnf file that you can use to test MariaDB.
# Uncomment these if you want to use a nonstandard connection to MariaDB #socket=/tmp/mysql.sock #port=3306 # This will be passed to all MariaDB clients #password=my_password # The MariaDB server # Directory where you want to put your data data=/usr/local/mysql/var # Directory for the errmsg.sys file in the language you want to use language=/usr/local/share/mysql/english # This is the prefix name to be used for all log, error and replication files log-basename=mysqld # Enable logging by default to help find problems general-log log-slow-queries
Example Hybrid Option File
The following is an extract of an option file that one can use if one wants to work with both MySQL and MariaDB.
# Example mysql config file. socket=/tmp/mysql-dbug.sock port=3307 # This will be passed to all mysql clients password=my_password # Here are entries for some specific programs # The following values assume you have at least 32M ram # The MySQL server temp-pool key_buffer_size=16M datadir=/my/mysqldata loose-innodb_file_per_table datadir=/my/data default-storage-engine=aria loose-mutex-deadlock-detector max-connections=20 language=/my/maria-5.5/sql/share/english/ socket=/tmp/mysql-dbug.sock port=3307 language=/my/maria-10.1/sql/share/english/ socket=/tmp/mysql2-dbug.sock quick max_allowed_packet=16M no-auto-rehash loose-abort-source-on-error
Пример выбор отдельных столбцов из одной таблицы
При использовании оператора SELECT в MariaDB вам не нужно выбирать все столбцы из таблицы. Вместо этого вы можете выбрать отдельные столбцы, которые вы хотели бы вернуть в своем наборе результатов.
Например:
PgSQL
SELECT site_id, site_name
FROM sites
WHERE site_id < 32
ORDER BY site_id ASC, site_name DESC;
|
1 |
SELECTsite_id,site_name FROMsites WHEREsite_id<32 ORDERBYsite_idASC,site_nameDESC; |
В этом MariaDB примере SELECT будут возвращены только поля site_id и site_name из таблицы sites, где site_id меньше 32. Результаты сортируются по site_id в порядке возрастания, а затем site_name в порядке убывания.
Default Option File Locations
MariaDB reads option files from many different directories by default. See the sections below to find out which directories are checked for which system.
For an exact list of option files read on your system by a specific program, you can execute:
$program --help --verbose
For example:
$ mysqld --help --verbose mysqld Ver 10.3.13-MariaDB-log for Linux on x86_64 (MariaDB Server) Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Starts the MariaDB database server. Usage: mysqld Default options are read from the following files in the given order: /etc/my.cnf ~/.my.cnf The following groups are read: mysqld server mysqld-10.3 mariadb mariadb-10.3 client-server galera ....
The option files are each scanned once, in the order given by . The effect of the configuration options are as if they would have been given as command line options in the order they are found.
Default Option File Locations on Linux, Unix, Mac
On Linux, Unix, or Mac OS X, the default option file is called . MariaDB looks for the MariaDB option file in the locations and orders listed below.
The locations are dependent on whether the option was defined when MariaDB was built. This option is usually defined as when building RPM packages, but it is usually not defined when building DEB packages or binary tarballs.
When the DEFAULT_SYSCONFDIR cmake option was not defined, MariaDB looks for the MariaDB option file in the following locations in the following order:
| Location | Scope |
|---|---|
| Global | |
| Global | |
| Server | |
| Server | |
| defaults-extra-file | File specified with , if any |
| User |
When the DEFAULT_SYSCONFDIR cmake option was defined, MariaDB looks for the MariaDB option file in the following locations in the following order:
| Location | Scope |
|---|---|
| Global | |
| Server (from MariaDB 10.6) | |
| Server | |
| defaults-extra-file | File specified with , if any |
| User |
-
(from MariaDB 10.6) or is the environment variable containing the path to the directory holding the server-specific file. If is not set, and the server is started with mysqld_safe, is set as follows:
- If there is a file in the MariaDB data directory, but not in the MariaDB base directory, is set to the MariaDB data directory.
- Else, is set to the MariaDB base directory.
- Note that if is set (from MariaDB 10.6), will not be used, even if set.
Default Option File Locations on Windows
On Windows, the option file can be called either or . MariaDB looks for the MariaDB option file in the following locations in the following order:
| Location | Scope |
|---|---|
| Global | |
| Global | |
| Global | |
| Global | |
| Global | |
| Global | |
| Server | |
| Server | |
| Server | |
| Server | |
| Server (from MariaDB 10.6) | |
| Server (from MariaDB 10.6) | |
| Server | |
| Server | |
| defaults-extra-file | File specified with , if any |
- The is the directory returned by the function. The value is usually . To find its specific value on your system, open and execute:
echo %WINDIR%
- The is the directory returned by the function. The value may be a private for the application, or it may be the same as the returned by the function.
- is the parent directory of the directory where is located. For example, if is in , then would be .
- (from MariaDB 10.6) or is the environment variable containing the path to the directory holding the server-specific file.
- Note that if is set (from MariaDB 10.6), will not be used, even if set.
Default Option File Hierarchy
MariaDB will look in all of the above locations, in order, even if has already found an option file, and it’s possible for more than one option file to exist. For example, you could have an option file in with global settings for all servers, and then you could another option file in (i.e.your user account’s home directory) which will specify additional settings (or override previously specified setting) that are specific only to that user.
Option files are usually optional. However, if the option is set, and if the file does not exist, then MariaDB will raise an error. If the option is set, then MariaDB will only read the option file referred to by this option.
If an option or system variable is not explicitly set, then it will be set to its default value. See Server System Variables for a full list of all server system variables and their default values.
4. Возможности и функциональность
В целом MariaDB развивается быстрее и имеет больше возможностей. Эти возможности касаются оптимизации, улучшения работы с памятью, и много другого. Обычно, со временем, эти возможности переносятся в MySQL. Например, та же поддержка GIS появилась в MariaDB раньше, чем в MySQL. Среди прочего MariaDB имеет множество улучшений производительности Inodb, MyISAM и движка обработки запросов, поддерживает GIS, ликвидацию таблиц, виртуальные и динамические колонки, репликацию с несколькими источниками, роли и многое другое.
Но у MariaDB есть и свои минусы, она не поддерживает некоторые возможности, которые есть в MySQL. А именно, MariaDB несовместима с синтаксисом JSON MySQL, не поддерживаются плагины ngram, MeCab, MySQL X, а также пространства таблиц, которые позволяют присваивать данные нескольким таблицам одновременно. Но разработчики активно работают над исправлением недостатков.
Для тех, кого интересуют кластеры MySQL будет интересно то, что в MariaDB используется новая система репликации Galera, прием ее работа отличается от стандартного master-salve. Galera разрабатывается с 2007 года, но она никогда не включалась в официальную версию MySQL.
Добавление базы в MariaDB
Для работы с базой необходимо после создания добавить пользователя к этой базе и назначить права.
Добавление базы с параметрами
MariаDB > CREATE DATABASE `base` CHARACTER SET utf8 COLLATE utf8_general_ci; = вывод команды = Query OK, 1 row affected (0.00 sec)
Просмотр пользователей с выводом их прав
MariaDB > SELECT User,Host FROM mysql.user; = вывод команды = +--------+-----------+ | User | Host | +--------+-----------+ | root | localhost | | mysql | localhost | +--------+-----------+ 2 rows in set (0.005 sec)
Права пользователя баз данных MariaDB
В случае необходимости подключатся к базе с других компьютеров необходимо создать пользователя с нужным параметром и дать права на доступ в настройках сервера MariaDB!
Права на доступ к серверу баз данных делается в двух местах:
- Параметр bind-address=0.0.0.0 в конфигурационном файле самого сервера баз MariaDB разрешающий подключатся с любого адреса (или укажите конкретный IP) в разделе ;
- Права пользователя на возможность удаленного подключения к базе данных.
Для работы с базами мне удобно использовать и эта программа позволяет настроить доступ к базам работающих на разных серверах.
Менять права пользователя root не желательно, но можно создать пользователя и дать ему полные права. Держать ещё одного пользователя с полными правами не разумно, но иногда необходимо. Добавляются пользователю полные права командой:
MariaDB > GRANT ALL PRIVILEGES ON *.* to 'имя пользователя'@'%';
После добавления пользователя с полными правами и имеющегося PhpMyAdmin настроенного для подключения к другим серверам баз данных, можно зайти и создать все необходимые базы и пользователей.
Например, ниже представлена полная версия команд после выполнения которых будет создан пользователь sevo44 с полными правами:
mysql -u root -p Enter password: Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 8 Server version: 10.4.8-MariaDB MariaDB Server Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB > CREATE USER `sevo44`@`%` IDENTIFIED BY 'ПАРОЛЬ'; Query OK, 0 rows affected (0.008 sec) MariaDB > GRANT ALL PRIVILEGES ON *.* to 'sevo44'@'%'; Query OK, 0 rows affected (0.017 sec) MariaDB > FLUSH PRIVILEGES; Query OK, 0 rows affected (0.001 sec) MariaDB > exit Bye
Для безопасности я никогда не создаю пользователя с полными правами который может подключатся с удаленных мест. Для каждой базы свой пользователь!
В случае если надо поменять права имеющемуся пользователю это делается следующей командой, после подключения к серверу баз данных:
# Команда смены прав доступа пользователя на подключение с любого адреса (параметр %) MariaDB > UPDATE mysql.user SET Host='%' WHERE Host='localhost' AND User='имя_пользователя'; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 # Команда смены прав доступа на базы MariaDB > UPDATE mysql.db SET Host='%' WHERE Host='localhost' AND User='имя_пользователя'; Query OK, 0 rows affected (0.03 sec) Rows matched: 0 Changed: 0 Warnings: 0
Добавление пользователя
# Права на доступ только с localhost MariаDB > CREATE USER `base_user`@localhost IDENTIFIED BY 'ПАРОЛЬ'; # Права на доступ с любого адреса (при использовании знаков в названиях код заключается в кавычки! MariаDB > CREATE USER `base_user`@`%` IDENTIFIED BY 'ПАРОЛЬ'; # Права на доступ с адреса 10.10.0.2 (при использовании знаков в названиях код заключается в кавычки! MariаDB > CREATE USER `base_user`@`10.10.0.2` IDENTIFIED BY 'ПАРОЛЬ'; = правильный вывод команды для любой команды = Query OK, 0 rows affected (0.10 sec)
Назначение пользователя базе
# При назначении прав выставляем пользователя с нужными правами! MariaDB > GRANT ALL PRIVILEGES ON base.* to base_user@localhost; = вывод команды = Query OK, 0 rows affected (0.04 sec)
После всех манипуляция с базами необходимо обновить права доступа
MariаDB > FLUSH PRIVILEGES; = вывод команды = Query OK, 0 rows affected (0.02 sec)
Создание пользователей и предоставление им прав доступа в MySQL или MariaDB
Павел Соловьёв
Full Stack Developer
#mysql
22 октября, 2019
310
MySQL является одной из самых популярных систем управления базами данных. В этом руководстве мы рассмотрим шаги, необходимые для создания нового пользователя MySQL и дадим ему права доступа в CentOS, Debian или Ubuntu.
Вся операции будут выполняться внутри консоли MySQL под root пользователем:
Вам будет предложено ввести пароль для root пользователя.
Создание нового пользователя
Вы можете создать нового пользователя MySQL с помощью следующей команды:
где:
- user — имя пользователя MySQL, который будет создан
- password — пароль, который мы хотим присвоить этому пользователю.
Все команды MySQL выполняются с точкой с запятой ().
Предоставление прав доступа для пользователя
Следующее, что нам нужно будет сделать, это предоставить привилегии этому пользователю, чтобы он мог получить доступ к MySQL клиенту и работать с соответствующей базой или базами данных:
где:
- database — название базы в MySQL, к которой мы предоставляем доступ.
- table — название таблицы БД, к которой мы предоставляем доступ
Вы можете использовать символ звездочки (*), если захотите предоставить доступ ко всем базам данных/таблицам:
или
С помощью первой команды мы предоставляем пользователю все права доступа ко всем таблицам, относящимся к базе данных с именем . Во втором случае, для пользователя предоставляется доступ ко всем базам данных.
Существует несколько типов привилегий, которые могут быть предоставлены пользователю. Вы можете найти полный список привилегий, поддерживаемых MySQL, здесь.
Вот список наиболее часто используемых привилегий MySQL:
- — пользователь имеет полный доступ к базе данных.
- — пользователю разрешено создавать базы данных и таблицы.
- — пользователь имеет право удалять базы данных и таблицы.
- — пользователь может удалять строки из определенной таблицы.
- — пользователь может вставлять строки в определенную таблицу.
- — пользователю разрешено читать базу данных.
- — пользователь может обновлять строки таблицы.
Вот пример синтаксиса, в котором только три типа привилегий предоставляются пользователю:
Для того, чтобы изменения вступили в силу и привилегии были сохранены, в конце должна быть выполнена следующая команда:
Вот ещё несколько примеров:
Предоставление всех привилегий пользователю по определенной базе данных:
Предоставление всех привилегий пользователю на всех базы данных:
Предоставление пользователю всех прав доступа для определенной таблицы в базе данных:
Отмена привилегий у пользователя MySQL
Синтаксис отзыва одного или нескольких привилегий с учетной записи пользователя практически идентичен синтаксису, используемому при предоставлении привилегий.
Например, чтобы отменить все привилегии учетной записи пользователя в определенной базе данных, воспользуйтесь следующей командой:
Удаление существующей учетной записи пользователя MySQL
Чтобы удалить учетную запись пользователя MySQL, используйте оператор :
Вышеприведенная команда удалит учетную запись пользователя и его привилегии.
Заключение
Это руководство охватывает только основы, но оно должно стать хорошим началом для тех, кто хочет научиться создавать новые учетные записи пользователей MySQL и предоставлять им права доступа.
Об авторе
Павел Соловьёв
5. Поддержка движков хранения данных
Система управления базами данных MariaDB поддерживает намного больше движков для хранения данных. Большинство этих движков доступны в качестве плагинов для MySQL, но в MariaDB они включены в официальный релиз. Это означает, что движки правильно интегрированы и будут хорошо работать. Вот список поддерживаемых движков:
- Aria;
- XtraDB — улучшенная версия InnoDB;
- FederatedX — улучшенная версия Federated;
- OQGRAPH;
- SphinxSE;
- IBMDB2I;
- TokuDB;
- Cassandra;
- CONNECT;
- SEQUENCE;
- Spider;
- ColumnStore;
- MySIAM.
Напомню, что оригинальная MySQL поддерживает по умолчанию только три типа таблиц — Aria, MySIAM и InnoDB. Это важный аспект в выборе MySQL или MariaDB.
Заключение
В этом уроке я ознакомил вас с основами работы с хранимыми процедурами и с некоторыми специфическими свойствами, связанными с ней. Конечно, вам нужно будет углубить знания в таких областях, как безопасность, выражения SQL и оптимизация, прежде чем стать настоящим гуру MySQL процедур.
Вы должны подсчитать, какие преимущества даст вам использование хранимых процедур в вашем конкретном приложении, и только потом создавать лишь необходимые процедуры. В общем, я использую процедуры; по-моему, их стоит внедрять в проекты в следствие их безопасности, обслуживания кода и общего дизайна. К тому же, не забывайте, что над процедурами MySQL все еще ведется работа. Ожидайте улучшений, касающихся функциональности и улучшений. Прошу, не стесняйтесь делиться мнениями.
