Does a MyISAM table locks if I copy it while MySQL is running? (READ DISCLAIMER)

Question

DISCLAIMER: I'm very aware this is not supposed to be done, but time consistency between tables aren't a concert here and Im trying whatever pops in my mind right now to have alternatives for punctual, on demand backups in complement to the more robust, scheduled ones.

Pretty much I want to know if there will be any reading problem while I copy a MyISAM table files (the .frm, .MYD, MYI) and it gets a transaction.

Most tables are small, so we could just take the risk, but there are a couple that worries me becuase of their size. I don't mind waiting for the transaction to get done, what worries me is getting a read error and failing to get a response from the query

That's about it, if you know something please let me know

Bill Karwin · Accepted Answer · 2014-03-24T20:59:00.027

+1 to @RolandoMySQLDBA of course for another valiant answer. But more to the point of your question:

...will [there] be any reading problem while I copy a MyISAM table files (the .frm, .MYD, MYI) and it gets a [write] transaction.

YES.

You can't get a consistent backup even for a single MyISAM table unless you do some type of locking to prevent writes. Rolando gave a pretty thorough answer with options available to you for locking.

One other option that people use for backing up MyISAM data: LVM snapshots. See http://www.lenzg.net/mylvmbackup/ for a great tool to assist with this.

The final recommendation is to stop using MyISAM, and use InnoDB instead. Then you can do fast, non-locking physical backups with Percona XtraBackup.

Re your comment:

Because reading through a large file isn't instantaneous or atomic. While your backup is progressing through the table, other concurrent updates could change both rows that your backup has already read, and rows that your backup hasn't reached yet.

Take a textbook example for transaction behavior: I do a bank transfer by debiting my bank account and crediting your bank account. My bank account is stored on a row that is physically early in the file, and your bank account is stored on a row later in the file.

While this is going on, the backup is reading through the file, and it has read up to a mid-point at the time our transaction happens. When we restore, we get my original account balance, without the debit applied, because the backup had already passed that point when the debit occurred (and it isn't able to go backwards). But the restore includes your updated account balance, because the backup got to that point in the file after we increased your balance.

Ergo, free money! ;-)

MyISAM solves this by requiring the table to be locked against updates while it's doing a read.

InnoDB solves this by keeping multiple versions of rows, for as long as a reading transaction needs to see them for the sake of a consistent view of the database. So anyone can update the data without waiting, even though the backup is in progress.

Percona XtraBackup solves this in a slightly different way: it can go backwards, in a way. While it's reading the data file, it keeps checking the transaction log continually, to see if there are any late changes it needs to include. These changes may apply to parts of the datafile that Percona XtraBackup has already read. But as long as it gets the data file plus any changes that were logged since the backup started, it can reconstruct the full database.

But that only works for storage engines like InnoDB, that create a reliable transaction log. Percona XtraBackup can also back up MyISAM, but only by using locking, like any other backup tool.

score 4 · Answer 2 · answered Mar 24 '14 at 17:38

