I have a database that is entirely comprised of MyISAM tables(yes, I did not create it). I need to create a slave and start replication so we could point read-only queries there. Here is my idea: Perform a Veritas cluster snapshot of the files, move them to the new place, and start replication.
Before I can start the cluster snapshot, I need to read lock so I don't lose any transaction (i.e., no SQL commands against the MyISAM table during the snapshot). I tried doing the following code but whenever I come out of the shell, the database becomes writable.
Is it the case that the read_only allows my ID to write since I own the lock? I am trying to google, but every article talks about mysqldump. Please help.
mysql> SET GLOBAL READ_ONLY=ON;
Query OK, 0 rows affected (0.00 sec)
mysql> create table test2(a integer);
ERROR 1050 (42S01): Table 'test2' already exists
mysql> create table test3(a integer);
Query OK, 0 rows affected (0.00 sec)
Best Answer
When you ran
SET GLOBAL READ_ONLY=ON;
you stop users that do not have the SUPER privilege.If every user has the SUPER, then setting read_only does not help.
Run this query
Whatever rows appear are the users that can still make changes even with read_only on.
SUGGESTIONS
What you really need to do is run
and hold that DB Connection open until you run your snapshot to completion.
How can you do so ? In my post How can I optimize a mysqldump of a large database?, I mention doing this in conjunction with a SLEEP command. Here is an except from that post under the heading "Option 4 : USE YOUR IMAGINATION" Point #2:
Here is some code from my 3-year-old post Writing transactions to .MYD file to take Backup
In your particular case, just replace the mysqldump with your LVM snapshot code.
GIVE IT A TRY !!!