mysql медленная репликация

 Бывает что слейв начинает отставать от мастера, поскольку на слейве репликация прокатывается в один поток. И тут помогает (для mariadb):

https://mariadb.com/kb/en/parallel-replication/

Впрочем, был подсказан ещё вариант, менее надёжный, более быстрый (на слейве):

SET GLOBAL sync_binlog = 0; SET GLOBAL  innodb_flush_log_at_trx_commit = 0;

частые проблемы при смене версии mysql

 Первым делом проверять:

sql_mode (тут вообще разброд и шатание, самое простое но не очень правильное делать sql_mode = "")

innodb_strict_mode - та же мария 10.1 - офф, 10.5 - он. Выставляется

[mysqld]

innodb_strict_mode = OFF

https://mariadb.com/kb/en/troubleshooting-row-size-too-large-errors-with-innodb/

Почему лучше заранее выключать query cache в mysql

https://mysqlquicksand.wordpress.com/2020/05/08/10-reasons-why-mysql-query-cache-should-be-switched-off/

Если вкратце - есть проблемы с локами (mutex lock), которые однопоточные... и уже выпилено в 8 версии.

mysql proxy?

 Когда-то может понадобиться mysql proxy. Была версия "официальная", которая так и называлась, но в репе последнее обновление 8 лет назад - по факту оно мертво.

Была найдена живая замена: https://github.com/sysown/proxysql/

После установки по README подключаться так

$ mysql -u admin -padmin -h 127.0.0.1 -P6032 --prompt='Admin> '

Есть в докере, например proxysql/proxysql, percona/proxysql

Мониторинг:

Интегрировать будем с графаной, и например, можно взять proxysql-exporter (или proxysql-grafana-prometheus - нужно ставить и проверять), но в целом есть такое

https://proxysql.com/documentation/backend-monitoring/

Что почитать:

ProxySQL — еще один mysql-proxy

Оптимизация запросов MySQL с помощью кеширования ProxySQL в Ubuntu 16.04 (по названию конечно старое, но дока за 2020, плюс там важны "азы")

https://mydbops.wordpress.com/2018/08/20/proxysql-series-percona-cluster-mariadb-cluster-galera-read-write-split/

https://mydbops.wordpress.com/2020/01/30/monitoring-mysql-using-proxysql/

(вообще на этом сайте много полезных статей про proxysql)

Есть и какая-то интеграция с реликом, другой вопрос кому он нужен при таких ценах..

https://newrelic.com/blog/how-to-relic/how-to-monitor-mysql

PS

Впрочем, можно и свой прокси написать, например как тут

https://xakep.ru/2017/02/22/mysql-proxy/

mysql - некоторые хитрости

 Задача - скопировать таблицы из одной базы в другую, поменяв имя таблиц (добавить префикс). Можно сделать через mysqldump | sed | mysql, а можно так:

Первое заполнение можно сделать примерно так

SELECT concat('CREATE TABLE maindb.', TABLE_NAME, ' LIKE tmpdb.', TABLE_NAME, ';') FROM information_schema.TABLES  WHERE TABLE_SCHEMA = 'tmpdb';

SELECT concat('INSERT INTO maindb.', TABLE_NAME, ' SELECT * FROM tmpdb.', TABLE_NAME, ';') FROM information_schema.TABLES  WHERE TABLE_SCHEMA = 'tmpdb';

Но.. надо сохранить вывод в файл (например через mysql -b -e '...' > tmp.sql) и выполнить, а можно прямо в консоли mysql, будет дальше.

Без временного файла это будет выглядеть так

T1=$(mysql -b -s -r -e "SELECT concat('CREATE TABLE maindb.prefix_', TABLE_NAME, ' LIKE tmpdb.', TABLE_NAME, ';') FROM information_schema.TABLES  WHERE TABLE_SCHEMA = 'tmpdb';")

mysql -e "$T1"

T2=$(mysql -b -s -r -e "SELECT concat('INSERT INTO maindb.prefix_', TABLE_NAME, ' SELECT * FROM tmpdb.', TABLE_NAME, ';') FROM information_schema.TABLES  WHERE TABLE_SCHEMA = 'tmpdb';")