In some cases, you could face issues copying .MYD and .MYIs because of a major weakness MyISAM has: Data changes (changes to .MYD files) are cached in the OS. (Of course, this would be 100 times worse with MySQL in Windows, so I'll leave Windows out of this answer).

If you are using MySQL 5.6, I have a little good news.

The command FLUSH TABLES WITH READ LOCK would look all MyISAM tables under a single lock. MySQL 5.6 now has the syntax FLUSH TABLES db.tb,db.tb ... WITH READ LOCK.

The MySQL 5.6 Documentation says

FLUSH TABLES tbl_name [, tbl_name] ... WITH READ LOCK

This statement flushes and acquires read locks for the named tables. The statement first acquires exclusive metadata locks for the tables, so it waits for transactions that have those tables open to complete. Then the statement flushes the tables from the table cache, reopens the tables, acquires table locks (like LOCK TABLES ... READ), and downgrades the metadata locks from exclusive to shared. After the statement acquires locks and downgrades the metadata locks, other sessions can read but not modify the tables.

Because this statement acquires table locks, you must have the LOCK TABLES privilege for each table, in addition to the RELOAD privilege that is required to use any FLUSH statement.

This statement applies only to existing base tables. If a name refers to a base table, that table is used. If it refers to a TEMPORARY table, it is ignored. If a name applies to a view, an ER_WRONG_OBJECT error occurs. Otherwise, an ER_NO_SUCH_TABLE error occurs.

Use UNLOCK TABLES to release the locks, LOCK TABLES to release the locks and acquire other locks, or START TRANSACTION to release the locks and begin a new transaction.

This variant of FLUSH enables tables to be flushed and locked in a single operation. It provides a workaround for the restriction in MySQL 5.6 that FLUSH TABLES is not permitted when there is an active LOCK TABLES ... READ.

This statement does not perform an implicit UNLOCK TABLES, so an error results if you use the statement while there is any active LOCK TABLES or use it a second time without first releasing the locks acquired.

If a flushed table was opened with HANDLER, the handler is implicitly flushed and loses its position.

CAVEAT : For you InnoDB people, same doc has FLUSH TABLES tbl_name [, tbl_name] ... FOR EXPORT that include metadata locking within the system tables.

You could write a script to lock every MyISAM table in a database, sleep that DB Connection, do your OS copy, and release the lock.

This is what I do now because I work with a MyISAM table that has 4019 partitions. This forces the flush of everything for .MYDs. Issuing a lock like this and sleeping the connection, I can then FTP MYDs and MYI to other data centers.

If you use Percona Server 5.6, I have better news. Percona just invented LOCK TABLES FOR BACKUP. This takes the guess work and grunt work out of locking tables with a measure of confidence in the tables' consistency.

http://www.percona.com/doc/percona-server/5.6/management/backup_locks.html

YOUR ACTUAL QUESTION

You should make the effort to script the locking of MyISAM per database.

Here is the script I use to create a lock on every MyISAM table in a database before the backup

MYSQL_HOSTADDR=${1}
DB_SUFFIX=${2}
RUNLOG=${3}

MYSQL_DATABASE=DB_${DB_SUFFIX}

MYSQL_CONN="-h${MYSQL_HOSTADDR} -D${MYSQL_DATABASE}"

PRODUCT_LIST="prod1 prod2 prod3"

#
# Acquire Locks
#
for PRODUCT in `echo "${PRODUCT_LIST}"`
do
        PREFIX=sum_${PRODUCT}
        LOCK_SLEEP_KILL_SCRIPT=/tmp/Kill_Summary_Table_Locks_For_${PRODUCT}_${DB_SUFFIX}.sql
        SQL="SELECT GROUP_CONCAT(table_name) FROM information_schema.tables"
        SQL="${SQL} WHERE table_schema=DATABASE() AND table_name LIKE '${PREFIX}%'"
        TABLES_TO_LOCK=`mysql -h${MYSQL_HOSTADDR} -D${MYSQL_DATABASE} -ANe"${SQL}"`
        LOCK_PATTERN="LockProcessFor${PRODUCT}"
        FLUSH_COMMAND="FLUSH TABLES ${TABLES_TO_LOCK} WITH READ LOCK; SELECT SLEEP(86400) INTO @${LOCK_PATTERN}"
        mysql ${MYSQL_CONN} -ANe"${FLUSH_COMMAND}" &
        SQL="SELECT MAX(id) FROM (SELECT 0 id UNION"
        SQL="${SQL} SELECT id FROM information_schema.processlist"
        SQL="${SQL} WHERE LOCATE('${LOCK_PATTERN}',Info)>0"
        SQL="${SQL} AND id <> CONNECTION_ID()) A"
        SLEEP_ID=0
        while [ ${SLEEP_ID} -eq 0 ]
        do
                sleep 5
                SLEEP_ID=`mysql -h${MYSQL_HOSTADDR} -ANe"${SQL}"`
        done
        echo "KILL QUERY ${SLEEP_ID};" > ${LOCK_SLEEP_KILL_SCRIPT}
done

Here is the script I use to release those locks after the backup

MYSQL_HOSTADDR=${1}

cd /tmp
ls Kill_Summary_Table_Locks_For*.sql > KillScriptList.txt
for KILLSQL in `cat KillScriptList.txt` ; do mysql -h${MYSQL_HOSTADDR} < ${KILLSQL} ; rm -f ${KILLSQL}; done

Does a MyISAM table locks if I copy it while MySQL is running? (READ DISCLAIMER)

2 Answers2

YES.

YOUR ACTUAL QUESTION

Give it a Try !!!

Linked