MySQL

Аватар пользователя Raven2000

В данной статье рассмотрена установка MySQL на OpenBSD с установкой лимитов, конфигурацией класса mysql, небольшим тюнингом, сохранением\восстановлением базы и т.п.

Установка

Обновим порты под свой релиз OpenBSD и проверим текущую версию MySQL.

# cd /usr/ports/
# cvs -d anoncvs@ftp5.eu.openbsd.org:/cvs update -PdrOPENBSD_4_7
~~~~
# make search name=mysql-server
Port:   mysql-server-5.1.42
Path:   databases/mysql,-server
Info:   multithreaded SQL database (server)
Maint:  Brad Smith <brad@comstyle.com>
Index:  databases
L-deps: mysqlclient:mysql-client->=5.1,<5.2:databases/mysql
B-deps: :devel/gmake :devel/libtool :devel/metaauto autoconf-2.61:devel/autoconf/2.61
R-deps: mysql-client->=5.1,<5.2:databases/mysql p5-DBD-mysql-*:databases/p5-DBD-mysql
Archs:  any

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

# export PKG_PATH=ftp://ftp.openbsd.org/pub/OpenBSD/4.7/packages/`machine -a`/
# pkg_add mysql-server
mysql-server-5.1.42:mysql-client-5.1.42: ok (2 to go)
mysql-server-5.1.42:p5-Net-Daemon-0.43: ok (4 to go)
mysql-server-5.1.42:p5-PlRPC-0.2018p0: ok (3 to go)
mysql-server-5.1.42:p5-DBI-1.609: ok (2 to go)
mysql-server-5.1.42:p5-DBD-mysql-4.013: ok (1 to go)
mysql-server-5.1.42: ok
--- +mysql-server-5.1.42 -------------------
You can find detailed instructions on how to install a database
in /usr/local/share/doc/mysql/README.OpenBSD.
# pkg_info
mysql-client-5.1.42 multithreaded SQL database (client)
mysql-server-5.1.42 multithreaded SQL database (server)
p5-DBD-mysql-4.013  MySQL drivers for the Perl DBI
p5-DBI-1.609        unified perl interface for database access
p5-Net-Daemon-0.43  extension for portable daemons
p5-PlRPC-0.2018p0   module for writing rpc servers and clients

Лимиты

Вы можете изменить некоторые характеристики демона для повышения устойчивости в работе и\или для ограничению прожорливости MySQL. Обычно изменяют такие переменные (есть и другие):

  • количество соединений
  • количество открытых таблиц
  • ограничения ядра для защиты вашей системы

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

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

mysqlcheck -m -A -uYourUsers -pYourPassword

Cfg /etc/sysctl.conf

Сначала вы можете увидеть ограничения накладываемые на открытые таблицы. Таким образом, вам необходимо увеличить количество открытых таблиц не только в конфигурации MySQL, но и в системе. Таким образом, необходимо простое изменение в sysctl.conf.
Значение по умолчанию хороши для большинства, но у вас может быть очень много открытых таблиц и вам этого может не хватать. Так что следите за системой. И при необходимости скорректируйте MySQL для своих значений.

Изменим ограничения на открытые таблицы вам нужно увеличить не только в конфигурации MYSQL, но и в системе. Для начала я сделаю простое изменение в системе.
Добавим в /etc/sysctl.conf.

# Increase for MySQL
kern.maxfiles=14060

Посмотреть текущее значение.

# sysctl kern.maxfiles
kern.maxfiles=7030

Установить необходимое значение.

# sysctl kern.maxfiles kern.maxfiles=14060
# sysctl kern.maxfiles
kern.maxfiles= 7030 -> 14060

Cfg /etc/login.conf

По умолчанию ваш сервер будет работать под пользователем и классом _mysql.
Они были созданы на время установки таким образом, вы должны использовать этот класс. Однако, если вы просто добавите скрипт запуска в rc.local то вы будете использовать дефолтный класс вашего login.conf, а не класс _mysql. Помните, что MySQL это демон и в этом качестве вы должны указать ему использовать класс _mysql при запуске или перезагрузке демона, или он будет использовать класс по умолчанию. Многие пользователи либо забываю этот факт или не знают об этом с самого начала.

