Set the maximum number of MySQL connections
MySQL/MariaDB uses an instruction max_connections which specifies how many concurrent connections are currently allowed in the server. Too many connections result in high memory consumption as well as high CPU load. For small websites, the connections can be specified to 100-200 and larger ones may need 500-800 and more. The max_connections can be dynamically changed using the SQL query. In this example, I have set the value to 200.
$ mysql -u root -p
mysql> set global max_connections=200;
Output :
Enable MySQL slow query log
Logging queries that take a very long time to execute makes it easier to troubleshoot the database problems. The slow query log can be enabled by adding the following lines in the MySQL/MariaDB configuration file.
slow-query-log=1 slow-query-log-file= /var/lib/mysql/mysql-slow-query.log long-query-time=1
Where the first variable enables the slow query log
The second variable defines the log file directory
The third variable defines the time to complete a MySQL query
Restart the MySQL/MariaDB service and monitor the log
$ systemctl restart mysql
$ systemctl restart mariadb
$ tail -f /var/lib/mysql/mysql-slow-query.log
Создание директории
Теперь необходимо создать каталог, в котором будут располагаться файлы вашего ресурса. В любой операционной системе Линукс это делается следующей командой:
sudo mkdir /var/www/sitename.ru/public_html
В данном примере sitename.ru необходимо заменить на доменное имя сайта. Что касается файлов CMS, их следует загрузить в папку public_html. По завершении загрузки необходимо передать права управления веб-серверу. Здесь есть различия в именах, используемых для обозначения Nginx. Для Debian-подобных дистрибутивов команда будет выглядеть так:
sudo chown -R www-data:www-data /var/www/html/*
В Centos иначе:
sudo chown -R nginx:nginx /var/www/html/*
Шаг 3 — (необязательно) Создание административного пользователя с аутентификацией по паролю
При установке MariaDB 10.3 в системах Ubuntu для пользователя root в MariaDB настраивается аутентификация с помощью плагина , а не с помощью пароля. Во многих случаях это обеспечивает более высокую безопасность и удобство, однако это также может осложнить ситуацию, если вам нужно предоставить права администратора внешней программе (например, phpMyAdmin).
Поскольку для ротации журналов, запуска и остановки сервера используется учетная запись root, лучше всего не менять учетные данные root. Изменение учетных данных в файле конфигурации может работать на начальном этапе, но при обновлении пакета изменения могут быть перезаписаны. Вместо изменения учетной записи root, мантейнеры пакетов рекомендуют создать отдельную административную учетную запись для доступа по паролю.
Для этого мы создадим новую учетную запись admin с теми же привилегиями, что и у учетной записи root, но настроенную для аутентификации с использованием пароля. Откройте в терминале командную строку MariaDB:
Затем создайте нового пользователя с правами root и с аутентификацией на основе пароля. Измените имя пользователя и пароль на предпочитаемые:
Очистите привилегии, чтобы они были сохранены и доступны в текущем сеансе:
После этого закройте оболочку MariaDB:
Теперь давайте проверим установку MariaDB.
Step 1 Remove MariaDB 5.5/10.0/10.1/10.2/10.3
Before installing it is recommended to backup your databases, although it is not necessary if you followed this steps carefully.
First backup your current my.cnf config :
cp /etc/my.cnf /etc/my.cnf.bak
Now we need to remove current version of mariadb 5.5 installed on centos 7 :
For MariaDB 5.5 :
service mariadb stop / service mysql stop rpm -e --nodeps galera yum remove mariadb mariadb-server
At this point MariaDB 5.5 will be removed completely but the databases are not removed don’t need to worry.
For MariaDB 10.0/10.1/10.2/10.3 :
service mysql stop rpm -e --nodeps galera yum remove MariaDB-server MariaDB-client
At this point MariaDB 10.0/10.1/10.2/10.3 will be removed completely but the databases are not removed don’t need to worry.
Then Install MariaDB 10.4 :
Установка пакетов MariaDB с YUM
После настройки репозитория вы можете установить MariaDB, выполнив команду yum . Конкретная команда, которую вы будете использовать, будет зависеть от того, какие конкретные пакеты вы хотите установить.
Установка наиболее распространенных пакетов с YUM
MariaDB начиная с 10.4
В MariaDB 10.4 и новее, чтобы установить наиболее распространенные пакеты, выполните следующую команду:
sudo yum install MariaDB-server galera-4 MariaDB-client MariaDB-shared MariaDB-backup MariaDB-common
MariaDB до 10.3
В MariaDB 10.3 и ранее, чтобы установить наиболее распространенные пакеты, выполните следующую команду:
sudo yum install MariaDB-server galera MariaDB-client MariaDB-shared MariaDB-backup MariaDB-common
Чтобы установить сервер MariaDB Server,выполните следующую команду:
sudo yum install MariaDB-server
Установка кластера MariaDB Galera Cluster с YUM
Процесс установки MariaDB Galera Cluster с репозиторием MariaDB практически такой же, как установка стандартного сервера MariaDB.
В MariaDB 10.1 и более поздних версиях поддержка Galera Cluster была включена в стандартные пакеты MariaDB Server, поэтому вам нужно будет установить пакет , как обычно.
В MariaDB 10.4 и более поздних версиях вам также необходимо установить пакет , чтобы получить библиотеку поставщика Galera 4 wsrep.
В MariaDB 10.3 и ранее вам также необходимо установить пакет , чтобы получить библиотеку поставщика Galera 3 wsrep.
MariaDB начиная с 10.4
В MariaDB 10.4 и новее, чтобы установить MariaDB Galera Cluster, вы можете выполнить следующую команду:
sudo yum install MariaDB-server MariaDB-client galera-4
MariaDB до 10.3
В MariaDB 10.3 и ранее, чтобы установить MariaDB Galera Cluster, вы могли выполнить следующую команду:
sudo yum install MariaDB-server MariaDB-client galera
Если вы еще не импортировали открытый ключ MariaDB GPG, предложит вам импортировать его после загрузки пакетов, но до того, как он предложит вам их установить.
См. Раздел Кластер MariaDB Galera для получения дополнительной информации о кластере MariaDB Galera.
Установка MariaDB Clients and Client Libraries с помощью YUM
В MariaDB 10.2 и более поздних версиях MariaDB Connector / C был включен в качестве клиентской библиотеки. Однако имя пакета для клиентской библиотеки не изменилось.
Для установки клиентов и клиентских библиотек выполните следующую команду:
sudo yum install MariaDB-client MariaDB-shared
Чтобы установить Mariabackup , выполните следующую команду:
sudo yum install MariaDB-backup
Установка плагинов с YUM
Также может потребоваться установка некоторых плагинов .
Например, чтобы установить плагин проверки пароля cracklib_password_check , выполните следующую команду:
sudo yum install MariaDB-cracklib-password-check
Установка отладочных информационных пакетов с помощью YUM
MariaDB начиная с 5.5.64
MariaDB впервые добавил пакеты debuginfo в MariaDB 5.5.64 , MariaDB 10.1.39 , MariaDB 10.2.23 , MariaDB 10.3.14 и MariaDB 10.4.4 .
MariaDB также содержит пакеты debuginfo . Этот пакет может понадобиться при отладке проблемы .
Установка отладочной информации для наиболее распространенных пакетов с помощью YUM
Чтобы установить debuginfo для наиболее распространенных пакетов, выполните следующую команду:
sudo yum install MariaDB-server-debuginfo MariaDB-client-debuginfo MariaDB-shared-debuginfo MariaDB-backup-debuginfo MariaDB-common-debuginfo
Установка отладочной информации для сервера MariaDB Server с помощью YUM
Чтобы установить отладочную информацию для сервера MariaDB, выполните следующую команду:
sudo yum install MariaDB-server-debuginfo
Установка отладочной информации для клиентов MariaDB и клиентских библиотек с помощью YUM
В MariaDB 10.2 и более поздних версиях MariaDB Connector / C был включен в качестве клиентской библиотеки. Однако имя пакета для клиентской библиотеки не изменилось.
Чтобы установить debuginfo для клиентов и клиентских библиотек, выполните следующую команду:
sudo yum install MariaDB-client-debuginfo MariaDB-shared-debuginfo
Установка отладочной информации для Mariabackup с YUM
Чтобы установить отладочную информацию для Mariabackup , выполните следующую команду:
sudo yum install MariaDB-backup-debuginfo
Установка отладочной информации для плагинов с помощью YUM
Для некоторых плагинов также может потребоваться установка debuginfo .
Например, чтобы установить debuginfo для плагина проверки пароля cracklib_password_check , выполните следующую команду:
Step 4 – Working with MariaDB
After installing and completing the configuration, connect to a MariaDB server using the following command.
sudo mysql -u root -p
Also try to create a new database, user and assign privileges to a database.
## CREATE DATABASE
MariaDB > CREATE DATABASE mydb;
## CREATE USER ACCOUNT
MariaDB > CREATE USER ‘dbuser’@’localhost’ IDENTIFIED BY ‘secret’;
## GRANT PERMISSIONS ON DATABASE
MariaDB > GRANT ALL ON mydb.* TO ‘dbuser’@’localhost’;
## RELOAD PRIVILEGES
MariaDB > FLUSH PRIVILEGES;
|
1 2 3 4 5 6 7 8 9 10 11 |
## CREATE DATABASE ## CREATE USER ACCOUNT ## GRANT PERMISSIONS ON DATABASE ## RELOAD PRIVILEGES |
You may also required install phpMyAdmin to manage MariaDB using web interface, which provides easy way to work.
Чем MariaDB лучше MySQL
MariaDB поддерживает больше систем хранения (Storage Engines).
В дополнении к стандартным MyISAM, BLACKHOLE, CSV, MEMORY, ARCHIVE, и MERGE движкам хранения, в MariaDB также имеются следующие:
- ColumnStore, система хранения, ориентированная на столбцы, оптимизирована для хранилища данных.
- MyRocks, система хранения с большим сжатием, добавлена с 10.2 версии
- Aria, замена для MyISAM с улучшенным кэшированием.
- FederatedX (замена для Federated)
- OQGRAPH.
- SphinxSE.
- TokuDB.
- CONNECT.
- SEQUENCE.
- Spider.
- Cassandra.
MariaDB имеет множество различных улучшений и оптимизаций в скорости обработки данных.
MariaDB дополнена новыми расширениями и функциями.
Скачать MariaDB для Windows
MariaDB является бесплатной, настоятельно рекомендуется её скачивать с официального сайта. Страница для скачивания MariaDB: https://downloads.mariadb.org/
Вы увидите несколько серий — несколько мажорных версий программы. Если вам не нужна какая-то специфическая совместимость с MySQL, то просто выберите самую последнюю версию и нажмите кнопку с «Download»
Поскольку эта программа работает на разных операционных системах, то в следующем окне вы увидите большой выбор файлов для скачивания.
Файлы Windows x86_64 — это 64-битные версии, а Windows x86 — 32-битные.
.zip — это портативные версии, которые нужно устанавливать самостоятельно, но которые дают полную свободу в тонкой подстройке. А .msi — это установщик для Windows.
В этой инструкции я покажу на примере работы с версией .zip.
На следующей странице достаточно нажать кнопку: «No thanks, just take me to the download»:
Вторичная оптимизация конфига MySQL
Под вторичной оптимизацией я подразумеваю тот тюнинг, который можно произвести только зная профиль нагрузки на БД: соотношение операций чтения и записи, долгие запросы и т.п.
Тюнинг performance_schema в MySQL
Опция performance_schema производит мониторинг всей БД, на что расходуется некоторая часть ресурсов, держать эту опцию постоянно включенной в продакшене крайне не рекомендуется, т.к. может замедлять время выполнения запросов до 25%. Объем потребляемых ресурсов зависит от конфигурации схемы, которую можно посмотреть выполнив запрос:
SHOW VARIABLES LIKE 'performance%';
А если выполнить от имени администратора БД этот запрос:
SHOW ENGINE performance_schema STATUS;
С помощью этого запроса можно понять все ли данные мониторятся, или что-то пропадает:
SHOW STATUS LIKE 'performance%';
Если какой-то счетчик оказался выше ноля, то нужно увеличить соответствующий параметр.
Именно на данных из performance_schema и основана вся фишка MySQLTuner! Чем дольше собираются данные, тем точнее будут рекомендации по оптимизации MySQL и MariaDB. Стоит учесть, что данные performance_schema обнуляются после каждой перезагрузки сервера, поэтому сначала лучше выполнить первичную конфигурацию, после чего оставить сервер под боевой нагрузкой на сутки для последующего анализа.
Работа с данными performance_schema
Переходим в базу данных performance_schema :
USE performance_schema;
И смотрим какие таблицы здесь есть:
SHOW TABLES;
Обратим внимание на наблицы с префиксом setup_, например:
SELECT * FROM setup_consumers; SELECT * FROM setup_instruments;
В них содержатся настройки того, что будет мониториться. С помощью UPDATE можно менять значение колонки ENABLED с NO на YES и наоборот.
Самые горячие таблицы
С помощью этого запроса можно узнать к каким таблицам происходит наибольшее число чтений и записей:
select substring_index(file_name, '/', -1) file_name, event_name, count_read, count_write from file_summary_by_instance where COUNT_READ+COUNT_WRITE > 0 order by COUNT_READ+COUNT_WRITE desc limit 30;
А этим запросом можно узнать статистику по блокировкам:
select event_name, source, sum(timer_wait) timer_wait from events_waits_history_long where event_name not like 'wait/io/file%' group by event_name, source order by 3 desc limit 30;
Install MariaDB Yum Repository in CentOS 8:
MariaDB Server 10.5.3 is the latest release therefore it is not available in CentOS 8 yum repositories.
MariaDB provides an official yum repository, that we can use to install MariaDB Server 10.5 on CentOS 8.
Create a yum repository file as follows.
# vi /etc/yum.repos.d/MariaDB.repo
Add following directives in this file.
# MariaDB 10.5 CentOS repository list - created 2020-05-29 08:37 UTC # http://downloads.mariadb.org/mariadb/repositories/ name = MariaDB baseurl = http://yum.mariadb.org/10.5/centos8-amd64 module_hotfixes=1 gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB gpgcheck=1
Build cache for MariaDB yum repository.
# dnf makecache CentOS-8 - AppStream 7.8 kB/s | 4.3 kB 00:00 CentOS-8 - Base 292 B/s | 3.9 kB 00:13 CentOS-8 - Extras 3.7 kB/s | 1.5 kB 00:00 MariaDB 164 kB/s | 512 kB 00:03 Metadata cache created.
Выбор версии MariaDB
/var/run/php5-fpm.sock уже должен присутствовать на сервере? в ubuntu 16.04 не нашел такого файла в /var/run
Существует 2 ветки MariaDB — 5.x и 10.x.
5.x — это версии, максимально приближенные к MySQL последних версий, они полностью совместимы с MySQL. Если вы только переходите на MariaDB с MySQL — эта линейка версий для вас.
10.x — новая линейка версий, которую начали после версии 5.5. Эти версии полностью не копируют все новые функции последних версий MySQL, но содержат свои уникальные функции. Они по прежнему совместимы с MySQL, но, если вы только начинаете использовать MariaDB — лучше начать с версии 5.5.
Configuring MariaDB
The configuration files and binaries for MariaDB are mostly the same as MySQL. For example, both use a configuration file called my.cnf. Even the daemon binary is called mysql. This is done done to ensure people can more easily migrate from MySQL.
The default configuration file is too liberal. It should not be used in either testing or production. If you are just playing around and learning the software, however, it should be fine.
The MariaDB installation includes several configuration file templates that can be used to quickly configure the server. The one you choose depends on how large you expect your databases to get.
| my-small.cnf | Ideal for servers with a very limited amount RAM (64MB or less) available for your databases. An example would be a small LAMP server hosting all web-related roles. |
|---|---|
| my-medium.cnf | Ideal for dedicated database servers with 128MB of available memory or less. Another good example for multi-role servers. |
| my-large.cnf | Ideal for for servers with at least 512MB of RAM available for the database server. |
| my-huge.cnf | Ideal for servers with 1GB of RAM or more available to the database server. |
- Backup the original configuration file by renaming it.
mv /etc/my.cnf /etc/my.bak
- Create a new my.cnf file by copying an existing template. We’ll use the medium server template for this example.
cp /usr/share/mysql/my-medium.cnf /etc/my.cnf
- Start the MariaDB service (daemon).
service mysql start
That is not a typo. MariaDB’s service name is mysql.
- Configure MariaDB to start automatically when the system is booted.
chkconfig mysql on
Управление пользователями
В 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.
Добавление нового хоста Nginx
Последний этап настройки VDS для размещения сайта — добавление виртуального хоста Nginx. Нам достаточно отредактировать файл default. В семействе Debian это делается так:
sudo nano /etc/nginx/sites-available/default
В Centos:
sudo vi /etc/nginx/conf.d/default.conf
Открыв файл, удалите из него всю информацию, заменив на код, представленный ниже (вместо sitename.ru подставьте актуальное имя сайта), и сохраните результат:
server {
listen 80; server_name sitename.ru; server_name_in_redirect off; access_log /var/log/nginx/sitename.access_log; error_log /var/log/nginx/sitename.error_log; root /var/www/sitename.ru/public_html; index index.php index.html index.htm default.html default.htm; location / { try_files $uri $uri/ /index.php?$args; } error_page 404 /404.html; error_page 500 502 503 504 /50x.html; location = /50x.html { root /usr/share/nginx/html; } location ~ \.php$ { try_files $uri =404; fastcgi_pass unix:/var/run/php-fpm/php-fpm.sock; fastcgi_index index.php; fastcgi_param SCRIPT_FILENAME $document_root$fastcgi_script_name; include fastcgi_params; }
}
Осталось перезапустить Nginx. Команда для дистрибутивов Debian:
sudo service nginx restart
Для Centos:
sudo systemctl restart nginx
Теперь виртуальный сервер полностью готов к эксплуатации, и можно начинать работу непосредственно с веб-ресурсом. Дальнейшие шаги зависят от выбранной CMS.
3: Тестирование MariaDB
Теперь нужно убедиться, что установка MariaDB прошла успешно.
Попробуйте подключиться к БД с помощью инструмента mysqladmin (это клиент для запуска команд администрирования). Чтобы подключиться к MariaDB как root (-u root), ввести пароль (-p) и запросить версию программы, введите команду:
mysqladmin -u root -p version
Команда выведет:
mysqladmin Ver 9.0 Distrib 5.5.50-MariaDB, for Linux on x86_64
Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.
Server version 5.5.50-MariaDB
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /var/lib/mysql/mysql.sock
Uptime: 4 min 4 sec
Threads: 1 Questions: 42 Slow queries: 0 Opens: 1 Flush tables: 2 Open tables: 27 Queries per second avg: 0.172
Это значит, что установка СУБД MariaDB прошла успешно.
Выводы
В этом руководстве мы показали вам, как установить и защитить MariaDB на сервере CentOS 7.
Теперь, когда ваш сервер MariaDB запущен и работает, и вы знаете, как подключиться к серверу MariaDB из командной строки, вы можете проверить следующие руководства:
- Как управлять учетными записями пользователей и базами данных MySQL
- Как сбросить пароль root MySQL
- Как создать базу данных MySQL
- Как создать учетные записи пользователей MySQL и предоставить привилегии
- Как показать пользователей MySQL
- Как сделать резервную копию и восстановить базы данных MySQL с помощью Mysqldump
Если вы предпочитаете веб-интерфейс командной строке, вы можете установить phpMyAdmin и управлять своими базами данных MariaDB через него.
Этот пост является частью серии Install LEMP Stack on CentOS 7. Другие сообщения из этой серии:
- Как установить Nginx на CentOS 7
- Защитите Nginx с помощью Let’s Encrypt на CentOS 7
- Установите PHP 7 на CentOS 7
- Как настроить серверные блоки Nginx на CentOS 7