CODEWITHSUNDEEP
mysqllockingpermanent
Michael Sheaver
Michael Sheaver

Reputation: 2119

MySQL Permanent Lock on Table

From what I read in the following: http://dev.mysql.com/doc/refman/5.0/en/lock-tables.html, the LOCK TABLES statement is only good for the current session. How can I permanently lock specific tables to make them read-only, for all connection sessions, until I explicitly unlock them?

Upvotes: 3

Views: 1850

Answers (2)

Jonathan Hall
Jonathan Hall

Reputation: 79754

For large, time-consuming operations like that, one good option can be to copy the data to a new location, to do your manipulations. This essentially makes a snapshot of the data, leaving the database unhindered (and possibly still accepting reads and writes) while you perform your operation.

  1. Stop MySQL
  2. Copy data files to new location.
  3. Restart MySqL
  4. Perform manipulations on the data copy.
  5. Delete copy (by way of DROP TABLE)

Upvotes: 0

Rahul Tripathi
Rahul Tripathi

Reputation: 172608

I dont think you can simply lock any table like that. The best way you can do so is to revoke all update, insert and delete privileges

Somthing like this:

REVOKE DROP, INSERT, TRUNCATE ON database.table FOR 'user'@'host'

Upvotes: 4

Related Questions