В /etc/login.conf добавим:

# Setting used by MySQL daemon
#_mysql:\
#            :datasize=infinity:\
#            :maxproc=infinity:\
#            :openfiles-cur=2048:\
#            :openfiles-max=4096:\
#            :stacksize-cur=8M:\
#            :localcipher=blowfish,8:\
#            :tc=default:
 
_mysql:\
        :openfiles-cur=1024:\
        :openfiles-max=2048:\
        :tc=daemon:

При необходимости перестроим файл login.conf.db:

# cap_mkdb /etc/login.conf

Cfg /etc/rc.local

Теперь мы должны все это использовать. Если вы просто запустите MySQL с вашего rc.local скрипта, он будет использовать класс по умолчанию, а не тот, который вы добавили. Таким образом, сделаем созданный класс по умолчанию.

Добавим в /etc/rc.local:

# Start MySQL server
 if [ -x /usr/local/bin/mysqld_safe ] ; then
   su -c _mysql root -c '/usr/local/bin/mysqld_safe >/dev/null 2>&1 &'
   echo -n ' mysql'
 fi

Cfg /etc/my.cnf

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

# Пример MySQL файла конфигурации для средних систем.
#
# Это для системы с небольшим объемом памяти (32M - 64M) или для 
# системы до 128M, где MySQL используется совместно с другими 
# программами (такими как веб-сервер)
#
# Вы можете скопировать этот файл в
# /etc/my.cnf и установить глобальные параметры,
# mysql-data-dir/my.cnf установить сервер-специфические опции
# (в данном каталоге установки /var/mysql) или
# ~/.my.cnf установить пользовательские опции.
#
# Для помощи выберете "--help" опцию.
 
# Следующие параметры будут переданы MySQL клиентам
[client]
#password       = your_password
port            = 3306
socket          = /var/run/mysql/mysql.sock
 
# Здесь идут записи для некоторых специфичных программ
 
# Для MySQL сервера
[mysqld]
port            = 3306
socket          = /var/run/mysql/mysql.sock
#skip-innodb
#skip-bdb
#skip-locking
key_buffer_size = 256M   # по умолчанию 16M
max_allowed_packet = 1M
table_open_cache = 256 # по умолчанию 64
sort_buffer_size = 1M  # по умолчанию 512K
net_buffer_length = 8K
read_buffer_size = 1M    # по умолчанию 256K
read_rnd_buffer_size = 4M # по умолчанию 512K
myisam_sort_buffer_size = 64M # по умолчанию 8M
 
# Не слушайте все TCP / IP-порты. Вы должны повысить безопасность,
# если все процессы, которые необходимо подключиться к mysqld работать на одном хосте.
# Все взаимодействие с mysqld должны идти через Unix сокеты или именованные каналы.
# Обратите внимание, что использование этой опции не включая именованные каналы на Windows
# (через опцию "enable-named-pipe" ) будет для mysqld бесполезно!
#
#skip-networking
 
# Репликации Master Server (по умолчанию)
# Бинарный лог, необходимые для репликации
log-bin=mysql-bin
 
# Двоичный формат журнала - рекомендован смешанный режим
binlog_format=mixed
 
# требуется уникальный идентификатор между 1 и 2^32 - 1
# по умолчанию 1, если мастер-хозяин не установлен
# но не будет работать, как мастер, если пропущен
server-id       = 1
 
