How FLUSH TABLES works in MySQL

“FLUSH TABLES” is generally used for closing all tables by force in MySQL. Its really interesting that how mysql do this. Most of time we are doing this because someone adds new tables outside of MySQL. (Like for MyISAM tables, we are copying .FRM, .MYD and .MYI files. We can do this when MySQL is already running). These table’s information will not be in table_cache buffer. So after coying it when we will try to access those tables through MySQL client, we’ll get error that “table doesn’t exist”. To resolve this, we have to run “FLUSH TABLES”.

  • When we’ll do a “FLUSH TABLES”, it’ll flush all tables to disk back. ( As we know that table structure will be in buffer cache and for that we are setting up value for table_cache system parameter)
  • Internally, there is one variable named refresh_version which will be incremented. Every time when a thread release a table, it will check if the refresh_version of the table is updated or not. Means its the same as the current refresh version or not.
  • If Its not the same than it will close it and will broadcast a signal on COND_refresh for waiting other thread/instances of that table to be closed and after that it will flush the table.
  • The current refresh_version will be also compared to the open refresh_version after thread/instance will get lock on the table. If the refresh_version is different than thread/instance will free all locks, reopen the table and try to get the locks again. This will happen to gell all tables to use the newest version.
  • When all tables will be closed than FLUSH TABLE returns to the MySQL client that its done and all tables are flushed to the disk.

One thought on “How FLUSH TABLES works in MySQL”

Leave a Reply

Your email address will not be published. Required fields are marked *