Translated from 日本語 • View original Please note that this is an old article
Hello. This is Komiya.
In a service that mixes the company's MyISAM tables,
I consulted with you about an incident where a backup with mysqldump failed and a cold backup was taken but it came out inconsistent.
I would like to record the story and summarize the precautions.
(I remember telling it lightly in the past, so I thought everyone knew it, but the ease of understanding and the ability to convey,
It seems that I lacked the recognition that my and other people's levels of love for mysql are slightly different.
Troubles happen, and failure is a sign of challenge and the source of success, but we have to make sure we don't repeat the same thing. ）
What is important is the consistency of the data between the master and the slave.
In other words,
⇒ The backup target must never be updated.
⇒ The position at the time of backup acquisition and the binary log file name must be clear.
is super important.
In a mixed MyISAM environment, it will be inconsistent if you are not careful.
If there is an inconsistency, it will be quite difficult, such as the content that should have been purchased will not be bought.
If you only use InnoDB, you don't have to worry about inconsistencies if you add --single-transaction.
・Causes of mysqldump failure
mysqldump: Error 1317: Query execution was interrupted when dumping table
This error is caused by
This is the one that comes out when the query is interrupted while reading data in mysqldump. Ctrl + C or kill killed the thread.
(I was told on Twitter.) Thank you very much.
Then, for consistency, I decided to take the data from the master with mysqldump,
At that time, the method of stopping replication in slave and using it without updates also caused inconsistencies.
(Even though MyISAM is a mixed environment, FLUSH TABLES WITH READ LOCK; I didn't do it)
In the case of a mixed environment with MyISAM, transactions cannot be used, so
Replication stop or share lock requires update stop.
ex)
FLUSH TABLES WITH READ LOCK;
Wait for sleep or sync to end the lock (definitely stop updating)
Logging positions and file names
mysqldump
UNLOCK TABLES;
In particular, it has been pointed out that the query is not interrupted (the consistency with the interruption is messed up), and I don't really understand that.
If you try not to update it, there will be no interruption, so I think the only way to deal with it is to stop updating.
・Causes of inconsistencies between the master and slave after cold backup
I got the following error and could not start replication
Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file'
reset master immediately after startup on the original master; The update came in before I hit (I can't think of anything else, so I'm guessing)
rep starts from the initial position after reset master,
It seems that the number of tables was different between the master and the slave.
reset master; If you do, the master binary log will be flushed into the data file and disappear.
The slave looking at the binary log will be erased and cannot be read, so
Be careful when running with a master that has a slave.
・Countermeasures
To ensure that MyISAM tables are updated and mysqldump does not fail, we recommend the following measures:
- FLUSH TABLES WITH READ LOCK After putting it on,
(If MyISAM is mixed, locking with --opt alone will not be consistent as a whole)
(If you stop replication on a slave machine and dump it, there is no possibility that it will be updated, so you don't need to lock the share.)
２．show master status; to make sure it hasn't been updated
(It seems that the shared lock may not be good if you wait a few seconds, such as waiting for a running query
http://d.hatena.ne.jp/jitsu102/20110423/1303553133）、 - Block 3306 in iptables (iptables -A INPUT -p tcp --dport 3306 -j DROP) and
(Or if you can restart, write skip-networking in my.cnf and reflect it)
４．show master status; I hit it many times to make sure it hasn't been updated, - Take bkup with mysqldump. Then it will definitely not be updated and it will prevent inconsistencies.
Or with a cold backup.
・How to check the position when using data for replication purposes
If you have a replication use, you need to check the binary log files and positions somehow.
mysqldump (fetched from master),
If you write --master-data, the CHANGE MASTER TO syntax will be written at the beginning of the dumpfile, so you can start rep from there.
mysqldump (taken from slave),
From 5.5 onwards, you can use the following useful options:
--dump-slave: If you take a dump from a slave, include the information of the master that the slave is referencing as a CHANGE MASTER in the dump.
--apply-slave-statements: Add the STOP SLALLOW and START SLAVE commands before and after the CHANGE MASTER.
--include-master-host-port: Include the master's hostname and port in the CHANGE MASTER command.
In earlier versions, it would be nice to record the position when replication was stopped (it should also be in the error log).
For cold backups (from the master),
If you look at the binary log in the unzipped data directory with mysqlbinlog with tail, you will find the binary log file name and position.
When cold backing up from a slave, the position should probably be written in the error log. (Please check)
*Reset master in master; If you hit the slave, you will lose the data you want to replicate in the slave, so
It is essential to make sure that it has not been updated from the time you backed it up in advance.
・What I would like to recommend to confirm consistency
If you don't know when it will be updated and you don't know if it's difficult to stop or if you don't fully grasp it, etc.,
show master status; It would be a good idea to check it multiple times for data consistency.
(From the master, the service downtime when mysqldump is wasted.) ）
After that, it seems quick to block it on the network.
There may be an opinion that set global read_only=1, but be careful because users with SUPER privileges can update it.
・Mysqldump option used in the case of mixing InnoDB only and MyISAM Details
InnoDB：
--all-databases --quote-names --opt --single-transaction --master-data=2 --hex-blob --flush-logs -R --order-by-primary
MyISAM：
--all-databases --quote-names --lock-all-tables --hex-blob --flush-logs -R --master-data=2 --order-by-primary
InnoDB is --single-transaction, MyISAM is --lock-all-tables! It would be good to remember.
If you take it from the master: --master-data=2 shows master status; is recorded in the head of the dump file as a CHANGE MASTER TO syntax.
If you choose from a slave: --dump-slave=2 (*MySQL 5.5 or later), the position of the master will be recorded at the head of the dump file.
mysqldump reference:
MySQL ::MySQL 5.5 Reference Manual ::4.5.4 mysqldump — A Database Backup Programmysqldump --dump-slave About - Studio3104::BLOG.newDon't add --flush-logs to mysqldump --single-transaction - @tmtms's note
As an aside, the reason why I had to take dump data from the master in the first place was
It seems that it was because the slave was about half the low specification of the master, and the heavy processing (a large amount of Delete) overlapped, causing a significant delay.
It seems that it is common for processing that has been completed immediately in the master to be delayed in the slave.
Saving is necessary for profit, but it is also important to have room and balance, such as not causing loss of service opportunities.
Delete in MySQL seems to be a very heavy category, so I think it will be a light process if you do it frequently or partition the table and drop the partition.
Partitioning Reference:
High-speed processing! Get started with MySQL partitioning | LIG Co., Ltd. I tried MySQL's partitioning function - (゚∀゚)o彡 sasata299's blogIntroduction to MySQL for Social Games - Technology of DeNA Han's Computer Road: Partitioning Use Cases - http session information
Thank you for taking a look at the above.