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.

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.

  • 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 9 '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 9 '14 at 22:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.