mysqldump and mysqlcheck now work perfectly

Backups of your database data and database maintenance are essential things that everybody should do who runs database servers in production.

I used to do this with a little batch that looks like this:

mysqldump --add-drop-table --default-character-set=utf8 
  --databases mysql -h localhost -u [my root user] -p 
  > "E:\mysql_dump\dump_mysql.sql"

mysqldump --add-drop-table --default-character-set=utf8 
  --all-databases -h localhost -u [my working user] -p 
  > "E:\mysql_dump\dump.sql"

mysqlcheck -u [my root user] -p --all-databases --analyze --optimize

This little batch creates two dump files - one with the mysql database which stores all the privilege data and a second dump file with the rest of the data (my working user has privileges for all databases except mysql). Finally, the mysqlcheck command optimizes my MyISAM tables and renews the index information of my tables to ensure that MySQL's optimizer can make his query desicions based on current index status data.

Until recently (at least 5.0.15) it wasn't possible to use the --all-database option for the mysqldump and mysqlcheck commands. mysqldump was stopped by some views and mysqldump also tried to optimize and analyze the tables in information_schema and failed on these. So I always had to specify all databases one by one (--databases [database1] [database2] ...) which required to maintain this list if I created or deleted databases on my server.

Now this requirement is over - I can use --all-databases and not care about the databases that I create or delete. Working with MySQL has again become a little bit more comfortable :-).