mysql -e "$T2"

Но, заполнили первый раз базу, обновляем - а теперь нужно сначала удалить все старые таблицы.

https://phoenixnap.com/kb/mysql-drop-table

(нюанс: мария 10.5 - не работает select * FROM information_schema.tables WHERE @schema = database();  - нужно убрать @schema = database(); )

Prometheus mysqld_exporter

https://grafana.com/oss/prometheus/exporters/mysql-exporter/?tab=installation

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

wget https://github.com/prometheus/mysqld_exporter/releases/download/v0.12.1/mysqld_exporter-0.12.1.linux-amd64.tar.gz
tar -zxvf mysqld_exporter*

Бэкапы mysql в 2021

 Кроме штатно mysqldump, который лочит таблицы, что вызывает в работе прода, есть весьма известный percona xtrabackup и сильно менее известный mariabackup

Отличия 2 утилит в том, что mariabackup это форк от xtrabackup, но там нужна целая матрица совместимостей -- тут можно только одним, там только другим, и между собой они совместимы не полностью. НО. Даже xtrabackup есть 2.4 и 8.0, и они тоже применимы в своих случаях! То есть 8.0 это только для mysql 8.0, для всех других версий ставить нужно строго 2.4.

Но и это не всё. mariabackup - судя по их сайту минимальная версия 10.2

The following MariaDB versions are currently supported:

mariadb-10.2

mariadb-10.3

mariadb-10.4

mariadb-10.5

Про xtrabackup можно почитать например это

https://habr.com/ru/post/520458/

Но сам бэкап ещё только пол дела, часто нужна информация о позиции в бинлоге (для поднятия слейва). Но и тут есть помощь, см

https://www.percona.com/doc/percona-xtrabackup/2.3/howtos/setting_up_replication.html

Ну и ещё чуть интересных ссылок

https://serveradmin.ru/polnyj-i-inkrementnyj-backup-mysql/

https://itc-life.ru/nastrojka-replikacii-mysql-s-pomoshhyu-xtrabackup-utility-percona/

https://minervadb.com/index.php/setup-mysql-slave-replication-with-percona-xtrabackup/

Некоторые нюансы с mysql

20 советов по оптимальному использованию MySQL (в том числе про ORDER BY RAND() - а как это лучше заменить, есть несколько решений)

 почему * это плохо

How to Fix error for MySQL 5.7 with Ubuntu 18.04 {ERROR 1698 (28000): Access denied for user ‘root’@’localhost’}

https://www.expresstechsoftwares.com/mysql-access-denied-user-root-localhost/

Суть в том что в 5.7.25 ВДРУГ перестал работать логин рутом по сети и даже от другого юзера (не рута сервера), в частности ломается коннект через phpmyadmin после минорного обновления mysql.
Коннектимся от рута, без параметров
# mysql
mysql> UPDATE user SET plugin='mysql_native_password' WHERE User='root';
mysql> FLUSH PRIVILEGES;

Или можно так
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'very_strong_password';FLUSH PRIVILEGES;

Непонятно чей это косяк, но нервов он может попортить.

ERROR 1005 (HY000): Can't create table `db`.`cdr2` (errno: -1 "Internal error < 0 (Not system error)")

Поймали недавно такую ошибку
ERROR 1005 (HY000): Can't create table `db`.`cdr2` (errno: -1 "Internal error < 0 (Not system error)")

Оказалось, это прилетело что-то не нужное, с таким описанием

ENGINE=InnoDB DEFAULT CHARSET=ascii DATA DIRECTORY='/var/lib/mysql_parition_moving/';

этого DIRECTORY не существует.

Server version: 10.1.26-MariaDB MariaDB Server

CentOS 7: переключаемся на оригинальный mysql

https://www.digitalocean.com/community/tutorials/how-to-install-mysql-on-centos-7

Вкратце:
wget https://dev.mysql.com/get/mysql57-community-release-el7-9.noarch.rpm
rpm -Uvh mysql57*

