0

I'm running into an odd issue when setting MySQL into a brief read-only mode for backup.

Reads continue fine during that "read only" minute, but writes are just rejected instead of being queued to run after tables are unlocked.

I'm not seeing the attempted writes in mysql "show processlist" where I would expect to see them, they just seem to be discarded.

Using "Flush Tables With Read Lock", copying the data files (less than a minute), then "unlock tables".

MySQL and backup code:

FLUSH TABLES WITH READ LOCK;
SET GLOBAL read_only = ON;

system time cp -pdRu /mysql_data_dir/thedb /backup_dir/thedb

SET GLOBAL read_only = OFF;
unlock tables;

Background:

Running WordPress MultiSite on MySQL 5.1, CentOS. 70,000 MyISAM tables.

Looking for the fastest way to back up the DB while keeping the DB online.

Both reads AND writes need to be possible during the backup. But the backup time is very short, so it's fine for the writes to be queued until the "unlock tables" command.

In the WordPress error_log, seeing MySQL errors like this:

[20-Nov-2014 01:41:56 UTC] Wordpress database error The MySQL server is running with the --read-only option so it cannot execute this statement for query INSERT INTO wp_options...

So my question is...

Why are writes being quietly discarded instead of being queued, as they would be for a regular table lock?

And more importantly, how can I fix that so they DO get queued and are eventually run in the order they were requested? :)

Thanks!

  • Why don't you use InnoDB? – Vérace Nov 21 '14 at 11:18
1

It is a brave soul indeed that still uses an all-MyISAM database.

Why are writes being quietly discarded instead of being queued, as they would be for a regular table lock?

Look at the error message again

[20-Nov-2014 01:41:56 UTC] Wordpress database error The MySQL server is running with the --read-only option so it cannot execute this statement for query INSERT INTO wp_options...

WordPress is reporting this as the error, but it not a failure on the part of MySQL. You turned on read_only. That's what WordPress sees.

And more importantly, how can I fix that so they DO get queued and are eventually run in the order they were requested? :)

Just remove read_only:

FLUSH TABLES WITH READ LOCK;
system time cp -pdRu /mysql_data_dir/thedb /backup_dir/thedb
unlock tables;

The DB Connections should start to queue up all INSERTs, UPDATEs, and DELETEs.

You said something far more profound in the question

Both reads AND writes need to be possible during the backup. But the backup time is very short, so it's fine for the writes to be queued until the "unlock tables" command.

SUGGESTION #1

You need to convert all your tables to InnoDB. Reads and Writes can continue and queue up just fine without FLUSH TABLES WITH READ LOCK;. You would dump the database like this

MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
MYSQLDUMP_OPTIONS="--single-transaction --routines --triggers"
mysqldump ${MYSQL_CONN} ${MYSQLDUMP_OPTIONS} thedb > thedb.sql

SUGGESTION #2

If you want everything to remain MyISAM, your should get another DB Server and setup MySQL Replication. All your writes would happen on the Master. On the Slave, you would do the backups like this

STOP SLAVE;
FLUSH TABLES WITH READ LOCK;
system time cp -pdRu /mysql_data_dir/thedb /backup_dir/thedb
START SLAVE;

You can also split your reads between Master and Slave, or do all reads on the Slave.

EPILOGUE

If you go with SUGGESTION #1, convert everything to InnoDB with this script

MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
MYISAM_TO_INNODB_CONVERSION_SCRIPT=/tmp/ConvertMyISAMToInnoDB.sql
SQL="SELECT CONCAT('ALTER TABLE ',table_schema,'.',table_name,' ENGINE=InnoDB;')"
SQL="${SQL} FROM information_schema.tables WHERE engine='MyISAM' AND"
SQL="${SQL} table_schema NOT IN ('information_schema','mysql','performance_schema')"
SQL="${SQL} ORDER BY (data_length+index_length)"
mysql ${MYSQL_CONN} -ANe"${SQL}" > ${MYISAM_TO_INNODB_CONVERSION_SCRIPT}
mysql ${MYSQL_CONN} < ${MYISAM_TO_INNODB_CONVERSION_SCRIPT}

GIVE IT A TRY !!!

UPDATE 2014-11-21 10:52 EST

In your comment, you just asked

Is there any major reason to NOT install a 2nd MySQL instance (the slave) on the same server?

There is a huge reason why you do not want to setup a 2nd MySQL instance on the same box to do replication.

  • Everything is MyISAM
  • MyISAM only caches indexes, never data
  • MyISAM defers caching data to the OS
  • Master and Slave caching the same data in two different DB means twice as much data for the OS to cache
  • See my other reasons in my old post Is it unwise to run replication on same physical server?

If you convert everything to InnoDB (which caches data and indexes, has failsafes for crash recovery and transactions), then, yes, you could setup a 2nd MySQL instance for replication on the same server (provided you have sufficient RAM and diskspace for the 2nd instance).

  • Thanks for the in-depth answer!! > Just remove read_only Your first answer to my original question - above the 2 recommendations - I can't seem to make that work. mysql> flush tables lock; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'lock' at line 1 Am I missing something? sounds like that WOULD absolutely be what I'm trying to do at this point. – Dan Nov 21 '14 at 15:12
  • Please look back at my answer. I didn't say FLUSH TABLES LOCK;. I said FLUSH TABLES WITH READ LOCK;. – RolandoMySQLDBA Nov 21 '14 at 15:16
  • <br> <br/>**Regarding the 2 recommendations ** <br/>1.) Looking to stay with MyISAM at this point <br/> <br/>2.) Replication: need to stay with a single server for the time being. Figured I'd go to replication eventually. <br/>That said, is there any major reason to NOT install a 2nd MySQL instance (the slave) on the same server? docs on that seem pretty clear. <br/>Currently on 15k SAS RAID 1, but plan on upgrading to similar system with SSD RAID 0 as soon as possible/feasible. – Dan Nov 21 '14 at 15:19
  • sorry, the color coding got me. FLUSH TABLES WITH READ LOCK is the same statement I've been trying. So the problem is the OTHER statement, this one? SET GLOBAL read_only = ON; – Dan Nov 21 '14 at 15:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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