Skip to content

MySQL

Jump to bottom
andyceo edited this page Sep 27, 2018 · 20 revisions

Установка mysql

sudo aptitude install mysql-server mysql-client

Установка из Docker-образа

Можно установить из официального образа mysql, и с помощью Ansible-роли andyceo.docker: Ansible Docker config.

Настройка mysql

Ограничение на количество открываемых файлов накладывает операционная система. http://dev.mysql.com/doc/refman/5.1/en/table-cache.html

Системные переменные table_open_cache, max_connections, и max_tmp_tables определяют максимальное количество файлов, которые сервер может держать открытыми. При увеличении одного или нескольких этих значений, можно столкнуться с лимитом накладываемым операционной системой по количеству открытых файловых дескрипторов на один процесс. Большинство операционных систем позволяют увеличить лимит открытых файлов (open-files limit), однако способ существенно различается от системы к системе. Сверьтесь с документацией вашей операционной системы на предмет возможности увеличения лимита и способа это сделать.

Переменная table_open_cache соотносится с max_connections. К примеру, для 200 одновременных соединений вы должны установить размер кеша таблицы (table cache size), по меньшей мере, 200 × N, где N - максимальное количество таблиц к объединению (JOIN) в любом из исполняемых запросов. Также необходимо зарезервировать еще немного для дескрипторов временных таблиц и файлов.

А также http://www.hostcms.ru/documentation/server/mysql/

Файл настроек на конфигурацию с 2-мя Гб памяти. Помимо БД на сервере будут еще веб-сервер, фтп-сервер.

Содержимое файла /etc/mysql/my.cnf см. тут.

MyISAM

Раздел в разработке.

InnoDB

  • innodb_buffer_pool_size - это очень важный параметр для настройки InnoDB. Таблицы этого типа гораздо более чувствительны к размеру буфера, нежели MyISAM. MyISAM может нормально работать даже при дефолтном значении buffer_size, в отличии от InnoDB, производительность которых будет заметно ниже при значении innodb_buffer_pool_size по умолчанию и больших объемах данных. Также пул буферов InnoDB самостоятельно кэширует индексы и данные, так что не нужно оставлять место для кэша ОС. Обычно предполагается выделение 70 - 80% памяти для серверов, на которых ничего не запущено, кроме InnoDB. Некоторые правила key_buffer применимы и в этом параметре: если у вас небольшие объемы данных и они не собираются стремительно увеличиваться, не завышайте значение innodb_buffer_pool_size, вы сможете найти свободной оперативной памяти лучшее применение. Вот формула в виде запроса, предложенная здесь, вычисляющая рекомендованное значение этого буфера:

    SELECT CONCAT(ROUND(KBS/POWER(1024,IF(Power1024<0,0,
IF(Power1024>3,0,Power1024)))+0.49999),SUBSTR(' KMG',IF(Power1024<0,0,
IF(Power1024>3,0,Power1024))+1,1)) recommended_innodb_buffer_pool_size
FROM (SELECT SUM(data_length+index_length) KBS FROM information_schema.tables
WHERE engine='InnoDB') A,(SELECT 2 Power1024) B;

  • innodb_additional_mem_pool_size - этот параметр не имеет сильного влияния на производительность. По крайней мере в операционных системах с грамотным распределением памяти. Но вы можете установить значение этого параметра равным 20MB (иногда больше) и вы можете видеть сколько памяти выделяет InnoDB для различных нужд.

  • innodb_flush_log_at_trx_commit - Вам кажется, что InnoDB в сто раз медленнее MyISAM? Вероятно, вы забыли изменить значение этого параметра. Значение по умолчанию 1 означает, что после каждой завершенной транзакции (или после изменения состояния транзакции) лог должен быть сброшен на диск. Это достаточно дорогая операция, особенно если у вас нет Battery backed up cache. Многие приложения, особенно те, в которых раньше использовался MyISAM будут хорошо работать при значении 2, который означает, что не надо сбрасывать буфер на диск, а следует отправить его в кэш операционной системы. Лог по-прежнему будет сбрасываться на диск каждую секунду и максимум, что вы можете потерять - это 1-2 секунды записей. Значение 0 обеспечивает более высокую скорость, но и более низкую надежность. Есть вероятность потерять транзакции даже при падении mysql-сервера. При значении равном 2 единственная возможность потерять данные - это фатальный сбой операционной системы.

  • innodb_flush_method = O_DSYNC (обычно - другой параметр. посоветовал Панов)

Ссылки:

Дампы

Основные команды