# Репликации Slave (комментарий использования из мастер-секции)
#
# Чтобы настроить этот хост, как репликация slave, вы можете выбирать между
# двумя способами:
#
# 1) Используйте команду CHANGE MASTER TO  (подробно описаны в нашем руководстве)
# Синтаксис:
#
# CHANGE MASTER TO MASTER_HOST = <host>, MASTER_PORT = <port>,
# MASTER_USER = <user>, MASTER_PASSWORD = <password>;
#
#, Где вы замените <host>, <user>, <password> и
# <port> номер порта master (по умолчанию 3306).
#
# Пример:
#
# CHANGE MASTER TO MASTER_HOST = '125 .564.12.1, MASTER_PORT = 3306,
# MASTER_USER = 'Joe', MASTER_PASSWORD "секрет" =;
#
# Или
#
# 2) Установить переменные ниже. Однако, в случае, если вы выбираете этот метод,
#   то в начале репликации в первый раз (например если вы 
#   опечатаетесь в пароле мастер-пароля, slave не cможет
#   подключится), slave создаст master.info файл, и любые более позднии
#   изменения значений в этом файле переменных ниже будут игнорироваться, и
#   переопределения содержания master.info файла идет при выключении
#   slave серверов и дальше удалите master.info с перезагрузкой серверов slave.
#   По этой причине, вы можете оставить нетронутыми строки ниже
#   (закоментированными) и вместо этого использовать CHANGE MASTER TO (см. выше)
#
# Требуется уникальный идентификатор между 2 и 2^32 - 1
# (отличный от мастера)
# по умолчанию 2, если мастер-хост установлен
# но не будет работать, как slave, если пропущено
#server-id       = 2
#
# Репликации master для этого slave - требуется
#master-host     =   <hostname>
#
# Имя пользователя slave будет использовать для аутентификации при 
# подключении к master серверу - требуется
#master-user     =   <username>
#
# Пароль slave будет использовать для аутентификации при 
# подключении к master серверу - требуется
#master-password =   <password>
#
# Порт прослушивания master сервера.
# опция - по умолчанию 3306
#master-port     =  <port>
#
# бинарный лог - не требуется для slave, но рекомендуется
#log-bin=mysql-bin
 
# Точка следующих путей к различным разделенным дискам
#tmpdir         = /tmp/
#log-update     = /path-to-dedicated-directory/hostname
# Раскомментируйте следующее, если вы используете InnoDB таблицы
#innodb_data_home_dir = /var/mysql/
#innodb_data_file_path = ibdata1:10M:autoextend
#innodb_log_group_home_dir = /var/mysql/
# Вы можете установить .. _buffer_pool_size до 50 - 80%
# оперативной памяти, но будьте осторожны настройки памяти слишком высоки
#innodb_buffer_pool_size = 16M
#innodb_additional_mem_pool_size = 2M
# Установите .. _log_file_size до 25% от размера пула буфера
#innodb_log_file_size = 5M
#innodb_log_buffer_size = 8M
#innodb_flush_log_at_trx_commit = 1
#innodb_lock_wait_timeout = 50
 
[mysqldump]
quick
max_allowed_packet = 16M
 
[isamchk]
key_buffer = 128M
sort_buffer_size = 128M
read_buffer = 2M
write_buffer = 2M
 
[my sql]
no-auto-rehash
# Удалить следующий символ комментария, если вы не знакомы с SQL
#safe-updates
 
[myisamchk]
key_buffer_size = 128M  # по умолчанию 20M
sort_buffer_size = 128M # по умолчанию 20M
read_buffer = 2M
write_buffer = 2M
 
[mysqlhotcopy]
interactive-timeout

Запуск

Запустим демон

# su -c _mysql root -c '/usr/local/bin/mysqld_safe &' > /dev/null &
[1] 22118

Проверим работу.
# ps aux |grep _mysql
_mysql   25593  0.2  1.8 42892 19032 p1  S      3:07PM    0:00.34 
/usr/local/libexec/mysqld --basedir=/usr/local --datadi

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

# /usr/local/bin/mysql_install_db
Installing MySQL system tables...
OK
Filling help tables...
OK

Пароль

Не забудьте изменить пароль рута создаем после запуска MySQL.

# /usr/local/bin/mysqladmin -u root password 'ПАРОЛЬ'

Обновление

Если вам нужно обновить MySQL при обновлении OpenBSD на новую версию, можете использовать pkg_add -u, и вы свободны. =)

man 1 pkg_add

Backup\restore

Я покажу как сохранить и восстановить базу данных.
Сохраняем.

# mysqldump -uUSER_DB -h127.0.0.1 -pUSER_DB_PASS DB > DB.sql

Восстанавливаем

# mysql --host=127.0.0.1 -u USER_DB -p DB < DB.sql

Оптимизация

