1

I see in the mysqlcheck manual that there's an option for --tables that overrides that databases option, but it isn't well explained.

I'm trying to run mysqlcheck -o and I want it to optimize a specific table that exists in every database, but only that table.

** I think I may not have been clear enough

I have 250 databases that all contain the table api_log. I want to run a single command that will optimize just the table api_log, but on every database that mysql server has.

For instance:

mysqlcheck -o --tables api_log -A

Obviously this specific snippet won't work, but it expresses what I'd like to accomplish. I don't want to optimize every table in every database, just one table that exists in all 250 databases.

Hrvoje Špoljar
  • 5,245
  • 26
  • 42
Allen Sellars
  • 11
  • 2

1 Answers1

2

You can optimize single table like this :

mysqlcheck -u root -o database_name table_name

e.g. this one will optimize table 'user' of 'mysql' database.

$ mysqlcheck -u root -o mysql user 
mysql.user                                         OK

edit

For case when you have hundreds of databases; you can script this procedure like 

table_name='api_log'
mysql -NB -u root -e 'show databases' | while read db_name
do
  if mysql -NB -u root "$db_name" -e 'show tables' | egrep -wq "$table_name"; then
    echo "Optimizing $db_name.$table_name"
    mysqlcheck -u root -o "$db_name" "$table_name"
  fi
done

which will interrate over all databases and check if any has table name named 'api_log' if it does it will optimize it.

Hrvoje Špoljar
  • 5,245
  • 26
  • 42
  • Well that's not really what I was looking for. I have 250 databases all with a table named api_logs, and I want to run a single command that will optimize table api_log in every database. – Allen Sellars Nov 09 '14 at 20:21
  • so you script it... for i in "$db_list"; do mysqlcheck -u root -o "$i" api_log; done . I don't think there is more straightforward way to do it. – Hrvoje Špoljar Nov 09 '14 at 22:14