Постоянно растет ibdata1

Этого недостаточно. Сначала вам следует слить всю базу в дамп (например утилитой mysqldump), затем удалить сами базы в mysql, затем прописать в конфигурацию упомянутый параметр, перезапустить mysql, затем залить базы из дампа.

И только в таком порядке.

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

Andreyka

pupseg

Пока что чистка происходит следующим образом:

- стягиваются дампы

- останавливается мускул

- удаляются ibdata1, ib_logfile0 и ib_logfile0

- запускается mysqld, при запуске он создает эти файлы но уже условно пустые

- восстанавливаются все дб руками, по одному

Ребята, может я не так выразился. Трабл такой:

- Файл ibdata1 растет постоянно 24/7, днем и ночью, утром и вечером. Средняя скорость прироста 1 минута +1 mb к размеру этого файла.

- Из-за того что используемый софт весь в ион кубе, то каких либо правок "ручным способом" сделать не представляется возможным.

- Размер файла ibdata1 может быть 150gb, а при просмотре в ips панели сумма всех баз 50-60 gb, т.е. 90-100 gb получается разница неизвестно какого хлама.

В результате, чтобы этот хлам дальше не забивал винт до отказа сервера, делается процедура описанная выше. Но она делается поочередная / руками. Вопрос в том, как (с помощью чего, какой последовательностью команд, каким софтом / скриптами и т.д.) облегчить эту рутинную процедуру?

Если есть еще какие то способы очистки таких баз от ненужного хлама - подскажите, желательно сразу с указанием команд / софта / скриптов.

Lupus Спасибо за совет, пойду погуглю указанные утилиты, но баз много <100 на сервере и хотелось по возможности найти что ни-ть для этого случая.

для слива всех баз в 1 файл у mysqldump есть ключ -A

может пригодится

#дамп всех баз

mysqdump -pРУТОВЫЙ_ПАРОЛЬ_КБАЗАМ -A --opt > /путь/куда/кладем/дамп/all_bases.sql

#останавливаем mysql

/etc/init.d/mysqld stop

#удаляем файды

rm /путь/к/файлу/bdata1

rm /путь/к/файлу/ib_logfile0

rm /путь/к/файлу/ib_logfile0

#запускаем mysql

/etc/init.d/mysqld start

#заливаем дамп

mysql < /путь/куда/кладем/дамп/all_bases.sql

если всё это записать в файл, и запускать его, то будет более-менее автоматизировано

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

вполне известно там все :

1. запись в таблице innodb идет в так называемых страницах. запись с данными меньше страницы все равно занимает всю страницу.

3. есть вероятность, что дерево индексов заполняется неоптимальным "рассеяным" образом во время работы приложения. При восстановлении как правило все плотно

3. Всевозможная служебная информация.

и 4. каким-то образом остается информация от незавершенных транзакций - а это уже известный баг некоторых старых версий mysql. Лучше бы он вам не попался, потому что там все мутно как-то.

так все же, после процедуры восстановления, насколько уменьшается объем файлов ?

Небольшое расхождение - это нормально. То что вам там показывает ispmanager - вообще не аргумент. Там, по-моему, у оператора show table status для innodb задокументировано неточное поведение.

Вообще странное поведение для innodb. Как вариант обновить mysql и включить лог sql запросов.

Далее с лога дергать все create/insert и экспериментальным путем искать.

Банальный optimize table table_name ; должен помочь. По крайней мере избавит от удаленных записей , а занятое ими место будет доступно для новой информации.

iHead, netwind, Stek, большое спасибо за советы.

iamsens, Это именно то что я ожидал, благодарю.

maximus200, если будете использовать код от iamsens, то учитывайте что mysqldump не корректно бакапит view таблицы. Там косяки в правами. И если вьювы есть, дамп будет падать с ошибкой.

Поможет "sed -i 's/\sDEFINER=`[^`]*`@`[^`]*`//' dump.sql" , но на 150gb базе, это будет очень долго.

Вообще это не правильно, путем бакапа решать проблему. Innodb не должен так себя вести. Надо запросы анализировать. Как вариант "с потолка", innodb как тип выставлен по умолчанию, а в где то в скриптах есть конструкция с созданием временных таблиц. И они создаются банальными create/drop , а не через create temporary .

Косяков там нет.

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

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

#!/bin/sh
#backup databases
#Usage: 
#dbbackup  - backup all databases
#dbbackup dbname - backup single database

SINGLE_BASE="1"
DBNAME=$*
DBUSER="root"
DBPASS="пароль_root"
DBHOST="localhost"
DIR="./"
TIMENAME=`date +%Y-%m-%d-%H%M`
LOG="mysql_dump.log"
mkdir -p ${DIR}/${TIMENAME}
touch $LOG

if [ "${DBNAME}" = "" ]; then
	DBASES=`mysql -u${DBUSER} -h ${DBHOST} -p${DBPASS} -Bse 'show databases'`
	for DBNAME in $DBASES; do
		TIMEDUMP=`date '+%T %x'`
		echo "backup has been done at $TIMEDUMP : $TIMENAME on db: $n" >> $LOG
		mysqldump -u${DBUSER} -h ${DBHOST} -p${DBPASS} ${DBNAME} > "$DIR/${TIMENAME}/${DBNAME}.sql" 
	done
	SINGLE_BASE="0"