В MYSQL есть возможность кэширования часто встречающихся запросов в оперативной памяти, и при поступлении конкретного запроса в MYSQL она возвратит его непосредственно из кеша. Однако в MYSQL по умолчанию эта функция отключена. И нам необходимо править конфигурационный файл MYSQL my.cnf Мы для примера добавим кэш размером в 64МБ. И на будущее с этим кэшом необходимо экспериментировать, чтобы добиться максимальной эффективности. Слишком маленький кэш приведет к тому, что кэшируемые запросы очень часто будут недействительными, а чересчур большой повлечет за собой относительно длительный поиск в кэше. Память будет неэффективно использоваться которая, могла бы пригодится другим службам.

[mysqld]
# Включам кэш запросов равным 64МБ
query_cache_size=64M
# Для индификации ресурсоемких запросов включаем следующее.
# Это приведет к регистрации
# всех запросов, для которых требуется больше 5 секунд (порог можете менять)
long_query_time = 5
log-slow-queries = /var/log/mysql/slow.log

Создаем лог меделенных запросов.

# touch /var/log/mysql/slow.log
# chown mysql:mysql /var/log/mysql/slow.log

В последствии присматриваем этот лог и смотрим, кто попался и делаем выводы.

Перезапустим mysql-server

# /usr/local/etc/rc.d/mysql-server restart

Проверим настройку

# mysql -u otor --password=*****
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 692516
Server version: 5.0.77 FreeBSD port: mysql-server-5.0.77_1
 
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
 
mysql> show variables like 'query_cache%';
+------------------------------+----------+
| Variable_name                | Value    |
+------------------------------+----------+
| query_cache_limit            | 1048576  |
| query_cache_min_res_unit     | 4096     |
| query_cache_size             | 67108864 |
| query_cache_type             | ON       |
| query_cache_wlock_invalidate | OFF      |
+------------------------------+----------+
5 rows in set (0.00 sec)

Как видим мы включили кэш (query_cache_type ON) и установили размер его равным 64МБ (query_cache_size 67108864)

Изменение типа таблиц с MyISAM на InnoDB

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

mysql> show status like 'Table%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Table_locks_immediate | 29958 |
| Table_locks_waited    | 0     |
+-----------------------+-------+
2 rows in set (0.00 sec)

Table_locks_immediate - количество случаев, когда блокировка таблицы была получена немедленно.
Table_locks_waited - когда получение блокировка таблиц сопровождалось ожиданием. Если это значение велико, то у вас проблемы =)

Если у вас CMS, например Drupal то можно некоторые таблицы преобоазовать в InnoDB, например cache, watchdog, session, acceslog делается это так.

mysql> alter table cache type='InnoDB';

(Для настройки производительности MYSQL можно загрузить сценарий MySQL Performance Tuning Primer Script) который позволяет корректировать переменные сервера MySQL.

Дополнительные материалы
Ваша оценка: Нет Средняя: 5 (4 голоса)

Отправить комментарий

Содержание этого поля является приватным и не предназначено к показу.
  • Адреса страниц и электронной почты автоматически преобразуются в ссылки.
  • Доступны HTML теги: <a> <p> <span> <div> <h1> <h2> <h3> <h4> <h5> <h6> <img> <map> <area> <hr> <br> <br /> <ul> <ol> <li> <dl> <dt> <dd> <table> <tr> <td> <em> <b> <u> <i> <strong> <font> <del> <ins> <sub> <sup> <quote> <blockquote> <pre> <address> <code> <cite> <embed> <object> <param> <strike> <caption>
  • Строки и параграфы переносятся автоматически.
  • Вы можете использовать подсветку исходного кода следующими тегами: <code>, <blockcode>, <apache>, <bash>, <c>, <cpp>, <mysql>, <perl>, <php>, <python>, <text>. The supported tag styles are: <foo>, [foo].
  • Glossary terms will be automatically marked with links to their descriptions. If there are certain phrases or sections of text that should be excluded from glossary marking and linking, use the special markup, [no-glossary] ... [/no-glossary]. Additionally, these HTML elements will not be scanned: a, abbr, acronym, code, pre.

Подробнее о форматировании

CAPTCHA
Этот вопрос задается для того, чтобы выяснить, являетесь ли Вы человеком или представляете из себя автоматическую спам-рассылку.
11 + 3 =
Решите эту простую математическую задачу и введите результат. Например, для 1+3, введите 4.

Последние статьи

Theme by Danetsoft and Danang Probo Sayekti inspired by Maksimer