CODEWITHSUNDEEP
mysql
Richard
Richard

Reputation: 41260

MySQL flush tables - Current database or every database?

Does the MySQL command :

FLUSH TABLES;

flush every table in the current database, or every table on the server ?

I'm using MySQL 5.0 - the documentation is unclear, although it does mention that :

FLUSH TABLES WITH READ LOCK;

will do so for ALL databases.

Thanks.

Upvotes: 23

Views: 50927

Answers (4)

Jihad Mehdi
Jihad Mehdi

Reputation: 65

FLUSH TABLES is done for all databases, except if you specify tables one by one.

the syntax is:

FLUSH TABLES tbl_name [, tbl_name] ...

with READ LOCK:

FLUSH TABLES tbl_name [, tbl_name] ... WITH READ LOCK

and to contain all tables within a database, you can get query SHOW TABLES to display all tables from the selected database. so with some edits, the query will be something like this:

FLUSH TABLES db_name.table_1, 
             db_name.table_2,
             db_name.table_3,
             ...

Upvotes: 0

kingspector
kingspector

Reputation:

when you do just a mysql_connect and then "show tables" for example -> mysql_error(): No Database selected

with "flush tables" there is no mysql_error, so i think the answer is every database

Upvotes: 7

Brent
Brent

Reputation: 23722

It's all databases.

Upvotes: 26

Bill Karwin
Bill Karwin

Reputation: 562731

I tried to look this up but I couldn't find an authoritative answer either.

  • I looked in the manual, as you did.
  • I found the MySQL Internals documentation on FLUSH TABLES, but it doesn't say specifically.
  • I even read the source code in mysql_server/sql/sql_base.cc but couldn't find the answer quickly.

I assume the answer is one of those things that the developers feel is so obvious that they never need to say it.

According to the internals doc, the MySQL table cache holds a list of of most recently used tables. There is no mention of database-specific table caches, there seems to be only one table cache in the MySQL Server.

FLUSH TABLES is described as forcing all open tables (those in the table cache) to be closed. There is no mention of this being limited to one database, but you can specify individual tables in the arguments to FLUSH TABLES. So likewise, I assume this applies to the whole table cache by default, and therefore to all databases with open files on the MySQL Server.

Upvotes: 11

Related Questions