Если будет ошибка про MariaDB* - удаляем эти пакеты руками. Чтобы без завистимостей - делать это так
rpm -qa|grep <имя проблемного пакета>
rpm -e --nodeps <полное имя пакета из шага выше>

Если что, идти сюда:
https://dev.mysql.com/downloads/repo/yum/

Обращаю внимание, что если уже стоял MariaDB-server, его нужно сначала остановить, потом удалить. Если данные нужны - забэкапить. Потом
# service mysqld start

!!! Если сразу набрать mysql, будет ошибка что нужен пароль. Получить его можно так:
sudo grep 'temporary password' /var/log/mysqld.log

Делаем mysql_secure_installation
(где-то было, что с временным паролем можно зайти только 1 раз)

mysqldump: убрать DEFINER из бэкапа

В некоторых версиях появилась бесполезная хрень называемая DEFINER, паразитирующая на CREATE VIEW, CREATE PROCEDURE.
Почему хрень? Потому что такой функционал востребован максимум в 5% бэкапов и должен включаться опционально, и штатно НЕ НУЖЕН. Так что 2 пути - выпиливать из кода или обработать дамп.
Первый шаг пропустим, второй - коды разные, синтаксис тоже. Коды 50017, 50013
пример на sed:
sed -i'' 's/DEFINER=[^*]*\*/\*/g' mydump.sql


http://stackoverflow.com/questions/9446783/remove-definer-clause-from-mysql-dumps

database и schema в postgres и mysql (с хабра)

http://habrahabr.ru/post/246339/#comment_8190995
Хинт: в mysql - database == schema, в postgres schema==namespace ~ mysql database, тогда как postgres database - некий глобальный контейнер для схем.
Примерно так же у oracle и без канистры водки порой сложно разобраться, что где и как.

CentOS 6 + mysqltuner: Use of uninitialized value in addition (+) at /usr/bin/mysqltuner line 547 and Use of uninitialized value $myvar{"have_innodb"} in string eq at

При попытке использовать mysqltuner может быть ошибка

Особенности обновления таймзон

Предисловие:
Рецепты для centos, debian, freebsd, небольшой такой сборник.
Не забываем обновить сначала порты/пакеты, у дебиана apt-get update, у freebsd через svnup или portsnap fetch update. Centos сам обновит.

What's the difference between utf8_general_ci and utf8_unicode_ci

http://stackoverflow.com/questions/766809/whats-the-difference-between-utf8-general-ci-and-utf8-unicode-ci/766996#766996

There are at least two important differences:

• Accuracy of sorting
  utf8_unicode_ci is based on the Unicode standard for sorting, and sorts accurately in a very wide range of languages.
  utf8_general_ci comes very close to correct Unicode sorting in many common languages, but has a number of inaccuracies in some languages making in unsuitable for correct sorting in those languages.
• Performance
  utf8_general_ci is faster at comparisons and sorting, because it takes a bunch of performance-related shortcuts.
  utf8_unicode_ci uses a much more complex comparison algorithm which aims for correct sorting according in a very wide range of languages. This makes it slower to sort and compare large numbers of fields.

Unicode defines complex sets of rules for how characters should be sorted. These rules need to take into account language-specific conventions; not everybody sorts their characters in what we would call 'alphabetical order'.

• As far as Latin (ie "European") languages go, there is not much difference between the Unicode sorting and the simplified utf8_general_ci sorting in MySQL, but there are still a few differences:
  For examples, the Unicode collation sorts "ß" like "ss", and "Œ" like "OE" as people using those characters would normally want, whereas utf8_general_ci sorts them as single characters (presumably like "s" and "e" respectively).
• In non-latin languages, such as Asian languages or languages with different alphabets, there may be a lot more differences between Unicode sorting and the simplified utf8_general_ci sorting. The suitability of utf8_general_ci will depend heavily on the language used. For some languages, it'll be quite inadequate.