fi

if [ "${SINGLE_BASE}" = "1" ]; then
	mysqldump -u${DBUSER} -h ${DBHOST} -p${DBPASS} ${DBNAME} > "$DIR/${TIMENAME}/${DBNAME}.sql" 
fi

---------- Добавлено 17.03.2014 в 09:52 ----------

Скрипт, которым можно слить всех пользователей с правами. Перенаправить вывод в файл, получатся SQL запросы для создания юзеров на другом сервере:

#!/bin/sh

#Экспорт SQL запросов создания привилегий для всех юзеров. Перенаправить в файл. 

SUPERUSER="$1"

SUPERPASS="$2"





SAVEIFS=$IFS

IFS='

'





if [ "${SUPERUSER}" = "" ]; then

	echo "Please specify the username of mysql."

	echo "Usage: exportsqlusers root pass"

	exit 1

fi

if [ "${SUPERPASS}" = "" ]; then

	echo "Please specify the password of mysql."

	echo "Usage: exmportsqlusers root pass"

	exit 1

fi

#echo ${SUPERUSER}

#echo ${SUPERPASS}



USERS_HOSTS=`mysql -u ${SUPERUSER} -B -N -p"${SUPERPASS}" -e "SELECT user, host FROM user" mysql`





for USER_HOST in ${USERS_HOSTS}; do

	NULL_STRING=0	#индикатор пустой строки

	#echo ${USER_HOST}

	USER=`echo ${USER_HOST} | awk '{print $1}'`

	#echo user: ${USER}

	HOST=`echo ${USER_HOST} | awk '{print $2}'`

	#echo host: ${HOST}

	if [ "${USER}" = "" ]; then

		NULL_STRING=1

	fi

	if [ "${HOST}" = "" ]; then

		NULL_STRING=1

	fi

	if [ "${NULL_STRING}" = "0" ]; then

		SQL_QUERIES=`mysql -u "${SUPERUSER}" -p"${SUPERPASS}" -B -N -e"SHOW GRANTS FOR '${USER}'@'${HOST}'"`

		for SQL_QUERY in ${SQL_QUERIES}; do

			echo ${SQL_QUERY}\;

			#echo -----------------------------------------

		done

	fi

done



IFS=$SAVEIFS

---------- Добавлено 17.03.2014 в 09:54 ----------

Теперь импортируем то и то:

#!/bin/sh

#Импорт SQL запросов в базу (привилегии пользователей)

FILE="$1"

SUPERUSER="$2"

SUPERPASS="$3"

if [ "${SUPERUSER}" = "" ]; then

	echo "Please specify the username of mysql."

	echo "Usage: importsqlusers filename root pass"

	exit 1

fi

if [ "${SUPERPASS}" = "" ]; then

	echo "Please specify the password of mysql."

	echo "Usage: importsqlusers filename root pass"

	exit 1

fi

if [ "${FILE}" = "" ]; then

	echo "Please specify the file with sql queries."

	echo "Usage: importsqlusers filename root pass"

	exit 1

fi

#echo ${SUPERUSER}

#echo ${SUPERPASS}



DATA=`cat "${FILE}" | grep GRANT`





SAVEIFS=$IFS

IFS='

'

for QUERY in ${DATA}; do

	echo ${QUERY}

	mysql -u ${SUPERUSER} -B -p"${SUPERPASS}" -e "${QUERY}"

	echo --------------

done

mysql -u ${SUPERUSER} -B -p"${SUPERPASS}" -e "FLUSH PRIVILEGES;"



IFS=$SAVEIFS

#!/bin/sh

#Импорт архивированных баз данных.

#должны быть в формате sql.gz

SUPERUSER="$1"

SUPERPASS="$2"

if [ "${SUPERUSER}" = "" ]; then

	echo "Please specify the username of mysql."

	echo "Usage: importdumps root pass"

	exit 1

fi

if [ "${SUPERPASS}" = "" ]; then

	echo "Please specify the password of mysql."

	echo "Usage: importdumps root pass"

	exit 1

fi



#echo ${SUPERUSER}

#echo ${SUPERPASS}



DIR=`ls -l | grep sql\.gz | awk '{print $9}'`



SAVEIFS=$IFS

IFS='

'

for FILENAME in ${DIR}; do

	echo ${FILENAME}

	#A=`gzip -dkc ${FILENAME} `

	DB_NAME=`gzip -dkc ${FILENAME} | grep Host: | grep Database: | awk -FDatabase: '{print $2}' | sed -e 's/^ *//g' -e 's/ *$//g'`

	mysql -u ${SUPERUSER} -B -p"${SUPERPASS}" -e "CREATE DATABASE IF NOT EXISTS ${DB_NAME} CHARSET utf8;"

	gunzip < ${FILENAME} | mysql -u ${SUPERUSER} -p"${SUPERPASS}"  ${DB_NAME}

	

	#echo --------------

done

#mysql -u ${SUPERUSER} -B -p"${SUPERPASS}" -e "FLUSH PRIVILEGES;"



IFS=$SAVEIFS