How to backup Windows Server Edition of MySQL/MariaDB

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)