Question :
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)
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
SELECT user,host FROM mysql.user WHERE super_priv = 'Y';
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
FLUSH TABLES WITH READ LOCK;
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:
Using separate process, run “FLUSH TABLES WITH READ LOCK; SELECT SLEEP(86400)” before launching mysqldumps. Kill this process after mysqldumps are complete. This is helpful if a database contains both InnoDB and MyISAM
Here is some code from my 3-year-old post Writing transactions to .MYD file to take Backup
MYSQL_CONN="-hhostip -uusername -ppassword"
mysql ${MYSQL_CONN} -A -e"FLUSH TABLES WITH READ LOCK; SELECT SLEEP(86400)" &
SEARCHING_FOR_SLEEP=0
while [ ${SEARCHING_FOR_SLEEP} -eq 0 ]
do
sleep 3
SEARCHING_FOR_SLEEP=`${MYSQL} ${MYSQL_CONN} -A -e"SHOW PROCESSLIST;" | grep -c "SELECT SLEEP(86400)"`
done
sleep 1
SLEEP_ID=`mysql ${MYSQL_CONN} -A -e"SHOW PROCESSLIST;" | grep "SELECT SLEEP(86400)" | awk '{print $1}'`
mysqldump ${MYSQL_CONN} --master-data=2 --single-transaction --flush-privileges --routines --triggers --all-databases > /root/mydata.sql
mysql ${MYSQL_CONN} -A -e"KILL ${SLEEP_ID}"
In your particular case, just replace the mysqldump with your LVM snapshot code.
MYSQL_CONN="-hhostip -uusername -ppassword"
mysql ${MYSQL_CONN} -A -e"FLUSH TABLES WITH READ LOCK; SELECT SLEEP(86400)" &
SEARCHING_FOR_SLEEP=0
while [ ${SEARCHING_FOR_SLEEP} -eq 0 ]
do
sleep 3
SEARCHING_FOR_SLEEP=`${MYSQL} ${MYSQL_CONN} -A -e"SHOW PROCESSLIST;" | grep -c "SELECT SLEEP(86400)"`
done
sleep 1
SLEEP_ID=`mysql ${MYSQL_CONN} -A -e"SHOW PROCESSLIST;" | grep "SELECT SLEEP(86400)" | awk '{print $1}'`
*** Snapshot Code Goes Here ***
mysql ${MYSQL_CONN} -A -e"KILL ${SLEEP_ID}"
GIVE IT A TRY !!!