Enable Read Only Mode on a MySQL server
Here are some useful options that can prevent writing to MySQL databases, while retaining the right reading.
The activation of these features may be particularly interesting in the case of a change of server. You can then export and import all data from one server to another, being sure to maintain data integrity. As for customers, they can only read data.
1. Method by changing a variable server
To do this, connect to the server with root user:
mysql-h localhost-u root-p
and run the command:
mysql> set GLOBAL read_only = true;
The data in all databases are accessible only for reading. Note that root guard on the other hand always have the right to write.
Example of inserting data with a simple user:
mysql> INSERT INTO foo VALUES ( 'tata2');
ERROR 1290 (HY000): The MySQL server is running with the-read-only
option so it can not execute this statement
To disable this mode, you can then run the following command:
mysql> set GLOBAL read_only = false;
2. Method of positioning locks
There is also a second method is to put locks via:
mysql> FLUSH TABLES WITH READ LOCK;
The latter will then close all open tables and locks all the reading tables and bases.
At this time, applications that wish to make an entry will be queued until the unlock command:
mysql> UNLOCK TABLES;
It will be possible to see a list of requests waiting in the listing process as follows:
mysql> SHOW processlist;
| 5 | root | localhost | test | Query | 160 | Waiting for release of readlock | INSERT INTO foo VALUES ( 'tata2') |
Conclusion
The first method seems more adapted when transferring data from one MySQL server to another, while the second plutût it will be used in case of a backup cold.
Enjoy this article?
-
Chamomile