Создание пользователей mysql/mariadb и предоставление прав доступа

Open Community

MariaDB Server undoubtedly has an open community, with governance mixed between MariaDB Foundation and MariaDB Corporation. There are open developer meetings on average about twice per year, two mailing lists (one for developers and users), an IRC channel and an open JIRA ticket system that logs bugs and feature requests.

Percona Server for MySQL also has an open community. Developer meetings are not open to general contributors, but there is a mailing list, an IRC channel and two systems – Launchpad and JIRA – for logging bugs and feature requests.

MySQL also has an open community where developer meetings are also not open to general contributors. There are many mailing lists, there are a few IRC channels and there is the MySQL bugs system. The worklogs are where the design for future releases happens, and these are opened up when their features are fully developed and source-code-pushed.

From a source code standpoint, MySQL makes pushes to Github when a release is made; whereas open source development happens for Percona Server for MySQL and MariaDB Server on Github.

Feature development on MySQL continues in leaps and bounds, and Oracle has been an excellent steward of MySQL. Please refer to The Complete List of Features in 5.7, as well as The Unofficial MySQL 8 Optimiser Guide.

Linux distributions have chosen MariaDB Server 5.5, and some have chosen MariaDB Server 10.0/10.1 when there was more backward compatibility to MySQL 5.5/5.6. It is the “default” MySQL in many Linux distributions (such as Red Hat Enterprise Linux, SUSE and Debian). However, Ubuntu still believes that when you ask for MySQL you should get it (and that is what Ubuntu ships).

One of the main reasons Debian switched was due to the way Oracle publishes updates for security issues. They are released as a whole quarterly as Critical Patch Updates, without much detail about individual fixes. This is a policy that is unlikely to change but has had no adverse effects on distribution.

All projects actively embrace contributions from the open community. MariaDB Server does include contributions like the MyRocks engine developed at Facebook, but so does Percona Server for MySQL. Oracle accepts contributions from a long list of contributors, including Percona. Please see Licensing information for MySQL 5.7 as an example.

Версии

Номера версий MariaDB до версии 5.5 следуют схеме нумерации MySQL. Таким образом, MariaDB 5.5 предлагает все функции, какие есть в MySQL 5.5. Но существует пробел в версиях MySQL между 5.1 и 5.5, в то время как MariaDB имеет точечные выпуски (англ. point releases) под номерами 5.2 и 5.3.

После версии 5.5 разработчики MariaDB решили начать серию под 10-й нумерацией, как попытка сделать понятным, что MariaDB 10.0 не будет импортировать все функции из MySQL 5.6; тем не менее, они могут быть импортированы в будущих версиях. И поскольку некоторые новые специфичные функции были разработаны именно в MariaDB, разработчики решили, что необходимо существенно изменить номер версии.

What is MariaDB?

How comparable is performance when we’re talking about MariaDB vs MySQL? Well first you need to know what MariaDB is. It’s a close relative MySQL, a software fork of MySQL that its developers came up with in 2009. After Oracle Corp picked up MySQL they wanted to create a stand-in.

MariaDB has SQL in its DNA and allows for ACID-style data processing with assured atomicity, durability, isolation, and consistency for transactions. It has lots of features (which developers at MariaDB Corp have worked hard to ensure are similar). They include support for JSON APIs, replication of parallel data and a variety of storage engines, like MariaDB, Aria, ColumnStore, InnoDB, MyRocks, Spider, TokuDB, and Cassandra.

But there are some areas that will be incompatible between versions. For instance, from version 10.1 on, MariaDB stores JSON data using a different format to MySQL 5.7. To get around this, users who want to copy columns of JSON objects from MySQL to MariaDB can either convert them to the format that the latter uses or run statement-based replication jobs with SQL.

MariaDB Corp. offers training, migration services, remote management, and various tailored products to subscribers. MariaDB Foundation, which was set up in 2012, looks after the source code to make sure that the software remains open source.

Шаг 4 — Тестирование MariaDB

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

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

Если MariaDB не запущена, вы можете воспользоваться командой для запуска.

В качестве дополнительной проверки вы можете попробовать подключиться к базе данных с помощью инструмента , который позволяет запускать команды администрирования. Например, эта команда позволяет подключиться к MariaDB в качестве root user и вернуть версию с помощью сокета Unix:

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

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

Это означает, что MariaDB запущена и работает и что ваш пользователь сможет успешно пройти аутентификацию.

High Availability

High Availability is an exciting topic in the MySQL world, considering the server itself has been around for over 22 years. There are so many solutions out there, and some have had evolution as well.

