Serov.eu » mysql

docker mysqldump

Evgen — Thu, 01 Jul 2021 21:07:35 +0000

Рецепт бекапа mysql БД, если она работает в докере:
Создаем скрипт:

#!/bin/bash

DATA=`date +"%Y-%m-%d_%H-%M"`

docker exec app-mysql /usr/bin/mysqldump -u bitrix  --triggers  --events --routines --password=PASS1234 bitrix | gzip -9 > "$DATA"-backup.sql.gz

Если надо исключить таблицы, которые не нужны, то добавляем флаг --ignore-table=БД.Таблица

Для bitrix актуально следующее:

--ignore-table=bitrix.b_stat_referer_list  --ignore-table=bitrix.b_stat_path  --ignore-table=bitrix.b_event_log  --ignore-table=bitrix.b_stat_guest  --ignore-table=bitrix.b_stat_session  --ignore-table=bitrix.b_stat_hit  --ignore-table=bitrix.b_stat_page  --ignore-table=bitrix.b_stat_referer  --ignore-table=bitrix.b_stat_path_cache

От потерь этих таблиц, которые содержат статистику по сайту, никто сильно не пострадает, а бекап с 5гб может уменьшиться до 30мб

Партиции mysql

Evgen — Sun, 30 May 2021 11:52:03 +0000

С версии MySQL 5.1 поддерживается партиция, позволяя Вам распределить части индивидуальных таблиц по файловым системам согласно правилам, которые Вы можете устанавливать в значительной степени так, как необходимо.


Типы Разбиения
 
RANGE partitioning: назначает строки разделам, основанным на значениях столбца, попадающих внутрь заданного диапазона.
LIST partitioning: подобно выделению разделов диапазоном, за исключением того, что раздел выбран основанным на столбцах, соответствующих одному из набора дискретных значений.
HASH partitioning: раздел выбран основанным на значении, возвращенном определяемым пользователем выражением, которое функционирует на значениях столбца в строках, которые будут вставлены в таблицу. Функция может состоять из любого выражения, допустимого в MySQL, которое выдает не отрицательное целочисленное значение.
KEY partitioning: подобно выделению разделов hash, за исключением того, что обеспечены только один или большее количество столбцов, которые будут оценены, и сервер MySQL обеспечивает собственную хэш-функцию. Эти столбцы могут содержать не целочисленные значения, так как хэш-функция, обеспеченная MySQL, гарантирует целочисленный результат, независимо от типа данных столбца.


От слов к практике. На примере Таблица разбитая на 2 раздела с типом LIST



CREATE TABLE 'contact` (
	`id` INT(11) NOT NULL AUTO_INCREMENT,
	`created` DATETIME NOT NULL,
	`text1` VARCHAR(20) NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci',
	`text2` VARCHAR(20) NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci',
	`file_name` VARCHAR(40) NOT NULL COLLATE 'utf8mb4_unicode_ci',
	`pn` TINYINT(3) UNSIGNED NOT NULL DEFAULT '1',
	PRIMARY KEY (`id`,`pn`) USING BTREE,
	INDEX `file_name_idx` (`file_name`) USING BTREE
)

				 
COLLATE='utf8mb4_unicode_ci'
ENGINE=InnoDB
AUTO_INCREMENT=3794499

/*!50100 PARTITION BY LIST (pn)
(PARTITION p0 VALUES IN (0) ENGINE = InnoDB,
 PARTITION p1 VALUES IN (1) ENGINE = InnoDB)  */;


Тут стоит обратить внимание  на строчку PRIMARY KEY (`id`,`pn`) USING BTREE,
Если не добавить в PRIMARY KEY столбец pn то ничего не выйдет.


Для меня в данном случае, разбиение на партиции была нужна для того, чтобы быстро удалять данные относящиеся к 0 или к 1 партиции. Делается это командой 

ALTER TABLE  contact TRUNCATE PARTITION p0;

Которая за 1 сек может удалить миллионы строк (по сути удаляет файл) из партиции p0


На физ уровне будет два файла таблицы:
contact#P#p0.ibd
contact#P#p1.ibd


Хорошая документация тут http://www.rldp.ru/mysql/mysqlpro/parts.htm


Кстати, update по столбцу  pn происходит очень долго, и после такого апдейта команда 
ALTER TABLE  contact TRUNCATE PARTITION p0;
тоже происходит не мгновенно

Подсчет количества по дате

Evgen — Mon, 19 Nov 2018 10:00:48 +0000

SELECT COUNT(id) AS count_id, DATE_FORMAT(`date`, '%Y %m %d %H') AS d, header, anons FROM cms_news GROUP BY d ORDER BY id

mysql trigger history table

Evgen — Sat, 20 Oct 2018 20:21:23 +0000

Поддержка для триггеров включена, начиная с MySQL 5.0.2.

Когда нужно историчность данных



CREATE TABLE `user` (
	`id` INT(11) NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(255) NULL DEFAULT NULL,
	`password` CHAR(32) NULL DEFAULT NULL,
	PRIMARY KEY (`id`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=0
;





CREATE TABLE `user_history` (
	`id` INT(11) NOT NULL AUTO_INCREMENT,
	`user_id` INT(11) NULL DEFAULT NULL,
	`name` VARCHAR(255) NULL DEFAULT NULL,
	`password` CHAR(32) NULL DEFAULT NULL,
	`date_changed` DATETIME NULL DEFAULT NULL,
	PRIMARY KEY (`id`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=0
;






CREATE DEFINER=`root`@`%` TRIGGER `history` BEFORE UPDATE ON `user` FOR EACH ROW BEGIN

INSERT INTO user_history 
SELECT NULL, h.*, NOW() FROM user h WHERE id = OLD.id;


END

mysql joins

Evgen — Thu, 29 Mar 2018 19:05:31 +0000

Как включить логирование в MySQL

Evgen — Tue, 27 Feb 2018 18:12:39 +0000

set global general_log='on'; set global log_output='table';
В файл /etc/my.cf добавляем следующие строки

[mysqld] ... log-queries-not-using-indexes log=/var/log/mysql-queries.log log-error=/var/log/mysql-errors.log log-slow-queries=/var/log/mysql-slow-queries.log ...
Создаем файлы и назначаем права, MySQL почему-то см этого не делает в CentOS 5:

touch /var/log/mysql-queries.log touch /var/log/mysql-errors.log touch /var/log/mysql-slow-queries.log chown mysql:mysql /var/log/mysql* chmod 640 /var/log/mysql*
Перезапускаем сервис:

service mysqld restart

Бекап mysql routines

Evgen — Wed, 22 Apr 2015 08:33:35 +0000

Недавно столкнулся с такой проблемой. Развернул бекап mysql базы в среде разработке, и не нашел хранимые процедуры. Бекап я делаю через штатную програмку mysqldump. Она хороша тем, что это официальный инструмент для бекапа, он входит в пакет mysql.

Итак, чтобы mysqldump сохранял процедуры, нужно добавить ключ

--routines

Полностью команда:

#!/bin/sh
data=`/bin/date +%d.%m.%Y_%H-%M-%S`
/usr/bin/mysqldump --user=root --password=pass --routines name_db > /root/sql/sql-dump_${data}.sql

mysqldump есть еще много нюансов работы. Один из важным моментов — она блокирует БД, на время работы, так что, если ваше БД больше гига, лучше это делать на слейв сервере.

А так можно всю БД забекапить

/usr/local/bin/mysqldump --all-databases --default-character-set=utf8 --user=root --password=passroot > /home/webmaster/sql-dump.sql

Путь до mysqldump может быть другой.

Узнать его можно командой

which

which mysqldump