Сделать дамп:

mysqldump -u root -p mydatabase > mydatabase.sql

Сделать консистентный дамп, если у вас в базе используется движок InnoDB и база большая, и одновременно запаковать дамп (больше информации):

mysqldump --single-transaction --quick -u root -p mydatabase | gzip > mydatabase.sql.gz

Сделать дамп только структуры таблиц, без данных:

mysqldump --no-data -u root -p mydatabase > mydatabase.sql

Создание дампа, в котором только первые 100 строк из каждой таблицы (пароль будет запрошен):

mysqldump --opt --where="1 LIMIT 100" -uroot -p DATABASE_NAME > DUMP.sql

Залить дамп:

mysql -u root -p mydatabase  < dump.sql

Импорт дампа базы данных, упакованных в gzip (*.sql.gz):

gunzip < mydatabase.sql.gz | mysql -u root -p mydatabase

Вывести список таблиц, удовлетворяющих какому-либо критерию в базе mydatabase:

echo "show tables where Tables_in_mydatabase not like 'fias%' and Tables_in_mydatabase not like 'cache%';" | mysql -uroot -p hsbo

Сделать дамп всех таблиц в базе данных mydatabase, кроме fias% и cache%:

mysqldump -uroot -p"password" mydatabase `echo "show tables where Tables_in_mydatabase not like 'fias%' and Tables_in_mydatabase not like 'cache%';" | mysql -uroot -p"password" mydatabase | sed '/Tables_in/d'` > mydatabase.sql

Ссылки:

Работа с большими дампами

Чтобы разделить большой дамп по кускам на каждую таблицу при встрече CREATE TABLE, можно использовать такой скрипт:

cat dumpfile.sql | awk 'BEGIN {
output = "comments"; } $data ~ /^CREATE TABLE/ {
 close(output);
 output = substr($3,2,length($3)-2); }
{ print $data >> output }'

Если возникает ошибка вроде

awk: program limit exceeded: maximum number of fields size=32767
  FILENAME="-" FNR=620 NR=620

то надо заменить awk на gawk.

Чтобы разделить большой дамп по кускам на каждую таблицу при встрече DROP TABLE IF EXISTS, можно использовать такой скрипт:

cat dumpfile.sql | gawk 'BEGIN {
output = "comments"; } $data ~ /^DROP TABLE IF EXISTS/ {
close(output);
output = substr($5,2,length($5)-3) ".sql"; }
{ print $data >> output }'

Источник: http://www.mindraven.com/blog/mysql/splitting-a-very-large-mysql-dump-file/