MySQL provides MySQL Cluster (NDBCLUSTER) (there is no equivalent in the MariaDB world). MySQL also provides group replication (similar to Galera Cluster). Combined with the proxy MySQL Router, and the mysqlsh for administration (part of the X Protocol/X Dev API), you can also get MySQL InnoDB Cluster.

We benefit from the above at Percona, but also put lots of engineering work to make Percona XtraDB Cluster.

MariaDB Server only provides Galera Cluster.

Extensibility

MySQL and MariaDB Server have a storage engine interface, and this is how you access all engines, including the favored InnoDB/Percona XtraDB. It is worth noting that Percona XtraDB was the default InnoDB replacement in MariaDB Server 5.1, 5.2, 5.3, 5.5, 10.0 and 10.1. But in MariaDB Server 10.2, the InnoDB of choice is upstream MySQL.

Stock MySQL has provided several storage engines beyond just InnoDB (the default) and MyISAM. You can find out more information about 5.7 Supported Engines.

Percona Server for MySQL includes a modified MEMORY storage engine, ships Percona XtraDB as the default InnoDB and also ships TokuDB and MyRocks (currently experimental). MyRocks is based on the RocksDB engine, and both are developed extensively at Facebook.

MariaDB Server includes many storage engines, beyond the default InnoDB. MyISAM is modified with segmented key caches, the default temporary table storage engine is Aria (which is a crash-safe MyISAM), the FederatedX engine is a modified FEDERATED engine, and there are more: CONNECT, Mroonga, OQGRAPH, Sequence, SphinxSE, SPIDER, TokuDB and of course MyRocks.

Storage engines have specific use cases and have different levels of feature completeness. You should thoroughly evaluate a storage engine before choosing it. We believe that over 90% of installations are fine with just InnoDB or Percona XtraDB. Percona TokuDB is another engine that users who need compression could use. We naturally expect more usage in the MyRocks sphere going forward.

Числовые типы данных

Ниже приведены числовые типы данных в MariaDB:

Преимущества MariaDB перед MySQL

В MariaDB добавлены оптимизации, которые повышают производительность СУБД по сравнению с оригинальным MySQL.

Представления

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

Колоночное хранилище

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

Более высокая производительность на SSD

MariaDB предоставляет механизм хранения MyRocks, который позволяет хранить данные в RocksDB. RocksDB — это встраиваемая база данных, которая была разработана для повышения производительности обработки данных, хранимых на SSD-накопителях.

Сегментированный кеш ключей

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

Виртуальные столбцы таблицы

Интересная функция, которую поддерживает MariaDB — это виртуальные столбцы. Эти столбцы способны выполнять вычисления на уровне базы данных. Это позволяет перенести типовые вычисления с приложений в сервер СУБД. Эта функция не доступна в MySQL.

Параллельное выполнение запросов

Одна из последних версий MariaDB — 10.0 допускает параллельное выполнение нескольких запросов. Идея состоит в том, что некоторые запросы от Master могут быть переданы на выполнение на ведомые серверы (slave). Этот параллелизм в выполнении запросов, безусловно, обеспечивает MariaDB преимущество над MySQL.

Пул потоков

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

Бэкенды хранения данных

MariaDB предоставляет несколько мощных механизмов хранения, которые не доступны в MySQL. Например, XtraDB, Aria и т. д. Чтобы настроить эти механизмы хранения для MySQL, вам необходимо установить их вручную.

Совместимость

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

3: Настройка поддержки парольной аутентификации

В новых установках Debian пользователь root MariaDB по умолчанию поддерживает аутентификацию с помощью плагина unix_socket, а не с помощью пароля. Это во многих случаях позволяет повысить безопасность и удобство использования, но также может усложнить работу, если вам необходимо разрешить доступ внешней программе (например, phpMyAdmin).

Поскольку сервер использует root-пользователя для таких задач, как ротация логов, запуск и остановка сервера, аутентификацию учетной записи root лучше не изменять. Изменение учетных данных в файле /etc/mysql/debian.cnf может сработать на начальном этапе, но дальнейшие обновления пакетов перезапишут эти изменения. Вместо этого разработчики рекомендуют создать отдельную учетную запись администратора с парольной аутентификацией.

Итак, создайте аккаунт под названием admin с теми же правами, что и у root, но с поддержкой парольной аутентификации. Для этого откройте командную строку MariaDB в терминале:

Теперь создайте нового пользователя с привилегиями root и поддержкой парольной аутентификации. Укажите в команде имя и пароль своего пользователя.

Сбросьте привилегии:

Закройте оболочку MariaDB:

Управление пользователями

