I'd like to know if a 'service mysqld restart' will automatically execute a clean flush tables before restarting the service.
I have Fedora/CentOS with 5.0.77 with mainly MyISAM tables, thousands of them.
Yes it will. In fact, if you have InnoDB all dirty pages in the InnoDB Buffer Pool are flushed to disk as part of the shutdown process.
If you want to speed up that operation, you should set the following parameter in /etc/my.cnf
Setting innodb_max_dirty_pages_pct to zero(0) keeps the InnoDB Buffer Pool to the bare minimum of dirty pages but will slightly increase disk I/O by flushing the InnoDB Buffer Pool a little more frequently.
Default for innodb_max_dirty_pages_pct is 75 in MySQL 5.5, and 90 before MySQL 5.5.
Doing this will speed up mysql shutdown since there will be less to flush. This will even be more noticeable in version.
If you want to flush the InnoDB Buffer Pool without 'service mysqld restart' run this
If replication is involved, STOP SLAVE will automatically be executed as well.
If you have MyISAM, all MyISAM tables will be flushed as well because if a MyISAM table is not flushed (say in the event of a crash), then MyISAM tables that were open at the moment of a crash will still have a nonzero open file count in its headers. This marks a MyISAM table as crashed. You would need to run REPAIR TABLE again such a table.
Same goes with InnoDB if tables did not have committed data written but kept them in the ib_logfiles, thus crash recovery runs in the startup phase of mysqld.
Thus 'service mysqld restart' has to impose FLUSH TABLES for MyISAM and InnoDB.