MySQL Permanent Lock on Table

Question

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?

I don't believe you can. What is the actual problem you're trying to solve? — Jonathan Hall, Nov 12 '13 at 18:27
I am bringing in several large datasets to perform some detailed analysis, and wanted to make sure that the original data is not modified once it is imported. — Michael Sheaver, Nov 12 '13 at 18:47

score 4 · Accepted Answer · answered Nov 12 '13 at 18:29

4

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'

answered Nov 12 '13 at 18:29

Rahul Tripathi

168,305
31
280
331

1

Thanks Rahul, this does look like my best option. – Michael Sheaver Nov 12 '13 at 18:48

score 0 · Answer 2 · answered Nov 12 '13 at 18:49

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.

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

MySQL Permanent Lock on Table

2 Answers2