В MariaDB вы можете создавать пользователей и группы для предоставления определенных прав доступа конкретным пользователям в базах данных и таблицах. Кроме того, MariaDB может использовать Linux PAM (Pluggable Authentication Modules) систему для аутентификации.При управлении пользователями из MySQL пользователи по умолчанию хранятся в таблице user в базе данных mysql. Для создания новых пользователей вы будете использовать CREATE USER;. Как пользователь, который создает нового пользователя, вам нужна привилегия CREATE USER или INSERT в базе данных mysql.При создании пользователя обычно включается @hostname на имя пользователя (так что результатом будет username@hostname). Это способ позволит вам отличать пользователей, которые могут войти только с локального хоста, от пользователей, которые могут войти с других хостов.Если вы хотите создать пользователя lisa, используйте команду CREATE USER lisa@localhost IDENTIFIED BY ‘password’;. Команда создает пользователя с именем lisa и паролем password. Пароли хранятся в зашифрованном виде в базе данных MariaDB.В спецификации имени хоста у вас есть несколько вариантов. Вы можете использовать имя хоста или IP-адрес и знак % в качестве подстановочного знака(маски). Например, lisa@% относится к пользователю lisa, который может войти с любого хоста.Когда пользователь больше не нужен, используйте DROP USER user@host; чтобы удалить пользователя

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

Привилегии могут предоставляеться на определенные таблицы, но также и ко всей базе данных.Например, GRANT SELECT, UPDATE, DELETE, INSERT on addressbook.names to lisa@localhost; предоставит пользователю lisa базовые права доступа к таблице names в базе данных addressbook.Давайте посмотрим на еще несколько примеров предоставления привилегий:Даёт права на SELECT для конкретной таблицы в конкретной БД.

Даёт права на SELECT для всех таблиц в конткретной БД.

Даёт права на SELECT для всех таблиц и БД.

Даёт права на CREATE, ALTER, DROP для всех таблиц в конкретной БД.

Делает user суперпользователем все таблиц и баз.

При работе с привилегиями важно перезапустить все привилегии после их изменения. Чтобы сделать это, используйте команду FLUSH PRIVILEGES;

Чтобы показать привилегии, назначенные определенному пользователю, вы можете использовать команду SHOW GRANTS FOR user@host;.В упражнении 2 вы создадите простую базу данных под рутом.

Упражнение 2. Создание базы данных.

1. Введите mysql -u root -p, чтобы войти в систему как root.2. Введите create database videos; чтобы создать базу данных с названием videos.3. Введите USE videos; чтобы переключиться на базу данных videos.4. Теперь давайте создадим несколько столбцов: CREATE TABLE videos(title VARCHAR(40), actor VARCHAR(40), year INT, registration INT);5. Давайте вставим некоторые данные:  INSERT INTO videos (registration,title,actor,year) VALUES(1,’Basic Instinct’,’Sharon Stone’, 1992);6. Повторите пункт 5, но чтобы ввести следующие видео:   2,Pretty Woman, Julia Roberts, 1990   3,Terminator, Arnold Schwarzenegger, 1984   4,Jurassic Park, Harrison Ford, 19927. Введите SELECT * from videos; чтобы показать обзор всей базы данных. Вы должны увидеть результат, как показано в листинге 7.Листинг 7

8. Создайте пользователя командой CREATE USER julia@’%’ IDENTIFIED BY ‘secret’;9. Предоставьте разрешения пользователю julia с помощью команды GRANT SELECT,INSERT,UPDATE,DELETE ON videos.* TO julia@’%’;10. Выполните FLUSH PRIVILEGES; чтобы обновить привилегии.11. Выполните  DESCRIBE videos; чтобы показать обзор записей в базе данных videos.12. Вставьте еще одно видео:  INSERT INTO videos(registration,title,actor,year) VALUES (5,’The Last Stand’, ‘Arnold Schwarzenegger’, 2013);13. Чтобы отобразить список всех записей, в которых значение поля актера установлено на Арни:  SELECT * FROM videos WHERE actor=‘Arnold Schwarzenegger’;14. Выполните quit, чтобы закрыть интерфейс оболочки MySQL.

Шаг 4 — Тестирование MariaDB

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

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

Если MariaDB не запущена, вы можете воспользоваться командой для запуска.

В качестве дополнительной проверки вы можете попробовать подключиться к базе данных с помощью инструмента , который позволяет запускать команды администрирования. Например, следующая команда предписывает подключиться к MariaDB как пользователь root, используя сокет Unix, и вывести номер версии:

Результат должен выглядеть примерно так:

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

Это означает, что MariaDB запущена и работает и что ваш пользователь сможет успешно пройти аутентификацию.