Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

HowTo repair/optimize all tables in ssh?

Posted on 2008-10-02
4
1,501 Views
Last Modified: 2008-10-15
HowTo repair/optimize all tables in ssh?
this line repair only one table, but how to repair them all?
repair table ibf_posts;
0
Comment
Question by:Stepa6ka
  • 2
4 Comments
 
LVL 37

Expert Comment

by:momi_sabag
ID: 22631662
you can use the sp_msforeachtable strored procedure in sql server
0
 

Author Comment

by:Stepa6ka
ID: 22631877
and how can i use it in mysql?
0
 

Author Comment

by:Stepa6ka
ID: 22635091
Help me please... :/
0
 
LVL 29

Accepted Solution

by:
Michael Worsham earned 500 total points
ID: 22650465
Here's a script I found via Google search that does both optimize and repair...

Reference:
http://www.adspeed.org/2007/01/mysql-shell-script-to-optimize-all.html

#!/bin/sh
 
# this shell script finds all the tables for a database and run a command against it
# @usage "mysql_tables.sh --optimize MyDatabaseABC"
# @date 6/14/2006
# @version 1.1 - 1/28/2007 - add repair
# @version 1.0 - 6/14/2006 - first release
# @author Son Nguyen
 
DBNAME=$2
 
printUsage() {
  echo "Usage: $0"
  echo " --optimize <dbname>"
  echo " --repair <dbname>"
  return
}
 
 
doAllTables() {
  # get the table names
  TABLENAMES=`mysql -D $DBNAME -e "SHOW TABLES\G;"|grep 'Tables_in_'|sed -n 's/.*Tables_in_.*: \([_0-9A-Za-z]*\).*/\1/p'`
 
  # loop through the tables and optimize them
  for TABLENAME in $TABLENAMES
  do
    mysql -D $DBNAME -e "$DBCMD TABLE $TABLENAME;"
  done
}
 
if [ $# -eq 0 ] ; then
  printUsage
  exit 1
fi
 
case $1 in
  --optimize) DBCMD=OPTIMIZE; doAllTables;;
  --repair) DBCMD=REPAIR; doAllTables;;
  --help) printUsage; exit 1;;
  *) printUsage; exit 1;;
esac

Open in new window

0

Featured Post

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

838 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question