Summary
In the Windows Edition of MySQL/MariaDB, it is required to stop the service by script and perform an offline backup.
More Info
In the Windows edition of MySQL commands, there is an [–execute] option that can execute commands by passing arguments. However, since [quit] is executed immediately after command execution, general queries can be executed, but it cannot use the [flush tables with read lock] command to operate. Inspection result — Execute following command in a batch file — mysql -u root -p<password> -e “flush tables with read lock;”
It [quit] immediately after this command and it is unlocked before executing [unlock tables] and the insert query is committed.
19 Query
flush tables with read lock
19
Quit
13 Query insert into ORDERLIST(ITEM_ID, ORDER_DATE) values (9630, SYSDATE(3)) 13 Query update ORDERCOUNT set ORDER_COUNT = ORDER_COUNT + 1 where ITEM_ID = 9630 13 Query commit
12 Query select * from ITEMLIST where CATEGORY = 97
— Execute interactively from the mysql command prompt — > flush tables with read lock;
Even when an insert query is executed, it will never be committed.
21 Query
flush tables with read lock
13 Query select * from ITEMLIST where CATEGORY = 617 : 15 Query select * from ITEMLIST where CATEGORY = 610 10 Query insert into ORDERLIST(ITEM_ID, ORDER_DATE) values (8627, SYSDATE(3)) 12 Query select * from ITEMLIST where CATEGORY = 372 : 13 Query select * from ITEMLIST where CATEGORY = 290 15 Query insert into ORDERLIST(ITEM_ID, ORDER_DATE) values (24, SYSDATE(3))
9 Query select * from ITEMLIST where CATEGORY = 839
— Execute interactively from the mysql command prompt — > unlock tables; It then immediately unlocks and the update query is committed.
* However, the order is random.
21 Query
unlock tables
14 Query update ORDERCOUNT set ORDER_COUNT = ORDER_COUNT + 1 where ITEM_ID = 48046 11 Query update ORDERCOUNT set ORDER_COUNT = ORDER_COUNT + 1 where ITEM_ID = 20164 16 Query update ORDERCOUNT set ORDER_COUNT = ORDER_COUNT + 1 where ITEM_ID = 28458 15 Query update ORDERCOUNT set ORDER_COUNT = ORDER_COUNT + 1 where ITEM_ID = 24 10 Query update ORDERCOUNT set ORDER_COUNT = ORDER_COUNT + 1 where ITEM_ID = 8627 13 Query update ORDERCOUNT set ORDER_COUNT = ORDER_COUNT + 1 where ITEM_ID = 42818 18 Query update ORDERCOUNT set ORDER_COUNT = ORDER_COUNT + 1 where ITEM_ID = 14144 11 Query commit 12 Query update ORDERCOUNT set ORDER_COUNT = ORDER_COUNT + 1 where ITEM_ID = 37162 18 Query commit 15 Query commit 12 Query commit 9 Query update ORDERCOUNT set ORDER_COUNT = ORDER_COUNT + 1 where ITEM_ID = 39381
14 Query commit
Therefore, you can not execute [flush tables with read lock] from a batch file and perform online backup.
Resolution
Create a batch file to Stop/Start the service.
- Set the following script to run before the snapshot —————-sample—————- @echo off net stop <MySQL Service Name>
if %ERRORLEVEL% neq 0 ( echo “Failed to stop MySQL Service” exit 1 ) else ( echo “Successful to stop MySQL Service”
)
- Set the following script to run after the snapshot —————-sample—————- @echo off net start <MySQL Service Name>
if %ERRORLEVEL% neq 0 ( echo “Failed to stop MySQL Service” exit 1 ) else ( echo “Successful to stop MySQL Service”
)
Target Products
- ActiveImage Protector 2016 / 2018 Windows
Comments
0 comments
Article is closed for comments.