Есть еще несколько способов для разбиения файла на несколько. Допустим, что надо разделить огромный дамп на два. Первый кусок - от начала и до строки, где встречается DROP TABLE IF EXISTS trololo;, а другой кусок - от строки DROP TABLE IF EXISTS trololo; до конца файла. Номер строки DROP TABLE IF EXISTS trololo;` можно узнать так:

grep -n 'DROP TABLE IF EXISTS `trololo`;' dump.sql

где dump.sql - имя файла вашего большого дампа. Допустим, эта команда выдала что-то вроде:

17819:DROP TABLE IF EXISTS `trololo`;

Теперь используем команду split:

split -l 17818 dump.sql

Дамп dump.sql разделится на несколько файлов (не обязательно 2), в которые будут записаны ровно 17818 строк. Этот способ удобен, если искомая строка, на которой надо разбить файл, находится ближе к концу файла (номер строки больше половины количества строк), тогда он гарантированно разобьется на 2 файла и не побьет, например, транзакции в дампе.

Чтобы изменить что-то в большом файле (дампе БД), например, убрать премфиксы таблиц, можно использовать команду sed:

sed -i 's/hello/bye, bye!/g' dump.sql

Еще один способ, это доработать следующий скрипт:

#!/usr/bin/env python3

f = 'forum.2011_10_20.sql'
of = open('dump.sql', 'w')
flag = False

for ln, l in enumerate(open(f, 'rt')):
    if flag or (l and l[:38] == 'DROP TABLE IF EXISTS `trololo`;'):
        flag = True
        of.write(l)

print('All done!')
of.close()

Однако, лично у меня этот скрипт вызвал ошибку:

andyceo@mycomp:~$ ./split.py
Traceback (most recent call last):
  File "./split.py", line 9, in <module>
    for ln, l in enumerate(open(f, 'rt')):
  File "/usr/lib/python3.2/codecs.py", line 300, in decode
    (result, consumed) = self._buffer_decode(data, self.errors, final)
UnicodeDecodeError: 'utf8' codec can't decode byte 0x86 in position 1329: invalid start byte

В чем ее причина, я не знаю. Мой дамп был на 15 Гб.

PS: Также можно использовать split (http://philipp.cuntz.org/2011/01/linux-split-large-text-files.html, http://ru.wikipedia.org/wiki/Split)

Ссылки:

Работа с пользователями и привилегиями

Создать нового MySQL-пользователя

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

GRANT ALL PRIVILEGES ON database.* TO 'user'@'localhost' IDENTIFIED BY 'password';

Если вы хотите предоставить пользователю возможность соединения не только с локального хоста, а вообще без всяких ограничений с любого IP-адреса, не забудьте в файле /etc/my.cnf закомментировать команду bind-address:

#bind-address = 127.0.0.1

и заменить в предыдущей команде 'localhost' на '%':

GRANT ALL PRIVILEGES ON database.* TO 'user'@'%' IDENTIFIED BY 'password';

Смена пароля для root (и других пользователей)

Свой пароль можно поменять через:

SET PASSWORD = PASSWORD('пароль')

Пароль определенного пользователя можно поменять через:

SET PASSWORD FOR логин@localhost = PASSWORD('пароль');
SET PASSWORD FOR логин@"%" = PASSWORD('пароль');

тоже самое делают:

UPDATE mysql.user SET Password=PASSWORD('пароль') WHERE User='логин' AND Host='localhost';
FLUSH PRIVILEGES;

или

GRANT USAGE ON БД.* TO логин@localhost IDENTIFIED BY 'пароль';

или

mysqladmin -u логин password пароль

Смена забытого пароля для root когда MySQL запущен в docker-контейнере

В случае, если забыт старый root-пароль или его сбросили на неизвестный, и при этом MySQL работает в docker-контейнере, нужно запустить контейнер со следующей командой:

command: --skip-grant-tables --skip-networking

затем зайти внутрь контейнера с помощью exec, вызвать клиент командной строки mysql и выполнить там:

FLUSH PRIVILEGES;
ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass';
FLUSH PRIVILEGES;

(в случае необходимости надо заменить localhost на % или тот хост который нужен, а MyNewPass - на новый пароль) и перезапустить контейнер в нормальном режиме, без опций --skip-grant-tables --skip-networking. После этого можно будет зайти в MySQL под пользователем root с новым паролем.

Ссылки:

Работа с процессами

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

SHOW PROCESSLIST;

Убить соединение (и запрос, выполняемый в нем):

KILL **Id**

где Id - это id соединения из SHOW PROCESSLIST

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

KILL QUERY **Id**

где Id - это id соединения из SHOW PROCESSLIST

Перенос MySQL с одного сервера на другой

  • Сначала надо выключить mysql на старом сервере:

      sudo /etc/init.d/mysql stop

  • Затем всю папку /var/lib/mysql положить в один ахив tar:

      cd /var/lib
  sudo tar -cf mysql.tar mysql

  • Залить на новый сервер этот архив

  • Распаковать:

      sudo tar -xf mysql.tar

  • Переместить получившуюся директорию mysql в /var/lib

  • Поставить на нее следующие права и владельца:

  • TODO:Поправить права и владельцев директорий и папок:

      sudo find /var/www/ -type f -exec chmod 644 {} \;
  sudo find /var/www/ -type d -exec chmod 755 {} \;
  sudo chown  www-data:www-data -R /var/www/

  • поправить пароль для пользователя debian-sys в файле /etc/mysql/debian.cnf на тот, что был на старом сервере. Или использовать такой метод:

      # killall mysqld
  # mysqld_safe --skip-grant-table
  ^Z
  # bg
  # mysql -u root
  > UPDATE mysql.user SET Password = PASSWORD( 'новый пароль' )
  WHERE user.Host = 'localhost'
  AND user.User = 'debian-sys-maint';
  > exit
  # fg
  ^C
  # /etc/init.d/mysql start

    Источник: http://www.opennet.ru/tips/info/1549.shtml

Обновление MySQL

Если нужно обновить MySQL, то есть два способа: через mysql_upgrade и путем создания дампов со старой базы и импорта их на новую. Версия 5.7.17 умеет апгрейдить "на лету". В случае, если вы запустили докер-контейнер над папкой с данными от MySQL старой версии, можно сделать следующее:

    sudo docker exec -ti mysql-container-name mysql_upgrade -u root -p"ROOT_PASSWORD"

Затем нужно обязательно перезапустить контейнер с MySQL, иначе при попытке дампа может быть такая ошиюка:

    mysqldump: Couldn't execute 'SHOW VARIABLES LIKE 'gtid\_mode'': Native table 'performance_schema'.'session_variables' has the wrong structure (1682)

Перезапуск корректно делать такой командой:

    sudo docker exec -ti mysql-container-name mysqladmin -u root -p"ROOT_PASSWORD" shutdown

ROOT_PASSWORD можно не указывать, тогда MySQL интерактивно спросит его.

Обновление с помошью dotdeb.org

Что получилось? Запуск

sudo aptitude dist-upgrade

попытался обновить мне mysql-server, но:

invoke-rc.d: initscript mysql, action "start" failed.
dpkg: error processing mysql-server-5.1 (--configure):
 subprocess post-installation script returned error exit status 1
dpkg: dependency problems prevent configuration of mysql-server:
 mysql-server depends on mysql-server-5.1; however:
  Package mysql-server-5.1 is not configured yet.
dpkg: error processing mysql-server (--configure):
 dependency problems - leaving unconfigured
Errors were encountered while processing:
 mysql-server-5.1
 mysql-server

Это произошло потому, что нужно было избавиться от пакета mysql-common через purge (и все зависимые от него пакеты), а затем с чистого листа установить mysql-server:

sudo aptitude purge mysql-common

Если спросит про другие пакеты, которые тоже надо удалить, соглашайтесь!!!

ВНИМАНИЕ! Это все мне также удалило proftpd, и proftpd-mysql (надо ставить заново).

Также перестал работать cacti и версия mysql-server отличается от версии mysql-client.

ВНИМАНИЕ! Файл настроек /etc/my.cnf обнулился (ведь мы сделали purge, поэтому нужен бэкап настроек). Однако не забыть убрать упоминание о BerkeleyDB!! а то будет ошибка.

Источники:

Полезные статьи

Хранение иерархичных и графовых структур в MySQL:

Полезный флаг в консоли MySQL, который переворачивает строки со столбцами (удобно смотреть, если строка длинная):

mysql> select * from users_field_data;
+-----+----------+--------------------+--------------------------+------+---------------------------------------------------------+----------------------------+---------------+--------+------------+------------+------------+------------+----------------------------+------------------+
| uid | langcode | preferred_langcode | preferred_admin_langcode | name | pass                                                    | mail                       | timezone      | status | created    | changed    | access     | login      | init                       | default_langcode |
+-----+----------+--------------------+--------------------------+------+---------------------------------------------------------+----------------------------+---------------+--------+------------+------------+------------+------------+----------------------------+------------------+
|   0 | ru       | ru                 | NULL                     |      | NULL                                                    | NULL                       |               |      0 | 1451669332 | 1451669332 |          0 |          0 | NULL                       |                1 |
|   1 | ru       | ru                 | NULL                     | root | $S$EhBCsTciNdQFHxLPgwfFIXY.7GLGjyzjiiX.JXJ6DM1ZO.rEkFzm | noreply@drupal8-core.local | Europe/Moscow |      1 | 1451669332 | 1451818176 | 1452261434 | 1452251276 | noreply@drupal8-core.local |                1 |
+-----+----------+--------------------+--------------------------+------+---------------------------------------------------------+----------------------------+---------------+--------+------------+------------+------------+------------+----------------------------+------------------+
2 rows in set (0.00 sec)

Попробуем:

mysql> select * from users_field_data\G
*************************** 1. row ***************************
                     uid: 0
                langcode: ru
      preferred_langcode: ru
preferred_admin_langcode: NULL
                    name: 
                    pass: NULL
                    mail: NULL
                timezone: 
                  status: 0
                 created: 1451669332
                 changed: 1451669332
                  access: 0
                   login: 0
                    init: NULL
        default_langcode: 1
*************************** 2. row ***************************
                     uid: 1
                langcode: ru
      preferred_langcode: ru
preferred_admin_langcode: NULL
                    name: root
                    pass: $S$EhBCsTciNdQFHxLPgwfFIXY.7GLGjyzjiiX.JXJ6DM1ZO.rEkFzm
                    mail: noreply@drupal8-core.local
                timezone: Europe/Moscow
                  status: 1
                 created: 1451669332
                 changed: 1451818176
                  access: 1452261434
                   login: 1452251276
                    init: noreply@drupal8-core.local
        default_langcode: 1
2 rows in set (0.00 sec)

т.е. \G вместо ;.

Footer is under construction

Sidebar is under construction

Clone this wiki locally