Some Unicode characters are defined as ignorable, which means they shouldn't count toward the sort order and the comparison should move on to the next character instead. utf8_unicode_ci handles these properly.
What should you use?
There is almost never any reason to use utf_general_ci anymore, as we have left behind the point where CPU speed is low enough that the performance difference would be important. Your database will almost certainly be limited by quite other bottlenecks than this nowadays. The difference in performance is only going to be measurable in extremely specialised situations, and if that's you, you'd already know about it. If you're experiencing slow sorting, in almost all cases it'll be an issue with your indexes/query plan. Changing your collation function should not be high on the list of things to troubleshoot.
When I originally wrote this answer (over 4 years ago) I said that if you wanted, you could use utf8_general_ci most of the time, and only use utf8_unicode_ci when sorting was going to be important enough to justify the performance cost. However, the performance cost is no longer really relevant (and it may not have been back then, either). It's more important to sort properly in whichever language your users are using.
One other thing I'll add is that even if you know your application only supports the English language, it may still need to deal with people's names, which can often contain characters used in other languages in which it is just as important to sort correctly. Using the Unicode rules for everything helps add peace of mind that the very smart Unicode people have worked very hard to make sorting work properly.

MySQL TokuDB: Высокопроизводительный MySQL-движок TokuDB переведён в разряд открытых проектов

http://www.opennet.ru/opennews/art.shtml?num=36779

Компания Tokutek открыла исходные тексты проекта TokuDB (Tokutek storage engine), в рамках которого развивается высокопроизводительный транзакционный движок хранения для MySQL и MariaDB. Вместо классических B-tree деревьев в TokuDB применяются рекурсивные индексы (Fractal Tree indexes), что в сочетании с хранением данных в сжатом виде, позволяет значительно оптимизировать операции ввода/вывода.

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

При проведении тестов, TokuDB опережает InnoDB при добавлении больших объемов данных более чем в 10 раз (InnoDB 1,555 записей в сек, TokuDB 16,437 записей в сек), но проигрывает по степени нагрузки на CPU при выборке данных. Недостаточная эффективность выборки данных компенсируется ситуациями когда требуется произвести выборку большого числа последовательно сохранённых записей. В некоторых тестах выигрыш в скорости добавления данных достигает 80 раз. Применяемые методы сжатия данных позволяют в разы уменьшить размер базы и индексов (в 6.2 раза по сравнению с InnoDB и в 5.5 раз по сравнению с MyISAM), в некоторых ситуациях степень сжатия данных может достигать 25 раз.

MariaDB multi-source replication

http://blog.mariadb.org/tag/mariadb-10/

Available since 10.0.0

• Multi-source replication (MDEV-253)
  • Multi-source replication is a longtime wish of many users. In scenarios where you partition your data over many masters you can then replicate the data from all masters onto one slave. Typical use cases are:
    • Data partitioned over many masters can be pulled together onto one slave for analytical queries
    • Many masters can replicate to the same slave and a complete backup can be done on the slave
    • Newer hardware usually provides more performance. Usually all hardware isn’t upgraded at once and multi-source can be used for replicating many masters to a powerful new slave.
  • Original code from Taobao

Более подробное описание
https://mariadb.atlassian.net/browse/MDEV-253

Each master is handled by a specific replicator instance in the slave server. Each replicator instance consists of separate I/O thread, SQL thread, and associated state and configuration. In efffect, several replication slaves are running at the same time, each replicating from a separate master, but all replicating into a common data store (typically, but not necessarily to separate databases/tables).
A replicator instance is identified with a user-chosen name used in replication SQL statements such as CHANGE MASTER TO ... This name is also included in file names to distinguish the files that keep the replication state (relay logs, master.info, relay-log.info). This way, each separate instance can be configured separately, but otherwise the same way as existing single-source replication.

синтаксис
https://mariadb.com/kb/en/multi-source-replication/

Crash-resistant replication: How to avoid MySQL replication errors

http://www.mysqlperformanceblog.com/2013/07/15/crash-resistant-replication-how-to-avoid-mysql-replication-errors/

MySQL+SSL репликация

Репликация у MySQL изначально без шифрования, что снижает нагрузку на систему, но при разнесении базы на несколько площадок возникает вопрос безопасной репликации. Это можно делать как через сторонние тоннели, так и штатными методами через SSL. Будем делать штатными методами.