How to backup Windows Server Edition of MySQL/MariaDB

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