0

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)
1
  • How large is your database? Sep 28, 2015 at 16:44

1 Answer 1

1

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 !!!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.