Summary
When using the Windows Edition of MySQL/MariaDB, it is necessary 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 will [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.
flush tables with read lock
> flush tables with read lock;
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.
unlock tables
> unlock tables;
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 Batch File------------
@echo off
net stop <MYSQ Service Name>
if %ERRORLEVEL% neq 0 (
echo "ERROR"
exit 1
) else (
echo "SUCCESS"
)
---------------------------------------
- Set the following script to run after the snapshot
--------- Sample Batch File------------
@echo off
net start <MYSQ Service Name>
if %ERRORLEVEL% neq 0 (
echo "ERROR"
exit 1
) else (
echo "SUCCESS"
)
---------------------------------------
Target Products
- ActiveImage Protector 2016 / 2018 Windows
(13451128097049)
Comments
0 comments
Article is closed for comments.