В MySQL есть команда OPTIMIZE TABLE, которая может быть использована для освобождения неиспользуемого пространства в установке MySQL. Существует ли способ (встроенная команда или обычная хранимая процедура) запустить эту оптимизацию для каждой таблицы в базе данных и/или на сервере, или это то, что вы должны написать сами?
Для этого в командной строке можно использовать mysqlcheck
.
Одна база данных:
mysqlcheck -o <db_schema_name>
Все базы данных:
mysqlcheck -o --all-databases
Я сделал это 'простые' скрипта:
set @tables_like = null;
set @optimize = null;
set @show_tables = concat("show tables where", ifnull(concat(" `Tables_in_", database(), "` like '", @tables_like, "' and"), ''), " (@optimize:=concat_ws(',',@optimize,`Tables_in_", database() ,"`))");
Prepare `bd` from @show_tables;
EXECUTE `bd`;
DEALLOCATE PREPARE `bd`;
set @optimize := concat('optimize table ', @optimize);
PREPARE `sql` FROM @optimize;
EXECUTE `sql`;
DEALLOCATE PREPARE `sql`;
set @show_tables = null, @optimize = null, @tables_like = null;
Чтобы запустить его, просто добавьте его в любой SQL IDE и подключен к базе данных.
Примечание: этот код выиграл't работа на панели phpmyadmin.
Он запускает шоу таблицах заявлении и сохраняет его в подготовленном заявлении. Затем он запускает оптимизировать таблицы в выбранном наборе.
Вы можете контролировать, какие таблицы для оптимизации, установив другое значение в var @tables_like
(например: набор @tables_like = '%тест%';
).
Следующий пример php-скрипта поможет вам оптимизировать все таблицы в вашей базе данных
<?php
dbConnect();
$alltables = mysql_query("SHOW TABLES");
while ($table = mysql_fetch_assoc($alltables))
{
foreach ($table as $db => $tablename)
{
mysql_query("OPTIMIZE TABLE '".$tablename."'")
or die(mysql_error());
}
}
?>
Делать все необходимые процедуры для фиксации всех таблиц во всех базах данных с простой shell-скрипт:
#!/bin/bash
mysqlcheck --all-databases
mysqlcheck --all-databases -o
mysqlcheck --all-databases --auto-repair
mysqlcheck --all-databases --analyze
Из phpMyAdmin и другие источники вы можете использовать:
SET SESSION group_concat_max_len = 99999999;
SELECT GROUP_CONCAT(concat('OPTIMIZE TABLE `', table_name, '`;') SEPARATOR '') AS O
FROM INFORMATION_SCHEMA.TABLES WHERE
TABLE_TYPE = 'BASE TABLE'
AND table_name!='dual'
AND TABLE_SCHEMA = '<your databasename>'
Затем вы можете копировать и усилитель; вставить его в новый запрос или выполнить его из своего собственного источника. Если вы Don'т увидеть всю постановку:
Вы можете оптимизировать/проверка и восстановление всех таблиц базы данных, используя MySQL клиент.
Во-первых, вы должны получить весь список таблиц, разделенных ',':
mysql -u[USERNAME] -p[PASSWORD] -Bse 'show tables' [DB_NAME]|xargs|perl -pe 's/ /,/g'
Теперь, когда у вас есть все список таблиц для оптимизации:
mysql -u[USERNAME] -p[PASSWORD] -Bse 'optimize tables [tables list]' [DB_NAME]
На уровне базы данных это можно сделать с помощью MySQL Administrator
(входит в состав MySQL GUI Tools).
Просто выберите схему и нажмите кнопку Maintenance
в правом нижнем углу.
После того, как GUI Tools перешли в статус End-of-life, их трудно найти на странице mysql. Нашел их через Google: http://dev.mysql.com/downloads/gui-tools/5.0.html.
Не знаю, умеет ли это делать новый MySQL Workbench.
Также можно использовать инструмент командной строки mysqlcheck
, который тоже должен уметь это делать.
Если вы хотите анализировать, ремонтировать и оптимизировать все таблицы во всех базах данных на сервере MySQL, вы можете сделать это в один проход из командной строки. Вам потребуется root-доступ для этого, хотя.
mysqlcheck -u root -p --auto-repair --optimize --all-databases
После запуска, вам будет предложено ввести пароль корневого пользователя MySQL. После этого он запустится и вы увидите результаты, как это'ы происходит.
Пример вывода:
yourdbname1.yourdbtable1 OK
yourdbname2.yourdbtable2 Table is already up to date
yourdbname3.yourdbtable3
note : Table does not support optimize, doing recreate + analyze instead
status : OK
etc..
etc...
Repairing tables
yourdbname10.yourdbtable10
warning : Number of rows changed from 121378 to 81562
status : OK
Если вы Don'т знать пароль root и использованием WHM, вы можете изменить его изнутри WHM собирается: Дом > служб SQL > для MySQL root пароль
Этот скрипт будет принимать пароль root как вариант и оптимизировать его один на один, с выходом статус:
#!/bin/bash
if [ -z "$1" ] ; then
echo
echo "ERROR: root password Parameter missing."
exit
fi
MYSQL_USER=root
MYSQL_PASS=$1
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
TBLLIST=""
COMMA=""
SQL="SELECT CONCAT(table_schema,'.',table_name) FROM information_schema.tables WHERE"
SQL="${SQL} table_schema NOT IN ('information_schema','mysql','performance_schema')"
for DBTB in `mysql ${MYSQL_CONN} -ANe"${SQL}"`
do
echo OPTIMIZE TABLE "${DBTB};"
SQL="OPTIMIZE TABLE ${DBTB};"
mysql ${MYSQL_CONN} -ANe"${SQL}"
done
Стартовый скрипт в списке и запустить средство против ДБС...
#!/bin/bash
declare -a dbs
unset opt
for each in $(echo "show databases;" | mysql -u root) ;do
dbs+=($each)
done
echo " The system found [ ${#dbs[@]} ] databases." ;sleep 2
echo
echo "press 1 to run a check"
echo "press 2 to run an optimization"
echo "press 3 to run a repair"
echo "press 4 to run check,repair, and optimization"
echo "press q to quit"
read input
case $input in
1) opt="-c"
;;
2) opt="-o"
;;
3) opt="-r"
;;
4) opt="--auto-repair -c -o"
;;
*) echo "Quitting Application .."; exit 7
;;
esac
[[ -z $opt ]] && exit 7;
echo " running option: mysqlcheck $opt in 5 seconds on all Dbs... "; sleep 5
for ((i=0; i<${#dbs[@]}; i++)) ;do
echo "${dbs[$i]} : "
mysqlcheck $opt ${dbs[$i]} -u root
done
Если вы обращаетесь к базе данных напрямую, то вы можете написать следующий запрос:
OPTIMIZE TABLE table1,table2,table3,table4......;
мой 2cents: начните с таблицы с самой высокой фрагментации
for table in `mysql -sss -e "select concat(table_schema,".",table_name) from information_schema.tables where table_schema not in ('mysql','information_schema','performance_schema') order by data_free desc;"
do
mysql -e "OPTIMIZE TABLE $table;"
done